C # for (oracle\sqlce\sqlite) simple operation

1: Connect Oracle

A: Install Oracle server (11g/10g)

B: Add Reference:

C: Introduction of namespaces:using System.Data.OracleClient;

D: Connection character:

<summary>///get Connection object///</summary>///<param name= "user" > Username </param>///<param name= " Password "> Password </param>///<param name=" url "> Connection URL, such as (LOCALHOST:1521/ORCL) </param>///< Returns>oracleconnection</returns>public oracleconnection getoracleconnection (String user,String password , string url) {string source = "Data source=" +url+ "; Integrated Security=no; User id= "+user+"; password= "+password;oracleconnection oraconn = new OracleConnection (source); Oraconn.open (); return oraconn;}

2: Connection SqlCE

A: Install SQLCE

B: Add Reference:

C: Introduction of namespaces:using System.Data.SQLite;

D: Test Connection

<summary>///get Connection object///</summary>///<param name= "source" > Data source (Database storage path), such as: E:\dataSource\ postingsys.sdf</param>///<param name= "password" > password </param>///<returns>sqlceconnection </returns>public sqlceconnection getsqlceconnection (String source,string password) {Source = @ "Data source=" + Source+ "; Password= "+password; SqlCeConnection sqlce = new SqlCeConnection (source); SqlCe. Open (); return sqlce;}

3: Connect SQLite

A: Install SQLite

B: Add Reference:

C: namespaces:using System.Data.SQLite;

D: Test Connection:

<summary>///get Connection object///</summary>///<param name= "source" > Data source (Database storage path), such as E:\dataSource\ openises.db</param>///<returns>sqliteconnection Connection Object </returns>public SQLiteConnection Getsqliteconnection (String db) {sqliteconnection SLC = new Sqliteconnection ("DateTimeKind = utc;data Source =" + db); SLC.O Pen (); return SLC;}

4: Because I found that C # operations on the database is very repetitive, the following only for the Oracle encapsulation operation. Because the other two ideas are basically the same, but the objects and methods are different. Download

• Oracle

Update

///<summary>///operation: update\add\delete///If you are using an update, you need to be aware of the following//1: update specifies its fields, The parameter that holds the placeholder cannot be null///</summary>///<param name= "OC" > Connection object </param>///<param name= "SQLText" > Executed SQL statement </param>///<param name= "Arraypara" > Placeholder parameter </param>///<returns>int The number of rows changed after the return operation </ returns>public int executeupdate (oracleconnection oc,string sqltext,list<oracleparameter> ArrayPara) { OracleCommand oracom = null;int edit = 0;try{oracom = new OracleCommand (); oracom.connection = Oc;oracom.commandtext = SqlT Ext;foreach (OracleParameter op in Arraypara) {oraCom.Parameters.Add (OP);} Oracom.transaction = oc. BeginTransaction ();//Open Transaction edit = Oracom.executenonquery (); OraCom.Transaction.Commit ();//After the transaction commits, the transaction object is NULL, but the session does not end !} catch (Exception ex) {if (oracom!=null) {oraCom.Transaction.Rollback ();//Rollback}console.writeline ("\ n operation failed, exception: {0}", ex.) Message); throw;} return edit;} 

Enquiry

<summary>///operation: Query///default is query all. SQLText for post-condition statement///</summary>///<param name= "OC" >oracle Connection object </param>///<param name= "SqlText" > Conditional statements (and XX=:XX) </param>///<param name= "Arraypara" > Placeholder parameters (for example: id_x,:name_x) </param>///< param name= "clazz" > query table corresponding entity class </param>///<returns>list qualifying data </returns>public List<object > ExecuteSelect (oracleconnection oc,string sqltext,list<oracleparameter> arraypara,type clazz) {List< object> Clazzarray = new list<object> ();//Execute object OracleCommand comm = new OracleCommand (); Comm. CommandText = "SELECT * from" + clazz. Name + "where 1=1" +sqltext;comm. Connection = oc;//parameter foreach (OracleParameter op in Arraypara) {comm. Parameters.Add (OP);} Execute Sqloracledatareader dataReader = Comm. ExecuteReader (); int fielen = Datareader.fieldcount;while (Datareader.read ()) {Object objtemp = Activator.CreateInstance (clazz);//gets each column of each row. for (int x = 0; x < fielen;x++) {String fiename = datareader.getname (x);Object fievalue= datareader.getvalue (x); FieldInfo fieinfo = Clazz. GetField (fiename,bindingflags.ignorecase| bindingflags.nonpublic| bindingflags.instance);/* Type conversion: */string typeName = fieinfo.fieldtype.name;if (fievalue.tostring () ==string.empty | | Fievalue.tostring (). Length < 1) Continue;fievalue = Transformationutils.typetransform (Typename,fievalue); Fieinfo.setvalue (ObjTemp, Fievalue);} Clazzarray.add (objtemp);} return Clazzarray;}

Tools download