诸位大侠好,小生这厢有利了,最近用.Net开发一个系统,做了一些自己比较满意的封装,特请大家给些意见,小生文笔不是很好,又没有很多的时间,只好以源代码的形式发布,请大家多多见谅.另有相关代码和注释,请大家自己下载(goto)
/************************************ * FileName : WDDb.cs * Target : 处理存取数据库的问题 * Author : Baihao * CreateDate : 03/02/19 * LastModify : 03/04/09 * History : * * ************************************* */
using System; using System.Data; using System.Data.SqlClient; using Wonder.SysConsole;
namespace Wonder.Web.Data { /// <summary> /// WDDb 的摘要说明。 /// </summary> public class DbObject {
private const string CONNNECT_STRING = "data source=172.16.36.222;initial catalog=RemoteEdu;" + "persist security info=False;user id=sa;password=1234567890;" + "packet size=4096"; private string m_sErrorMessage = null;
protected SqlConnection Connection; //保护连接 private string connectionString; //私有连接字符串 private const string DEF_TABLE = "table1";
private SqlCommand command = new SqlCommand();
/// <summary> /// A parameterized constructor, it allows us to take a connection /// string as a constructor argument, automatically instantiating /// a new connection. /// </summary> /// <param name="newConnectionString">Connection String to the associated database</param> public DbObject( string newConnectionString ) { connectionString = newConnectionString; Connection = new SqlConnection( connectionString ); command.Connection = Connection;
}
public DbObject() // :base(/*(SQLConnString.IniReadValue("catalog")== "")?CONNNECT_STRING:*/SQLConnString.GetConnStr()) { connectionString = CONNNECT_STRING; Connection = new SqlConnection( connectionString ); command.Connection = Connection; // // TODO: 在此处添加构造函数逻辑 // }
/// <summary> /// 传递字符型参数到SqlCommand /// </summary> /// <param name="str"></param> /// <returns></returns> public object SendStr(string str) { if(str == null) return DBNull.Value; else return str; }
/// <summary> /// 传递日期型参数到SqlCommand /// </summary> /// <param name="dt"></param> /// <returns></returns> public object SendDate(DateTime dt) { if(dt.Ticks == 0) return DBNull.Value; else return dt; }
/// <summary> /// 传送字节型数据到SqlCommand /// </summary> /// <param name="c"></param> /// <returns></returns> public object SendChar(char c) { if(c == '\0') return DBNull.Value; else return c; } /// <summary> /// 传送Guid型数据到SqlCommand /// </summary> /// <param name="c"></param> /// <returns></returns> public object SendGuid(Guid c) { if(c == Guid.Empty ) return DBNull.Value; else return c; } /// <summary> /// 取得错误描述 /// </summary> /// <returns></returns> public string GetLastError() { return m_sErrorMessage; }
/// <summary> /// 清除 Err 对象的属性。 /// </summary> public void ErrClear() { m_sErrorMessage = ""; }
/// <summary> /// Protected property that exposes the connection string /// to inheriting classes. Read-Only. /// </summary> protected string ConnectionString { get { return connectionString; } }
/// <summary> /// Protected property that exposes the connection string /// to inheriting classes. Read-Only. /// </summary> protected SqlCommand Command { get{return command;} }
/// <summary> /// 打开连接 /// </summary> public void OpenConnect() { if(Connection.State != ConnectionState.Closed ) Connection.Close(); Connection.Open(); }
/// <summary> /// 关闭连接,主要使用在查询结果后 /// </summary> public void CloseConnect() { if(Connection.State != ConnectionState.Closed ) Connection.Close(); }
/// <summary> /// 执行指定SQL语句 /// (ex:Exec(" Delete MemberInfo Where UserID ='baihao'; ")) /// **需要打开/关闭连接 ** /// </summary> public void ExecNoOpen(string sSql) { command.CommandText = sSql; command.CommandType= CommandType.Text; command.ExecuteNonQuery(); }
/// <summary> ///执行查询语句,返回DataReader ///**由于DataReader还在查询数据库,没有断开连接,使用中请注意** ///(ex:(Query(" SELECT * FROM MemberInfo "))) ///不需要打开连接,但需要关闭 /// </summary> /// <param name="storedProcName">查询语句</param> /// <returns>A newly instantiated SqlDataReader object</returns> public SqlDataReader Query(string sSql) { SqlDataReader returnReader; try { OpenConnect();
command.CommandType= CommandType.Text; command.CommandText= sSql;
returnReader = command.ExecuteReader(); //Connection.Close(); } catch(Exception e) { m_sErrorMessage = e.Message; return null; }
return returnReader; }
/// <summary> /// 执行SQL语句并返回查询结果,返回有默认表名为"table1"的DataSet /// (ex:FillDataSet("Select * from MemberInfo")) /// 不需要打开/关闭连接 /// </summary> /// <param name="sSql">要执行的Sql语句</param> /// <returns></returns> public DataSet FillDataSet(string sSql) { return FillDataSet(sSql,DEF_TABLE); } /// <summary> /// 执行SQL语句并返回查询结果 /// (ex:FillDataSet("Select * from MemberInfo","MemberInfo")) /// 不需要打开/关闭连接 /// </summary> /// <param name="sSql">要执行的Sql语句</param> /// <param name="sTable">返回的DataSet中的表名</param> /// <returns></returns> public DataSet FillDataSet(string sSql,string sTable) { DataSet dataSet; try { dataSet = new DataSet(); OpenConnect();
SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = command; command.CommandText = sSql; command.CommandType = CommandType.Text;
int nRows = sqlDA.Fill( dataSet, sTable );
if(nRows == 0 ) dataSet = null; } catch(SqlException e) { m_sErrorMessage = e.Message; return null;
} return dataSet; }
/// <summary> /// 执行SQL语句并返回查询结果 /// (ex:FillDataSet(ds,"Select * from MemberInfo","MemberInfo")) //在已经存在的打算中添加表 /// 不需要打开/关闭连接 /// </summary> /// <param name="ds" >已经存在的DataSet,添加表</param> /// <param name="sSql">要执行的Sql语句</param> /// <param name="sTable">返回的DataSet中的表名</param> /// <returns></returns> public bool FillDataSet(ref DataSet ds,string sSql,string sTable) { bool bRe = true; try { OpenConnect();
SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = command; command.CommandText = sSql; command.CommandType = CommandType.Text;
int nRows = sqlDA.Fill( ds, sTable );
if(nRows == 0 ) bRe = false; } catch(SqlException e) { m_sErrorMessage = e.Message; return false; }
return bRe; } /// <summary> /// 执行查询,并返回结果的第一行的第一列,忽略其他行和列。 /// (ex:GetFirstColumn(" SELECT UserName FROM MemberInfo Where UserID ='baihao'")) /// 不需要打开/关闭连接 /// </summary> /// <param name="sSql"></param> /// <returns></returns> public object GetFirstColumn(string sSql) { try { object oRe; OpenConnect(); command.CommandType= CommandType.Text; command.CommandText= sSql;
oRe = command.ExecuteScalar(); CloseConnect();
return oRe; } catch(Exception e) { m_sErrorMessage = e.Message; return null; } }
/// <summary> /// 执行查询,并返回整数型的第一行的第一列的结果,忽略其他行和列。 /// (ex:ExecuteScalar(" SELECT COUNT(*) FROM MemberInfo ")) /// 不需要打开/关闭连接 /// </summary> /// <param name="sSql"></param> /// <returns>返回-1,表示不成功,否则成功</returns> public int ExecuteScalar(string sSql) { try { int iRe; OpenConnect(); command.CommandType= CommandType.Text; command.CommandText= sSql;
iRe = (int)command.ExecuteScalar(); CloseConnect();
return iRe; } catch(Exception e) { m_sErrorMessage = e.Message; return -1; } }
/// <summary> /// 执行指定SQL语句 /// (ex:Exec(" Delete MemberInfo Where UserID ='baihao'; ")) /// 不需要打开/关闭连接 /// </summary> /// <param name="sSql"></param> public void Exec(string sSql) { try { OpenConnect(); command.CommandType= CommandType.Text; command.CommandText= sSql;
command.ExecuteNonQuery(); CloseConnect(); } catch(Exception e) { m_sErrorMessage = e.Message; } } }
/// <summary> /// 封装SqlDataReader,主要处理了DBNull /// </summary> public class WDRead {
public const byte NULL_INT = 0; public const string NULL_STR = ""; public static DateTime NULL_DATE = new DateTime(1,1,1); public const char NULL_CHAR = '\0'; public static Guid NULL_GUID = Guid.Empty; /// <summary> /// /// </summary> public SqlDataReader m_read = null;
/// <summary> /// 构造函数 /// </summary> /// <param name="read"></param> public WDRead(SqlDataReader read) { m_read = read; } /// <summary> /// 读下一条记录 /// </summary> /// <returns></returns> public bool Read() { return m_read.Read(); }
/// <summary> /// 取得字符串类型 /// </summary> /// <param name="item">字段名</param> /// <returns>字段值或NULL_STR</returns> public string GString(string item) { if(m_read.IsDBNull(m_read.GetOrdinal(item))) return NULL_STR; else return m_read.GetString (m_read.GetOrdinal(item)); }
/// <summary> /// 取得整数类型 /// </summary> /// <param name="item">字段名</param> /// <returns>字段值或NULL</returns> public int GInt(string item) { if(m_read.IsDBNull(m_read.GetOrdinal(item))) return NULL_INT ; else return m_read.GetInt32 (m_read.GetOrdinal(item)); }
public Int16 GWord(string item) { if(m_read.IsDBNull(m_read.GetOrdinal(item))) return NULL_INT ; else return m_read.GetInt16 (m_read.GetOrdinal(item)); }
/// <summary> /// 取得日期类型 /// </summary> /// <param name="item">字段名</param> /// <returns>字段值或NULL_DATE</returns> public DateTime GDate(string item) { if(m_read.IsDBNull(m_read.GetOrdinal(item))) return NULL_DATE ; else return m_read.GetDateTime (m_read.GetOrdinal(item)); }
/// <summary> /// 取得byte类型 /// </summary> /// <param name="item">字段名</param> /// <returns>字段值或NULL_INT</returns> public byte GByte(string item) { if(m_read.IsDBNull(m_read.GetOrdinal(item))) return NULL_INT ; else return m_read.GetByte (m_read.GetOrdinal(item)); }
/// <summary> /// 取得boolean类型 /// </summary> /// <param name="item">字段名</param> /// <returns>字段值或NULL_INT</returns> public bool GBool(string item) { if(m_read.IsDBNull(m_read.GetOrdinal(item))) return false ; else return m_read.GetBoolean( m_read.GetOrdinal(item));//( 1 ==m_read.GetByte (m_read.GetOrdinal(item))); }
/// <summary> /// 取得char类型 /// </summary> /// <param name="item">字段名</param> /// <returns>字段值或NULL_INT</returns> public char GChar(string item) { if(m_read.IsDBNull(m_read.GetOrdinal(item))) return NULL_CHAR ; else return Convert.ToChar(m_read[item]); }
/// <summary> /// 取得Decimal类型 /// </summary> /// <param name="item">字段名</param> /// <returns>字段值或NULL_INT</returns> public Decimal GDec(string item) { if(m_read.IsDBNull(m_read.GetOrdinal(item))) return NULL_INT ; else return m_read.GetDecimal (m_read.GetOrdinal(item)); }
/// <summary> /// 取得Guid类型 /// </summary> /// <param name="item">字段名</param> /// <returns>字段值或NULL_GUID</returns> public Guid GGuid(string item) { if(m_read.IsDBNull(m_read.GetOrdinal(item))) return NULL_GUID ; else { return m_read.GetGuid(m_read.GetOrdinal(item)); } }
/// <summary> /// 取得指定read 的 字符串 /// </summary> /// <param name="read"></param> /// <param name="item"></param> /// <returns></returns> public static string GStr(SqlDataReader read,int item) { if(read.Read()) { if(read.IsDBNull(item)) return null; else return read.GetString(item);
} else return null; }
/// <summary> /// 取得指定read 的 字符串 /// </summary> /// <param name="read"></param> /// <param name="item"></param> /// <returns></returns> public static string GStr(SqlDataReader read,string item) { if(read.Read()) { if(read.IsDBNull(read.GetOrdinal(item))) return null; else return read.GetString(read.GetOrdinal(item));
} else return null; } } }

|