We all want to write some code less when developing software, hoping to use it everywhere, I hope that what kind of database software we can use, what should we do?
What kind of class is used when we operate a database?
In general, we will use some classes when working on the database. I will summarize some classes:
1.
SQLServer:
a)
System.data.sqlclient.sqldataAdapter: SQL data adapter. Indicates a set of data commands for populating the DataSet and updating the SQL Server database and a database connection. Unable to inherit this class. This class is inherited in system.data.common.dbdataAdapter and implements interface system.data.idbdataadapter. Adapter mode design.
b)
System.Data.sqlclient.sqlConnection: SQL database connection. Indicates an open connection for the SQL Server database. Unable to inherit this class.
c)
System.Data.sqlclient.sqlcommandbuilder: SQL Database Command Builder. Automatically generate a single table command with the following use: Coordinating the changes made to DataSet with associated SQL Server databases. Unable to inherit this class. Designed with Builder mode.
There are also some, but in this article, it will not be described here.
2.
Oracle:
a)
System.Data.OracleClient.OracleDataAdapter: Oracle data adapter. Indicates a set of data commands to populate DataSet and update the Oracle database and to the database. Unable to inherit this class. This class is inherited in system.data.common.dbdataAdapter and implements interface system.data.idbdataadapter. Adapter mode design.
b)
System.Data.OracleClient.OracleConnection: Oracle Database Connection. Indicates a connection to the Oracle database. Unable to inherit this class.
c)
System.Data.OracleClient.OracleCommandbuilder: Oracle Database Command Builder. Automatically generate a single table command for coordinating the DataSet change and the associated Oracle database. Unable to inherit this class. Designed with Builder mode.
3.
ODBC:
a)
System.Data.odbc.odbcdataAdapter: ODBC Type Data Adapter. Indicates the data command set and the connection to the ODBC data source, which is used to populate the DataSet and update the data source. Unable to inherit this class. . This class is inherited in system.data.common.dbdataAdapter and implements interface system.data.idbdataadapter. Adapter mode design.
b)
System.Data.odbc.odbcConnection: ODBC database connection. It is indicated that the connection to the ODBC data source is open.
c)
System.Data.odbc.odbccommandbuilder: ODBC Database Command Builder. Automatically generate a single table command for coordinating the DataSet change and the associated ODBC type data source. Unable to inherit this class. Designed with Builder mode.
4.
OLEDB:
a)
System.Data.Oledb.oledbdataAdapter: ODBC Type Data Adapter. Indicates the data command set and the connection to the OLEDB data source, which is used to populate the DataSet and update the data source. Unable to inherit this class. . This class is inherited in system.data.common.dbdataAdapter and implements interface system.data.idbdataadapter. Adapter mode design. b)
System.Data.Oledb.oledbconnection: OLEDB database connection. It is indicated that the connection to the OLEDB data source is open.
c)
System.Data.Oledb.oledbcommandbuilder: OLEDB Database Command Builder. Automatically generate a single table command for coordinating the DataSet change and the associated OLEDB type data source. Unable to inherit this class. Designed with Builder mode.
What kind of data operator do we need?
Of course, the more simple and better, the function is not necessarily strong, enough to use it. I hope to support a variety of databases. The programs that use this operator don't have to consider it. It is the kind of database; I hope to operate multiple databases, one project uses multiple databases without adding programming complexity; hoping to support transaction, failure can be automatically returned roll. The function, you can read data and update the data.
General data operator ideas
The operation of the database is actually two things: out and enter. What is read from the database, and the input is to write the data back the database, including new data, update data, and delete data.
So what should I do for these two things?
When reading data, we open a connection, instantiate a data adapter and populate the data set, turn off the connection, you can. It should be noted here that since the table in the data set is often the result of multiple data sheet Join in the database, you want to let the operator automatically generate query statements, you write yourself.
When writing data, there will be some troubles, but because you are performing a single table action, you can write a SQL statement yourself, so that the operator is generated.
So how do you do step by step? First open a database connection, regenerate into a query string, then instantiate a data adapter, generating a CommandBuilder instance and register the listener of DataAdapter, and then configure the transaction. Then update the database and finally closes the connection. The transaction cannot be configured earlier because the commands of the data adapter are not allowed after the configured transaction.
The idea is, it is very simple, we only need to define these operations for each database connection, and then call according to the actual situation.
Of course, we don't want to use which database is incorporated by calling it as a parameter, defined in the configuration file, which seems to be better.
We should reflect this because there may be multiple databases. For example, we can define the default database to connect to "dbcon", and the connection of database A is "Adbcon".
Since the operation of multiple tables is to be implemented, we want to define a mapping of a data set table and a table name.
Code
First define an enumeration to specify which databases can support:
///
/// database type enumeration
///
Public Enum DBTYPE
{
///
/// SQLServer
///
SQL Server,
///
/// oracle
///
Oracle,
///
/// OLEDB
///
OLEDB,
///
/// ODBC
/// ODBC
}
Define a class to extend the DataTable:
///
// Using the data sheet of the database, the library table name is
///
Public Class DataTableExtend
{
///
/// data sheet
///
Public system.data.dataable dataatable;
///
/// Dataset map to the table name of the database
///
Public String DataTableName;
///
// Up the data sheet for updating the database, the library table name pair constructor
///
/// Data table used to update the database
/// Data table mapped to the table name
Public DataTableExtend (System.Data.DataTable MyTable, String MyTableName)
{
DataTable = MyTable;
DataTablename = mytablename;
}
}
Then write a class to read the configuration file and get the database connection string:
///
/// DBSETTING summary description.
///
Public Class DBSETTING
{
///
/// Database connection string suffix
///
Public Static String DbconnectionENDS
{
get
{
Return "DBCON";
}
}
///
/// Database type suffix
///
Public Static String DbtypeEnds
{
get
{
Return "DBTYPE";
}
}
///
/// Get the type of the specified database
///
/// Specified database name
///
Public static dbtype getdbtype (String Dbname)
{
String dbtype = NULL;
DBTYPE = Appconfig.GetAppSetting (DBNAME DBTYPEENDS);
IF (dbtype.tolower () == dbtype.Oracle.toString (). TOLOWER ())
{
Return Dbtype.Oracle;
}
IF (dbtype.tolower () == dbtype.odbc.tostring (). TOLOWER ())
{
Return DBTYPE.ODBC;
}
IF (dbtype.tolower () == dbtype.oledb.tostring (). TOLOWER ())
{
Return DBTYPE.OLDB;
}
Else
{
Return DBTYPE.SQLSERVER;
}
}
///
// / Save the type of the specified database
///
/// Specify the type of database
/// Specified database name
Public Static Void SavedbType (DBTYPE DBTYPE, STRING DBNAME)
{
Appconfig.saveAppSetting (DBNAME DBTYPEENDS, DBTYPE.TOSTRING ());
}
///
/// Get the connection string of the specified database
///
/// Specified database name
///
Public static string getDbconnectionstring (String Dbname)
{
Return Appconfig.GetAppSetting (DBNAME DBCONNECTIONENDS);
}
///
// / Save the connection string of the specified database
///
/// Connection string
/// Specified database name
Public static void savedbconnectionstring (string connectionstring, string dbname)
{
Appconfig.SAVEAppSetting (DBNAME DBCONNECTIONENDS, Connectionstring);
}
}
Then write a class for each database to perform an operation for the database, such as for SQL Server:
///
/// The class for SQL data source operations
///
Public Class Sqlexec
{
///
/// Get the database connection, read the settings of DBNAME "DBCON" from the storm.appsetting profile (such as "TestDbcon" for database TEST), if not, throw an exception
///
/// To get the database name
///
Public Static SqlConnection GetDbconnection (String Dbname)
{
Return New SqlConnection (DBSETTING.GETDBCONNECTIONSTRING ());
}
Private void ModifyDatabase (DATATABLEEXTEND [] DTS, STRING DBNAME
{
// Open connection
SqlConnection SQLCON = getDbconnection (dbname);
Sqlcon.open ();
/ / Generate multiple data adapters according to the data table and generate SQL statements separately
INT length = dts.length;
Sqldataadapter [] mydataadapters = new sqldataadapter [length]; for (int i = 0; i { String selectText = GetSelectCommand (DTS [i] .datablename); MyDataAdapters [i] = new sqldataadapter (selecttext, sqlcon); SQLCommandbuilder CB = New Sqlcommandbuilder (MyDataAdapters [i]); MyDataAdapters [I] .insertCommand = CB.GetInsertCommand (); MyDataAdapters [i] .updateCommand = Cb.getUpdateCommand (); MyDataAdapters [i] .deleteCommand = Cb.getDeleteCommand (); } // Configure a transaction Sqltransaction myTrans; MyTrans = Sqlcon.Begintransaction (isolationledLevel.RepeAtableRead); Try { For (INT i = 0; I { MyDataAdapters [i] .selectcommand.transaction = MyTrans; MyDataAdapters [i] .insertcommand.transaction = myTrans; MyDataAdapters [i] .updateCommand.transaction = MyTrans; MyDataAdapters [i] .deleteCommand.transaction = MyTrans; / / Update the database MyDataAdapters [i] .update (DTS [i] .datable; } MyTrans.commit (); Sqlcon.close (); For (INT i = 0; I { DTS [i] .dataable.acceptchanges (); } } // If you fail, you automatically roll back Catch (Exception EE) { MyTrans.rollback (); Sqlcon.close (); For (INT i = 0; I { DTS [I] .datable.rejectchanges (); } Throw EE; } } /// /// Read data from the database /// /// Data table to carry data /// Query statement Public void getData (DataTable DT, String SelectString, String Dbname) { SqlDataAdapter mydataadapter = new sqldataadapter (selectString, Sqlconfig.getdbConnection (dbname)); MyDataAdapter.Fill (DT); } / / Automatically generate query statements Private static string getSelectCommand (String DataTableName) { String strg = "select * from" DataTablename; Return strg; } } Then write a class to call these stuff according to the actual situation: Public Class DatabaseExecute { PRIVATE STRING DBNAME; /// // Target database /// Public String Dbname { Get {return dbname;} Set {DBNAME = Value; } /// /// Generate an instance of DatabaseExecute /// Public DatabaseExecute () { DBNAME = NULL; } /// // Generate DatabaseModifier instance with the specified target database /// /// Public DatabaseExecute (String Dbname) { THIS.DBNAME = DBNAME; } /// /// Read data from the database /// /// Data table to carry data /// Query statement Public void getData (DataTable DT, String SelectString) { // Operation specified database IF (DBNAME! = NULL) { IF (dbsetting.getdbtype (dbname) == dbtype.sqlserver) { SQLEXEC mysqlexec = new sqlexec (); GetData (DT, SELECTSTRING, DBNAME); } ELSE IF (dbsetting.getdbtype (dbname) == dbtype.odbc) { Odbcexec myodbcexec = new odbcexec (); Myodbcexec. getData (DT, SelectString, DBNAME); } Else IF (dbsetting.getdbtype (dbname) == dbtype.Oledb) { OLEDBEXEC MyoledBexec = New OLEDBEXEC (); GetData (DT, SELECTSTRING, DBNAME); } Else { Oracleexec myoracleexec = new oracleexec (); Myoracleexec. getData (DT, SelectString, DBNAME); } } // Operation Default Database Else { IF (dbsetting.getdbtype (") == dbtype.sqlserver) { SQLEXEC mysqlexec = new sqlexec (); GetData (DT, SELECTSTRING, ""); } Else IF (dbsetting.getdbtype (") == dbtype.odbc) { Odbcexec myodbcexec = new odbcexec (); Getbcexec. getData (DT, SELECTSTRING, ""); } Else IF (dbsetting.getdbtype (dbname) == dbtype.Oledb) { OLEDBEXEC MyoledBexec = New OLEDBEXEC (); GetData (DT, SELECTSTRING, ""); } Else { Oracleexec myoracleexec = new oracleexec (); Myoracleexec. getData (DT, SelectString, ""); } } } /// / / Update the database according to the data table group /// /// Data table group to update Public void modifydatabase (DATATABLEEXTEND [] DTS) { // Operation specified database IF (DBNAME! = NULL) { IF (dbsetting.getdbtype (dbname) == dbtype.sqlserver) { SQLEXEC mysqlexec = new sqlexec (); Mysqlexec ModifyDatabase (DTS, DBNAME); } ELSE IF (dbsetting.getdbtype (dbname) == dbtype.odbc) { Odbcexec mysqlexec = new odbcexec (); Myodbcexec ModifyDatabase (DTS, DBNAME); } Else IF (dbsetting.getdbtype (dbname) == dbtype.Oledb) { OLEDBEXEC mysqlexec = new OLEDBEXEC (); MyoledBexec ModifyDatabase (DTS, DBNAME); } Else { Oracleexec mysqlexec = new oracleexec (); Myoracleexec ModifyDatabase (DTS, DBNAME); } } // Operation Default Database Else { IF (dbsetting.getdbtype (") == dbtype.sqlserver) { SQLEXEC mysqlexec = new sqlexec (); Mysqlexec ModifyDatabase (DTS, "); } ELSE IF (dbsetting.getdbtype (dbname) == dbtype.odbc) { Odbcexec mysqlexec = new odbcexec (); Myodbcexec ModifyDatabase (DTS, ""); } Else IF (dbsetting.getdbtype (dbname) == dbtype.Oledb) { OLEDBEXEC mysqlexec = new OLEDBEXEC (); MyoledBexec ModifyDatabase (DTS, "); } Else { Oracleexec mysqlexec = new oracleexec (); Myoracleexec ModifyDatabase (DTS, "); } } } This way, as long as this DatabaseEexecute class is referenced in the project. Finally, pay attention to: 1. For multi-table operation, because the interval is associated, the order in which the operation is important, the order of this component operation is to post-post-back processing from the data sheet array, please pay attention to the order of table processing! 2. The default database connection is determined by the settings of "dbcon" in the configuration file, non-default database connection determines the settings of "* dbcon" in the configuration file, where the asterisk represents the database ID 3. The default database type is determined by the settings of "dbcon" in the configuration file. Non-default database types are determined by the settings of "* dbcon" in the configuration file, in which the asterisk represents the database ID 4. There are two configurations for each database, namely database connection and database types, respectively.
