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.