C#源码: 数据库访问的简化封装
来源:互联网 发布:优化软件游戏助手 编辑:程序博客网 时间:2024/05/17 06:56
ADO.NET很好, 但是有时候访问数据库你就觉得有些繁琐了。利用封装,我们可以轻松地访问数据库,而无须考虑太多操作细节。注意:下面的类MyDatabase中的连接字符串从应用程序配置中读取。
/**//******************************************************************************
*
*
* 示例:
* MyDatabase db = new MyDatabase(); // 首先创建一个MyDataBase对象
*
* 查询数据:
* DataTable table = db.GetRecords("Product", (new string[]{"name", "price"}, null);
* foreach(DataRow row in table.rows) {
* ...
* }
*
* 添加一条记录:
* ColumnEntry entries = new ColumnEntry[] {
* new ColumnEntry("name", OleDbDbType.NChar, "Guitar"),
* new ColumnEntry("price", OleDbDbType.Decimal, 1000.0)
* };
* db.AddRecord("Product", entries);
*
* 更新一条记录:
* ColumnEntry entries = new ColumnEntry[] {
* new ColumnEntry("name", OleDbType.NChar, "Eelectric-Guitar"),
* new ColumnEntry("price", OleDbType.Decimal, 1200.0)
* };
* db.UpdateRecord("Product", entries, "productid=100");
*
*****************************************************************************/
using System;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
namespace OOHacker.Example
...{
// 列入口
public class ColumnEntry
...{
public ColumnEntry()
...{
colName = null;
colDataType = OleDbType.Char;
colValue = null;
}
// Constructor
public ColumnEntry(string _colName, OleDbType _colDataType, object _colValue)
...{
colName = _colName;
colDataType = _colDataType;
colValue = _colValue;
}
// 列名属性,可读写
public string ColName
...{
set
...{
colName = value;
}
get
...{
return colName;
}
}
// 列数据类型属性,可读写
public OleDbType ColDataType
...{
set
...{
colDataType = value;
}
get
...{
return colDataType;
}
}
// 列值属性,可读写
public object ColValue
...{
set
...{
colValue = value;
}
get
...{
return colValue;
}
}
private string colName; // 列名
private OleDbType colDataType; // 列数据类型
private object colValue; // 列值
};
/**//// <summary>
/// 数据库操纵类
/// </summary>
public class MyDatabase
...{
public MyDatabase()
...{
connString = ConfigurationManager.AppSettings["DBConnectionString"];
conn = new OleDbConnection(connString);
}
/**//// <summary>
/// 执行非查询SQL语句;
/// </summary>
/// <param name="strSql">非SELECT的SQL</param>
/// <returns>成功时返回时true,失败返回false</returns>
public bool ExecuteSqlNoQuery(string strSql)
...{
bool ret = false;
errMsg = "";
OleDbTransaction trans = null;
if (Open())
...{
try
...{
trans = conn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand(strSql, conn, trans);
cmd.ExecuteNonQuery();
trans.Commit();
ret = true;
}
catch (Exception e)
...{
trans.Rollback();
errMsg = e.Message;
}
finally
...{
conn.Close();
}
}
return ret;
}
/**//// <summary>
/// 本方法封装了获取数据的过程,只需提供表名,以及相应的列名,条件即可。
/// </summary>
/// <param name="table">表名</param>
/// <param name="cols">若cols为空(null),则返回所有列(字段);否则返回对应列</param>
/// <param name="sWhere">若要返回全部记录,sWhere应为空(null);否则返回指定记录</param>
/// <returns>成功时返回一个DataTable对象,失败时返回null</returns>
public DataTable GetRecords(string table, string[] cols, string sWhere)
...{
errMsg = "";
if (!Open()) return null;
// 构造SQL查询语句
StringBuilder sqlBuilder = new StringBuilder(256);
sqlBuilder.Append("select ");
for (int i = 0; i < cols.Length; ++i)
...{
sqlBuilder.AppendFormat("[{0}]", cols[i]);
if (i < cols.Length - 1)
...{
sqlBuilder.Append(",");
}
}
sqlBuilder.AppendFormat(" from {0}", table);
if (sWhere != null && sWhere.Trim() != "")
...{
sqlBuilder.AppendFormat(" where {0}", sWhere);
}
// 执行查询,返回查询结果
DataTable recs = null;
try
...{
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(sqlBuilder.ToString(), conn);
adapter.Fill(ds, table);
recs = ds.Tables[table];
}
catch (Exception e)
...{
errMsg = e.Message;
recs = null;
}
finally
...{
conn.Close();
}
return recs;
}
/**//// <summary>
/// 添加一条记录.本方法封装了添加数据的过程,只需提供表名,以及相应的列名,值及数据类型即可。
/// </summary>
/// <param name="table">指定更新表</param>
/// <param name="entries">要更新的列。不能为空</param>
/// <returns>成功时返回true,失败返回false;</returns>
public bool AddRecord(string table, ColumnEntry[] entries)
...{
errMsg = "";
OleDbTransaction trans = null;
if (!Open()) return false;
// 构造SQL插入语句
StringBuilder sb1 = new StringBuilder(256);
StringBuilder sb2 = new StringBuilder(256);
sb1.AppendFormat("insert into {0}(", table);
sb2.Append("values(");
for (int i = 0; i < entries.Length; ++i)
...{
sb1.AppendFormat("[{0}]", entries[i].ColName.Trim());
sb2.AppendFormat("@p{0}", entries[i].ColName.Trim());
if (i < entries.Length - 1)
...{
sb1.Append(",");
sb2.Append(",");
}
}
sb1.Append(") ");
sb2.Append(") ");
sb1.Append(sb2.ToString());
// 构造插入命令及参数
OleDbCommand cmd = new OleDbCommand(sb1.ToString(), conn);
OleDbParameterCollection paras = cmd.Parameters;
for (int i = 0; i < entries.Length; ++i)
...{
try
...{
string paraName = "@p" + entries[i].ColName.Trim();
paras.Add(paraName, entries[i].ColDataType);
paras[paraName].Value = entries[i].ColValue;
}
catch (Exception e)
...{
errMsg = "构造命令参数失败!原因:" + e.Message;
conn.Close();
return false;
}
}
bool successed = false;
// 提交插入命令,更新数据源
try
...{
trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
successed = true;
}
catch (Exception e)
...{
trans.Rollback();
errMsg = e.Message;
}
finally
...{
conn.Close();
}
return successed;
}
/**//// <summary>
/// 更改记录。本方法封装了更改数据的过程,只需提供表名,以及相应的列名,值及数据类型和
/// 条件即可。
/// </summary>
/// <param name="table">指定更新表</param>
/// <param name="entries">指定要更新的列</param>
/// <param name="sWhere">更新条件</param>
/// <returns></returns>
public bool UpdateRecord(string table, ColumnEntry[] entries, string sWhere)
...{
errMsg = "";
OleDbTransaction trans = null;
if (!Open()) return false;
// 构造SQL插入语句
StringBuilder sb1 = new StringBuilder(256);
sb1.AppendFormat("update {0} set ", table);
for (int i = 0; i < entries.Length; ++i)
...{
sb1.AppendFormat("[{0}]=@p{0}", entries[i].ColName.Trim());
if (i < entries.Length - 1)
...{
sb1.Append(",");
}
}
sb1.AppendFormat(" where {0}", sWhere);
// 构造更新命令及参数
OleDbCommand cmd = new OleDbCommand(sb1.ToString(), conn);
OleDbParameterCollection paras = cmd.Parameters;
for (int i = 0; i < entries.Length; ++i)
...{
try
...{
string paraName = "@p" + entries[i].ColName.Trim();
paras.Add(paraName, entries[i].ColDataType);
paras[paraName].Value = entries[i].ColValue;
}
catch (Exception e)
...{
errMsg = "构造命令参数失败!原因:" + e.Message;
conn.Close();
return false;
}
}
bool successed = false;
// 提交插入命令,更新数据源
try
...{
trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
successed = true;
}
catch (Exception e)
...{
trans.Rollback();
errMsg = e.Message;
}
finally
...{
conn.Close();
}
return successed;
}
public String ConnString
...{
set
...{
connString = value;
}
get
...{
return connString;
}
}
public string errMessage
...{
get
...{
return errMsg;
}
}
private bool Open()
...{
bool isOpen = false;
errMsg = "";
try
...{
conn.Open();
isOpen = true;
}
catch (Exception e)
...{
errMsg = "打开数据库失败" + e.Message;
}
return isOpen;
}
private OleDbConnection conn;
private String connString;
private String errMsg;
}
}
*
*
* 示例:
* MyDatabase db = new MyDatabase(); // 首先创建一个MyDataBase对象
*
* 查询数据:
* DataTable table = db.GetRecords("Product", (new string[]{"name", "price"}, null);
* foreach(DataRow row in table.rows) {
* ...
* }
*
* 添加一条记录:
* ColumnEntry entries = new ColumnEntry[] {
* new ColumnEntry("name", OleDbDbType.NChar, "Guitar"),
* new ColumnEntry("price", OleDbDbType.Decimal, 1000.0)
* };
* db.AddRecord("Product", entries);
*
* 更新一条记录:
* ColumnEntry entries = new ColumnEntry[] {
* new ColumnEntry("name", OleDbType.NChar, "Eelectric-Guitar"),
* new ColumnEntry("price", OleDbType.Decimal, 1200.0)
* };
* db.UpdateRecord("Product", entries, "productid=100");
*
*****************************************************************************/
using System;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
namespace OOHacker.Example
...{
// 列入口
public class ColumnEntry
...{
public ColumnEntry()
...{
colName = null;
colDataType = OleDbType.Char;
colValue = null;
}
// Constructor
public ColumnEntry(string _colName, OleDbType _colDataType, object _colValue)
...{
colName = _colName;
colDataType = _colDataType;
colValue = _colValue;
}
// 列名属性,可读写
public string ColName
...{
set
...{
colName = value;
}
get
...{
return colName;
}
}
// 列数据类型属性,可读写
public OleDbType ColDataType
...{
set
...{
colDataType = value;
}
get
...{
return colDataType;
}
}
// 列值属性,可读写
public object ColValue
...{
set
...{
colValue = value;
}
get
...{
return colValue;
}
}
private string colName; // 列名
private OleDbType colDataType; // 列数据类型
private object colValue; // 列值
};
/**//// <summary>
/// 数据库操纵类
/// </summary>
public class MyDatabase
...{
public MyDatabase()
...{
connString = ConfigurationManager.AppSettings["DBConnectionString"];
conn = new OleDbConnection(connString);
}
/**//// <summary>
/// 执行非查询SQL语句;
/// </summary>
/// <param name="strSql">非SELECT的SQL</param>
/// <returns>成功时返回时true,失败返回false</returns>
public bool ExecuteSqlNoQuery(string strSql)
...{
bool ret = false;
errMsg = "";
OleDbTransaction trans = null;
if (Open())
...{
try
...{
trans = conn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand(strSql, conn, trans);
cmd.ExecuteNonQuery();
trans.Commit();
ret = true;
}
catch (Exception e)
...{
trans.Rollback();
errMsg = e.Message;
}
finally
...{
conn.Close();
}
}
return ret;
}
/**//// <summary>
/// 本方法封装了获取数据的过程,只需提供表名,以及相应的列名,条件即可。
/// </summary>
/// <param name="table">表名</param>
/// <param name="cols">若cols为空(null),则返回所有列(字段);否则返回对应列</param>
/// <param name="sWhere">若要返回全部记录,sWhere应为空(null);否则返回指定记录</param>
/// <returns>成功时返回一个DataTable对象,失败时返回null</returns>
public DataTable GetRecords(string table, string[] cols, string sWhere)
...{
errMsg = "";
if (!Open()) return null;
// 构造SQL查询语句
StringBuilder sqlBuilder = new StringBuilder(256);
sqlBuilder.Append("select ");
for (int i = 0; i < cols.Length; ++i)
...{
sqlBuilder.AppendFormat("[{0}]", cols[i]);
if (i < cols.Length - 1)
...{
sqlBuilder.Append(",");
}
}
sqlBuilder.AppendFormat(" from {0}", table);
if (sWhere != null && sWhere.Trim() != "")
...{
sqlBuilder.AppendFormat(" where {0}", sWhere);
}
// 执行查询,返回查询结果
DataTable recs = null;
try
...{
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(sqlBuilder.ToString(), conn);
adapter.Fill(ds, table);
recs = ds.Tables[table];
}
catch (Exception e)
...{
errMsg = e.Message;
recs = null;
}
finally
...{
conn.Close();
}
return recs;
}
/**//// <summary>
/// 添加一条记录.本方法封装了添加数据的过程,只需提供表名,以及相应的列名,值及数据类型即可。
/// </summary>
/// <param name="table">指定更新表</param>
/// <param name="entries">要更新的列。不能为空</param>
/// <returns>成功时返回true,失败返回false;</returns>
public bool AddRecord(string table, ColumnEntry[] entries)
...{
errMsg = "";
OleDbTransaction trans = null;
if (!Open()) return false;
// 构造SQL插入语句
StringBuilder sb1 = new StringBuilder(256);
StringBuilder sb2 = new StringBuilder(256);
sb1.AppendFormat("insert into {0}(", table);
sb2.Append("values(");
for (int i = 0; i < entries.Length; ++i)
...{
sb1.AppendFormat("[{0}]", entries[i].ColName.Trim());
sb2.AppendFormat("@p{0}", entries[i].ColName.Trim());
if (i < entries.Length - 1)
...{
sb1.Append(",");
sb2.Append(",");
}
}
sb1.Append(") ");
sb2.Append(") ");
sb1.Append(sb2.ToString());
// 构造插入命令及参数
OleDbCommand cmd = new OleDbCommand(sb1.ToString(), conn);
OleDbParameterCollection paras = cmd.Parameters;
for (int i = 0; i < entries.Length; ++i)
...{
try
...{
string paraName = "@p" + entries[i].ColName.Trim();
paras.Add(paraName, entries[i].ColDataType);
paras[paraName].Value = entries[i].ColValue;
}
catch (Exception e)
...{
errMsg = "构造命令参数失败!原因:" + e.Message;
conn.Close();
return false;
}
}
bool successed = false;
// 提交插入命令,更新数据源
try
...{
trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
successed = true;
}
catch (Exception e)
...{
trans.Rollback();
errMsg = e.Message;
}
finally
...{
conn.Close();
}
return successed;
}
/**//// <summary>
/// 更改记录。本方法封装了更改数据的过程,只需提供表名,以及相应的列名,值及数据类型和
/// 条件即可。
/// </summary>
/// <param name="table">指定更新表</param>
/// <param name="entries">指定要更新的列</param>
/// <param name="sWhere">更新条件</param>
/// <returns></returns>
public bool UpdateRecord(string table, ColumnEntry[] entries, string sWhere)
...{
errMsg = "";
OleDbTransaction trans = null;
if (!Open()) return false;
// 构造SQL插入语句
StringBuilder sb1 = new StringBuilder(256);
sb1.AppendFormat("update {0} set ", table);
for (int i = 0; i < entries.Length; ++i)
...{
sb1.AppendFormat("[{0}]=@p{0}", entries[i].ColName.Trim());
if (i < entries.Length - 1)
...{
sb1.Append(",");
}
}
sb1.AppendFormat(" where {0}", sWhere);
// 构造更新命令及参数
OleDbCommand cmd = new OleDbCommand(sb1.ToString(), conn);
OleDbParameterCollection paras = cmd.Parameters;
for (int i = 0; i < entries.Length; ++i)
...{
try
...{
string paraName = "@p" + entries[i].ColName.Trim();
paras.Add(paraName, entries[i].ColDataType);
paras[paraName].Value = entries[i].ColValue;
}
catch (Exception e)
...{
errMsg = "构造命令参数失败!原因:" + e.Message;
conn.Close();
return false;
}
}
bool successed = false;
// 提交插入命令,更新数据源
try
...{
trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
successed = true;
}
catch (Exception e)
...{
trans.Rollback();
errMsg = e.Message;
}
finally
...{
conn.Close();
}
return successed;
}
public String ConnString
...{
set
...{
connString = value;
}
get
...{
return connString;
}
}
public string errMessage
...{
get
...{
return errMsg;
}
}
private bool Open()
...{
bool isOpen = false;
errMsg = "";
try
...{
conn.Open();
isOpen = true;
}
catch (Exception e)
...{
errMsg = "打开数据库失败" + e.Message;
}
return isOpen;
}
private OleDbConnection conn;
private String connString;
private String errMsg;
}
}
- C#源码: 数据库访问的简化封装
- 简化JDBC的数据库访问
- C#数据库访问封装类
- c#访问sybase数据库源码
- C# SQLite数据库 访问封装类
- 封装的数据库访问类
- 用索引器简化的C#类型信息访问
- 封装的访问sql数据库的类
- 封装ADO访问数据库的两个类
- java中访问数据库的封装
- C# .NET万能数据库访问封装类(ACCESS、SQLServer、Oracle)
- C# 访问数据库的用法
- HBase数据库访问封装
- C#数据库操作类的封装
- C#连接MySQL数据库的封装类
- C# ADO.NET_数据库操作的封装
- C#访问数据库。C#访问MSSQL的基本方法。
- c# 数据库操纵封装
- 搜索某个字符串在那个表的那个字段中
- 处理死锁
- 查询重复记录
- 升迁从sqlserver6.5到sqlserver2000
- 分析死锁的方法
- C#源码: 数据库访问的简化封装
- 为进行ODBC访问配置MicrosoftJet引擎
- SQL取得最大排序数字并累加1的自定义函数
- 中如何计算农历
- 通过sql语句获取的基本信息
- T-SQL生成两个新的真正的公历年历
- 感悟
- 空间数据挖掘技术理论及方法
- 对会计中凭证输入模块的一点看法