How to do SQL Bulk Update using C# programmatically

Hello everyone, in this article we will see how to update SQL Table using C#. I have given all necessary information like connectionString value and SQL table name in App.Config file as show below.

 <connectionStrings>
     <add name="SqlCon" connectionString="server=yourServerName;database=yourDbName;Integrated Security=True;"/>
   </connectionStrings>
 <appSettings>
     <add key="SQL_Table_Name" value="MyTable"/> 
   </appSettings> 
 

Here in this example, I am pulling data from XML file and put it into DataSet to DataTable and then updating SQL table using SQLBulkCopy.

 //Getting values from app.config file
 string conString = ConfigurationManager.ConnectionStrings["SqlCon"].ConnectionString;
 string sqlTable = ConfigurationManager.AppSettings["SQL_Table_Name"];
 string currentDirectory = Directory.GetCurrentDirectory();
 //My XML data is inside the folder called "Message"
 string path = System.IO.Path.Combine(currentDirectory, "Message", "TestData.xml");
 string sampleXML = File.ReadAllText(path);
 using (SqlConnection sqlCon = new SqlConnection(conString))
 {
 sqlCon.Open();
 XmlReader xmlReader = XmlReader.Create(new StringReader(sampleXML));  
 DataSet myDataSet = new DataSet();
 myDataSet.ReadXml(xmlReader);
 //The Table you want to get from DataSet. Since my DS has many tables    
 DataTable dtSQLData = myDataSet.Tables["MainTable"];
 using (SqlBulkCopy bcSQL = new SqlBulkCopy(sqlCon))
 {
 	bcSQL.DestinationTableName = sqlTable;
 //Map Source column name to Destination Column name
 //Src Column is in your DataTable
 //Dest Column is column name available in your SQL Table
 bcSQL.ColumnMappings.Add(”Column1Src”, “Column1Dest”);
 bcSQL.ColumnMappings.Add(”Column2Src”, “Column2Dest”);
 bcSQL.ColumnMappings.Add(”Column3Src”, “Column3Dest”);
 
 bcSQL.WriteToServer(dtSQLData);
 	}
 }
 

Happy Coding

Ahamed