Jan 20, 2015

c# BulkInsertDataTable to sql

private bool BulkInsertDataTable(string tableName, DataTable dataTable)
    {
        con.Open();
        SqlTransaction objTrans = con.BeginTransaction();
        try
        {
            //SqlConnection SqlConnectionObj = GetSQLConnection();
            //SqlBulkCopy bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, objTrans);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, objTrans);
            //SqlBulkCopy bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.TableLock, objTrans);
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.NotifyAfter = 10000;

            foreach (DataColumn col in dataTable.Columns)
            {
                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
            }

            bulkCopy.WriteToServer(dataTable);
            bulkCopy.Close();
            objTrans.Commit();
            return true;
        }
        catch (Exception ex)
        {
            objTrans.Rollback();
            throw new Exception(ex.Message);
            //MessageBox.Show(ex.Message);
            //return false;
        }
        finally
        {
            con.Close();
        }
    }

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib