Basic use of the data access module in the Microsoft enterprise database

Basic use of the data access module in the Microsoft enterprise database

The data access module of the Microsoft enterprise database encapsulates several methods for operating the database. This article is based on the Microsoft enterprise database 5.0.

1. Add Assembly reference

You need to add a reference to Microsoft. Practices. EnterpriseLibrary. Common. dll and Microsoft. Practices. EnterpriseLibrary. Data. dll assembly in the project.

2. Use a namespace

Using Microsoft. Practices. EnterpriseLibrary. Data;
Using Microsoft. Practices. EnterpriseLibrary. Data. SQL;
Using System. Data. Common;

3. Configure the connection string

<ConnectionStrings>
<Add name = "ConnectionString" connectionString = "Data Source =. \ sqlexpress; Initial Catalog = MySchool; Integrated Security = True" providerName = "System. Data. SqlClient"/>
</ConnectionStrings>

4. Main Methods

Database db = DatabaseFactory. CreateDatabase ("ConnectionString ");

4.1 execute the add, delete, and modify SQL statement

Public int Insert (Model. Subject model)
{
String SQL = "insert into subject values (@ subjectname, @ hours, @ gradeid )";
DbCommand cmd = db. GetSqlStringCommand (SQL );
Db. AddInParameter (cmd, "@ subjectname", DbType. String, model. SubjectName );
Db. AddInParameter (cmd, "@ hours", DbType. Int32, model. Hours );
Db. AddInParameter (cmd, "@ gradeid", DbType. Int32, model. GradeId );
Return db. ExecuteNonQuery (cmd );
}

4.2 returns the value of 1st columns in 1st rows.

Public int GetCount ()
{
String SQL = "select count (*) from subject ";
DbCommand cmd = db. GetSqlStringCommand (SQL );
Return Convert. ToInt32 (db. ExecuteScalar (cmd ));
}

4.3 return DataSet

Public DataTable GetList ()
{
String SQL = "SELECT [SubjectId], [SubjectName], [Hours], [GradeName] from subject inner join grade on subject. GradeId = grade. GradeId ";

DbCommand cmd = db. GetSqlStringCommand (SQL );
DataSet ds = db. ExecuteDataSet (cmd );
Return ds. Tables [0];

}

4.4 IDataReader

String SQL = "select * from College where CollegeId = @ id ";
DbCommand cmd = db. GetSqlStringCommand (SQL );
Db. AddInParameter (cmd, "@ id", DbType. Int32, id );
Using (IDataReader dr = db. ExecuteReader (cmd ))
{
If (dr. Read ())
{
TextBox1.Text = dr [0]. ToString ();
TextBox2.Text = dr [1]. ToString ();
}
}

4.5 execute the Stored Procedure

Public int Delete (int subjectId)
{
String SQL = "usp_DeleteSubject ";

DbCommand cmd = db. GetStoredProcCommand (SQL );

Db. AddInParameter (cmd, "@ subjectid", System. Data. DbType. Int32, subjectId );
Return db. ExecuteNonQuery (cmd );

}

4.6 use database transactions

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 );
}

4.7 visualize the returned data

Private void DataAsObject_Click (object sender, EventArgs e)
{
Var results = db. ExecuteSprocAccessor <College> ("usp_College_LoadAll ");

MessageBox. Show (results. ElementAt (0). ToString ());
}

4.8 asynchronous execution (asynchronous database access in the enterprise database only supports SQL Server)

Private void Async_Click (object sender, EventArgs e)
{
// Create a new database connection. The attribute must be added: Asynchronous Processing = true.
String connectionString = @ "server =. \ sqlexpress; 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 );

If (reader. Read ())
{
College c = new College (int) reader [0], (string) reader [1]);

MessageBox. Show (c. ToString ());
}
Reader. Close ();
}
Catch (Exception ex)
{
MessageBox. Show (ex. ToString ());
}
}