New Features - SQL 2008: Table-Valued Parameters (TVP)
SQL 2008 introduced a nifty feature called Table-Valued Parameters (TVP) into its codebase.
TVPs allow developers to pass sets of data to a stored procedure (i.e. passing a table to a stored
procedure parameter).
The first thing we need to do, is define a table type, like this:
CREATE TYPE [dbo].[MyFriends] AS TABLE( [firstname] [varchar](50) NOT NULL, [lastname] [varchar](50) NOT NULL )
Create a little table for our testing purposes:
CREATE TABLE [dbo].[friends]( [friendID] [int] IDENTITY(1,1) NOT NULL, [firstname] [varchar](50) NOT NULL, [lastname] [varchar](50) NOT NULL, CONSTRAINT [PK_friends] PRIMARY KEY CLUSTERED ( [friendID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Next we create a stored procedure using our newly created table type. Note a TVP must be passed as READONLY - which means you can't modify(insert/update/delete rows) it within the body of the routine you're passing it to.
CREATE PROCEDURE [dbo].[addFriends] @friends MyFriends READONLY AS BEGIN INSERT INTO friends(firstname, lastname) SELECT firstname, lastname FROM @friends END
In our C# snippet we create a DataTable, add some rows to it, and pass it to our stored procedure:
using (SqlConnection connection = new SqlConnection(@"Some connectionString")) { using (SqlCommand command = new SqlCommand("addFriends", connection) { CommandType = CommandType.StoredProcedure }) { DataTable dt = new DataTable(); // Add columns dt.Columns.Add("firstname", typeof(String)).MaxLength = 50; dt.Columns.Add("lastname", typeof(String)).MaxLength = 50; ; // Add some rows dt.Rows.Add(new Object[] { "Eugene", "Stander" }); dt.Rows.Add(new Object[] { "Roland", "Cooper" }); dt.Rows.Add(new Object[] { "Jacques", "Brits" }); connection.Open(); command.Parameters.Add("@friends", SqlDbType.Structured).Value = dt; command.ExecuteNonQuery(); } }
How does all of this affect performance though?
A few clues can be found at http://technet.microsoft.com/en-us/library/bb510489.aspx
Summarized in the case of bulk inserts vs TVPs; a formatted data file on the server will perform better using a bulk insert, unless it is a complex operation with less than 1000 rows.
Using a remote client process TVPs will usually perform better, unless its a direct insert with more than 1000 rows.
Posted by - Christoff Truter
Date - 2010-01-06 22:58:25
Comments - 0
Date - 2010-01-06 22:58:25
Comments - 0
Paging: ASP.net (C#) and SQL 2005/8
Source code for the following post can be downloaded here.
Paging data is one of those basic things most, if not every developer
will have to do from time to time.
In the following approach, we're going to use a ListView, DataPager and
an ObjectDataSource to achieve this. Equally one can use a DataView and
simply use it with an ObjectDataSource - I personally prefer the
ListView, simply because I feel its a more flexible control.
Lets have a look at the ObjectDataSource first:
<asp:ObjectDataSource ID="obsFriends" runat="server" SelectCountMethod="CountFriends" SelectMethod="ViewFriends" TypeName="friends" EnablePaging="true"> </asp:ObjectDataSource>
Notice that we'll need a "SelectCountMethod", this method returns a count of the total rows that needs paging, the "SelectMethod" is the method that returns the actual rows.
TypeName is the class (along with its namespaces) where the ObjectDataSource can find these methods.
That class will look something like the following:
using System; using System.Data; using System.Configuration; using System.ComponentModel; using System.Data.SqlClient; [DataObject()] public class friends { [DataObjectMethod(DataObjectMethodType.Select)] public static DataTable ViewFriends(Int32 maximumRows, Int32 startRowIndex) { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString)) { using (SqlCommand command = new SqlCommand("ViewFriends", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@maximumRows", maximumRows); command.Parameters.AddWithValue("@startRowIndex", startRowIndex); using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { DataTable dt = new DataTable(); adapter.Fill(dt); return dt; } } } } [DataObjectMethod(DataObjectMethodType.Select)] public static Int32 CountFriends() { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString)) { using (SqlCommand command = new SqlCommand("CountFriends", connection)) { command.CommandType = CommandType.StoredProcedure; connection.Open(); return Convert.ToInt32(command.ExecuteScalar()); } } } }
There are a few things you'll notice in the preceding snippet, attributes like DataObjectMethod & DataObject - these attributes are simply there to make these methods visible in the control designers, eg Configure Data Source in design view.
Also, you'll notice we need to create two stored procedures ViewFriends and CountFriends. ViewFriends require two parameters, maximumRows and startRowIndex, these parameters automatically become required the second we set EnablePaging in our object to true.
Now is probably a good time to create some data.
Lets create a little table, where we insert the first and last names of friends.
CREATE TABLE [dbo].[friends]( [id] [int] IDENTITY(1,1) NOT NULL, [firstname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [lastname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_friends] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
Populate it with some data.
INSERT INTO friends(firstname, lastname) VALUES ('Julie', 'Truter') INSERT INTO friends(firstname, lastname) VALUES ('Eugene', 'Stander') INSERT INTO friends(firstname, lastname) VALUES ('Pam', 'Nizar') INSERT INTO friends(firstname, lastname) VALUES ('Alexander', 'Mehlhorn') INSERT INTO friends(firstname, lastname) VALUES ('Roland', 'Cooper') INSERT INTO friends(firstname, lastname) VALUES ('Loren', 'Stevens') INSERT INTO friends(firstname, lastname) VALUES ('Edward', 'Anderson') INSERT INTO friends(firstname, lastname) VALUES ('Wayne', 'Kleynhans')
The ViewFriends stored procedure will look like this, notice we use a CTE to aid us in our paging process - this works in SQL 2005 and greater.
CREATE PROCEDURE [dbo].[ViewFriends] @maximumRows AS INT, @startRowIndex AS INT AS BEGIN WITH entries AS ( SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW, id, firstname, lastname FROM friends ) SELECT firstname, lastname FROM entries WHERE ROW BETWEEN @startRowIndex+1 AND (@startRowIndex + @maximumRows) END
And the CountFriends like this:
CREATE PROCEDURE CountFriends AS BEGIN SELECT COUNT(*) FROM friends END
Getting back to our frontend, our pager will look something like this:
<asp:DataPager runat="server" ID="dpFriends" PagedControlID="lvFriends" PageSize="3"> <Fields> <asp:NumericPagerField ButtonType="Link" /> </Fields> </asp:DataPager>
Notice PagedControlID, this is the control we'll be paging, and PageSize is the number of rows that must be displayed.
Finally, our ListView will look like this:
<asp:ListView ID="lvFriends" DataSourceID="obsFriends" runat="server" ItemPlaceholderID="phFriends"> <LayoutTemplate> <table> <tr style="background-color: Black; color: White"> <td> Firstname </td> <td> Lastname </td> </tr> <asp:PlaceHolder ID="phFriends" runat="server"></asp:PlaceHolder> </table> </LayoutTemplate> <ItemTemplate> <tr> <td> <%# Eval("firstname") %> </td> <td> <%# Eval("lastname") %> </td> </tr> </ItemTemplate> </asp:ListView>
DataSourceID refers to our ObjectDataSource, ItemPlaceholderID to the control within our LayoutTemplate thats going to be substituted, with the values generated within the ItemTemplate.
Posted by - Christoff Truter
Date - 2009-10-19 15:01:45
Comments - 0
Date - 2009-10-19 15:01:45
Comments - 0
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27