Adding Editor, and Deleting data in Web Forms using ADO.NET

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.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.