以下代码是参考几个不同人的写法总结写成的，肯定还有很大的优化空间，暂存该版本；有建议的欢迎提出；
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace FirstBI
{
public abstract class SqlHelpertrain//定义为abstract抽象类，不能被实例化，在应用时直接调用
{
/*1.获取数据库连接字符串；
* 2.Connection 链接绑定，open/close；
* 3.实例化一个Command命令，给定命令执行的超时时间 ，命令执行的连接；命令的类型（增/删/改/查）
* ，是否带事务，执行用到哪些参数，还有最重要的命令执行的sql字符串；
* 4.SqlDataAdapter承接命令返回的结果集
*/
private static int TimeOut = ;
private static string connStrs = ConfigurationManager.ConnectionStrings["FirstBI.Properties.Settings.BaseERPConnectionString"].ConnectionString;
/// <summary>
/// 获取SqlCommand
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdType"></param>
/// <param name="tran"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static SqlCommand GetSqlCommand(string sql, SqlConnection conn, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams)
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = TimeOut;
cmd.CommandType = cmdType;
if (tran != null)
cmd.Transaction = tran;
if (sqlParams != null && sqlParams.Length > )
cmd.Parameters.AddRange(sqlParams);
return cmd;
}
/// <summary>
/// DataTable
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="tran"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static DataTable GetDataTable(string sql, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams)
{
using (SqlConnection conn = new SqlConnection(connStrs))
{
try
{
conn.Open();
using (SqlCommand cmd = GetSqlCommand(sql, conn, cmdType, tran, sqlParams))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
} }
catch (SqlException ex)
{
StringBuilder log = new StringBuilder();
log.Append("查询数据错误：");
log.Append(ex);
throw new Exception(log.ToString());
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// DataSet
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="tran"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams)
{
using (SqlConnection conn = new SqlConnection(connStrs))
{
using (SqlCommand cmd = GetSqlCommand(sql, conn, cmdType, tran, sqlParams))
{
try
{
conn.Open();
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
catch (SqlException ex)
{
StringBuilder log = new StringBuilder();
log.Append("查询数据错误：");
log.Append(ex);
throw new Exception(log.ToString());
}
finally
{
conn.Close();
}
}
}
}
/// <summary>
/// 返回影响的行数
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="tran"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static int ExecNonQuery(string sql, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams)
{
/*定义： SqlCommand cmd = new SqlCommand()；//或者SqlCommand cmd = new SqlConnection().CreateCommand(); 注意参数
cmd.CommandType = CommandType.StoredProcedure; //存储过程
cmd.CommandType = CommandType.Text; //sql语句
cmd.CommandType = CommandType.TableDirect; System.Data.CommandType.TableDirect表示要执行的是表
，此时，cmd.CommandText 的值应该是要查询表的的名称。查询结果返回的是整个表。
*/
int count = ;
using (SqlConnection conn = new SqlConnection(connStrs))
{
using (SqlCommand cmd = GetSqlCommand(sql, conn, cmdType, tran, sqlParams))
{ try
{
conn.Open(); if (cmdType == CommandType.StoredProcedure)
cmd.Parameters.AddWithValue("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;
count = cmd.ExecuteNonQuery();
if (count <= )
if (cmdType == CommandType.StoredProcedure)
count = (int)cmd.Parameters["@RETURN_VALUE"].Value;
}
catch (SqlException ex)
{
StringBuilder log = new StringBuilder();
log.Append("查询数据错误：");
log.Append(ex);
throw new Exception(log.ToString());
}
finally
{
conn.Close();
}
}
}
return count;
}
//返回查询结果的第一行第一列
public static object QueryScalar(string sql, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams)
{
using (SqlConnection conn = new SqlConnection(connStrs))
{
using (SqlCommand cmd = GetSqlCommand(sql, conn, cmdType, tran, sqlParams))
{
try
{
conn.Open();//创建cmd之前或之后Open都是可以的
return cmd.ExecuteScalar();
}
catch (SqlException ex)
{
StringBuilder log = new StringBuilder();
log.Append("查询数据出错：");
log.Append(ex);
throw new Exception(log.ToString());
}
finally
{
conn.Close();
}
}
}
}
/// <summary>
/// 使用完应关闭Reader；因为SQLDataReader为在线操作数据库，所以这个封装的方法中不能关闭连接；
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="tran"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static SqlDataReader GetDataReader(string sql, CommandType cmdType, SqlTransaction tran, params SqlParameter[] sqlParams)
{
//SqlDataReader要求，它读取数据的时候有，它独占它的SqlConnection对象，而且SqlConnection必须是Open状态
//SqlDataReader与SqlDataAdapter的区别：SqlDataReader为在线操作数据库，SqlDataAdapter为离线操作（打开连接获取数据集之后关闭连接，然后离线操作数据库，然后再打开连接将数据更新到数据库）
SqlConnection conn = new SqlConnection(connStrs);//不可以使用using
SqlCommand cmd = GetSqlCommand(sql, conn, cmdType, tran, sqlParams);
try
{
conn.Open();
//CommandBehavior.CloseConnection当SqlDataReader释放的时候，顺便把SqlConnection对象也释放掉
return cmd.ExecuteReader(CommandBehavior.CloseConnection);//在执行该命令时，如果关闭关联的 DataReader 对象，则关联的 Connection 对象也将关闭
}
catch (SqlException ex)
{
conn.Close();//异常时要关闭连接
StringBuilder log = new StringBuilder();
log.Append("查询数据错误：");
log.Append(ex);
throw new Exception(log.ToString());
}
}
}
}