Click here to Skip to main content
15,867,686 members
Articles / Web Development / ASP.NET

Sending a DataTable to a Stored Procedure

Rate me:
Please Sign up or sign in to vote.
4.75/5 (51 votes)
10 Jul 2012CPOL3 min read 329.8K   3.4K   104   59
Sending a DataTable to a Stored Procedure using Table Valued Parameters.

Introduction

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

Background 

It uses a logic which allows us to do add, modify, and delete operations on a set of records in the database using a simple form. The details also need to be shown after adding, updating, and deleting of records in a table. And the data-table should be given to a stored procedure to perform the operations. This feature will work with SQL Server 2008.

Advantages  

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

    are performed on client side only. They will be persisted in the database only after getting confirmation from the user. So, the performance of the application will improve.

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

Using the code

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

Step 1: Declaring global variables:  

C#
private clsEmpDetails _objEmpDetails;
private DataTable _dtEmpDetails;
private DataTable _dtEmpDetailsVals;

Step 2: Initializing the data members when page loads.

C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        _objEmpDetails = new clsEmpDetails();
        _dtEmpDetails = _objEmpDetails.fnGetDetails();
        if(_dtEmpDetails != null){
           //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:

C#
protected void gvEmpDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName == "AddNew") 
    {
       try{
            _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
        }
        catch(Exception ex){            
             //Handle your exception
        }
    }
}

On click of the Save button, call the following function by passing your DataTable as a parameter.

C#
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 comes from the front-end. In this case:

SQL
/*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.

SQL
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 a 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Malaysia Malaysia
I've been working with various Microsoft Technologies. I have earned my Microsoft Certified Technology Specialist (MCTS) certification. I'm a highly motivated self-starter with an attitude for learning new skills and utilizing that in my work.


--Amit Kumar
You can reach me at:
Facebook | Linkedin | Twitter | Google+

Comments and Discussions

 
GeneralNice posts Pin
Hakik Paci17-Mar-20 23:35
Hakik Paci17-Mar-20 23:35 
QuestionNice Article : I have a Question Pin
Renuka Garg Phatak22-Feb-18 9:24
Renuka Garg Phatak22-Feb-18 9:24 
GeneralMy vote of 5 Pin
khurram ali lashari15-May-15 7:40
professionalkhurram ali lashari15-May-15 7:40 
GeneralAnother great way to pass datatable to SP Pin
crazie.coder2-Jul-13 1:24
professionalcrazie.coder2-Jul-13 1:24 
QuestionNot an article Pin
bbirajdar3-Apr-13 21:23
bbirajdar3-Apr-13 21:23 
GeneralMy vote of 5 Pin
S. M. Ahasan Habib18-Feb-13 18:18
professionalS. M. Ahasan Habib18-Feb-13 18:18 
GeneralRe: My vote of 5 Pin
_Amy18-Feb-13 18:38
professional_Amy18-Feb-13 18:38 
Questioncreate type of table is necessary? Pin
Aarti Meswania10-Feb-13 18:25
Aarti Meswania10-Feb-13 18:25 
AnswerRe: create type of table is necessary? Pin
_Amy10-Feb-13 18:50
professional_Amy10-Feb-13 18:50 
GeneralRe: create type of table is necessary? Pin
Aarti Meswania10-Feb-13 18:59
Aarti Meswania10-Feb-13 18:59 
AnswerRe: create type of table is necessary? Pin
_Amy10-Feb-13 19:04
professional_Amy10-Feb-13 19:04 
GeneralMy vote of 1 Pin
Frantisek Ruzicka8-Jan-13 5:21
professionalFrantisek Ruzicka8-Jan-13 5:21 
QuestionRe: My vote of 1 Pin
_Amy8-Jan-13 17:06
professional_Amy8-Jan-13 17:06 
AnswerRe: My vote of 1 Pin
Frantisek Ruzicka9-Jan-13 3:10
professionalFrantisek Ruzicka9-Jan-13 3:10 
GeneralRe: My vote of 1 Pin
GregoryW7-Apr-13 19:38
GregoryW7-Apr-13 19:38 
GeneralMy vote of 5 Pin
Savalia Manoj M27-Dec-12 17:15
Savalia Manoj M27-Dec-12 17:15 
GeneralRe: My vote of 5 Pin
_Amy27-Dec-12 17:19
professional_Amy27-Dec-12 17:19 
GeneralMy vote of 3 Pin
HaBiX2-Oct-12 23:39
HaBiX2-Oct-12 23:39 
GeneralRe: My vote of 3 Pin
_Amy3-Oct-12 0:07
professional_Amy3-Oct-12 0:07 
GeneralRe: My vote of 3 Pin
HaBiX3-Oct-12 0:47
HaBiX3-Oct-12 0:47 
GeneralMy vote of 5 Pin
Prasad_Kulkarni19-Aug-12 19:10
Prasad_Kulkarni19-Aug-12 19:10 
GeneralRe: My vote of 5 Pin
_Amy19-Aug-12 20:02
professional_Amy19-Aug-12 20:02 
GeneralMy vote of 5 Pin
pradiprenushe23-Jul-12 23:41
professionalpradiprenushe23-Jul-12 23:41 
GeneralRe: My vote of 5 Pin
_Amy23-Jul-12 23:59
professional_Amy23-Jul-12 23:59 
GeneralMy vote of 5 Pin
blackSpood17-Jul-12 22:32
blackSpood17-Jul-12 22:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.