诸位大侠好,小生这厢有利了,最近用.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;
}
}
}
