Objective

In this article, I will show you how we can perform CRUD operation on AZURE table. This article is divided into two parts. In part 1, I am using local development fabric and storage. In next part, I will show you how can use AZURE table from Microsoft data center.

Step 1: Create the project

Open visual studio. Create a new project by selecting New from File menu. Then from AZURE tab select Windows Azure Cloud Service project.

clip_image002

Give a meaningful name and then select a location for your project. After giving name and location click ok. Now select the Role. Select an ASP.Net WEB Role. If you want, you can change the name of selected ASP.Net Web Role but, I am leaving here the default name.

clip_image003

Now in solution explorer, you can see two projects one is cloud service project and other is ASP.Net Web Role project.

clip_image004

Step 2: Add the references

Add the below, references in your web role project. To do that, right click on web role project and select add references from context menu.

clip_image005

Add the below references in the project.

1. System.DataService

2. System.DataService.Client

3. Microsoft.WindowsAzure.Diagnostic

4. Microsoft.WindowsAzure.RunTime

5. Microsoft.WindowsAzure.StorageClient

clip_image006

clip_image007

Step 3: Create Data Model class

Let us say, we want to create a table in database as of below schema,

Player Table

Column Name Type
PlayerId String
PlayerName String
PlayerSports String
PlayerCountry String

1. Right click on web role project and add a new class. Give a meaningful name to the class. I am giving name here PlayerModel

clip_image008

clip_image010

So, now you can see in solution explorer, you have added a new class PlayerModel.cs.

2. Add the namespaces. Only keep below namespcaes and remove all other namespace.

clip_image012

3. Inherit the class from TableServiceEntity class.

clip_image014

After inheriting class will look like below,

clip_image016

4. Define the constructor . In constructor define the row key and partition key.

clip_image018

You can give any string as partition key. Now model the column of table as properties of the class.

clip_image020

So the full source code for model class can be given as below,

PlayerModel.cs

using System;

using Microsoft.WindowsAzure.StorageClient;

namespace WebRole1

{

public class PlayerModel : TableServiceEntity

{

public PlayerModel()

{

RowKey = string.Format("{0:10}_{1}",

DateTime.MaxValue.Ticks - DateTime.Now.Ticks,

Guid.NewGuid());

PartitionKey = "Player";

}

public string PlayerId { get; set; }

public string PlayerName { get; set; }

public string PlayerSports { get; set; }

public string PlayerCountry { get; set; }

}

}

Step 4: Create the Entity class

1. Right click on web role project and add a new class. Give a meaningful name to the class. I am giving name here PlayerEntity

clip_image008[1]

clip_image022

2. Define the properties

clip_image024

So, the PlayerEntity class will look like

PlayerEntity.cs

namespace WebRole1

{

public class PlayerEntity

{

public string playerId { get; set; }

public string playerName { get; set; }

public string playerSports { get; set; }

public string playerCountry { get; set; }

}

}

Step 5: Create the Context class

3. Right click on web role project and add a new class. Give a meaningful name to the class. I am giving name here PlayerContext

clip_image008[2]

clip_image026

4. Remove all the namespaces and leave the below namespaces

clip_image027

5. Inherit the class from TableServiceContext class.

clip_image029

So, after inheriting class will look like

clip_image031

6. Now in the constructor, pass the base address and credentials.

clip_image033

7. Create a query to return the data from the table. Create a property in the context class for this purpose.

clip_image035

8. Write the function to add players in the table

clip_image037

In this function, you need to pass object of PlayerEntity class. Then using, automatic property initializer, you need to create instance of PlayerModel class.

9. Write a function to edit the player in the table.

clip_image039

Here again, you have to pass as parameter object of PlayerEntity class and then retrieve particular player from the table based on playerId. After retrieving the player modify the particular player and do the update object operation.

10. Write a function to delete a particular Player from the table

clip_image041

Here, again we are passing playerId of the player to be deleted.

So, for your reference the full source code is given below,

PlayerContext.cs

using System.Linq;

using Microsoft.WindowsAzure;

using Microsoft.WindowsAzure.StorageClient;

namespace WebRole1

{

public class PlayerContext : TableServiceContext

{

public PlayerContext(string baseAddress, StorageCredentials credentials)

:base(baseAddress ,credentials)

{

}

public IQueryable<PlayerModel> Players

{

get

{

return this.CreateQuery<PlayerModel>(“Players”);

}

}

public void AddPlayer(PlayerEntity player)

{

this.AddObject(“Players”,new PlayerModel{PlayerId= player.playerId ,

PlayerName = player.playerName,

PlayerCountry = player.playerCountry ,

PlayerSports = player.playerSports });

this.SaveChanges();

}

public void EditPlayer(PlayerEntity player)

{

PlayerModel playerToModify  = (from r in this.Players

where r.PlayerId == player.playerId

select r).First();

playerToModify.PlayerName = player.playerName;

playerToModify.PlayerSports = player.playerSports;

playerToModify.PlayerCountry = player.playerCountry;

this.UpdateObject(playerToModify);

this.SaveChanges();

}

public void DeletePlayer(string playerId)

{

PlayerModel playerToDelete = (from r in this.Players

where r.PlayerId == playerId

select r).First();

this.DeleteObject(playerToDelete);

this.SaveChanges();

}

}

}

Step 5: Working with Connection strings

clip_image043

Either you can use local development fabric storage or the real azure table you create at Microsoft data center.

Connection string in Using Local development

1. Click on the Web Role in Cloud project

clip_image044

2. Click on the Setting in the tab.

clip_image046

3. The from top click on the Add Settings.

clip_image047

4. When you click on Add Setting, a setting will get added. From the drop down select Connectin string as the type.

clip_image049

5. After selecting the type. Click on the Value tab. If you are going to use development storage, you do not need to do anything. If you are using storage credentials from azure table then you have to provide the credential of azure table.

Note: I will explain about using credentials of real azure table from Microsoft data center in later articles and video

clip_image050

So, after this step you can see that we have added a connection string. This connection string is going to use the local development storage. We have given name of this connection string as LocalDevConnectionString. So we will be using LocalDevConnectionString (Connection string name, if you want you can change it) in order to connect to DB.

clip_image052

If you can notice in value column that UseDevelopmentStorage=true value is there. It says, we are going to use local dev storage.

Step 6: Modifying WebRole class to create table in the storage.

1. Click on WebRole.cs class in ASP.Net Web Role Project.

clip_image053

2. You need to modify this class , first add the below code

clip_image055

You need to create the table using the below code. Make sure that, you are providing the same connection string name, which you created in previous step. [In our case it is LocalDevConnectionString]

clip_image057

So after doing above steps, the WebRole.cs class will look like

WebRole.cs

using System.Linq;

using Microsoft.WindowsAzure.Diagnostics;

using Microsoft.WindowsAzure.ServiceRuntime;

using Microsoft.WindowsAzure.StorageClient;

using Microsoft.WindowsAzure;

namespace WebRole1

{

public class WebRole : RoleEntryPoint

{

public override bool OnStart()

{

DiagnosticMonitor.Start(“DiagnosticsConnectionString”);

// For information on handling configuration changes

// see the MSDN topic at http://go.microsoft.com/fwlink/?LinkId=166357.

RoleEnvironment.Changing += RoleEnvironmentChanging;

CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>

{

configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));

RoleEnvironment.Changed += (anotherSender, arg) =>

{

if (arg.Changes.OfType<RoleEnvironmentConfigurationSettingChange>()

.Any((change) => (change.ConfigurationSettingName == configName)))

{

if (!configSetter(RoleEnvironment.GetConfigurationSettingValue(configName)))

{

RoleEnvironment.RequestRecycle();

}

}

};

}

);

var account = CloudStorageAccount.FromConfigurationSetting(“LocalDevConnectionString”);

CloudTableClient.CreateTablesFromModel(typeof(PlayerContext ),

account.TableEndpoint.AbsoluteUri,

account.Credentials);

return base.OnStart();

}

private void RoleEnvironmentChanging(object sender, RoleEnvironmentChangingEventArgs e)

{

// If a configuration setting is changing

if (e.Changes.Any(change => change is RoleEnvironmentConfigurationSettingChange))

{

// Set e.Cancel to true to restart this role instance

e.Cancel = true;

}

}

}

}

Step 7: Designing the default.aspx page.

1. I have added four buttons for add, edit and delete operations.

2. I have added a grid view to show all the players.

Default.aspx

   1: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebRole1._Default" %>

   2:

   3: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

   4:

   5: <html xmlns="http://www.w3.org/1999/xhtml">

   6:

   7: <head runat="server">

   8:

   9: <title></title>

  10:

  11: </head>

  12:

  13: <body>

  14:

  15: <form id="form1" runat="server">

  16:

  17: <div style="height: 614px">

  18:

  19: <asp:Button ID="btnAddPlayer" runat="server" Text="Add Player" Width="175"

  20:

  21: onclick="btnAddPlayer_Click" />

  22:

  23: <asp:Button ID="btnEditPlayer" runat="server" Text="Edit Player" Width="175px"

  24:

  25: onclick="btnEditPlayer_Click" />

  26:

  27: <asp:Button ID="btnDeletePlayer" runat="server" Text="Delete Player"

  28:

  29: Width="175px" onclick="btnDeletePlayer_Click" />

  30:

  31: <asp:Button ID="btnDisplayPlayer" runat="server" Text="Display Player"

  32:

  33: Width="175px" onclick="btnDisplayPlayer_Click" />

  34:

  35: <asp:GridView ID="grdPlayers" runat="server">

  36:

  37: </asp:GridView>

  38:

  39: </div>

  40:

  41: </form>

  42:

  43: </body>

  44:

  45: </html>

  46:

Step 8

To add a Player

clip_image059

a. First need to create account reading connection string.

b. Create object of PlayerContext .

c. Call AddPlayer method passing object of PlayerEntity class.

Step 9

To edit a player

clip_image061

a. First need to create account reading connection string.

b. Create object of PlayerContext .

c. Call EditPlayer method passing object of PlayerEntity class.

Step 10

To delete a player

clip_image063

a. First need to create account reading connection string.

b. Create object of PlayerContext .

c. Call DeletePlayer method passing object playerID.

Step 11

clip_image065

Here, we are fetching the data and binding the grid view.

So when you run, you will get the below output.

clip_image067

For your reference the source code is below

Default.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Microsoft.WindowsAzure;

using Microsoft.WindowsAzure.StorageClient;

using Microsoft.WindowsAzure.ServiceRuntime;

using Microsoft.WindowsAzure.Diagnostics;

using System.Data.Services;

using System.Data.Services.Client;

namespace WebRole1

{

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnAddPlayer_Click(object sender, EventArgs e)

{

var account = CloudStorageAccount.FromConfigurationSetting(“LocalDevConnectionString”);

var playerContext = new PlayerContext(account.TableEndpoint.ToString(), account.Credentials);

playerContext.AddPlayer(new PlayerEntity { playerId = “1”,

playerCountry = “India “,

playerSports = “Cricket”,

playerName = “Sachin Tendulakr” });

playerContext.AddPlayer(new PlayerEntity { playerId = “2”,

playerCountry = “Argentina “,

playerSports = “Football”,

playerName = “Messy” });

}

protected void btnEditPlayer_Click(object sender, EventArgs e)

{

var account = CloudStorageAccount.FromConfigurationSetting(“LocalDevConnectionString”);

var playerContext = new PlayerContext(account.TableEndpoint.ToString(), account.Credentials);

playerContext.EditPlayer(new PlayerEntity

{ playerId = “1”,

playerCountry = “India “,

playerSports = “Cricket”,

playerName = “Sachin Ramesh Tendulakr” });

}

protected void btnDeletePlayer_Click(object sender, EventArgs e)

{

var account = CloudStorageAccount.FromConfigurationSetting(“LocalDevConnectionString”);

var playerContext = new PlayerContext(account.TableEndpoint.ToString(), account.Credentials);

playerContext.DeletePlayer(“1”);

}

protected void btnDisplayPlayer_Click(object sender, EventArgs e)

{

List<PlayerEntity> players = new List<PlayerEntity>();

var account = CloudStorageAccount.FromConfigurationSetting(“LocalDevConnectionString”);

var playerContext = new PlayerContext(account.TableEndpoint.ToString(), account.Credentials);

var res =  playerContext.Players ;

foreach(var r in res )

{

players.Add(

new PlayerEntity

{

playerId = r.PlayerId,

playerName = r.PlayerName,

playerCountry = r.PlayerCountry,

playerSports = r.PlayerSports

}

);

}

grdPlayers.DataSource = players;

grdPlayers.DataBind();

}

}

}

I hope this article was useful. Thanks for reading. Happy Coding.

Advertisements

One thought on “Step by Step walkthrough on CRUD operation on Azure Table Part #1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s