90 likes | 157 Views
Introduction to Table Value Parameters with SQL Server 2008 and .NET . John Sterrett http://twitter.com/johnsterrett http://johnsterrett.com. Problem. http://www.sqlservercentral.com/articles/Array/70702/. -- Let's imagine this is the parameter ---- Declare @sParameter varchar(8000)
E N D
Introduction to Table Value Parameters with SQL Server 2008 and .NET John Sterrett http://twitter.com/johnsterrett http://johnsterrett.com
Problem • http://www.sqlservercentral.com/articles/Array/70702/ -- Let's imagine this is the parameter ---- Declare @sParameter varchar(8000) Set @sParameter = 'WA,AZ,CA,TS‘ -- Now the loop ---- Declare @iPos integer Set @sState varchar(8000) Set @iPos = CharIndex( ',', @sParameter ) While @sParameter <> '' Begin If @iPos > 0 Begin Set @sState = Substring( @sParameter, 1, @iPos - 1 ) Set @sParameter = Substring( @sParameter, @iPos + 1, 8000 ) End Else Begin Set @sState = @sParameter Set @sParameter = '' End -- Insert here code to validate @sState ---- Insert @tStates Values ( @sState ) Set @iPos = CharIndex( ',', @sParameter ) End Select * From @tStates
Answer • Use Table Value Parameters -- Create the data type CREATE TYPE dbo.SQLServerCentral AS TABLE ( [state] [nvarchar](2) NULL ) GO DECLARE @t dbo.SQLServerCentral; INSERT INTO @t VALUES('CA'), ('PA'), ('WV'), ('TX'), ('OK'), ('NM'); SELECT * FROM @t
How do get this to work in .NET? SqlParameter tvpParam = new SqlParameter(); tvpParam.ParameterName = "@TVP"; tvpParam.Value = dtList; //DataTable in this example tvpParam.SqlDbType = SqlDbType.Structured; // This data type allows us to pass a table into DB Objects tvpParam.TypeName = "TVPComponentType"; // Name of strong typed (table type)
Things to Know • ADO.NET 3.5 Supports Table Value Parameters • You can populate TVP from the following .NET objects • DataTable • DbDataReader • System.Collections.Generic.IList <SQLDataRecord> • Recommended for 1000 or less records
Demo • Table Value Parameters with SQL Server 2008
Demo • Table Value Parameters with .NET 2008 • Filter Selection • Insert Multiple Values
Limitations and Gotchas • Table Value Parameters areREADONLY • No statistics are created on TVP • Default Value is always an empty table not NULL • Resources are limited by TEMPDB
Great now what?? • Download SQL Server 2008 R2 Developers Training Kit • Check blog for demo and slides • www.johnsterrett.com