This article has been
excerpted from book "A Programmer's Guide to ADO.NET in C#".
In this example, I'll show you various database operations such as adding;
updating and deleting data form a database using ADO.NET. I've used a Microsoft
Access 2000 database in this application to add, edit, and delete data; however,
working with other data sources such as SQL server is similar to Microsoft
Access. The only difference is creating data adapters objects.
Creating the Application
This application is web applicat6ion developed in Visual C# (see Figure 7-52).
It displays the contents of the Employees table of the Northwind database. You
can set the color, fonts, headers, and footers of a DataGrid by using its
properties window at design-time as well as at run time.
Besides the Grid Control, the page has three buttons, three text boxes, and
three labels. The Add button adds a record to the database, the Edit button
updates a record, and the Delete button deletes a record from the database.
Figure 7-52. Adding, editing, and deleting in an ASP.NET application
Creating the Data Source
Similar to the previous samples, I'll use the same Northwind database in this
application. Listing 7-17 shows the code for the FillDataGrid method. As you can
see, you create a data adapter and dataset and them fill the dataset using the
Fill method of the data adapter. After that you set the DataSource property of
the data grid and call the DataGrid.DataBind method.
Listing 7-17. The FillDataGrid method to fill datagrid
private void
FillDataGrid()
{
// Creating a
connection
OleDbConnection conn = new
OleDbConnection();
conn.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
" Data Source = C:/Northwind.mdb";
string sql =
"SELECT EmployeeID,FirstName,LastName,Title FROM
Employees";
conn.Open();
// Creating a data
adapter
OleDbDataAdapter da = new
OleDbDataAdapter(sql, conn);
// Creating a
DataSet Object
DataSet ds =
new DataSet();
// Fill DataSet with
the data
da.Fill(ds, "Employees");
DataGrid1.DataSource = ds.Tables["Employees"].DefaultView;
DataGrid1.DataBind();
conn.Close();
}
Executing SQL Queries
You can use the OleDbCommand or SqlCommand object's Execute and ExecuteNonQuery
methods to execute SQL queries. If you want to add, update, or delete data from
a database, executing SQL queries is one of the easiest ways to do so. It's fast
and requires a minimum of code to write.
You use the OleDbCommand object to execute a SQL command. The Execute and
ExecuteNonQuery methods of OleDbCommand execute a SQL query. The
OleDbCommand.Execute method executes the CommandText property and returns data
in the OleDbDataReader object. As you can see from listing 7-18, the
CreateMyOleDbComamnd method executes a SQL statement.
Listing 7-18. Calling the Execute method to execute a SQL statement
public void
CreateMyOleDbComamnd(string sqlQuery,
string myCon)
{
OleDbCommand cmd = new
OleDbCommand(sqlQuery, AdoCon);
OleDbCommand myCom = (oleDbcommand)cmd.Clone();
OleDbDataAdapter reader;
myCom.ActiveConnection.open();
myCom.Execute(out reader);
}
Listing 7-19 shows how to construct OleDbCommand and set the
OleDbCommand.Command text property for performing a SELECT from the database's
Employees table.
Listing 7-19.The CommandText property of the command object
OleDbCommand cmd = new
OleDbCommand();
cmd.CommandText="SELECT * FROM
Employees";
The OleDbCommand.ExecuteNonQuery method executes CommandText and doesn't return
any data. The logical time to use this method is when you're writing to the
database or executing SQL statements that don't return any data (See Listing
7-20).
Listing 7-20. Calling the ExecuteNonQuery method
public void
CreateMyOleDbCommand(string sqlQuery,
string AdoCon)
{
OleDbCommand cmd = new
OleDbCommand(sqlQuery, AdoCon);
cmd.ActiveConnection.Open();
cmd.ExecuteNonQuery();
}
Note: Listing 7-21 shows the complete code for using the ExecuteNonQuery
method to execute SQL statements.
In this example, I used the OleDbCommand.ExecuteNonQuery() method to execute the
INSERT, UPDATE and DELETE SQL queries because I don't need to return any data.
My ExecuteSQL method the execution of a SQL query (see Listing 7-21).
Listing 7-21. The ExecuteSQL method executes a SQL statement using the
ExecuteNonQuery method
public bool
ExecuteSQL(string strSQL)
{
// Creating a
connection
OleDbConnection conn = new
OleDbConnection();
conn.ConnectionString = "Provider =
Microsoft.Jet.OLEDB.4.0;" +
"Data Source =C:/Northwind.mdb";
OleDbCommand myCmd = new
OleDbCommand(strSQL, conn);
try
{
conn.Open();
myCmd.ExecuteNonQuery();
}
catch (Exception
exp)
{
Console.WriteLine("SQL
Query Failed! {0}", exp.Message);
return
false;
}
finally
{
// clean up here
conn.Close();
}
return true;
}
Adding Data
The Add button adds a new record to the database and calls the FillDataGrid
method, which rebinds the data source and fills the data grid control with the
updated data. Because the ID column of the database table is auto Number, you
don't have to enter it. You only need to enter the first name and last name.
Listing 7-22 shows the Add button click event handler. As you can see, you
create an INSERT SQL statement, call ExecuteSQL, and refill the data using the
FillDataGrid method.
Listing 7-22. Add button click event handler
//Add button click event
handler
private void
Button1_Click(object sender, System.EventArgs
e)
{
// Build a SQL
statement
string SQL =
"INSERT INTO Employee(FirstName,LastName)" +
"VALUES ('" + TextBox2.Text.ToString() +
"', '" + TextBox3.Text.ToString() +
" ') ";
//Execute SQL and
refresh the data grid
ExecuteSQL(SQL);
FillDataGrid();
}
In figure 7-53, I add a new record with the first name as "Amy" and the last
name as "Sue"
Figure 7-53. Adding a new record in the Web application
EditingData
The editing button updates a record corresponding to an ID. This is where you
build an UPDATE, SET SQL statement and execute it by calling the ExecuteSQL
method, as shown in Listing 7-23.
Listing 7-23 updating data on the edit button click
// edit Button Click
event handler
private void
Button2_Click(object sender, System.EventArgs
e)
{
//Build a SQL
statement
string SQL =
"UPDATE Employees SET FirstName = '" +
TextBox2.Text + "',LastName='" +
TextBox3.Text
+ "' WHERE EmployeeID=" +
TextBox1.Text;
//execute SQL and
refresh the data grid
ExecuteSQL(SQL);
FillDataGrid();
}
Now to test the code, I type Mel in the First Name box, Tel in the Last Name
box, and 10 in the ID box. Then I click the edit button. The result updates the
row with ID = 10 and the output looks like Figure 7-54. As you can see, that
record is updated as Mel Tel.
Figure 7-54. Editing records in a Web application
Deleting data
The delete button deletes a record corresponding with the ID from the database.
I then build a DELETE SQL statement and execute it by calling the ExecuteSQL
method, as shown in Listing 7-24.
Listing 7-24. Deleting data on the Delete button click
//delete button click
event handler
private void
Button3_Click(object sender, System.EventArgs
e)
{
// Build a SQL
statement
string SQL =
"DELETE * FROM Employees" +
"WHERE EmployeeID= " +
TextBox1.Text;
//execute SQL and
refresh the data grid
ExecuteSQL(SQL);
FillDataGrid();
}
To avoid the newly added record, just enter 10 in the ID field or the ID of the
record you want to delete and click the Delete button.
Conclusion
Hope this article would have helped you in understanding
Adding Editor, and Deleting data in Web Forms using ADO.NET. See other articles on
the website also for further reference.
![adobook.jpg]()
|
This essential guide
to Microsoft's ADO.NET overviews C#, then leads you toward deeper
understanding of ADO.NET. |