/// <summary> /// 用户开始注册,生成邮箱的验证码 /// </summary> /// <param name="phone"></param> /// <param name="userName"></param> /// <param name="emailAddress"></param> /// <returns></returns> public string NewUserSentVerificationCode(string phone, string userName, string emailAddress) { Random rd = new Random(); var aa = rd.Next(100000, 999999); string verificationCode = aa.ToString(); emailAddress = emailAddress.TrimStart().TrimEnd(); using (DataAccessBroker broker = DataAccessFactory.Instance()) { broker.BeginTransaction(); try { string sql = @" INSERT INTO dbo.[User]( userid ,username ,password ,age ,realname ,idcard ,userclass ,createtime ,endtime ,logintime ,emailaddress ,phonenumber,VerificationCode) VALUES ( NEWID() ,'" + userName + "' ,'' ,0 ,'" + userName + "' ,'' ,0 ,GETDATE() ,GETDATE() ,GETDATE() ,'" + emailAddress + "' ,'" + phone + "','" + verificationCode + "')"; var success = broker.ExecuteSQL(sql); broker.Commit(); } catch (Exception e) { } } var reciever = emailAddress; var cc = ""; var title = "用户注册"; var content = "感谢您注册磐石系统账号,您的验证码是:" + verificationCode; LubrSentMail lubrmial = new LubrSentMail(); lubrmial.SendEmail(reciever, title, content, null); return(""); }
public virtual bool DeleteUserByIdList(List <string> idList, string userid) { string sql = ""; using (DataAccessBroker broker = DataAccessFactory.Instance()) { broker.BeginTransaction(); try { foreach (string id in idList) { sql = "UPDATE " + CasUserTable.C_TableName; //sql += " SET " + CasUserTable.C_IS_DELETE + "=1 "; sql += " WHERE " + CasUserTable.C_USER_ID + "='" + id + "'"; broker.ExecuteSQL(sql); } broker.Commit(); WebCaching.UserCaching = null; return(true); } catch { broker.RollBack(); return(false); } } }
public static void Update <T>(DataAccessBroker broker, List <T> list) where T : EntityBase { // 目标为数据表的情况 string curProcess = ""; try { // 保存数据 curProcess = "修改数据"; if (list.Count > 0) { string sqlString = "UPDATE " + list[0].OringTableSchema.TableName + " SET " + ParseSQL(list[0].OringTableSchema.ValueColumnInfo, ",") + " WHERE " + ParseSQL(list[0].OringTableSchema.KeyColumnInfo, " AND "); foreach (T entity in list) { Application.DoEvents(); // 给目标表新增记录 DataAccessParameterCollection dpc = new DataAccessParameterCollection(); foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo) { dpc.AddWithValue(field, entity.GetData(field.ColumnName)); } broker.ExecuteSQL(sqlString, dpc); } } } catch (Exception ex) { // 写日志 string description = "运行中异常,目前执行到'" + curProcess + "',单一事务,事务回滚."; // 继续抛出异常 throw new InvalidOperationException(description + ex.Message); } }
/// <summary> /// 用户开始注册,生成手机短信的验证码 /// </summary> /// <param name="phone"></param> /// <param name="userName"></param> /// <returns></returns> public string NewUserSentSMSCode(string phone, string userName, string flagstatus) { Random rd = new Random(); var aa = rd.Next(1000, 10000); string SMSCode = aa.ToString(); using (DataAccessBroker broker = DataAccessFactory.Instance()) { broker.BeginTransaction(); try { string sql = @" INSERT INTO [eContract].[dbo].[UserPhoneSMSCode](phonenumber,sendtime,expiretime,smscode,spare2,SMSId,flagstatus) VALUES ( '" + phone + "', GETDATE() , 20 ,'" + SMSCode + "','',NEWID(),'" + flagstatus + "')"; var success = broker.ExecuteSQL(sql); broker.Commit(); } catch (Exception e) { } } /*var reciever = emailAddress; * var cc = ""; * var title = "用户注册"; * var content = "感谢您注册磐石系统账号,您的验证码是:" + verificationCode; * LubrSentMail lubrmial = new LubrSentMail(); * lubrmial.SendEmail(reciever, title, content, null);*/ return(SMSCode); }
public static void DeleteAllIrisRecoResult() { using (DataAccessBroker broker = DataAccessFactory.Instance()) { broker.ExecuteSQL("delete from Iris_Reco_Result"); } }
/// <summary> /// 删除文件 /// </summary> /// <param name="id"></param> /// <returns>true:删除成功 false:删除失败</returns> public bool DeleteFile(string id) { using (DataAccessBroker broker = DataAccessFactory.Instance()) { string sql = $"DELETE FROM dbo.CAS_ATTACHMENT WHERE ATTACHMENT_ID = '{id}'"; return(broker.ExecuteSQL(sql) >= 1); } }
/// <summary> /// 删除用户 /// </summary> /// <param name="userId"></param> /// <param name="broker"></param> public static void DeleteUser(string userId, DataAccessBroker broker) { DateTime time = DateTime.Now; string now = time.Year.ToString() + "-" + time.Month.ToString() + "-" + time.Day.ToString() + " " + time.Hour.ToString() + "-" + time.Minute.ToString() + "-" + time.Second.ToString(); string sql = "update {0} set {1}={2}, {3}=to_date('{4}','YYYY-MM-DD HH24-MI-SS') where {5}='{6}'"; broker.ExecuteSQL(sql); }
public bool UpdatePageControls(string pageID, int pageType, List <SecPageControlEntity> list) { List <SecPageControlEntity> oldPageControls = GetPageControls(pageID, pageType); List <SecPageControlEntity> insertList = new List <SecPageControlEntity>(); List <SecPageControlEntity> updateList = new List <SecPageControlEntity>(); List <SecPageControlEntity> deleteList = new List <SecPageControlEntity>(); foreach (var item1 in oldPageControls) { bool find = false; foreach (var item2 in list) { if (item2.ControlId == item1.ControlId) { find = true; if (item1.ControlName != item2.ControlName || item1.ServerId != item2.ServerId) { updateList.Add(item2); } } } if (!find) { deleteList.Add(item1); } } foreach (var item2 in list) { if (string.IsNullOrEmpty(item2.ControlId)) { item2.ControlId = Guid.NewGuid().ToString(); insertList.Add(item2); } } using (DataAccessBroker broker = DataAccessFactory.Instance()) { try { broker.BeginTransaction(); DataAccess.Update(updateList, broker); DataAccess.Insert(insertList, broker); foreach (var item in deleteList) { broker.ExecuteSQL("DELETE FROM [SEC_ROLE_CONTROL] WHERE [CONTROL_ID]='" + item.ControlId + "' and [PAGE_TYPE]=" + pageType); } DataAccess.Delete(deleteList, broker); broker.Commit(); return(true); } catch (Exception ex) { broker.RollBack(); return(false); } } }
public static void SaveToTargetDB(DataAccessBroker broker, TableMap tableMap, DataTable dtSource) { // 目标为数据表的情况 string curProcess = ""; try { if (tableMap.IsFullData == true) { // 清空目标表数据 curProcess = "清空目标表数据"; string deleteSql = tableMap.GetTargetDeleteAllSQL(); broker.ExecuteSQL(deleteSql); } // 保存数据 curProcess = "保存数据"; // 确保TableMap有FieldMap tableMap.InitialFieldMapByDataTable(dtSource); string insertSql = tableMap.GetTargetInsertSQL(broker.ParameterPrefix.ToString()); for (int i = 0; i < dtSource.Rows.Count; i++) { Application.DoEvents(); // 给目标表新增记录 DataRow row = dtSource.Rows[i]; DataAccessParameterCollection dpc = tableMap.GetTargetParameters(row, tableMap.TargetTable.NullFieldNameList); broker.ExecuteSQL(insertSql, dpc); } } catch (Exception ex) { // 写日志 string description = "运行中异常,目前执行到'" + curProcess + "',单一事务,事务回滚."; // 继续抛出异常 throw new InvalidOperationException(description + ex.Message); } }
/// <summary> /// 领导批注 /// </summary> /// <param name="casContractApprovalResultEntity"></param> /// <returns></returns> public bool OptionCommnet(CasContractApprovalResultEntity casContractApprovalResultEntity) { bool flag = false; using (DataAccessBroker broker = DataAccessFactory.Instance()) { broker.BeginTransaction(); try { string sql = $@"UPDATE dbo.CAS_CONTRACT_APPROVER SET STATUS = 3 WHERE APPROVER_TYPE = 1 AND CONTRACT_ID = '{casContractApprovalResultEntity.ContractId}' AND APPROVER_ID = '{casContractApprovalResultEntity.ApproverId}' AND STATUS = 2"; //审批人表更改领导批注状态为已批注 broker.ExecuteSQL(sql); //插入审批结果 flag = DataAccess.Insert(casContractApprovalResultEntity); if (flag) { broker.Commit(); } else { broker.RollBack(); } } catch (Exception e) { flag = false; broker.RollBack(); SystemService.LogErrorService.InsertLog(e); } } return(flag); }
public override List <LogEntity> GetExceptionLog() { List <LogEntity> list = new List <LogEntity>(); string strSql = "SELECT * FROM " + TableName + " WHERE LOG_STATE=2 AND LOG_TYPE=1 ORDER BY LOG_TIME DESC"; DataSet ds = DataAccess.SelectDataSet(strSql); if (ds == null || ds.Tables.Count == 0) { return(list); } DataTable dt = ds.Tables[0]; for (int i = 0; i < dt.Rows.Count; i++) { DataRow row = dt.Rows[i]; LogEntity entity = new LogEntity(); entity.Time = row["LOG_TIME"].ToString(); entity.Category = Convert.ToInt32(row["LOG_STATE"]); entity.JobCode = row["JOB_NAME"].ToString(); entity.Description = row["DESCRIPTION"].ToString(); entity.ExceptionType = row["EXCEPTION_TYPE"].ToString(); entity.ExceptionMessage = row["EXCEPTION_MESSAGE"].ToString(); entity.ThreadName = row["THREAD_Name"].ToString(); list.Add(entity); } using (DataAccessBroker broker = DataAccessFactory.Instance()) { broker.ExecuteSQL("UPDATE " + TableName + " SET LOG_TYPE=2 WHERE LOG_STATE=2 AND LOG_TYPE=1 "); } return(list); }
/// <summary> /// 写日志 /// </summary> /// <param name="jobCode"></param> /// <param name="logState"></param> /// <param name="description"></param> public override void WriteLogInternal(string jobCode, string description, Exception ex, int logCategory, string threadName) { using (DataAccessBroker broder = DataAccessFactory.Instance()) { string sqlString = "insert into " + this.TableName + " (" + " sn" + " ,LOG_DDP_ID" + " ,JOB_NAME" + " ,LOG_TIME" + " ,LOG_TYPE" + " ,LOG_STATE" + " ,PHASE" + " ,DESCRIPTION" + " ,EXCEPTION_TYPE" + " ,EXCEPTION_MESSAGE" + " ,MACHINE_NAME" + " ,PROCESS_NAME" + " ,THREAD_NAME" + " )" + " values(" + " emp_sequence.nextval" + " ," + _prefix + "LOG_DDP_ID" + " ," + _prefix + "JOB_NAME" + " ," + _prefix + "LOG_TIME" + " ," + _prefix + "LOG_TYPE" + " ," + _prefix + "LOG_STATE" + " ," + _prefix + "PHASE" + " ," + _prefix + "DESCRIPTION" + " ," + _prefix + "EXCEPTION_TYPE" + " ," + _prefix + "EXCEPTION_MESSAGE" + " ," + _prefix + "MACHINE_NAME" + " ," + _prefix + "PROCESS_NAME" + " ," + _prefix + "THREAD_NAME" + ")"; DataAccessParameterCollection dpc = new DataAccessParameterCollection(); ColumnInfo columnInfo = new ColumnInfo("LOG_DDP_ID", "LOG_DDP_ID", true, typeof(string)); dpc.AddWithValue(columnInfo, Guid.NewGuid().ToString()); if (jobCode == "") { jobCode = " "; } columnInfo = new ColumnInfo("JOB_NAME", "JOB_NAME", true, typeof(string)); dpc.AddWithValue(columnInfo, jobCode); columnInfo = new ColumnInfo("LOG_TIME", "LOG_TIME", true, typeof(DateTime)); dpc.AddWithValue(columnInfo, DateTime.Now); columnInfo = new ColumnInfo("LOG_TYPE", "LOG_TYPE", true, typeof(int)); dpc.AddWithValue(columnInfo, Log_Type_Hand); columnInfo = new ColumnInfo("LOG_STATE", "LOG_STATE", true, typeof(int)); dpc.AddWithValue(columnInfo, logCategory); columnInfo = new ColumnInfo("PHASE", "PHASE", true, typeof(string)); dpc.AddWithValue(columnInfo, " "); columnInfo = new ColumnInfo("DESCRIPTION", "DESCRIPTION", true, typeof(string)); dpc.AddWithValue(columnInfo, description); if (ex == null) { columnInfo = new ColumnInfo("EXCEPTION_TYPE", "EXCEPTION_TYPE", true, typeof(string)); dpc.AddWithValue(columnInfo, " "); columnInfo = new ColumnInfo("EXCEPTION_MESSAGE", "EXCEPTION_MESSAGE", true, typeof(string)); dpc.AddWithValue(columnInfo, " "); } else { columnInfo = new ColumnInfo("EXCEPTION_TYPE", "EXCEPTION_TYPE", true, typeof(string)); dpc.AddWithValue(columnInfo, ex.GetType().ToString()); string tempMessage = ex.Message + ex.StackTrace; if (tempMessage.Length > 1999) { tempMessage = tempMessage.Substring(0, 1999); } columnInfo = new ColumnInfo("EXCEPTION_MESSAGE", "EXCEPTION_MESSAGE", true, typeof(string)); dpc.AddWithValue(columnInfo, tempMessage); } columnInfo = new ColumnInfo("MACHINE_NAME", "MACHINE_NAME", true, typeof(string)); dpc.AddWithValue(columnInfo, Environment.MachineName); columnInfo = new ColumnInfo("PROCESS_NAME", "PROCESS_NAME", true, typeof(string)); dpc.AddWithValue(columnInfo, "eContract.DDP.WindowsService.exe"); columnInfo = new ColumnInfo("THREAD_NAME", "THREAD_NAME", true, typeof(string)); dpc.AddWithValue(columnInfo, threadName); broder.ExecuteSQL(sqlString, dpc); } }
/// <summary> /// 保存header和detail到数据,一笔header与对应的几笔detail作为一个事务 /// </summary> /// <param name="dtHeader"></param> /// <param name="dtDetail"></param> private void SaveHeaderDetailToTarget(ref Hashtable parameters, DataTable dtHeader, DataTable dtDetail, string dataSplitOperator) { List <string> dataIDList = (List <string>)parameters[DDPConst.Param_DataIDList]; int nError = 0; int nSuccess = 0; DataAccessBroker brokerTarget = DataAccessFactory.Instance(this.TargetDataAccessCfg); try { // header sql this.HeaderTableMap.InitialFieldMapByDataTable(dtHeader); string insertSqlHeader = this.HeaderTableMap.GetTargetInsertSQL(brokerTarget.ParameterPrefix.ToString()); // detail sql if (dtDetail.Rows.Count > 0) { this.DetailTableMap.InitialFieldMapByDataTable(dtDetail.Rows[0]); } string insertSqlDetail = this.DetailTableMap.GetTargetInsertSQL(brokerTarget.ParameterPrefix.ToString()); for (int i = 0; i < dtHeader.Rows.Count; i++) { Application.DoEvents(); string recordsCondition = ""; // 一条header DataRow rowHeader = dtHeader.Rows[i]; // 一笔完整的记录一个事务,包括header和detail brokerTarget.BeginTransaction(); try { // 并出关联字段及值,便出检索detail string refFieldSql = ""; string refValueSql = ""; string[] refFieldList = this.DetailTableMap.SourceTable.RefFields.Split(new char[] { ',' }); for (int x = 0; x < refFieldList.Length; x++) { string refField = refFieldList[x]; if (refFieldSql != "") { refFieldSql += "+'|'+"; } refFieldSql += refField; if (refValueSql != "") { refValueSql += "|"; } refValueSql += rowHeader[refField].ToString(); } recordsCondition = refFieldSql + "='" + refValueSql + "'"; // 找到Detail数据 DataRow[] detailRowList = dtDetail.Select(recordsCondition); LogManager.Current.WriteCommonLog(this.JobCode, refValueSql + "对应" + detailRowList.Length.ToString() + "条detail记录。", this.ThreadName); // 将header插入到数据库 DataAccessParameterCollection dpcHeader = this.HeaderTableMap.GetTargetParameters(rowHeader, this.HeaderTableMap.TargetTable.NullFieldNameList); brokerTarget.ExecuteSQL(insertSqlHeader, dpcHeader); // 将detail插入到数据库 for (int j = 0; j < detailRowList.Length; j++) { // detail DataRow rowDetail = detailRowList[j]; DataAccessParameterCollection dpcDetail = this.DetailTableMap.GetTargetParameters(rowDetail, this.DetailTableMap.TargetTable.NullFieldNameList); brokerTarget.ExecuteSQL(insertSqlDetail, dpcDetail); } // 事务提交 brokerTarget.Commit(); LogManager.Current.WriteCommonLog(this.JobCode, recordsCondition + "记录保存数据库成功。", this.ThreadName); nSuccess++; } catch (Exception ex) { // 事务回滚 brokerTarget.RollBack(); LogManager.Current.WriteErrorLog(this.JobCode, recordsCondition + "记录保存数据库出错:" + ex.Message, this.ThreadName); // 从DataIDList中移出此笔记录,这样就不会更新最新时间了 string dataID = this.GetFieldValue(rowHeader, this.HeaderTableMap.SourceTable.PrimaryKeys, dataSplitOperator); dataIDList.Remove(dataID); nError++; // 继续下面的记录 continue; } } } catch (Exception ex) { throw ex; } finally { brokerTarget.Close(); } // 写日志 string errorInfo = "来源共" + dtHeader.Rows.Count.ToString() + "笔记录," + nSuccess.ToString() + "笔保存成功," + nError.ToString() + "笔保存失败。"; LogManager.Current.WriteCommonLog(this.JobCode, errorInfo, this.ThreadName); // 保存到参数里,传给外面 parameters[DDPConst.Param_Info] = errorInfo; // 将错误的记录数保存在参数里 if (nError > 0) { parameters[DDPConst.Param_ErrorCount] = nError; } }