public static class SqlHelper
{
#region 获取连接字符串属性
private static string _connectionStringInner = string.Empty;
public static string APPStartupPath = string.Empty;
public static string ConnectionString
{
get
{
if (_connectionStringInner == string.Empty)
{
try
{
SqlConnInfo sqlConnInfo = new SqlConnInfo();
string strConn = GetConnectionString(sqlConnInfo);
if (strConn == "")
throw new Exception("Not configured database connection string");
_connectionStringInner = strConn;
}
catch (Exception)
{
throw new Exception("Not configured database connection string");
}
}
return _connectionStringInner;
}
set
{
_connectionStringInner = value;
}
}
public static void MainDBLost()
{
_connectionStringInner = ConfigurationManager.ConnectionStrings["StrConn3"].ToString();
string table = ConfigurationManager.ConnectionStrings["db"].ToString();
string cmd = "ALTER DATABASE " + table + " SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS";
ExecuteSql(cmd);
_connectionStringInner = ConfigurationManager.ConnectionStrings["StrConn2"].ToString();
}
public static void MainDBRestore()
{
_connectionStringInner = ConfigurationManager.ConnectionStrings["StrConn3"].ToString();
string table = ConfigurationManager.ConnectionStrings["db"].ToString();
string cmd = "ALTER DATABASE " + table + " SET PARTNER RESUME";
string cmd2 = "ALTER DATABASE " + table + " SET PARTNER FAILOVER";
ExecuteSql(cmd);
ExecuteSql(cmd2);
_connectionStringInner = ConfigurationManager.ConnectionStrings["StrConn"].ToString();
}
public static bool CheckDBState()
{
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("select getdate()"))
{
cmd.Connection = connection;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
return true;
}
}
}
catch
{
return false;
}
}
#endregion
public static bool ExecuteSql(string sqlString, params SqlParameter[] cmdParms)
{
if (!DbMonitor.IsOk)
return false;
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, connection, null, CommandType.Text, sqlString, cmdParms);
int res = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return res > 0;
}
}
}
catch
{
return false;
}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (!DbMonitor.IsOk)
return ;
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 parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
private static DataTable ExecuteSearch(SqlCommand comm, params SqlParameter[] p)
{
DataTable dt = new DataTable();
if (!DbMonitor.IsOk)
return dt;
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
if (p != null && p.Length > 0)
{
foreach (SqlParameter parameter in p)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
comm.Parameters.Add(parameter);
}
}
comm.Connection = conn;
using (SqlDataAdapter da = new SqlDataAdapter(comm))
{
da.Fill(dt);
}
comm.Parameters.Clear();
comm.Dispose();
}
return dt;
}
public static DataTable ExecuteSearch(SqlCommand comm)
{
if (!DbMonitor.IsOk)
return null;
DataTable result = new DataTable();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
comm.Connection = conn;
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(result);
}
return result;
}
public static object ExecuteScalar(string sql, params SqlParameter[] p)
{
if (!DbMonitor.IsOk)
return null;
object obj = null;
SqlCommand comm = new SqlCommand();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
if (p != null && p.Length > 0)
{
foreach (SqlParameter item in p)
comm.Parameters.Add(item);
}
comm.Connection = conn;
conn.Open();
comm.CommandText = sql;
obj = comm.ExecuteScalar();
conn.Close();
}
return obj;
}
public static bool AddTransForList(List<string> sql, List<SqlParameter[]> para)
{
if (!DbMonitor.IsOk)
return false;
bool result = false;
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
SqlTransaction transaction = null;
try
{
conn.Open();
transaction = conn.BeginTransaction();
int totalCount = 0;
if (sql != null && sql.Count > 0)
{
int userId = -1;
using (SqlCommand comm = new SqlCommand(sql[0]))
{
comm.Connection = conn;
comm.Transaction = transaction;
comm.Parameters.Clear();
if (para != null && para.Count > 0)
{
foreach (SqlParameter parameter in para[0])
{
if ((parameter.Direction == ParameterDirection.InputOutput ||
parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
comm.Parameters.Add(parameter);
}
}
totalCount += comm.ExecuteNonQuery();
string tempSql = "select @@identity";
comm.CommandText = tempSql;
userId = Convert.ToInt32(comm.ExecuteScalar());
}
if (userId != -1)
{
for (int i = 1; i < sql.Count; i++)
{
if (!string.IsNullOrEmpty(sql[i]))
{
SqlCommand comm = new SqlCommand(sql[i]);
comm.Connection = conn;
comm.Transaction = transaction;
comm.Parameters.Clear();
if (para != null && para[i] != null)
{
foreach (SqlParameter sqlParameterse in para[i])
{
if ((sqlParameterse.Direction == ParameterDirection.InputOutput ||
sqlParameterse.Direction == ParameterDirection.Input) &&
(sqlParameterse.Value == null))
{
sqlParameterse.Value = DBNull.Value;
}
comm.Parameters.Add(sqlParameterse);
}
}
comm.Parameters.Add(new SqlParameter("@TempID", userId));
totalCount += comm.ExecuteNonQuery();
}
}
}
}
transaction.Commit();
result = true;
}
catch (Exception e)
{
if (transaction != null)
{
transaction.Rollback();
}
throw e;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
return result;
}
public static bool ExecuteTrans(List<string> sqlList, List<SqlParameter[]> paraList)
{
if (!DbMonitor.IsOk)
return false;
bool isSucc = false;
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
SqlTransaction transaction = null;
cmd.Connection = conn;
try
{
conn.Open();
transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
for (int i = 0; i < sqlList.Count; i++)
{
cmd.CommandText = sqlList[i];
if (paraList != null && paraList[i] != null)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(paraList[i]);
}
cmd.ExecuteNonQuery();
}
transaction.Commit();
isSucc = true;
}
catch (Exception e)
{
try { transaction.Rollback(); }
catch { }
throw e;
}
}
}
return isSucc;
}
public static DataTable ExecuteStoredProcedure(string storedProcedureName, params SqlParameter[] p)
{
SqlCommand comm = new SqlCommand();
comm.CommandText = storedProcedureName;
comm.CommandType = CommandType.StoredProcedure;
return ExecuteSearch(comm, p);
}
public static DataTable ExecuteSqlToTable(string sql, params SqlParameter[] p)
{
SqlCommand comm = new SqlCommand();
comm.CommandText = sql;
return ExecuteSearch(comm, p);
}
public static DataTable ExecuteSqlToTable(string sql)
{
SqlCommand comm = new SqlCommand();
comm.CommandText = sql; //SQL语句
return ExecuteSearch(comm, null);
}
public static DataTable CreateSqlByPageExcuteSql(string Sql, int PageIndex, int PageSize, string OrderBy, SqlParameter[] Paras, ref int TotalCount)
{
StringBuilder sbSql = new StringBuilder();
if (PageIndex == 1)
sbSql.Append("SELECT TOP " + PageSize + " * FROM");
else
sbSql.Append("SELECT * FROM ");
sbSql.Append(" ( SELECT ROW_NUMBER() OVER (ORDER BY " + OrderBy + ") as RowNumber,tempTable.*");
sbSql.Append(" FROM ( " + Sql + " ) AS tempTable ) AS tmp ");
if (PageIndex != 1)
sbSql.Append("WHERE RowNumber BETWEEN CONVERT(varchar,(@PageIndex-1)*@PageSize+1) AND CONVERT(varchar,(@PageIndex-1)*@PageSize+@PageSize) ");
sbSql.Append(" SELECT @TotalRecord=count(*) from (" + Sql + ") tempTable");
/*重新构造SqlParameter*/
int index = 0;
int Length = 0;
SqlParameter[] SqlParas;
if (Paras != null && Paras.Length > 0)
{
Length = Paras.Length;
SqlParas = new SqlParameter[Length + 3];
for (int i = 0; i < Paras.Length; i++)
{
SqlParas[i] = Paras[i];
index++;
}
}
else
SqlParas = new SqlParameter[Length + 3];
/*将分页参数追加至SqlParameter*/
SqlParas[index] = new SqlParameter("@PageIndex", SqlDbType.Int);
SqlParas[index].Value = PageIndex;
index++;
SqlParas[index] = new SqlParameter("@PageSize", SqlDbType.Int);
SqlParas[index].Value = PageSize;
index++;
SqlParas[index] = new SqlParameter("@TotalRecord", SqlDbType.Int);
SqlParas[index].Direction = ParameterDirection.Output;
DataTable dtTemp = ExecuteSqlToTable(sbSql.ToString(), SqlParas);
TotalCount = (int)SqlParas[index].Value;
return dtTemp;
}
public static DataTable CreateSqlByPageExcuteSqlArr(string Sql, int PageIndex, int PageSize, string OrderBy, ArrayList paramList, ref int TotalCount)
{
StringBuilder sbSql = new StringBuilder();
if (PageIndex == 1)
sbSql.Append("SELECT TOP " + PageSize + " * FROM");
else
sbSql.Append("SELECT * FROM ");
sbSql.Append(" ( SELECT ROW_NUMBER() OVER (ORDER BY " + OrderBy + ") as RowNumber,tempTable.*");
sbSql.Append(" FROM ( " + Sql + " ) AS tempTable ) AS tmp ");
if (PageIndex != 1)
sbSql.Append("WHERE RowNumber BETWEEN CONVERT(varchar,(@PageIndex-1)*@PageSize+1) AND CONVERT(varchar,(@PageIndex-1)*@PageSize+@PageSize) ");
sbSql.Append(" SELECT @TotalRecord=count(*) from (" + Sql + ") tempTable");
/*重新构造SqlParameter*/
int index = 0;
int Length = 0;
SqlParameter[] SqlParas;
if (paramList != null && paramList.Count > 0)
{
SqlParas = new SqlParameter[paramList.Count + 3];
for (int i = 0; i < paramList.Count; i++)
{
SqlParas[i] = (SqlParameter)paramList[i];
index++;
}
}
else
SqlParas = new SqlParameter[Length + 3];
/*将分页参数追加至SqlParameter*/
SqlParas[index] = new SqlParameter("@PageIndex", SqlDbType.Int);
SqlParas[index].Value = PageIndex;
index++;
SqlParas[index] = new SqlParameter("@PageSize", SqlDbType.Int);
SqlParas[index].Value = PageSize;
index++;
SqlParas[index] = new SqlParameter("@TotalRecord", SqlDbType.Int);
SqlParas[index].Direction = ParameterDirection.Output;
DataTable dtTemp = ExecuteSqlToTable(sbSql.ToString(), SqlParas);
TotalCount = (int)SqlParas[index].Value;
return dtTemp;
}
public static DataTable PagerWithCommand(SqlCommand cmd, int PageIndex, int PageSize, string OrderBy, ref int TotalCount)
{
//变量定义
StringBuilder sbSql = new StringBuilder();
//第一页时
if (PageIndex == 1)
sbSql.Append("SELECT TOP " + PageSize + " * FROM");
else
sbSql.Append("SELECT * FROM ");
sbSql.Append(" ( SELECT ROW_NUMBER() OVER (ORDER BY " + OrderBy + ") as RowNumber,tempTable.*");
sbSql.Append(" FROM ( " + cmd.CommandText + " ) AS tempTable ) AS tmp ");
if (PageIndex != 1)
sbSql.Append("WHERE RowNumber BETWEEN CONVERT(varchar,(@PageIndex-1)*@PageSize+1) AND CONVERT(varchar,(@PageIndex-1)*@PageSize+@PageSize) ");
sbSql.Append("; SELECT @TotalRecord = count(*) FROM (" + cmd.CommandText + ") tempTable");
//重新设置命令SQL语句
cmd.CommandText = sbSql.ToString();
/* 将分页参数追加至SqlParameter */
//当前页
SqlParameter param = new SqlParameter("@PageIndex", SqlDbType.Int);
param.Value = PageIndex;
cmd.Parameters.Add(param);
//每页显示数
param = new SqlParameter("@PageSize", SqlDbType.Int);
param.Value = PageSize;
cmd.Parameters.Add(param);
//总数
param = new SqlParameter("@TotalRecord", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
DataTable dtTemp = ExecuteSearch(cmd);
TotalCount = (int)cmd.Parameters["@TotalRecord"].Value;
return dtTemp;
}
//private static string GetConnectionString()
//{
// Assembly ass;
// Type type;
// string strConn = "";
// try
// {
// //路径
// //string filePath_old = System.Environment.CurrentDirectory + @"\数据库配置.exe";
// string filePath = APPStartupPath + @"\数据库配置.exe";
// //Debug.WriteLine(string.Format("原先获取的当前工作目录的完全限定路径：{0}", filePath_old));
// //Debug.WriteLine(string.Format("修改后的启动应用程序的可执行文件路径：{0}", filePath));
// ass = Assembly.LoadFrom(filePath);
// type = ass.GetType("TH_SqlConnectionConfig.SqlConnectionInfo");
// MethodInfo Method = type.GetMethod("GetConnectionString");
// strConn = Convert.ToString(Method.Invoke(Activator.CreateInstance(type), new Object[] { }));
// }
// catch (Exception)
// {
// return "";
// }
// return strConn;
//}
/// <summary>
/// 读取配置
/// </summary>
public static bool ReadConfig(SqlConnInfo scInfo)
{
if (!File.Exists(scInfo.ConfigPath))
return false;
try
{
XDocument xd = XDocument.Load(scInfo.ConfigPath);
XElement root = xd.Element("Root");
XElement xe = root.Element("SqlConfig");
scInfo.MainDbIp = xe.Attribute("MainDbIp").Value;
scInfo.MirrorDbIp = xe.Attribute("MirrorDbIp").Value;
scInfo.DbName = xe.Attribute("DbName").Value;
scInfo.UserName = xe.Attribute("UserName").Value;
scInfo.Pwd = Security.Base64Decrypt(xe.Attribute("Pwd").Value);
return true;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 获取连接字符串
/// </summary>
/// <returns></returns>
public static string GetConnectionString(SqlConnInfo scInfo, bool readXml = true)
{
string strConn = "";
if (readXml && !ReadConfig(scInfo))
return strConn;
strConn = string.Format("Data Source={0};Failover Partner ={1};Initial Catalog={2};User ID={3};Password={4};Connect Timeout=10",
scInfo.MainDbIp, scInfo.MirrorDbIp, scInfo.DbName, scInfo.UserName, scInfo.Pwd);
return strConn;
}
}
strConn = string.Format("Data Source={0};Failover Partner ={1};Initial Catalog={2};User ID={3};Password={4};Connect Timeout=10"
扫一扫
669
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
