Self-built tool set development document ------ Database Operation (1.0.0.1)
version number
founder
Create time
Note
1.0.0.1 Musi Road 2003-12-21 Draft
Keywords: C #, tool set, database, connection pool
text:
.NET Framework provides an ADO.NET powerful, and the main purpose of this database processing class is to process database processing, automatically generate simple SQL statements, hide database details for the upper layer, which is convenient for expansion and maintenance. And you can put it in other systems and don't need to make a procedure change, just modify some of the configuration information.
Design goals
1. Implement the database connection pool and configure the size of the pool, configure whether to use the connection pool.
2. Automatically generate insert, delete, updated SQL statements.
3. Automatically generate simple queries, aggregate the SQL statement of the query.
4. Configure the operation class collection that can be implemented for different databases.
5. Hide database details on the upper layer, hide data source details.
6. Automatically export the database structure from the database and store it in the XML file for use.
Design ideas:
The main idea is to encapsulate all the operations related to the database. The internal implementation uses an alias for automatically generating SQL statements. Try to separate various coupling, with a configuration file to reduce reuse or modification. Workload. For example, modifications to the field name of the table will not affect changes in tools and systems.
The operation of Microsoft SQL Server2000 is currently implemented.
Structural description:
It mainly includes an interface and 4 classes, and their relationships are shown below:
1. IDBase Interface Class: Define a public method for a series of database operations, and this excuse must be implemented for an operation class written for a database or other data source.
2. SQLDBase class: Implement all database operations, including insert, update, delete, query, execution stored procedure, etc., implement the above interface definition method.
3. DBASEFActory class: Define a factory, decide to call the database operation class according to the information configured in the system configuration file. In this system, only database operation classes in SQL Server can be extended to other database operations. . Used the factory mode.
4. DBPool Class: A database connection pool is implemented, and you can get available database connections, using a single piece (Singleton) mode.
5. OwnConnection Class: Encapsulated the SQLConnection object provided by the frame, adding a layer of status information, mainly for DBPool class.
These classes are described one by one.
OwnConnection class:
The SQLConnection object provided by the frame is encapsulated, and the main purpose is to join a custom state. A plurality of connection objects will be stored in the database connection pool, and the operation of the connection object will primarily refer to the custom state here.
Field:
Private string _connectionstring = constant.data_base_connection;
Private sqlconnection _myconnection = new sqlConnection ();
Private constant.connectionStatus_status;
* _Connectionstring: Database connection string, constructed with constants of Constant classes.
* _MyConnection: Database connection, SqlConnection object * _status status, values are determined by an enumeration type in the constant class, with these values free, busy, death.
method:
Private bool openconnection ()
Public ownConnection ()
Public SqlConnection getConnection ()
Public void settobusy ()
Public void resetConnection ()
* OpenConnection (): Private method, open the database connection according to the field connectionString, throw an exception.
* OWNCONNECTION (): The public constructor, in the internal call method OpenConnection (), set the state of this instance, open successfully set to Free, otherwise set to Death.
* GetConnection (): Get the available database connection and returns a SQLConnection object.
* SetTobusy (): Set the status of this instance to BUSY, and the logo is in use.
* ResetConnection (): Reset the database connection, if the connection status is invalid, call openconnection () to re-open the connection.
DBPOOL class
The database connection pool class, uses the Singleton mode to ensure uniqueness of the instance, maintain a connection object array according to the size of the connection pool set in the configuration file, and provide a database operation class to the database operation class.
Field:
Private static arraylist dbpools = new arraylist (constant.init_pool_size)
Private static int initpoolsize;
Private static int validconnectioncount;
Private static volatile dbpool instance = new dbpool ();
Private static object syncroot = new object ();
Since the DBPool class can only have an instance in the system, the fields are marked as static. These fields will stay in memory and initialize when the system is started.
* DBPools: Store a static array arraylist in the database connection, the size of the connection pool defined in the system configuration file.
* InitPoolSize: The size of the pool defined in the connection pool size, the value, and the pool defined in the system configuration file is consistent.
* ValidConnectionCount: The number of connections available in the connection pool is not available for 0, and the initial is 0.
* Instance: Connect the pool object, instantiate when initialization, to instantiate the connection pool object when the system is started, this is part of the single-piece mode, which will specifically tell me about this mode.
* Syncroot: Initialize a synchronization object, prevent deadlocks in single-mode, will be described later.
method:
Private static bool initpool ()
Private dbpool ()
Public Static dbpool instancepublic static wnconnection getConnection ()
Public Static SqlConnection GetConnection (String Conname)
* INitPool (): According to the database connection pool size, initialize the database connection pool, and return to the initial success or failure flag, the current program returns success, and will be processed by the upper layer.
* Dbpool (): Private constructor prevents instantiation. Set the field and call the initPool () initialize the connection pool.
* Instance: Get instances of database connection pools.
* GetConnection (): Get a database connection.
* GetConnection (String Conname): Reburses the above method, which will determine whether to taking data from the connection pool based on the parameters. This is considered to be: Maybe a system will correspond to several databases, while the connection pool is placed based on a database, and other database connections will be obtained.
Singleton mode application, code as shown below:
Private static volatile dbpool instance = new dbpool ();
Private static object syncroot = new object ();
Private dbpool ()
{
Try
{
INitPoolsize = constant.init_pool_size;
ValidConnectionCount = 0;
INitPool ();
}
Catch (Utilexception E)
{
}
Catch (Exception E)
{
Utilexception myexception = new utilexception (e, "");
}
}
Public Static DBPOOL Instance
{
get
{
IF (instance == null)
{
Lock (syncroot)
{
IF (instance == null)
Instance = new dbpool ();
}
}
Return Instance;
}
}
Static field instance declaration, will call a private constructor. When the system is started, there is a database connection pool object. When you need to use the connection pool object, you will be accessed by static attribute instance, mainly indicates the inside of this property. achieve.
1. When the system is started, there should be a connection pool instance if it is not directly returned to the email, otherwise
2. Lock (syncroot) Make sure that another thread does not enter the critical area, and the mutually exclusive access is performed to prevent simultaneous requests to generate multiple instances.
This guarantees the uniqueness of the database connection pool instance, and the typical application of the Singleton mode.
Connection pool maintenance
1. Establish a database connection according to the connection pool when initializing the connection pool;
2. When the user uses getConnection (), when it is connected, it is traversed the database connection object in the connection pool. After finding the available database connection, set the status of the connection object to BUSY, then return to the connection object;
3. When there is no connection available in the connection pool, traverse the database connection object, reopen all unavailable connection objects with resetConnection (). 4. Continue with step 2, return to the available connection object; if there is still no connection to return, perform step 5
5. Throw an exception, indicating that the connection is occupied.
