Lesson 2: Using the ADO.NET Connected Classes in Visual Studio .NET

Print QR Code ISO/IEC18004 in Visual Studio .NET Lesson 2: Using the ADO.NET Connected Classes

Lesson 2: Using the ADO.NET Connected Classes
Denso QR Bar Code Drawer In Visual Studio .NET
Using Barcode printer for ASP.NET Control to generate, create QR image in ASP.NET applications.
www.OnBarcode.com
Barcode Creator In VS .NET
Using Barcode drawer for ASP.NET Control to generate, create bar code image in ASP.NET applications.
www.OnBarcode.com
The following code snippet shows how MARS can be used to perform the nested que ries for the author list and the total of the royalties:
Paint QR In Visual C#
Using Barcode encoder for .NET framework Control to generate, create QR Code JIS X 0510 image in Visual Studio .NET applications.
www.OnBarcode.com
Denso QR Bar Code Drawer In .NET Framework
Using Barcode drawer for Visual Studio .NET Control to generate, create Quick Response Code image in .NET framework applications.
www.OnBarcode.com
'VB Protected Sub Button10_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Button10.Click
Making Quick Response Code In VB.NET
Using Barcode creation for VS .NET Control to generate, create QR Code ISO/IEC18004 image in Visual Studio .NET applications.
www.OnBarcode.com
PDF 417 Generation In .NET Framework
Using Barcode printer for ASP.NET Control to generate, create PDF 417 image in ASP.NET applications.
www.OnBarcode.com
Dim lbl As Label = GetLabel(275, 20)
Create Code 3/9 In Visual Studio .NET
Using Barcode printer for ASP.NET Control to generate, create ANSI/AIM Code 39 image in ASP.NET applications.
www.OnBarcode.com
UCC - 12 Creation In .NET Framework
Using Barcode printer for ASP.NET Control to generate, create EAN / UCC - 13 image in ASP.NET applications.
www.OnBarcode.com
Dim pubs As ConnectionStringSettings
Generating Barcode In .NET Framework
Using Barcode generation for ASP.NET Control to generate, create barcode image in ASP.NET applications.
www.OnBarcode.com
Creating Barcode In .NET Framework
Using Barcode generator for ASP.NET Control to generate, create barcode image in ASP.NET applications.
www.OnBarcode.com
pubs = ConfigurationManager.ConnectionStrings("PubsDataMars")
Quick Response Code Creation In .NET
Using Barcode maker for ASP.NET Control to generate, create QR Code ISO/IEC18004 image in ASP.NET applications.
www.OnBarcode.com
Code-27 Drawer In .NET
Using Barcode generator for ASP.NET Control to generate, create Ames code image in ASP.NET applications.
www.OnBarcode.com
Dim connection As DbConnection = New SqlConnection()
Code 128 Code Set B Encoder In None
Using Barcode drawer for Excel Control to generate, create Code 128 image in Microsoft Excel applications.
www.OnBarcode.com
Scanning Bar Code In VB.NET
Using Barcode Control SDK for Visual Studio .NET Control to generate, create, read, scan barcode image in .NET applications.
www.OnBarcode.com
connection.ConnectionString = pubs.ConnectionString
ANSI/AIM Code 39 Encoder In VS .NET
Using Barcode maker for Visual Studio .NET Control to generate, create Code 3/9 image in .NET framework applications.
www.OnBarcode.com
Creating UCC - 12 In Java
Using Barcode creation for BIRT Control to generate, create UPC Symbol image in BIRT reports applications.
www.OnBarcode.com
Dim cmd As DbCommand = connection.CreateCommand()
Printing USS Code 128 In Objective-C
Using Barcode drawer for iPhone Control to generate, create Code-128 image in iPhone applications.
www.OnBarcode.com
Creating QR-Code In None
Using Barcode generator for Online Control to generate, create QR Code JIS X 0510 image in Online applications.
www.OnBarcode.com
cmd.CommandType = CommandType.Text
Data Matrix ECC200 Creation In None
Using Barcode encoder for Online Control to generate, create ECC200 image in Online applications.
www.OnBarcode.com
Recognize UPC A In .NET Framework
Using Barcode decoder for Visual Studio .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
cmd.CommandText = "SELECT au_id, au_lname, au_fname FROM Authors"
connection.Open()
Dim rdr As DbDataReader = cmd.ExecuteReader()
While rdr.Read()
Dim salesCmd As DbCommand = connection.CreateCommand()
salesCmd.CommandType = CommandType.Text
salesCmd.CommandText = _
"SELECT SUM(royaltyper) FROM TitleAuthor WHERE (au_id = @auId)"
Dim parm As DbParameter = salesCmd.CreateParameter()
parm.ParameterName = "@auId"
parm.Value = rdr("au_id")
salesCmd.Parameters.Add(parm)
Dim qtySales As Object = salesCmd.ExecuteScalar()
lbl.Text += rdr("au_lname").ToString() + ", " + rdr("au_fname").ToString() _
+ ": " + string.Format("{0:C}",qtySales) + "<br />"
End While
connection.Close()
End Sub //C# protected void Button10_Click(object sender, EventArgs e) { Label lbl = GetLabel(275, 20); ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsDataMars"];
DbConnection connection = new SqlConnection(pubs.ConnectionString);
DbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT au_id, au_lname, au_fname FROM Authors";
connection.Open();
DbDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
DbCommand salesCmd = connection.CreateCommand();
salesCmd.CommandType = CommandType.Text;
salesCmd.CommandText =
"SELECT SUM(royaltyper) FROM titleauthor WHERE (au_id = @auId)";
DbParameter parm = salesCmd.CreateParameter();
parm.ParameterName = "@auId";
4
Using ADO.NET and XML with ASP.NET
parm.Value = (string)rdr["au_id"];
salesCmd.Parameters.Add(parm);
object qtyRoyalties = salesCmd.ExecuteScalar();
lbl.Text += (string)rdr["au_lname"] + ", " + (string)rdr["au_fname"]
+ ": " + string.Format("{0:C}",qtyRoyalties) + "<br />";
connection.Close();
Performing Bulk Copy Operations with the SqlBulkCopy Object
The SqlBulkCopy class provides a high-performance method for copying data to a table in a SQL Server database. The source of the copy is constrained to the overloads of the WriteToServer method, which can accept an array of DataRow objects, an object that implements the IDbDataReader interface, a DataTable object, or a DataTable and DataRowState, as shown in Figure 4-13. This variety of parameters means that you can retrieve data from most locations.
SqlBulkCopy Class 1. 2. 3. 4. WriteToServer( ) Method
DataRow Array
DataTable IDataReader DataTable, DataRowState
Data Store XML Document
SQL Server
Figure 4-13 The SqlBulkCopy object can copy from a variety of sources to a SQL Server table.
NOTE
New in ASP.NET 2.0
The SqlBulkCopy object is new in version 2.0 of the .NET Framework.
The following code shows how you can use a SqlBulkCopy object to copy data from the Store table in the pubs database to the StoreList table:
'VB Protected Sub Button11_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Button11.Click
Dim pubs As ConnectionStringSettings
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection()
connection.ConnectionString = pubs.ConnectionString
Dim bulkCopy As ConnectionStringSettings
bulkCopy = ConfigurationManager.ConnectionStrings("PubsData")
Dim bulkCopyConnection As DbConnection = New SqlConnection()
bulkCopyConnection.ConnectionString = bulkCopy.ConnectionString
Dim cmd As DbCommand = connection.CreateCommand()
cmd.CommandType = CommandType.Text
Lesson 2: Using the ADO.NET Connected Classes
cmd.CommandText = "SELECT stor_name FROM Stores"
connection.Open()
bulkCopyConnection.Open()
'make sure that table exists and is empty
'in case button is clicked more that once
Dim cleanup as SqlCommand = bulkCopyConnection.CreateCommand()
cleanup.CommandText = _
"IF EXISTS ( SELECT * FROM sys.objects " _ + " WHERE object_id = OBJECT_ID('dbo.StoreList') " _ + " AND type in ('U')) " _ + "DROP TABLE dbo.StoreList " _ + "CREATE TABLE dbo.StoreList(stor_name varchar(40) NOT NULL )"
cleanup.ExecuteNonQuery()
'do the bulkcopy
Dim rdr As DbDataReader = cmd.ExecuteReader()
Dim bc As New SqlBulkCopy(bulkCopyConnection)
bc.DestinationTableName = "StoreList"
bc.WriteToServer(rdr)
connection.Close()
bulkCopyConnection.Close()
Dim lbl as Label = GetLabel(275,20)
lbl.Text = "Done with bulk copy"
End Sub //C# protected void Button11_Click(object sender, EventArgs e) { ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"]; DbConnection connection = new SqlConnection(pubs.ConnectionString); ConnectionStringSettings bulkCopy = ConfigurationManager.ConnectionStrings["PubsData"]; SqlConnection bulkCopyConnection = new SqlConnection(bulkCopy.ConnectionString);
DbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT stor_name FROM Stores";
connection.Open();
bulkCopyConnection.Open();
//make sure that table exists and is empty
//in case button is clicked more that once
SqlCommand cleanup = bulkCopyConnection.CreateCommand();
cleanup.CommandText =
"IF EXISTS ( SELECT * FROM sys.objects " + " WHERE object_id = OBJECT_ID('dbo.StoreList') " + " AND type in ('U')) " + "DROP TABLE dbo.StoreList " + "CREATE TABLE dbo.StoreList(stor_name varchar(40) NOT NULL )";
cleanup.ExecuteNonQuery();
4
Using ADO.NET and XML with ASP.NET
//do the bulkcopy
DbDataReader rdr = cmd.ExecuteReader();
SqlBulkCopy bc = new SqlBulkCopy(bulkCopyConnection);
bc.DestinationTableName = "StoreList";
bc.WriteToServer(rdr);
connection.Close();
bulkCopyConnection.Close();
Label lbl = GetLabel(275, 20);
lbl.Text = "Done with bulk copy";
You should consider using the IDbDataReader parameter whenever possible to get the best performance with the least resources used. You can decide how much data should be copied based on the query that you use. For example, the preceding code sample retrieved only the store names and could have had a WHERE clause to further limit the data.
Copyright © OnBarcode.com . All rights reserved.