Using a DbDataReader object as a TVP in Visual C#

Drawer Code128 in Visual C# Using a DbDataReader object as a TVP

Listing 2 Using a DbDataReader object as a TVP
USS Code 128 Generation In Visual C#.NET
Using Barcode printer for .NET framework Control to generate, create Code 128C image in VS .NET applications.
www.OnBarcode.com
Code 128 Code Set B Reader In C#
Using Barcode decoder for Visual Studio .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
protected void DbDataReaderButton_Click(object sender, EventArgs e) { DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
Code 128 Code Set A Generation In Visual C#.NET
Using Barcode generation for Visual Studio .NET Control to generate, create Code128 image in Visual Studio .NET applications.
www.OnBarcode.com
Code 39 Full ASCII Generation In Visual C#
Using Barcode printer for Visual Studio .NET Control to generate, create Code 39 Extended image in VS .NET applications.
www.OnBarcode.com
Using TVPs from client applications
Matrix Generation In Visual C#
Using Barcode creation for Visual Studio .NET Control to generate, create Matrix Barcode image in VS .NET applications.
www.OnBarcode.com
QR Code JIS X 0510 Drawer In Visual C#.NET
Using Barcode creator for .NET Control to generate, create QR Code 2d barcode image in VS .NET applications.
www.OnBarcode.com
DbConnection cnnAW = factory.CreateConnection(); cnnAW.ConnectionString = ConfigurationManager .ConnectionStrings["AWLT"].ConnectionString; DbCommand dbCmd = cnnAW.CreateCommand(); dbCmd.CommandText = "SELECT Productid, [Name] FROM SalesLT.Product"; dbCmd.CommandType = CommandType.Text; cnnAW.Open(); DbDataReader ddr = dbCmd.ExecuteReader(); using (SqlConnection cnn = new SqlConnection(ConfigurationManager .ConnectionStrings["TVPs"].ConnectionString)) { SqlCommand cmd = new SqlCommand("dkSelectFromTVP", cnn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter param = cmd.Parameters.AddWithValue("@TVParam", ddr); param.TypeName = "dbo.MyTbl"; param.SqlDbType = SqlDbType.Structured; cnn.Open(); SqlDataReader dr = cmd.ExecuteReader(); GridView1.DataSource = dr; GridView1.DataBind(); } }
Linear Drawer In Visual C#.NET
Using Barcode generator for VS .NET Control to generate, create Linear Barcode image in Visual Studio .NET applications.
www.OnBarcode.com
Printing Postnet In Visual C#
Using Barcode encoder for .NET Control to generate, create Delivery Point Barcode (DPBC) image in .NET applications.
www.OnBarcode.com
Figure 4 Results of passing a DbDataReader as a TVP
Recognize Code 128C In Java
Using Barcode recognizer for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Code 128B Encoder In Objective-C
Using Barcode creation for iPhone Control to generate, create USS Code 128 image in iPhone applications.
www.OnBarcode.com
Using TVPs to enter orders
GTIN - 13 Generator In Java
Using Barcode encoder for Android Control to generate, create EAN-13 Supplement 5 image in Android applications.
www.OnBarcode.com
Generate QR Code ISO/IEC18004 In Java
Using Barcode printer for BIRT reports Control to generate, create Quick Response Code image in BIRT applications.
www.OnBarcode.com
The next example shows a more realistic use for TVPs. The code is part of an order entry system, and one of the application requirements is to reduce the number of round trips between the client application and the database server. The code in Orders.sql takes the order header information and line items and inserts them into the appropriate tables in the database in a single round trip. The code in listing 3 shows the T-SQL used to create the database objects. The OrderDetailsType is a TABLE type that the client application will pass to the stored procedure with the list of
Encode ANSI/AIM Code 39 In Objective-C
Using Barcode printer for iPad Control to generate, create Code-39 image in iPad applications.
www.OnBarcode.com
Read GTIN - 12 In VS .NET
Using Barcode recognizer for .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
Table-valued parameters
Barcode Generation In Objective-C
Using Barcode creator for iPad Control to generate, create Barcode image in iPad applications.
www.OnBarcode.com
Code 128 Code Set A Creator In None
Using Barcode creator for Office Excel Control to generate, create Code 128A image in Excel applications.
www.OnBarcode.com
products selected by the user. Pricing and other information will be gathered from other tables in the AdventureWorksLT2008 database. Then the code creates the Orders and OrderDetails tables to hold the data. Finally, the code creates the dkPlaceOrder stored procedure that receives the CustomerID and OrderDetailsType TVP. It gets the next order ID from the Orders table, then inserts the data into the Orders and OrderDetails tables. Note that all the data is either passed to the procedure or gathered from related tables in the AdventureWorksLT2008 database.
Recognizing Code 128 Code Set A In VS .NET
Using Barcode decoder for .NET framework Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
Print USS Code 39 In None
Using Barcode generation for Microsoft Excel Control to generate, create Code 39 image in Office Excel applications.
www.OnBarcode.com
NOTE
DataMatrix Encoder In None
Using Barcode encoder for Word Control to generate, create Data Matrix ECC200 image in Microsoft Word applications.
www.OnBarcode.com
Code 39 Extended Decoder In Java
Using Barcode decoder for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
To keep things simple and to focus on TVPs, I didn t make this code robust enough to handle concurrency issues. For example, the method of getting the next OrderID number will likely result in duplicate IDs in a multiuser environment. So don t use this code in a production environment without beefing it up to handle your environment!
Code to create database objects to insert orders
Listing 3
-- Create type to hold user product selections CREATE TYPE OrderDetailsType AS TABLE (ProductID INT NOT NULL, Quantity INT NOT NULL) GO CREATE TABLE Orders ( OrderID INT NOT NULL, CustomerID INT NOT NULL, OrderDate Datetime NOT NULL) GO CREATE TABLE OrderDetails ( OrderID INT NOT NULL, ProductID INT NOT NULL, Cost MONEY NOT NULL, Quantity INT NOT NULL) GO CREATE PROCEDURE dkPlaceOrder ( @CustomerID INT, @Items OrderDetailsType READONLY) AS -- Get the next OrderID -- CAUTION! Simple, but not robust for concurrent apps DECLARE @OrderID INT SET @OrderID = (SELECT ISNULL(MAX(OrderID)+1, 1) FROM Orders) -- Create the order INSERT INTO dbo.Orders VALUES(@OrderID, @CustomerID, GETDATE()) -- Insert the products INSERT dbo.OrderDetails(OrderID, ProductID, Cost, Quantity) SELECT @OrderID, tvp.ProductID, prd.StandardCost, tvp.Quantity FROM @Items AS tvp INNER JOIN AdventureWorksLT2008.SalesLT.Product AS prd ON tvp.ProductID = prd.ProductID GO
Using TVPs from client applications
Figure 5 The order entry user interface
Orders.sql also has some code you can use to test the stored procedure by entering an order. Figure 5 shows that the Orders.aspx page in the Client sample website application has the user interface. The user selects a product category, then selects one of the available products. To order the item, the user enters the quantity and clicks the Add to Cart button. The Shopping Cart section keeps track of the selected items. When the user is done, she clicks the Checkout button to place the order, which inserts the data into the Orders and OrderDetails tables. To maximize profits, there is no way to remove an item from the shopping cart after it is placed there. Most of the code behind the Orders.aspx page manages the user interface and is not included here; you can explore that code on your own. The shopping cart is managed with a DataTable that is saved as a session variable as the user interacts with the page and selects products. This makes it easy to pass to the stored procedure when the user is ready to place the order. But there is a twist in the code: to make displaying the contents of the shopping cart convenient, the DataTable includes a ProductName field in addition to the two fields that the stored procedure expects. So before passing the DataTable to the procedure, the code removes that extra field. It does this by creating a copy of the DataTable object and removing the column from the copy instead of the original object. If you tried to pass the DataTable with the extra field, SQL Server would refuse it and raise an error. Listing 4 contains the code for the Click event of the Checkout button. The code uses a hardcoded CustomerID of 1, and sets up to pass the dt DataTable object as a TVP.
Copyright © OnBarcode.com . All rights reserved.