CSDN博客

img gujianxin

Web.Page基类,封装了数据库操作和Session处理

发表于2003/4/11 9:08:00  584人阅读

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

0 0

相关博文

我的热门文章

img
取 消
img