Transferring Data Using SqlBulkCopy in C#

Requirement of SqlBulkCopy :

In our “Project” we have many modules (Library, Registration etc.) where we have to upload heavy data in the database through Excel Sheet. By the help of a SqlBulkCopy Class.

By this technique we achieve our goal in simple three steps :

Step1: Fetch Excel Sheet Data by Linq into Datatable.

Step2: Call Bulk Upload Function by pass required age. The data is uploaded in respective  TempTable of SQL

Step3: Fetch the proper data from Temp Table to Respective Original Table.

//Here by using Linq we are fetching Excel data and store in DataTable for bulk Upload

 public System.Data.DataTable fnGetExcelValue(string path)

    {

        string pathToExcelFile = path;

        string sheetName = "Sheet1";

        var excelFile = new ExcelQueryFactory(pathToExcelFile);

        var artistAlbums = from cat in excelFile.Worksheet(sheetName)

                           select cat;

        System.Data.DataTable dt = new System.Data.DataTable();

        dt.Columns.Add("UNIVERSITY_NAME", typeof(string), null);

        dt.Columns.Add("COLLEGE_NAME", typeof(string), null);

        dt.Columns.Add("DEPT_ID", typeof(string));

        dt.Columns.Add("DEGREE", typeof(string));

        dt.Columns.Add("SPECIALIZATION", typeof(string));

        dt.Columns.Add("ADMISSION_YEAR", typeof(string));

        dt.Columns.Add("UNIVERSITY_ENROLL_NO", typeof(string));

        dt.Columns.Add("COLLEGE_ENROLL_NO", typeof(string));

        dt.Columns.Add("FIRST_NAME", typeof(string));

        dt.Columns.Add("MIDDLE_NAME", typeof(string));

        dt.Columns.Add("LAST_NAME", typeof(string));

        dt.Columns.Add("DATE_OF_BIRTH", typeof(string), null);

        dt.Columns.Add("GENDER", typeof(string), null);

        dt.Columns.Add("MOBILE", typeof(string));

        dt.Columns.Add("EMAIL_ID", typeof(string));

        dt.Columns.Add("FATHER_FIRST_NAME", typeof(string));

        dt.Columns.Add("FATHER_LAST_NAME", typeof(string));

        dt.Columns.Add("FATHER_MOBILE", typeof(string));

        dt.Columns.Add("FATHER_EMAIL", typeof(string));

        dt.Columns.Add("LATERAL_ADMISSION", typeof(string));

        dt.Columns.Add("CURRENT_YEAR", typeof(string));

        dt.Columns.Add("CURRENT_SEM", typeof(string));

        dt.Columns.Add("CURRENT_SECTION", typeof(string));

      

        foreach (var item in artistAlbums)

        {

            dt.Rows.Add(item["UNIVERSITY_NAME"].ToString().Trim(),

                        item["COLLEGE_NAME"].ToString().Trim(),

                        item["DEPT_ID"].ToString().Trim(),

                        item["DEGREE"].ToString().Trim(),

                        item["SPECIALIZATION"].ToString().Trim(),

                        item["ADMISSION_YEAR"].ToString().Trim(),

                        item["UNIVERSITY_ENROLL_NO"].ToString().Trim(),

                        item["COLLEGE_ENROLL_NO"].ToString().Trim(),

                        item["FIRST_NAME"].ToString().Trim(),

                        item["MIDDLE_NAME"].ToString().Trim(),

                        item["LAST_NAME"].ToString().Trim(),

                        item["DATE_OF_BIRTH"].ToString().Trim(),

                        item["GENDER"].ToString().Trim(),

                        item["MOBILE"].ToString().Trim(),

                        item["EMAIL_ID"].ToString().Trim(),

                        item["FATHER_FIRST_NAME"].ToString().Trim(),

                        item["FATHER_LAST_NAME"].ToString().Trim(),

                        item["FATHER_MOBILE"].ToString().Trim(),

                        item["FATHER_EMAIL"].ToString().Trim(),

                        item["LATERAL_ADMISSION"].ToString().Trim(),

                        item["CURRENT_YEAR"].ToString().Trim(),

                        item["CURRENT_SEM"].ToString().Trim(),

                        item["CURRENT_SECTION"].ToString().Trim());

        }

        return fnconvertdate(dt);

    }

//Here we call “fnBulkCopyUploadDataTable” Function and pass three arg. Datatable, Sql Table Name and BatchSize

//'STUDENT_REGISTRATION_EXCEL_TEMP' is a table name in which we insert the records.

int _result = _dl.fnBulkCopyUploadDataTable(dt1, "STUDENT_REGISTRATION_EXCEL_TEMP", 5000);

//Definition of  “fnBulkCopyUploadDataTable” function.

public int fnBulkCopyUploadDataTable(DataTable sourceData, string tableName, int batchSize)

    {

        string connectionString = ConfigurationManager.ConnectionStrings["EdcitedConnectionString"].ConnectionString;

        // open the destination data

        using (SqlConnection destinationConnection = new SqlConnection(connectionString))

        {

            // open the connection

            destinationConnection.Open();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString, SqlBulkCopyOptions.TableLock))

            {

                bulkCopy.BatchSize = batchSize;

                for (int i = 0; i < sourceData.Columns.Count; i++)

                {

                    bulkCopy.ColumnMappings.Add(sourceData.Columns[i].ColumnName, sourceData.Columns[i].ColumnName);

                }

                bulkCopy.DestinationTableName = tableName;

                bulkCopy.WriteToServer(sourceData);

            }

            destinationConnection.Close();

        }

        return sourceData.Rows.Count;

    }