Thursday, 19 February 2015

SQL Bulk copy from DataTable in ASP.NET, C#

SQL Bulk copy from DataTable in ASP.NET, C#:

Create Table "EmployeeInfo":

Name NVarChar(255) not null
Adderss NVarChar(255) not null
Phone NVarChar(12) not null

Create Store Procedure "InsertData_Employee_SP":

CREATE PROCEDURE InsertData_Employee_SP
(
     @xmlString VARCHAR(MAX)
)
AS
BEGIN

      DECLARE @xmlHandle INT
      DECLARE @stagingTable TABLE
      (
         [Name]               VARCHAR(50),
         [Address]            VARCHAR(50),
         [Phone]              VARCHAR(50)
      )
             
      EXEC sp_xml_preparedocument @xmlHandle output, @xmlString

      INSERT INTO @stagingTable
      SELECT  [Name],
                  [Address],
                  [Phone]   
      FROM  OPENXML (@xmlHandle, '/DataTable',1)
                        WITH (
                               [Name]  varchar(50)   '@Name',
                                [Address]   varchar(50)   '@Address',
                                [Phone]      varchar(50)    '@Phone'
                               )

      INSERT INTO EmployeeInfo([Name], [Address], [Phone])
            (SELECT [Name] , [Address],[Phone]FROM @stagingTable)
    
      EXEC sp_xml_removedocument @xmlHandle
END

Code in C#:
Sample Data  Table 

DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Address");
dt.Columns.Add("Phone");
dt.Rows.Add("Sujoy Santra", "Kolkata", "123456890");
dt.Rows.Add("Sanjoy Roy", "Kolkata", "99999900");
dt.Rows.Add("Dilip Pal", "Mumbai", "9876543210");

Convert data table to XML:
 private static string ConvertToXML(DataTable dt)
{
      DataSet dsBuildSQL = new DataSet();
      StringBuilder sbSQL;
      StringWriter swSQL;
      string XMLformat;
      try
      {
           sbSQL = new StringBuilder();
           swSQL = new StringWriter(sbSQL);
           dsBuildSQL.Merge(dt, true, MissingSchemaAction.AddWithKey);
           dsBuildSQL.Tables[0].TableName = "DataTable";
           foreach (DataColumn col in dsBuildSQL.Tables[0].Columns)
           {
               col.ColumnMapping = MappingType.Attribute;
           }
           dsBuildSQL.WriteXml(swSQL, XmlWriteMode.WriteSchema);
           XMLformat = sbSQL.ToString();
           return XMLformat;
       }
       catch (Exception sysException)
       {
           throw sysException;
       }
}

Now Insert the data table to database:

SqlConnection conn = newSqlConnection(WebConfigurationManager.ConnectionStrings["connection"].ToString());
SqlCommand command = new SqlCommand("InsertData_Employee_SP  '" + xmlData + "'", conn);
conn.Open();
command.ExecuteNonQuery();
conn.Close();