Articles in this section
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

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied