using System;
using System.Data; using System.Configuration; using System.Web; using System.Collections; using System.Collections.Generic; using System.Text; using System.IO; using ACS = System.Data.OleDb; namespace DAL { public class DataAccess { //创建连接access数据库的字符串 private string AccessCennection = null;#region 构造函数
/// <summary> /// 构造函数创建连接字符串 /// </summary> public DataAccess() { this.AccessCennection = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\\Inetpub\\wwwroot\\biz\\App_Data\\ccc.mdb";//连接数据库字符串 } #endregion#region 创建连接数据库的连接
/// <summary> /// 创建连接数据库的连接 /// </summary> /// <param name="acs_connection">连接数据库的字符串</param> /// <returns>返回实体连接</returns> private ACS.OleDbConnection CreateACS_Connection(string acs_connection) { return new ACS.OleDbConnection(acs_connection); } #endregion#region 创建操纵数据库的实体命令
/// <summary> /// 创建操纵数据库的实体命令 /// </summary> /// <param name="sql">数据库操纵语言</param> /// <param name="acs_connection">数据库连接实体</param> /// <returns>数据库命令实体</returns> private ACS.OleDbCommand CreateACS_Command(string sql, ACS.OleDbConnection acs_connection) { return new ACS.OleDbCommand(sql, acs_connection); } #endregion#region 创建填充数据用的数据适配器
/// <summary> /// 创建填充数据用的数据适配器 /// </summary> /// <param name="acs_command">数据库命令实体</param> /// <returns>数据库适配器的实体</returns> private ACS.OleDbDataAdapter CreateACS_Adapter(ACS.OleDbCommand acs_command) { return new ACS.OleDbDataAdapter(acs_command); } #endregion#region 通过查询语句返回用户需要的结果
/// <summary> /// 通过查询语句返回用户需要的结果 /// </summary> /// <param name="sql">查询语句</param> /// <returns>返回的结果集</returns> public DataSet Select(string sql) { ACS.OleDbConnection acs_connection = null; ACS.OleDbCommand acs_command; ACS.OleDbDataAdapter acs_adapter; DataSet ds = new DataSet(); try { acs_connection = CreateACS_Connection(this.AccessCennection); acs_command = CreateACS_Command(sql, acs_connection); acs_adapter = CreateACS_Adapter(acs_command); acs_connection.Open(); acs_adapter.Fill(ds); } catch (Exception acse) {}
finally { acs_connection.Close(); } return ds; } #endregion#region 批处理执行SQL操作
/// <summary> /// 批处理执行SQL操作 /// </summary> /// <param name="SqlStrings">SQL语句泛型类</param> /// <returns>是否成功</returns> public Boolean ExecuteSQL(List<String> SqlStrings) { bool success = true; int Rows = 0; ACS.OleDbConnection acs_connection = null; acs_connection = CreateACS_Connection(this.AccessCennection); ACS.OleDbCommand acs_command = new System.Data.OleDb.OleDbCommand(); acs_connection.Open(); ACS.OleDbTransaction trans = acs_connection.BeginTransaction(); acs_command.Connection = acs_connection; acs_command.Transaction = trans; try { for (int i = 0; i < SqlStrings.Count; i++) { acs_command.CommandText = SqlStrings[i].ToString(); Rows += acs_command.ExecuteNonQuery(); } trans.Commit(); } catch { success = false; trans.Rollback(); } finally { acs_connection.Close(); } if (Rows == 0) success = false; return success; } public Boolean ExecuteSQL(String SqlString) { List<String> SqlStrings = new List<String>(); SqlStrings.Add(SqlString); return ExecuteSQL(SqlStrings); } #endregion #region 在一个数据表中插入一条记录 /// <summary> /// 在一个数据表中插入一条记录 /// </summary> /// <param name="TableName">表名</param> /// <param name="Cols">哈希表，键值（Key）为字段名，值（value）为字段值</param> /// <returns>是否成功</returns> public bool Insert(String TableName, Hashtable Cols) { int Count = 0;//用于SQL语句创建 int Rows = 0;//记录受影响的行数 if (Cols.Count <= 0) { return true; } String Fields = "("; String Values = " Values('"; foreach (DictionaryEntry item in Cols) { if (Count != 0) { Fields += ","; Values += "','"; } Fields += item.Key.ToString(); Values += item.Value.ToString(); Count++; } Fields += ")"; Values += "')"; String SqlString = "insert into " + TableName + Fields + Values; return ExecuteSQL(SqlString); } #endregion #region 更新一个数据表的一条记录 /// <summary> /// 更新一个数据表的一条记录 /// </summary> /// <param name="TableName">表名</param> /// <param name="Cols">哈希表，键值为字段名，值为字段值</param> /// <param name="Where">Where字句</param> /// <returns></returns> public Boolean Update(String TableName, Hashtable Cols, String Where) { int Count = 0; if (Cols.Count <= 0) { return true; } String Fields = " "; foreach (DictionaryEntry item in Cols) { if (Count != 0) { Fields += ","; } Fields += item.Key.ToString(); Fields += "="; Fields += item.Value.ToString(); } Fields += " "; String SqlString = "update " + TableName + " set " + Fields + Where; return ExecuteSQL(SqlString); } #endregion #region 获取数据返回一个Dataset /// <summary> /// 获取数据返回一个Dataset /// </summary> /// <param name="SqlString">Sql语句</param> /// <returns>DataSet</returns> public DataSet GetDataSet(String SqlString) { ACS.OleDbConnection acs_connection = null; ACS.OleDbCommand acs_command; ACS.OleDbDataAdapter acs_adapter; DataSet ds = new DataSet(); try { acs_connection = CreateACS_Connection(this.AccessCennection); acs_command = CreateACS_Command(SqlString, acs_connection); acs_adapter = CreateACS_Adapter(acs_command); acs_connection.Open(); acs_adapter.Fill(ds); } catch (Exception acse) { } finally { acs_connection.Close(); } if (ds.Tables.Count == 0) { return null; } return ds; } #endregion #region 根据用户所给的条件返回用户需要的数据 /// <summary> /// 根据用户所给的条件返回用户需要的数据 /// </summary> /// <param name="TableName">表名</param> /// <param name="ParameterTypes">参数类型</param> /// <param name="ParameterValues">参数值</param> /// <returns>DataSet</returns> public DataSet Select(String TableName, List<String> ParameterTypes, List<String> ParameterValues) { StringBuilder sbSql = new StringBuilder("SELECT * FROM "); sbSql.Append(TableName); if (ParameterTypes[0] != null && ParameterValues[0] != null) { sbSql.Append(" WHERE ").Append(ParameterTypes[0]).Append(" ='").Append(ParameterValues[0]).Append("'"); //如果还有参数就继续加 for (int i = 1; i < ParameterTypes.Count; i++) { sbSql.Append(" and ").Append(ParameterTypes[i]).Append(" ='").Append(ParameterValues[i]).Append("'"); } } String SqlString = sbSql.ToString(); return GetDataSet(SqlString); } /// <summary> /// 根据用户所给的条件返回用户需要的数据（单参数） /// </summary> /// <param name="TableName">表名</param> /// <param name="ParameterType">参数类型</param> /// <param name="ParameterValue">参数值</param> /// <returns>DataSet</returns> public DataSet Select(String TableName, String ParameterType, String ParameterValue) { List<String> ParameterTypes = new List<String>(); List<String> ParameterValues = new List<String>(); ParameterTypes.Add(ParameterType); ParameterValues.Add(ParameterValue); return Select(TableName, ParameterTypes, ParameterValues); } #endregion #region 获取数据，返回一个DataRow /// <summary> /// 获取数据，返回一个DataRow /// </summary> /// <param name="SqlString">Sql语句</param> /// <returns>DataRow</returns> public DataRow GetDataRow(String SqlString) { DataSet ds = GetDataSet(SqlString); ds.CaseSensitive = false; if (ds.Tables[0].Rows.Count > 0) { return ds.Tables[0].Rows[0]; } else { return null; } } /// <summary> /// 根据条件返回用户需要的数据 /// </summary> /// <param name="TableName">表名</param> /// <param name="ParameterTypes">参数类型</param> /// <param name="ParameterValues">参数值</param> /// <returns>DataRow</returns> public DataRow Select_DataRow(String TableName, List<String> ParameterTypes, List<String> ParameterValues) { DataSet ds = Select(TableName, ParameterTypes, ParameterValues); ds.CaseSensitive = false; if (ds.Tables.Count > 0) { if (ds.Tables[0].Rows.Count > 0) { return ds.Tables[0].Rows[0]; } else { return null; } } else { return null; } }/// <summary>
/// 根据用户给定的条件返回数据（单参数） /// </summary> /// <param name="TableName">表名</param> /// <param name="ParameterType">参数类型</param> /// <param name="ParameterValue">参数值</param> /// <returns>DataRow</returns> public DataRow Select_DataRow(String TableName, String ParameterType, String ParameterValue) { List<String> ParameterTypes = new List<String>(); List<String> ParameterValues = new List<String>(); ParameterTypes.Add(ParameterType); ParameterValues.Add(ParameterValue); return Select_DataRow(TableName, ParameterTypes, ParameterValues); } #endregion #region 从一个DataRow中，安全得到colname中的值，值为字符串类型 /// <summary> /// 从一个DataRow中，安全得到colname中的值，值为字符串类型 /// </summary> /// <param name="row">数据行对象</param> /// <param name="colname">列名</param> /// <returns>如果值存在，返回；否则，返回System.String.Empty</returns> public static String ValidateDataRow_S(DataRow row, String colname) { if (row[colname] != DBNull.Value) return row[colname].ToString(); else return System.String.Empty; } #endregion } }