用于Access数据库的DB操作类AccessHelper.cs
程序员文章站
2022-05-17 08:06:16
...
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
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; } } }
示意图:
推荐阅读
-
C#实现的ACCESS数据库操作类完整实例
-
C#实现的ACCESS数据库操作类完整实例
-
Laravel之DB类操作数据库(创建表、添加字段、创建控制器、添加数据、修改数据、删除数据、显示查询数据、返回ID的方法getId)
-
一个数据库操作类,适用于Oracle,ACCESS,SQLSERVER
-
php操作access数据库的类分享
-
php操作access数据库的类分享
-
用于Access数据库的DB操作类AccessHelper.cs
-
Java语言mysql数据库的访问步骤,一个简单的实例——使用DAO(数据库操作类 Data Access Object ) 实现对mysql数据库的增删改查
-
一个数据库操作类,适用于Oracle,ACCESS,SQLSERVER