Gautam Buddha Temple Varanasi,Varanasi - Buddhist Pilgrimage

Saturday, August 24, 2013

Using SqlBulkCopy to perform bulk copy operations from one database to other database/SqlBulkCopy Class in C#.

 SqlBulkCopy is a class that was added to the .NET Framework version 2.0. It provides feature likeimport data from one database to other Microsoft SQL Server database.By using this you can get data from one database manupulate data then bulk copy that data into other database programmatically.  
 public int ImportData(string connectionString)  
     {   
       SqlConnection connection = null;  
       SqlCommand cmd = null;  
       string fname = string.Empty;  
       string lname = string.Empty;  
       string addr1 = string.Empty;  
       string addr2 = string.Empty;  
       string city = string.Empty;  
       string state = string.Empty;  
       string zip = string.Empty;  
       try  
       {  
         DataTable myDataTable = GetCreatedTable();           
         connection = new SqlConnection(connectionString);  
         DataSet Ds = new DataSet();          
         connection.Open();  
         string sqlcmd = "SELECT * from tablename";  
         cmd = new SqlCommand(sqlcmd, connection);  
         cmd.CommandType = CommandType.Text;  
         SqlDataAdapter da = new SqlDataAdapter(cmd);  
         da.Fill(Ds);  
         if (Ds.Tables[0].Rows.Count > 0)  
         {  
           foreach (DataRow dr in Ds.Tables[0].Rows)  
           {  
             DataRow drData = myDataTable.NewRow();  
             fname = Convert.ToString(dr["fname"]);  
             lname = Convert.ToString(dr["lname"]);  
             addr1 = Convert.ToString(dr["addr1"]);  
             addr2 = Convert.ToString(dr["addr2"]);  
             city = Convert.ToString(dr["city"]);  
             state = Convert.ToString(dr["state"]);  
             zip = Convert.ToString(dr["zip"]);  
             drData["FirstName"] = fname;  
             drData["LastName"] = lname;  
             drData["Add1"] = addr1;  
             drData["Addr2"] = addr2;  
             drData["City"] = city;  
             drData["State"] = state;  
             drData["Zip"] = zip;  
             myDataTable.Rows.Add(drData);            
           }  
           string connString = ConfigurationManager.AppSettings["DestConnString"].ToString();  
           using (SqlConnection dbConnection = new SqlConnection(connString))  
           {  
             dbConnection.Open();             
             using (SqlBulkCopy bulkCopy = new SqlBulkCopy(dbConnection))  
             {  
               bulkCopy.ColumnMappings.Add("FirstName", "FirstName");  
               bulkCopy.ColumnMappings.Add("LastName", "LastName");  
               bulkCopy.ColumnMappings.Add("Add1", "Add1");  
               bulkCopy.ColumnMappings.Add("Addr2", "Addr2");  
               bulkCopy.ColumnMappings.Add("City", "City");  
               bulkCopy.ColumnMappings.Add("State", "State");  
               bulkCopy.ColumnMappings.Add("Zip", "Zip");  
               bulkCopy.BatchSize = 100;  
               bulkCopy.BulkCopyTimeout = 100;  
               bulkCopy.DestinationTableName = "EmpTable";  
               bulkCopy.WriteToServer(myDataTable.CreateDataReader());                
             }  
           }  
         }  
         else  
         {  
                       //show message for no record found  
           "No record found to transfer data in empdata Database.";  
         }  
       }        
       catch (Exception ex)  
       {  
         throw ex;  
       }  
       finally  
       {  
         connection.Close();  
       }        
       return 1;  
     }  
 public DataTable GetCreatedTable()  
     {  
       DataTable empData = new DataTable("EMPData");  
       DataColumn dcFirstName = new DataColumn();  
       dcFirstName.DataType = Type.GetType("System.String");  
       dcFirstName.ColumnName = "FirstName";  
       DataColumn dcLastName = new DataColumn();  
       dcLastName.DataType = Type.GetType("System.String");  
       dcLastName.ColumnName = "LastName";  
       DataColumn dcAdd1= new DataColumn();  
       dcAdd1.DataType = Type.GetType("System.String");  
       dcAdd1.ColumnName = "Add1";  
       DataColumn dcAdd2 = new DataColumn();  
       dcAdd2.DataType = Type.GetType("System.String");  
       dcAdd2.ColumnName = "Add2";  
       DataColumn dcCity = new DataColumn();  
       dcCity.DataType = Type.GetType("System.String");  
       dcCity.ColumnName = "City";  
       DataColumn dcState = new DataColumn();  
       dcState.DataType = Type.GetType("System.String");  
       dcState.ColumnName = "State";  
       DataColumn dcZip = new DataColumn();  
       dcZip.DataType = Type.GetType("System.String");  
       dcZip.ColumnName = "Zip";  
       empData.Columns.Add(dcFirstName);  
       empData.Columns.Add(dcLastName);  
       empData.Columns.Add(dcAdd1);  
       empData.Columns.Add(dcAdd2);  
       empData.Columns.Add(dcCity);  
       empData.Columns.Add(dcState);  
       empData.Columns.Add(dcZip);  
       return empData;  
     }  

No comments:

Post a Comment