Windows Azure - Data Definition and Manipulation in 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:



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.



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.