Introduction

This is a small article explaining send an entire data-Table to a Stored Procedure at the database server as input parameter.

Background

It uses a logic which allows us to add, modify and delete operations on a set of records together in the database using a simple form. The details also need to be shown after adding, updation and deletion of records in a table. And the data-table should be given to a stored procedure to perform the operations.

Advantages

1. For each event on the page the form will not be interacting with database. It reduces server round trip.
2. All necessary operation such as:
1. Inserting a new record
2. Updating an existing record
3. Deleting an existing record

Is performed on the client side only. It will be persisted in the database only after getting confirmation from the user. So, the performance of the application will be fast.

1. After performing the certain action (such as Insert, Update or Delete) on the grid-view the data will be saved in “Session”. And whenever the data will be required that can be fetched through that session.
2. We send whole data-table of the session to the database for manipulation, and there with the use of Stored Procedure we can do operations such as insert, delete or update.
3. It is easier to perform all the operations on the set of rows at a time.
4. User can deal with thousands of data-rows at a time without connection.
5. Extends the functionality of programming in database engine.

Using the code

When page is getting loaded first time, data from database is loaded to a data-table. In that data-table a column is added (Named “Operation”). Whenever user will perform any action on grid-view, the indicator bit is added to that column for that record. (For Insert- 0, Update-1, and Delete-2). If user clicks on delete link on a particular row then the operation column of that row is updated to 2, and in “RowDataBound” event of grid-view we are hiding that records whose operation is 2.  The temporary table is stored in session state. Before performing any operation, that session table will be called and the action will be performed on that table and again that table will stored in session. After performing all the necessary operation when user clicks on save button then a function will be called named “fnMangeOperations”, which will filter all rows on which the operation was performed and the save button will send only those details on which operation is to be performed.

Step 1: Declaring global variables:

private clsEmpDetails _objEmpDetails;
private DataTable _dtEmpDetails;
private DataTable _dtEmpDetailsVals;

Step 2: Initializing the data members when page loads.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        _objEmpDetails = new clsEmpDetails();
        _dtEmpDetails = _objEmpDetails.fnGetDetails();

        //Adding a new column to the table which will store the operation details
        //For new insert it will store 1
        //For updating the existing record it will store 0
        _dtEmpDetails.Columns.Add("Operation", typeof(string));
        Session["EmpDetails"] = _dtEmpDetails;
        gvEmpDetails.DataSource = _dtEmpDetails;
        gvEmpDetails.DataBind();
     }
}

Just store the data in session and maintain the indicator for each operation. The operation will be like this:

protected void gvEmpDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName == "AddNew")
    {
        _dtEmpDetails = new DataTable();
        _dtEmpDetails = Session["EmpDetails"] as DataTable;
        string strID = ((TextBox)gvEmpDetails.FooterRow.Cells[0].FindControl("txtInsID")).Text;
        string strName = ((TextBox)gvEmpDetails.FooterRow.Cells[1].FindControl("txtInsName")).Text;
        string strAddress = ((TextBox)gvEmpDetails.FooterRow.Cells[1].FindControl("txtInsAddress")).Text;
        _dtEmpDetails.Rows.Add(strID, strName, strAddress, "0");
        Session["EmpDetails"] = _dtEmpDetails;
        fnBindEmpDetails(); //This function will bind the data to the gridview by fetching the data from session
    }
}

On click of save button call the following function by passing parameter as your Datatable.

public string fnStoredProc(DataTable dtDetails)
{
    string strMsg = "";
    try
    {
        fnConOpen();//Function for opening connection
        SqlCommand cmdProc = new SqlCommand("spEmpDetails", con);
        cmdProc.CommandType = CommandType.StoredProcedure;
        cmdProc.Parameters.AddWithValue("@Type", "InsertDetails");
        cmdProc.Parameters.AddWithValue("@Details", dtDetails);
        cmdProc.ExecuteNonQuery();
        strMsg = "Saved successfully.";
    }
    catch (SqlException e) {
        //strMsg = "Data not saved successfully.";
        strMsg = e.Message.ToString();
    }
    finally
    {
        fnConClose();//Function for closing connection

    }
    return strMsg;
}

Procedure Structure

First create a table type with matching columns which is coming from front-end. In this case:

/*Creating type for the procedure parameter*/
CREATE TYPE EmpType AS TABLE
(
    ID INT, Name VARCHAR(3000), Address VARCHAR(8000), Operation SMALLINT
)

Write the procedure for the operations.

ALTER PROCEDURE spEmpDetails
@Type VARCHAR(15),
@Details EmpType READONLY
AS
BEGIN
    IF(@Type='FetchDetails')
        SELECT * FROM EmployeeDetails
    ELSE
        BEGIN

            --For deleting the details from the table
            DELETE FROM EmployeeDetails WHERE ID IN(SELECT ID FROM @Details WHERE Operation=2)

            --For updating the details in the table
            UPDATE e SET e.Name=d.Name, e.Address=d.Address FROM EmployeeDetails e, @Details d
            WHERE d.ID=e.ID and d.Operation=1

            --For inserting the new records in the table
            INSERT INTO EmployeeDetails(ID, Name, Address)
            SELECT ID, Name, Address FROM @Details WHERE Operation=0;                      

        END
END
GO

Note: This procedure takes datatabe as its parameter and performs insert, update, and delete operations based on the table data.

End Point

Thank you for reading this article. Any suggestions will be appreciated.

For more information and sample code visit my CodeProject Profile.