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.
- System.DataService
- System.DataService.Client
- Microsoft.WindowsAzure.Diagnostic
- Microsoft.WindowsAzure.RunTime
- 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.