title: Loop through dynamic DataSet and insert Stored Procedure
body: <p>I have a DataTable in C# that I need to insert into a table. The DataTable is completely dynamic (Columns are not pre-defined). I inserted this DataTable row by row using C# code but because it is inefficient I am sending the DataTable bulk into an SQL Stored Procedure. I need the Stored Procedure to loop through the bulk, insert row by row, and return the set of invalid data.</p>
  
  <p><strong>C# code:</strong></p>
  
  <pre><code>SqlConnection sqlConnection = getDBConnection();
  SqlCommand command = sqlConnection.CreateCommand();
  
  command.CommandType = System.Data.CommandType.StoredProcedure;
  command.CommandText = "[dbo].[saveDataIntoTable]";
  
  SqlParameter parameter = new SqlParameter();
  
  //The parameter for the SP must be of SqlDbType.Structured
  parameter.ParameterName = "@Sample";
  parameter.SqlDbType = System.Data.SqlDbType.Structured;
  parameter.Value = dataTable;
  
  command.Parameters.Add(parameter);
  
          foreach (DataRow row in dataTable.Rows)
          {
              System.Diagnostics.Debug.WriteLine(row.ItemArray);
              if (row.ItemArray[0] == null)
              {
                  dataTable.Rows.Remove(row);
              }
          }
  
  SqlDataReader dr = command.ExecuteReader();
  DataTable dt = new DataTable();
  dt.Load(dr);
  
  //handling the dt DataTable here
  </code></pre>
  
  <p><strong>Stored Procedure:</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>USE [DATABASE_NAME]
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE PROCEDURE [dbo].[saveDataIntoTable]
  (
      -- which accepts one table value parameter. It should be noted that   the parameter is readonly
      @Sample As [dbo].[SampleUserData] Readonly
  )
  AS
  BEGIN
  
  BEGIN TRY
  
      Insert  Into    USER(USER_ID,EMAIL,PASSWORD,PASSWORD_HINT,PWD_CHANGED_DATE,
          CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,STATUS,VERSION,VALIDATE)
          Select USER_ID, EMAIL,PASSWORD,PASSWORD_HINT,PWD_CHANGED_DATE,
          CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,STATUS,VERSION,VALIDATE From @Sample
  
  END TRY
  BEGIN CATCH
      Select USER_ID, EMAIL,PASSWORD,PASSWORD_HINT,PWD_CHANGED_DATE,
          CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,STATUS,VERSION,VALIDATE From @Sample 
  END CATCH
  END
  </code></pre>
  
  <p><strong>I used a User-defined Table Type for the Stored Procedure:</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>-- Create a table data type
  CREATE TYPE [dbo].[SampleUserData] As Table
  (
          --This type has structure similar to the DB table           
          USER_ID Nvarchar(20) ,
          EMAIL Nvarchar(50),
          PASSWORD Nvarchar(100),
  
          PASSWORD_HINT Nvarchar(20),
          PWD_CHANGED_DATE date,
          CREATED_BY Nvarchar(20),
  
          CREATED_DATE date,
          UPDATED_BY Nvarchar(20),
          UPDATED_DATE date,
          STATUS Nvarchar(20),
          VERSION Int,
          VALIDATE Nvarchar(10)
  
  );
  </code></pre>
  
  <p>Right now, my Stored Procedure inserts the whole bulk of data at once. When an exception occurs, it returns the whole DataSet (I do not know how to separate the rows).</p>
  
  <p>PS: If there is any other method for the above scenario which is easier, please let me know.</p>
  
  <p>Thanks.</p>