To copy data from DataTable or DataSet to SQL Server, we need to use either a foreach or a for loop. It is OK when the count of rows is reasonable. But if the data is like 1 lakh or 1 crore, then it is not possible to round the loop for that much time. Then what do we do? A simple method to follow is to use XML. I am sure you all are more or less aware of this XML. It's like a database with tags. Or, it's better to say it's a database. It keeps data in a file with an extension of ".xml".
Our intention is to convert the DataTable's data into an XML file and send it to the server, where with the help of a stored procedure, we will extract data and insert into the database table. So let's come and see how to do this...
First of all, create a new database and create a new table, name it as you want. Database table structure will be like this.
- Name NVarChar(255) not null
- Address NVarChar(255) not null
- Phone NvarChar(12) not null
OK, now, we need to create a new DataTable and put some data in it. Make sure your column and the database table's column are the same.
- 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("Rahul", "Delhi", "9876543210");
Now, you have to convert this DataTable into XML. To do this, copy and paste the following code after the DataTable section.
- 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;
- }
- }
Call this method to convert the DataTable to XML.
- String xmlData = ConvertToXML(dt);
Now, pass the value to the stored procedure in the following way.
- SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["connection"].ToString());
- SqlCommand command = new SqlCommand("sp_InsertData '" + xmlData + "'", conn);
- conn.Open();
- command.ExecuteNonQuery();
- conn.Close();
Now, let's check the stored procedure sp_InsertData.
- 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 INTOSampleData ([Name], [Address], [Phone])
- (SELECT [Name] , [Address],[Phone]FROM @stagingTable)
-
- EXEC sp_xml_removedocument @xmlHandle
- END