Database Level Editing
In numerous cases you may want to perform data editing operations at database level with Update/Insert/Delete commands from the code-behind. This can be done wiring the UpdateCommand/InsertCommand/DeleteCommand events of RadGrid and executing appropriate Update/Insert/Delete queries which refresh the data in the underlying database. Below are the code snippets from a simple example which uses auto-generated edit form:
<telerik:RadGrid RenderMode="Lightweight" ID="RadGrid1" runat="server" Skin="Lime" GridLines="None" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" Width="97%" OnNeedDataSource="RadGrid1_NeedDataSource"
OnDeleteCommand="RadGrid1_DeleteCommand" OnInsertCommand="RadGrid1_InsertCommand"
OnUpdateCommand="RadGrid1_UpdateCommand">
<PagerStyle Mode="NextPrevAndNumeric"></PagerStyle>
<MasterTableView DataKeyNames="EmployeeID" GridLines="None" Width="100%" CommandItemDisplay="Top">
<Columns>
<telerik:GridButtonColumn CommandName="Delete" Text="Delete" UniqueName="Delete">
</telerik:GridButtonColumn>
<telerik:GridBoundColumn DataField="EmployeeID" HeaderText="EmployeeID" UniqueName="EmployeeID"
ReadOnly="True">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="LastName" HeaderText="LastName" UniqueName="LastName">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="FirstName" HeaderText="FirstName" UniqueName="FirstName">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="Title" HeaderText="Title" UniqueName="Title">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="Address" HeaderText="Address" UniqueName="Address">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="City" HeaderText="City" UniqueName="City">
</telerik:GridBoundColumn>
<telerik:GridEditCommandColumn>
</telerik:GridEditCommandColumn>
</Columns>
<EditFormSettings ColumnNumber="2" CaptionFormatString="Edit details for employee with ID {0}"
CaptionDataField="EmployeeID">
<FormTableItemStyle Wrap="False"></FormTableItemStyle>
<FormCaptionStyle CssClass="EditFormHeader"></FormCaptionStyle>
<FormMainTableStyle CellSpacing="0" CellPadding="3" Width="100%" />
<FormTableStyle GridLines="Horizontal" CellSpacing="0" CellPadding="2" CssClass="module"
Height="110px" Width="100%" />
<FormTableAlternatingItemStyle Wrap="False"></FormTableAlternatingItemStyle>
<FormStyle Width="100%" BackColor="#EEF2EA"></FormStyle>
<EditColumn UpdateText="Update record" UniqueName="EditCommandColumn1" CancelText="Cancel edit">
</EditColumn>
<FormTableButtonRowStyle HorizontalAlign="Right" CssClass="EditFormButtonRow"></FormTableButtonRowStyle>
</EditFormSettings>
<ExpandCollapseColumn Visible="False">
<HeaderStyle Width="19px"></HeaderStyle>
</ExpandCollapseColumn>
<RowIndicatorColumn Visible="False">
<HeaderStyle Width="20px" />
</RowIndicatorColumn>
</MasterTableView>
</telerik:RadGrid>
public partial class _Default : System.Web.UI.Page
{
//Declare a global DataTable dtTable
public static DataTable dtTable;
//Get the connectionstring from the webconfig and declare a global SqlConnection "SqlConnection"
public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
public SqlConnection SqlConnection = new SqlConnection(connectionString);
//Declare a global SqlDataAdapter SqlDataAdapter
public SqlDataAdapter SqlDataAdapter = new SqlDataAdapter();
//Declare a global SqlCommand SqlCommand
public SqlCommand SqlCommand = new SqlCommand();
protected void RadGrid1_NeedDataSource(object source, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
{
//Populate the Radgrid
dtTable = new DataTable();
//Open the SqlConnection
SqlConnection.Open();
try
{
//Select Query to populate the RadGrid with data from table Employees.
string selectQuery = "SELECT * FROM Employees";
SqlDataAdapter.SelectCommand = new SqlCommand(selectQuery, SqlConnection);
SqlDataAdapter.Fill(dtTable);
RadGrid1.DataSource = dtTable;
}
finally
{
//Close the SqlConnection
SqlConnection.Close();
}
}
protected void RadGrid1_DeleteCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)
{
//Get the GridDataItem of the RadGrid
GridDataItem item = (GridDataItem)e.Item;
//Get the primary key value using the DataKeyValue.
string EmployeeID = item.OwnerTableView.DataKeyValues[item.ItemIndex]["EmployeeID"].ToString();
try
{
//Open the SqlConnection SqlConnection.Open();
string deleteQuery = "DELETE from Employees where EmployeeID='" + EmployeeID + "'";
SqlCommand.CommandText = deleteQuery;
SqlCommand.Connection = SqlConnection;
SqlCommand.ExecuteNonQuery();
//Close the SqlConnection
SqlConnection.Close();
}
catch (Exception ex)
{
RadGrid1.Controls.Add(new LiteralControl("Unable to delete Employee. Reason: " + ex.Message));
e.Canceled = true;
}
}
protected void RadGrid1_UpdateCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)
{
//Get the GridEditableItem of the RadGrid
GridEditableItem editedItem = e.Item as GridEditableItem;
//Get the primary key value using the DataKeyValue.
string EmployeeID = editedItem.OwnerTableView.DataKeyValues[editedItem.ItemIndex]["EmployeeID"].ToString();
//Access the textbox from the edit form template and store the values in string variables.
string LastName = (editedItem["LastName"].Controls[0] as TextBox).Text;
string FirstName = (editedItem["FirstName"].Controls[0] as TextBox).Text;
string Title = (editedItem["Title"].Controls[0] as TextBox).Text;
string Address = (editedItem["Address"].Controls[0] as TextBox).Text;
string City = (editedItem["City"].Controls[0] as TextBox).Text;
try
{
//Open the SqlConnection
SqlConnection.Open();
//Update Query to update the Datatable
string updateQuery = "UPDATE Employees set LastName='" + LastName + "',FirstName='" + FirstName + "',Title='" + Title + "',Address='" + Address + "',City='" + City + "' where EmployeeID='" + EmployeeID + "'";
SqlCommand.CommandText = updateQuery;
SqlCommand.Connection = SqlConnection;
SqlCommand.ExecuteNonQuery();
//Close the SqlConnection
SqlConnection.Close();
}
catch (Exception ex)
{
RadGrid1.Controls.Add(new LiteralControl("Unable to update Employee. Reason: " + ex.Message));
e.Canceled = true;
}
}
protected void RadGrid1_InsertCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)
{
//Get the GridEditFormInsertItem of the RadGrid
GridEditFormInsertItem insertedItem = (GridEditFormInsertItem)e.Item;
//string EmployeeID = (insertedItem["EmployeeID"].Controls[0] as TextBox).Text;
string LastName = (insertedItem["LastName"].Controls[0] as TextBox).Text;
string FirstName = (insertedItem["FirstName"].Controls[0] as TextBox).Text;
string Title = (insertedItem["Title"].Controls[0] as TextBox).Text;
string Address = (insertedItem["Address"].Controls[0] as TextBox).Text;
string City = (insertedItem["City"].Controls[0] as TextBox).Text;
try
{
//Open the SqlConnection
SqlConnection.Open();
//Update Query to insert into the database
string insertQuery = "INSERT into Employees(LastName,FirstName,Title,Address,City) values('" + LastName + "','" + FirstName + "','" + Title + "','" + Address + "','" + City + "')";
SqlCommand.CommandText = insertQuery;
SqlCommand.Connection = SqlConnection;
SqlCommand.ExecuteNonQuery();
//Close the SqlConnection
SqlConnection.Close();
}
catch (Exception ex)
{
RadGrid1.Controls.Add(new LiteralControl("Unable to insert Employee. Reason: " + ex.Message));
e.Canceled = true;
}
}
}
Public Shared dtTable As DataTable
'Get the connectionstring from the webconfig and declare a global SqlConnection "SqlConnection"
Public Shared connectionString As String = ConfigurationManager.AppSettings("ConnectionString")
Public SqlConnection As New SqlConnection(connectionString)
'Declare a global SqlDataAdapter SqlDataAdapter
Public SqlDataAdapter As New SqlDataAdapter()
'Declare a global SqlCommand SqlCommand
Public SqlCommand As New SqlCommand()
Protected Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs)
'Populate the Radgrid
dtTable = New DataTable()
'Open the SqlConnection
SqlConnection.Open()
Try
'Select Query to populate the RadGrid with data from table Employees.
Dim selectQuery As String = "SELECT * FROM Employees"
SqlDataAdapter.SelectCommand = New SqlCommand(selectQuery, SqlConnection)
SqlDataAdapter.Fill(dtTable)
RadGrid1.DataSource = dtTable
Finally
'Close the SqlConnection
SqlConnection.Close()
End Try
End Sub
Protected Sub RadGrid1_DeleteCommand(ByVal source As Object, ByVal e As Telerik.Web.UI.GridCommandEventArgs)
'Get the GridDataItem of the RadGrid
Dim item As GridDataItem = DirectCast(e.Item, GridDataItem)
'Get the primary key value using the DataKeyValue.
Dim EmployeeID As String = item.OwnerTableView.DataKeyValues(item.ItemIndex)("EmployeeID").ToString()
Try
'Open the SqlConnection
SqlConnection.Open()
Dim deleteQuery As String = "DELETE from Employees where EmployeeID='" & EmployeeID & "'"
SqlCommand.CommandText = deleteQuery
SqlCommand.Connection = SqlConnection
SqlCommand.ExecuteNonQuery()
'Close the SqlConnection
SqlConnection.Close()
Catch ex As Exception
RadGrid1.Controls.Add(New LiteralControl("Unable to delete Employee. Reason: " + ex.Message))
e.Canceled = True
End Try
End Sub
Protected Sub RadGrid1_UpdateCommand(ByVal source As Object, ByVal e As Telerik.Web.UI.GridCommandEventArgs)
'Get the GridEditableItem of the RadGrid
Dim editedItem As GridEditableItem = TryCast(e.Item, GridEditableItem)
'Get the primary key value using the DataKeyValue.
Dim EmployeeID As String = editedItem.OwnerTableView.DataKeyValues(editedItem.ItemIndex)("EmployeeID").ToString()
'Access the textbox from the edit form template and store the values in string variables.
Dim LastName As String = (TryCast(editedItem("LastName").Controls(0), TextBox)).Text
Dim FirstName As String = (TryCast(editedItem("FirstName").Controls(0), TextBox)).Text
Dim Title As String = (TryCast(editedItem("Title").Controls(0), TextBox)).Text
Dim Address As String = (TryCast(editedItem("Address").Controls(0), TextBox)).Text
Dim City As String = (TryCast(editedItem("City").Controls(0), TextBox)).Text
Try
'Open the SqlConnection
SqlConnection.Open()
'Update Query to update the Datatable
Dim updateQuery As String = "UPDATE Employees set LastName='" & LastName & "',FirstName='" & FirstName & "',Title='" & Title & "',Address='" & Address & "',City='" & City & "' where EmployeeID='" & EmployeeID & "'"
SqlCommand.CommandText = updateQuery
SqlCommand.Connection = SqlConnection
SqlCommand.ExecuteNonQuery()
'Close the SqlConnection
SqlConnection.Close()
Catch ex As Exception
RadGrid1.Controls.Add(New LiteralControl("Unable to update Employee. Reason: " + ex.Message))
e.Canceled = True
End Try
End Sub
Protected Sub RadGrid1_InsertCommand(ByVal source As Object, ByVal e As Telerik.Web.UI.GridCommandEventArgs)
'Get the GridEditFormInsertItem of the RadGrid
Dim insertedItem As GridEditFormInsertItem = DirectCast(e.Item, GridEditFormInsertItem)
'string EmployeeID = (insertedItem["EmployeeID"].Controls[0] as TextBox).Text;
Dim LastName As String = (TryCast(insertedItem("LastName").Controls(0), TextBox)).Text
Dim FirstName As String = (TryCast(insertedItem("FirstName").Controls(0), TextBox)).Text
Dim Title As String = (TryCast(insertedItem("Title").Controls(0), TextBox)).Text
Dim Address As String = (TryCast(insertedItem("Address").Controls(0), TextBox)).Text
Dim City As String = (TryCast(insertedItem("City").Controls(0), TextBox)).Text
Try
'Open the SqlConnection
SqlConnection.Open()
'Update Query to insert into the database
Dim insertQuery As String = "INSERT into Employees(LastName,FirstName,Title,Address,City) values('" & LastName & "','" & FirstName & "','" & Title & "','" & Address & "','" & City & "')"
SqlCommand.CommandText = insertQuery
SqlCommand.Connection = SqlConnection
SqlCommand.ExecuteNonQuery()
'Close the SqlConnection
SqlConnection.Close()
Catch ex As Exception
RadGrid1.Controls.Add(New LiteralControl("Unable to insert Employee. Reason: " + ex.Message))
e.Canceled = True
End Try
End Sub
You can also review the forthcoming code library thread for different scenarios:
Manual Update/Insert/Delete with FormTemplate and SQL backend