Efficient Use of Grid Data When Saving In DB

Introduction

A data grid is a fundamental element for every developer. We have to save records from Grid to our Backend SQL Server Table object.

Here in this article we are trying to save the records from Data Grid to our Table object in efficient way. That is the entire data grid records all in together. Hope it will be interesting.

What we find in some development

Suppose we have data grid like this

Student Roll	Student Name	Student Class
1	Joydeep Das	1
2	Shipra Roy Chowdhury	1
3	Deepasree Das	1

To save the records in the Database we have a Stored procedure like this

CREATE PROCEDURE [dbo].[proc_SaveRecord]

         (

               @p_idRoll        INT,

               @p_cStdName      VARCHAR(50),

               @p_iStdClass     INT

         )

   AS

   BEGIN

      ......

       .....

   END

This type of common practice stored procedure have parameters for all the columns in the table and the developer pass the value from grid to the parameters one by one by some sort of looping and save the data in the database.

Bad practice as the frontend frequently communicates with backend and the cost is so high. This type of practice make or increasing BLOACKING and the result we find the TIME OUT in case of Web Application.

What the Suggested Method to do that

We can pass the entire data table into our stored procedure and save the records in our database table

Example of Best Practice

Step – 1 [ Create the Base Table to Save Records from Grid ]

CREATE TABLE tbl_Student

   (

      idRoll    INT         NOT NULL IDENTITY PRIMARY KEY,

      cStdName  VARCHAR(50) NOT NULL,

      iStdClass INT         NOT NULL

  );

Step – 2 [ Create the Stored Procedure to Accept XML String ]

CREATE PROCEDURE [dbo].[proc_SaveRecord]

         (

             @p_GridData   XML

         )

   AS

BEGIN

     DECLARE @Handle AS INT;

      CREATE TABLE #tmpStudent

           (

                  idRoll     INT           NOT NULL,

                  cStdName   VARCHAR(50)   NOT NULL,

                  iStdClass  INT           NOT NULL

           )

      EXEC sp_xml_preparedocument

            @Handle OUTPUT,

             @p_GridData;

     INSERT INTO #tmpStudent

     SELECT *

     FROM   OPENXML (@Handle, '/Dataset/tbl_Student', 1)

            WITH

            (

              idRoll       INT,

              cStdName     VARCHAR(50),

              iStdClass    INT

            );

     EXEC sp_xml_removedocument @Handle;

     -- Actual Save --

     INSERT INTO tbl_Student

            (cStdName, iStdClass)

     SELECT cStdName, iStdClass

     FROM   #tmpStudent;

END

Step – 3 [ Now we have to Concert Grid Data into XML String ]

<Dataset>

<tbl_Student idRoll="1" cStdName="Joydeep Das" iStdClass="1" />

<tbl_Student idRoll="2" cStdName="Shipra Roy Chowdhury" iStdClass="1" />

<tbl_Student idRoll="3" cStdName="Deepasree Das" iStdClass="1" />

</Dataset>

Step-4 [ Calling the Stored Procedure with XML ]

BEGIN

     DECLARE @p_GridData AS XML;

     SET @p_GridData =

'<Dataset>

<tbl_Student idRoll="1" cStdName="Joydeep Das" iStdClass="1" />

<tbl_Student idRoll="2" cStdName="Shipra Roy Chowdhury" iStdClass="1" />

<tbl_Student idRoll="3" cStdName="Deepasree Das" iStdClass="1" />

</Dataset>

';

      

      EXEC [dbo].[proc_SaveRecord]

           @p_GridData = @p_GridData;

END

Step-5 [ Observe the Output ]

SELECT * FROM tbl_Student;

idRoll  cStdName                              iStdClass

1          Joydeep Das                           1

2          Shipra Roy Chowdhury           1

3          Deepasree Das                       1

There are other alternate way for that. If have you nay other best way to Save grid records in a table, please share your knowledge with us.

Hope you like it.

Posted by: JOYDEEP DAS