Skip to main content

LINQ To SQL Samples - ADO.NET Interop


Connection Interop

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={ }


Transaction Interop

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={ }