Jean Paul's Blog

There are 2 types of People in the World, One who Likes SharePoint and..

    Advertisements
  • Microsoft MVP

  • MindCracker MVP

  • CodeProject MVP

  • eBook on SharePoint 2010

  • eBook on Design Patterns

  • eBook on Windows Azure

  • NLayers Framework @ CodePlex

  • MSDN Forums

  • .Net vs. Java

    Due to Public Demand
  • Advertisements

Windows Azure – Data Definition and Manipulation on SQL Azure

Posted by Paul on November 13, 2011


In the previous article we have explored how to connect to the SQL Azure database through code. In this article we can work on the data definition and manipulation on the SQL Azure database. The idea of the article is to get familiar with the SQL Azure data access code.

The activities include the following:

clip_image002

We can start with the activities one by one:

Create Table

Add the following method for creating the table.

private void CreateTable()

{

SqlCommand command = new SqlCommand();

command.Connection = _connection;

command.CommandText = “CREATE TABLE Employee (Id int primary key, Name

varchar(50))”;

command.ExecuteNonQuery();

}

Insert Rows

Use the following method to insert rows into the table.

private void InsertRows()

{

SqlCommand command = new SqlCommand();

command.Connection = _connection;

for (int i = 1; i <= 10; i++)

{

command.CommandText = string.Format(“INSERT INTO Employee (Id, Name) VALUES({0}, ‘Name{1}’)”, i.ToString(), i.ToString());

command.ExecuteNonQuery();

}

}

Update Rows

Use the following method to update certain rows in the table.

private void UpdateRows()

{

SqlCommand command = new SqlCommand();

command.Connection = _connection;

command.CommandText = “UPDATE Employee SET Name=’NewName’ WHERE Id=5”;

command.ExecuteNonQuery();

}

Delete Rows

Use the following method to delete certain rows into the table.

private void DeleteRows()

{

SqlCommand command = new SqlCommand();

command.Connection = _connection;

command.CommandText = “DELETE FROM Employee WHERE Id > 5”;

command.ExecuteNonQuery();

}

View Rows

Place a DataGrid control on the Default.aspx page and name it as grid. Now add the following method to view the rows.

private void ViewRows()

{

grid.AutoGenerateColumns = false;

grid.Columns.Add(new BoundColumn() { HeaderText = “Id”, DataField = “Id” });

grid.Columns.Add(new BoundColumn() { HeaderText = “Name”, DataField = “Name” });

SqlCommand command = new SqlCommand();

command.Connection = _connection;

command.CommandText = “SELECT * FROM Employee”;

SqlDataReader reader = command.ExecuteReader();

grid.DataSource = reader;

grid.DataBind();

}

On execution of the application with the above methods invoked in the page load event in given order, we can see the following output.

clip_image004

Note: The open connection operation was demonstrated in the previous article so not included here. For the complete working application you can use the source code attached.

Summary

In this article we have explored the table creation, insert, update, delete database operations and selecting of rows on SQL Azure using ADO.NET classes.

Advertisements

One Response to “Windows Azure – Data Definition and Manipulation on SQL Azure”

  1. Bobby Jindal said

    Truly amazing article…..Really thankful to you. It helped me lot. There are some other good articles too helped me in completing my task. You may check this links….
    http://www.mindstick.com/Articles/106add7e-1856-4030-b101-718035954dbb/?Insert,%20Update,%20Delete%20in%20Windows%20Azure%20Application

    http://blogs.msdn.com/b/windowsazure/archive/2012/02/09/windows-azure-isv-blog-series-sociobridge-by-reedrex-windows-azure-isv-blog-series-sociobridge-by-reedrex.aspx

    Thanks Everyone for your precious post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s