sql数据访问接口类.txt14热情是一种巨大的力量，从心灵内部迸发而出，激励我们发挥出无穷的智慧和活力；热情是一根强大的支柱，无论面临怎样的困境，总能催生我们乐观的斗志和顽强的毅力……没有热情，生命的天空就没的色彩。using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Data.SqlClient;
///
///DataBase 的摘要说明
///
namespace https://www.doczj.com/doc/2e12835382.html,mon
{
public class DataBase
{
string connectString;
SqlConnection connection;
public DataBase()
{
this.connectString = ConfigurationSettings.AppSettings["conn"];
}
public void Open()
{
if (this.connection == null)
{
this.connection = new SqlConnection(this.connectString);//建立一个连接
this.connection.Open();
}
if (this.connection.State.Equals(ConnectionState.Closed))//如果连接状态是关闭的
{
this.connection.Open();
}
}
public void Close()
{
if (this.connection != null)//如果连接存在
{
this.connection.Close();
}
}
public SqlConnection GetConnection()
{
this.Open();
return connection;
}
///
/// 对SqlCommand属性作设置
///
/// SqlCommand对象
/// SqlCommand对象的命令类型
/// SqlCommand对象的文本
/// SqlCommand对象的参数
public void Preparecommand(SqlCommand cmd, CommandType ct, string cmdTxt, SqlParameter[] cmdParms)
{
this.Open();//打开连接
cmd.Connection = this.connection;//指明这个Command是基于我打开的这个连接
https://www.doczj.com/doc/2e12835382.html,mandText = cmdTxt;
https://www.doczj.com/doc/2e12835382.html,mandType = ct;
if (cmdParms != null)//如果使用存储过程
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
public void Preparecommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType ct, string cmdTxt, SqlParameter[] cmdParms)
{
//this.Open();
cmd.Connection = conn;
cmd.Transaction = trans;//把事务和command关联
https://www.doczj.com/doc/2e12835382.html,mandText = cmdTxt;
https://www.doczj.com/doc/2e12835382.html,mandType = ct;
if (cmdParms != null)//如果使用存储过程
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
///
/// 对数据库进行增删改方法
///
/// SqlCommand对象的命令类型
/// SqlCommand对象的文本
/// SqlCommand对象的参数
///
public void ExcuteNonQuery(CommandType ct, string cmdTxt, SqlParameter[] cmdParms)//对数据库进行增删改方法
{
SqlCommand cmd = new SqlCommand();
this.Preparecommand(cmd, ct, cmdTxt, cmdParms);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
this.Close();
}
//对数据库进行增删改方法（使用事务）
public void ExcuteNonQuery(SqlTransaction trans,CommandType ct, string cmdTxt, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
this.Preparecommand(cmd,trans.Connection,trans,ct, cmdTxt, cmdParms);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
//对数据库进行增删改方法
public object ExcuteNonQueryReturn(SqlTransaction trans,CommandType ct, string cmdTxt, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
this.Preparecommand(cmd,trans.Connection,trans, ct, cmdTxt, cmdParms);
cmd.ExecuteNonQuery();
object ob = cmd.Parameters["@Return"].Value;//获取数组中名为"@Return"的值(orderId)赋给ob
return ob;
}
///
/// 读取数据
///
/// SqlCommand对象的命令类型
/// SqlCommand对象的文本
/// SqlCommand对象的参数
///
public SqlDataReader ExcuteDataReader(CommandType ct, string cmdTxt, SqlParameter[] cmdParms)//读取数据
{
SqlCommand cmd = new SqlCommand();
this.Preparecommand(cmd, ct, cmdTxt, cmdParms);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
//调用名为cmdText的存储过程
public SqlDataReader ExecuteDataReader(string cmdText)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectString);
try
{
this.Preparecommand(cmd, conn, null, CommandType.StoredProcedure, cmdText, null);
SqlDataReader rdr = cmd.ExecuteReader();
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
public object Excut
eScalar(CommandType ct, string cmdTxt, SqlParameter[] cmdParms)// 返回第一行第一列的一个数值
{
SqlCommand cmd = new SqlCommand();
this.Preparecommand(cmd, ct, cmdTxt, cmdParms);
return cmd.ExecuteScalar();
}
///
/// 创建DataAdapter方法
///
/// SqlCommand的命令类型
/// SqlCommand对象的文本
/// SqlCommand对象的参数
/// 整型数值
///
public SqlDataAdapter CreateDataAdapter(CommandType ct, string cmdTxt, SqlParameter[] cmdParms, int opt)
{
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
this.Preparecommand(cmd, ct, cmdTxt, cmdParms);
switch (opt)
{
case 1:
sda.SelectCommand = cmd;
break;
case 2:
sda.InsertCommand = cmd;
break;
case 3:
sda.DeleteCommand = cmd;
break;
case 4:
sda.UpdateCommand = cmd;
break;
default:
break;
}
this.Close();
return sda;
}
}
}