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= NewSqlConnection ();
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= NewSqlDataAdapter ();
SqlCommand cmd= NewSqlCommand ();
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= NewDataSet ();
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= NewSqlCommand ();
Cmd. Connection=Conn;
Cmd. CommandText= "Insert into test (ID, NAME, VAL) VALUES (@ ID, @ NAME, @ VAL)";
SqlParameter param= NewSqlParameter ("@ ID",Null);
Param. SourceColumn= "ID";
Cmd. Parameters. Add (param );
Param= NewSqlParameter ("@ NAME",Null);
Param. SourceColumn= "NAME";
Cmd. Parameters. Add (param );
Param= NewSqlParameter ("@ VAL",Null);
Param. SourceColumn= "VAL";
Cmd. Parameters. Add (param );
Da. InsertCommand=Cmd;

//Modify data
Cmd= NewSqlCommand ();
Cmd. Connection=Conn;
Cmd. CommandText= "Update test set name = @ NAME, VAL = @ val where id = @ ID";
Param= NewSqlParameter ("@ ID",Null);
Param. SourceColumn= "ID";
Cmd. Parameters. Add (param );
Param= NewSqlParameter ("@ NAME",Null);
Param. SourceColumn= "NAME";
Cmd. Parameters