DML and DQL using Table Service in Windows Azure
In this article, I am going to
explain how we can perform insert, update, delete and select operation on
AZURE table.
Here, I am going to create a sample application in which user can insert, update,
delete the records of a student and also can display the student data in
gridview.
·
Open Microsoft Visual Studio 2010 as an administrator.
·
To create a new project File –
New –
Project.
·
Select Cloud template from the
Installed Templates.
·
Select Windows Azure Project and
enter the name of the project as
AzureTableService.
·
Click OK to proceed.
·
To add a web role to the solution, choose ASP.NET Web Role and then
choose the right arrow. The roles are displayed in the Windows Azure solution
pane of the dialog box.
·
Click OK.
Modify the
Default.aspx as shown below:
<div>
<font
face="calibri">
<table
align="center"
border="1">
<tr><td>
<table
cellpadding="5">
<tr>
<td
align="right">
<asp:Label ID="lblId" runat="server" Text="Student Id :">
</asp:Label>
</td>
<td>
<asp:TextBox
ID="txtId"
runat="server"
Columns="30">
</asp:TextBox>
</td>
</tr>
<tr>
<td
align="right">
<asp:Label
ID="lblName"
runat="server"
Text="Name :">
</asp:Label>
</td>
<td>
<asp:TextBox
ID="txtName"
runat="server"
Columns="30">
</asp:TextBox>
</td>
</tr>
<tr>
<td align="right">
<asp:Label
ID="lblAge"
runat="server"
Text="Age :">
</asp:Label>
</td>
<td>
<asp:TextBox
ID="txtAge"
runat="server"
Columns="30">
</asp:TextBox>
</td>
</tr>
<tr>
<td
align="right">
<asp:Label
ID="lblDob"
runat="server"
Text="Date of Birth :">
</asp:Label>
</td>
<td>
<asp:TextBox
ID="txtDob"
runat="server"
Columns="30">
</asp:TextBox>
</td>
</tr>
<tr>
<td
colspan="2"
align="center">
<asp:Button
ID="btnAdd"
runat="server"
Text="Insert"
onclick="btnAdd_Click"
/>
<asp:Button
ID="btnEdit"
runat="server"
Text="Edit"
onclick="btnEdit_Click"
/>
<asp:Button
ID="btnDelete"
runat="server"
Text="Delete"
onclick="btnDelete_Click" />
<asp:Button
ID="btnDisplay"
runat="server"
Text="Display"
onclick="btnDisplay_Click" />
</td>
</tr>
<tr>
<td
colspan="2"
align="center">
<asp:GridView
ID="grdStudents"
runat="server"
Width="100%">
</asp:GridView>
</td>
</tr>
</table>
</td></tr>
</table>
</font>
</div>
Now create a
StudentEntry.cs class which inherits
TableServiceEntity, as shown below:
public
class StudentEntry
: TableServiceEntity
{
public StudentEntry()
{
RowKey = Guid.NewGuid().ToString();
PartitionKey = "Student";
}
public string
StudentId
{
get; set;
}
public string
Name
{
get; set;
}
public int Age
{
get; set;
}
public string
DOB
{
get; set;
}
}
Create
StudentModel.cs as shown below:
public
class StudentModel
{
public string
StudentId
{
get; set;
}
public string
Name
{
get; set;
}
public int Age
{
get; set;
}
public string
DOB
{
get; set;
}
}
Create
StudentContext.cs class which
inherits TableServiceContext:
public
class StudentContext
: TableServiceContext
{
public StudentContext(string
baseaddress, StorageCredentials credentials)
: base(baseaddress, credentials)
{
}
public IQueryable<StudentEntry> Students
{
get
{
return this.CreateQuery<StudentEntry>("Students");
}
}
public void
AddStudent(StudentEntry student)
{
this.AddObject("Students", student);
this.SaveChanges();
}
public void
EditStudent(StudentEntry student)
{
StudentEntry studentToModify = (from r in
this.Students where
r.StudentId ==
student.StudentId select r).First();
studentToModify.StudentId = student.StudentId;
studentToModify.Name = student.Name;
studentToModify.Age = student.Age;
studentToModify.DOB = student.DOB;
this.UpdateObject(studentToModify);
this.SaveChanges();
}
public void
DeleteStudent(string studentid)
{
StudentEntry studentToDelete = (from r in
this.Students where
r.StudentId ==
studentid select r).First();
this.DeleteObject(studentToDelete);
this.SaveChanges();
}
}
To add a student record:
·
First need to create account reading connection string.
·
Create object of StudentContext.
·
Call AddStudent method passing object
of StudentEntry class.
protected void
btnAdd_Click(object sender,
EventArgs e)
{
CloudStorageAccount.SetConfigurationSettingPublisher((configname,
configSettingPublisher) =>
{
var connectionstring =
RoleEnvironment.GetConfigurationSettingValue
(configname);
configSettingPublisher(connectionstring);
});
var account =
CloudStorageAccount.FromConfigurationSetting
("DataConnectionString");
var studentContext =
new StudentContext(account.TableEndpoint.ToString(),
account.Credentials);
studentContext.AddStudent(new
StudentEntry { StudentId = txtId.Text, Name =
txtName.Text, Age = Convert.ToInt32(txtAge.Text),
DOB = txtDob.Text });
}
To update a student record:
·
First need to create account reading connection string.
·
Create object of StudentContext.
·
Call EditStudent method passing
object of StudentEntry class.
protected void
btnEdit_Click(object sender,
EventArgs e)
{
CloudStorageAccount.SetConfigurationSettingPublisher((configname,
configSettingPublisher) =>
{
var connectionstring =
RoleEnvironment.GetConfigurationSettingValue
(configname);
configSettingPublisher(connectionstring);
});
var account =
CloudStorageAccount.FromConfigurationSetting
("DataConnectionString");
var studentcontext =
new StudentContext(account.TableEndpoint.ToString(),
account.Credentials);
studentcontext.EditStudent(new
StudentEntry { StudentId = txtId.Text, Name =
txtName.Text, Age = Convert.ToInt32(txtAge.Text),
DOB = txtDob.Text });
}
To delete a student record:
·
First need to create account reading connection string.
·
Create object of StudentContext.
·
Call DeleteStudent method passing
object of StudentEntry class.
protected void
btnDelete_Click(object sender,
EventArgs e)
{
CloudStorageAccount.SetConfigurationSettingPublisher((configname,
configSettingPublisher) =>
{
var connectionstring =
RoleEnvironment.GetConfigurationSettingValue
(configname);
configSettingPublisher(connectionstring);
});
var account =
CloudStorageAccount.FromConfigurationSetting
("DataConnectionString");
var studentcontext = new
StudentContext(account.TableEndpoint.ToString(),
account.Credentials);
studentcontext.DeleteStudent(txtId.Text);
}
To display records in Gridview:
·
Here we are fetching the data and binding the gridview.
protected void
btnDisplay_Click(object sender,
EventArgs e)
{
CloudStorageAccount.SetConfigurationSettingPublisher((configname,
configSettingPublisher) =>
{
var connectionstring =
RoleEnvironment.GetConfigurationSettingValue
(configname);
configSettingPublisher(connectionstring);
});
var account =
CloudStorageAccount.FromConfigurationSetting
("DataConnectionString");
var studentcontext =
new StudentContext(account.TableEndpoint.ToString(),
account.Credentials);
List<StudentModel>
students = new List<StudentModel>();
var res = studentcontext.Students;
foreach (var
stud in res)
{
students.Add(new
StudentModel { StudentId = stud.StudentId,
Name =
stud.Name, Age = stud.Age, DOB = stud.DOB });
}
grdStudents.DataSource = students;
grdStudents.DataBind();
}
You also have to add the setting
(DataConnectionString) in a web role
configuration file:
·
Right click on web role, select Properties.
·
Move to Settings tab.
·
Add setting (DataConnectionString) as
shown in below figure:
You need to modify
WebRole.cs as shown below:
public
override bool
OnStart()
{
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();
}
}
};
});
//create a
table in windows azure
var account=CloudStorageAccount.FromConfigurationSetting("DataConnectionString");
CloudTableClient.CreateTablesFromModel(typeof(StudentContext),
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;
}
Now press F5 to execute your
application. The output should something like below:
Now fill student details in
textbox and click insert button to add the record of student. After inserting
the records click on display button to show the records of all the students. The
output should something like below:
I think this article will help
you a lot. After reading this article you can easily perform DML and DQL using
table service in windows azure application.
|