using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace JonseTool
{
public abstract class AccessHelper
{
// string s = Application.StartupPath.Replace(@"\bin\Debug", "") + @"\Data\#test.mdb";
JonseTool.AccessHelper.ConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + s + @";Persist Security Info=false;User Id=admin;Password=";
//JonseTool.AccessHelper.ConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + s + @";";
public static string _sConnString = string.Empty;
public static string ConnString
{
get{return _sConnString;}
set{_sConnString = value;}
}
public static bool TestConn(string sConnStr="")
{
OleDbConnection myConn = null;
bool bResult = false;
try
{
if (string.IsNullOrEmpty(sConnStr)) sConnStr = _sConnString;
myConn = new OleDbConnection(_sConnString);
myConn.Open();
}
catch (Exception ex)
{
}
finally
{
if (myConn != null && myConn.State.ToString() == "Open")
bResult = true;
}
myConn.Close();
return bResult;
}
public static DataTable GetDataTable(out string sErr, string sSQL,string sConnStr="",params OleDbParameter[] cmdParams)
{
DataTable dt = null;
sErr = string.Empty;
if (string.IsNullOrEmpty(sConnStr)) sConnStr = _sConnString;
OleDbConnection accConn = null;
try
{
accConn = new OleDbConnection(sConnStr);
OleDbCommand accCmd = new OleDbCommand(sSQL, accConn);
accConn.Open();
if (cmdParams != null)
{
foreach (OleDbParameter parm in cmdParams)
accCmd.Parameters.Add(parm);
}
OleDbDataAdapter adapter = new OleDbDataAdapter(accCmd);
dt = new DataTable();
adapter.Fill(dt);
accConn.Close();
}
catch (Exception ex)
{
sErr = ex.Message;
}
return dt;
}
// 取dataset
public static DataSet GetDataSet(out string sError, string sSQL, string sConnStr = "", params OleDbParameter[] cmdParams)
{
DataSet ds = null;
sError = string.Empty;
try
{
if (string.IsNullOrEmpty(sConnStr)) sConnStr = _sConnString;
OleDbConnection conn = new OleDbConnection(sConnStr);
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = sSQL;
if (cmdParams != null)
{
foreach (OleDbParameter parm in cmdParams)
cmd.Parameters.Add(parm);
}
OleDbDataAdapter dapter = new OleDbDataAdapter(cmd);
ds = new DataSet();
dapter.Fill(ds);
}
catch (Exception ex)
{
sError = ex.Message;
}
return ds;
}
// 取某个单一的元素
public static object GetSingle(out string sError, string sSQL,string sConnStr)
{
DataTable dt = GetDataTable(out sError, sSQL,sConnStr);
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0][0];
}
return null;
}
// 取最大的ID
public static Int32 GetMaxID(out string sError, string sKeyField, string sTableName,string sConnStr=null)
{
DataTable dt = GetDataTable(out sError, "select iif(isnull(max([" + sKeyField + "])),0,max([" + sKeyField + "])) as MaxID from [" + sTableName + "]", sConnStr);
if (dt != null && dt.Rows.Count > 0)
{
return Convert.ToInt32(dt.Rows[0][0].ToString());
}
return 0;
}
// 执行 insert,update,delete 动作,也可以使用事务
public static bool UpdateData(out string sError, string sSQL,string sConnStr="",OleDbParameter[] cmdParams = null, bool bUseTransaction = false)
{
int iResult = 0;
sError = string.Empty;
if (string.IsNullOrEmpty(sConnStr)) sConnStr = _sConnString;
if (!bUseTransaction)
{
try
{
OleDbConnection conn = new OleDbConnection(sConnStr);
if (conn.State != ConnectionState.Open)
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = sSQL;
if (cmdParams != null)
{
foreach (OleDbParameter parm in cmdParams)
cmd.Parameters.Add(parm);
}
iResult = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
sError = ex.Message;
iResult = -1;
}
}
else // 使用事务
{
OleDbTransaction trans = null;
try
{
OleDbConnection conn = new OleDbConnection(sConnStr);
if (conn.State != ConnectionState.Open)
conn.Open();
trans = conn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = sSQL;
if (cmdParams != null)
{
foreach (OleDbParameter parm in cmdParams)
cmd.Parameters.Add(parm);
}
cmd.Transaction = trans;
iResult = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
sError = ex.Message;
iResult = -1;
trans.Rollback();
}
}
return iResult > 0;
}
}
}
示意图: