天空的影子 通过本文主要向大家介绍了c#sqlhelper,c#中sqlhelper,c#sqlhelper类,sqlhelper,sqlhelper类等相关知识,希望对您有所帮助,也希望大家支持linkedu.com www.linkedu.com
第一次写博客，想不到写什么好b（￣▽￣）d ，考虑的半天决定从sqlhelper开始，sqlhelper对程序员来说就像helloworld一样，很简单却又很重要，helloworld代表着程序员萌新第一次写代码，而sqlhelper则是初次接触数据库（不知道这种说法对不对）。
好了不废话了，下面直接上代码（无话可说了）：
public class SQLHelper
{
// 超时时间
private static int Timeout = 1000;
// 数据库名称
public const String BestNet = "BestNet";
//存储过程名称
public const String UserInfoCURD = "UserInfoCURD";
// 数据库连接字符串
private static Dictionary<String, String> ConnStrs = new Dictionary<String, String>();
/// <summary>
/// SQLServer操作类(静态构造函数)
/// </summary>
static SQLHelper()
{
ConnectionStringSettingsCollection configs = WebConfigurationManager.ConnectionStrings;
foreach (ConnectionStringSettings config in configs)
{
ConnStrs.Add(config.Name, config.ConnectionString);
}
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <param name="database">数据库(配置文件内connectionStrings的name)</param>
/// <returns>数据库连接</returns>
private static SqlConnection GetConnection(string database)
{
if (string.IsNullOrEmpty(database))
{
throw new Exception("未设置参数：database");
}
if (!ConnStrs.ContainsKey(database))
{
throw new Exception("未找到数据库：" + database);
}
return new SqlConnection(ConnStrs[database]);
}
/// <summary>
/// 获取SqlCommand
/// </summary>
/// <param name="conn">SqlConnection</param>
/// <param name="transaction">SqlTransaction</param>
/// <param name="cmdType">CommandType</param>
/// <param name="sql">SQL</param>
/// <param name="parms">SqlParameter数组</param>
/// <returns></returns>
private static SqlCommand GetCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string sql, SqlParameter[] parms)
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = cmdType;
cmd.CommandTimeout = Timeout;
if (transaction != null)
cmd.Transaction = transaction;
if (parms != null && parms.Length != 0)
cmd.Parameters.AddRange(parms);
return cmd;
}
/// <summary>
/// 查询数据，返回DataTable
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">SQL语句或存储过程名</param>
/// <param name="parms">参数</param>
/// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
/// <returns>DataTable</returns>
public static DataTable QueryDataTable(string database, string sql, SqlParameter[] parms, CommandType cmdType)
{
if (string.IsNullOrEmpty(database))
{
throw new Exception("未设置参数：database");
}
if (string.IsNullOrEmpty(sql))
{
throw new Exception("未设置参数：sql");
}
try
{
using (SqlConnection conn = GetConnection(database))
{
conn.Open();
using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
}
catch (SqlException ex)
{
System.Text.StringBuilder log = new System.Text.StringBuilder();
log.Append("查询数据出错：");
log.Append(ex);
throw new Exception(log.ToString());
}
}
/// <summary>
/// 查询数据，返回DataSet
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">SQL语句或存储过程名</param>
/// <param name="parms">参数</param>
/// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
/// <returns>DataSet</returns>
public static DataSet QueryDataSet(string database, string sql, SqlParameter[] parms, CommandType cmdType)
{
if (string.IsNullOrEmpty(database))
{
throw new Exception("未设置参数：database");
}
if (string.IsNullOrEmpty(sql))
{
throw new Exception("未设置参数：sql");
}
try
{
using (SqlConnection conn = GetConnection(database))
{
conn.Open();
using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
}
}
catch (SqlException ex)
{
System.Text.StringBuilder log = new System.Text.StringBuilder();
log.Append("查询数据出错：");
log.Append(ex);
throw new Exception(log.ToString());
}
}
/// <summary>
/// 执行命令获取唯一值(第一行第一列)
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">SQL语句或存储过程名</param>
/// <param name="parms">参数</param>
/// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
/// <returns>获取值</returns>
public static object QueryScalar(string database, string sql, SqlParameter[] parms, CommandType cmdType)
{
if (string.IsNullOrEmpty(database))
{
throw new Exception("未设置参数：database");
}
if (string.IsNullOrEmpty(sql))
{
throw new Exception("未设置参数：sql");
}
try
{
using (SqlConnection conn = GetConnection(database))
{
conn.Open();
using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
{
return cmd.ExecuteScalar();
}
}
}
catch (SqlException ex)
{
System.Text.StringBuilder log = new System.Text.StringBuilder();
log.Append("处理出错：");
log.Append(ex);
throw new Exception(log.ToString());
}
}
/// <summary>
/// 执行命令更新数据
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">SQL语句或存储过程名</param>
/// <param name="parms">参数</param>
/// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
/// <returns>更新的行数</returns>
public static int Execute(string database, string sql, SqlParameter[] parms, CommandType cmdType)
{
if (string.IsNullOrEmpty(database))
{
throw new Exception("未设置参数：database");
}
if (string.IsNullOrEmpty(sql))
{
throw new Exception("未设置参数：sql");
}
//返回(增删改)的更新行数
int count = 0;
try
{
using (SqlConnection conn = GetConnection(database))
{
conn.Open();
using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
{
if (cmdType == CommandType.StoredProcedure)
cmd.Parameters.AddWithValue("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;
count = cmd.ExecuteNonQuery();
if (count <= 0)
if (cmdType == CommandType.StoredProcedure)
count = (int)cmd.Parameters["@RETURN