Category / Section
How to persist data in the SQL Server on server-side events?
1 min read
Solution
You can perform SQL Server CRUD operations in the code behind by using Grid server-side edit events with SQL Stored Procedures. The Grid has separate server side events for each edit action.
Initialize the Grid with SQLDataSource ID and server-side edit events in the view page.
ASPX
<ej:Grid ID="SQLDataGrid" runat="server" AllowPaging="True" AllowSorting="True" DataSourceID="SqlData" OnServerAddRow="SQLDataGrid_ServerAddRow" OnServerEditRow="SQLDataGrid_ServerEditRow" OnServerDeleteRow="SQLDataGrid_ServerDeleteRow"> <Columns> <ej:Column Field="SupplierID" HeaderText="Supplier ID" IsPrimaryKey="True" TextAlign="Right" Width="75" /> <ej:Column Field="CompanyName" HeaderText="Company Name" Width="80" /> <ej:Column Field="City" HeaderText="City" Width="75" /> <ej:Column Field="PostalCode" HeaderText="Postal Code" TextAlign="Right" Width="90" /> <ej:Column Field="Country" HeaderText="Country" Width="80"> </ej:Column> </Columns> <EditSettings AllowEditing="True" AllowAdding="True" AllowDeleting="True"></EditSettings> <ToolbarSettings ShowToolbar="True" ToolbarItems="add,edit,delete,update,cancel"></ToolbarSettings> </ej:Grid> <asp:SqlDataSource runat="server" ID="SqlData" ConnectionString='<%$ ConnectionStrings:SQLConnectionString %>' SelectCommand="SELECT * FROM [Suppliers]"> </asp:SqlDataSource>
Perform the SQL Server CRUD operation for each edit function based on its event type from the code behind. The GridEventArgs has the edited record with event type.
CS
static string cons = ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString; static SqlConnection con = new SqlConnection(cons); protected void Page_Load(object sender, EventArgs e) { } protected void SQLDataGrid_ServerAddRow(object sender, Syncfusion.JavaScript.Web.GridEventArgs e) { ExecuteToSQL("SQLInsert", e.EventType, e.Arguments["data"]); // SQLInsert stored procedure } protected void SQLDataGrid_ServerEditRow(object sender, Syncfusion.JavaScript.Web.GridEventArgs e) { ExecuteToSQL("SQLUpdate", e.EventType, e.Arguments["data"]); // SQLUpdate stored procedure } protected void SQLDataGrid_ServerDeleteRow(object sender, Syncfusion.JavaScript.Web.GridEventArgs e) { ExecuteToSQL("SQLDelete", e.EventType, e.Arguments["data"]); // SQLDelete stored procedure } public static void ExecuteToSQL(string commandText, string eventType, object record) { Dictionary<string, object> KeyVal = record as Dictionary<string, object>; var Order = KeyVal.Values.ToArray(); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = commandText; // Stored procedure for editing actions sqlCommand.CommandType = CommandType.StoredProcedure; if (eventType == "endDelete") { // Pass parameter to SQLRemove stored procedure sqlCommand.Parameters.Add(new SqlParameter("@SupplierID", Order[0])); } else if (eventType == "endAdd" || eventType == "endEdit") { // Pass parameter to SQLInsert and SQLUpdate stored procedures sqlCommand.Parameters.Add(new SqlParameter("@SupplierID", Order[0])); sqlCommand.Parameters.Add(new SqlParameter("@CompanyName", Order[1])); sqlCommand.Parameters.Add(new SqlParameter("@City", Order[2])); sqlCommand.Parameters.Add(new SqlParameter("@PostalCode", Order[3])); sqlCommand.Parameters.Add(new SqlParameter("@Country", Order[4])); } sqlCommand.Connection = con; if (con.State != ConnectionState.Open) con.Open(); sqlCommand.ExecuteNonQuery(); con.Close(); }
SQL Stored Procedures for each edit action.
SQLInsert: CREATE PROCEDURE [dbo].[SQLInsert] ( @SupplierID int, @CompanyName nvarchar(40), @City nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15) ) AS SET NOCOUNT OFF; INSERT INTO Suppliers (SupplierID, CompanyName, City, PostalCode, Country) VALUES (@SupplierID, @CompanyName, @City, @PostalCode, @Country) SQLUpdate: CREATE PROCEDURE [dbo].[SQLUpdate] ( @SupplierID int, @CompanyName nvarchar(40), @City nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15) ) AS SET NOCOUNT OFF; UPDATE Suppliers SET CompanyName = @CompanyName, City = @City, PostalCode = @PostalCode, Country = @Country WHERE SupplierID = @SupplierID; SQLDelete: CREATE PROCEDURE [dbo].[SQLDelete] (@SupplierID int) AS SET NOCOUNT OFF; DELETE FROM Suppliers WHERE SupplierID = @SupplierID