Microsoft Enterprise Library 5.0 series (5) Data Access Application Block

The enterprise database access module uses the abstract factory mode to allow users to select different databases through simple configuration.ProgramBecause I only installed SQL Server 2005 on the local machine, so here only do the SQL demonstration, you need to study friends can visit the following websites:

Http://msdn.microsoft.com/en-us/library/ff664408%28v=PandP.50%29.aspx

Functions of the enterprise database access module:

1. The simplest function is to execute SQL statements using the executenonquery. method.

2. Execute executedataset and return a dataset of the dataset type.

3. Execute executescalar to obtain the information in the first column of the First row returned.

4. Execute the stored procedure.

5. PassCodeImplement transactions.

6. update the database through dataset.

7. XML-based return values.

8. Visualize the returned data.

9. Access the database asynchronously.

I will introduce the above functions one by one below. I have packaged the test program. You can click here to download it.

The following describes how to use the database access module in Microsoft Enterprise Library 5.0.

1. first, create a test database. The SQL file for creating the database is packaged in the compressed package. You can click the download link above to download the database. after the SQL file is executed, we can see the created testdb database:

2. Download and install microsoftenterprise library 5.0,and then run entlibconfig.exe, selectBlocksMenu, clickAdddatabase settings.

3. after the file is configured, save it as an app. config File and add it to the created application. and add the corresponding references. Here I will not talk about it more. You can see it by downloading the packaged program.

4. The following describes the functions I have implemented in the application:

(1) executenonquery. Method:

 
/// <Summary>
/// Execute executenonquery
/// </Summary>
Private void executenonquery_click (Object sender, eventargs E)
{
DB. executenonquery (commandtype. Text, "insert into [College] ([collegeid], [name]) values (6, 'School of sports ')");
}
 

(2) execute executedataset and return a dataset of the dataset type.

 
/// <Summary>
/// Execute executedataset and return to the college list
/// </Summary>
/// <Returns> </returns>
Private void executedataset_click (Object sender, eventargs E)
{
String SQL = "select * from college ";
Dbcommand DW = dB. getsqlstringcommand (SQL );

Datagridview1.datasource = dB. executedataset (DW). Tables [0];
}
 

(3) execute executescalar and return the value in the first column of the first row.

/// <Summary>
/// Execute executescalar to return the value of the first column in the first row
/// </Summary>
/// <Returns> </returns>
Private void executescalar_click (Object sender, eventargs E)
{
Database DB = databasefactory. createdatabase ("connectionstring ");

String SQL = "select [name] From college where [collegeid] = 1 ";
Dbcommand Dc = dB. getsqlstringcommand (SQL );
String STR = "obtained school name:" + (string) dB. executescalar (DC );
MessageBox. Show (STR );

SQL = "select [collegeid] From college where [collegeid] = 1 ";
Dc = dB. getsqlstringcommand (SQL );
STR = "The obtained school ID is:" + (INT) dB. executescalar (DC );
MessageBox. Show (STR );
}
 

(4) execute the stored procedure.

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
Private void storedproccommand_click (Object sender, eventargs E)
{
Dbcommand Dc = dB. getstoredproccommand ("usp_college_loadbyid ");

DB. addinparameter (DC, "@ collegeid", system. Data. dbtype. int32, 5 );

Datagridview1.datasource = dB. executedataset (dc). Tables [0];
}
 

(5) Implement transactions through code.

/// <Summary>
/// Transaction
/// </Summary>
Private void transaction_click (Object sender, eventargs E)
{
Dbcommand DC1 = dB. getstoredproccommand ("usp_college_insert ");

DB. addinparameter (DC1, "@ collegeid", dbtype. int32, 7 );
DB. addinparameter (DC1, "@ name", dbtype. String, "文 ");

Dbcommand DC2 = dB. getstoredproccommand ("usp_college_insert ");

DB. addinparameter (DC2, "@ collegeid", dbtype. int32, 7 );
DB. addinparameter (DC2, "@ name", dbtype. String, "Emy of Chemical Engineering ");

Using (dbconnection conn = dB. createconnection ())
{
Conn. open ();
Dbtransaction trans = conn. begintransaction ();

Try
{
// Add a school with ID 7
DB. executenonquery (DC1, trans );

// Add a school with ID 7. The primary key is repeated and the transaction will be rolled back.
DB. executenonquery (DC2, trans );

// Submit the transaction.
Trans. Commit ();
}
Catch
{
// Rollback
Trans. rollback ();
}
Conn. Close ();
}

// Check the database. If the data is not added, the transaction has been rolled back.
Executedataset_click (null, null );
}

(6) update the database through dataset.

/// <Summary>
/// Update the database through Dataset
/// </Summary>
Private void datasetupdate_click (Object sender, eventargs E)
{
Dataset productsdataset = new dataset ();

String SQL = "select * from college ";
Dbcommand cmd = dB. getsqlstringcommand (SQL );

String collegetablename = "college ";

// Restore original data
DB. loaddataset (CMD, productsdataset, collegetablename );

// Obtain a data table
Datatable dtable = productsdataset. Tables [collegetablename];

// Add a new message to Dataset
Datarow addedrow = dtable. Rows. Add (new object [] {8, "Emy of foreign languages "});

// Modify an existing data
Dtable. Rows [0] ["name"] = "Emy of Education ";

// Provides the insert, update, and delete stored procedures.
Dbcommand insertcommand = dB. getstoredproccommand ("usp_college_insert ");
DB. addinparameter (insertcommand, "@ collegeid", dbtype. int32, "collegeid", datarowversion. Current );
DB. addinparameter (insertcommand, "@ name", dbtype. String, "name", datarowversion. Current );

Dbcommand deletecommand = dB. getstoredproccommand ("usp_college_delete ");
DB. addinparameter (deletecommand, "@ collegeid", dbtype. int32, "collegeid", datarowversion. Current );

Dbcommand updatecommand = dB. getstoredproccommand ("usp_college_update ");
DB. addinparameter (updatecommand, "@ collegeid", dbtype. int32, "collegeid", datarowversion. Current );
DB. addinparameter (updatecommand, "@ name", dbtype. String, "name", datarowversion. Current );

// Update the database through Dataset
Int rowsaffected = dB. updatedataset (productsdataset, collegetablename, insertcommand, updatecommand, deletecommand,
Microsoft. Practices. enterpriselibrary. Data. updatebehavior. Standard );

MessageBox. Show ("affected rows:" + rowsaffected );
}

(7) XML-based return values.

/// <Summary>
/// XML-based return values
/// </Summary>
Private void returnxml_click (Object sender, eventargs E)
{
// Use the "for XML auto" parameter to make SQL return XML format information
Sqldatabase sqldb = (sqldatabase) databasefactory. createdatabase ("connectionstring ");

Dbcommand cmd = sqldb. getsqlstringcommand ("select * from college for XML auto ");
Ienumerable <string> productlist;

Using (VAR reader = sqldb. executexmlreader (CMD ))
{
If (reader. isstartelement ())
{
VaR root = (xelement) xnode. readfrom (Reader );
Productlist = root. Elements ("collegeid ")
. Attributes ("name ")
. Select (A => A. Value). toarray ();

MessageBox. Show (xelement) root). tostring ());
}
}
}
 

(8) visualize the returned data.

/// <Summary>
/// Dataasobject
/// </Summary>
Private void dataasobject_click (Object sender, eventargs E)
{
// Visualize the returned data
VaR Results = dB. executesprocaccessor <College> ("usp_college_loadall ");

MessageBox. Show (results. elementat (0). tostring ());
}
 

(9) access the database asynchronously.

/// <Summary>
/// Access the database Asynchronously
/// </Summary>
Private void async_click (Object sender, eventargs E)
{
// Create a new database connection. The attribute must be added: asynchronous Processing = true.
String connectionstring = @ "Server = (local); database = testdb; Integrated Security = true; asynchronous Processing = true ";
Database sqldb = new sqldatabase (connectionstring );
Dbcommand cmd = sqldb. getstoredproccommand ("usp_college_loadbyid ");
Sqldb. addinparameter (CMD, "@ collegeid", dbtype. int32, 1 );

Try
{
Iasyncresult result = sqldb. beginexecutereader (CMD, myendexecutecallback, sqldb );
}
Catch (exception ex)
{
MessageBox. Show (ex. tostring ());
}
}

// Execute this function after obtaining it.
Private void myendexecutecallback (iasyncresult result)
{
Try
{
Database sqldb = (database) result. asyncstate;
Idatareader reader = dB. endexecutereader (result );

College c = New College (INT) Reader [0], (string) Reader [1]);

MessageBox. Show (C. tostring ());
}
Catch (exception ex)
{
MessageBox. Show (ex. tostring ());
}
}

Source: http://www.cnblogs.com/huangcong/archive/2010/05/31/1748672.html please enter

The copyright of this article is shared by the author and the blog. You are welcome to repost this article, but you must keep this statement without the author's consent andArticleThe original text connection is clearly displayed on the page. Otherwise, the legal liability is retained.