Create your own data access layer 1

In project development, the core work of most developers is how to store and access data. To perform data operations, we must first solve several basic problems:

1. How to establish a connection with a database.

2. How to read data from the database.

3. how to add and modify data tables.

In. NET, ADO. NET easily solves the preceding three problems. We can use DbConnection for connection, DataSet for data storage, and DataAdapter for data update. First look at a piece of code:

 
 

  
  
// Create a DbConnection object to connect to the database
  
  
SqlConnection conn = new SqlConnection ();
  
  
Conn. ConnectionString = "server =.; uid = sa; password = 123456; database = DATA_BASE; max pool size = 300 ;";
  
  
// Create a DataAdapter and Command object to read data
  
  
SqlDataAdapter da = new SqlDataAdapter ();
  
  
SqlCommand cmd = new SqlCommand ();
  
  
Cmd. Connection = conn;
  
  
Cmd. CommandText = "SELECT * from test ";
  
  
Da. SelectCommand = cmd;
  
  
// Create a DataSet object, store data, and create a ing with a physical table
  
  
DataSet ds = new DataSet ();
  
  
Da. Fill (ds, "TEST ");
 
 

The preceding Code reads data from the "TEST" table in the Database "DATA_BASE" and stores the data in the DataSet row.

Since the data in the TEST table is read, the next solution is to add, delete, and modify the TEST table.

To add, delete, and modify an object, you must specify InsertCommand, DeleteCommand, and UpdateCommand for the DataAdapter and bind parameters to each Command object:

 
 

  
  
// Add data
  
  
Cmd = new SqlCommand ();
  
  
Cmd. Connection = conn;
  
  
Cmd. CommandText = "insert into test (ID, NAME, VAL) VALUES (@ ID, @ NAME, @ VAL )";
  
  
SqlParameter param = new SqlParameter ("@ ID", null );
  
  
Param. SourceColumn = "ID ";
  
  
Cmd. Parameters. Add (param );
  
  
Param = new SqlParameter ("@ NAME", null );
  
  
Param. SourceColumn = "NAME ";
  
  
Cmd. Parameters. Add (param );
  
  
Param = new SqlParameter ("@ VAL", null );
  
  
Param. SourceColumn = "VAL ";
  
  
Cmd. Parameters. Add (param );
  
  
Da. InsertCommand = cmd;
  
  
 
  
  
// Modify data
  
  
Cmd = new SqlCommand ();
  
  
Cmd. Connection = conn;
  
  
Cmd. CommandText = "update test set name = @ NAME, VAL = @ val where id = @ ID ";
  
  
Param = new SqlParameter ("@ ID", null );
  
  
Param. SourceColumn = "ID ";
  
  
Cmd. Parameters. Add (param );
  
  
Param = new SqlParameter ("@ NAME", null );
  
  
Param. SourceColumn = "NAME ";
  
  
Cmd. Parameters. Add (param );
  
  
Param = new SqlParameter ("@ VAL", null );
  
  
Param. SourceColumn = "VAL ";
  
  
Cmd. Parameters. Add (param );
  
  
Da. UpdateCommand = cmd;
  
  
 
  
  
// Delete data
  
  
Cmd = new SqlCommand ();
  
  
Cmd. Connection = conn; cmd. CommandText = "delete from test where id = @ ID ";
  
  
Param = new SqlParameter ("@ ID", null );
  
  
Param. SourceColumn = "ID ";
  
  
Cmd. Parameters. Add (param );
  
  
Da. DeleteCommand = cmd;
 
 

After completing the preparation, use DataTable to perform data operations:

 
 

  
  
DataTable dt = ds. Tables ["TEST"];
  
  
Dt. PrimaryKey = new DataColumn [] {dt. Columns ["ID"]};
  
  
Dt. Rows. Add (new object [] {
  
  
Guid. newGuid (). toString (), string. format ("test: {0}", DateTime. now), string. format ("test value: {0}", DateTime. now)
  
  
});
  
  
DataRow dr = dt. Rows. Find ("f8dc2c64-f51a-4e99-bde1-a20069b09c3a ");
  
  
If (dr! = Null ){
  
  
Dr ["NAME"] = string. Format ("test modification: {0}", DateTime. Now );
  
  
}
  
  
Dr = dt. Rows. Find ("ed7d079b-81ec-4ba4-bf85-688621e495e7 ");
  
  
If (dr! = Null ){
  
  
Dr. Delete ();
  
  
}
 
 

Finally, call the Update method of DataAdapter to save the changed data:

Da. Update (ds, "TEST ");

Using ADO. NET, we have completed the complete database operations.

Note: The above code is the basic principle of using DataAdapter to read and write the database. It is very important that we will use this as the basis for subsequent changes.

Although we can complete data operations, there are still many problems:

1. We only operate on the MSSql database. to operate on Oracle or MySql, we need to define a new Oracle or MySql DATA object. If the Database Type of the operation can be automatically determined by the system, we can ignore the attention to data objects.

2. We have done a lot of preparation work. We only operate on one table. For example, we want to operate on multiple tables with many parameters. In fact, a lot of repeated code will be generated, we need to consider eliminating this code.

If we implement a data access layer. NET object encapsulation, only focus on the actual data operations, not on how the system connects to the database, and how to transmit parameters, then our needs are basically met.

We can first assume that the following results need to be achieved, in the form of a pseudo code:

 
 

  
  
Create a data executor: DataExecuter: execObj;
  
  
Create a data ing object: DataMapping map;
  
  
Data Set filled by ing object: map. Fill (sqlText, "TEST", ds );
  
  
Set the ing object Update command: map. SetCommands (Insert | Update | Delete, ds );
  
  
DataTable performs addition, deletion, and modification operations.
  
  
The data performer performs the final change operation: execObj. Update (ds );
 
 

I will see how the above assumptions are implemented step by step.