using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
public static class DBHelpers
{
#region [ Connection ]
public static string connectionString = "Data Source=USER-20190309OM;Initial Catalog=users;Integrated Security=True";
public static SqlConnection GetConnection
{
get
{
return new SqlConnection(connectionString);
}
}
#endregion
#region [ ExecuteNonQuery ]
/// <summary>
/// 普通SQL语句执行增删改
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteNonQuery(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 存储过程执行增删改
/// </summary>
/// <param name="cmdText">存储过程</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQueryByProc(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteNonQuery(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 执行增删改
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] commandParameters)
{
int result = 0;
using (SqlConnection conn = GetConnection)
{
try
{
SqlCommand command = new SqlCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
result = command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
return result;
}
#endregion
#region [ ExecuteReader ]
/// <summary>
/// SQL语句得到 MySqlDataReader 对象
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>MySqlDataReader 对象</returns>
public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteReader(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 存储过程得到 MySqlDataReader 对象
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>MySqlDataReader 对象</returns>
public static SqlDataReader ExecuteReaderByProc(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteReader(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 得到 MySqlDataReader 对象
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>MySqlDataReader 对象</returns>
public static SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, params SqlParameter[] commandParameters)
{
SqlDataReader result = null;
//using (MySqlConnection conn = GetConnection)
//{
SqlConnection conn = GetConnection;
conn.Open();
try
{
SqlCommand command = new SqlCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
result = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
conn.Close();
throw new Exception(ex.Message);
}
return result;
}
#endregion
#region [ ExecuteDataSet ]
/// <summary>
/// 执行SQL语句, 返回DataSet
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataSet </returns>
public static DataSet ExecuteDataSet(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteDataSet(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 执行存储过程, 返回DataSet
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataSet </returns>
public static DataSet ExecuteDataSetByProc(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteDataSet(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataSet </returns>
public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params SqlParameter[] commandParameters)
{
DataSet result = null;
using (SqlConnection conn = GetConnection)
{
try
{
SqlCommand command = new SqlCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
result = new DataSet();
adapter.Fill(result);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
return result;
}
#endregion
#region [ ExecuteDataTable ]
/// <summary>
/// 执行SQL语句, 返回DataTable
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataTable </returns>
public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteDataTable(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 执行存储过程, 返回DataTable
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataTable </returns>
public static DataTable ExecuteDataTableByProc(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteDataTable(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataTable </returns>
public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params SqlParameter[] commandParameters)
{
DataTable dtResult = null;
DataSet ds = ExecuteDataSet(cmdText, cmdType, commandParameters);
if (ds != null && ds.Tables.Count > 0)
{
dtResult = ds.Tables[0];
}
return dtResult;
}
#endregion
#region [ ExecuteScalar ]
/// <summary>
/// 普通SQL语句执行ExecuteScalar
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static object ExecuteScalar(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteScalar(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 存储过程执行ExecuteScalar
/// </summary>
/// <param name="cmdText">存储过程</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static object ExecuteScalarByProc(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteScalar(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 执行ExecuteScalar
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static object ExecuteScalar(string cmdText, CommandType cmdType, params SqlParameter[] commandParameters)
{
object result = null;
using (SqlConnection conn = GetConnection)
{
try
{
SqlCommand command = new SqlCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
result = command.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
return result;
}
#endregion
#region [ PrepareCommand ]
/// <summary>
/// Command对象执行前预处理
/// </summary>
/// <param name="command"></param>
/// <param name="connection"></param>
/// <param name="trans"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
private static void PrepareCommand(SqlCommand command, SqlConnection connection, CommandType cmdType, string cmdText, SqlParameter[] commandParameters)
{
try
{
if (connection.State != ConnectionState.Open) connection.Open();
command.Connection = connection;
command.CommandText = cmdText;
command.CommandType = cmdType;
if (commandParameters != null)
{
foreach (SqlParameter parm in commandParameters)
command.Parameters.Add(parm);
}
}
catch(Exception ex)
{
Console.Write(ex.Message);
}
}
#endregion
}
}