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)); }
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); }
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); }