A Stored Procedure With A DataTable
Sign in

A Stored Procedure with a DataTable

Project Leader
A article explaining send an data-table to a Stored Procedure at the sql server as input parameter.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
For each event on the page the form will not be interacting with database. It reduces server round trip.All necessary operation such as:
Inserting a new record
Updating an existing record
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.


After performing the certain action (such as Insert, Update or Delete) on the gridview the data will be saved in “Session”. And whenever the data will be required that can be fetched through that session.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.It is easier to perform all the operations on the set of rows at a time.User can deal with thousands of data-rows at a time without connection.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. 

 

Code of c# : 

public string CallStoredProc(DataTable dtEmpDetails)

{   

string strMsg = "";   

try   

{       

ConOpen();//Function for opening connection       

SqlCommand cmdProc = new SqlCommand("spEmpDetails", con);       

cmdProc.CommandType = CommandType.StoredProcedure;       

cmdProc.Parameters.AddWithValue("@Type", "InsertDetails");       

cmdProc.Parameters.AddWithValue("@Details", dtEmpDetails);       

cmdProc.ExecuteNonQuery();       

strMsg = "Saved successfully.";   

}   

catch (SqlException e) 

{        throw ex;    }   

finally    

{ ConOpen(); }   

return strMsg; 

}


Procedure Structure


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

 

CREATE TYPE EmpType AS TABLE (ID INT, Name VARCHAR(3000), Address VARCHAR(8000), Operation SMALLINT)

SP for the operations.

ALTER PROCEDURE spEmpDetails

@Type VARCHAR(15),

@Details EmpTypeREADONLY

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

 

Thanks.

start_blog_img