using System; using System.Data; using System.Data.OleDb; namespace AmlSystem.Data { /// <summary> /// Written: [China] Zhao Zhe /// Modify : [China] 饶绍泉 /// Date: Sep. 2004 /// Modify : 2008.09 /// Version 1.0 /// Support MyBask /// Looking for the latest version or similar implentation of this function, please visit:<see cref="http://www.mybask.net"/> /// /// Purpose: /// To make connection to access easier.a Access version of SqlHelper. /// Describe: /// - Add several overload functions, which made it easy to use. /// - only for DataReader /// /// </summary> public abstract class OLEHelper { public OLEHelper() { } /// <summary> /// 执行一段SQL语句,只有三个最简的必要参数,省去了CommandType /// To excute a SQL statement, which reuturns a integer stand for effect line number. /// default Command type is text /// </summary> /// <param name="connString">连接字符串 (Conntection String)</param> /// <param name="cmdText">Command的SQL语句 (SQL Statement)</param> /// <param name="cmdParms">参数列表 (Paramters)</param> /// <returns>返回影响行数 (effect line number)</returns> public static int ExecuteNonQuery(string connString, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connString)) { PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 执行一段SQL语句 /// To excute a SQL statement, which reuturns a integer stand for effect line number. /// </summary> /// <param name="connString">连接字符串 (Connection String)</param> /// <param name="cmdType">CommandType (Command type)</param> /// <param name="cmdText">Command的SQL语句 (SQL Statement)</param> /// <param name="cmdParms">参数列表 (Paramters)</param> /// <returns>返回影响行数 (effect line number)</returns> public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 执行一段SQL语句 /// To excute a SQL statement, which reuturns a integer stand for effect line number. /// a connection is passed in instead of a connection string /// </summary> /// <param name="conn">已初始化的 OleDbConnection (a Conncection)</param> /// <param name="connString">连接字符串 (Conntection String)</param> /// <param name="cmdText">Command的SQL语句 (SQL Statement)</param> /// <param name="cmdParms">参数列表 (Paramters)</param> /// <returns>返回影响行数 (effect line number)</returns> public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// 执行一段SQL语句,需要传入一个事务Transaction. /// To excute a SQL statement, which reuturns a integer stand for effect line number. /// a transaction is reqired /// </summary> /// <param name="trans">一个Trasaction (Trasaction)</param> /// <param name="cmdType">Command的SQL语句 (SQL Statement)</param> /// <param name="cmdText">CommandType (Command type)</param> /// <param name="cmdParms">参数列表 (Paramters)</param> /// <returns>返回影响行数 (effect line number)</returns> public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// 执行一段查询语句返回DataReader,省略了CommandType参数 /// To excute a SQL statement, and reuturns a dataReader. /// default command type is text /// </summary> /// <param name="connString">连接字符串 (Conntection String)</param> /// <param name="cmdText">Command的SQL 语句 (SQL Statement)</param> /// <param name="cmdParms">参数列表 (Paramters)</param> /// <returns>DataReader</returns> public static OleDbDataReader ExecuteReader(string connString, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); OleDbConnection conn = new OleDbConnection(connString); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms); OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// <summary> /// 执行一段查询语句返回DataReader /// To excute a SQL statement, and reuturns a dataReader. /// </summary> /// <param name="connString">连接字符串 (Connection String)</param> /// <param name="cmdType">Command的SQL语句 (SQL Statement)</param> /// <param name="cmdText">CommandType (Command type)</param> /// <param name="cmdParms">参数列表 (Paramters)</param> /// <returns>DataReader</returns> public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); OleDbConnection conn = new OleDbConnection(connString); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// <summary> /// 用于读取一个值,查询所返回的是结果集中第一行的第一列,省去了CommandType /// To excute a SQL statement, and returns the first column of the first line /// Default command type is text /// </summary> /// <param name="connString">连接字符串 (Conntection String)</param> /// <param name="cmdText">Command的SQL语句 (SQL Statement)</param> /// <param name="cmdParms">参数列表 (Paramters)</param> /// <returns>结果集中第一行的第一列</returns> public static object ExecuteScalar(string connString, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connString)) { PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 用于读取一个值,查询所返回的是结果集中第一行的第一列 /// To excute a SQL statement, and returns the first column of the first line /// </summary> /// <param name="connString">连接字符串 (Connection String)</param> /// <param name="cmdType">Command的SQL语句 (SQL Statement)</param> /// <param name="cmdText">CommandType (Command type)</param> /// <param name="cmdParms">参数列表 (Paramters)</param> /// <returns>结果集中第一行的第一列</returns> public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 用于读取一个值,查询所返回的是结果集中第一行的第一列 /// To excute a SQL statement, and returns the first column of the first line /// a connection is passed in instead of a connection string /// </summary> /// <param name="conn">已初始化的OleDbConnection (a Conncection)</param> /// <param name="connString">连接字符串 (Conntection String)</param> /// <param name="cmdText">Command的SQL语句 (SQL Statement)</param> /// <param name="cmdParms">参数列表 (Paramters)</param> /// <returns>结果集中第一行的第一列</returns> public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } /// <summary> /// 在执行SQL语句之前的准备工作 /// </summary> /// <param name="cmd">Command</param> /// <param name="conn">Connection</param> /// <param name="trans">Trasaction</param> /// <param name="cmdType">CommandType</param> /// <param name="cmdText">CommandText</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); } } } }