using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DAL
{
public class DBHelper
{
private static SqlConnection connection;
/// <summary>
/// 数据库连接
/// </summary>
public static SqlConnection Connection
{
get
{
//if (Auth.GetAuth() == true)
//{
string connectionString = GetSqlServerDBConnectionString();//"Data Source=.;Initial Catalog=Learn;Persist Security Info=True;User ID=sa;Password=123456";
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
//}
return connection;
}
}
//解密数据连接字段
private static string GetSqlServerDBConnectionString()
{
string SqlDataBase = Model.Encrypt.GetDecode(ConfigurationManager.AppSettings["SqlDataBase"]);
string SqlDataSource = Model.Encrypt.GetDecode(ConfigurationManager.AppSettings["SqlDataSource"]);
string SqlUserID = Model.Encrypt.GetDecode(ConfigurationManager.AppSettings["SqlUserID"]);
string SqlPassword = Model.Encrypt.GetDecode(ConfigurationManager.AppSettings["SqlPassword"]);
string conStr = "Data Source=" + SqlDataSource + ";Initial Catalog=" + SqlDataBase +
";Persist Security Info=True;User ID=" + SqlUserID + ";Password=" + SqlPassword + ";";
return conStr;
}
#region 返回第一行第一列数据
/// <summary>
/// 非传参查询 返回第一行第一列数据 ----存储过程执行方式
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <returns>返回第一行第一列数据</returns>
public static object GetScalar(string StoredProcedureName)
{
SqlCommand cmd = new SqlCommand(StoredProcedureName, Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
return cmd.ExecuteScalar();
}
/// <summary>
/// 传参查询 返回第一行第一列数据 -存储过程执行方式
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="values">变量</param>
/// <returns>返回第一行第一列数据</returns>
public static object GetScalar(string StoredProcedureName, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(StoredProcedureName, Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
return cmd.ExecuteScalar();
}
/// <summary>
/// 返回第一行第一列数据 -非参sql语句执行方式
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns>返回第一行第一列数据</returns>
public static object GetScalarBySql(string strSql)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
return cmd.ExecuteScalar();
}
/// <summary>
/// 返回第一行第一列数据 -带参sql语句执行方式
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="values">参数</param>
/// <returns>返回第一行第一列数据</returns>
public static object GetScalarBySql(string strSql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(values);
return cmd.ExecuteScalar();
}
#endregion
#region 执行增删改操作
/// <summary>
/// 执行增 删 改 等SQL操作 -存储过程执行方式
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="values">变量</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteCommand(string StoredProcedureName, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(StoredProcedureName, Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 执行增 删 改 等SQL操作 -非参sql语句执行方式
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="values">变量</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteCommandBySql(string strSql)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 执行增 删 改 等SQL操作 -带参sql语句执行方式
/// </summary>
/// <param name="strSql">存储过程名称</param>
/// <param name="values">参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteCommandBySql(string strSql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
#endregion
#region 数据查询
/// <summary>
/// 非传参数据查询 -存储过程执行方式
/// </summary>
/// <param name="StoredProcedureName">存储过程</param>
/// <returns>返回 SqlDataReader</returns>
public static SqlDataReader GetReader(string StoredProcedureName)
{
SqlCommand cmd = new SqlCommand(StoredProcedureName, Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 传参数据查询 -存储过程执行方式
/// </summary>
/// <param name="StoredProcedureName">存储过程</param>
/// <param name="values">变量</param>
/// <returns>返回 SqlDataReader</returns>
public static SqlDataReader GetReader(string StoredProcedureName, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(StoredProcedureName, Connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 非传参数据查询 -sql语句执行方式
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns>返回 SqlDataReader</returns>
public static SqlDataReader GetReaderBySql(string strSql)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 传参数据查询 -sql语句执行方式
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="values">参数</param>
/// <returns>返回 SqlDataReader</returns>
public static SqlDataReader GetReaderBySql(string strSql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddRange(values);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion
#region DataSet数据查询
/// <summary>
/// DataTable 非参数据查询 -sql语句执行方式
/// </summary>
/// <param name="StoredProcedureName">存储过程 </param>
/// <returns>返回DataTable </returns>
public static DataTable GetDataSetBySql(string StoredProcedureName)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(StoredProcedureName, Connection);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// DataTable 传参数据查询 -存储过程执行方式
/// </summary>
/// <param name="StoredProcedureName">存储过程</param>
/// <param name="values">变量</param>
/// <returns>返回DataTable</returns>
public static DataTable GetDataSet(string StoredProcedureName, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(StoredProcedureName, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
#endregion
#region 事务处理
/// <summary>
/// 执行事务 增删改 --存储过程执行方式
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="values">参数列表</param>
/// <returns>返回bool执行结果</returns>
public static Boolean ExecuteTransaction(string[] StoredProcedureName, List<SqlParameter[]> values)
{
int i = 0; Boolean yn = false;
SqlCommand cmd = new SqlCommand();
SqlTransaction sqlt = Connection.BeginTransaction(IsolationLevel.Unspecified);
cmd.Transaction = sqlt;
cmd.Connection = Connection;
try
{
for (; i < StoredProcedureName.Length; i++)
{
cmd.CommandText = StoredProcedureName[i];
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddRange(values[i]);
cmd.ExecuteNonQuery();
}
sqlt.Commit();
}
catch (Exception ex)
{
sqlt.Rollback();
}
finally
{
Connection.Close();
}
if (i == StoredProcedureName.Length)
{
yn = true;
}
return yn;
}
/// <summary>
/// 执行事务 增删改 --sql语句执行方式
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns>返回bool执行结果</returns>
public static Boolean ExecuteTransaction(string[] strSql)
{
int i = 0; Boolean yn = false;
SqlCommand cmd = new SqlCommand();
SqlTransaction sqlt = Connection.BeginTransaction(IsolationLevel.Unspecified);
cmd.Transaction = sqlt;
cmd.Connection = Connection;
try
{
for (; i < strSql.Length; i++)
{
cmd.CommandText = strSql[i];
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
sqlt.Commit();
}
catch (Exception ex)
{
sqlt.Rollback();
}
finally
{
Connection.Close();
}
if (i == strSql.Length)
{
yn = true;
}
return yn;
}
#endregion
}
}
以前经常用的DBHelper.cs类
最新推荐文章于 2020-08-22 21:59:24 发布