科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道为ASP.NET封装的SQL数据库访问类

为ASP.NET封装的SQL数据库访问类

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

using System;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Collections;

作者:中国IT实验室 来源:中国IT实验室 2007年9月5日

关键字: SQL数据库 ASP.NET

  • 评论
  • 分享微博
  • 分享邮件
   using System;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Collections;

    namespace MyCorporation.DepartMent.DataBase { /// <summary> /// 通用数据库类/// </summary> public class DataBase {

    private string ConnStr = null;

    public DataBase()

    { ConnStr = ConfigurationSettings.AppSettings["ConnStr"];} public DataBase(string Str)

    { try { this.ConnStr = Str;

    } catch(Exception ex)

    { throw ex;}

    /// <summary> /// 返回connection对象/// </summary> /// <returns></returns> public SqlConnection ReturnConn()

    { SqlConnection Conn = new SqlConnection(ConnStr);Conn.Open();return Conn;} public void Dispose(SqlConnection Conn)

    { if(Conn!=null)

    { Conn.Close();Conn.Dispose();} GC.Collect();} /// <summary> /// 运行SQL语句/// </summary> /// <param name="SQL"></param> public void RunProc(string SQL)

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();SqlCommand Cmd ;Cmd = CreateCmd(SQL, Conn);try { Cmd.ExecuteNonQuery();} catch { throw new Exception(SQL);} Dispose(Conn);return;}

    /// <summary> /// 运行SQL语句返回DataReader /// </summary> /// <param name="SQL"></param> /// <returns>SqlDataReader对象。</returns> public SqlDataReader RunProcGetReader(string SQL)

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();SqlCommand Cmd ;Cmd = CreateCmd(SQL, Conn);SqlDataReader Dr;try { Dr = Cmd.ExecuteReader(CommandBehavior.Default);} catch { throw new Exception(SQL);} //Dispose(Conn);return Dr;}

    /// <summary> /// 生成Command对象/// </summary> /// <param name="SQL"></param> /// <param name="Conn"></param> /// <returns></returns> public SqlCommand CreateCmd(string SQL, SqlConnection Conn)

    { SqlCommand Cmd ;Cmd = new SqlCommand(SQL, Conn);return Cmd;}

    /// <summary> /// 生成Command对象/// </summary> /// <param name="SQL"></param> /// <returns></returns> public SqlCommand CreateCmd(string SQL)

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();SqlCommand Cmd ;Cmd = new SqlCommand(SQL, Conn);return Cmd;}

    /// <summary> /// 返回adapter对象/// </summary> /// <param name="SQL"></param> /// <param name="Conn"></param> /// <returns></returns> public SqlDataAdapter CreateDa(string SQL)

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();SqlDataAdapter Da;Da = new SqlDataAdapter(SQL, Conn);return Da;}

    /// <summary> /// 运行SQL语句,返回DataSet对象/// </summary> /// <param name="procName">SQL语句</param> /// <param name="prams">DataSet对象</param> public DataSet RunProc(string SQL ,DataSet Ds)

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();SqlDataAdapter Da;//Da = CreateDa(SQL, Conn);Da = new SqlDataAdapter(SQL,Conn);try { Da.Fill(Ds);} catch(Exception Err)

    { throw Err;} Dispose(Conn);return Ds;}

    /// <summary> /// 运行SQL语句,返回DataSet对象/// </summary> /// <param name="procName">SQL语句</param> /// <param name="prams">DataSet对象</param> /// <param name="dataReader">表名</param> public DataSet RunProc(string SQL ,DataSet Ds,string tablename)

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();SqlDataAdapter Da;Da = CreateDa(SQL);try { Da.Fill(Ds,tablename);} catch(Exception Ex)

    { throw Ex;} Dispose(Conn);return Ds;}

    /// <summary> /// 运行SQL语句,返回DataSet对象/// </summary> /// <param name="procName">SQL语句</param> /// <param name="prams">DataSet对象</param> /// <param name="dataReader">表名</param> public DataSet RunProc(string SQL , DataSet Ds ,int StartIndex ,int PageSize, string tablename )

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();SqlDataAdapter Da ;Da = CreateDa(SQL);try { Da.Fill(Ds, StartIndex, PageSize, tablename);} catch(Exception Ex)

    { throw Ex;} Dispose(Conn);return Ds;}

    /// <summary> /// 检验是否存在数据/// </summary> /// <returns></returns> public bool ExistDate(string SQL)

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();SqlDataReader Dr ;Dr = CreateCmd(SQL,Conn)。ExecuteReader();if (Dr.Read())

    { Dispose(Conn);return true;} else { Dispose(Conn);return false;}

    /// <summary> /// 返回SQL语句执行结果的第一行第一列/// </summary> /// <returns>字符串</returns> public string ReturnValue(string SQL)

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();string result;SqlDataReader Dr ;try { Dr = CreateCmd(SQL,Conn)。ExecuteReader();if (Dr.Read())

    { result = Dr[0].ToString();Dr.Close();} else { result = "";Dr.Close();} catch { throw new Exception(SQL);} Dispose(Conn);return result;}

    /// <summary> /// 返回SQL语句第一列,第ColumnI列,/// </summary> /// <returns>字符串</returns> public string ReturnValue(string SQL, int ColumnI)

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();string result;SqlDataReader Dr ;try { Dr = CreateCmd(SQL,Conn)。ExecuteReader();} catch { throw new Exception(SQL);} if (Dr.Read())

    { result = Dr[ColumnI].ToString();} else { result = "";} Dr.Close();Dispose(Conn);return result;}

    /// <summary> /// 生成一个存储过程使用的sqlcommand. /// </summary> /// <param name="procName">存储过程名。</param> /// <param name="prams">存储过程入参数组。</param> /// <returns>sqlcommand对象。</returns> public SqlCommand CreateCmd(string procName, SqlParameter[] prams)

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();SqlCommand Cmd = new SqlCommand(procName, Conn);Cmd.CommandType = CommandType.StoredProcedure;if (prams != null)

    { foreach (SqlParameter parameter in prams)

    { if(parameter != null)

    { Cmd.Parameters.Add(parameter);} return Cmd;}

    /// <summary> /// 为存储过程生成一个SqlCommand对象/// </summary> /// <param name="procName">存储过程名</param> /// <param name="prams">存储过程参数</param> /// <returns>SqlCommand对象</returns> private SqlCommand CreateCmd(string procName, SqlParameter[] prams,SqlDataReader Dr)

    { SqlConnection Conn;Conn = new SqlConnection(ConnStr);Conn.Open();SqlCommand Cmd = new SqlCommand(procName, Conn);Cmd.CommandType = CommandType.StoredProcedure;if (prams != null)

    { foreach (SqlParameter parameter in prams)

    Cmd.Parameters.Add(parameter);} Cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,ParameterDirection.ReturnValue, false, 0, 0,string.Empty, DataRowVersion.Default, null));

    return Cmd;}

    /// <summary> /// 运行存储过程,返回。

    /// </summary> /// <param name="procName">存储过程名</param> /// <param name="prams">存储过程参数</param> /// <param name="dataReader">SqlDataReader对象</param> public void RunProc(string procName, SqlParameter[] prams, SqlDataReader Dr)

    {

    SqlCommand Cmd = CreateCmd(procName, prams, Dr);Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);return;}

    /// <summary> /// 运行存储过程,返回。

    /// </summary> /// <param name="procName">存储过程名</param> /// <param name="prams">存储过程参数</param> public string RunProc(string procName, SqlParameter[] prams)

    { SqlDataReader Dr;SqlCommand Cmd = CreateCmd(procName, prams);Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);if(Dr.Read())

    { return Dr.GetValue(0)。ToString();} else { return "";}

    /// <summary> /// 运行存储过程,返回dataset. /// </summary> /// <param name="procName">存储过程名。</param> /// <param name="prams">存储过程入参数组。</param> /// <returns>dataset对象。</returns> public DataSet RunProc(string procName,SqlParameter[] prams,DataSet Ds)

    { SqlCommand Cmd = CreateCmd(procName,prams);SqlDataAdapter Da = new SqlDataAdapter(Cmd);try { Da.Fill(Ds);} catch(Exception Ex)

    { throw Ex;} return Ds;}

    }

查看本文来源

    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章