连接Access数据库的DAL层操作代码
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
}
}
