C# 基于Sql 数据访问类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace SqlDAL
{
public class DataAccess
{
private SqlConnection conn; //SQL连接
private SqlTransaction sqlTran; //事务处理
private bool inTran = false; //标识是否处于事务中
private string connStr; //连接字符串
/// <summary>
/// 默认构造函数 用于初始化数据库连接
/// </summary>
public DataAccess()
{
connStr = "";
conn = new SqlConnection(connStr);
}
/// <summary>
/// 打开数据库连接
/// </summary>
public void open()
{
if (this.conn.State.ToString().ToUpper() != "OPEN")
{
this.conn.Open();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void close()
{
if (this.conn.State.ToString().ToUpper() == "OPEN")
{
this.conn.Close();
}
}
/// <summary>
/// 开始一个事务
/// </summary>
public void beginTran()
{
this.sqlTran = this.conn.BeginTransaction();
this.inTran = true;
}
/// <summary>
/// 提交一个事务
/// </summary>
public void commitTran()
{
this.sqlTran.Commit();
this.inTran = false;
}
/// <summary>
/// 回滚当前事务
/// </summary>
public void rollbackTran()
{
this.sqlTran.Rollback();
this.inTran = false;
}
/// <summary>
/// 执行sql语句返回受影响的行数
/// </summary>
/// <param name="sqlStr">执行的sql语句</param>
/// <returns>受影响的行数</returns>
public int execSql_ReInt(string sqlStr)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.conn;
if (this.inTran)
{
cmd.Transaction = this.sqlTran;
}
cmd.CommandText = sqlStr;
int i = cmd.ExecuteNonQuery();
return i;
}
/// <summary>
/// 执行带参数的sql语句返回受影响的行数
/// </summary>
/// <param name="sqlStr">执行的sql语句</param>
/// <param name="para">参数列表</param>
/// <returns>受影响的行数</returns>
public int execSql_ReInt(string sqlStr, IDataParameter[] para)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.conn;
if (inTran)
{
cmd.Transaction = sqlTran;
}
cmd.CommandText = sqlStr;
if (para != null)
{
foreach (SqlParameter paramete in para)
{
cmd.Parameters.Add(paramete);
}
}
int i = cmd.ExecuteNonQuery();
return i;
}
/// <summary>
/// 执行带参数sql语句返回受影响行数
/// </summary>
/// <param name="sqlStr">sql语句</param>
/// <param name="ParamsName">参数名数组</param>
/// <param name="ParamsValue">参数值数组</param>
/// <returns>受影响函数</returns>
public int execSql_ReInt(string sqlStr, string[] ParamsName, object[] ParamsValue)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.conn;
if (inTran)
{
cmd.Transaction = sqlTran;
}
if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
{
throw new System.Exception("参数和值不对应!");
}
cmd.CommandText = sqlStr;
if (ParamsName != null)
{
SqlParameter SqlParams;
for (int i = 0; i < ParamsName.Length; i++)
{
SqlParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
cmd.Parameters.Add(SqlParams);
}
}
int k = cmd.ExecuteNonQuery();
return k;
}
/// <summary>
/// 执行sql语句返回数据集
/// </summary>
/// <param name="sqlStr">执行sql语句</param>
/// <returns>返回的数据集</returns>
public DataSet execSql_ReDs(string sqlStr)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.conn;
if (inTran)
{
cmd.Transaction = sqlTran;
}
cmd.CommandText = sqlStr;
DataSet ds = new DataSet();
using (SqlDataAdapter ad = new SqlDataAdapter())
{
ad.SelectCommand = cmd;
ad.Fill(ds);
}
return ds;
}
/// <summary>
/// 执行带参数sql语句返回数据集
/// </summary>
/// <param name="sqlStr">执行的sql语句</param>
/// <param name="para">参数列表</param>
/// <returns>返回的数据集</returns>
public DataSet execSql_ReDs(string sqlStr, IDataParameter[] para)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.conn;
if (inTran)
{
cmd.Transaction = sqlTran;
}
cmd.CommandText = sqlStr;
if (para != null)
{
foreach (SqlParameter parateme in para)
{
cmd.Parameters.Add(parateme);
}
}
DataSet ds = new DataSet();
using (SqlDataAdapter ad = new SqlDataAdapter())
{
ad.SelectCommand = cmd;
ad.Fill(ds);
}
return ds;
}
/// <summary>
/// 执行带参数sql语句返回数据集
/// </summary>
/// <param name="sqlStr">sql语句</param>
/// <param name="ParamsName">参数名数组</param>
/// <param name="ParamsValue">参数值数组</param>
/// <returns>返回数据集</returns>
public DataSet execSql_ReDs(string sqlStr, string[] ParamsName, object[] ParamsValue)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.conn;
if (inTran)
{
cmd.Transaction = sqlTran;
}
if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
{
throw new System.Exception("参数和值不对应!");
}
cmd.CommandText = sqlStr;
if (ParamsName != null)
{
SqlParameter SqlParams;
for (int i = 0; i < ParamsName.Length; i++)
{
SqlParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
cmd.Parameters.Add(SqlParams);
}
}
DataSet ds = new DataSet();
using (SqlDataAdapter ad = new SqlDataAdapter())
{
ad.SelectCommand = cmd;
ad.Fill(ds);
}
return ds;
}
/// <summary>
/// 执行存储过程返回return值 string 类型
/// </summary>
/// <param name="StoreName">过程名</param>
/// <param name="ParamsName">参数名数组</param>
/// <param name="ParamsValue">参数值数组</param>
/// <returns>Return值</returns>
public string execProc_ReRet(string StoreName, string[] ParamsName, object[] ParamsValue)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.conn;
if (inTran)
{
cmd.Transaction = sqlTran;
}
if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
{
throw new System.Exception("参数和值不对应!");
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoreName;
if (ParamsName != null)
{
SqlParameter SqlParams;
for (int i = 0; i < ParamsName.Length; i++)
{
SqlParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
cmd.Parameters.Add(SqlParams);
}
//增加返回的参数
SqlParameter Re_params = new SqlParameter("Return_Value", SqlDbType.VarChar);
Re_params.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(Re_params);
}
cmd.ExecuteNonQuery();
string k = cmd.Parameters["Return_Value"].Value.ToString();
return k;
}
/// <summary>
/// 执行带参数的存储过程返回Int类型
/// </summary>
/// <param name="StoreName">存储过程名称</param>
/// <param name="ParamsName">参数名称列表</param>
/// <param name="ParamsValue">参数值列表</param>
/// <param name="OutName">返回参数名</param>
/// <returns>返回的Int类型值</returns>
public int execProc_ReInt(string StoreName, string[] ParamsName, object[] ParamsValue, string OutName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.conn;
if (inTran)
{
cmd.Transaction = sqlTran;
}
if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
{
throw new System.Exception("参数和值不对应!");
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoreName;
if (ParamsName != null)
{
SqlParameter SqlParams;
for (int i = 0; i < ParamsName.Length; i++)
{
SqlParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
if (ParamsName[i].ToString() == OutName)
{
SqlParams.Direction = ParameterDirection.Output;
}
cmd.Parameters.Add(SqlParams);
}
}
cmd.ExecuteNonQuery();
int k = Convert.ToInt32(cmd.Parameters[OutName].Value.ToString());
return k;
}
/// <summary>
/// 执行带参数的存储过程返回String类型
/// </summary>
/// <param name="StoreName">存储过程名称</param>
/// <param name="ParamsName">参数名称列表</param>
/// <param name="ParamsValue">参数值列表</param>
/// <param name="OutName">返回参数名</param>
/// <returns>返回的String类型值</returns>
public string execProc_ReStr(string StoreName, string[] ParamsName, object[] ParamsValue, string OutName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.conn;
if (inTran)
{
cmd.Transaction = sqlTran;
}
if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
{
throw new System.Exception("参数和值不对应!");
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoreName;
if (ParamsName != null)
{
SqlParameter SqlParams;
for (int i = 0; i < ParamsName.Length; i++)
{
SqlParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
if (ParamsName[i].ToString() == OutName)
{
SqlParams.Size = 255;
SqlParams.Direction = ParameterDirection.Output;
}
cmd.Parameters.Add(SqlParams);
}
}
cmd.ExecuteNonQuery();
string k = Convert.ToString(cmd.Parameters[OutName].Value.ToString());
return k;
}
/// <summary>
/// 执行带参数的存储过程返回数据集
/// </summary>
/// <param name="StoreName">过程名称</param>
/// <param name="ParamsName">参数名称列表</param>
/// <param name="ParamsValue">参数值列表</param>
/// <returns>返回的数据集</returns>
public DataSet execProc_ReDs(string StoreName, string[] ParamsName, object[] ParamsValue)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.conn;
if (inTran)
{
cmd.Transaction = sqlTran;
}
if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
{
throw new System.Exception("参数和值不对应!");
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoreName;
if (ParamsName != null)
{
SqlParameter OraParams;
for (int i = 0; i < ParamsName.Length; i++)
{
OraParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
cmd.Parameters.Add(OraParams);
}
}
DataSet ds = new DataSet();
using (SqlDataAdapter ad = new SqlDataAdapter())
{
ad.SelectCommand = cmd;
ad.Fill(ds);
}
return ds;
}
}
}