articles

Home / DeveloperSection / Articles / DML and DQL using Table Service in Windows Azure

DML and DQL using Table Service in Windows Azure

Chris Anderson10143 23-Jan-2012

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.

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)=> configSettingPublisher) =>
            {
                var connectionstring =  RoleEnvironment.GetConfigurationSettingValue (configname);                                                                      
                configSettingPublisher(connectionstring);
            });
            var account = CloudStorageAccount.FromConfigurationSetting                                                               (                                                              ("DataConnectionString");
            var studentcontext = new StudentContext(account.TableEndpoint.ToString(),                                                                    account.Credentials);                                                                   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.


Updated 07-Sep-2019
hi I am software developer at mindstick software pvt. ltd.

Leave Comment

Comments

Liked By