LINQ To SQL Samples - ADO.NET InteropOn This Page |
This sample uses a pre-existing ADO.NET connection to create a Northwind object that can be used to perform queries, in this case a query to return all orders with freight of at least 500.00.
Public Sub LinqToSqlAdo01()
' Create a standard ADO.NET connection:
Dim nwindConn As SqlConnection = New SqlConnection(My.Settings.NORTHWINDConnectionString1)
nwindConn.Open()
' ... other ADO.NET database access code ... '
' Use pre-existing ADO.NET connection to create DataContext:
Dim interop_db = New NorthwindDataContext(nwindConn) With {.Log = db.Log}
Dim orders = From o In interop_db.Orders _
Where o.Freight > 500D _
Select o
ObjectDumper.Write(orders)
nwindConn.Close()
End Sub
Result:
OrderID=10372 CustomerID=QUEEN EmployeeID=5 OrderDate=12/4/1996 RequiredDate=1/1/1997
ShippedDate=12/9/1996 ShipVia=2 Freight=890.7800 ShipName=Queen Cozinha ShipAddress=Alameda dos Canàrios, 891 ShipCity=Sao Paulo ShipRegion=SP ShipPostalCode=05487-020 ShipCountry=Brazil Order_Details=... Employee={ } Customer={ } Shipper={ }
OrderID=10479 CustomerID=RATTC EmployeeID=3 OrderDate=3/19/1997 RequiredDate=4/16/1997
ShippedDate=3/21/1997 ShipVia=3 Freight=708.9500 ShipName=Rattlesnake Canyon Grocery ShipAddress=2817 Milton Dr. ShipCity=Albuquerque ShipRegion=NM ShipPostalCode=87110 ShipCountry=USA Order_Details=...
Employee={ } Customer={ } Shipper={ }OrderID=10514 CustomerID=ERNSH EmployeeID=3 OrderDate=4/22/1997 RequiredDate=5/20/1997
ShippedDate=5/16/1997
ShipVia=2 Freight=789.9500 ShipName=Ernst Handel ShipAddress=Kirchgasse 6 ShipCity=Graz ShipRegion=null ShipPostalCode=8010 ShipCountry=Austria Order_Details=...
Employee={ } Customer={ } Shipper={ } OrderID=10540 CustomerID=QUICK EmployeeID=3 OrderDate=5/19/1997 RequiredDate=6/16/1997
ShippedDate=6/13/1997 ShipVia=3 Freight=1007.6400 ShipName=QUICK-Stop ShipAddress=Taucherstraße 10 ShipCity=Cunewalde ShipRegion=null ShipPostalCode=01307 ShipCountry=Germany Order_Details=...
Employee={ } Customer={ } Shipper={ }
OrderID=10612 CustomerID=SAVEA EmployeeID=1 OrderDate=7/28/1997 RequiredDate=8/25/1997
ShippedDate=8/1/1997 ShipVia=2 Freight=544.0800 ShipName=Save-a-lot Markets ShipAddress=187 Suffolk Ln. ShipCity=Boise ShipRegion=ID ShipPostalCode=83720 ShipCountry=USA Order_Details=...
Employee={ } Customer={ } Shipper={ }
OrderID=10691 CustomerID=QUICK EmployeeID=2 OrderDate=10/3/1997 RequiredDate=11/14/1997
ShippedDate=10/22/1997 ShipVia=2 Freight=810.0500 ShipName=QUICK-Stop ShipAddress=Taucherstraße 10 ShipCity=Cunewalde ShipRegion=null ShipPostalCode=01307 ShipCountry=Germany Order_Details=...
Employee={ } Customer={ } Shipper={ }
OrderID=10816 CustomerID=GREAL EmployeeID=4 OrderDate=1/6/1998 RequiredDate=2/3/1998
ShippedDate=2/4/1998 ShipVia=2 Freight=719.7800 ShipName=Great Lakes Food Market ShipAddress=2732 Baker Blvd. ShipCity=Eugene ShipRegion=OR ShipPostalCode=97403 ShipCountry=USA Order_Details=...
Employee={ } Customer={ } Shipper={ }
OrderID=10897 CustomerID=HUNGO EmployeeID=3 OrderDate=2/19/1998 RequiredDate=3/19/1998
ShippedDate=2/25/1998
ShipVia=2 Freight=603.5400 ShipName=Hungry Owl All-Night Grocers ShipAddress=8 Johnstown Road ShipCity=Cork ShipRegion=Co. Cork ShipPostalCode=null ShipCountry=Ireland Order_Details=... Employee={ } Customer={ } Shipper={ }
OrderID=10912 CustomerID=HUNGO EmployeeID=2 OrderDate=2/26/1998 RequiredDate=3/26/1998
ShippedDate=3/18/1998 ShipVia=2 Freight=580.9100 ShipName=Hungry Owl All-Night Grocers ShipAddress=8 Johnstown Road ShipCity=Cork ShipRegion=Co. Cork ShipPostalCode=null ShipCountry=Ireland Order_Details=... Employee={ } Customer={ } Shipper={ }
OrderID=10983 CustomerID=SAVEA EmployeeID=2 OrderDate=3/27/1998 RequiredDate=4/24/1998
ShippedDate=4/6/1998 ShipVia=2 Freight=657.5400 ShipName=Save-a-lot Markets ShipAddress=187 Suffolk Ln. ShipCity=Boise ShipRegion=ID ShipPostalCode=83720 ShipCountry=USA Order_Details=...
Employee={ } Customer={ } Shipper={ }
OrderID=11017 CustomerID=ERNSH EmployeeID=9 OrderDate=4/13/1998 RequiredDate=5/11/1998
ShippedDate=4/20/1998 ShipVia=2 Freight=754.2600 ShipName=Ernst Handel ShipAddress=Kirchgasse 6 ShipCity=Graz ShipRegion=null ShipPostalCode=8010 ShipCountry=Austria Order_Details=...
Employee={ } Customer={ } Shipper={ }
OrderID=11030 CustomerID=SAVEA EmployeeID=7 OrderDate=4/17/1998 RequiredDate=5/15/1998
ShippedDate=4/27/1998 ShipVia=2 Freight=830.7500 ShipName=Save-a-lot Markets ShipAddress=187 Suffolk Ln. ShipCity=Boise ShipRegion=ID ShipPostalCode=83720 ShipCountry=USA Order_Details=...
Employee={ } Customer={ } Shipper={ }
OrderID=11032 CustomerID=WHITC EmployeeID=2 OrderDate=4/17/1998 RequiredDate=5/15/1998
ShippedDate=4/23/1998 ShipVia=3 Freight=606.1900 ShipName=White Clover Markets ShipAddress=1029 - 12th Ave. S. ShipCity=Seattle ShipRegion=WA ShipPostalCode=98124 ShipCountry=USA Order_Details=...
Employee={ } Customer={ } Shipper={ }
This sample uses a pre-existing ADO.NET connection to create a Northwind object and then shares an ADO.NET transaction with it. The transaction is used both to execute SQLCommands through the ADO.NET connection and to submit changes through the Northwind object. When the transaction aborts due to a violated check constraint, all changes are rolled back, including both the changes made through the SqlCommand and the changes made through the Northwind object.
Public Sub LinqToSqlAdo02()
Dim q = From p In db.Products _
Where p.ProductID = 3 _
Select p
Console.WriteLine("*** BEFORE ***")
ObjectDumper.Write(q)
Console.WriteLine()
Console.WriteLine("*** INSERT ***")
' Create a standard ADO.NET connection:
Dim nwindConn As SqlConnection = New SqlConnection(My.Settings.NORTHWINDConnectionString1)
nwindConn.Open()
' Use pre-existing ADO.NET connection to create DataContext:
Dim interop_db As NorthwindDataContext = New NorthwindDataContext(nwindConn)
interop_db.Log = db.Log
Dim nwindTxn As SqlTransaction = nwindConn.BeginTransaction()
Try
Dim cmd As SqlCommand = New SqlCommand("UPDATE Products SET QuantityPerUnit = 'single item' WHERE ProductID = 3")
cmd.Connection = nwindConn
cmd.Transaction = nwindTxn
cmd.ExecuteNonQuery()
interop_db.Transaction = nwindTxn
Dim prod1 As Product = (From p In interop_db.Products _
Where p.ProductID = 4 _
Select p).First()
Dim prod2 As Product = (From p In interop_db.Products _
Where p.ProductID = 5 _
Select p).First()
prod1.UnitsInStock = New Short?(prod1.UnitsInStock.Value - 3)
prod2.UnitsInStock = New Short?(prod2.UnitsInStock.Value - 5) ' ERROR: this will make the units in stock negative
interop_db.SubmitChanges()
nwindTxn.Commit()
Catch e As Exception
' If there is a transaction error, all changes are rolled back,
' including any changes made directly through the ADO.NET connection
Console.WriteLine(e.Message)
Console.WriteLine("Error submitting changes... all changes rolled back.")
End Try
nwindConn.Close()
Console.WriteLine()
Console.WriteLine("*** AFTER ***")
clearDBCache()
ObjectDumper.Write(q)
Cleanup101() ' Restore previous database state
End Sub
Result:
*** BEFORE ***
ProductID=3 ProductName=Aniseed Syrup SupplierID=1 CategoryID=2 QuantityPerUnit=12 - 550 ml bottles UnitPrice=10.0000 UnitsInStock=13 UnitsOnOrder=70 ReorderLevel=25
Discontinued=False Order_Details=... Category={ } Supplier={ }
*** INSERT ***
The UPDATE statement conflicted with the CHECK constraint "CK_UnitsInStock". The conflict occurred in database "CE559A7E617443375E2DBA1456355C1C_L STUDIO 2008\PROJECTS\SAMPLEQUERIESTOCONTENT\SAMPLE QUERIES\BIN\DEBUG\SAMPLEDATA\NORTHWIND.MDF", table "dbo.Products", column 'UnitsInStock'.
The statement has been terminated.
Error submitting changes... all changes rolled back.
*** AFTER ***
ProductID=3 ProductName=Aniseed Syrup SupplierID=1 CategoryID=2 QuantityPerUnit=12 - 550 ml bottles UnitPrice=10.0000 UnitsInStock=13 UnitsOnOrder=70 ReorderLevel=25
Discontinued=False Order_Details=... Category={ } Supplier={ }
