Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

Jan 24, 2015
Introduction:

Today in this post I will show you how to copy multiple data into database table at a time. I am sure you have wrote single INSERT statement to insert a single row to database. Or you have used a for loop or a foreach loop to run multiple insert query. But apart form all these in this post I will show you how to insert data into database table in bulk at a time.


Description:

Previously we have seen How to Import data from EXCEL to SQL Database in ASP.NET, C#- Import/Upload Data from Excel to SQL Server 2008 in Asp.net using C#, VB.Net, Pass XML as Parameter from C# to SQL Stored Procedure in VB.NET. And now we will learn how to entry bulk data into database. So what are you waiting for? Start a new project and a new database.

Before proceeding just create the database first. Name it as you like and create a table,

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

Again named the table as you want. Now its time for coding. 

Create a new web page and add a button. We will write code against the button click.  Here I am showing you with a DataTable. According to your requirement you put data into the DataTable and proceed.




DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Address");
dt.Columns.Add("Phone");

dt.Rows.Add("Arkadeep""Kolkata""123456890");
dt.Rows.Add("Saikat""Chennai""99999999");

dt.Rows.Add("Sucheta""Delhi""9876543210");


Now we will convert this DataTable into an XML formatted string and will pass that XML file to server. Lets see how?



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, trueMissingSchemaAction.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 its time to send that string to database to make entry. We will use a stored procedure to do this.



CREATE PROCEDURE sp_InsertData
(@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 SampleData ([Name], [Address], [Phone])
            (SELECT [Name] , [Address],[Phone]FROM @stagingTable)
     
      EXEC sp_xml_removedocument @xmlHandle
END

Execute the procedure and save it into your database. And now write the code to execute the stored procedure.


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

Now execute all this code all along. Run the project and check your database after executing the application.


Arkadeep De

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

1 comments :

sandeep said...

sp is not working please verify it (0) rows effected

Give your Valuable Comments

Other Related Posts

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.