By Neeraj Saluja | 5 Jun 2008 | Unedited contribution Part of The SQL Zone.
Various ways to do Multiple Inserts in SQL Server 2000/2005 or Oracle Database using ADO.NET in single database round trip.
Download Multiple Insert Sample C# 2003.zip - 294.96 KB
Download Multiple Inserts Sample C# 2005.zip - 41.85 KB
Table of Contents
*
Introduction and scope of the Article
*
Setting up the Environment
*
Use Batch Update of ADO.NET 2.0 DataAdapter Object
*
Use SQLBulkCopy of ADO.NET 2.0
*
Pass Array as Parameter to ODP.NET Command Object
*
Pass the data in XML format to SQL Server Stored Procedure
*
Form a Single SQL Statement and execute it directly from ADO.NET
o
Append Insert Statements
o
Use Select and Union All for Insert
o
Pass the values in a string with ColumnSeperator and/or RowSeperator and pass it to Stored proc in SQL Server. Split it in proc and Insert
*
Other Useful References:
*
Wrapping up
Introduction and Scope of the Article
Very often we come across a scenario where we need to execute same Insert or update commands with different values multiple times in one go. Say for example, show multiple records in updatable grid/tablular format allowing user to update them and then update them in database. Their are multiple ways to handle it and simplest being execute each DML command one after the other. The most resource consuming part of it is establishing connection for each DML command. Well, connection pooling helps a bit, but still one needs to request for connection from the connection pool. For details about connection pooling, refer to the article ADO.NET Connection Pooling at a Glance . Best solution in such a situation would be to establish the connection only once and perform multiple insert/update within it, and this is what is the target of this article. Let us see couple of mechanisms in which we can do this.
Let us start by setting up the Environment.
Setting up the Environment
We are going to use SQL Server 2000/2005 Or Oracle in the examples. So let us create a very simple table Person in the desired database. Let us keep it very simple by having just 2 columns PersonId and PersonName in it. The syntax for the same would be:
CREATE TABLE Person
(
PersonId INT PRIMARY KEY,
PersonName VARCHAR(100)
)
Use Batch Update of ADO.NET 2.0 DataAdapter Object
Valid for Environment: .NET 2.0 (ADO.NET 2.0), SQL Server 2000 or above
It was quite a cumbersome job until ADO.NET 2.0. But with ADO.NET 2.0 things got very simple as Adapter now supports multiple Insert/Update with the user defined batch size. We are going to limit our discussion to Insert functionality. For Insert, create the Command object with usual simple stored proc for Insert and specify that as the InsertCommand to the DataAdapter object. Along with this we need to specify the UpdateBatchSize which determines the number of Inserts to be processed in one network round trip. Follow the code below to have complete understanding.
First of all create the stored proc in your SQL Server Instance:
CREATE PROCEDURE sp_BatchInsert ( @PersonId INT, @PersonName VARCHAR(100) )
AS
BEGIN
INSERT INTO Person VALUES ( @PersonId, @PersonName);
END
Now refer to the C# code below:
private void btnBatchInsert_Click(object sender, EventArgs e)
{
// Get the DataTable with Rows State as RowState.Added
DataTable dtInsertRows = GetDataTable();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("sp_BatchInsert", connection);
command.CommandType = CommandType.StoredProcedure;
command.UpdatedRowSource = UpdateRowSource.None;
// Set the Parameter with appropriate Source Column Name
command.Parameters.Add("@PersonId", SqlDbType.Int, 4, dtInsertRows.Columns[0].ColumnName);
command.Parameters.Add("@PersonName", SqlDbType.VarChar, 100, dtInsertRows.Columns[1].ColumnName);
SqlDataAdapter adpt = new SqlDataAdapter();
adpt.InsertCommand = command;
// Specify the number of records to be Inserted/Updated in one go. Default is 1.
adpt.UpdateBatchSize = 2;
connection.Open();
int recordsInserted = adpt.Update(dtInsertRows);
connection.Close();
MessageBox.Show("Number of records affected : " + recordsInserted.ToString());
}
Well, first thing we all developers do is check using SQL Profiler. And to our surprise it shows 4 different RPC requests sent to SQL Server. This is how it looks there :
Do not Panic. The difference in not in the way your statements are executed at the Server, the difference is in terms of how your request(s) are sent to the server. In simple words, with UpdateBatchSize as 2 ( in this case ), it just means that request for insertion of 2 rows will be grouped together and sent to the database server in single network round trip. You can probably use some other tools like “Netmon” etc to have a closer look. So use the UpdateBatchSize appropriately
The DataAdapter has two update-related events: RowUpdating and RowUpdated. Only one RowUpdated event occurs for each batch, whereas the RowUpdating event occurs for each row processed. You may also like to look at the Exceptional handling part of it. Explore them.
Use SQLBulkCopy of ADO.NET 2.0
Valid for Environment: NET 2.0 or above on SQL Server 2005 database or above
With ADO.NET 2.0 we got the programming interface for Bulk Copy which provides quite simple and straight forward mechanism to transfer the data from one SQL server instance to another, from one table to another, from DataTable to SQL Server 2005 database, from DataReader to SQL Server 2005 database and many more.
SqlBulkCopy belongs to System.Data.SqlClient namespace and it is as simple as ADO.NET Command object when it comes to programming it. Let us see it working:
private void btnSQLBulkCopyInsert_Click(object sender, EventArgs e)
{
// Get the DataTable
DataTable dtInsertRows = GetDataTable();
using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))
{
sbc.DestinationTableName = "Person";
// Number of records to be processed in one go
sbc.BatchSize = 2;
// Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table
sbc.ColumnMappings.Add("PersonId", "PersonId");
sbc.ColumnMappings.Add("PersonName", "PersonName");
// Number of records after which client has to be notified about its status
sbc.NotifyAfter = dtInsertRows.Rows.Count;
// Event that gets fired when NotifyAfter number of records are processed.
sbc.SqlRowsCopied+=new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);
// Finally write to server
sbc.WriteToServer(dtInsertRows);
sbc.Close();
}
}
void sbc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
MessageBox.Show("Number of records affected : " + e.RowsCopied.ToString());
}
The code above is very simple and quite self explanatory.
Key Notes :
1. BatchSize and NotifyAfter are two different properties. Former specify the number of records to be processed in one go while later specifies the number of records to be processed after which client needs to be notified.
Reference:
1. No better place than MSDN. Refer to http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx for details on SqlBulkCopy
2. Refer http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy_properties.aspx to get details on all properties on SqlBulkCopy.
Pass Array as Parameter to ODP.NET Command Object
Valid for Environment: ODP.NET 9.2.0.1 or above with NET 1.1 or above on Oracle 9i Database or above
Oracle has been investing a lot in connectivity between Oracle Database and .NET. Oracle provided ODP.NET (Oracle Provider for .NET) to have connectivity between Oracle Database and .NET. Well, I have not gone into its details but it is believed that it is better to use ODP.NET to connect to Oracle database from .Net environment. May be since both are Oracle products they may have optimized ODP.NET for better performance, may be…
Anyways, let us see what we have got in it for Multiple Inserts in one network roundtrip. ODP.NET provides us OracleCommand object which is quite similar to SQLCommand Object. OracleCommand object supports taking arrays as parameters. The only thing is while using array one needs to provide ArrayBindCount, which informs ODP.NET the number of records to expect and process from the array. Simply put, the code is exactly same as if we are calling a stored proc by providing two simple parameters, just that, rather than providing simple value to a parameter, we need to specify an array of values. And along with that we specify ArrayBindCount same as Array Length, to enable ODP.NET to do multiple inserts. I am sure the code below will help you to understand this better:
Create the simple stored proc sp_InsertByODPNET in oracle database similar to that of sp_BatchInsert in the code above and follow the .NET Code below :
private void btnOracleODPNET_Click(object sender, System.EventArgs e)
{
int[] arrPersonId = {Convert.ToInt32(txtPersonId1.Text.Trim()), Convert.ToInt32(txtPersonId2.Text.Trim()), Convert.ToInt32(txtPersonId3.Text.Trim()), Convert.ToInt32(txtPersonId3.Text.Trim())};
string[] arrPersonName = {txtPersonName1.Text.Trim(), txtPersonName2.Text.Trim(), txtPersonName3.Text.Trim(), txtPersonId4.Text.Trim()};
// You need to put the Namespace "using Oracle.DataAccess.Client;" to compile this code
OracleConnection connection = new OracleConnection(oracleConnectionString);
OracleCommand command = new OracleCommand("sp_InsertByODPNET", connection);
command.CommandType = CommandType.StoredProcedure;
// We need to tell the ODP.NET the number of rows to process
//and that we can do using "ArrayBindCount" property of OracleCommand Object
command.ArrayBindCount = arrPersonId.Length;
// For specifying Oracle Data Types for Parameters you need to use "Oracle.DataAccess.Types" namespace.
command.Parameters.Add("@PersonId", OracleDbType.Int16);
command.Parameters[0].Value = arrPersonId;
command.Parameters.Add("@PersonName", OracleDbType.Varchar2, 100);
command.Parameters[1].Value = arrPersonName;
connection.Open();
int recordsInserted = command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Number of records affected : " + recordsInserted.ToString());
}
Couple of people have raised issues against this approach as they encountered Memory Leak while using ODP.NET along with .NET. But also, I have heard that the issues are resolved with recent version of ODP.NET and .NET and patches. You may like to do your research before adopting this approach.
Key Notes:
1. Use the latest possible version of ODP.NET, as it usually has the bug fixes from all the previous releases.
2. Do not forget to set command.ArrayBindCount.
References:
1. For latest on Oracle’s ODP.NET refer to its details on Oracle site at URL: http://www.oracle.com/technology/tech/windows/odpnet/index.html
Pass the data in XML format to SQL Server Stored Procedure
Valid for Environment: .NET 1.1, .NET 2.0, SQL Server 2000 or above
SQL Server 2000 supports XML. SELECT * FROM table FOR XML AUTO. Syntax sounds familiar, right? Yes, SQL Server 2000 supports XML. It not only supports returning the data in XML format, it also supports reading the XML string and parsing it. Before going to the implementation of Multiple Insert using this approach. To understand it a little bit, copy the code below and execute it in SQL Query Analyzer SQL Window :
DECLARE @intDocHandle int
DECLARE @xmlString varchar(4000)
SET @xmlString ='
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @xmlString
SELECT * FROM OPENXML(@intDocHandle,
'/root/person')
WITH
( PersonId INT,
PersonName VARCHAR(100)
)
-- Remove the internal representation.
exec sp_xml_removedocument @intDocHandle
I am leaving further interpretation and understanding part up to you. It is quite easy to observe that it revolves around two important stored procs : sp_xml_preparedocument, sp_xml_removedocument and a key word OPENXML Let us now see how can we exploit this for Multiple Insert Scenario.
With the Logic mentioned above, the code for desired Stored Procedure looks like:
CREATE PROCEDURE sp_InsertByXML ( @strXML VARCHAR(4000) )
AS
BEGIN
DECLARE @intDocHandle int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @strXML
INSERT INTO Person ( PersonId, PersonName )
SELECT * FROM OPENXML(@intDocHandle,
'/PersonData/Person', 2)
WITH
( PersonId INT,
PersonName VARCHAR(100)
)
-- Remove the internal representation.
EXEC sp_xml_removedocument @intDocHandle
END
Now we need to form the XML at front end, which we can pass to this stored proc. I am sure, there can be various ways to do it. You can form by concatenating and forming XML or by using XMLDocument object of System.XML namespace and get the XML string out of it. Since most of times we play around DataSet and DataTables, I chose to get the XML out from the DataSet. First of all get the desired data in DataSet object. If you have trouble forming DataSet at runtime, refer to the “private string GetXml()” method in the sample code attached and then use the following code to get the string out of it :
System.IO.StringWriter sw = new System.IO.StringWriter ( );
dsPersonData.WriteXml (sw);
string strXml = sw.ToString();
With this I have the desired XML string. Now only job left is to call the stored procedure from my front end code, which is as follows:
private void btnInsertByXMLInput_Click(object sender, System.EventArgs e)
{
string strXml = GetXml();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("sp_InsertByXML", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@strXML",SqlDbType.VarChar, 4000);
command.Parameters[0].Value = strXml;
connection.Open();
int recordsInserted = command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Number of records affected : " + recordsInserted.ToString());
}
It also returns the message “Number of records affected : 4 “. Multiple records inserted, and Mission Accomplished yet again.
Key Notes:
1. XML is Case Sensitive. For example, in the OPENXML statement ‘/root/person’ is not equal to ‘/ROOT/person’.
2. You can form the Attribute Centric as well as Element Centric XML. In the code above, it is Element Centric XML thus we have “2” in OPEN XML syntax, else default is 1 which is used for Attribute Centric XML
3. In ADO.NET 2.0, you can get the XML out of DataTable itself. In such case modify the XPath in OPENXML appropriately.
4. SQL Server 2000 supports XML processing, but in SQL Server 2005 we have xml as datatype. It has got lot more ways to support DML and DDL for xml. Choose as per your need.
References:
1. Books Online for details of sp_xml_preparedocument, sp_xml_removedocument, OPENXML
Form a Single SQL Statement and execute it directly from ADO.NET
It may sound bit rude and may be disgusting to today’s developers and architects of doing it this way, but yes, it is also an option. And, I agree with them.
If you believe that all the latest mechanisms like the one mentioned above does not suits your requirement, form a query by yourself and execute it. But that too when your project design permits you to execute the query directly from .NET Data Access Layer, one can use Command Object with CommandType as Text.
And to execute multiple insert queries, we can simply append the insert queries separated by semi-colon “;” and use that as the CommandText for your Command Object. Use the ExecuteNonQuery() method of Command Object and observe the resulting number of records affected.
Well, there are couple of ways in which we can form the SQL Statement for our need. Let us see each of them one by one.
Append Insert Statements
In this case we are going to append each of t Insert statement one after the other and execute it as a single command. The syntax that we are trying to achieve here is
INSERT INTO Person VALUES (‘1’, ‘AA’); INSERT INTO Person VALUES (‘2’, ‘BB’);
The .NET 1.1 code for creating sql query having 4 simultaneous insert and executing it with the command object would look like this:
private void btnInsertByJoiningQueries_Click(object sender, System.EventArgs e)
{
string sqlText = "INSERT INTO Person VALUES ( '" + txtPersonId1.Text.Trim() + "',' " + txtPersonName1.Text + "'); ";
sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId2.Text.Trim() + "',' " + txtPersonName2.Text + "'); ";
sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId3.Text.Trim() + "',' " + txtPersonName3.Text + "'); ";
sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId4.Text.Trim() + "',' " + txtPersonName4.Text + "'); ";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sqlText,connection);
command.CommandType = CommandType.Text;
connection.Open();
int recordsInserted = command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Number of records affected : " + recordsInserted.ToString());
}
In the example above, it returns the message “Number of records affected : 4 “. Multiple records inserted, Mission Accomplished.
Use Select and Union All for Insert
This is quite similar to appending insert statements, but rather than appending each complete Insert statement we are going to first going to Select the values and then pass it to Insert statement to Insert them in the table. The syntax that we are trying to achieve here is
INSERT INTO TableName (Col1, Col2)
SELECT 1, ‘AA’
UNION ALL
SELECT 2, ‘BB’
UNION ALL
SELECT 3, ‘CC’
Rest, the approach is quite similar to what we saw in “Append Insert Statements” section. So the code for it is left to you. Still if you need some assistance feel free to post it.
Pass the values in a string with ColumnSeperator and/or RowSeperator and pass it to Stored proc in SQL Server. Split it in proc and Insert
Here what we can do is, from the front end create a string with values separated by predefined ColumnSeperator and/or RowSeperator and then let Stored Proc parse it, separate the data, bring the data into useful format and then insert it into the respective table. Well, I am not going to go in its details as I also believe that this approach should be used when all your other options are really ruled out. But I can give you tips for it. Create a function which returns you the values after splitting the in the desired format. I found couple of them as mentioned below :
Split functions for SQL Server 2000 at the URLs :
· http://www.eggheadcafe.com/community/aspnet/9/10012556/split-function-in-your-da.aspx
· http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
Split functions for Oracle at the URLs :
*
http://www.eggheadcafe.com/community/aspnet/9/10030407/equivalent-split-function.aspx
May be you can take the permission from the Author and modify and use it.
Key Notes:
1. If number of Insert queries are fairly large in number, use StringBuilder rather than simple string object for better performance.
2. Go for clubbing the SQL statements only if you do not have any option left with you. This is surely an option, but not as maintainable and secure as other previously mentioned options.
Other Useful References:
I came across couple of articles which I really found relevant and useful, I advise you to go through them
* Performing Batch Operations Using DataAdapters (ADO.NET): http://msdn2.microsoft.com/en-us/library/aadf8fk2.aspx
* Data Access Application Block at microsoft.com
* How to: Bind an Array to an ODP.NET Database Command at oracle.com/technology
* Passing an array or DataTable into a stored procedure : http://www.codeproject.com/KB/database/PassingArraysIntoSPs.aspx
Wrapping up
Huff, numerous ways to do the same thing. Now the big question - which one to go for? Trust me there is no hard and fast rule, but when you choose one of them, ensure that whichever mechanism you are choosing gives best run time performance, easy to develop, easy to maintain, secure and reliable. As stated earlier, this article limits the scope of discussion to ensure that in one connection instance we get the multiple inserts done for better performance and less resource consumption, but I am sure there are various ways in which performance can be enhanced.
One more important thing that I really want you all to take care of is Exception Handling. You may get exceptions like Primary Key violation, foreign key violation, or some other constraint violation even when we try to insert single record, here we are attempting to insert lot more than one, so got to be very careful. I have not taken exception handling in details here due to the limited scope.
Please spare some time to rate and provide feedback about this article. Your couple of minutes can help in enhancing the quality of this article.
No comments:
Post a Comment