When writing bulk updates or inserts into Oracle with ODP.NET you can use PLSQLAssociativeArray (example here) and you may search around for an equivalent array type in SQL Server but there isn’t one. Firstly this is disappointing, then you kind of get used to the idea, hopefully this post will help you on onto this path.
First off I thought it was wrong not to have an array type, “Oracle does, this is stupid” I thought. After a cup of tea, I put a couple of seconds thought into this and the best I could come up with is all the data we use in SQL is tabular data anyway and you could represent a PLSQLAssociativeArray as a table with one column right? So is it any loss? No, of course not, but the programmer in me is used to dealing with arrays.
However there is a drawback that I haven’t completely got over yet, and that is that you must define the type name of the SqlParameter, this must be a valid type, so randomly creating some SQL and firing it at the database isn’t an option. But I have other options in my mind, here’s some examples of bulk operations utilizing table valued parameters:
Create a table to manipulate:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Greetings]( [PrimaryId] [int] NOT NULL, [FirstValue] [nchar](10) NULL, [SecondValue] [nchar](10) NULL ) ON [PRIMARY] GO
Create a loosely typed DataTable and populate it with some data to insert into the table:
DataTable dt = new DataTable("Tim");
dt.Columns.Add(new DataColumn("FirstCol", typeof(int)));
dt.Columns.Add(new DataColumn("SecondCol", typeof(String)));
dt.Columns.Add(new DataColumn("ThirdCol", typeof(String)));
DataRow r1 = dt.NewRow();
r1["FirstCol"] = 1;
r1["SecondCol"] = "Hello";
r1["ThirdCol"] = "Hi";
dt.Rows.Add(r1);
DataRow r2 = dt.NewRow();
r2["FirstCol"] = 2;
r2["SecondCol"] = "Goodbye";
r2["ThirdCol"] = "Bye";
dt.Rows.Add(r2);
(brings back memories of .NET 1.1 doesn’t it)
Next up create a table valued type in SQL Server, of course you could keep well defined types in your database, but for this example I just want something “temporary”. Also this could be a generated string from the DataTable, look around someone might already be doing this, else it’s easy enough to roll your own
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"BEGIN TRY"
+ " CREATE TYPE dbo.T1"
+ " AS TABLE ( [FirstCol] [int] NOT NULL, [SecondCol] [nchar](10) NULL, [ThirdCol] [nchar](10) NULL )"
+ " END TRY"
+ " BEGIN CATCH"
+ " END CATCH";
cmd.ExecuteNonQuery();
}
So by this point in execution your app would have a type defined called dbo.T1, you could of course use a temporary generated name for this too. Note the TRY CATCH in there; ignoring errors.
Next do a bulk insert: (Note: SqlBulkCopy may be quicker, I’ve not checked)
using (SqlCommand cmd = conn.CreateCommand())
{
SqlParameter p = new SqlParameter("@MyInputDT", SqlDbType.Structured);
p.Value = dt;
p.TypeName = "dbo.T1";
cmd.Parameters.Add(p);
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"INSERT INTO dbo.Greetings (PrimaryId, SecondValue)"
+ " SELECT FirstCol, ThirdCol"
+ " FROM @MyInputDT";
cmd.ExecuteNonQuery();
}
This to me is better than passing two arrays, a size parameter and looping though and inserting each. But we’ve got SqlBulkCopy, so you may not want to use this approach.
By this point in execution there would be two new entries in the dbo.Greetings table but I’ve deliberately missed out the FirstValue, so that will be NULL.
Let’s do a bulk update to fix it, also remove the “temporary” table valued type:
using (SqlCommand cmd = conn.CreateCommand())
{
SqlParameter p = new SqlParameter("@MyUpdDT", SqlDbType.Structured);
p.Value = dt;
p.TypeName = "dbo.T1";
cmd.Parameters.Add(p);
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"UPDATE Greetings"
+ " SET FirstValue = SecondCol"
+ " FROM @MyUpdDT"
+ " WHERE PrimaryId = FirstCol"
+ " BEGIN TRY"
+ " DROP TYPE dbo.T1"
+ " END TRY"
+ " BEGIN CATCH"
+ " END CATCH";
cmd.ExecuteNonQuery();
}
So there’s a bulk insert and bulk update, I think this is pretty neat, but the requirement on having a well defined type is a gripe, now I’m over the lack of array type I want a var type now, shame.
See also Table-Valued Parameters in SQL Server 2008 (ADO.NET)
Tim's cycling blog 