Today, I learned about the data access application block of Enterprise library2.0. The Data Access Application Block provides a common data access function, which has greatly changed with the release of Enterprise library2.0. I wrote more about the free switch between SQL and access databases. Code Shared. Read the original text and then pick up the code.
I. Improvement
In daab1.1, we know that the database method returns or creates a dbcommandwrapper object. In daab2.0, The dbcommandwrapper class is removed and ADO is used. the dbcommand class in net2.0 replaces the implementation of similar functions, so that daab and ours. net class library is more closely integrated. Let's recall the code we used dbcommandwrapper in 1.1 to access data:
Database DB = databasefactory. createdatabase ();
Dbcommandwrapper dbcommand = dB. getstoredproccommandwrapper ("getproductsbycategory ");
Dbcommand. addinparameter ("categoryid", dbtype. int32, category );
Dataset productdataset = dB. executedataset (dbcommand );
After using the new dbcommand class, it becomes:
Database DB = databasefactory. createdatabase ();
Dbcommand = dB. getstoredproccommand ("getproductsbycategory ");
DB. addinparameter (dbcommand, "categoryid", dbtype. int32, category );
Dataset productdataset = dB. executedataset (dbcommand );
Database connection strings are always indispensable in our database-based development. However, in daab1.1, the strings used by the database are the same as those used by us. the connection strings used in the. NET class library cannot be shared. They are stored in different locations. In the Data Access Application Block of 2.0, ADO is used. in the <connectionstrings> Configuration area of net2.0, the advantage of this is that the connection string can be in the Application Block and custom.. Net classes are shared using this configuration area, for example:
<Connectionstrings>
<Add
Name = "dataaccessquickstart"
Providername = "system. Data. sqlclient"
Connectionstring = "Server = (local) \ sqlexpress; database = entlibquickstarts; Integrated Security = true"/>
</Connectionstrings> in. net2.0, generic programming has become the core, and a genericdatabase object has been added to daab of version 2.0. Although the daab already contains sqldatabase and orcaledatabase, if we need to use other databases such as DB2, we need to use genericdatabase, which can be used for any.. Net class library, including odbcprovider and oledbprovider.
Ii. Example
The configuration of daab2.0 is very simple, mainly including the following Configuration:
Configure the connection string
Configure the default database
Add related namespaces:
Using Microsoft. Practices. enterpriselibrary. Data;
Using system. Data;
Data Access Application Block is generally divided into three steps:
1. Create a database object
2. Provides command parameters, if needed
3. execute commands
The following are examples of dataaccessquickstart:
Execute static SQL statements
Public String getcustomerlist ()
{
// Create a database object
Database DB = databasefactory. createdatabase ();
// Create a dbcommand object using SQL statements
String sqlcommand = "select customerid, name, address, city, country, postalcode" +
"From MERs ";
Dbcommand = dB. getsqlstringcommand (sqlcommand );
Stringbuilder readerdata = new stringbuilder ();
// Call the executereader Method
Using (idatareader datareader = dB. executereader (dbcommand ))
{
While (datareader. Read ())
{
// Get the value of the 'name' column in The datareader
Readerdata. append (datareader ["name"]);
Readerdata. append (environment. newline );
}
}
Return readerdata. tostring ();
}
Execute the stored procedure and pass parameters, and return Dataset
Public dataset getproductsincategory (INT category)
{
// Create the database object, using the default database service.
// Default database service is determined through configuration.
Database DB = databasefactory. createdatabase ();
String sqlcommand = "getproductsbycategory ";
Dbcommand = dB. getstoredproccommand (sqlcommand );
// Retrieve products from the specified category.
DB. addinparameter (dbcommand, "categoryid", dbtype. int32, category );
// Dataset that will hold the returned results
Dataset productsdataset = NULL;
Productsdataset = dB. executedataset (dbcommand );
// Note: connection was closed by executedataset method call
Return productsdataset;
}
Update Data Using Dataset
Public int updateproducts ()
{
// Create the database object, using the default database service.
// Default database service is determined through configuration.
Database DB = databasefactory. createdatabase ();
Dataset productsdataset = new dataset ();
String sqlcommand = "select productid, productname, categoryid, unitprice, lastupdate" +
"From products ";
Dbcommand = dB. getsqlstringcommand (sqlcommand );
String productstable = "Products ";
// Retrieve the initial data
DB. loaddataset (dbcommand, productsdataset, productstable );
// Get the table that will be modified
Datatable table = productsdataset. Tables [productstable];
// Add a new product to existing Dataset
Datarow addedrow = table. Rows. Add (new object [] {dbnull. value, "new product", 11, 25 });
// Modify an existing product
Table. Rows [0] ["productname"] = "modified product ";
// Establish our insert, delete, and update commands
Dbcommand insertcommand = dB. getstoredproccommand ("addproduct ");
DB. addinparameter (insertcommand, "productname", dbtype. String, "productname", datarowversion. Current );
DB. addinparameter (insertcommand, "categoryid", dbtype. int32, "categoryid", datarowversion. Current );
DB. addinparameter (insertcommand, "unitprice", dbtype. Currency, "unitprice", datarowversion. Current );
Dbcommand deletecommand = dB. getstoredproccommand ("deleteproduct ");
DB. addinparameter (deletecommand, "productid", dbtype. int32, "productid", datarowversion. Current );
Dbcommand updatecommand = dB. getstoredproccommand ("updateproduct ");
DB. addinparameter (updatecommand, "productid", dbtype. int32, "productid", datarowversion. Current );
DB. addinparameter (updatecommand, "productname", dbtype. String, "productname", datarowversion. Current );
DB. addinparameter (updatecommand, "lastupdate", dbtype. datetime, "lastupdate", datarowversion. Current );
// Submit the dataset, capturing the number of rows that were affected
Int rowsaffected = dB. updatedataset (productsdataset, "Products", insertcommand, updatecommand,
Deletecommand, updatebehavior. Standard );
Return rowsaffected;
}
Obtain Record details by ID
Public String getproductdetails (INT productid)
{
// Create the database object, using the default database service.
// Default database service is determined through configuration.
Database DB = databasefactory. createdatabase ();
String sqlcommand = "getproductdetails ";
Dbcommand = dB. getstoredproccommand (sqlcommand );
// Add paramters
// Input parameters can specify the input value
DB. addinparameter (dbcommand, "productid", dbtype. int32, productid );
// Output Parameters specify the size of the return data
DB. addoutparameter (dbcommand, "productname", dbtype. String, 50 );
DB. addoutparameter (dbcommand, "unitprice", dbtype. Currency, 8 );
DB. executenonquery (dbcommand );
// Row of data is captured via output parameters
String Results = string. Format (cultureinfo. currentculture, "{0}, {1}, {2: c }",
DB. getparametervalue (dbcommand, "productid "),
DB. getparametervalue (dbcommand, "productname "),
DB. getparametervalue (dbcommand, "unitprice "));
Return results;
}
Return data in XML format
Public String getproductlist ()
{
// Use a named database instance that refers to a SQL Server database.
Sqldatabase dbsql = databasefactory. createdatabase () as sqldatabase;
// Use "for XML auto" to have SQL return XML data
String sqlcommand = "select productid, productname, categoryid, unitprice, lastupdate" +
"From products for XML auto ";
Dbcommand = dbsql. getsqlstringcommand (sqlcommand );
Xmlreader productsreader = NULL;
Stringbuilder productlist = new stringbuilder ();
Try
{
Productsreader = dbsql. executexmlreader (dbcommand );
// Iterate through the xmlreader and put the data into our results.
While (! Productsreader. EOF)
{
If (productsreader. isstartelement ())
{
Productlist. append (productsreader. readouterxml ());
Productlist. append (environment. newline );
}
}
}
Finally
{
// Close the reader.
If (productsreader! = NULL)
{
Productsreader. Close ();
}
// Explicitly close the connection. The connection is not closed
// When the xmlreader is closed.
If (dbcommand. connection! = NULL)
{
Dbcommand. Connection. Close ();
}
}
Return productlist. tostring ();
}
Use transactions
Public bool transfer (INT transactionamount, int sourceaccount, int destinationaccount)
{
Bool result = false;
// Create the database object, using the default database service.
// Default database service is determined through configuration.
Database DB = databasefactory. createdatabase ();
// Two operations, one to credit an account, and one to debit another
// Account.
String sqlcommand = "creditaccount ";
Dbcommand creditcommand = dB. getstoredproccommand (sqlcommand );
DB. addinparameter (creditcommand, "accountid", dbtype. int32, sourceaccount );
DB. addinparameter (creditcommand, "amount", dbtype. int32, transactionamount );
Sqlcommand = "debitaccount ";
Dbcommand debitcommand = dB. getstoredproccommand (sqlcommand );
DB. addinparameter (debitcommand, "accountid", dbtype. int32, destinationaccount );
DB. addinparameter (debitcommand, "amount", dbtype. int32, transactionamount );
Using (dbconnection connection = dB. createconnection ())
{
Connection. open ();
Dbtransaction transaction = connection. begintransaction ();
Try
{
// Credit the first account
DB. executenonquery (creditcommand, transaction );
// Debit the Second Account
DB. executenonquery (debitcommand, transaction );
// Commit the transaction
Transaction. Commit ();
Result = true;
}
Catch
{
// Rollback transaction
Transaction. rollback ();
}
Connection. Close ();
Return result;
}
}
Iii. Common functions
1. Create a database object
Create a default database object
Database dbsvc = databasefactory. createdatabase ();
The default database is in the configuration file:
<Dataconfiguration defaultdatabase = "dataaccessquickstart"/>
Create an instance database object
// Use a named database instance that refers to an arbitrary database type,
// Which is determined by configuration information.
Database mydb = databasefactory. createdatabase ("dataaccessquickstart ");
Create a specific type of database object
// Create a SQL database.
Sqldatabase dbsql = databasefactory. createdatabase ("dataaccessquickstart") as sqldatabase;
2. Create a dbcommand object
Create a dbcommand for a static SQL statement
Database DB = databasefactory. createdatabase ();
String sqlcommand = "select customerid, lastname, firstname from customers ";
Dbcommand = dB. getsqlstringcommand (sqlcommand );
Create a dbcommand
Database DB = databasefactory. createdatabase ();
Dbcommand = dB. getstoredproccommand ("getproductsbycategory ");
3. Manage objects
After the connection object is opened, you do not need to connect again.
Database DB = databasefactory. createdatabase ();
String sqlcommand = "select productid, productname from products ";
Dbcommand = dB. getsqlstringcommand (sqlcommand );
// No need to open the connection; just make the call.
Dataset customerdataset = dB. executedataset (dbcommand );
Use Using to release objects early
Database DB = databasefactory. createdatabase ();
Dbcommand = dB. getsqlstringcommand ("Select name, address from MERs ");
Using (idatareader datareader = dB. executereader (dbcommand ))
{
// Process results
}
4. Parameter Processing
The database class provides the following methods for parameter processing:
Addparameter. Pass parameters to the stored procedure
Addinparameter. Pass the input parameters to the stored procedure
Addoutparameter. Pass the output parameters to the stored procedure
Getparametervalue. Get the value of the specified parameter.
Setparametervalue. Set the parameter value
Example:
Database DB = databasefactory. createdatabase ();
String sqlcommand = "getproductdetails ";
Dbcommand = dB. getstoredproccommand (sqlcommand );
DB. addinparameter (dbcommand, "productid", dbtype. int32, 5 );
DB. addoutparameter (dbcommand, "productname", dbtype. String, 50 );
DB. addoutparameter (dbcommand, "unitprice", dbtype. Currency, 8 );
Database DB = databasefactory. createdatabase ();
Dbcommand insertcommand = dB. getstoredproccommand ("addproduct ");
DB. addinparameter (insertcommand, "productname", dbtype. String, "productname", datarowversion. Current );
DB. addinparameter (insertcommand, "categoryid", dbtype. int32, "categoryid", datarowversion. Current );
DB. addinparameter (insertcommand, "unitprice", dbtype. Currency, "unitprice", datarowversion. Current );
Iv. application scenarios
Daab2.0 is a supplement to ADO. net2.0. It allows you to use the same data access code to support different databases. By changing the configuration file, you can switch between different databases. Currently, although only sqlserver and Oracle are supported, you can use dbproviderfactory objects in genericdatabase and ADO. NET 2.0 to add support for other databases. If you want to write the database accessProgramWith better portability, daab2.0 is a good choice, but if you want to program specific database features, you need to use ADO. net.
Refer to: Enterprise libaray-January 2006 help document and Quickstart
Well, I should have understood how to use it here. I will try the SQL and Access Database switch method freely, because I usually use VB.net to write things, therefore, only the VB.net code is written. If you are interested, change it to C #. Check the following HTML code: <%... @ page Language = "VB" autoeventwireup = "false" codefile = "SQL. aspx. VB "inherits =" SQL "%>
<! Doctype HTML public "-// W3C // dtd xhtml 1.0 transitional // en" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> web3.cn -- SQL and Access Database switch </title>
</Head>
<Body>
<Form ID = "form1" runat = "server">
<Div>
<Asp: gridview id = "gridview1" runat = "server" autogeneratecolumns = "false">
<Columns>
<Asp: boundfield datafield = "ID" headertext = "ID" sortexpression = "ID">
<Headerstyle backcolor = "Silver"/>
</ASP: boundfield>
<Asp: boundfield datafield = "provinceid" headertext = "provinceid" sortexpression = "provinceid">
<Headerstyle backcolor = "Silver"/>
</ASP: boundfield>
<Asp: boundfield datafield = "Province" headertext = "provinceid" sortexpression = "Province">
<Headerstyle backcolor = "Silver"/>
</ASP: boundfield>
</Columns>
</ASP: gridview>
</Div>
</Form>
</Body>
</Html> VB.net code: Imports system. Data
Imports Microsoft. Practices. enterpriselibrary. Data
Imports system. Data. Common
Imports system. Data. ODBC
Partial class SQL _accessclass SQL _access
Inherits system. Web. UI. Page
Dim sys as new WebService
Protected sub page_load () sub page_load (byval sender as object, byval e as system. eventargs) handles me. Load
If not page. ispostback then
Bindgrid ()
End if
End sub
Sub bindgrid () sub bindgrid ()
Dim DV as dataview
DV = getlist_access (). defaultview
Gridview1.datasource = dv
Gridview1.databind ()
End sub
'List
Public Function getlist_ SQL () function getlist_ SQL () as datatable
Dim dB as database = databasefactory. createdatabase ()
Dim sqlcommand as string = "select * from province order by id desc"
'The SQL statement or stored procedure to be executed on the data source.
Dim dbcommand as dbcommand = dB. getsqlstringcommand (sqlcommand)
Return dB. executedataset (dbcommand). Tables (0)
End Function
'List
Public Function getlist_access () function getlist_access () as datatable
Dim dB as database = new genericdatabase ("driver = {Microsoft Access Driver (*. MDB)}; DBQ = D: vs2005dbdb. MDB; uid = sa; Pwd = sa; ", odbcfactory. instance)
Dim sqlcommand as string = "select * from province order by id desc"
'The SQL statement or stored procedure to be executed on the data source.
Dim dbcommand as dbcommand = dB. getsqlstringcommand (sqlcommand)
Return dB. executedataset (dbcommand). Tables (0)
The code above the end function is not much, you should understand it, huh, just put "DV = getlist_access (). change defaultview to DV = getlist_ SQL (). defaultview can be replaced with the SQL database. Here, we only provide one idea, which is more simple and easy to use.
