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.
Now we will convert this DataTable into an XML formatted string and will pass that XML file to server. Lets see how?
Now its time to send that string to database to make entry. We will use a stored procedure to do this.
Execute the procedure and save it into your database. And now write the code to execute the stored procedure.
Now execute all this code all along. Run the project and check your database after executing the application.
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, 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 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 Email
|
|||
1 comments :
sp is not working please verify it (0) rows effected
Note: Only a member of this blog may post a comment.