/// <summary> /// 查询数据 /// </summary> /// <param name="TableName">表名</param> /// <param name="page">页码</param> /// <param name="rows">页量</param> /// <param name="sort">排序字段</param> /// <param name="order">排序方式</param> /// <param name="listFieldName">查询列,默认为 *</param> /// <param name="total">返回总条数</param> /// <returns></returns> public DataTable GetData(string TableName, int page, int rows, string sort, string order, string listFieldName, out int total) { if (listFieldName == "*") { listFieldName = "t.*"; } var sql = @" SELECT * FROM ( SELECT ROWNUM AS rowno," + listFieldName + @" FROM " + TableName + @" t WHERE ROWNUM <= " + (page * rows) + @" ORDER BY " + sort + " " + order + @" ) table_alias WHERE table_alias.rowno >= " + ((page - 1) * rows + 1); var sqlTotal = "select count(1) as total from " + TableName; var dt = new Data.Oracle.OracleHelper(connectionString).Query(sql).Tables[0]; var dtTotal = new Data.Oracle.OracleHelper(connectionString).Query(sqlTotal).Tables[0].Rows[0][0].ToString(); int.TryParse(dtTotal, out total); return(dt); }
/// <summary> /// 获取所有表 /// </summary> /// <returns></returns> public List <Model.DkTableName> GetTable() { var ds = new Data.Oracle.OracleHelper(connectionString).Query(GetTableSQL()); var list = ds.Tables[0].ToModel <Model.DkTableName>(); return(list); }
/// <summary> /// 获取所有列 /// </summary> /// <param name="listTableName">表名</param> /// <returns></returns> public List <Model.DkTableColumn> GetColumn(List <string> listTableName = null) { var whereSql = string.Empty; if (listTableName?.Count > 0) { listTableName.ForEach(x => x = x.Replace("'", "")); whereSql = "AND A.TABLE_NAME IN ('" + string.Join("','", listTableName) + "')"; } var sql = GetColumnSQL(whereSql); var ds = new Data.Oracle.OracleHelper(connectionString).Query(sql); var list = ds.Tables[0].ToModel <Model.DkTableColumn>(); return(list); }
/// <summary> /// 查询数据 /// </summary> /// <param name="TableName">表名</param> /// <param name="page">页码</param> /// <param name="rows">页量</param> /// <param name="sort">排序字段</param> /// <param name="order">排序方式</param> /// <param name="listFieldName">查询列,默认为 *</param> /// <param name="whereSql">条件</param> /// <param name="total">返回总条数</param> /// <returns></returns> public DataTable GetData(string TableName, int page, int rows, string sort, string order, string listFieldName, string whereSql, out int total) { if (listFieldName == "*") { listFieldName = "t.*"; } var countWhere = string.Empty; if (string.IsNullOrWhiteSpace(whereSql)) { whereSql = ""; } else { countWhere = "WHERE " + whereSql; whereSql = "AND " + whereSql; } var sql = $@" SELECT * FROM ( SELECT ROWNUM AS rowno,{listFieldName} FROM {TableName} t WHERE ROWNUM <= {(page * rows)} {whereSql} ORDER BY {sort} {order} ) table_alias WHERE table_alias.rowno >= {((page - 1) * rows + 1)}"; var sqlTotal = $"select count(1) as total from {TableName} {countWhere}"; var dt = new Data.Oracle.OracleHelper(connectionString).Query(sql).Tables[0]; var dtTotal = new Data.Oracle.OracleHelper(connectionString).Query(sqlTotal).Tables[0].Rows[0][0].ToString(); int.TryParse(dtTotal, out total); return(dt); }
/// <summary> /// 检测未发送短信 /// </summary> /// <returns></returns> public static ActionResultVM SmsNotSent() { var vm = new ActionResultVM(); var listPrimaryKey = new List <string>(); try { string connectionString = string.Empty; //查询SQL var querySql = GlobalTo.GetValue("Task:QuerySql"); DataTable dt = null; //数据源连接字符串 if (Enum.TryParse(GlobalTo.GetValue("TypeDB"), true, out TypeDB TDB)) { connectionString = GlobalTo.Configuration.GetConnectionString(TDB.ToString()); switch (TDB) { case TypeDB.MySQL: dt = new Data.MySQL.MySQLHelper(connectionString).Query(querySql).Tables[0]; break; case TypeDB.SQLite: dt = new Data.SQLite.SQLiteHelper(connectionString).Query(querySql).Tables[0]; break; case TypeDB.Oracle: dt = new Data.Oracle.OracleHelper(connectionString).Query(querySql).Tables[0]; break; case TypeDB.SQLServer: dt = new Data.SQLServer.SQLServerHelper(connectionString).Query(querySql).Tables[0]; break; case TypeDB.PostgreSQL: dt = new Data.PostgreSQL.PostgreSQLHelper(connectionString).Query(querySql).Tables[0]; break; } } if (dt.Rows.Count > 0) { var listPhoneNumber = new List <string>(); var listSignName = new List <string>(); var TemplateCode = string.Empty; var listTemplateParamJson = new List <string>(); //表列名 var colPrimaryKey = GlobalTo.GetValue("Task:TableColumn:PrimaryKey"); var colPhoneNumber = GlobalTo.GetValue("Task:TableColumn:PhoneNumber"); var colSignName = GlobalTo.GetValue("Task:TableColumn:SignName"); var colTemplateCode = GlobalTo.GetValue("Task:TableColumn:TemplateCode"); var colTemplateParam = GlobalTo.GetValue("Task:TableColumn:TemplateParam"); foreach (DataRow dr in dt.Rows) { listPrimaryKey.Add(dr[colPrimaryKey].ToString()); listPhoneNumber.Add(dr[colPhoneNumber].ToString()); listSignName.Add(dr[colSignName].ToString()); if (string.IsNullOrWhiteSpace(TemplateCode)) { TemplateCode = dr[colTemplateCode].ToString(); } listTemplateParamJson.Add(dr[colTemplateParam].ToString()); } //发送参数 var dicAddQuery = new Dictionary <string, string> { { "PhoneNumberJson", listPhoneNumber.ToJson() }, { "SignNameJson", listSignName.ToJson() }, { "TemplateCode", TemplateCode }, { "TemplateParamJson", listTemplateParamJson.ToJson() } }; //调用发送方法 vm = SmsService.SendBatchSms(dicAddQuery); //发送成功 if (vm.Code == 200) { //修改状态 var updateSql = GlobalTo.GetValue("Task:UpdateSql"); updateSql = string.Format(updateSql, "'" + string.Join("','", listPrimaryKey) + "'"); int num = 0; switch (TDB) { case TypeDB.MySQL: num = new Data.MySQL.MySQLHelper(connectionString).ExecuteNonQuery(updateSql); break; case TypeDB.SQLite: num = new Data.SQLite.SQLiteHelper(connectionString).ExecuteNonQuery(updateSql); break; case TypeDB.Oracle: num = new Data.Oracle.OracleHelper(connectionString).ExecuteNonQuery(updateSql); break; case TypeDB.SQLServer: num = new Data.SQLServer.SQLServerHelper(connectionString).ExecuteNonQuery(updateSql); break; case TypeDB.PostgreSQL: num = new Data.PostgreSQL.PostgreSQLHelper(connectionString).ExecuteNonQuery(updateSql); break; } if (num <= 0) { vm.Msg += "(修改发送状态失败)"; } } } else { vm.Set(ARTag.lack); } } catch (Exception ex) { vm.Set(ex); } //日志记录 if (GlobalTo.GetValue <bool>("Logs:Enable")) { Core.ConsoleTo.Log(new { vm, listPrimaryKey }.ToJson() + Environment.NewLine); } return(vm); }