Shulou(Shulou.com)06/02 Report--

This article is about how to connect to ADO.NET. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Connect

When you create a LINQ to SQL DataContext, you can provide an existing ADO.NET connection. The connection provided is used for all operations on DataContext, including queries. If the connection is already open, LINQ to SQL will keep it open when you finish using the connection. We can always access this connection, and we can also close it by ourselves using the Connection property.

/ / create a new standard ADO.NET connection: SqlConnection nwindConn = new SqlConnection (connString); nwindConn.Open (); / /. Other ADO.NET data manipulation codes. / / use the existing ADO.NET connection to create a DataContext: Northwind interop_db = new Northwind (nwindConn); var orders = from o in interop_db.Orders where o.Freight > 500.00M select o; / / return order nwindConn.Close with Freight > 500.00M ()

Statement description: this example creates a Northwind object using a pre-existing ADO.NET connection, and the query in this case returns all orders with a shipping cost of at least 500.00.

two。 Business

We can provide this transaction to DataContext when we have started our own database transaction and we want DataContext to be included.

The * method for creating transactions through the .NET Framework is to use the TransactionScope object. By using this approach, we can create distributed transactions that are executed across databases and other resource managers that reside in memory. Transaction scope can be started with few resources. They promote themselves to distributed transactions only if there are multiple connections within the transaction scope.

Using (TransactionScope ts = new TransactionScope ()) {db.SubmitChanges (); ts.Complete ();}

Note: you cannot use this method for all databases. For example, a SqlClient connection cannot promote system transactions when used against a SQL Server 2000 server. The approach it takes is that whenever it finds a situation in which transaction scope is used, it automatically enlists the full distributed transaction.

Let's use an example to illustrate the use of transactions. Here, it also illustrates the reuse of the same connection between the ADO.NET command and DataContext.

Var Q = from p in db.Products where p.ProductID = = 3 select p; / / query out using LINQ to SQL / / create a new standard ADO.NET connection: SqlConnection nwindConn = new SqlConnection (connString); nwindConn.Open (); / / create a DataContext using the existing ADO.NET connection: Northwind interop_db = new Northwind (nwindConn); SqlTransaction nwindTxn = nwindConn.BeginTransaction () Try {SqlCommand cmd = new SqlCommand ("UPDATE Products SET" + "QuantityPerUnit = 'single item' WHERE ProductID = 3"); cmd.Connection = nwindConn; cmd.Transaction = nwindTxn; cmd.ExecuteNonQuery (); interop_db.Transaction = nwindTxn; Product prod1 = interop_db.Products.First (p = > p.ProductID = = 4); Product prod2 = interop_db.Products.First (p = > p.ProductID = = 5); prod1.UnitsInStock-= 3; prod2.UnitsInStock-= 5 / / there is an error, which cannot be negative interop_db.SubmitChanges (); nwindTxn.Commit ();} catch (Exception e) {/ / if there is an error, all operations roll back Console.WriteLine (e.Message);} nwindConn.Close ()

Statement description: this example creates a Northwind object using a pre-existing ADO.NET connection and then shares an ADO.NET transaction with this object. This transaction is used both to execute SQL commands over an ADO.NET connection and to commit changes through a Northwind object. When a transaction is aborted due to a violation of CHECK constraints, all changes are rolled back, including changes made through SqlCommand, as well as changes made through the Northwind object.

Thank you for reading! This is the end of the article on "how to connect ADO.NET". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.