C#access数据库连接代码.docx

1、C#access数据库连接代码using System;using System.Collections;using System.Collections.Specialized;using System.Data;using System.Data.OleDb;using System.Configuration;namespace Maticsoft.DBUtility / / Copyright (C) Maticsoft / 数据访问基础类(基于OleDb) / 可以用户可以修改满足自己项目的需要。 / public abstract class DbHelperOleDb /数据库连

2、接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. /public static string connectionString = |DataDirectory|+pp.asp; public static string connectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + System.Web.HttpContext.Current.Server.MapPath(/db/dbtest.mdb); public DbHelperOleDb() #region 公用方

3、法 public static int GetMaxID(string FieldName, string TableName) string strsql = select max( + FieldName + )+1 from + TableName; object obj = DbHelperSQL.GetSingle(strsql); if (obj = null) return 1; else return int.Parse(obj.ToString(); public static bool Exists(string strSql) object obj = DbHelperS

4、QL.GetSingle(strSql); int cmdresult; if (Object.Equals(obj, null) | (Object.Equals(obj, System.DBNull.Value) cmdresult = 0; else cmdresult = int.Parse(obj.ToString(); if (cmdresult = 0) return false; else return true; public static bool Exists(string strSql, params OleDbParameter cmdParms) object ob

5、j = GetSingle(strSql, cmdParms); int cmdresult; if (Object.Equals(obj, null) | (Object.Equals(obj, System.DBNull.Value) cmdresult = 0; else cmdresult = int.Parse(obj.ToString(); if (cmdresult = 0) return false; else return true; #endregion #region 执行简单SQL语句 / / 执行SQL语句，返回影响的记录数 / / SQL语句 / 影响的记录数 pu

6、blic static int ExecuteSql(string SQLString) using (OleDbConnection connection = new OleDbConnection(connectionString) using (OleDbCommand cmd = new OleDbCommand(SQLString, connection) try connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; catch (System.Data.OleDb.OleDbException E) co

7、nnection.Close(); throw new Exception(E.Message); / / 执行多条SQL语句，实现数据库事务。 / / 多条SQL语句 public static void ExecuteSqlTran(ArrayList SQLStringList) using (OleDbConnection conn = new OleDbConnection(connectionString) conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; OleDbTransact

8、ion tx = conn.BeginTransaction(); cmd.Transaction = tx; try for (int n = 0; n 1) cmd.CommandText = strsql; cmd.ExecuteNonQuery(); tx.Commit(); catch (System.Data.OleDb.OleDbException E) tx.Rollback(); throw new Exception(E.Message); / / 执行带一个存储过程参数的的SQL语句。 / / SQL语句 / 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这

9、个方式添加 / 影响的记录数 public static int ExecuteSql(string SQLString, string content) using (OleDbConnection connection = new OleDbConnection(connectionString) OleDbCommand cmd = new OleDbCommand(SQLString, connection); System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter(cont

10、ent, OleDbType.VarChar); myParameter.Value = content; cmd.Parameters.Add(myParameter); try connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; catch (System.Data.OleDb.OleDbException E) throw new Exception(E.Message); finally cmd.Dispose(); connection.Close(); / / 向数据库里插入图像格式的字段(和上面情况类

11、似的另一种实例) / / SQL语句 / 图像字节,数据库的字段类型为image的情况 / 影响的记录数 public static int ExecuteSqlInsertImg(string strSQL, byte fs) using (OleDbConnection connection = new OleDbConnection(connectionString) OleDbCommand cmd = new OleDbCommand(strSQL, connection); System.Data.OleDb.OleDbParameter myParameter = new Sys

12、tem.Data.OleDb.OleDbParameter(fs, OleDbType.Binary); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; catch (System.Data.OleDb.OleDbException E) throw new Exception(E.Message); finally cmd.Dispose(); connection.Close(); /

13、/ 执行一条计算查询结果语句，返回查询结果（object）。 / / 计算查询结果语句 / 查询结果（object） public static object GetSingle(string SQLString) using (OleDbConnection connection = new OleDbConnection(connectionString) using (OleDbCommand cmd = new OleDbCommand(SQLString, connection) try connection.Open(); object obj = cmd.ExecuteScala

14、r(); if (Object.Equals(obj, null) | (Object.Equals(obj, System.DBNull.Value) return null; else return obj; catch (System.Data.OleDb.OleDbException e) connection.Close(); throw new Exception(e.Message); / / 执行查询语句，返回OleDbDataReader / / 查询语句 / OleDbDataReader public static OleDbDataReader ExecuteReade

15、r(string strSQL) OleDbConnection connection = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand(strSQL, connection); try connection.Open(); OleDbDataReader myReader = cmd.ExecuteReader(); return myReader; catch (System.Data.OleDb.OleDbException e) throw new Exception(e.Messa

16、ge); / / 执行查询语句，返回DataSet / / 查询语句 / DataSet public static DataSet Query(string SQLString) using (OleDbConnection connection = new OleDbConnection(connectionString) DataSet ds = new DataSet(); try connection.Open(); OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection); command.Fill

17、(ds, ds); catch (System.Data.OleDb.OleDbException ex) throw new Exception(ex.Message); return ds; #endregion #region 执行带参数的SQL语句 / / 执行SQL语句，返回影响的记录数 / / SQL语句 / 影响的记录数 public static int ExecuteSql(string SQLString, params OleDbParameter cmdParms) using (OleDbConnection connection = new OleDbConnect

18、ion(connectionString) using (OleDbCommand cmd = new OleDbCommand() try PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; catch (System.Data.OleDb.OleDbException E) throw new Exception(E.Message); / / 执行多条SQL语句，实现数据库事务。

19、/ / SQL语句的哈希表（key为sql语句，value是该语句的OleDbParameter） public static void ExecuteSqlTran(Hashtable SQLStringList) using (OleDbConnection conn = new OleDbConnection(connectionString) conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction() OleDbCommand cmd = new OleDbCommand(); try /循环 foreach

20、 (DictionaryEntry myDE in SQLStringList) string cmdText = myDE.Key.ToString(); OleDbParameter cmdParms = (OleDbParameter)myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); trans.Commit(); catch trans.Rollback(); throw; / / 执行一条计算

21、查询结果语句，返回查询结果（object）。 / / 计算查询结果语句 / 查询结果（object） public static object GetSingle(string SQLString, params OleDbParameter cmdParms) using (OleDbConnection connection = new OleDbConnection(connectionString) using (OleDbCommand cmd = new OleDbCommand() try PrepareCommand(cmd, connection, null, SQLStri

22、ng, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if (Object.Equals(obj, null) | (Object.Equals(obj, System.DBNull.Value) return null; else return obj; catch (System.Data.OleDb.OleDbException e) throw new Exception(e.Message); / / 执行查询语句，返回OleDbDataReader / / 查询语句 / OleDbDataR

23、eader public static OleDbDataReader ExecuteReader(string SQLString, params OleDbParameter cmdParms) OleDbConnection connection = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand(); try PrepareCommand(cmd, connection, null, SQLString, cmdParms); OleDbDataReader myReader = cm

24、d.ExecuteReader(); cmd.Parameters.Clear(); return myReader; catch (System.Data.OleDb.OleDbException e) throw new Exception(e.Message); / / 执行查询语句，返回DataSet / / 查询语句 / DataSet public static DataSet Query(string SQLString, params OleDbParameter cmdParms) using (OleDbConnection connection = new OleDbCo

25、nnection(connectionString) OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (OleDbDataAdapter da = new OleDbDataAdapter(cmd) DataSet ds = new DataSet(); try da.Fill(ds, ds); cmd.Parameters.Clear(); catch (System.Data.OleDb.OleDbException ex) throw new Exception(ex.Message); return ds; private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction tran