using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Transactions; namespace Boss.DAL
{
/// <summary>
/// 数据库的通用访问类
/// 此类为抽象类，不允许实例化，在应用时直接调用
/// </summary>
public abstract class SqlHelperEx
{
#region 数据操作 /// <summary>
/// 打开数据库链接
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
public static SqlConnection ConnOpen(string conn)
{
try
{
SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings[conn].ConnectionString);
myConn.Open();
return myConn;
}
catch (SqlException ex)
{
throw ex;
}
} /// <summary>
/// 关闭数据库链接
/// </summary>
/// <param name="myConn">数据库连接对象SqlConnection</param>
public static void ConnClose(SqlConnection myConn)
{
try
{ if (myConn.State == ConnectionState.Open)
{
myConn.Close();
myConn.Dispose();
}
}
catch (SqlException ex)
{
throw ex;
}
} /// <summary>
/// 获取SqlDataReader 带参
/// </summary>
/// <param name="strSql">Sql语句</param>
/// <param name="myConn">SqlConnection</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string strSql, SqlConnection myConn, params SqlParameter[] cmdParms)
{
try
{
SqlDataReader myDr;
SqlCommand myComm = GetSqlCommand(strSql, myConn, cmdParms);
myDr = myComm.ExecuteReader();
myComm.Dispose();
return myDr;
}
catch (SqlException ex)
{
throw ex;
}
} /// <summary>
/// 获取SqlDataReader 无参
/// </summary>
/// <param name="strSql">Sql语句</param>
/// <param name="myConn">SqlConnection</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string strSql, SqlConnection myConn)
{
try
{
SqlDataReader myDr;
SqlCommand myComm = GetSqlCommand(strSql, myConn, null);
myDr = myComm.ExecuteReader();
myComm.Dispose();
return myDr;
}
catch (SqlException ex)
{
throw ex;
}
} /// <summary>
/// 执行sql语句（有参数）
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="cmdParms">参数</param>
/// <returns>返回影响行数</returns>
public static int ExecuteNonQuery(string strSql, SqlConnection myConn, params SqlParameter[] cmdParms)
{
int Result = -;
try
{
SqlCommand myComm = GetSqlCommand(strSql, myConn, cmdParms);
Result = myComm.ExecuteNonQuery();
myComm.Dispose();
WriteLog(strSql, cmdParms);
}
catch (SqlException ex)
{
throw ex;
}
return Result;
}
/// <summary>
/// 执行sql语句（无参数）
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="cmdParms">参数</param>
/// <returns>返回影响行数</returns>
public static int ExecuteNonQuery(string strSql, SqlConnection myConn)
{
int Result = -;
try
{
SqlCommand myComm = GetSqlCommand(strSql, myConn, null);
Result = myComm.ExecuteNonQuery();
myComm.Dispose();
WriteLog(strSql);
}
catch (SqlException ex)
{
throw ex;
}
return Result;
} public static int ExecuteNonQueryLog(string strSql, SqlConnection myConn, params SqlParameter[] cmdParms)
{
int Result = -;
try
{
SqlCommand myComm = GetSqlCommand(strSql, myConn, cmdParms);
Result = myComm.ExecuteNonQuery();
myComm.Dispose();
}
catch (SqlException ex)
{
throw ex;
}
return Result;
} /// <summary>
/// 获取DataSet 带参
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="myConn">数据库连接</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSet(string strSql, SqlConnection myConn, params SqlParameter[] cmdParms)
{ try
{
DataSet ds = new DataSet();
SqlCommand sqlCommand = GetSqlCommand(strSql, myConn, cmdParms);
SqlDataAdapter adpt = new SqlDataAdapter(sqlCommand);
adpt.Fill(ds);
adpt.Dispose();
return ds;
}
catch (SqlException ex)
{
throw ex;
}
} /// <summary>
/// 获取DataSet 无参
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="myConn">数据库连接</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSet(string strSql, SqlConnection myConn)
{ try
{
DataSet ds = new DataSet();
SqlCommand sqlCommand = GetSqlCommand(strSql, myConn, null);
SqlDataAdapter adpt = new SqlDataAdapter(sqlCommand);
adpt.Fill(ds);
adpt.Dispose();
return ds;
}
catch (SqlException ex)
{
throw ex;
}
} /// <summary>
/// 执行SQL，返回结果集中第一行第一列(带参数)
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>object</returns>
public static object ExecuteScalar(string strSql, SqlConnection myConn, params SqlParameter[] cmdParms)
{
try
{
SqlCommand sqlCommand = GetSqlCommand(strSql, myConn, cmdParms);
object flag = sqlCommand.ExecuteScalar();
return flag;
}
catch (SqlException ex)
{
throw ex;
}
} /// <summary>
/// 执行SQL，返回结果集中第一行第一列(不带参数)
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>object</returns>
public static object ExecuteScalar(string strSql, SqlConnection myConn)
{
try
{
SqlCommand sqlCommand = GetSqlCommand(strSql, myConn, null);
object flag = sqlCommand.ExecuteScalar();
return flag;
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 获取DataTable带参
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="myConn">数据库连接</param>
/// <returns>DataSet</returns>
public static DataTable GetDataTable(string strSql, SqlConnection myConn, params SqlParameter[] cmdParms)
{
try
{
DataTable dt = new DataTable();
SqlCommand sqlCommand = new SqlCommand(); sqlCommand = GetSqlCommand(strSql, myConn, cmdParms);
SqlDataAdapter adpt = new SqlDataAdapter(sqlCommand);
adpt.Fill(dt);
adpt.Dispose();
return dt;
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 获取DataTable 无参
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="myConn">数据库连接</param>
/// <returns>DataSet</returns>
public static DataTable GetDataTable(string strSql, SqlConnection myConn)
{
try
{
DataTable dt = new DataTable();
SqlCommand sqlCommand = new SqlCommand(); sqlCommand = GetSqlCommand(strSql, myConn, null);
SqlDataAdapter adpt = new SqlDataAdapter(sqlCommand);
adpt.Fill(dt);
adpt.Dispose();
return dt;
}
catch (SqlException ex)
{
throw ex;
}
} private static SqlCommand GetSqlCommand(string strSql, SqlConnection myConn, params SqlParameter[] cmdParms)
{
try
{
SqlCommand myComm = new SqlCommand();
//设置数据库连接
myComm.Connection = myConn;
//设置Sql语句
myComm.CommandText = strSql;
//设置Sql执行方式
myComm.CommandType = CommandType.Text;
//等待时间
myComm.CommandTimeout = ; if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
myComm.Parameters.Add(parameter);
}
}
return myComm;
}
catch (SqlException ex)
{
throw ex;
}
} public static void WriteLog(string sql, params SqlParameter[] cmdParms)
{
string straction = "";
string strtablename = "";
string strsql = sql.Trim(); if (strsql.ToLower().Trim().IndexOf("insert") == )
{
straction = "insert";
strtablename = sql.Trim().ToLower().Replace("insert", "").Trim().Replace("into", "").Trim().Split('(')[];
}
else if (strsql.ToLower().Trim().IndexOf("update") == )
{
straction = "update";
strtablename = sql.Trim().ToLower().Replace("update", "").Trim().Split(' ')[];
} if (cmdParms != null)
{
for (int i = ; i < cmdParms.Length; i++)
{
strsql = strsql.Replace(cmdParms[i].ParameterName, "'" + cmdParms [i].Value+ "'");
}
} SqlConnection conndef = ConnOpen("Default");
//写日志
int ilog = Convert.ToInt32(CommonClass.InsertSysLog(straction, "表：" + strtablename, strsql, conndef));//调用执行写系统日志
ConnClose(conndef);
} public static void WriteLog(string sql)
{
string straction = "";
string strtablename = "";
string strsql = sql.Trim(); if (strsql.ToLower().Trim().IndexOf("insert") == )
{
straction = "insert";
strtablename = sql.Trim().ToLower().Replace("insert", "").Trim().Replace("into", "").Trim().Split('(')[];
}
else if (strsql.ToLower().Trim().IndexOf("update") == )
{
straction = "update";
strtablename = sql.Trim().ToLower().Replace("update", "").Trim().Split(' ')[];
} SqlConnection conndef = ConnOpen("Default");
//写日志
int ilog = Convert.ToInt32(CommonClass.InsertSysLog(straction, "表：" + strtablename, strsql, conndef));//调用执行写系统日志
ConnClose(conndef);
} #endregion #region 存储过程 /// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataTable RunProcedure(SqlConnection conn, string storedProcName, IDataParameter[] parameters)
{
try
{
DataSet dataSet = new DataSet();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(conn, storedProcName, parameters);
sqlDA.Fill(dataSet);
return dataSet.Tables[];
}
catch (Exception ex)
{ return null;
}
} /// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
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 #region 事物操作
/// <summary>
/// 跨库事物处理
/// </summary>
/// <param name="SqlList"></param>
/// <returns></returns>
public static int ExecuteSqlTranEx(IList<CommandInfo> SqlList)
{
int result = ;
try
{
using (TransactionScope scope = new TransactionScope())
{
for (int i = ; i < SqlList.Count; i++)
{
SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings[SqlList[i].ConnStr].ConnectionString);
using (myConn)
{
myConn.Open();
SqlCommand myComm = GetSqlCommand(SqlList[i].Sql, myConn, SqlList[i].Parameters);
int r = myComm.ExecuteNonQuery();
result += ;
myConn.Dispose();
myConn.Close(); }
scope.Complete(); //提交事物
}
}
}
catch (Exception ex) //发生异常后自动回滚
{
//throw;
}
return result;
} /// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的IList,Sql为CommandInfo.Sql,SqlParameter为CommandInfo.Parameters</param>
public static int ExecuteSqlTran(IList<CommandInfo> SqlList, SqlConnection SqlDrConn)
{ //当连接处于打开状态时关闭,然后再打开,避免有时候数据不能及时更新
if (SqlDrConn.State == ConnectionState.Open)
{
SqlDrConn.Close();
}
SqlDrConn.Open();
using (SqlTransaction trans = SqlDrConn.BeginTransaction())
{
SqlCommand sqlCommand = new SqlCommand();
try
{
//受影响总条数
int count = ;
//循环
foreach (CommandInfo myDE in SqlList)
{
string sql = myDE.Sql;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
sqlCommand = GetSqlCommand(sql, SqlDrConn, cmdParms);
sqlCommand.Transaction = trans;
int val = sqlCommand.ExecuteNonQuery();
if (val == )
{
trans.Rollback();
return ;
}
count += val;
sqlCommand.Parameters.Clear();
}
trans.Commit();
return count;
}
catch
{
trans.Rollback();
throw;
}
}
}
#endregion }
public class CommandInfo
{
public CommandInfo(string strSql, SqlParameter[] cmdParameters, string strconn)
{
this.Sql = strSql;
this.Parameters = cmdParameters;
this.ConnStr = strconn;
}
public CommandInfo(string strSql, SqlParameter[] cmdParameters)
{
this.Sql = strSql;
this.Parameters = cmdParameters;
}
/// <summary>
/// Sql语句
/// </summary>
public string Sql { set; get; }
/// <summary>
/// Sql参数
/// </summary>
public SqlParameter[] Parameters { set; get; }
/// <summary>
/// 数据库链接字符串 说明 跨库事务执行时使用
/// </summary>
public string ConnStr { set; get; }
}
}
-->
<configuration>
<connectionStrings>
<clear/>
<!--开发库-->
<add name="Default" connectionString="Data Source=211.149.217.181;Initial Catalog=DevFotileEcBossDB;User ID=sa;Password=ft!@12" providerName="System.Data.SqlClient"/>
<add name="MemberDB" connectionString="Data Source=211.149.217.181;Initial Catalog=DevMemberDB;User ID=sa;Password=ft!@12" providerName="System.Data.SqlClient"/>
<add name="WEBDB" connectionString="Data Source=211.149.217.181;Initial Catalog=DevWebDB;User ID=sa;Password=ft!@12" providerName="System.Data.SqlClient"/>
<add name="webadDB" connectionString="Data Source=211.149.217.181;Initial Catalog=ECwebadDB;User ID=sa;Password=ft!@12" providerName="System.Data.SqlClient"/>
<add name="CRMDB" connectionString="Data Source=211.149.217.181;Initial Catalog=ECCRMDB;User ID=sa;Password=ft!@12" providerName="System.Data.SqlClient"/>
<add name="ECSMS" connectionString="Data Source=211.149.217.181;Initial Catalog=ftSMS;User ID=sa;Password=ft!@12" providerName="System.Data.SqlClient"/>
<add name="CIOKMDB" connectionString="Data Source=211.149.217.181;Initial Catalog=CIOKMDB;User ID=sa;Password=ft!@12" providerName="System.Data.SqlClient"/>
<add name="SPSDB" connectionString="Data Source=211.149.217.181;Initial Catalog=DevSNSResourceDB;User ID=sa;Password=ft!@12" providerName="System.Data.SqlClient"/>
<add name="LOGDB" connectionString="Data Source=211.149.217.181;Initial Catalog=DevFotileEcBossDB;User ID=sa;Password=ft!@12" providerName="System.Data.SqlClient"/>
<add name="WDGJDB" connectionString="Data Source=211.149.197.100;Initial Catalog=WDGJDB;User ID=readonlysa;Password=ft!@12" providerName="System.Data.SqlClient"/>
</connectionStrings>
<appSettings>
<add key="authorizedSequence" value="2c9f62803bcc4478bb3fe6ea227cb7d3"/>
</appSettings>