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.