[C #] using ADO to access a multi-database C # Database

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.