一聚教程网:一个值得你收藏的教程网站

最新下载

热门教程

三种asp.net数据库连接类

时间:2022-06-25 06:03:17 编辑:袖梨 来源:一聚教程网

连接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 {
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 {
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 {
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;
}
}
}

热门栏目