Docstoc

DAT404_ Passing a Set of Data to Microsoft SQL Server

Document Sample
DAT404_ Passing a Set of Data to Microsoft SQL Server Powered By Docstoc
					// C#
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test.spDelimitedString";
cmd.Parameters.AddWithValue("@Values", @"…|…|…
                                         …|…|…
                                         …|…|…");
cmd.Execute…;

-- T-SQL: What happens on the server?
EXEC Test.spDelimitedString @Values = '…|…|…
                                       …|…|…
                                       …|…|…';
// C#
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test.spXML";
cmd.Parameters.AddWithValue("@Values",
                                  xmlDoc.OuterXml);
cmd.Execute…;

-- T-SQL: What happens on the server?
EXEC Test.spXML @Values = N'<Orders><Order…
// C#
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test.spTVP";
var p = cmd.Parameters.Add("@Values“, SqlDbType.Structured);
p.TypeName = "Test.OrderTableType";
p.Value = dataTable;
cmd.Execute…;

-- T-SQL: What happens on the server?
DECLARE @Values Test.OrderTableType;
INSERT @Values …
EXEC Test.spTVP @Values = @Values;
// C#
class MyStreamingTvp : IEnumerable<SqlDataRecord> {
   …
}
…
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test.spTVP";
var p = cmd.Parameters.Add("@Values", SqlDbType.Structured);
p.TypeName = "Test.OrderTableType";
p.Value = new MyStreamingTvp(…);
cmd.Execute…;

-- T-SQL: What happens on the server?:
DECLARE @Values Test.OrderTableType;
INSERT @Values …
EXEC Test.spTVP @Values = @Values;
Data passed as delimited string     Data passed as XML        Data passed as TVP
                                     266




                                                             32
                19


                     Execution time (ms., lower is better)
2. Querying the data

       Data passed as delimited string      Data passed as XML        Data passed as TVP
                                             969




                       307



                                                                     33

                             Execution time (ms., lower is better)
www.microsoft.com/teched       www.microsoft.com/learning




http://microsoft.com/technet   http://microsoft.com/msdn
    Sign up for Tech·Ed 2011 and save $500
           starting June 8 – June 31st
http://northamerica.msteched.com/registration




             You can also register at the
    North America 2011 kiosk located at registration
             Join us in Atlanta next year

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:7
posted:2/22/2013
language:Latin
pages:25