Home > DeveloperSection > Beginner > DML and DQL using Table Service in Windows Azure

DML and DQL using Table Service in Windows Azure


Cloud Computing Cloud Computing 
Ratings:
4 Comment(s)
 5313  View(s)
Rate this:

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 FileNewProject.

·         Select Cloud template from the Installed Templates.

·         Select Windows Azure Project and enter the name of the project as AzureTableService.

·         Click OK to proceed.

DML and DQL using Table Service in Windows Azure

·         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.

DML and DQL using Table Service in Windows Azure

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>

 

DML and DQL using Table Service in Windows Azure

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:

DML and DQL using Table Service in Windows Azure

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:

DML and DQL using Table Service in Windows Azure

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:

DML and DQL using Table Service in Windows Azure

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.


WCFREST SERVICE Perform All DML operations in DATABASE

By anil babu on   4 years ago
How to Perform All DML operations in Database using WCFREST service?
and also how to consume this service in my ASP.NET webpages ?
plz give me one example

WCFREST SERVICE Perform All DML operations in DATABASE

By Rohit Kesharwani on   4 years ago
Hi,

You can refer the following to perform DML operations in Database using WCFREST service:
http://www.dotnetcurry.com/ShowArticle.aspx?ID=469

and consuming WCFREST service in asp.net application:
http://mrvshah.wordpress.com/2012/02/18/wcf-rest-service-in-an-asp-net-application/

WCFREST SERVICE Perform All DML operations in DATABASE

By anil babu on   4 years ago
Thank you so much,
Service is ok,
Consume this service In my ASP.NET webpages perform All operations,
Using WCFREST service,
I have no idea about silverlight,
if possible tell me ASP.NET webpages through perform all DML operations using WCFREST service

WCFREST SERVICE Perform All DML operations in DATABASE

By Rohit Kesharwani on   4 years ago

Don't want to miss updates? Please click the below button!

Follow MindStick