可以作为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;
}
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;
}