Database Advanced Applications

Advanced | data | database
One, Connection pool

1, Features:

①, Advantages: Performance

②, disadvantage: There may be many unused connections, resource waste

2, ADO. NET Connection Pool

① each. NET data provider that is contained in Ado.net can implement connection pooling.

② Each connection pool is associated with a separate connection string and its transaction context. Each time a new connection is opened, the data provider attempts to match the specified connection string to the string of the connection pool. If it fails, create a new connection and join the connection pool.

After the ③ connection pool is created, the system creates some connection objects and joins them into the connection pool until the minimum number of connected objects is reached. Create a new connection later, as needed, until the maximum number of connections is reached.

④.net The default is to use connection pooling. If you want to disable, you can use the following methods:

Ⅰ, when using the SqlConnection object, add in the connection string: Pooling = False

Ⅱ, when using the OleDbConnection object, join in the connection string: OLE DB Services =-4

3. Tips and Tricks

① open connection should be late, close the connection should be early

② ensure that all user-defined transactions are turned off before closing the database connection

③ at least one connection is guaranteed to be available in the connection pool

Second, the cache

1, features

①, Benefits: Improve performance, stability, usability

②, ASP. NET cache

Ⅰ, in ASP.net, provides a cache object specifically for caching data, and it is scoped to application domains. Lifetimes are closely related to applications that recreate the cache object every time the application is started, recreating the cache object whenever the application is started. The main difference between it and the Application object is that it provides performance specifically for cache management, such as dependencies and expiration policies.

Ⅱ The cache object is defined in the System.Web.Caching namespace, you can use the Cache property of the HttpContext class or the Cache property of the Page object to get the cache reference, in addition to storing the key value, the cache object can store the objects of the. NET Framework.

2. Dependencies and expiration policies

① file Policy: Force the removal of cached data when one (certain) file on the hard disk changes

CacheDependency cdependency = new CacheDependency (Server.MapPath ("Authors.xml"));

Cache.Insert ("Cacheditem", item,cdependency);

② Key Value dependency: Specifies that a data item in the cache is removed when it is changed.

Create a cache entry

cache["Key1"]= "Value1";

Make Key2 dependent on Key1

string[] Dependencykey = new String[1];

Dependencykey[0] = "Key1";

CacheDependency dependency = new CacheDependency (Null,dependencykey);

Cache.Insert ("Key2", "Value2", dependency);

③ Expiration policy: Absolute and relative

Absolute expiration

Cache.Insert ("Cacheditem", Item,null,datetime.now,addseconds (5), cache.noslidingexpiration);

Sliding expiration

Cache.Insert ("", Item,null,cache.noabsoluteexpiration,timespan.fromseconds (5));

④ database dependencies (not recommended): cache invalidation if the relevant data in the database has changed

3, the use of caching: Because the data will expire, the use of caching must check the validity of the data

String data = (string) cache["myitem"];

if (data==null)

{

Data=getdata ();

Cache.Insert ("myitem", data);

}

4, Cache callback: When the cache is invalidated, automatically call

Cacheitemremovecallback onremove = new Cacheitemremovedcallack (this. Removecallback);

Cache.Insert ("Cacheditem", Item,null,cache.noabsoluteexpiration,cache.noslidingexpiration, Cacheitempriority.default,onremove);

Implement the function to handle the expiration of the cache.

public void RemovedCallback (string key,obejct Value,cacheitemremonvedreason R)

{

Test whether the item is expired and reinsert it into the cache.

if (r==cacheitemremovedreason.expired)

{

Reinsert it into the cache again.

CacheItemRemovedCallback OnRemove = = null;

OnRemove = new Cacheitemremovecallback (this. RemovedCallback);

Cache.Insert (Key,value,null,cache.noabsoluteexpiration,cache.noslidingexpiration,cacheitempriority.default, OnRemove);

}

}

5, Cache Priority: When running the application server is not enough memory, will automatically clear the data in the cache, known as "clear scavenging"

Cache.Insert ("DSN", connectionstring,null,d,t,cacheitempriority.high,onremove);

6. Using the ASP.net cache in non-web projects

The Httpruntime.cache object can exist in every application domain other than aspnet_wp.exe.

HttpRuntime httprt = new HttpRuntime ();

Cache cache = Httpruntime.cache;

Third, the business

1. Write directly to SQL: Use the BEGIN Trans,commit Trans,rollback Trans implementation in the stored procedure.

Begin Trans

Declare @orderDetailsError int, @productError int

Delete from ' Order Details ' where ProductID = 42

Select @orderDetailsError = @ @Error

Delete from the products where ProductID = 42

Select @productError = @ @Error

If @orderDetailsError = 0 and @productError = 0

Commit Trans

Else

RollBack Trans

Advantage: All transaction logic is contained in a separate call

Have the best performance for running a transaction

Independent of application

Limit: Transaction context exists only in database calls

Database code is related to the database system

2, the use of ado.net implementation: can be in the middle tier to manage transactions. The SqlConnection and OleDbConnection objects have a BeginTransaction method that can return SqlTransaction or OleDbTransaction objects.

cn. Open ();

SqlTransaction trans = cn. BeginTransaction ();

SqlCommand cmd = new SqlCommand ();

Try

{

Cmd.commandtext = "Delete [order Details] where ProductID = 23";

Cmd. ExecuteNonQuery ();

Cmd.commandtext = "Delete products where ProductID = 23";

Cmd. ExecuteNonQuery ();

Trans.commit ();

}

Catch (Exception e)

Trans. Rollback ();

Finally

cn. Close ();

Advantages: simplicity; Almost as fast as database transactions; independent of database

Disadvantage: Transactions cannot be connected across multiple databases;

Transactions are performed on the database connection layer, so a database connection needs to be maintained during the transaction;

Iv. Distributed Transactions

1, Features:

To participate in COM + transactions,. NET class must be inherited from the System.EnterpriseServices.ServicedComponent class.

You can get information about COM + object context by using the System.EnterpriseServices.ContextUtil class, which provides the SetComplete and SetAbort methods to display the commit and rollback transactions separately.

Advantage: You can only use DTC or COM + transactions in a system that requires transactions to run across MSMQ and other identifiable transaction resources. The DTC coordinates all the resource managers involved in the distributed transaction and also manages transaction-related operations.

Disadvantage: Performance is reduced due to the existence of DTC and COM interoperability overhead.

2. Transaction type:

①, automatic transactions: using the System.EnterpriseServices.AutoComplete property

[Transaction (transactionoption.required)]

public class Class1:servicedcomponent

{

[AutoComplete]

public void Example ()

{}

}

②, manual transactions

public string Transfermoneyfrombtoa (double m)

{

Try

{

Contextutil.enablecommit ();

This. Transferoutfromb (m);

This. Transferintoa (m);

ContextUtil.SetComplete ();

}

catch (Exception err)

{

ContextUtil.SetAbort ();

}

}

3, the way to choose

For the following scenario, manual transactions are used: Transactions are performed on a single database;

For the following scenarios, you should use automatic transaction processing:

①, the need to extend a single transaction processing to multiple remote databases;

②, requiring a single transaction to have multiple resource managers (such as databases and Windows2000 Message Queuing resource managers);

Note: Avoid a mixed transaction model, preferably one.