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

For each event on the page the form will not be interacting with the database.
This reduces server round trips.

All necessary operations such as:

Inserting a new record

Updating an existing record

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.

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.

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.

It is easier to perform all operations on a set of rows at a time.

The user can deal with thousands of data-rows at the same time without a connection.

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).

Share

About the Author

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.