Save complete table to database MS Sql + C#

First of all we need to create a user defined data type in SQL:

CREATE TYPE [dbo].[CustomUserType] AS TABLE(

      [Name] [varchar](250) NULL,

      [Age] [varchar](250) NULL,

      [Email] [varchar](250) NULL,

      [Department] [nvarchar](200) NULL

)

GO

Now create a procedure in SQL to consume variable of above defined data table:

CREATE PROC [dbo].[SaveCustomUserInfo]     

(     

 @UserTable [dbo].CustomUserType READONLY

)     

AS       

BEGIN     

      DECLARE @ErrCode INT     

       

      BEGIN TRAN     

      INSERT INTO UserMaster([Name],[Age],[Email], [Department])

            SELECT [Name],[Age],[Email], [Department] FROM @UserTable  

      SELECT @ErrCode = @@ERROR     

      IF (@ErrCode <> 0) GOTO PROBLEM     

            COMMIT TRAN     

      PROBLEM:     

      IF (@ErrCode <> 0) BEGIN     

            PRINT 'Unexpected error occurred!'     

            ROLLBACK TRAN     

      END     

      SELECT * FROM UserMaster

END     

Now pass table with pre-defined columns to above create procedure:

        public void SaveCustomUserInfo (DataTable UserTable)

        {

            ds = new DataSet();

            try

            {

                OpenConn();

                strSql = "SaveCustomUserInfo";

                SqlParameter[] parm = new SqlParameter[1];

                parm[0] = new SqlParameter("@UserTable", UserTable);

                ds = SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, strSql, parm);

            }

            catch (Exception Ex)

            {

                throw Ex;

            }

            finally { CloseConn(); }

        }

Use following code to create data table and pass to above mentioned function:

    DataTable dt = new DataTable();

    dt.Columns.Add("Name");

    dt.Columns.Add("Age");

    dt.Columns.Add("Email");

    dt.Columns.Add("Department");

    DataRow dr = dt.NewRow();

    dr["Name"] = "Nitin";

    dr["Age"] = "30";

    dr["Email"] = "nitindhiman@gmail.com";

    dr["Department"] = ".net";

    dt.Rows.Add(dr);

    SaveServiceNowAccountDetail(dt);