三种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;
        }
    }

实例三

代码如下

复制代码

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;
   }
}
}

三种asp.net数据库连接类

相关文章

精彩推荐