The following sample code shows how to return a resultset from a CLR stored procedure.
The main thing to note is that you have to pass an empty SqlDataRecord to the SendResultsStart() method in order to provide feedback on the columns sent back to the caller.
[SqlProcedure]
public static SqlInt32 MyStoredProcedure()
{
SqlMetaData[] meta = new SqlMetaData[] { new SqlMetaData( "MyColumn", SqlDbType.Int ) };
Int32[] data = new Int32[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
SqlDataRecord[] records = data.Select( x => { SqlDataRecord r = new SqlDataRecord( meta ); r.SetInt32( 0, x ); return r; } ).ToArray();
SqlContext.Pipe.SendResultsStart( new SqlDataRecord( meta ) );
foreach ( SqlDataRecord r in records )
SqlContext.Pipe.SendResultsRow( r );
SqlContext.Pipe.SendResultsEnd();
return records.Length;
} |
In case you would like to return data retrieved from the database using a SqlDataReader object, you can directly pass the reader to the SqlContext.Pipe.Send() method:
[SqlProcedure]
public static void MyStoredProcedure()
{
using ( SqlConnection con = new SqlConnection( "context connection=true" ) )
{
con.Open();
using ( SqlCommand cmd = con.CreateCommand() )
{
cmd.CommandText = "SELECT SomeColumn FROM SomeTable WHERE SomeColumn IS NOT NULL";
using ( SqlDataReader r = cmd.ExecuteReader() )
SqlContext.Pipe.Send( r );
}
}
} |
Categories: C#, Software Development, SQL Tags: .NET, C++, CLR, Resultset, SendResultsStart, SQL Server, SQL Server 2005, SqlDataReader, SqlDataRecord, SqlMetaData, Stored Procedure, T-SQL
SQL Server 2008 supports a new way of passing a variable number of arguments to functions and stored procedures.
The good thing for .NET developers is that those table-valued parameters are also usable from a managed context.
So when doing a mass join/mass update using SqlCommand, this approach offers a huge increase in performance when migrating from a line-by-line approach to a single statement that is to be executed.
The following example is to demonstrate a sample usage of this technique. We are assuming that we want to insert a set of Unicode strings into the database.
First of all, TVPs are only supported with custom types that must be created prior to execution.
CREATE TYPE dbo.MyType AS TABLE( MyColumn NVARCHAR(MAX) );
GRANT EXEC ON TYPE::dbo.MyType TO MyRole; |
This is kind of a drawback if you are using a large number of different data types from different queries.
Here is how to pass data from an array to SQL Server:
using ( SqlConnection con = new SqlConnection( "connection string" ) )
{
con.Open();
using ( SqlCommand cmd = con.CreateCommand() )
{
string[] arr = { "a", "b", "c", "d" };
SqlMetaData meta = new SqlMetaData( "MyColumn", SqlDbType.NVarChar, SqlMetaData.Max );
var records = arr.Select( x => { SqlDataRecord r = new SqlDataRecord( meta ); r.SetString( 0, x ); return r; } );
SqlParameter UrlParam = cmd.Parameters.AddWithValue( "@MyTVP", records );
UrlParam.SqlDbType = SqlDbType.Structured;
UrlParam.TypeName = "dbo.MyType";
cmd.CommandText = "INSERT INTO MyTable ( SomeColumn ) SELECT MyAlias.MyColumn FROM @MyTVP AS MyAlias;";
cmd.ExecuteNonQuery();
}
} |
In order to send the array data to the database server, we need to wrap it in an IEnumerable. This is then added to the parameters collection with the special type qualifier SqlDbType.Structured. On the SQL side, the handling is the same as a real table in the database, although it might be necessary to work with aliases when referencing the TVP.
Note that in this example we are limiting the insert to a single column. However you may also create TVPs with multiple columns and different data types.
This approach does not offer much savings when dealing with a few records to be passed to the server. However when you want to insert a few hundred records at once, executing hundreds of queries instead of a single one pays off.
The System.Data.SqlClient namespace does not include a straightforward way of sending an arbitrary amount of data rows to SQL Server in a single statement, e.g. for making a mass updates/bulk insert.
In most circumstances, generating SQL statements dynamically using string concatenation would be a bad choice, since it might offer vectors for SQL injection and other pitfalls resulting from inadequately escaped code.
When using SQL Server 2008, table valued parameters come in handy, however they require modifications on the server which may not be eligible in all scenarios.
There is another possibility using SQL Server’s builtin XML data type together with the System.Xml classes to achieve this goal. The following code demonstrates a simple approach for inserting data from a string array using a single SQL statement.
using ( SqlConnection con = new SqlConnection( "connection string" ) )
{
con.Open();
using ( SqlCommand cmd = con.CreateCommand() )
{
string[] arr = { "a", "b", "c", "d" };
XmlDocument xml = new XmlDocument();
xml.AppendChild( xml.CreateElement( "MyColumns" ) );
foreach ( string s in arr )
{
XmlElement e = xml.CreateElement( "MyColumn" );
e.InnerText = s;
xml.DocumentElement.AppendChild( e );
}
cmd.AddParameter( "@XML", DbType.Xml, xml.OuterXml );
cmd.CommandText =
"INSERT INTO MyTable ( SomeColumn ) SELECT Item.value( '.', 'NVARCHAR(MAX)' ) " +
"FROM @XML.nodes( 'MyColumns/MyColumn' ) AS x( Item );";
cmd.ExecuteNonQuery();
}
} |
Since preparing data with an XmlDocument can require a lot of memory with large data, the following alternative can be considered when passing a string array:
/// <summary>Serializes the given strings to an XML string. Will use short element names to reduce string size.</summary>
public static string SerializeToXML( IEnumerable<string> strings )
{
using ( StringWriter sw = new StringWriter() )
using ( XmlTextWriter xw = new XmlTextWriter( sw ) )
{
foreach ( string s in strings )
{
xw.WriteStartElement( "s" );
xw.WriteString( s );
xw.WriteEndElement();
}
xw.Flush();
return sw.ToString();
}
} |
When an entire object needs to be passed to the database, the following generic solution works well:
/// <summary>Serializes the given objects to an XML string. Will use XmlSerializer which requires more memory.</summary>
public static string SerializeToXMLUsingXmlSerializer<T>( IEnumerable<T> t )
{
using ( StringWriter sw = new StringWriter() )
using ( XmlWriter xw = XmlWriter.Create( sw, new XmlWriterSettings() { OmitXmlDeclaration = true, Indent = false } ) )
{
T[] tmp = t.ToArray();
XmlSerializer xs = new XmlSerializer( tmp.GetType(), new XmlRootAttribute( typeof( T ).Name ) );
XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
ns.Add( String.Empty, String.Empty );
xs.Serialize( xw, tmp, ns );
return sw.ToString();
}
} |
When only sending a few rows to the server, this approach does not offer better performance that an iterative approach. However when dealing with larger amounts of data, the overhead of sending, executing, receiving and evaluating each query will add up to a signifficant amount of time – which can be avoided using this technique.
Categories: C#, Software Development Tags: Bulk Insert, C++, SQL, SQL Server, SqlConnection, T-SQL, XML, XmlDocument, XmlSerializer, XmlSerializerNamespaces, XmlTextWriter, XmlWriter