1 / 9

Mastering Table Value Parameters in SQL Server 2008 and .NET

Learn how to effectively use Table Value Parameters in SQL Server 2008 and .NET, including creating data types, inserting values, and integrating with ADO.NET. Discover limitations, benefits, and practical demos.

thane-snow
Download Presentation

Mastering Table Value Parameters in SQL Server 2008 and .NET

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Table Value Parameters with SQL Server 2008 and .NET John Sterrett http://twitter.com/johnsterrett http://johnsterrett.com

  2. 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

  3. 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

  4. 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)

  5. 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

  6. Demo • Table Value Parameters with SQL Server 2008

  7. Demo • Table Value Parameters with .NET 2008 • Filter Selection • Insert Multiple Values

  8. 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

  9. Great now what?? • Download SQL Server 2008 R2 Developers Training Kit • Check blog for demo and slides • www.johnsterrett.com

More Related