using JKTAC_LMIS.Entity;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
namespace JKTAC_LMIS.DAL
{
public abstract class DbHelperSQL
{
public DbHelperSQL()
{
}
//定义连接字符串。
//protected static string ConnectionString = DecryptDBStr(ConfigurationManager.AppSettings["SQLConnectionString"], "zhangweilong");
protected static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
protected static SqlConnection Connection;
//定义数据库的打开和关闭方法
protected static void Open()
{
if (Connection == null)
{
Connection = new SqlConnection(ConnectionString);
}
if (Connection.State.Equals(ConnectionState.Closed))
{
Connection.Open();
}
}
protected static void Close()
{
if (Connection != null)
{
Connection.Close();
}
}
//判断用Sql查询的数据是否存在,true表示存在，False表示不存在
public static bool Exists(string strSql)
{
object obj = DbHelperSQL.GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = ;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == )
{
return false;
}
else
{
return true;
}
}
public static bool Exists(string strSql, params SqlParameter[] cmdParms)
{
object obj = DbHelperSQL.GetSingle(strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = ;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == )
{
return false;
}
else
{
return true;
}
}
//返回SqlDataReader数据集,使用完后记得关闭SqlDataReader
public static SqlDataReader GetDataReader(string SqlString)
{
try
{
Open();
SqlCommand cmd = new SqlCommand(SqlString, Connection);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
}
// 公有方法，获取数据，返回一个DataSet。
public static DataSet GetDataSet(string SqlString)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(SqlString, connection))
{
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;
}
}
}
}
// 公有方法，获取数据，返回一个DataTable。
public static DataTable GetDataTable(string SqlString)
{
DataSet dataset = GetDataSet(SqlString);
return dataset.Tables[];
}
// 公有方法，获取数据，返回首行首列。
public static string GetSHSL(string SqlString)
{
DataSet dataset = GetDataSet(SqlString);
if (dataset.Tables[].Rows.Count > )
{
return Convert.ToString(dataset.Tables[].Rows[][].ToString());
}
else
{
return "";
}
}
// 公有方法，获取数据，返回首行首列的INT值。
public static string GetSHSLInt(string SqlString)
{
DataSet dataset = GetDataSet(SqlString);
if (dataset.Tables[].Rows.Count > )
{
return Convert.ToString(dataset.Tables[].Rows[][].ToString());
}
else
{
return "";
}
}
// 公有方法，获取数据，返回一个DataRow。
public static DataRow GetDataRow(string SqlString)
{
DataSet dataset = GetDataSet(SqlString);
if (dataset.Tables[].Rows.Count > )
{
return dataset.Tables[].Rows[];
}
else
{
return null;
}
}
// 公有方法，执行Sql语句。对Update、Insert、Delete为影响到的行数，其他情况为-1
public static int ExecuteSQL(String SqlString, Hashtable MyHashTb)
{
int count = -;
Open();
try
{
SqlCommand cmd = new SqlCommand(SqlString, Connection);
foreach (DictionaryEntry item in MyHashTb)
{
string[] CanShu = item.Key.ToString().Split('|');
if (CanShu[].ToString().Trim() == "string")
{
cmd.Parameters.Add(CanShu[], SqlDbType.VarChar);
}
else if (CanShu[].ToString().Trim() == "int")
{
cmd.Parameters.Add(CanShu[], SqlDbType.Int);
}
else if (CanShu[].ToString().Trim() == "text")
{
cmd.Parameters.Add(CanShu[], SqlDbType.Text);
}
else if (CanShu[].ToString().Trim() == "datetime")
{
cmd.Parameters.Add(CanShu[], SqlDbType.DateTime);
}
else
{
cmd.Parameters.Add(CanShu[], SqlDbType.VarChar);
}
cmd.Parameters[CanShu[]].Value = item.Value.ToString();
}
count = cmd.ExecuteNonQuery();
}
catch
{
count = -;
}
finally
{
Close();
}
return count;
}
// 公有方法，执行Sql语句。对Update、Insert、Delete为影响到的行数，其他情况为-1
public static int ExecuteSQL(String SqlString)
{
int count = -;
Open();
try
{
SqlCommand cmd = new SqlCommand(SqlString, Connection);
count = cmd.ExecuteNonQuery();
}
catch
{
count = -;
}
finally
{
Close();
}
return count;
}
// 公有方法，执行一组Sql语句。返回是否成功,采用事务管理，发现异常时回滚数据
public static bool ExecuteSQL(string[] SqlStrings)
{
bool success = true;
Open();
SqlCommand cmd = new SqlCommand();
SqlTransaction trans = Connection.BeginTransaction();
cmd.Connection = Connection;
cmd.Transaction = trans;
try
{
foreach (string str in SqlStrings)
{
cmd.CommandText = str;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
success = false;
trans.Rollback();
}
finally
{
Close();
}
return success;
}
// Trans
public static bool ExecuteSqlByTrans(List<SqlAndPrams> list)
{
bool success = true;
Open();
SqlCommand cmd = new SqlCommand();
SqlTransaction trans = Connection.BeginTransaction();
cmd.Connection = Connection;
cmd.Transaction = trans;
try
{
foreach (SqlAndPrams item in list)
{
if (item.cmdParms==null)
{
cmd.CommandText = item.sql;
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = item.sql;
cmd.CommandType = CommandType.Text;//cmdType;
cmd.Parameters.Clear();
foreach (SqlParameter parameter in item.cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch(Exception e)
{
success = false;
trans.Rollback();
}
finally
{
Close();
}
return success;
}
// 执行一条计算查询结果语句，返回查询结果（object）。
public static object GetSingle(string SQLString)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
public static object GetSingle(string SQLString, int Times)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}
// 执行SQL语句，返回影响的记录数
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
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;
}
}
}
}
//执行查询语句，返回DataSet
public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
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;
}
}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State == ConnectionState.Open)
conn.Close();
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);
}
}
}
#region 执行存储过程 Add by LQB 2014-12-18
public static object RunProcedure(string storedProcName, IDataParameter[] paramenters)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, paramenters);
object obj = command.ExecuteNonQuery();
//object obj = command.Parameters["@Output_Value"].Value; //@Output_Value和具体的存储过程参数对应
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
}
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
return command;
}
#endregion
}
}
