Dismiss Notice

Register now to be one of the first members of this SharePoint Community! Click here it just takes seconds!

Dismiss Notice
Welcome Guest from Country Flag

How to do SQL Bulk Update using C# programmatically

Discussion in 'SharePoint Blogs' started by ahamed-fazil-buhari,, Sep 12, 2017.

Thread Status:
Not open for further replies.
  1. ahamed-fazil-buhari,

    ahamed-fazil-buhari, Guest

    Blog Posts:
    0
    0
    0
    0
    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

    Continue reading...
     
Thread Status:
Not open for further replies.

Share This Page

LiveZilla Live Chat Software