三种asp.net数据库连接类

作者:袖梨 2022-06-25

连接sqlserver数据库

 代码如下 复制代码

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;

namespace zz
{
 ///


 /// ClassConn 的摘要说明。
 ///

 public class conn       //数据库连接类
 {
  public conn()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }
  public SqlConnection connstr;   //连接字符串
  public string getconnstr()    //获取连接字符串
  {
   string constr;
   constr=System.Configuration.ConfigurationSettings.AppSettings["connstring"];
   return constr;
  }
  public void open()      //打开数据库
  {
   string constr;
   constr=getconnstr();
   connstr=new SqlConnection(constr);
   connstr.Open();
  }
  public void close()      //关闭数据库
  {
   connstr.Dispose();
   connstr.Close();
  }
  public void execsql(string sql)   //执行sql语句
  {
   open();
   SqlCommand cmd=new SqlCommand(sql,connstr);
   cmd.ExecuteNonQuery();
   close();
  }
  public DataSet dataset(string sql)  //返回DataSet对象
  {
   open();
   SqlDataAdapter rs=new SqlDataAdapter(sql,connstr);
   DataSet ds=new DataSet();
   rs.Fill(ds);
   return ds;
  }
  public DataView dataview(string sql) //返回DataView对象
  {
   DataSet ds=new DataSet();
   ds=dataset(sql);
   DataView dv=new DataView(ds.Tables[0]);
   return dv;
  }
  public SqlDataReader datareader(string sql) //返回DataReader对象
  {
   open();
   SqlCommand cmd=new SqlCommand(sql,connstr);
   SqlDataReader dr=cmd.ExecuteReader();
   return dr;
  }
 }
}


2.连接oledb数据库

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace xyl
{
 ///


 /// ClassConn 的摘要说明。
 ///

 public class ClassConn
 {
  public ClassConn()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }
  public OleDbConnection connstr;
  public string getconnstr()
  {
   string constr;
   constr="Provider=Microsoft.Jet.OleDb.4.0;Data Source="+HttpRuntime.AppDomainAppPath+ System.Configuration.ConfigurationSettings.AppSettings["connstring"];
   return constr;
  }
  public void OpenDB()
  {
   string constr;
   constr=getconnstr();
   connstr=new OleDbConnection(constr);
   connstr.Open();
  }
  public void CloseDB()
  {
   connstr.Dispose();
   connstr.Close();
  }
  public void execsql(string sql)
  {
   OpenDB();
   OleDbCommand cmd=new OleDbCommand(sql,connstr);
   cmd.ExecuteNonQuery();
   CloseDB();
  }
  public DataSet DataSets(string sql)
  {
   OpenDB();
   OleDbDataAdapter rs=new OleDbDataAdapter(sql,connstr);
   DataSet ds=new DataSet();
   rs.Fill(ds);
   return ds;
  }
  public DataView DataViews(string sql)
  {
   DataSet ds=new DataSet();
   ds=DataSets(sql);
   DataView dv=new DataView(ds.Tables[0]);
   return dv;
  }
  public OleDbDataReader MyDataReader(string sql)
  {
   OpenDB();
   OleDbCommand myCom=new OleDbCommand(sql,connstr);
   OleDbDataReader myreader=myCom.ExecuteReader();
   return myreader;
  }
 }
}


实例二

 代码如下 复制代码

public class SqlOperation
    {
        #region 属性
        ///


        /// 保存在Web.config中的连接字符串
        ///

        protected static string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["hao"].ConnectionString;
        ///
        /// SqlConnection对象
        ///

        protected static SqlConnection conn = new SqlConnection();
        ///
        /// SqlCommand对象
        ///

        protected static SqlCommand comm = new SqlCommand();
        #endregion

        #region 内部函数
        ///


        /// 打开数据库连接
        ///

        private static void ConnectionOpen()
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Close();
                conn.ConnectionString = connectionstring;
                comm.Connection = conn;
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        ///


        /// 关闭数据库连接
        ///

        private static void ConnectionClose()
        {
            conn.Close();
            conn.Dispose();
            comm.Dispose();
        }

        #endregion

        ///


        /// 执行SQL语句
        ///

        /// 要执行的SQL语句
        public static void ExecuteSQL(string SqlString)
        {
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.Text;
                comm.CommandText = SqlString;
                comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
        }

        ///


        /// 执行存储过程
        ///

        /// 存储过程名称
        /// 存储过程需要的参数集合
        public static void ExecuteProcedure(string ProcedureName, params SqlParameter[] coll)
        {
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = ProcedureName;
                comm.Parameters.Clear();
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
        }

        ///


        /// 执行Sql查询并返回第一行的第一条记录,返回object,使用时需要拆箱 -> unbox
        ///

        /// 传入的Sql语句
        /// 返回object类型的第一行第一条记录
        public static object ExecuteScalar(string SqlString)
        {
            object obj = new object();
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.Text;
                comm.CommandText = SqlString;
                obj = comm.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return obj;
        }

        ///


        /// 执行SQL语句,同时进行事务处理
        ///

        /// 要执行的SQL语句
        public static void ExecuteTransactionSQL(string SqlString)
        {
            SqlTransaction trans;
            trans = conn.BeginTransaction();
            comm.Transaction = trans;
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.Text;
                comm.CommandText = SqlString;
                comm.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
        }

        ///


        /// 执行指定SQL查询,返回DataSet
        ///

        /// 要执行的SQL语句
        /// DataSet
        public static DataSet GetDataSetBySQL(string SqlString)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.Text;
                comm.CommandText = SqlString;
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return ds;
        }

        ///


        /// 通过存储过程返回DataSet
        ///

        /// 存储过程名称
        /// SqlParameter集合
        /// DataSet
        public static DataSet GetDataSetByProcedure(string ProcedureName, params SqlParameter[] coll)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.Clear();
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                comm.CommandText = ProcedureName;
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return ds;
        }


        ///


        /// 通过存储过程返回DataSet
        ///

        /// 存储过程名称
        /// DataSet
        public static DataSet GetDataSetByProcedure(string ProcedureName)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = ProcedureName;
                comm.Parameters.Clear();
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return ds;
        }

        ///


        /// 返回指定sql语句的DataTable
        ///

        /// 传入的Sql语句
        /// DataTable
        public static DataTable GetDataTableBySQL(string SqlString)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.Text;
                comm.CommandText = SqlString;
                da.SelectCommand = comm;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return dt;
        }

        ///


        /// 根据存储过程返回DataTable
        ///

        /// 存储过程名
        /// SqlParameter集合
        /// DataTable
        public static DataTable GetDataTableByProcedure(string ProcedureName, params SqlParameter[] coll)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            try
            {
                ConnectionOpen();
                comm.Parameters.Clear();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = ProcedureName;
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                da.SelectCommand = comm;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return dt;
        }

        ///


        /// 根据存储过程返回DataTable
        ///

        /// 存储过程名称
        /// DataTable
        public static DataTable GetDataTableByProcedure(string ProcedureName)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            try
            {
                ConnectionOpen();
                comm.Parameters.Clear();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = ProcedureName;
                da.SelectCommand = comm;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return dt;
        }
    }


实例三

 代码如下 复制代码

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;

namespace zz
{
///


/// ClassConn 的摘要说明。
///

public class conn        //数据库连接类
{
   public conn()
   {
    //
    // TODO: 在此处添加构造函数逻辑
    //
   }
   public SqlConnection connstr;    //连接字符串
   public string getconnstr()     //获取连接字符串
   {
    string constr;
    constr=System.Configuration.ConfigurationSettings.AppSettings["connstring"];
    return constr;
   }
   public void open()       //打开数据库
   {
    string constr;
    constr=getconnstr();
    connstr=new SqlConnection(constr);
    connstr.Open();
   }
   public void close()       //关闭数据库
   {
    connstr.Dispose();
    connstr.Close();
   }
   public void execsql(string sql)    //执行sql语句
   {
    open();
    SqlCommand cmd=new SqlCommand(sql,connstr);
    cmd.ExecuteNonQuery();
    close();
   }
   public DataSet dataset(string sql)   //返回DataSet对象
   {
    open();
    SqlDataAdapter rs=new SqlDataAdapter(sql,connstr);
    DataSet ds=new DataSet();
    rs.Fill(ds);
    return ds;
   }
   public DataView dataview(string sql) //返回DataView对象
   {
    DataSet ds=new DataSet();
    ds=dataset(sql);
    DataView dv=new DataView(ds.Tables[0]);
    return dv;
   }
   public SqlDataReader datareader(string sql) //返回DataReader对象
   {
    open();
    SqlCommand cmd=new SqlCommand(sql,connstr);
    SqlDataReader dr=cmd.ExecuteReader();
    return dr;
   }
}
}

相关文章

精彩推荐