How to update the Database while performing CRUD Operation

1 min read

You can update the changes to the database that are done in Grid using the following events.

DataSourceControlRowAdding – To add a new Row
DataSourceControlRowUpdating - To Update an existing Row
DataSourceControlRowDeleting – To delete the Current Row

You can use C# or VB code to perform CRUD operations with Grid as follows. Here, SQL query is used to update the database. You can perform update, insert and delete operation in the Grid. While performing these operations, the corresponding event is invoked. In that event SQL query is used to update the database.

Steps to update the database:

Step - 1 Declaring Events

The events for performing CRUD operation are declared. Refer the following code example in C# and VB to declare the events.

this.GridGroupingControl1.DataSourceControlRowAdding += new GridDataSourceControlRowAddingEventHandler(this.RowAdding);
this.GridGroupingControl1.DataSourceControlRowUpdating += new GridDataSourceControlRowUpdateEventHandler(this.RowUpdating);        
this.GridGroupingControl1.DataSourceControlRowDeleting += new GridDataSourceControlRowDeleteEventHandler(this.RowDeleting);

AddHandler Me.GridGroupingControl1.DataSourceControlRowAdding, AddressOf RowAdding
AddHandler Me.GridGroupingControl1.DataSourceControlRowUpdating, AddressOf RowUpdating    
AddHandler Me.GridGroupingControl1.DataSourceControlRowDeleting, AddressOf RowDeleting

Step-2 Define an Event

SQL queries are used to update the database that is used inside these events. In the RowAdding event, insert query is used to add a new record to the database. Update query is used in RowUpdate event to update the existing record in the database. In the RowDeleting event, Delete query is used to remove the selected record from the database.

Please refer the following C# and VB code example to update the changed data in the database.

//Connection String of the database which needs to be update
string conn = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\NORTHWND.mdf";
 
private void RowAdding(object sender, 
GridDataSourceControlRowAddingEventArgs e){
      myConnection = new SqlConnection(conn);
      myConnection.Open();
      //Use insert query to add a new row to the database
      SqlCommand cmdres = new SqlCommand("insert into Detail values('" + 
           e.NewValues[0] + "','" + e.NewValues[1] + "','" + e.NewValues[2] +   
           "')", myConnection);
      cmdres.ExecuteNonQuery();
      myConnection.Close();
      e.Cancel = true;
      e.Handled = true;
}
 
private void RowDeleting(object sender, GridDataSourceControlRowDeleteEventArgs e){
      myConnection = new SqlConnection(Conn);
      myConnection.Open();
      //Use Delete query to delete a Current row from the database
      SqlCommand cmdres = new SqlCommand("delete from Detail where Id = '" + 
           e.Values[0] + "' ", myConnection);
      cmdres.ExecuteNonQuery();
      myConnection.Close();
      e.Cancel = true;
      e.Handled = true;
}
 
private void RowUpdating(object sender, GridDataSourceControlRowUpdateEventArgs e){
      myConnection = new SqlConnection(Conn);
      myConnection.Open();
     //Use Update query to update existing row in the database
      SqlCommand cmdres = new SqlCommand("update Detail set Name = '" + 
         e.NewValues[1] + "', Designation = '" + e.NewValues[2] + "' where Id  
         = '" + e.NewValues[0] + "' ", myConnection);
      cmdres.ExecuteNonQuery();
      myConnection.Close();
      e.Cancel = true;
      e.Handled = true;
}

 
//Connection String of the database which needs to be update
Dim Conn As String = "Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\NORTHWND.mdf"
 
Private Sub RowAdding(sender As Object, e As GridDataSourceControlRowAddingEventArgs)
     myConnection = New SqlConnection(Conn)
     myConnection.Open()
     //Use insert query to add a new row to the database
     Dim cmdres As New SqlCommand("insert into Detail values('" +  e.NewValues(0) + "','" + e.NewValues(1) + "','" + e.NewValues(2) + "')", myConnection)
     cmdres.ExecuteNonQuery()
     myConnection.Close()
     e.Cancel = True
     e.Handled = True
End Sub   
 
Private Sub RowUpdating(sender As Object, e As GridDataSourceControlRowUpdateEventArgs)
     myConnection = New SqlConnection(Conn)
     myConnection.Open()
     //Use Delete query to delete a Current row from the database
     Dim res As New SqlCommand("update Detail set Name = '" + e.NewValues(1) + "', Designation = '" + e.NewValues(2) + "' where Id = '" + e.NewValues(0) + "' ", myConnection)
     res.ExecuteNonQuery()
     myConnection.Close()
     e.Cancel = True
     e.Handled = True
End Sub  
 
Private Sub RowDeleting(sender As Object, e As GridDataSourceControlRowDeleteEventArgs)
     myConnection = New SqlConnection(Conn)
     myConnection.Open()
     //Use Update query to update existing row in the database
     Dim res As New SqlCommand("delete from Detail where Id = '" + e.Values(0) + "' ", myConnection)
     res.ExecuteNonQuery()
     myConnection.Close()
     e.Cancel = True
     e.Handled = True
End Sub

Did you find this information helpful?

Yes

Comments (0)

How to update the Database while performing CRUD Operation

Access denied