1 using System;  2 using System.Data;  3 using System.Configuration;  4 using System.Data.OleDb;  5 using System.Collections;  6 using System.Windows.Forms;  7 using System.Security.Cryptography;  8 using System.Text;  9 /// <summary> 10 /// AcceHelper 的摘要说明 11 /// </summary> 12 public static class AccessHelper 13 { 14     //数据库连接字符串 15     //public static readonly string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; 16     /// <summary> 17     /// 打开数据库 18     /// </summary> 19     public static string GetConn() 20     {           21        //conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + AppDomain.CurrentDomain.BaseDirectory+ConfigurationManager.AppSettings["myconn"].ToString() + ";Jet OLEDB:Database PassWord=sa";             22             try 23             { 24             string strConnection = "Provider=Microsoft.Jet.OleDb.4.0;"; 25             string filedata = http://www.mamicode.com/Application.StartupPath + @"\lz_db.dat"; 26             strConnection += @"Data Source=" + filedata; 27             return strConnection; 28             } 29             catch (Exception e) 30             { 31                 throw new Exception(e.Message); 32             } 33     } 34     // 用于缓存参数的HASH表 35     private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); 36     /// <summary> 37     ///  给定连接的数据库用假设参数执行一个sql命令（不返回数据集） 38     /// </summary> 39     /// <param name="connectionString">一个有效的连接字符串</param> 40     /// <param name="commandText">存储过程名称或者sql命令语句</param> 41     /// <param name="commandParameters">执行命令所用参数的集合</param> 42     /// <returns>执行命令所影响的行数</returns> 43     public static int ExecuteNonQuery(string connectionString, string cmdText, params OleDbParameter[] commandParameters) 44     { 45         OleDbCommand cmd = new OleDbCommand(); 46         using (OleDbConnection conn = new OleDbConnection(connectionString)) 47         { 48             PrepareCommand(cmd, conn, null, cmdText, commandParameters); 49             int val = cmd.ExecuteNonQuery(); 50             cmd.Parameters.Clear(); 51             return val; 52         } 53     } 54     /// <summary> 55     /// 用现有的数据库连接执行一个sql命令（不返回数据集） 56     /// </summary> 57     /// <remarks> 58     ///举例:   59     ///  int result = ExecuteNonQuery(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); 60     /// </remarks> 61     /// <param name="conn">一个现有的数据库连接</param> 62     /// <param name="commandText">存储过程名称或者sql命令语句</param> 63     /// <param name="commandParameters">执行命令所用参数的集合</param> 64     /// <returns>执行命令所影响的行数</returns> 65     public static int ExecuteNonQuery(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters) 66     { 67         OleDbCommand cmd = new OleDbCommand(); 68         PrepareCommand(cmd, connection, null, cmdText, commandParameters); 69         int val = cmd.ExecuteNonQuery(); 70         cmd.Parameters.Clear(); 71         return val; 72     } 73     /// <summary> 74     ///使用现有的SQL事务执行一个sql命令（不返回数据集） 75     /// </summary> 76     /// <remarks> 77     ///举例:   78     ///  int result = ExecuteNonQuery(trans, "PublishOrders", new OleDbParameter("@prodid", 24)); 79     /// </remarks> 80     /// <param name="trans">一个现有的事务</param> 81     /// <param name="commandText">存储过程名称或者sql命令语句</param> 82     /// <param name="commandParameters">执行命令所用参数的集合</param> 83     /// <returns>执行命令所影响的行数</returns> 84     public static int ExecuteNonQuery(OleDbTransaction trans, string cmdText, params OleDbParameter[] commandParameters) 85     { 86         OleDbCommand cmd = new OleDbCommand(); 87         PrepareCommand(cmd, trans.Connection, trans, cmdText, commandParameters); 88         int val = cmd.ExecuteNonQuery(); 89         cmd.Parameters.Clear(); 90         return val; 91     } 92     /// <summary> 93     /// 用执行的数据库连接执行一个返回数据集的sql命令 94     /// </summary> 95     /// <remarks> 96     /// 举例:   97     ///  OleDbDataReader r = ExecuteReader(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); 98     /// </remarks> 99     /// <param name="connectionString">一个有效的连接字符串</param>100     /// <param name="commandText">存储过程名称或者sql命令语句</param>101     /// <param name="commandParameters">执行命令所用参数的集合</param>102     /// <returns>包含结果的读取器</returns>103     public static OleDbDataReader ExecuteReader(string connectionString, string cmdText, params OleDbParameter[] commandParameters)104     {105         //创建一个SqlCommand对象106         OleDbCommand cmd = new OleDbCommand();107         //创建一个SqlConnection对象108         OleDbConnection conn = new OleDbConnection(connectionString);109         //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，110         //因此commandBehaviour.CloseConnection 就不会执行111         try112         {113             //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数114             PrepareCommand(cmd, conn, null, cmdText, commandParameters);115             //调用 SqlCommand  的 ExecuteReader 方法116             OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);117             //清除参数118             cmd.Parameters.Clear();119             return reader;120         }121         catch122         {123             //关闭连接，抛出异常124             conn.Close();125             throw;126         }127     }128     /// <summary>129     /// 返回一个DataSet数据集130     /// </summary>131     /// <param name="connectionString">一个有效的连接字符串</param>132     /// <param name="cmdText">存储过程名称或者sql命令语句</param>133     /// <param name="commandParameters">执行命令所用参数的集合</param>134     /// <returns>包含结果的数据集</returns>135     public static DataSet ExecuteDataSet(string connectionString, string cmdText, params OleDbParameter[] commandParameters)136     {137         //创建一个SqlCommand对象，并对其进行初始化138         OleDbCommand cmd = new OleDbCommand();139         using (OleDbConnection conn = new OleDbConnection(connectionString))140         {141             PrepareCommand(cmd, conn, null, cmdText, commandParameters);142             //创建SqlDataAdapter对象以及DataSet143             OleDbDataAdapter da = new OleDbDataAdapter(cmd);144             DataSet ds = new DataSet();145             try146             {147                 //填充ds148                 da.Fill(ds);149                 // 清除cmd的参数集合 150                 cmd.Parameters.Clear();151                 //返回ds152                 return ds;153             }154             catch155             {156                 //关闭连接，抛出异常157                 conn.Close();158                 throw;159             }160         }161     }162     /// <summary>163     /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列164     /// </summary>165     /// <remarks>166     ///例如:  167     ///  Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));168     /// </remarks>169     ///<param name="connectionString">一个有效的连接字符串</param>170     /// <param name="commandText">存储过程名称或者sql命令语句</param>171     /// <param name="commandParameters">执行命令所用参数的集合</param>172     /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>173     public static object ExecuteScalar(string connectionString, string cmdText, params OleDbParameter[] commandParameters)174     {175         OleDbCommand cmd = new OleDbCommand();176         using (OleDbConnection connection = new OleDbConnection(connectionString))177         {178             PrepareCommand(cmd, connection, null, cmdText, commandParameters);179             object val = cmd.ExecuteScalar();180             cmd.Parameters.Clear();181             return val;182         }183     }184     /// <summary>185     /// 用指定的数据库连接执行一个命令并返回一个数据集的第一列186     /// </summary>187     /// <remarks>188     /// 例如:  189     ///  Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));190     /// </remarks>191     /// <param name="conn">一个存在的数据库连接</param>192     /// <param name="commandText">存储过程名称或者sql命令语句</param>193     /// <param name="commandParameters">执行命令所用参数的集合</param>194     /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>195     public static object ExecuteScalar(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)196     {197         OleDbCommand cmd = new OleDbCommand();198         PrepareCommand(cmd, connection, null, cmdText, commandParameters);199         object val = cmd.ExecuteScalar();200         cmd.Parameters.Clear();201         return val;202     }203     /// <summary>204     /// 将参数集合添加到缓存205     /// </summary>206     /// <param name="cacheKey">添加到缓存的变量</param>207     /// <param name="cmdParms">一个将要添加到缓存的sql参数集合</param>208     public static void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters)209     {210         parmCache[cacheKey] = commandParameters;211     }212     /// <summary>213     /// 找回缓存参数集合214     /// </summary>215     /// <param name="cacheKey">用于找回参数的关键字</param>216     /// <returns>缓存的参数集合</returns>217     public static OleDbParameter[] GetCachedParameters(string cacheKey)218     {219         OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];220         if (cachedParms == null)221             return null;222         OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];223         for (int i = 0, j = cachedParms.Length; i < j; i++)224             clonedParms = (OleDbParameter[])((ICloneable)cachedParms).Clone();225         return clonedParms;226     }227     /// <summary>228     /// 准备执行一个命令229     /// </summary>230     /// <param name="cmd">sql命令</param>231     /// <param name="conn">Sql连接</param>232     /// <param name="trans">Sql事务</param>233     /// <param name="cmdText">命令文本,例如：Select * from Products</param>234     /// <param name="cmdParms">执行命令的参数</param>235     private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)236     {237         //判断连接的状态。如果是关闭状态，则打开238         if (conn.State != ConnectionState.Open)239             conn.Open();240         //cmd属性赋值241         cmd.Connection = conn;242         cmd.CommandText = cmdText;243         //是否需要用到事务处理244         if (trans != null)245             cmd.Transaction = trans;246         cmd.CommandType = CommandType.Text;247         //添加cmd需要的存储过程参数248         if (cmdParms != null)249         {250             foreach (OleDbParameter parm in cmdParms)251                 cmd.Parameters.Add(parm);252         }253     }254 }