public class DbHelper
{
private string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Environment.CurrentDirectory + "\\db.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False;";
// private static string ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0; Data Source=" + Environment.CurrentDirectory + "\\db.mdb;";
private string Connection64String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Environment.CurrentDirectory + "\\db.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False;";
/// <summary>
/// <summary>
/// 实例化
/// </summary>
public DbHelper() { }
public DbHelper(string sDbName)
{
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Environment.CurrentDirectory + "\\db_"+ sDbName + ".mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False;";
Connection64String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Environment.CurrentDirectory + "\\db_" + sDbName + ".mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False;";
}
public string ConnString
{
get
{
if (Environment.Is64BitOperatingSystem)
{
return Connection64String;
}
else
{
return ConnectionString;
}
}
}
/// <summary>
/// 执行查询语句,返回OleDbDataReader
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>OleDbDataReader</returns>
public OleDbDataReader ExecuteReader(string strSQL)
{
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand Cmd = new OleDbCommand(strSQL, Conn);
try
{
Conn.Open();
OleDbDataReader MyReader = Cmd.ExecuteReader();
return MyReader;
}
catch (OleDbException e) { throw new Exception(e.Message); }
finally
{
Cmd.Dispose();
Conn.Close();
}
}
public OleDbDataReader ExecuteReader(string strSQL, OleDbParameter[] Parame)
{
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand Cmd = new OleDbCommand(strSQL, Conn);
Cmd.CommandType = CommandType.Text;
Cmd.Parameters.AddRange(Parame);
try
{
Conn.Open();
OleDbDataReader MyReader = Cmd.ExecuteReader();
return MyReader;
}
catch (OleDbException e)
{
throw new Exception(e.Message);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString)
{
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand Cmd = new OleDbCommand(SQLString, Conn);
try
{
Conn.Open();
int rows = Cmd.ExecuteNonQuery();
return rows;
}
catch (OleDbException Ex)
{
throw Ex;
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// /// <param name="sConnString">链接串</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString, string sConnString)
{
OleDbConnection Conn = new OleDbConnection(sConnString);
OleDbCommand Cmd = new OleDbCommand(SQLString, Conn);
try
{
Conn.Open();
int rows = Cmd.ExecuteNonQuery();
return rows;
}
catch (OleDbException Ex)
{
throw Ex;
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
public int ExecuteSql(string SQLString, OleDbParameter[] Parame)
{
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand Cmd = new OleDbCommand(SQLString, Conn);
Cmd.CommandType = CommandType.Text;
Cmd.Parameters.AddRange(Parame);
try
{
Conn.Open();
int rows = Cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
public int ExecuteSql(string SQLString, OleDbParameter[] Parame, string sConnString)
{
OleDbConnection Conn = new OleDbConnection(sConnString);
OleDbCommand Cmd = new OleDbCommand(SQLString, Conn);
Cmd.CommandType = CommandType.Text;
Cmd.Parameters.AddRange(Parame);
try
{
Conn.Open();
int rows = Cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string SQLString)
{
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand cmd = new OleDbCommand(SQLString, Conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet MyDataSet = new DataSet();
try
{
da.Fill(MyDataSet);
return MyDataSet;
}
catch (OleDbException ex)
{
throw ex;
}
finally
{
cmd.Dispose();
Conn.Close();
}
}
public DataSet ExecuteDataSet(string SQLString, string sConnString)
{
OleDbConnection Conn = new OleDbConnection(sConnString);
OleDbCommand cmd = new OleDbCommand(SQLString, Conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet MyDataSet = new DataSet();
try
{
da.Fill(MyDataSet);
return MyDataSet;
}
catch (OleDbException ex)
{
throw ex;
}
finally
{
cmd.Dispose();
Conn.Close();
}
}
public DataSet ExecuteDataSet(string SQLString, OleDbParameter[] Parame)
{
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand Cmd = new OleDbCommand(SQLString, Conn);
Cmd.CommandType = CommandType.Text;
Cmd.Parameters.AddRange(Parame);
OleDbDataAdapter da = new OleDbDataAdapter(Cmd);
DataSet MyDataSet = new DataSet();
try
{
da.Fill(MyDataSet);
return MyDataSet;
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
public DataSet ExecuteDataSet(string SQLString, OleDbParameter[] Parame, string sConnString)
{
OleDbConnection Conn = new OleDbConnection(sConnString);
OleDbCommand Cmd = new OleDbCommand(SQLString, Conn);
Cmd.CommandType = CommandType.Text;
Cmd.Parameters.AddRange(Parame);
OleDbDataAdapter da = new OleDbDataAdapter(Cmd);
DataSet MyDataSet = new DataSet();
try
{
da.Fill(MyDataSet);
return MyDataSet;
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
/// <summary>
/// 执行多条SQL语句,实现数据库事务。(Server)
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public void ExecuteSqlTran(ArrayList SQLStringList)
{
OleDbConnection Conn = new OleDbConnection(ConnString);
{
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);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
}
/// <summary>
/// 查询记录是否存在
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public bool Exists(string strSql)
{
object obj = 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 bool Exists(string strSql, string sConnString)
{
object obj = GetSingle(strSql, sConnString);
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;
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
private object GetSingle(string SQLString)
{
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand cmd = new OleDbCommand(SQLString, Conn);
try
{
Conn.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)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
Conn.Close();
}
}
private object GetSingle(string SQLString, string sConnString)
{
OleDbConnection Conn = new OleDbConnection(sConnString);
OleDbCommand cmd = new OleDbCommand(SQLString, Conn);
try
{
Conn.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)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
Conn.Close();
}
}
/// <summary>
/// 返回首行首列
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>首行首列值</returns>
public string ExecuteScalar(string SQLString)
{
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand cmd = new OleDbCommand(SQLString, Conn);
try
{
Conn.Open();
return Convert.ToString(cmd.ExecuteScalar());
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
Conn.Close();
}
}
public string ExecuteScalar(string SQLString, string sConnString)
{
OleDbConnection Conn = new OleDbConnection(sConnString);
OleDbCommand cmd = new OleDbCommand(SQLString, Conn);
try
{
Conn.Open();
return Convert.ToString(cmd.ExecuteScalar());
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
Conn.Close();
}
}
/// <summary>
/// 返回首行首列
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>首行首列值</returns>
public string ExecuteScalar(string SQLString, OleDbParameter[] Parame)
{
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand cmd = new OleDbCommand(SQLString, Conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(Parame);
try
{
Conn.Open();
return Convert.ToString(cmd.ExecuteScalar());
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
Conn.Close();
}
}
public string ExecuteScalar(string SQLString, OleDbParameter[] Parame, string sConnString)
{
OleDbConnection Conn = new OleDbConnection(sConnString);
OleDbCommand cmd = new OleDbCommand(SQLString, Conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(Parame);
try
{
Conn.Open();
return Convert.ToString(cmd.ExecuteScalar());
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
Conn.Close();
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="sProdcedureName"></param>
/// <returns></returns>
public OleDbDataAdapter ExecuteProcedure(string sProdcedureName, OleDbParameter[] Parame)
{
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = Conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(Parame);
try
{
Conn.Open();
OleDbDataAdapter oleAd = new OleDbDataAdapter(cmd);
return oleAd;
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
Conn.Close();
}
}
/// <summary>
/// 测试连接数据库是否成功
/// </summary>
/// <returns></returns>
public bool ConnectionTest(string sConn)
{
bool IsCanConnectioned = false;
OleDbConnection Conn = new OleDbConnection(sConn);
try
{
Conn.Open();
IsCanConnectioned = true;
}
catch
{
IsCanConnectioned = false;
}
finally
{
Conn.Close();
}
if (Conn.State == ConnectionState.Closed || Conn.State == ConnectionState.Broken)
{
return IsCanConnectioned;
}
else
{
return IsCanConnectioned;
}
}
}