Ado.net quick start practice (1)

These two days, I have had a deep understanding of ado.net. By the way, I would like to write some code practitioners to take a review note.
I. Brief introduction to the five common objects of ado.net
Since ado.net is mentioned, five common objects cannot be mentioned in the plain sense. This article will not explain the relationship between the five objects of ado.net and them too much, but we should have an understanding of the structure of the figure below:

 

 
For the five objects shown in the figure, the data-driven mis system should not be unfamiliar. This article takes a long time. Next we will implement the data access program with ado.net as the core step by step.
Ii. Data Access Persistence Layer
1. IDbOperation Interface
Code
Using System. Collections. Generic;
Using System. Data;
Using System. Data. Common;
Namespace AdoNetDataaccess. Core. Contract
{
Public interface IDbOperation
{
DbCommand CreateDbCommd (DbConnection sqlConn, DbTransaction transaction, string sqlStr, CommandType primitive type, List <DbParameter> listParams );
DbParameter CreateDbPRameter (string paramName, object paramValue );
DbDataReader ExecuteReader (string sqlStr, CommandType queue type, List <DbParameter> listParams );
DataTable FillDataTable (string sqlStr, CommandType parameter type, List <DbParameter> listParams );
DataSet FillDataSet (string sqlStr, CommandType struct type, List <DbParameter> listParams );
Object ExecuteScalar (string sqlStr, CommandType queue type, List <DbParameter> listParams );
Int ExecuteNonQuery (string sqlStr, CommandType limit type, List <DbParameter> listParams );
/// <Summary>
/// Batch insert
/// </Summary>
/// <Param name = "tableName"> table name </param>
/// <Param name = "dt"> assembled datatable to be imported in batches </param>
/// <Returns> </returns>
Bool ExecuteBatchInsert (string tableName, int batchSize, int copyTimeout, DataTable dt );
Void OpenConnection ();
Void CloseConnection ();
}
}
The preceding interfaces include addition, deletion, modification, and query, batch insertion, and connection and closure of database connection objects. You can easily understand the meaning of a function based on the name and parameters. Based on the development experience of pig, the above methods are almost sufficient for normal database operations. You can also rewrite the organization to add other functions as needed.

2. Data operations for a data source
After the underlying data operation interface is defined, the above data operations must be implemented for a data source. Here, the pig chooses SQL Server. We can also implement data access operations for other data sources. According to the configuration, we can use abstract factory dynamic reflection to select which data source is implemented. Here, you can try it out by yourself. The specific implementation is as follows:
Code
Using System;
Using System. Collections. Generic;
Using System. Data;
Using System. Data. Common;
Using System. Data. SqlClient;
Using System. Transactions;
Namespace AdoNetDataAccess. Core. Implement
{
Using AdoNetDataAccess. Core. Contract;
Public class SqlServer: IDbOperation, IDisposable
{
Private int timeout = 60;
Private DbConnection sqlConn = null;
Private DbCommand cmd = null;
Private SqlServer ()
{
}
Public SqlServer (string sqlConStr)
{
SqlConn = new SqlConnection (sqlConStr );
CmdTimeOut = sqlConn. ConnectionTimeout;
}
Public SqlServer (string sqlConStr, int timeOut)
{
SqlConn = new SqlConnection (sqlConStr );
If (timeOut <0)
{
TimeOut = sqlConn. ConnectionTimeout;
}
Optional timeOut = timeOut;
}
# Region contract method
Public DbCommand CreateDbCommd (DbConnection sqlConn, DbTransaction transaction, string sqlStr, CommandType primitive type, List <DbParameter> listParams)
{
DbCommand cmd = new SqlCommand ();
Cmd. Connection = sqlConn;
Cmd. CommandText = sqlStr;
Cmd. CommandType = primitive type;
If (transaction! = Null)
{
Cmd. Transaction = transaction;
}
If (listParams! = Null & listParams. Count> 0)
{
Cmd. Parameters. AddRange (listParams. ToArray ());
}
Cmd. CommandTimeout = timed timeout;
OpenConnection ();
Return cmd;
}
Public DbParameter CreateDbPrameter (string paramName, object paramValue)
{
SqlParameter sp = new SqlParameter (paramName, paramValue );
Return sp;
}
Public DbDataReader ExecuteReader (string sqlStr, CommandType initialize type, List <DbParameter> listParams)
{
DbDataReader rdr = null;
Try
{
OpenConnection ();
Cmd = CreateDbCommd (sqlConn, null, sqlStr, callback type, listParams );
Rdr = cmd. ExecuteReader ();
}
Catch (Exception ex)
{
Throw ex;
}
Return rdr;
}
Public DataTable FillDataTable (string sqlStr, CommandType parameter type, List <DbParameter> listParams)
{
OpenConnection ();
DbTransaction trans = sqlConn. BeginTransaction ();
DbCommand cmd = CreateDbCommd (sqlConn, trans, sqlStr, callback type, listParams );
SqlDataAdapter sqlDataAdpter = new SqlDataAdapter (cmd as SqlCommand );
DataTable dt = new DataTable ();
Try
{
SqlDataAdpter. Fill (dt );
Trans. Commit ();
}
Catch (Exception e)
{
Trans. Rollback ();
Throw new Exception ("failed to execute database operations, SQL:" + sqlStr, e );
}
Finally
{
SqlDataAdpter. Dispose ();
Cmd. Dispose ();
Trans. Dispose ();
CloseConnection ();
}
Return dt;
}
Public DataSet FillDataSet (string sqlStr, CommandType parameter type, List <DbParameter> listParams)
{
OpenConnection ();
DbTransaction trans = sqlConn. BeginTransaction ();
DbCommand cmd = CreateDbCommd (sqlConn, trans, sqlStr, callback type, listParams );
SqlDataAdapter sqlDataAdpter = new SqlDataAdapter (cmd as SqlCommand );
DataSet ds = new DataSet ();
Try
{
SqlDataAdpter. Fill (ds );
Trans. Commit ();
}
& Nbs