public DataTable RunDirectSQL(string strLoginName, string strSQL,
out string ErrorMsg, out bool bolIsError)
{
DataTable dtReturn = null;
bolIsError = false;
ErrorMsg = "";
try
{
string strConn = GetConnectionStr();
SQLServerDAL.GetConnectionString = strConn;
if (DBHelper.DBType == 0)
{
strSQL = strSQL.Replace("isnull(", "ifnull(");
}
dtReturn = SQLServerDAL.Query(strSQL);
dtReturn.TableName = "Result";
}
catch (Exception ex)
{
ErrorMsg = ex.InnerException.Message;
bolIsError = true;
}
return(dtReturn);
}
private static void ThreadBackUp()
{
while (true)
{
try
{
lock (mlstBackTask)
{
if (mlstBackTask.Count > 0)//有备份任务，执行备份
{
foreach (BackUpTask task in mlstBackTask)
{
BackUpDB(task);
string strSQL = @"insert dbo.DbBackUpLog( BackUpConfigID, BackUpTime, BackUpFileName)
values({0},getdate(),'{1}')";
strSQL = string.Format(strSQL, task.BackUpConfigID, task.BackUpFileName);
DataTable dtBackUpConfig = SQLServerDAL.Query(strSQL);
}
mlstBackTask.Clear();
}
}
Thread.Sleep(1000 * 60 * 5);
}
catch (Exception ex)
{
}
}
}
public string RunDirectSQL(long SessionID, bool IsNeedSession, string strLoginName, string strSQL,
out string ErrorMsg, out bool bolIsError)
{
DataTable dtReturn = null;
bolIsError = false;
ErrorMsg = "";
try
{
string strConn = GetConnectionStr();
SQLServerDAL.GetConnectionString = strConn;
dtReturn = SQLServerDAL.Query(strSQL);
dtReturn.TableName = "Result";
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
ErrorMsg = ex.InnerException.Message;
}
else
{
ErrorMsg = ex.Message;
}
bolIsError = true;
return("");
}
return(RarDataTable(dtReturn));
}
private void Init()
{
bool isEncrypt = true;
string conn = string.Empty;
string connEncrypt = Utility.Config.GetAppSettings("ConnEncrypt");
if (!string.IsNullOrEmpty(connEncrypt) && bool.TryParse(connEncrypt, out isEncrypt))
{
isEncrypt = (isEncrypt == true);
}
Sql = new SQLServerDAL();
Sql.IsConnEncrypt(isEncrypt);
if (string.IsNullOrEmpty(Utility.Config.GetAppSettings("ConnectionString")))
{
if (Utility.DataCache.Get("ConnectionString") == null ||
Convert.ToString(Utility.DataCache.Get("ConnectionString")) == "非法访问")
{
conn = Convert.ToString(Utility.DataCache.Get("ConnectionString"));
}
Sql.ConnectionString = conn;
}
this.ConnectionString = Sql.ConnectionString;
}
public DataTable RunView(int iViewType, string strLoginName, string strFieldNames, string strWhere, string strOrderBy,
out string ErrorMsg, out bool bolIsError)
{
VerifyEncrypt();
LogHelper.WriteLog("开始RunView：" + iViewType.ToString());
DataTable dtReturn = null;
bolIsError = false;
ErrorMsg = "";
//BackUpHelper.StartBackUp(AppDomain.CurrentDomain.BaseDirectory);
//LogHelper.WriteLog("正在调用" + iViewType.ToString());
try
{
string strConn = GetConnectionStr();
SQLServerDAL.GetConnectionString = strConn;
//LogHelper.WriteLog(SQLServerDAL.GetConnectionString);
DataTable dtView = SQLServerDAL.Query("select * from dbo.SysViewType where SysViewType=" + iViewType);
//LogHelper.WriteLog("查询语句成功！");
if (dtView.Rows.Count == 0)
{
//LogHelper.WriteLog("查询出错！视图号：【" + iViewType + "】不存在！");
throw new Exception("查询出错！视图号：【" + iViewType + "】不存在！");
}
//LogHelper.WriteLog("SysViewName");
string strSysViewName = dtView.Rows[0]["SysViewName"].ToString().TrimEnd();
//LogHelper.WriteLog(strSysViewName);
DataTable dtViewExists = SQLServerDAL.Query(@"
select * from sysobjects
where id = object_id(N'[" + strSysViewName + @"]')
");
if (dtViewExists.Rows.Count == 0)
{
throw new Exception("查询出错！视图名称：【" + strSysViewName + "】不存在！");
}
string strFields = string.IsNullOrEmpty(strFieldNames) ? "*" : strFieldNames;
//LogHelper.WriteLog(strFields);
strWhere = string.IsNullOrEmpty(strWhere) ? "" : "where " + strWhere;
strOrderBy = string.IsNullOrEmpty(strOrderBy) ? "" : "Order By " + strOrderBy;
string strSQL = @"
select {0}
from {1}
{2}
{3}
";
//LogHelper.WriteLog(strSQL);
strSQL = string.Format(strSQL, strFields, strSysViewName, strWhere, strOrderBy);
dtReturn = SQLServerDAL.Query(strSQL);
dtReturn.TableName = "Result";
}
catch (Exception ex)
{
ErrorMsg = ex.InnerException.Message;
//LogHelper.WriteLog(ErrorMsg);
bolIsError = true;
}
return(dtReturn);
}
public void Add(Employee objEmpolyee)
{
if (true)
{
SQLServerDAL objDAL = new SQLServerDAL();
objDAL.Add();
}
else
{
MysqlDAL objDAL = new MysqlDAL();
objDAL.Add();
}
}
private static void BackUpDB(BackUpTask task)
{
if (!Directory.Exists(mFilePath))
{
Directory.CreateDirectory(mFilePath);
}
string strSQL = @" select BackUpLogID,BackUpFileName
from dbo.DbBackUpLog
where BackUpConfigID={0}
order by BackUpLogID asc";
strSQL = string.Format(strSQL, task.BackUpConfigID, task.BackUpFileName);
DataTable dtBackUpLog = SQLServerDAL.Query(strSQL);
if (dtBackUpLog.Rows.Count >= task.BackUpFileMaxNum)//备份数大于最大数量时，先删除最旧的备份帐套
{
DataRow dr = dtBackUpLog.Rows[0];
string strBakName = dr["BackUpFileName"].ToString().TrimEnd();
long lBackUpLogID = LBConverter.ToInt64(dr["BackUpLogID"]);
string strBakFileName = Path.Combine(mFilePath, strBakName);
if (File.Exists(strBakFileName))
{
File.Delete(strBakFileName);
}
string strSQLDel = @"delete from dbo.DbBackUpLog where BackUpLogID=" + lBackUpLogID;
SQLServerDAL.Query(strSQLDel);
}
string strFileName = Path.Combine(mFilePath, task.BackUpFileName);
using (SqlConnection conn = new SqlConnection(SQLServerDAL.GetConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandTimeout = 1000 * 30 * 60;
cmd.CommandType = System.Data.CommandType.Text;
string strSQLBackUp = "backup database [{0}] to disk = '{1}'";
strSQLBackUp = string.Format(strSQLBackUp, conn.Database, strFileName);
cmd.CommandText = strSQLBackUp;
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public DataTable RunView(int iViewType, string strLoginName, string strFieldNames, string strWhere, string strOrderBy,
out string ErrorMsg, out bool bolIsError)
{
DataTable dtReturn = null;
bolIsError = false;
ErrorMsg = "";
try
{
DataTable dtView = SQLServerDAL.Query("select * from dbo.SysViewType where SysViewType=" + iViewType);
if (dtView.Rows.Count == 0)
{
throw new Exception("查询出错！视图号：【" + iViewType + "】不存在！");
}
string strSysViewName = dtView.Rows[0]["SysViewName"].ToString().TrimEnd();
DataTable dtViewExists = SQLServerDAL.Query(@"
select * from sysobjects
where id = object_id(N'[" + strSysViewName + @"]')
");
if (dtViewExists.Rows.Count == 0)
{
throw new Exception("查询出错！视图名称：【" + strSysViewName + "】不存在！");
}
string strFields = string.IsNullOrEmpty(strFieldNames) ? "*" : strFieldNames;
strWhere = string.IsNullOrEmpty(strWhere)?"":"where " + strWhere;
strOrderBy = string.IsNullOrEmpty(strOrderBy)?"":"Order By " + strOrderBy;
string strSQL = @"
select {0}
from {1}
{2}
{3}
";
strSQL = string.Format(strSQL, strFields, strSysViewName, strWhere, strOrderBy);
dtReturn = SQLServerDAL.Query(strSQL);
dtReturn.TableName = "Result";
}
catch (Exception ex)
{
ErrorMsg = ex.InnerException.Message;
bolIsError = true;
}
return(dtReturn);
}
private static void MTimer_Elapsed(object sender, ElapsedEventArgs e)
{
try
{
string strSQL = "select * from dbo.DbBackUpConfig where IsEffect=1";
DataTable dtBackUpConfig = SQLServerDAL.Query(strSQL);
//查询当日的备份清单
strSQL = "select * from dbo.DbBackUpLog where BackUpTime>='" + DateTime.Now.ToString("yyyy-MM-dd") + "'";
DataTable dtBackUpLog = SQLServerDAL.Query(strSQL);
DataView dvBackUpLog = new DataView(dtBackUpLog);
foreach (DataRow dr in dtBackUpConfig.Rows)
{
int iBackUpType = LBConverter.ToInt32(dr["BackUpType"]);
int iBackUpWeek = LBConverter.ToInt32(dr["BackUpWeek"]);
int iBackUpHour = LBConverter.ToInt32(dr["BackUpHour"]);
int iBackUpMinu = LBConverter.ToInt32(dr["BackUpMinu"]);
long lBackUpConfigID = LBConverter.ToInt64(dr["BackUpConfigID"]);
string strBackUpName = LBConverter.ToString(dr["BackUpName"]);
int iBackUpFileMaxNum = LBConverter.ToInt32(dr["BackUpFileMaxNum"]);
DateTime now = DateTime.Now;
int iWeek = (int)now.DayOfWeek;
int iHour = now.Hour;
int iMinu = now.Minute;
if (iBackUpType == 0)//每周备份一次
{
if (iWeek == iBackUpWeek)
{
if (iHour == iBackUpHour)
{
if (iMinu >= iBackUpMinu && iMinu <= iBackUpMinu + 29)
{
string strBackUpTimeFrom = DateTime.Now.ToString("yyyy-MM-dd ") + iBackUpHour + ":" + iBackUpMinu;
string strBackUpTimeTo = DateTime.Now.ToString("yyyy-MM-dd ") + iBackUpHour + ":" + (iBackUpMinu + 29);
dvBackUpLog.RowFilter = "BackUpConfigID=" + lBackUpConfigID +
" and BackUpTime>='" + strBackUpTimeFrom + "' and BackUpTime<='" + strBackUpTimeTo + "'";
if (dvBackUpLog.Count == 0)
{
lock (mlstBackTask)
{
bool bolIsAdd = true;
foreach (BackUpTask t in mlstBackTask)
{
if (t.BackUpConfigID == lBackUpConfigID)
{
bolIsAdd = false;
break;
}
}
if (bolIsAdd)
{
BackUpTask task = new BackUp.BackUpTask(lBackUpConfigID, strBackUpName, iBackUpFileMaxNum);
mlstBackTask.Add(task);
}
}
}
}
}
}
}
else
{
//每日备份一次
if (iHour == iBackUpHour)
{
if (iMinu >= iBackUpMinu && iMinu <= iBackUpMinu + 29)
{
string strBackUpTimeFrom = DateTime.Now.ToString("yyyy-MM-dd ") + iBackUpHour + ":" + iBackUpMinu;
string strBackUpTimeTo = DateTime.Now.ToString("yyyy-MM-dd ") + iBackUpHour + ":" + (iBackUpMinu + 29);
dvBackUpLog.RowFilter = "BackUpConfigID=" + lBackUpConfigID +
" and BackUpTime>='" + strBackUpTimeFrom + "' and BackUpTime<='" + strBackUpTimeTo + "'";
if (dvBackUpLog.Count == 0)
{
lock (mlstBackTask)
{
bool bolIsAdd = true;
foreach (BackUpTask t in mlstBackTask)
{
if (t.BackUpConfigID == lBackUpConfigID)
{
bolIsAdd = false;
break;
}
}
if (bolIsAdd)
{
BackUpTask task = new BackUp.BackUpTask(lBackUpConfigID, strBackUpName, iBackUpFileMaxNum);
mlstBackTask.Add(task);
}
}
}
}
}
}
}
}
catch (Exception ex)
{
}
}
public string RunProcedure(int ProcedureType, long SessionID, bool IsNeedSession, string strLoginName, byte[] bSerializeValue, byte[] bSerializeDataType,
out string strOut, out string ErrorMsg, out bool bolIsError)
{
strOut = "";
DataTable dtOut = null;
bolIsError = false;
DataSet dsReturn = null;
ErrorMsg = "";
try
{
string strConn = GetConnectionStr();
SQLServerDAL.GetConnectionString = strConn;
DataTable dtParmValue = new DataTable("SPIN");
List <Dictionary <object, object> > lstDictValue = DeserializeObject(bSerializeValue) as List <Dictionary <object, object> >;
Dictionary <object, object> dictDataType = DeserializeObject(bSerializeDataType) as Dictionary <object, object>;
foreach (KeyValuePair <object, object> keyvalue in dictDataType)
{
dtParmValue.Columns.Add(keyvalue.Key.ToString(), GetType(keyvalue.Value.ToString()));
}
foreach (Dictionary <object, object> dictValue in lstDictValue)
{
DataRow drNew = dtParmValue.NewRow();
foreach (KeyValuePair <object, object> keyvalue in dictValue)
{
drNew[keyvalue.Key.ToString()] = keyvalue.Value;
}
dtParmValue.Rows.Add(drNew);
}
dtParmValue.AcceptChanges();
DBHelper.Provider = new DBMSSQL();
SqlConnection con = new SqlConnection(SQLServerDAL.GetConnectionString);
string strDBName = con.Database;
DBMSSQL.InitSettings(5000, con.DataSource, strDBName, true, "", "");
con.Close();
//LBFactory factory = new LBFactory();
//IBLLFunction function = factory.GetAssemblyFunction(ProcedureType);
IBLLFunction function = DBHelper.GetFunctionMethod(ProcedureType);
if (function == null)
{
#region -- 调用存储过程 --
DataTable dtView = SQLServerDAL.Query("select * from dbo.SysSPType where SysSPType=" + ProcedureType);
if (dtView.Rows.Count > 0)
{
DataRow drView = dtView.Rows[0];
string strSysSPName = drView["SysSPName"].ToString().TrimEnd();
SQLServerDAL.ExecuteProcedure(strSysSPName, dtParmValue, out dtOut, out dsReturn);
}
else
{
throw new Exception("存储过程号【" + ProcedureType + "】不存在！");
}
#endregion
}
else
{
#region -- 调用中间层程序方法 --
string strMethod = function.GetFunctionName(ProcedureType);
string str = function.ToString();
string strLoad = str.Substring(0, str.IndexOf('.', 8));
Assembly s = Assembly.Load(strLoad);
Type tpe = s.GetType(str);
//调用GetName方法
MethodInfo method = tpe.GetMethod(strMethod);
Dictionary <string, string> dictParmType = new Dictionary <string, string>();
ParameterInfo[] parameterInfos = method.GetParameters();
foreach (ParameterInfo parmInfo in parameterInfos)
{
if (parmInfo.ParameterType.Name != "FactoryArgs")
{
string strParmTypeName = parmInfo.ParameterType.Name.Replace("&", "");
if (!dictParmType.ContainsKey(strParmTypeName))
{
dictParmType.Add(parmInfo.Name, strParmTypeName);
}
}
}
int iRowIndex = 0;
if (dtParmValue == null || dtParmValue.Rows.Count == 0)
{
return(null);
}
FactoryArgs factoryArgs = new FactoryArgs(strDBName, strLoginName, SessionID, IsNeedSession, null, null);
#region -- 校验Session --
if (factoryArgs.SessionID > 0)
{
bool bolExistsSession = SessionTimer.VerifySession(factoryArgs);
if (IsNeedSession && !bolExistsSession)
{
bolIsError = true;
ErrorMsg = "长时间未操作或者被其他人逼退，请重新登录！";
return("");
}
}
#endregion -- 校验Session --
foreach (DataRow drParmValue in dtParmValue.Rows)
{
//获取需要传入的参数
ParameterInfo[] parms = method.GetParameters();
Dictionary <int, string> dictOutFieldName = new Dictionary <int, string>();
object[] objValue = new object[parms.Length];
int iParmIndex = 0;
foreach (ParameterInfo ss in parms)
{
string strParmName = ss.Name;
if (ss.ParameterType == typeof(FactoryArgs))
{
objValue[iParmIndex] = factoryArgs;
}
else if (ss.Attributes != ParameterAttributes.Out)
{
if (dtParmValue.Columns.Contains(strParmName))
{
DataColumn dc = dtParmValue.Columns[strParmName];
object value = null;
if (dc.DataType == typeof(long) ||
dc.DataType == typeof(decimal) ||
dc.DataType == typeof(float) ||
dc.DataType == typeof(double) ||
dc.DataType == typeof(int) ||
dc.DataType == typeof(byte) ||
dc.DataType == typeof(bool))
{
if (drParmValue[strParmName] == DBNull.Value)
{
if (dictParmType.ContainsKey(strParmName))
{
ILBDbType lbType = LBDBType.GetILBDbType(dictParmType[strParmName]);
value = lbType;
}
else
{
value = new t_Decimal();
}
}
else
{
if (dictParmType.ContainsKey(strParmName))
{
ILBDbType lbType = LBDBType.GetILBDbType(dictParmType[strParmName]);
lbType.SetValueWithObject(drParmValue[strParmName]);
value = lbType;
}
else
{
value = new t_Decimal(drParmValue[strParmName]);
}
}
}
else
{
if (dictParmType.ContainsKey(strParmName))
{
ILBDbType lbType = LBDBType.GetILBDbType(dictParmType[strParmName]);
lbType.SetValueWithObject(drParmValue[strParmName]);
value = lbType;
}
else
{
value = new t_String(drParmValue[strParmName]);
}
}
objValue[iParmIndex] = value;
}
}
else
{
if (dictParmType.ContainsKey(strParmName))
{
ILBDbType lbType = LBDBType.GetILBDbType(dictParmType[strParmName]);
lbType.SetValueWithObject(null);
objValue[iParmIndex] = lbType;
}
if (dtOut == null)
{
dtOut = new DataTable("Out");
}
if (!dtOut.Columns.Contains(strParmName))
{
dtOut.Columns.Add(strParmName, typeof(object));
}
dictOutFieldName.Add(iParmIndex, strParmName);
}
iParmIndex++;
}
if (dtOut != null)
{
dtOut.Rows.Add(dtOut.NewRow());
}
//获取Car对象
object obj = s.CreateInstance(str);
//如果有返回值接收下
method.Invoke(obj, objValue);
int iobjReturnIndex = 0;
foreach (object objReturn in objValue)
{
if (objReturn is FactoryArgs)
{
FactoryArgs args = (FactoryArgs)objReturn;
if (args.SelectResult != null)
{
if (dsReturn == null)
{
dsReturn = new DataSet("DSResult");
}
args.SelectResult.TableName = "Return" + iRowIndex.ToString();
dsReturn.Tables.Add(args.SelectResult.Copy());
}
}
if (dictOutFieldName.ContainsKey(iobjReturnIndex))
{
if (objReturn is ILBDbType)
{
ILBDbType lbtype = objReturn as ILBDbType;
if (lbtype.Value != null)
{
dtOut.Rows[0][dictOutFieldName[iobjReturnIndex]] = lbtype.Value;
}
}
}
iobjReturnIndex++;
}
iRowIndex++;
}
#endregion -- 调用中间层程序方法 --
}
strOut = RarDataTable(dtOut);
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
ErrorMsg = ex.InnerException.Message;
}
else
{
ErrorMsg = ex.Message;
}
bolIsError = true;
return("");
}
return(RarDataSet(dsReturn));
}
public DataSet RunProcedure(int ProcedureType, string strLoginName, DataTable dtParmValue,
out DataTable dtOut, out string ErrorMsg, out bool bolIsError)
{
dtOut = null;
bolIsError = false;
DataSet dsReturn = null;
ErrorMsg = "";
try
{
LBFactory factory = new LBFactory();
IBLLFunction function = factory.GetAssemblyFunction(ProcedureType);
if (function == null)
{
#region -- 调用存储过程 --
DataTable dtView = SQLServerDAL.Query("select * from dbo.SysSPType where SysSPType=" + ProcedureType);
if (dtView.Rows.Count > 0)
{
DataRow drView = dtView.Rows[0];
string strSysSPName = drView["SysSPName"].ToString().TrimEnd();
SQLServerDAL.ExecuteProcedure(strSysSPName, dtParmValue, out dtOut, out dsReturn);
}
else
{
throw new Exception("存储过程号【" + ProcedureType + "】不存在！");
}
#endregion
}
else
{
#region -- 调用中间层程序方法 --
string strMethod = function.GetFunctionName(ProcedureType);
string str = function.ToString();
Assembly s = Assembly.Load("LB.Web.Project");
Type tpe = s.GetType(str);
//调用GetName方法
MethodInfo method = tpe.GetMethod(strMethod);
int iRowIndex = 0;
foreach (DataRow drParmValue in dtParmValue.Rows)
{
//获取需要传入的参数
ParameterInfo[] parms = method.GetParameters();
FactoryArgs factoryArgs = new FactoryArgs(strLoginName);
Dictionary <int, string> dictOutFieldName = new Dictionary <int, string>();
object[] objValue = new object[parms.Length];
int iParmIndex = 0;
foreach (ParameterInfo ss in parms)
{
string strParmName = ss.Name;
if (ss.ParameterType == typeof(FactoryArgs))
{
objValue[iParmIndex] = factoryArgs;
}
else if (ss.Attributes != ParameterAttributes.Out)
{
if (dtParmValue.Columns.Contains(strParmName))
{
objValue[iParmIndex] = drParmValue[strParmName];
}
}
else
{
if (dtOut == null)
{
dtOut = new DataTable("Out");
}
if (!dtOut.Columns.Contains(strParmName))
{
dtOut.Columns.Add(strParmName, typeof(object));
}
dictOutFieldName.Add(iParmIndex, strParmName);
}
iParmIndex++;
}
if (dtOut != null)
{
dtOut.Rows.Add(dtOut.NewRow());
}
//获取Car对象
object obj = s.CreateInstance(str);
//如果有返回值接收下
method.Invoke(obj, objValue);
int iobjReturnIndex = 0;
foreach (object objReturn in objValue)
{
if (objReturn is FactoryArgs)
{
FactoryArgs args = (FactoryArgs)objReturn;
if (args.SelectResult != null)
{
if (dsReturn == null)
{
dsReturn = new DataSet("DSResult");
}
args.SelectResult.TableName = "Return" + iRowIndex.ToString();
dsReturn.Tables.Add(args.SelectResult.Copy());
}
}
if (dictOutFieldName.ContainsKey(iobjReturnIndex))
{
dtOut.Rows[0][dictOutFieldName[iobjReturnIndex]] = objReturn;
}
iobjReturnIndex++;
}
iRowIndex++;
}
#endregion -- 调用中间层程序方法 --
}
}
catch (Exception ex)
{
ErrorMsg = ex.InnerException.Message;
bolIsError = true;
}
return(dsReturn);
}
