Tuesday, May 6, 2014

Pass DataTable to SQL Server Stored Procedure using C# ADO.Net

Database:

//Create new type (i.e. Table data type) as input parameter to be used in ADO.Net SQL Command

CREATE TYPE DtFile as Table
(
    Id int
)

//Create Procedure and use the above created type in it.

Create Proc dbo.UspInsertFileData
(
@FileId dbo.DtFile READONLY
)
AS
Begin
        INSERT INTO MainFileData (FileId, AddedOn)
        SELECT Id, GETDATE() From dbo.DtFile
End

C# Asp.Net:

Data Access Layer:


public static int InsertFileData(DataTable dtFileIds)
{
         var sqlConnectionString = ConfigurationManager.AppSettings["fileConnection"];
         var sqlParameters = new SqlParameter[1];
         sqlParameters[0] = new SqlParameter
                                   {
                                       ParameterName = @"FileId",
                                       SqlDbType = SqlDbType.Structured,
                                       Value = fileTreeIds,
                                       TypeName = @"DtFile"
                                   };
          var result = SqlHelper.ExecuteScalar(sqlConnectionString,  CommandType.StoredProcedure,  @"UspInsertFileData" , sqlParameters);          return Convert.ToInt32(result);
}

Presentation Layer:

protected void BtnClickAddFileData(object sender, EventArgs e)
{
      var fileIds = new[] {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15};
      var dtFileData = new DataTable();
      if (fileIds.Length > 0)
      {
                /* Make Sure to keep the name of the data column same as given in the SQL User-Defined  type */
                dtFileData.Columns.Add(new DataColumn(@"Id", typeof(int)));
                foreach (var id in fileIds)
                {
                           var dr = dtFileData.NewRow();
                          dr["FileTreeId"] = item;
                          dtFileData.Rows.Add(dr);
                          dtFileData.AcceptChanges();
                }
       }
       var result = Dal.InsertFileData(dtFileData);
}



No comments:

Post a Comment