Inserting DataTable contents to SQL Server using StoredProcedures

This post is about one of the solution I suggested for my friend in our User Group.

The context of this solution is given below.

1. There are few tables (for now let it be 5 tables)

2. For inserting data to these tables we have individual tables with standard naming convention(“usp_TABLENAME_Insert”).

3. Need to have a single method that will build parameters and sql command accordingly based on the Table Name or name of the input DataTable. We will pass a “DataTable” contains records to insert in to the specific method,  method will figure out which Stored Procedure to call(based on DataTable.TableName) and will prepare parameter set based on the column names in the “DataTable”.

So the code I put in together is given below. Not perfected much. Might still need improvement. Thought some body out there might be looking for something similar and useful for any one. 🙂  Go through it, criticisms are welcomed. 🙂

        /// <summary>
        /// Inserts the data set to tables.
        /// </summary>
        /// <param name="dsInput">The ds input.</param>
        /// <returns></returns>
        public bool InsertDataSetToTables(DataSet dsInput)
        {
            bool isSuccess = true;

            if(dsInput != null && dsInput.Tables.Count > 0)
            {
                foreach (DataTable dt in dsInput.Tables)
                {
                    bool tableInsertSuccess = false;
                    tableInsertSuccess = InsertDataToTables(dt);
                }
            }

            return isSuccess;
        }


        /// <summary>
        /// Inserts the data to tables.
        /// </summary>
        /// <param name="dtInput">The dt input.</param>
        /// <returns></returns>
        public bool InsertDataToTables(DataTable dtInput)
        {
            bool isSuccess = false;


            if(dtInput != null && dtInput.Rows.Count > 0)
            {

                SqlConnection sqlConnection = new SqlConnection("<YOUR_CONN_STRING_HERE>");
                SqlTransaction trans = sqlConnection.BeginTransaction();

                try
                {

                    int totalRecords = dtInput.Rows.Count;
                    int insertedRecordsCount = 0;

                    //Assumes TableName indicates the "TargetTable" for insert and "Stored procedure" 
                    //will have same set of parameters matching the columns in the datatable.
                    string sp_Name = GetSpName(dtInput.TableName);

                    SqlCommand insertCommand = BuildCommandandParameters(sqlConnection, sp_Name, dtInput.Columns);

                    insertCommand.Prepare(); //prepared statement - single command and parameters for multiple record insert.

                    for (int iRowIndex = 0; iRowIndex < dtInput.Rows.Count; iRowIndex++)
                    {

                        for (int iColIndex = 0; iColIndex < dtInput.Columns.Count; iColIndex++)
                        {
                            if (dtInput.Rows[iRowIndex][iColIndex] != null)
                                insertCommand.Parameters[iColIndex].Value = dtInput.Rows[iRowIndex][iColIndex];
                            else
                                insertCommand.Parameters[iColIndex].Value = DBNull.Value;
                        }

                        int rowAffected = insertCommand.ExecuteNonQuery();

                        if(rowAffected > 0)
                        {
                            insertedRecordsCount++;
                        }
                    }


                    if(totalRecords == insertedRecordsCount)
                    {

                        trans.Commit();
                        isSuccess = true;
                    }
                    else
                    {
                        trans.Rollback();
                    }

                }
                catch(SqlException)
                {
                    if (sqlConnection.State == ConnectionState.Open)
                        trans.Rollback();
                }
                catch(Exception ex)
                {
                   
                }
                finally
                {
                    if (sqlConnection.State == ConnectionState.Open)
                        sqlConnection.Close();
                }
            }

            return isSuccess;
        }

        /// <summary>
        /// Builds the commandand parameters.
        /// </summary>
        /// <param name="sqlConnection">The SQL connection.</param>
        /// <param name="sp_Name">Name of the SP_.</param>
        /// <param name="columns">The columns.</param>
        /// <returns></returns>
        private SqlCommand BuildCommandandParameters(SqlConnection sqlConnection,string sp_Name, DataColumnCollection columns)
        {
             SqlCommand insertCommand = new SqlCommand(sp_Name,sqlConnection);

             for (int iColIndex = 0; iColIndex < columns.Count; iColIndex++)
             {
                 DataColumn dataColumn = columns[iColIndex]; //just safe side getting a copy
                 insertCommand.Parameters.Add(new SqlParameter("@" + dataColumn.ColumnName.Trim(), dataColumn.DataType));
             }

             return insertCommand;
        }

        /// <summary>
        /// Gets the name of the sp.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <returns></returns>
        private string GetSpName(string tableName)
        {
            string spName = string.Empty;

            //Use conditional Switch  or just formulate sp_name like this . your choice.
            // spName = "usp_"+ tableName +"_Insert";   

            switch (tableName)
            {
                case "Table1" :
                      //Init Stored procedure name here
                      //implement your own logic. 
                      //either create all the sps in the same naming convention like below etc.
                      spName = "usp_"+ tableName +"_Insert";  
                    break;

                case "Table2" :

                    break;

                case "Table3":

                    break;

                case "Table4":

                    break;

                case "Table5":

                    break;
            }

            return spName;
        }