数据库访问操作类（有源代码），希望大家提出建议和批评～～

[txdlf]

public abstract class DbBase
{
protected String _ConectionString = null;

protected IDbConnection _IDbConnection;

#region 打开关闭数据库操作
protected virtual void OpenConnection()
{
if(this._IDbConnection.State == ConnectionState.Closed)
{
this._IDbConnection.Open();
}
}
protected virtual void CloseConnection()
{
if(this._IDbConnection.State == ConnectionState.Open)
{
this._IDbConnection.Close();
}
}
#endregion

#region 产生Guid
public static String NewGuid36()
{
String Guid = System.Guid.NewGuid().ToString();
return Guid;
}
public static String NewGuid32()
{
String Guid = System.Guid.NewGuid().ToString();
Guid = Guid.Replace("-","");
return Guid;
}
#endregion
#region 定义数据库抽象访问方法
public abstract DataTable GetDataTable(String queryText);
public abstract DataTable GetDataTable(String queryText,Int32 startRecord,Int32 recordNum,String scrTable);
public abstract Object GetResult(String queryText);
public abstract Int32 ExcuteSQL(String sqlText);
public abstract Int32[] ExcuteSQL(IsolationLevel isolLevel,params String[] sqlTexts);

public abstract Boolean IsInTable(String fieldName,String fieldValue,String tableName);
#endregion
public DbBase()
{
}
}

[txdlf]

这个类主要是定义了数据库访问的一些标准方法，如果有缺少的大家可以提出来。
下面是用Sql的实现类：
public class DbSql:DbBase
{
public DbSql(string conString)
{
this._ConectionString = conString;
this._DbConnection = new SqlConnection(this._ConectionString);
this._IDbConnection = this._DbConnection;
}
private SqlConnection _DbConnection;

#region 实现继承抽象方法
public override DataTable GetDataTable(String queryText)
{
this.OpenConnection();
DataTable dt = new DataTable();
try
{
SqlDataAdapter da = new SqlDataAdapter(queryText,this._DbConnection);
da.Fill(dt);
return dt;
}
catch(InvalidOperationException)
{
throw;
}
finally
{
this.CloseConnection();
}
}

public override DataTable GetDataTable(String queryText,Int32 startRecord,Int32 recordNum,String srcTable)
{
this.OpenConnection();
DataSet ds = new DataSet();
try
{
SqlDataAdapter da = new SqlDataAdapter(queryText,this._DbConnection);
da.Fill(ds,startRecord,recordNum,srcTable);
return ds.Tables[0];
}
catch(InvalidOperationException)
{
throw;
}
catch(ArgumentException)
{
throw;
}
finally
{
this.CloseConnection();
}
}

public override Object GetResult(String queryText)
{
this.OpenConnection();
SqlCommand cmd = new SqlCommand(queryText,this._DbConnection);
try
{
object obj= cmd.ExecuteScalar();
return obj;
}
catch(SqlException)
{
throw;
}
finally
{
this.CloseConnection();
}
}

public override Int32 ExcuteSQL(String sqlText)
{
this.OpenConnection();
SqlCommand cmd = new SqlCommand(sqlText,this._DbConnection);
try
{
int rows = cmd.ExecuteNonQuery(); return rows;
}
catch(SqlException)
{
throw;
}
finally
{
this.CloseConnection();
}
}
public override Int32[] ExcuteSQL(IsolationLevel isolLevel,params String[] sqlTexts)
{
this.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = this._DbConnection;
//启动一个事务
SqlTransaction myTrans;
myTrans = this._DbConnection.BeginTransaction(isolLevel);
cmd.Transaction = myTrans;
try
{
int[] affectRows = new int[sqlTexts.Length];
for(int i=0;i<sqlTexts.Length;++i)
{
cmd.CommandText = sqlTexts[i];
affectRows[i] = cmd.ExecuteNonQuery();
}
myTrans.Commit();
return affectRows;
}
catch(SqlException)
{
myTrans.Rollback();
throw;
}
finally
{
this.CloseConnection();
}
}
public override Boolean IsInTable(String fieldName,String fieldValue,String tableName)
{
if(fieldName == null || fieldName == "")
{
throw new ArgumentNullException(fieldName,fieldName + "不能为空。");
}
if(tableName == null || tableName == "")
{
throw new ArgumentNullException(tableName,tableName + "不能为空。");
}
if(fieldValue.Trim() != "")
{
String strSql = "SELECT count(*) Num FROM " + tableName + " WHERE " + fieldName + "='" + fieldValue + "'";
DataTable dt = this.GetDataTable(strSql);
if((int)dt.Rows[0]["Num"] == 0)
{return false;}
else
{return true;}
}
else{return false;}
}
#endregion
}

[txdlf]

除了这些还有一个TableBase类和一个代码生产器，主要用来对表进行插入、更新、删除操作用的

[txdlf]

TableBase:
/// <summary>
/// 数据表操作基础类
/// </summary>0
public class TableBase
{
private String _Guid;

protected String _TableName;

protected String _KeyField = "Guid";

protected DbBase _DBA = new DbSql(Common.Configuration.DB_ConnectionString);

public String Guid
{
get{return this._Guid;}
set{this._Guid = value;}
}

protected virtual void sqlInsertPart(out String fields ,out String values)
{
fields = "";
values = "";
fields += "," + this._KeyField;
fields = fields.TrimStart(',');

values += ",'" + this._Guid + "'";
values = values.TrimStart(',');
}

protected virtual void sqlUpdatePart(out String setPart)
{
setPart = "";
setPart += "," + this._KeyField + "='" + this._Guid + "'";
setPart = setPart.TrimStart(',');
}

/// <summary>
/// 向数据库中插入记录SQL语句
/// </summary>
/// <returns></returns>
protected virtual String InsertSql()
{
String strFields;
String strValues;
if(this._Guid.Trim() == "")
{
this._Guid = DbBase.NewGuid36();
}
this.sqlInsertPart(out strFields,out strValues);
string strSql = "INSERT INTO " + this._TableName + "(" + strFields + ") VALUES (" + strValues + ")";
return strSql;
}

/// <summary>
/// 更新当前记录SQL语句
/// </summary>
/// <returns></returns>
protected virtual String UpdateSql()
{
String strUpdate;
this.sqlUpdatePart(out strUpdate);
string strSql = "UPDATE " + this._TableName.Trim() + " SET " + strUpdate + " where (" + this._KeyField + "='"+ this._Guid +"')";
return strSql;
}

/// <summary>
/// 获取保存数据库的SQL语句
/// </summary>
/// <returns></returns>
public virtual String SaveSql()
{
if(this._DBA.IsInTable(this._KeyField,this.Guid,this._TableName))
{
return this.UpdateSql();
}
else
{
return this.InsertSql();
}
}


/// <summary>
/// 将本实例保存到数据库
/// </summary>
/// <returns>是否成功</returns>
public virtual Boolean Save()
{
if(this._DBA.ExcuteSQL(this.SaveSql()) == 1)
{return true;}
else
{return false;}
}


/// <summary>
/// 从数据库中删除本实例
/// </summary>
/// <returns>是否成功</returns>
public virtual Boolean Delete()
{
if(this._DBA.ExcuteSQL(this.DeleteSql()) == 1)
{return true;}
else
{return false;}
}

/// <summary>
/// 返回删除SQL语句
/// </summary>
/// <returns></returns>
public virtual String DeleteSql()
{
string strSql = "DELETE FROM " + this._TableName + " WHERE "+this._KeyField+" = '" + this.Guid + "'";
return strSql;
}

/// <summary>
/// 从数据库读取本实例的各项属性值
/// </summary>
/// <returns>是否成功</returns>
public virtual Boolean LoadMe()
{
String strSql = "SELECT * FROM " + this._TableName + " WHERE " + this._KeyField + "='" + this.Guid + "'";
System.Data.DataTable dt = this._DBA.GetDataTable(strSql);
if(dt.Rows.Count == 1)
{
return this.drToMember(dt.Rows[0]);
}
else
{return false;}
}

/// <summary>
/// 将dataRow中的值赋值给本实例的属性
/// </summary>
/// <param name="dr">一个dataRow</param>
/// <returns>成功与否</returns>
protected virtual Boolean drToMember(System.Data.DataRow dr)
{
this._Guid = dr[this._KeyField].ToString();
return true;
}

public TableBase(String Guid)
{
this._Guid = Guid;
}
}

[henryfan1]

对于数据访问类，你可能参考SqlHelper
数据表的操作，维护起来有点麻烦，建议用参数化的SQL语句，比较安全。
封装性不好，还是要重复写和维护大量的SQL语句。SQL语句写在方法里，别的地方用到相到相同的SQL语句，又要重写。
看一下O/R M方面的资料。

[91bct]

先占个座再仔细看...

[udonome]

好长啊，没看清楚，同意 henryfan1(每天好心情(*_*)) 的看法，用参数化的SQL语句，要不然肯定要出问题，比如变量中含有单引号肯定会出错。

[zdnet]

最好继承自接口~~不要继承自抽象类~~~（建议而已）

[zdnet]

using System;
using System_dede.Entity;
using System.Data;

namespace Dal_dede.common
{
/// <summary>
/// IDbCommon 的摘要说明。
/// </summary>
public interface IDbCommon
{
}
}

public class DbCommon :IDbCommon

[lkal4587]

能再详细点吗

最好做个实例程序

[running_su]

学习

[屡败屡战数马援]

一个建议，你的这个类最好继承IDisposable接口，以便释放数据库联接的资源。
虽然垃圾收集器可以自动回收不用的类实例，但是IDisposable接口实现的Dispose()方法可以让你显式关闭数据库联接资源。
你使用的SqlConnection.Close()方法是关闭数据库联接，而不是释放资源。
释放资源最好使用SqlConnection.Dispose()。

[Jacksonz]

同意,你的SQL最好能够参数话，sql很容易出错的
我和笑望人生有同感，不要太依赖于垃圾收集器，最好还是用dispose(true)来释放资源
总的来说，写得蛮不错的，
向你学习.....

[Kylix_NOW]

革命尚未成功。同志仍需努力。

[DTWUJP]

mark，学习

[sleeping100]

mark，学习