Table of Contents
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)
{
DataTable dtInsertRows = GetDataTable();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("sp_BatchInsert", connection);
command.CommandType = CommandType.StoredProcedure;
command.UpdatedRowSource = UpdateRowSource.None;
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;
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)
{
DataTable dtInsertRows = GetDataTable();
using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))
{
sbc.DestinationTableName = "Person";
sbc.BatchSize = 2;
sbc.ColumnMappings.Add("PersonId", "PersonId");
sbc.ColumnMappings.Add("PersonName", "PersonName");
sbc.NotifyAfter = dtInsertRows.Rows.Count;
sbc.SqlRowsCopied+=new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);
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 :
-
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:
-
No better place than
MSDN. Refer to
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
for details on SqlBulkCopy
-
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()};
OracleConnection connection = new OracleConnection(oracleConnectionString);
OracleCommand command = new OracleCommand("sp_InsertByODPNET", connection);
command.CommandType = CommandType.StoredProcedure;
command.ArrayBindCount = arrPersonId.Length;
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:
-
Use the
latest possible version of ODP.NET, as it usually has the bug fixes from all
the previous releases.
- Do not forget
to set command.ArrayBindCount.
References:
-
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 ='
<root>
<person PersonId="1" PersonName="AA"/>
<person PersonId="2" PersonName="BB"/>
<person PersonId="3" PersonName="CC"/>
<person PersonId="4" PersonName="DD"/>
</root>'
EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @xmlString
SELECT * FROM OPENXML(@intDocHandle,
'/root/person')
WITH
( PersonId INT,
PersonName VARCHAR(100)
)
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
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)
)
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:
- XML is Case
Sensitive. For example, in the OPENXML statement ‘/root/person’ is not equal
to ‘/ROOT/person’.
-
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
- In ADO.NET
2.0, you can get the XML out of DataTable itself. In such case modify the
XPath in OPENXML appropriately.
- 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:
-
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 :
May be you can take the
permission from the Author and modify and use it.
Key Notes:
-
If number of
Insert queries are fairly large in number, use StringBuilder rather than
simple string object for better performance.
- 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
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.
If interested, Click
here to view all my published articles.
