using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb; namespace CommonLib
{
/// <summary>
/// 需要实例化的Access存取类
/// </summary>
public class DB_Access
{
public OleDbConnection Conn;
public string ConnString;
public DB_Access(string Dbpath)
{
ConnString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=";
ConnString += Dbpath;
Conn = new OleDbConnection(ConnString);
Conn.Open();
}
public OleDbConnection DbConn()
{
Conn.Open();
return Conn;
}
public void Close()
{
Conn.Close();
}
public DataTable SelectToDataTable(string SQL)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
DataTable Dt = new DataTable();
adapter.Fill(Dt);
return Dt;
}
public DataSet SelectToDataSet(string SQL, string subtableName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
DataSet Ds = new DataSet();
Ds.Tables.Add(subtableName);
adapter.Fill(Ds, subtableName);
return Ds;
}
public DataSet SelectToDataSet(string SQL, string subtableName, DataSet DataSetName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
DataTable Dt = new DataTable();
DataSet Ds = new DataSet();
Ds = DataSetName;
adapter.Fill(DataSetName, subtableName);
return Ds;
}
public OleDbDataAdapter SelectToOleDbDataAdapter(string SQL)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
return adapter;
}
public bool ExecuteSQLNonquery(string SQL)
{
OleDbCommand cmd = new OleDbCommand(SQL, Conn);
try
{
cmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
}
}
public static class DB_AccessStatic
{
/// <summary>
/// 为执行命令准备参数
/// </summary>
/// <param name="cmd">DbCommand 命令</param>
/// <param name="conn">已经存在的数据库连接</param>
/// <param name="trans">数据库事物处理</param>
/// <param name="cmdType">DbCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
/// <param name="cmdText">Command text，T-SQL语句 例如 Select * from Products</param>
/// <param name="cmdParms">返回带参数的命令</param>
private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, 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 = cmdType;
if (cmdParms != null)
{
foreach (OleDbParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
} public static bool ExecuteSQLNonquery(string Dbpath, string strSql, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
try
{
string ConnString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=";
ConnString += Dbpath;
OleDbConnection conn = new OleDbConnection(ConnString);
PrepareCommand(cmd, conn, null, CommandType.Text, strSql, commandParameters);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 执行一条返回结果集的OleDbCommand，通过一个已经存在的数据库连接
/// 使用参数数组提供参数
/// </summary>
/// <param name="cmdText">SQL 语句</param>
/// <returns></returns>
public static DataTableCollection GetTable(string Dbpath, string strSql, OleDbParameter[] commandParameters)
{
string ConnString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=";
ConnString += Dbpath;
return GetTable(ConnString, CommandType.Text, strSql, null);
}
/// <summary>
/// 执行一条返回结果集的OleDbCommand，通过一个已经存在的数据库连接
/// 使用参数数组提供参数
/// </summary>
/// <param name="connecttionString">一个现有的数据库连接</param>
/// <param name="cmdTye">OleDbCommand命令类型</param>
/// <param name="cmdText">存储过程的名字或者 SQL 语句</param>
/// <param name="commandParameters">以数组形式提供DbCommand命令中用到的参数列表</param>
/// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>
public static DataTableCollection GetTable(string connecttionString, CommandType cmdTye, string cmdText, OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(connecttionString))
{ PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds);
}
DataTableCollection table = ds.Tables;
return table;
} }
}