In this example, I'll show you various database
operations such as adding; updating and deletingdata 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 Sub FillDataGrid()
' Creating a connection
Dim conn As New OleDbConnection()
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & " Data Source = C:/Northwind.mdb"
Dim sql As String = "SELECT EmployeeID,FirstName,LastName,Title FROM Employees"
conn.Open()
' Creating a data adapter
Dim da As New OleDbDataAdapter(sql, conn)
' Creating a DataSet Object
Dim ds As New DataSet()
' Fill DataSet with the data
da.Fill(ds,"Employees")
DataGrid1.DataSource = ds.Tables("Employees").DefaultView
DataGrid1.DataBind()
conn.Close()
End Sub
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 Sub CreateMyOleDbComamnd(ByVal sqlQueryAs String,ByVal myCon As String)
Dim cmd As New OleDbCommand(sqlQuery, AdoCon)
Dim myCom As OleDbCommand = DirectCast(cmd.Clone(), oleDbcommand)
Dim reader As OleDbDataAdapter
myCom.ActiveConnection.open()
myCom.Execute(reader)
End Sub
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
Dim cmd As 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 Sub CreateMyOleDbCommand(ByVal sqlQueryAs String,ByVal AdoCon As String)
Dim cmd As New OleDbCommand(sqlQuery, AdoCon)
cmd.ActiveConnection.Open()
cmd.ExecuteNonQuery()
End Sub
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 Function ExecuteSQL(ByVal strSQLAs String)As Boolean
' Creating a connection
Dim conn As New OleDbConnection()
conn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source =C:/Northwind.mdb"
Dim myCmd As New OleDbCommand(strSQL, conn)
Try
conn.Open()
myCmd.ExecuteNonQuery()
Catch exp As Exception
Console.WriteLine("SQL Query Failed! {0}", exp.Message)
Return False
Finally
' clean up here
conn.Close()
End Try
Return True
End Function
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 Sub Button1_Click(ByVal senderAs Object,ByVal e As System.EventArgs)
' Build a SQL statement
Dim SQL As String = (("INSERT INTO Employee(FirstName,LastName)" & "VALUES ('") + TextBox2.Text.ToString() & "', '") + TextBox3.Text.ToString() & " ') "
'Execute SQL and refresh the data grid
ExecuteSQL(Sql)
FillDataGrid()
End Sub
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 Sub Button2_Click(ByVal senderAs Object,ByVal e As System.EventArgs)
'Build a SQL statement
Dim SQL As String = (("UPDATE Employees SET FirstName = '" & TextBox2.Text &"',LastName='") + TextBox3.Text &"' WHERE EmployeeID=") + TextBox1.Text
'execute SQL and refresh the data grid
ExecuteSQL(Sql)
FillDataGrid()
End Sub
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 Sub Button3_Click(ByVal senderAs Object,ByVal e As System.EventArgs)
' Build a SQL statement
Dim SQL As String = ("DELETE * FROM Employees" & "WHERE EmployeeID= ") + TextBox1.Text
'execute SQL and refresh the data grid
ExecuteSQL(Sql)
FillDataGrid()
End Sub
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