Optimization of. Net's frequent access to the database (1). net's access to data

Optimization of. Net's frequent access to the database (1). net's access to data

Knowledge points: DataTable, Linq, lamda expressions, Cache

Scenario: Query all employees and administrators of a department and assign corresponding permissions.

The implementation process is generally: Query departments, traverse departments (query employees, assign permissions, query administrators, assign permissions)

Frequently accessing databases involves traversing the employees and administrators in the Department, and we try to optimize them here.

1. all users are read and stored in the DataTable object. The next time you can query data directly from the DataTable without having to read the database, even if you store the data in the DataTable object, the DataTable object is also lost when the page is refreshed or sent back, so the DataTable object is saved.. Net Cache. (This is a qualitative difference from Session storage ).

Code:

if (Page.Cache["users"] == null)     Page.Cache.Insert("users", UsersCom.GetSimpleUser(), null, System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(20));DataTable dt = (DataTable)Page.Cache["users"];

The last two parameters of Page. Cache. Insert indicate the Cache expiration time;

UsersCom. GetSimpleUser () is a method for querying data from the database, and a DataTable object is returned. Try not to return all the fields, but the required fields. The returned fields include user_id and unit_id.

2. query data from the Cache, that is, querying the able object. There are many ways to query data, starting from the simplest.

Code:

dt.Select("unit_id=" + unitID);

Directly query data using the Select method of DataTable and return the DataRow array. If you do not want to obtain the DataRow array, You have to traverse the array and reassemble it into other objects.

This efficiency may not be highly efficient when using Linq. In this case, use the Lamda expression directly!

Code:

dt.AsEnumerable().Where(t => t.Field<decimal>("unit_id") == unitID).Select(t => t.Field<decimal>("user_id")).ToList();

Returns the List <decimal> object;

Note: You need using System. Linq; and reference System. Data. DataSetExtensions in the solution (you do not need to using in the Code ).

Of course, if you want to use Linq, the Code is as follows:

(from t in dt.AsEnumerable()where t.Field<decimal>("unit_id") == unitIDselect t.Field<decimal>("user_id")).ToList();

Returns the List <decimal> object;

3. Use multithreading as appropriate

In this scenario, the instance runs for a long time, so a new thread is enabled. It also avoids the waiting of the UI thread.

You can encapsulate a thread execution class and save the Class Object to the Session to check the running status at any time.

Code:

/// <Summary> // Asp.net thread execution class // </summary> public class DocWork {// <summary> /// constructor /// </summary> /// <param name = "method"> execution method </param> public DocWork (Action method) {this. method = method;} private DateTime startTime; // <summary> // start time // </summary> public DateTime StartTime {get {return startTime ;}} private DateTime endTime; // <summary> // End Time // </summary> public DateTim E EndTime {get {return endTime;} private DateTime errorTime; /// <summary> /// error time /// </summary> public DateTime ErrorTime {get {return errorTime;} private Thread currnetThread; /// <summary> /// current Thread /// </summary> public Thread CurrnetThread {get {return currnetThread;} private string errorInfo; /// <summary> // error message // </summary> public string ErrorInfo {get {return error Info ;}} private Action method; // <summary >/// execution Method // </summary> public Action method {get {return method ;} set {method = value ;}/// <summary> /// Start execution /// </summary> public void Start () {if (currnetThread = null | currnetThread. threadState = ThreadState. stopped) {currnetThread = new Thread (new ThreadStart (WorkMethod); currnetThread. start () ;}/// <summary> /// suspends the thread // </sum Mary> public void Suspend () {if (currnetThread! = Null & currnetThread. threadState = ThreadState. running) {currnetThread. suspend () ;}/// <summary> /// continue to execute the suspended thread // </summary> public void Resume () {if (currnetThread! = Null & currnetThread. threadState = ThreadState. suincluded) {currnetThread. resume () ;}/// <summary> // terminate the execution /// </summary> public void Abort () {if (currnetThread! = Null) {currnetThread. Abort () ;}/// <summary> // thread Method /// </summary> protected void WorkMethod () {if (Method! = Null) {try {startTime = DateTime. now; Method. invoke ();} catch (Exception ex) {errorTime = DateTime. now; errorInfo = ex. message + "|" + ex. source;} finally {endTime = DateTime. now ;}}}}

There are many methods to optimize data access, and the methods for different links are also different. This article demonstrates the process of querying the database data to the page.

Address of this article:. Net Optimization of frequent database access reprinted please indicate the source