C# 访问 Access 数据库的参考代码和连接语句的写法
可以作为AccessHelper使用,直接粘贴后放置在DAL层即可
using
System;
using
System.IO;
using
System.Collections;
using
System.Data;
using
System.Data.OleDb;
namespace
Exam_Manage.DAL
...
{ /**/ /// <summary> /// 试题数据访问基础类 /// </summary> 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语句 /**/ /// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString"> SQL语句 </param> /// <returns> 影响的记录数 </returns> 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); } } } } /**/ /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList"> 多条SQL语句 </param> 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 ;n < SQLStringList.Count;n ++ ) ... { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1 ) ... { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (System.Data.OleDb.OleDbException E) ... { tx.Rollback(); throw new Exception(E.Message); } } } /**/ /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString"> SQL语句 </param> /// <param name="content"> 参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加 </param> /// <returns> 影响的记录数 </returns> 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(); } } } /**/ /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="SQLString"> 计算查询结果语句 </param> /// <returns> 查询结果(object) </returns> 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); } } } } /**/ /// <summary> /// 执行查询语句,返回SqlDataReader /// </summary> /// <param name="strSQL"> 查询语句 </param> /// <returns> SqlDataReader </returns> 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); } } /**/ /// <summary> /// 执行查询语句,返回DataSet /// </summary> /// <param name="SQLString"> 查询语句 </param> /// <returns> DataSet </returns> 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; } } #endregion 执行带参数的SQL语句 #region 执行带参数的SQL语句 /**/ /// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString"> SQL语句 </param> /// <returns> 影响的记录数 </returns> 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); } } } } /**/ /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="SQLString"> 计算查询结果语句 </param> /// <returns> 查询结果(object) </returns> 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); } } } } /**/ /// <summary> /// 执行查询语句,返回SqlDataReader /// </summary> /// <param name="strSQL"> 查询语句 </param> /// <returns> SqlDataReader </returns> 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); } } /**/ /// <summary> /// 执行查询语句,返回DataSet /// </summary> /// <param name="SQLString"> 查询语句 </param> /// <returns> DataSet </returns> 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); } } #endregion } }
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=2214826