你的位置:首页 > ASP.net教程

[ASP.net教程]ADO.NET之常用功能的封装


  一、连接字符串(使用配置文件)

  添加对 System.Configuration 的引用,并且在封装类中引入该namespace

    /// <summary>    /// 功能:读取配置文件中的连接字符串    /// 返回值类型:string    /// </summary>    /// <param name="conName">参数:配置文件中的连接字符串名称</param>    /// <returns>返回值:string 连接字符串</returns>    public static string GetConnectString(string conName)    {      try      {        return ConfigurationManager.ConnectionStrings[conName].ConnectionString;      }      catch (Exception e)      {        throw e;      }    }

  二、SqlConnection对象

    /// <summary>    /// 功能:根据给定的连接字符串创建一个SqlConnection对象    /// 返回类型:SqlConnection    /// </summary>    /// <param name="conStr">参数:连接字符串</param>    /// <returns>返回值:SqlConnection对象</returns>    public static SqlConnection GetConnect(string conStr)    {      return new SqlConnection(conStr);    }

    /// <summary>    /// 功能:打开数据库连接    /// </summary>    /// <param name="Con">参数:SqlConnection对象</param>    public static void OpenDBConnet(SqlConnection Con)    {      try      {        if (Con.State == ConnectionState.Open)        {          return;        }        else        {          Con.Open();        }      }      catch (SqlException e)      {        throw e;      }    }

    /// <summary>    /// 功能:关闭数据库连接    /// </summary>    /// <param name="Con">参数:SqlConnection对象</param>    public static void CloseDBConnect(SqlConnection Con)    {      try      {        if (Con.State == ConnectionState.Closed)        {          return;        }        else        {          Con.Close();        }      }      catch (SqlException e)      {        throw e;      }    }

  三、同步操作数据库

  SqlDataReader对象 

    /// <summary>    /// 功能:执行存储过程并返回一个SqlDataReader对象    /// </summary>    /// <param name="sql">参数:存储过程名</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter参数数组</param>    /// <returns>返回值:SqlDataReader对象</returns>    public static SqlDataReader GetSdrBySp(string sql, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      try      {        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.StoredProcedure;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }      }      catch (SqlException s)      {        throw s;      }      return Cmd.ExecuteReader(CommandBehavior.CloseConnection);    }

    /// <summary>    /// 功能:执行T-SQL语句语句并返回一个SqlDataReader对象    /// </summary>    /// <param name="sql">参数:T-SQL语句</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter参数数组</param>    /// <returns>返回值:SqlDataReader对象</returns>    public static SqlDataReader GetSdrByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      try      {        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.Text;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }      }      catch (SqlException s)      {        throw s;      }      return Cmd.ExecuteReader(CommandBehavior.CloseConnection);    }

  ExecuteNonQuery

    /// <summary>    /// 功能:执行存储过程返回受影响的行数    /// 返回类型:int    /// </summary>    /// <param name="sql">参数:存储过程名</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter参数数组</param>    /// <returns>返回值:int 受影响的行数</returns>    public static int GetEnqBySp(string sql, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      try      {        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.StoredProcedure;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }      }      catch (SqlException s)      {        throw s;      }      return Cmd.ExecuteNonQuery();    }

    /// <summary>    /// 功能:执行T-SQL语句返回受影响的行数    /// </summary>    /// <param name="sql">参数:T-SQL语句</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter参数数组</param>    /// <returns>返回值:int 受影响的行数</returns>    public static int GetEnqByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      try      {        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.Text;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }      }      catch (SqlException s)      {        throw s;      }      return Cmd.ExecuteNonQuery();    }

  ExecuteScalar

    /// <summary>    /// 功能:通过T-SQl语句执行SqlCommand的ExecuteScalar()方法返回object类型对象    /// </summary>    /// <param name="sql">参数:T-SQL语句</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter参数数组</param>    /// <returns>返回值:object对象</returns>    public static object GetEScalarByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      try      {        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.Text;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }      }      catch (SqlException s)      {        throw s;      }      return Cmd.ExecuteScalar();    }

    /// <summary>    /// 功能:通过执行T-SQL语句返回一个DataSet数据集对象    /// </summary>    /// <param name="sql">参数:T-SQL语句</param>    /// <param name="tableName">参数:DataSet表名</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:可变SqlParameter参数数组</param>    /// <returns>返回值:DataSet数据集对象</returns>    public static DataSet GetDsByTSql(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      DataSet Ds = null;      SqlDataAdapter Sda = null;      try      {        Ds = new DataSet(tableName);        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.Text;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }        Sda = new SqlDataAdapter(Cmd);        Sda.Fill(Ds, tableName);      }      catch (SqlException s)      {        throw s;      }      return Ds;    }

  DataSet

    /// <summary>    /// 功能:通过执行存储过程返回一个DataSet数据集对象    /// </summary>    /// <param name="sql">参数:存储过程名</param>    /// <param name="tableName">参数:DataSet表名</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:可变SqlParameter参数数组</param>    /// <returns>返回值:DataSet数据集对象</returns>    public static DataSet GetDsBySp(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      DataSet Ds = null;      SqlDataAdapter Sda = null;      try      {        Ds = new DataSet(tableName);        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.StoredProcedure;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }        Sda = new SqlDataAdapter(Cmd);        Sda.Fill(Ds, tableName);      }      catch (SqlException s)      {        throw s;      }      return Ds;    }

    /// <summary>    /// 功能:执行存储过程返回一个DataTable数据表对象    /// </summary>    /// <param name="sql">参数:存储过程名</param>    /// <param name="tableName">参数:DataTable表名</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter可变参数数组</param>    /// <returns>返回值:DataTable</returns>    public static DataTable GetDtBySp(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      DataTable Dt = null;      SqlDataAdapter Sda = null;      try      {        Cmd = new SqlCommand(sql, Con);        Dt = new DataTable(tableName);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.StoredProcedure;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }        Sda = new SqlDataAdapter(Cmd);        Sda.Fill(Dt);      }      catch (SqlException s)      {        throw s;      }      return Dt;    }

  DataTable

    /// <summary>    /// 功能:执行T-SQL语句返回一个DataTable数据表对象    /// </summary>    /// <param name="sql">参数:T-SQL语句</param>    /// <param name="tableName">参数:DataTable表名</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter可变参数数组</param>    /// <returns>返回值:DataTable</returns>    public static DataTable GetDtByTSql(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      DataTable Dt = null;      SqlDataAdapter Sda = null;      try      {        Cmd = new SqlCommand(sql, Con);        Dt = new DataTable(tableName);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.Text;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }        Sda = new SqlDataAdapter(Cmd);        Sda.Fill(Dt);      }      catch (SqlException s)      {        throw s;      }      return Dt;    }

  其它同步ADO.NET操作

    /// <summary>    /// 功能:执行T-SQL语句判断表中是否包含指定的内容    /// </summary>    /// <param name="sql">参数:T-SQL语句</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter可变参数数组</param>    /// <returns>返回值:bool值</returns>    public static bool IsContainFiledByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)    {      try      {        using (SqlCommand Cmd = new SqlCommand(sql, Con))        {          DBHelper.OpenDBConnet(Con);          Cmd.CommandType = CommandType.Text;          foreach (SqlParameter parameter in parameters)          {            Cmd.Parameters.Add(parameter);          }          using (SqlDataReader Sdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection))          {            if (Sdr.HasRows)            {              return true;            }            else            {              return false;            }          }        }      }      catch (SqlException e)      {        throw e;      }    }

  异步操作数据库

    /// <summary>    /// 功能:通过存储过程异步操作数据库,返回SqlDataReader对象    /// </summary>    /// <param name="sql">参数:存储过程名</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter可变参数数组</param>    /// <returns>返回值:SqlDataReader对象</returns>    public static SqlDataReader AsyncGetSdrBySp(string sql, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      IAsyncResult Iasy = null;      try      {        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.StoredProcedure;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }        Iasy = Cmd.BeginExecuteReader();      }      catch (SqlException s)      {        throw s;      }      return Cmd.EndExecuteReader(Iasy);    }    /// <summary>    /// 功能:通过T-SQL语句异步操作数据库,返回SqlDataReader对象    /// </summary>    /// <param name="sql">参数:T-SQL语句</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter可变参数数组</param>    /// <returns>返回值:SqlDataReader对象</returns>    public static SqlDataReader AsyncGetSdrByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      IAsyncResult Iasy = null;      try      {        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.Text;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }        Iasy = Cmd.BeginExecuteReader();      }      catch (SqlException s)      {        throw s;      }      return Cmd.EndExecuteReader(Iasy);    }    /// <summary>    /// 功能:通过存储过程异步操作数据库返回受影响的行数    /// </summary>    /// <param name="sql">参数:存储过程</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter可变参数数组</param>    /// <returns>返回值:int 受影响行数</returns>    public static int AsyncGetEnqBySp(string sql, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      IAsyncResult Iasy = null;      try      {        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.StoredProcedure;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }        Iasy = Cmd.BeginExecuteNonQuery();      }      catch (SqlException s)      {        throw s;      }      return Cmd.EndExecuteNonQuery(Iasy);    }    /// <summary>    /// 功能:通过T-SQL语句异步操作数据库返回受影响的行数    /// </summary>    /// <param name="sql">参数:T-SQL语句</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter可变参数数组</param>    /// <returns>返回值:int 受影响行数</returns>    public static int AsyncGetEnqByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      IAsyncResult Iasy = null;      try      {        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.Text;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }        Iasy = Cmd.BeginExecuteNonQuery();      }      catch (SqlException s)      {        throw s;      }      return Cmd.EndExecuteNonQuery(Iasy);    }    /// <summary>    /// 功能:通过存储过程异步操作数据库返回/// </summary>    /// <param name="sql">参数:存储过程名</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter可变参数数组</param>    /// <returns>返回值:</returns>    public static string sql, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      IAsyncResult Iasy = null;      try      {        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.StoredProcedure;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }        Iasy = Cmd.BeginExecutecatch (SqlException s)      {        throw s;      }      return Cmd.EndExecute/// <summary>    /// 功能:通过T-SQL语句异步操作数据库返回/// </summary>    /// <param name="sql">参数:T-SQL语句</param>    /// <param name="Con">参数:SqlConnection对象</param>    /// <param name="parameters">参数:SqlParameter可变参数数组</param>    /// <returns>返回值:</returns>    public static string sql, SqlConnection Con, params SqlParameter[] parameters)    {      SqlCommand Cmd = null;      IAsyncResult Iasy = null;      try      {        Cmd = new SqlCommand(sql, Con);        DBHelper.OpenDBConnet(Con);        Cmd.CommandType = CommandType.Text;        foreach (SqlParameter parameter in parameters)        {          Cmd.Parameters.Add(parameter);        }        Iasy = Cmd.BeginExecutecatch (SqlException s)      {        throw s;      }      return Cmd.EndExecute

  其它重要方法封装

    /// <summary>    /// 功能:检查字符串类型数据是不是空,为空则向数据库插入Null    /// </summary>    /// <param name="notNullStr">字符串可变参数数组</param>    /// <returns>最终要插入数据库的数据</returns>    public static List<object> CheckDBNullValue(params string[] notNullStr)    {      List<object> TheDBNllResult = new List<object>(notNullStr.Length);      foreach (string s in notNullStr)      {        if ((s == string.Empty) || (s == ""))        {          TheDBNllResult.Add(DBNull.Value);        }        else        {          TheDBNllResult.Add(s.Trim());        }      }      return TheDBNllResult;    }    /// <summary>    /// 功能:判断数据阅读器读到的指定列是否是Null,如果是Null就返回空字符串,否则就读取字段的值    /// </summary>    /// <param name="Sdr">SqlDataReader</param>    /// <param name="filedName">数据库字段名</param>    /// <returns>字符串表示的字段值</returns>    public static string GetDBValue(SqlDataReader Sdr, string filedName)    {      string result = null;      if (Sdr.IsDBNull(Sdr.GetOrdinal(filedName)))      {        result = string.Empty;      }      else      {        result = Sdr.GetString(Sdr.GetOrdinal(filedName));      }      return result;    }