Step by Step walkthrough on CRUD operation on Azure Table: Part I

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. 

 

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. 

 

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

 

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. 

 

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

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

 

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. 

 

3. Inherit the class from TableServiceEntity class. 

 

After inheriting class will look like below, 

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

 

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

 

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

2. Define the properties 

 

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 

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

2. Remove all the namespaces and leave the below namespaces 

 

3. Inherit the class from TableServiceContext class. 

 

So, after inheriting class will look like 

 

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

 

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

6. Write the function to add players in the table 

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

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

 

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. 

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

 

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 6:  Working with Connection strings 

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 

 

2. Click on the  Setting in the tab. 

 

3. The from top click on the Add Settings. 

 

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

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 

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. 

 

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

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

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

  

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

 

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]

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 8:  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

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

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

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

<head runat="server">

    <title></title>

</head>

<body>

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

    <div style="height: 614px">   

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

            onclick="btnAddPlayer_Click" />

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

            onclick="btnEditPlayer_Click" />

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

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

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

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

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

        </asp:GridView>

    </div>

    </form>

</body>

</html>

Step 9

To add a Player 

 

a. First need to create account reading connection string.

b. Create object of PlayerContext .

c. Call AddPlayer method passing object of PlayerEntity class. 

Step 10

To edit a player 

 

a. First need to create account reading connection string.

b. Create object of PlayerContext .

c. Call EditPlayer method passing object of PlayerEntity class. 

Step 11

To delete a player 

 

a. First need to create account reading connection string.

b. Create object of PlayerContext .

c. Call DeletePlayer method passing object  playerID. 

Step 12

 

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

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

 

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.