System.Data.SqlClient.SqlBulkCopy - Bulk Update in DB from asp.net
The Bulk update is used to send a raw data into DB. The below structure shows the structure to send data from .Net ot DB.

    /// <summary>
    /// Update the Bulk Collection details into DB.
    /// </summary>
    /// <param name="objTable">Input DatatTable</param>
    public void BulkEntryUpdate(DataTable objTable)
    {
        using (System.Data.SqlClient.SqlConnection objCon =
                                                CommonBehavior.GetMainConnection())
        {
            objCon.Open();
            using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new 
                System.Data.SqlClient.SqlBulkCopy(objCon))
            {
                bulkCopy.DestinationTableName = "DB Table Name";
                                 //Ex : dbo.T_TransactionTable
                // Write from the source to the destination.
                bulkCopy.WriteToServer(objTable);
            }
        }

    }

Exameple :

In Codebehind, First we need to generate the source data table as per our needs.For example,


using (System.IO.StreamReader objReader = new System.IO.StreamReader(sFilename.Trim()))
{
InputLine = objReader.ReadLine();
string sUpdateMsg = string.Empty;

DataTable dtAdjData = new DataTable();
DataColumn dc;
DataRow dr;


dc = new DataColumn();
dc.DataType = System.Type.GetType("System.Int32");
dc.ColumnName = "LocationID";
//dc.Unique = false;
dtAdjData.Columns.Add(dc);

dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "System";
dtAdjData.Columns.Add(dc);

dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "Customer";
dtAdjData.Columns.Add(dc);

dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "Project";
dtAdjData.Columns.Add(dc);

dc = new DataColumn();
dc.DataType = System.Type.GetType("System.Int32");
dc.ColumnName = "FileID";
dtAdjData.Columns.Add(dc);

while ((InputLine = objReader.ReadLine()) != null)
{
//Do your checking some coditions here…
try
{
string[] ProcessDate = arrAdj[6].Trim().Split('/');
dr = dtAdjData.NewRow();
dr["LocationID"] = Convert.ToInt32(Locationid);
dr["System"] = arrAdj[7].Trim();
dr["Customer"] = arrAdj[3].Trim();
dr["Project"] = arrAdj[4].Trim();
dr["FileID"] = Convert.ToInt32(_fileid);
dtAdjData.Rows.Add(dr);
}
catch (Exception exErrorLog)
{
objErrorList.Add("Error Record No: " + (Successcnt + ErrorCount) + ". Error:" + exErrorLog.Message);
sUploadMsg.Text = "Uploaded Successfully!. Found wrong entries, view log file.";
}

}


Then we will map our generated Table columns with Database table columns and directly update the bulk data into DB.

using(SqlConnection objCon = new SqlConnection(CommonBehavior.GetMainConnectionString()))
{

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(objCon))
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("LocationID", "LocationID"));
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("System", "System"));
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Customer", "Customer"));
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Project", "Project"));
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("FileID", "FileID"));

bulkCopy.DestinationTableName = "dbo.[T_AdjData]";
bulkCopy.WriteToServer(dtAdjData);
System.Threading.Thread.Sleep(1000);
}
}


Suppose the table contains any Identity Column, no need to map that column, just leave that and mapping the remaining columns, the above case will works fine.


 
08:13 | Category: | 0 comments

Comments (0)

Post a Comment