Friday, 2 September 2011

Bulk insert in sql using C#.Net ?

// Create table in sql
            CREATE TABLE Student(
          StuId int,
          StuName nvarchar(50),
          StuEmail nvarchar(100),
          StuAdd nvarchar(100),
          StuMobile nvarchar(50))
// Implement in C#.Net
using System.IO;
using System.Data.SqlClient;
using System.Configuration;

 private void bulkInser_InSql()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id");
            dt.Columns.Add("Name");
            dt.Columns.Add("Email");
            dt.Columns.Add("Add");
            dt.Columns.Add("Mobile");

            dt.Rows.Add(1, "Amit", "Amit@yahoo.com", "Jodhpur", "98979843534");
            dt.Rows.Add(2, "Ashok", "Ashok@yahoo.com", "Jaipur", "097895674745");
            dt.Rows.Add(3, "Vijay", "Vijay@yahoo.com", "Udaipur", "96784565654");
            dt.Rows.Add(4, "Dinesh", "Dinesh@yahoo.com", "Raj", "978657657");
            dt.Rows.Add(5, "Hunny", "Hunny@yahoo.com", "Jodhana", "84556756456");

            using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString()))
            {
                cn.Open();
                using (SqlBulkCopy copy = new SqlBulkCopy(cn))
                {
                    copy.ColumnMappings.Add(0, 0);
                    copy.ColumnMappings.Add(1, 1);
                    copy.ColumnMappings.Add(2, 2);
                    copy.ColumnMappings.Add(3, 3);
                    copy.ColumnMappings.Add(4, 4);

                    copy.DestinationTableName = "Student";
                    copy.WriteToServer(dt);
                }
            }
        }

SqlBulkCopy uses ADO.NET to connect to a database to bulk load the data. I have created an SqlConnection object, and that object reference is used to create the SqlBulkCopy object. The DestinationTableName property references a table in the database where the data is to be loaded. A handy feature of SqlBulkCopy is the SqlBulkCopyColumnMappingCollection. Column mappings define the relationships between columns in the data source and columns in the destination. This is handy if the data source file has columns that don’t need to be inserted into the database. Column mappings can be set by an index, such as the example above, or they can be set by the name of the column. Using the index is handy when you’re working with files that contain no column names. Finally the data is sent to the database by running the WriteToServer method.

No comments:

Post a Comment