Windows Azure – Data Definition and Manipulation on SQL Azure

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.

One thought on “Windows Azure – Data Definition and Manipulation on SQL Azure

  1. Bobby Jindal says:

    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

Your email address will not be published. Required fields are marked *