ArticleDirectory
- I. C # Database
- Ii. Example
- Iii. Notes
[C #] using ADO to access a multi-database C # Database
Luo chaohui (http://www.cnblogs.com/kesalin)
This article follows the "signature-non-commercial use-consistency" creation public agreement
I. C # Database
A c # database that supports accessing multiple databases (OLE, MySQL, SQL Server, and Oracle) through ADO. The implementation is quite simple. You can use the factory method to create Wrapper Classes for various database accesses.
Source code download: Click here to download
Or visit GitHub: https://github.com/kesalin/CSharpSnippet/tree/master/Database
The class diagram is as follows:
Idatabase is an open interface class, which defines a bunch of database operation interface methods;
Databasefactory is the factory class of the window database;
Databasetype is a database type enumeration;
Databasehelper encapsulates some common database-related gadgets, such as easily creating connection strings and reading values from dataset.
Ii. Example
ExampleCodeAs follows:
View code
Internal Class Program { Private Idatabase _ dB; Private Const Databasetype _ dbtype = Databasetype. MySQL; # Region Database related // You need to create a MySQL database named "sample" with columns // ID (INT), name (varchar (45), address (varchar (45), age (INT) for this test. // Private Void Createdatabase (){ If (_ DB = Null ){ // Setup you database information here. // VaR Connstr = databasehelper. createconnectionstring (_ dbtype, " Localhost " , " Sample " , " Root " , " 123456 " ); _ DB = Databasefactory. createdatabase (_ dbtype, connstr ); If (_ DB = Null ) Console. writeline ( " > Failed to create database with connection string {0 }. " , Connstr ); Else Console. writeline ( " > Created database. " );}} Private Void Closedatabase (){ If (_ DB! = Null ) {_ DB. Dispose (); _ DB = Null ;}} Public Void Testinsert (){ If (_ DB = Null ) Return ; Const String Sqlcmd = " Insert into customer (ID, name, address, age) values (0, 'floating white cloud', 'shanghai Zhangjiang Gaoke ', 28) " ; Try {_ DB. open (); _ dB. excutesql (sqlcmd); console. writeline ( " >>> Succeed. {0} " , Sqlcmd );} Catch (Exception ex) {console. writeline ( " > Failed to {0}. {1} " , Sqlcmd, Ex. Message );} Finally {_ DB. Close ();}} Public Void Testfind (){ If (_ DB = Null ) Return ; Const String Sqlcmd = " Select name, address, age from customer where name = 'floating white clouds' " ; Try {_ DB. open (); VaR Dataset = _ DB. excutesqlfordataset (sqlcmd ); VaR Recordcount =Databasehelper. getrowcount (Dataset); console. writeline ( " >>> Excuted {0} " , Sqlcmd); console. writeline ( " > Found {0} record. " , Recordcount ); For ( Int I = 0 ; I <recordcount; I ++ ){ VaR Name = databasehelper. getvalue (dataset, I, 0 ) As String ; VaR Address = databasehelper. getvalue (dataset, I, 1 ) As String ; VaR Age = databasehelper. getintvalue (dataset, I, 2 ); Console. writeline ( " > Record {0}, name: {1}, address: {2}, age: {3} " , I + 1 , Name, address, age );}} Catch (Exception ex) {console. writeline ( " > Failed to {0}. {1} " , Sqlcmd, Ex. Message );} Finally {_ DB. Close ();}} Public Void Testupdate (){ If (_ DB = Null ) Return ; Const String Sqlcmd = " Update customer set address = 'zhangjiang Gaoke 'Where name = 'floating white clouds' " ; Try {_ DB. open (); _ dB. excutesql (sqlcmd); console. writeline ( " >>> Succeed. {0} " , Sqlcmd );} Catch (Exception ex) {console. writeline ( " > Failed to {0}. {1} " , Sqlcmd, Ex. Message );} Finally {_ DB. Close ();}} Public Void Testdelete (){ If (_ DB = Null ) Return ; Const String Sqlcmd = " Delete from customer where name = 'floating white clouds' " ; Try {_ DB. open (); _ dB. excutesql (sqlcmd); console. writeline ( " >>> Succeed. {0} " , Sqlcmd );} Catch (Exception ex) {console. writeline ( " > Failed to {0}. {1} " , Sqlcmd, Ex. Message );} Finally {_ DB. Close ();}} # Endregion Static Void Main ( String [] ARGs ){ VaR Runner = New Program (); runner. createdatabase (); runner. testinsert (); runner. testfind (); runner. testupdate (); runner. testfind (); runner. testdelete (); runner. testfind (); runner. closedatabase (); console. readline ();}}
Output result:
Iii. Notes
If you do not know much about common database command syntax, refer to the following link:
// SQL syntax
//
Select: http://en.wikipedia.org/wiki/Select_ (SQL)
Insert: http://en.wikipedia.org/wiki/Insert_ (SQL)
Delete: http://en.wikipedia.org/wiki/Delete_ (SQL)
Update: http://en.wikipedia.org/wiki/Update_ (SQL)
Truncate: http://en.wikipedia.org/wiki/Truncate_ (SQL)
Because different database vendors have different database implementations, There are some minor differences in the database command syntax, so pay special attention to it. The following lists some common differences:
1. Maximum number of query records
Use top keywords for SQL Server. For example:
Select Top 100 * from customer
Use the limit keyword for MySQL. For example:
Select * From Client limit 100
Use rownum for Oracle <=. For example:
Select * from customer where rownum <= 100
2. escape characters in the Command (For details, refer to the databdate method of the databasehelper class)
For SQL Server, single quotation marks must be replaced with two single quotation marks. Double quotation marks must be replaced with two double quotation marks;
For MySQL, the single quotation mark 'must be replaced with \'; The backslash \ is used to replace.