DBhelper
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 DBUtility
{
public abstract class WBSQLHelper
{
#region Public
public static readonly string strCn = ConfigurationManager.ConnectionStrings["WBConnString"].ConnectionString;
//public static readonly string strCn = "server=128.1.10.5\\secondinstance;User Id=sa;Password=sz562;database=HSEDB";
public static SqlConnection oConn = new SqlConnection(strCn);
/// <summary>
/// 打开连接
/// </summary>
private static void OpenCon(SqlConnection con)
{
if (con.State != ConnectionState.Open)
con.Open();
}
/// <summary>
/// 释放Connection对象
// <param name="con">CONNECTION参数</param>
/// </summary>
public static void DisposeCon()
{
if ((oConn.State == ConnectionState.Open) && (oConn != null))
oConn.Close();
oConn.Dispose();
}
#endregion
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(strCn))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
}
public static bool UpdateDB(string pSQL, ref SqlTransaction ltxn, params SqlParameter[] cmdParms)
{
SqlConnection objCnn = null;
SqlCommand objCmd = new SqlCommand();
if (ltxn == null)
{
objCnn = new SqlConnection(strCn);
objCmd = new SqlCommand(pSQL, objCnn);
objCnn.Open();
objCmd.ExecuteNonQuery();
objCnn.Close();
}
else
{
PrepareCommand(objCmd, ltxn.Connection, ltxn, CommandType.Text, pSQL, cmdParms);
//objCmd = new SqlCommand(pSQL, ltxn.Connection, ltxn);
objCmd.ExecuteNonQuery();
objCmd.Parameters.Clear();
}
return true;
}
/// <summary>
/// 准备执行一个命令
/// </summary>
/// <param name="cmd">sql命令</param>
/// <param name="conn">Sql连接</param>
/// <param name="trans">Sql事务</param>
/// <param name="cmdType">命令类型例如 存储过程或者文本</param>
/// <param name="cmdText">命令文本,例如:Select * from Products</param>
/// <param name="cmdParms">执行命令的参数</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
public static DataTable Select(string sql)
{
SqlConnection conn = new SqlConnection(strCn);
DataTable dt = new DataTable();
SqlDataAdapter dap = new SqlDataAdapter(sql, conn);
dap.Fill(dt);
return dt;
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
public static int ExceQuery(string sql)
{
SqlConnection conn = new SqlConnection(strCn);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
int i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(strCn))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}
#region 返回Command对象
/// <summary>
/// 共用成员函数,传入存储过程名,并返回未带参数的Command对象
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="Parameters">类型为SqlParameter的数组</param>
/// </summary>
/// <returns>oComd</returns>
private static SqlCommand BuildQueryCommand(string StoredProcedureName)
{
OpenCon(oConn);
SqlCommand oCmd = new SqlCommand(StoredProcedureName, oConn);
oCmd.CommandType = CommandType.StoredProcedure;
return oCmd;
}
/ <summary>
// /// 共用成员函数,传入存储过程名、参数和值,并返回带参数和值的Command对象
// /// </summary>
// /// <param name="StoredProcedureName">存储过程名称</param>
// /// <param name="Parameters">类型为SqlParameter的数组</param>
// /// <param name="ParaValue">类型为object的数组</param>
// /// <returns>oComd</returns>
// public static SqlCommand BuildQueryCommand(string StoredProcedureName, SqlParameter[] Parameters, object[] ParaValue)
// {
// OpenCon(oConn);
// SqlCommand oCmd = new SqlCommand(StoredProcedureName, oConn);
// oCmd.CommandType = CommandType.StoredProcedure;
// foreach (SqlParameter Parameter in Parameters)
// oCmd.Parameters.Add(Parameter);
// for (int i = 0; i < ParaValue.Length; i++)
// {
// oCmd.Parameters[i].Value = ParaValue[i];
// }
// return oCmd;
// }
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// </summary>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(string storedProcName, SqlParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, oConn);
OpenCon(oConn);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
return command;
}
/// <summary>
/// 创建 SqlCommand 对象实例(用来返回一个整数值)
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// </summary>
/// <returns>SqlCommand 对象实例</returns>
private static SqlCommand BuildIntCommand(string storedProcName, SqlParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(storedProcName, parameters);
command.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
/// <summary>
///向SQLCOMMAND自动添加参数,返回SQLCOMMAND对象
/// <param name="oComd">待增加参数的SQLCOMMAND</param>
/// <param name="commandParameters">存储过程参数</param>
/// </summary>
/// <returns>oComd</returns>
private static SqlCommand AddParameters(SqlCommand oComd, SqlParameter[] commandParameters)
{
if (oComd == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (SqlParameter p in commandParameters)
{
if (p != null)
{
// Check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
oComd.Parameters.Add(p);
}
}
}
return oComd;
}
/// <summary>
///向SQLCOMMAND自动添加参数和值,返回SQLCOMMAND对象
/// <param name="command">待增加参数的SQLCOMMAND</param>
/// <param name="commandParameters">待增加到SQLCOMMAND参数</param>
/// <param name="parameterValues">待增加到SQLCOMMAND参数值</param>
/// </summary>
/// <returns>oComd</returns>
private static SqlCommand AssignParameterValues(SqlCommand command, SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
throw new ArgumentException(" Sorry! Parameter and parametervalue are not empty。");
}
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException(" Sorry! Parameter Length is not equal parametervalue Length。");
}
command = AddParameters(command, commandParameters);
for (int i = 0; i < parameterValues.Length; i++)
{
command.Parameters[i].Value = parameterValues[i];
}
return command;
}
#endregion
#region 返回影响行数
/// <summary>
/// 执行存储过程,返回影响的行数
/// 用于有参数的添加/修改/删除
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// </summary>
/// <returns></returns>
public static int RunProcedure(string storedProcName, SqlParameter[] parameters)
{
int result;
OpenCon(oConn);
// SqlCommand command = BuildIntCommand(storedProcName, parameters);
SqlCommand command = BuildQueryCommand(storedProcName, parameters);
result = command.ExecuteNonQuery();
command.Parameters.Clear();
// result = (int)command.Parameters["ReturnValue"].Value;
return result;
}
/// <summary>
/// 执行存储过程,返回查询行数
/// 用于Select Count(*) 这样的查询
/// <param name="StoCredProcedureName">存储过程名</param>
/// <param name="TableName">表名</param>
/// <param name="WhereStr">条件</param>
/// </summary>
/// <returns>查询后返回的行数</returns>
public static string RunQueryCount(string StoredProcedureName, string TableName, string WhereStr)
{
SqlCommand cmd = new SqlCommand();
SqlParameter[] paras = new SqlParameter[2];
paras[0] = new SqlParameter("@TableName", TableName);
paras[1] = new SqlParameter("@WhereStr", WhereStr);
cmd = BuildQueryCommand(StoredProcedureName, paras);
string result = cmd.ExecuteScalar().ToString();
cmd.Parameters.Clear();
return result;
}
/// <summary>
/// 传入SQL语句参数返回影响的行数
/// <param name="SqlStr">SQL句</param>
/// </summary>
/// <returns>Result</returns>
public static int GetInfectCount(string SqlStr)
{
int Result;
SqlCommand cmd = new SqlCommand(SqlStr, oConn);
OpenCon(oConn);
cmd.CommandType = CommandType.Text;
Result = cmd.ExecuteNonQuery();
return Result;
}
/// <summary>
/// 执行存储过程,返回影响的行数
/// 用于无参数的添加/修改/删除
/// <param name="storedProcName">存储过程名</param>
/// <param name="rowsAffected">影响的行数</param>
/// </summary>
/// <returns></returns>
public static int RunProcedure(string StoredProcedureName)
{
int NumAffect;
OpenCon(oConn);
SqlCommand oCmd = BuildQueryCommand(StoredProcedureName);
NumAffect = oCmd.ExecuteNonQuery();
// NumAffect = (int)oCmd.Parameters["ReturnValue"].Value;
oCmd.ExecuteScalar().ToString();
return NumAffect;
}
#endregion
public static SqlDataReader ExecuteDataReader(CommandType commandType, string commandText, params SqlParameter[] prms)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection(strCn);
cmd.CommandText = commandText;
cmd.CommandType = commandType;
cmd.CommandTimeout = 0;
cmd.Parameters.Clear();
if (null != prms && prms.Length > 0)
{
foreach (SqlParameter sp in prms)
{
cmd.Parameters.Add(sp);
}
}
try
{
cmd.Connection.Open();
SqlDataReader sr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sr;
}
catch (Exception err)
{
throw new Exception(err.Message);
}
}
/// <summary>
///
/// </summary>
/// <param name="ProcName">储存过程名称</param>
/// <param name="parameter">参数集合</param>
/// <param name="outputNameList">返回得结果集</param>
/// <returns></returns>
public static string ExeProc(string procName, SqlParameter[] parameters, string outputName)
{
string msg = "";
try
{
using (SqlConnection conn = new SqlConnection(strCn))
{
SqlCommand cmd = new SqlCommand(procName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
for (int i = 0; i < parameters.Length; i++)
{
cmd.Parameters.Add(parameters[i]);
}
cmd.Connection.Open();
cmd.ExecuteNonQuery();
msg = cmd.Parameters[outputName].Value.ToString();
}
return msg;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
///
/// </summary>
/// <param name="ProcName">储存过程名称</param>
/// <param name="parameter">参数集合</param>
/// <param name="outputNameList">返回得结果集</param>
/// <returns></returns>
public static List<string> ExeProc(string procName, List<SqlParameter> parameters, List<string> outputNameList)
{
List<string> msgs = new List<string>();
try
{
using (SqlConnection conn = new SqlConnection(strCn))
{
SqlCommand cmd = new SqlCommand(procName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
foreach (SqlParameter item in parameters)
{
cmd.Parameters.Add(item);
}
cmd.ExecuteNonQuery();
foreach (var name in outputNameList)
{
string msg = cmd.Parameters[name].Value.ToString();
msgs.Add(msg);
}
}
return msgs;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 转换成Int32类型
/// </summary>
public static int ExecToInt32(SqlDataReader reader, string dataName)
{
return reader[dataName] == DBNull.Value ? 0 : Convert.ToInt32(reader[dataName]);
}
/// <summary>
/// 转换成String类型
/// </summary>
public static string ExecToString(SqlDataReader reader, string dataName)
{
return reader[dataName] == DBNull.Value ? "" : reader[dataName].ToString();
}
/// <summary>
/// 转换成DateTime类型
/// </summary>
public static DateTime ExecToDateTime(SqlDataReader reader, string dataName)
{
if (reader[dataName] == DBNull.Value || reader[dataName].ToString() == "")
{
return DateTime.MinValue;
}
else
{
return Convert.ToDateTime(reader[dataName]);
}
return reader[dataName] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(reader[dataName]);
}
/// <summary>
/// 转换成Double类型
/// </summary>
public static Double ExecToDateDouble(SqlDataReader reader, string dataName)
{
return reader[dataName] == DBNull.Value ? 0 : Convert.ToDouble(reader[dataName]);
}
/// <summary>
/// 转换成Double类型
/// </summary>
public static float ExecToDateFloat(SqlDataReader reader, string dataName)
{
return reader[dataName] == DBNull.Value ? 0 : Convert.ToSingle(reader[dataName]);
}
/// <summary>
/// 转换成Decimal类型
/// </summary>
public static decimal ExecToDecimal(SqlDataReader reader, string dataName)
{
return reader[dataName] == DBNull.Value ? Convert.ToDecimal("0.00") : decimal.Round(decimal.Parse(reader[dataName].ToString()), 2);
}
/// <summary>
/// 传入DataSet、存储过程名、参数和参数值,返回DataSet.
/// <param name="ProcName">存储过程名</param>
/// <param name="commandParameters">参数数组</param>
/// <param name="parameterValues">参数数组值</param>
/// </summary>
public static DataTable ExecuteDataset(DataTable dt, string ProcName, SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
cmd = BuildQueryCommand(ProcName, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
cmd.Parameters.Clear();
return dt;
}
public static DataSet ExecuteDataset(DataSet ds, string ProcName, SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
cmd = BuildQueryCommand(ProcName, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
public static DataTable ExecuteDataset(DataTable dt, string ProcName)
{
SqlCommand cmd = new SqlCommand();
cmd = BuildQueryCommand(ProcName);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
cmd.Parameters.Clear();
return dt;
}
public static DataSet ExecuteDataset(DataSet ds, string ProcName)
{
SqlCommand cmd = new SqlCommand();
cmd = BuildQueryCommand(ProcName);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
}