c#操作access*转载

 

 

public static class AccessHelper { //数据库连接字符串 //WebForm //public static readonly string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; //WinForm //public static readonly string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AppDomain.CurrentDomain.BaseDirectory + System.Configuration.ConfigurationManager.ConnectionStrings["WZDFGL"].ConnectionString; //public static readonly string conn_str = System.Configuration.ConfigurationManager.ConnectionStrings["WZDFGL"].ConnectionString; public static readonly string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AppDomain.CurrentDomain.BaseDirectory + System.Configuration.ConfigurationManager.ConnectionStrings["WZDFGL"].ConnectionString + ";Persist Security Info=True"; // 用于缓存参数的HASH表 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /// <summary> /// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集） /// </summary> /// <param name="connectionString">一个有效的连接字符串</param> /// <param name="commandText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>执行命令所影响的行数</returns> public static int ExecuteNonQuery(string connectionString, string cmdText, params OleDbParameter[] commandParameters) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 用现有的数据库连接执行一个sql命令（不返回数据集） /// </summary> /// <remarks> ///举例: /// int result = ExecuteNonQuery(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> /// <param name="conn">一个现有的数据库连接</param> /// <param name="commandText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>执行命令所影响的行数</returns> public static int ExecuteNonQuery(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, connection, null, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> ///使用现有的SQL事务执行一个sql命令（不返回数据集） /// </summary> /// <remarks> ///举例: /// int result = ExecuteNonQuery(trans, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> /// <param name="trans">一个现有的事务</param> /// <param name="commandText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>执行命令所影响的行数</returns> public static int ExecuteNonQuery(OleDbTransaction trans, string cmdText, params OleDbParameter[] commandParameters) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// 用执行的数据库连接执行一个返回数据集的sql命令 /// </summary> /// <remarks> /// 举例: /// OleDbDataReader r = ExecuteReader(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">一个有效的连接字符串</param> /// <param name="commandText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>包含结果的读取器</returns> public static OleDbDataReader ExecuteReader(string connectionString, string cmdText, params OleDbParameter[] commandParameters) { //创建一个SqlCommand对象 OleDbCommand cmd = new OleDbCommand(); //创建一个SqlConnection对象 OleDbConnection conn = new OleDbConnection(connectionString); //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， //因此commandBehaviour.CloseConnection 就不会执行 try { //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数 PrepareCommand(cmd, conn, null, cmdText, commandParameters); //调用 SqlCommand 的 ExecuteReader 方法 OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); //清除参数 cmd.Parameters.Clear(); return reader; } catch { //关闭连接，抛出异常 conn.Close(); throw; } } /// <summary> /// 返回一个DataSet数据集 /// </summary> /// <param name="connectionString">一个有效的连接字符串</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>包含结果的数据集</returns> public static DataSet ExecuteDataSet(string connectionString, string cmdText, params OleDbParameter[] commandParameters) { //创建一个SqlCommand对象，并对其进行初始化 OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdText, commandParameters); //创建SqlDataAdapter对象以及DataSet OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); try { //填充ds da.Fill(ds); // 清除cmd的参数集合 cmd.Parameters.Clear(); //返回ds return ds; } catch { //关闭连接，抛出异常 conn.Close(); throw; } } } public static DataSet ExecuteDataSet(OleDbConnection conn, string cmdText, params OleDbParameter[] commandParameters) { //创建一个SqlCommand对象，并对其进行初始化 OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, conn, null, cmdText, commandParameters); //创建SqlDataAdapter对象以及DataSet OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); try { //填充ds da.Fill(ds); // 清除cmd的参数集合 cmd.Parameters.Clear(); //返回ds return ds; } catch { //关闭连接，抛出异常 conn.Close(); throw; } } /// <summary> /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列 /// </summary> /// <remarks> ///例如: /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> ///<param name="connectionString">一个有效的连接字符串</param> /// <param name="commandText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> public static object ExecuteScalar(string connectionString, string cmdText, params OleDbParameter[] commandParameters) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection connection = new OleDbConnection(connectionString)) { PrepareCommand(cmd, connection, null, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 用指定的数据库连接执行一个命令并返回一个数据集的第一列 /// </summary> /// <remarks> /// 例如: /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> /// <param name="conn">一个存在的数据库连接</param> /// <param name="commandText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> public static object ExecuteScalar(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, connection, null, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } /// <summary> /// 将参数集合添加到缓存 /// </summary> /// <param name="cacheKey">添加到缓存的变量</param> /// <param name="cmdParms">一个将要添加到缓存的sql参数集合</param> public static void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters) { parmCache[cacheKey] = commandParameters; } /// <summary> /// 找回缓存参数集合 /// </summary> /// <param name="cacheKey">用于找回参数的关键字</param> /// <returns>缓存的参数集合</returns> public static OleDbParameter[] GetCachedParameters(string cacheKey) { OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey]; if (cachedParms == null) return null; OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length]; for (int i = 0, j = cachedParms.Length; i < j; i++) clonedParms = (OleDbParameter[])((ICloneable)cachedParms).Clone(); return clonedParms; } /// <summary> /// 准备执行一个命令 /// </summary> /// <param name="cmd">sql命令</param> /// <param name="conn">Sql连接</param> /// <param name="trans">Sql事务</param> /// <param name="cmdText">命令文本,例如：Select * from Products</param> /// <param name="cmdParms">执行命令的参数</param> private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms) { //判断连接的状态。如果是关闭状态，则打开 if (conn.State != ConnectionState.Open) conn.Open(); //cmd属性赋值 cmd.Connection = conn; cmd.CommandText = cmdText; //是否需要用到事务处理 if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text; //添加cmd需要的存储过程参数 if (cmdParms != null) { foreach (OleDbParameter parm in cmdParms) cmd.Parameters.Add(parm); } } }

新增

 public int InsertCby(CbyEntity objCbyEntity) { int intResult = 0; string strSql = "insert into cby (dh,xm) values (@dh,@xm)"; OleDbParameter[] prams = { new OleDbParameter("@dh", objCbyEntity.Dh), new OleDbParameter("@xm", objCbyEntity.Xm) }; intResult = AccessHelper.ExecuteNonQuery(AccessHelper.conn_str, strSql, prams); return intResult; }

事务

public bool InsertDfdjzkOrder(DfdjzkEntity etyDfdj, DfdjhmEntity etyDfdjhm, out string errmsg) { bool result = true; errmsg = ""; string strCon = AccessHelper.conn_str; OleDbConnection con = new OleDbConnection(strCon); try { con.Open(); OleDbTransaction tra = con.BeginTransaction(); //创建事务，开始执行事务 if (this.InsertDfdjzk(tra, etyDfdj) != 1) { tra.Rollback(); return false; } DfdjhmDA daDfdjhm = new DfdjhmDA(); if (daDfdjhm.UpdateDfdjhm(tra, etyDfdjhm) != 1) { tra.Rollback(); return false; } tra.Commit();//关闭事务 } catch (Exception ex) { errmsg = ex.Message; return false; } finally { con.Close(); } return result; }

批量

public bool UpdateXhRqBatch() { using (OleDbConnection conn = new OleDbConnection(AccessHelper.conn_str)) { string strSql = "SELECT * FROM dfdjzk Where 1=1 ";//and djhm>'00050235' DataSet ds; ds = AccessHelper.ExecuteDataSet(conn, strSql, null); foreach (DataRow dr in ds.Tables[0].Rows) { string djhm = dr[DfdjzkSchema.DJHM].ToString(); string xhrq = dr[DfdjzkSchema.XHRQ].ToString(); if (!string.IsNullOrEmpty(xhrq) && xhrq.Length == 4) { if (xhrq.Substring(0, 2) == "99") { xhrq = "19" + xhrq + "01"; } else { xhrq = "20" + xhrq + "01"; } string updatesql = "update Dfdjzk set xhrq='" + xhrq + "' where djhm ='" + djhm + "' "; AccessHelper.ExecuteNonQuery(conn, updatesql, null); } } } return true; }

注意点：

在access中日期前要有#

string strSql = @"insert into Dfdjzk (zhh,yhdw,bqss1,sqss1,syl1,dj1,je1, bqss2,sqss2,syl2,dj2,je2, bqss3,sqss3,syl3,dj3,je3, je,rzbz,zfbz,djhm,kprq,sssq,ssqh,xhrq,cby,sfy) values (@zhh,@yhdw,@bqss1,@sqss1,@syl1,@dj1,@je1, @bqss2,@sqss2,@syl2,@dj2,@je2, @bqss3,@sqss3,@syl3,@dj3,@je3, @je,@rzbz,@zfbz,@djhm,#" + objDfdjzkEntity.Kprq.ToString("yyyy-MM-dd") + "#,@sssq,@ssqh,@xhrq,@cby,@sfy)";