c# 访问Access数据库

using System;

using System.IO;

using System.Collections;

using System.Data;

using System.Data.OleDb;

namespace Exam_Manage.DAL { /**//// /// 试题数据访问基础类 /// public abstract class DbSQL { public DbSQL() { } protected static string connectionString=@"provider=microsoft.jet.oledb.4.0; " + "Data Source= " +Directory.GetCurrentDirectory() + @"mdbdb.mdb"; //执行简单SQL语句#region 执行简单SQL语句 /**//// /// 执行SQL语句，返回影响的记录数 /// ///SQL语句 /// 影响的记录数 public 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) { connection.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; OleDbTransaction tx=conn.BeginTransaction(); cmd.Transaction=tx; try { for(int n=0;n1) { cmd.CommandText=strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch(System.Data.OleDb.OleDbException E) { tx.Rollback(); throw new Exception(E.Message); } } } /**//// /// 执行带一个存储过程参数的的SQL语句。 /// ///SQL语句 ///参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 /// 影响的记录数 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( "@content",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(); } } } /**//// /// 执行一条计算查询结果语句，返回查询结果（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.ExecuteScalar(); 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); } } } } /**//// /// 执行查询语句，返回SqlDataReader /// ///查询语句 /// SqlDataReader public static OleDbDataReader ExecuteReader(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.Message); } } /**//// /// 执行查询语句，返回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(ds,"ds"); } catch(System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } return ds; } } //执行带参数的SQL语句#region 执行带参数的SQL语句 /**//// /// 执行SQL语句，返回影响的记录数 /// ///SQL语句 /// 影响的记录数 public static int ExecuteSql(string SQLString,params OleDbParameter[] cmdParms) { using (OleDbConnection connection = new OleDbConnection(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); } } } } /**//// /// 执行一条计算查询结果语句，返回查询结果（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,SQLString, 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); } } } } /**//// /// 执行查询语句，返回SqlDataReader /// ///查询语句 /// SqlDataReader 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 = cmd.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 OleDbConnection(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 trans, string cmdText, OleDbParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (OleDbParameter parm in cmdParms) cmd.Parameters.Add(parm); } } } }