In application design, database access is very important. We usually need to centralize database access to ensure good encapsulation and maintainability. In. for SQL Server of Microsoft and other databases (OleDb supported), different access methods are used for database access. These classes are distributed in System. data. sqlClient and System. data. oleDb namespace. Microsoft later launched a class library dedicated to accessing Oracle databases. We hope that when writing an application system, the database will not be affected because of so many different types of data, and the client code will not need to be changed when the background database changes.
Sometimes, for performance and other reasons, we also hope to provide a cache for database access, especially for database connection. Although Microsoft has provided us with a built-in database cache, its own control of the cache will undoubtedly provide greater flexibility and efficiency. Public class BaseDataAccess: IDisposable
{
/// Data adapter
Private SqlDataAdapter dsAdapter;
/// Data update transaction handle
SqlTransaction dsTransaction;
Private SqlCommand selectCommand;
Private SqlCommand updateCommand;
Private SqlCommand insertCommand;
Private SqlCommand deleteCommand;
// Database connection
Private SqlConnection dbConnection;
Private DBConfigration dbConfigration = new DBConfigration ();
Public BaseDataAccess ()
{
/// Create the data adapter object "OldDbDataAdapter"
DsAdapter = new SqlDataAdapter ();
/// Obtain the data to manipulate the connection
DbConnection = new SqlConnection (DBConfigration. BitSoftConnectionString );
}
Public void Dispose ()
{
Dispose (true );
GC. SuppressFinalize (true );
}
Protected virtual void Dispose (bool disposing)
{
If (! Disposing)
Return;
If (dsAdapter! = Null)
{
If (dsAdapter. SelectCommand! = Null)
{
If (dsAdapter. SelectCommand. Connection! = Null)
DsAdapter. SelectCommand. Connection. Dispose ();
DsAdapter. SelectCommand. Dispose ();
}
DsAdapter. Dispose ();
DsAdapter = null;
}
}
/// <Summary>
/// Check the input fields of a single data form (DataTable) to automatically obtain the "Update" command for the data form.
/// </Summary>
/// <Param name = "DataTable"> single data form </param>
/// <Returns> SqlCommand </returns>
Private SqlCommand GetUpdateByIdCommand (DataTable oneTable)
{
String sqlFirst = "UPDATE ";
String sqlSecond = "WHERE ";
SqlFirst = sqlFirst + oneTable. TableName + "SET ";
/// Create a SqlCommand object
If (updateCommand = null)
{
UpdateCommand = new SqlCommand (sqlFirst, dbConnection );
}
/// A set of parameter tables for the Update statement
SqlParameterCollection updateParams = updateCommand. Parameters;
UpdateCommand. Parameters. Clear ();
/// Traverse the DataTable field and generate an SQL UPDATE statement
Bool isFirstColumn = true;
Bool isFirstKeyColumn = true;
Foreach (BaseDataColumn dc in oneTable. Columns)
{
/// Ignore useless Fields
If (dc. IsValidColumn ())
{
/// Obtain the keyword Field
If (dc. IsKeyColumn ())
{
If (isFirstKeyColumn)
{
SqlSecond = sqlSecond + dc. ColumnName + "= @" + dc. ColumnName;
IsFirstKeyColumn = false;
}
Else
SqlSecond = sqlSecond + "AND" + dc. ColumnName + "= @" + dc. ColumnName;
}
Else
{
If (isFirstColumn)
{
SqlFirst = sqlFirst + dc. ColumnName + "= @" + dc. ColumnName;
IsFirstColumn = false;
}
Else
SqlFirst = sqlFirst + "," + dc. ColumnName + "= @" + dc. ColumnName;
UpdateParams. Add (new sqlParameter ("@" + dc. ColumnName, dc. GetColumnSqlDbType ()));
UpdateParams ["@" + dc. ColumnName]. SourceColumn = dc. ColumnName;
}
}
}
Foreach (BaseDataColumn dc in oneTable. Columns)
{
/// Ignore useless Fields
If (dc. IsValidColumn ())
{
/// Obtain the keyword Field
If (dc. IsKeyColumn ())
{
UpdateParams. Add (new SqlParameter ("@" + dc. ColumnName, dc. GetColumnSqlDbType ()));
UpdateParams ["@" + dc. ColumnName]. SourceColumn = dc. ColumnName;
}
}
}
/// Set the Command type and complete UPDATE statement
UpdateCommand. CommandType = CommandType. Text;
UpdateCommand. CommandText = sqlFirst + sqlSecond;
Return updateCommand;
}
/// <Summary>
/// Check the input fields of a single data form (DataTable) to automatically obtain the "Insert" command for the data form.
/// </Summary>
/// <Param name = "oneTable"> single data form </param>
/// <Returns> SqlCommand </returns>
Private SqlCommand GetInsertCommand (DataTable oneTable)
{
String sqlFirst = "insert ";
String sqlSecond = "VALUES (";
/// Name of the joined table
SqlFirst = sqlFirst + oneTable. TableName;
/// Create a SqlCommand object
If (insertCommand = null)
{
InsertCommand = new SqlCommand (sqlFirst, dbConnection );
}
/// Set of parameters for the Insert statement
SqlParameterCollection insertParams = insertCommand. Parameters;
InsertCommand. Parameters. Clear ();
/// Traverse the DataTable field to generate an SQL INSERT statement
Bool isFirstColumn = true;
Foreach (BaseDataColumn dc in oneTable. Columns)
{
/// Ignore useless Fields
If (dc. IsValidColumn ()&&! Dc. AutoIncrement)
{
If (isFirstColumn)
{
SqlFirst = sqlFirst + "(" + dc. ColumnName;
IsFirstColumn = false;
SqlSecond = sqlSecond + "@" + dc. ColumnName;
}
Else
{
SqlFirst = sqlFirst + "," + dc. ColumnName;
SqlSecond = sqlSecond + "," + "@" + dc. ColumnName;
} /// Generate an Insert parameter table set
InsertParams. Add (new SqlParameter ("@" + dc. ColumnName, dc. GetColumnSqlDbType ()));
InsertParams ["@" + dc. ColumnName]. SourceColumn = dc. ColumnName;
}
}
/// Set the Command type and complete UPDATE statement
InsertCommand. CommandType = CommandType. Text;
InsertCommand. CommandText = sqlFirst + ")" + sqlSecond + ")";
Return insertCommand;
}
/// <Summary>
/// Check the input fields of a single data form (DataTable) to automatically obtain the "Delete" command for the data form.
/// </Summary>
/// <Param name = "oneTable"> single data form </param>
/// <Returns> SqlCommand </returns>
Private SqlCommand GetDeleteByIdCommand (DataTable oneTable)
{
String sqlFirst = "delete from ";
SqlFirst = sqlFirst + oneTable. TableName + "WHERE ";
/// Create a SqlCommand object
If (deleteCommand = null)
{
DeleteCommand = new SqlCommand (sqlFirst, dbConnection );
}
/// A set of parameter tables for the Delete statement
SqlParameterCollection deleteParams = deleteCommand. Parameters;
DeleteCommand. Parameters. Clear ();
/// Traverse the DataTable field and generate an SQL DELETE statement
Bool isFirstKeyColumn = true;
Foreach (BaseDataColumn dc in oneTable. Columns)
{
/// Ignore useless Fields
If (dc. IsValidColumn ())
{
/// Obtain the keyword Field
If (dc. IsKeyColumn ())
{
If (isFirstKeyColumn)
{
SqlFirst = sqlFirst + dc. ColumnName + "= @" + dc. ColumnName;
IsFirstKeyColumn = false;
}
Else
SqlFirst = sqlFirst + "AND" + dc. ColumnName + "= @" + dc. ColumnName; /// Generate the Delete parameter table set
DeleteParams. Add (new SqlParameter ("@" + dc. ColumnName, dc. GetColumnSqlDbType ()));
DeleteParams ["@" + dc. ColumnName]. SourceColumn = dc. ColumnName;
}
}
}
/// Set the Command type and complete DELETE statement
DeleteCommand. CommandType = CommandType. Text;
DeleteCommand. CommandText = sqlFirst;
Return deleteCommand;
} /// <Summary>
/// Update the database based on the specified object dataset.
/// </Summary>
/// <Param name = "dataSet"> Object dataSet </param>
/// <Returns> returns a Boolean value indicating whether the operation is successful. </returns>
Public bool UpdateObjectData (BaseDataSet myDataSet)
{
If (dsAdapter = null)
{
Throw new System. ObjectDisposedException (GetType (). FullName );
}
Try
{
DbConnection. Open ();
Try
{
DsTransaction = dbConnection. BeginTransaction ();
}
Catch
{
DbConnection. Close ();
Return false;
}
/// Traverse every form in the DataSet for update
Foreach (DataTable dt in myDataSet. Tables)
{
DsAdapter. UpdateCommand = GetUpdateByIdCommand (dt );
/// Set the transaction handle for record updates
DsAdapter. UpdateCommand. Transaction = dsTransaction;
/// Update the database based on the contents of the input DataSet table and table
DsAdapter. Update (myDataSet, dt. TableName );
}
Try
{
DsTransaction. Commit ();
}
Catch
{
DsTransaction. Rollback ();
Return false;
}
Finally
{
/// Release the database connection in time no matter whether the update is successful or not
DbConnection. Close ();
}
/// Refresh the DataSet update flag
MyDataSet. AcceptChanges ();
Return true;
}
Catch (Exception e)
{
String a = e. Message;
Return false;
}
}
/// <Summary>
/// Insert a new database based on the specified object dataset.
/// </Summary>
/// <Param name = "myDataSet"> Object dataset </param>
/// <Returns> returns a Boolean value indicating whether the operation is successful. </returns>
Public bool InsertObjectData (BaseDataSet myDataSet)
{
If (dsAdapter = null)
{
Throw new System. ObjectDisposedException (GetType (). FullName );
}
Try
{
DbConnection. Open ();
/// Start Transaction Processing
Try
{
DsTransaction = dbConnection. BeginTransaction ();
}
Catch
{
DbConnection. Close ();
Return false;
}
/// Traverse every form in the DataSet for update
Try
{
/// Call the method that automatically generates the keyword ID.
If (AutoCreatID (myDataSet, dsTransaction ))
{
Foreach (DataTable dt in myDataSet. Tables)
{
DsAdapter. InsertCommand = GetInsertCommand (dt );
/// Set the transaction handle for record updates
DsAdapter. InsertCommand. Transaction = dsTransaction;
/// Update the database based on the contents of the input DataSet table and table
DsAdapter. Update (myDataSet, dt. TableName );
}
/// Submit the transaction
DsTransaction. Commit ();
}
Else
{
/// Roll back the transaction
DsTransaction. Rollback ();
Return false;
}
}
Catch (Exception e)
{
/// Roll back the transaction
String a = e. Message;
DsTransaction. Rollback ();
Return false;
}
Finally
{
/// Release the database connection in time no matter whether the update is successful or not
DbConnection. Close ();
}
/// Refresh the DataSet update flag
MyDataSet. AcceptChanges ();
Return true;
}
Catch (Exception e)
{
String a = e. Message;
Return false;
}
}
/// <Summary>
/// Delete the corresponding database records based on the specified object dataset.
/// </Summary>
/// <Param name = "myDataSet"> Object dataset </param>
/// <Returns> returns a Boolean value indicating whether the operation is successful. </returns>
Public bool DeleteObjectData (BaseDataSet myDataSet)
{
If (dsAdapter = null)
{
Throw new System. ObjectDisposedException (GetType (). FullName );
}
Try
{
DbConnection. Open ();
Try
{
DsTransaction = dbConnection. BeginTransaction ();
}
Catch
{
DbConnection. Close ();
Return false;
}
/// Traverse every form in the DataSet for update
Try
{
Foreach (DataTable dt in myDataSet. Tables)
{
DsAdapter. DeleteCommand = GetDeleteByIdCommand (dt );
/// Set the transaction handle for record updates
DsAdapter. DeleteCommand. Transaction = dsTransaction;
/// Update the database based on the contents of the input DataSet table and table
DsAdapter. Update (myDataSet, dt. TableName );
}
DsTransaction. Commit ();
}
Catch (Exception e)
{
String a = e. Message;
DsTransaction. Rollback ();
Return false;
}
Finally
{
/// Release the database connection in time no matter whether the update is successful or not
DbConnection. Close ();
}
/// Refresh the DataSet update flag
MyDataSet. AcceptChanges ();
Return true;
}
Catch
{
Return false;
}
}
/// <Summary>
/// Delete, modify, and add database records based on the specified object dataset.
/// </Summary>
/// <Param name = "myDataSet"> Object dataset </param>
/// <Returns> returns a Boolean value indicating whether the operation is successful. </returns>
Public bool SynchronizeObjectData (BaseDataSet dataSet)
{
If (dsAdapter = null)
{
Throw new System. ObjectDisposedException (GetType (). FullName );
}
Try
{
DbConnection. Open ();
Try
{
DsTransaction = dbConnection. BeginTransaction ();
}
Catch
{
DbConnection. Close ();
Return false;
}
/// Traverse every form in the DataSet for update
Try
{
/// Call the method that automatically generates the keyword ID.
If (AutoCreatID (dataSet, dsTransaction ))
{
Foreach (DataTable dt in dataSet. Tables)
{
/// Delete the corresponding record.
DsAdapter. DeleteCommand = GetDeleteByIdCommand (dt );
/// Set the transaction handle for record Deletion
DsAdapter. DeleteCommand. Transaction = dsTransaction;
/// Modify the corresponding record.
DsAdapter. UpdateCommand = GetUpdateByIdCommand (dt );
/// Set the transaction handle for record updates
DsAdapter. UpdateCommand. Transaction = dsTransaction;
/// Add a new record.
DsAdapter. InsertCommand = GetInsertCommand (dt );
/// Set the transaction handle for record updates
DsAdapter. InsertCommand. Transaction = dsTransaction;
/// Delete/update/Add the corresponding database based on the imported DataSet table and table content
DsAdapter. Update (dataSet, dt. TableName );
}
/// Submit the transaction
DsTransaction. Commit ();
}
Else
{
/// Roll back the transaction
DsTransaction. Rollback ();
Return false;
}
}
Catch
{
DsTransaction. Rollback ();
Return false;
}
Finally
{
/// Release the database connection in time no matter whether the update is successful or not
DbConnection. Close ();
}
/// Refresh the DataSet update flag
DataSet. AcceptChanges ();
Return true;
}
Catch
{
Return false;
}
}
/// <Summary>
/// Fill in the corresponding object dataset based on the input keyword Value List.
/// Note: the keyword Value List is a parameter table in the format of "@ field name, keyword value.
/// </Summary>
/// <Param name = "dataSet"> dataSet object for Assembly </param>
/// <Param name = "keyTable"> key words of an object (possibly multiple) and value parameter table </param>
/// <Returns> returns a Boolean value indicating whether the operation is successful. </returns>
Public BaseDataSet FillDataSetByArray (BaseDataSet dataSet, ParmArrayWithOP keyArray)
{
If (dsAdapter = null)
{
Throw new System. ObjectDisposedException (GetType (). FullName );
}
Try
{
Foreach (DataTable dt in dataSet. Tables)
{
DsAdapter. SelectCommand = GetSelectBySqlOP (dt, keyArray );
// Set the keyword Value
Foreach (SqlParameter pt in dsAdapter. SelectCommand. Parameters)
{
DsAdapter. SelectCommand. Parameters [pt. ParameterName]. Value = keyArray. GetParmValue (keyArray. IndexOf (pt. ParameterName ));
}
DsAdapter. Fill (dataSet, dt. TableName );
}
}
Catch
{
/// Write exceptions to handle error messages.
// MessagerBulletin. WriteMsg (e. Message );
Return dataSet;
}
Return dataSet;
}
/// <Summary>
/// Description: This method will obtain the SELECT statement with the paging display function. The purpose is to return only one page of records at a time, rather than the entire table record.
/// This method has two reload modes: Conditional query and non-conditional query.
/// Parameter: intPageNum: number of records displayed on each page
/// IntPageIndex: Current page number
/// StrTableName: Table Name
/// StrKey ": Primary Key
/// StrParam []: parameter array. This value is valid only when blnCondition is true.
/// ObjValues []: Value array. This value is valid only when blnCondition is true.
/// StrOrderField: name of the sorted Field
/// StrOrderType: Sorting type, in ascending or descending order. The value is ASC-ascending and DESC-descending.
/// Return: select statement, where the output parameter returns the SQL statement that obtains the total number of records, and the method itself returns the query SQL statement
/// </Summary> Public string GetPageSelectByID (int intPageNum, int intPageIndex, string strTableName, string strKey, string [] strParams, object [] objValues, string strOrderField, string strOrderType, out string strSqlCount)
{
String strSql;
String strTemp = ""; // Storage Condition
// Construct a select statement
StrSql = "select Top" + intPageNum. ToString () + "* from" + strTableName + "where"
+ StrKey + "not in (select top" + (intPageIndex-1) * intPageNum). ToString () + "" + strKey + "from" + strTableName;
// Add the where clause to the generated select statement
If (strParams. Length = 0) | (strParams. Length! = ObjValues. Length ))
{
Throw (new ParamValueNotMatchException ("the parameter value is missing or the parameter does not match the value "));
}
For (int I = 0; I <= strParams. Length-1; I ++)
{
StrTemp = strTemp + "and" + strParams [I] + "=" + "'" + objValues [I]. ToString () + "'";
}
Char [] and = new char [] {'A', 'n', 'd '};
StrTemp = (strTemp. TrimStart (). TrimStart (and); // remove unnecessary "and"
StrSql = strSql + "where" + strTemp + "order by" + strOrderField + "" + strOrderType + ")"
+ "And" + strTemp + "order by" + strOrderField + "" + strOrderType;
// Obtain the statement for querying the total number of records
StrSqlCount = "select count (*) from" + strTableName + "where" + strTemp;
Return strSql;
} |
