public static string ExecuteDataTableSql(string connectionCode, string sql)
{
string rtn = "";
var dbObject = OThinker.H3.Controllers.AppUtility.Engine.SettingManager.GetBizDbConnectionConfig(connectionCode);
if (dbObject != null)
{
OThinker.Data.Database.CommandFactory factory = new OThinker.Data.Database.CommandFactory(dbObject.DbType, dbObject.DbConnectionString);
var command = factory.CreateCommand();
DataTable dt = command.ExecuteDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter {
DateTimeFormat = "yyyy'-'MM'-'dd HH':'mm':'ss"
};
//JsonConvert.SerializeObject(list.Tables[0], Formatting.Indented, timeConverter);
rtn = Newtonsoft.Json.JsonConvert.SerializeObject(dt, Formatting.Indented, timeConverter);
rtn = "{ \"errcode\": \"0\", \"errmsg\": \"\", \"data\":" + rtn + "}";
}
else
{
rtn = "{ \"errcode\": \"0\", \"errmsg\": \"数据为空\", \"data\":[]}";
}
}
else
{
rtn = "{ \"errcode\": \"1\", \"errmsg\": \"未取到数据，connectionCode错误，请联系接口供应商\", \"data\":[]}";
}
return(rtn);
}
/// <summary>
/// 通过任务编码获得任务信息
/// </summary>
/// <param name="missionCode"></param>
/// <returns></returns>
public static ZD_SyncTaskConfig GetTaskInfoByCode(string missionCode)
{
string sql = @"SELECT [MissionCode]
,[MissionName]
,[SyncTime]
,[SyncSuccessTime]
,[IsAyncSuccess]
,[JobClassName]
,[State]
,[CronExplain],[SyncSuccessBeginTime],[HostName] FROM [H3Cloud].[dbo].[ZD_SyncTaskConfig] where MissionCode='" + missionCode + "'";
OThinker.Data.Database.CommandFactory factory = OThinker.H3.WorkSheet.AppUtility.Engine.EngineConfig.CommandFactory;
OThinker.Data.Database.ICommand command = factory.CreateCommand();
var dataTable = command.ExecuteDataTable(sql);
ZD_SyncTaskConfig result = new ZD_SyncTaskConfig();
if (dataTable.Rows.Count > 0)
{
result.MissionCode = missionCode;
result.MissionName = dataTable.Rows[0]["MissionName"] + string.Empty;
result.SyncSuccessTime = Convert.ToDateTime(dataTable.Rows[0]["SyncSuccessTime"]);
result.SyncTime = Convert.ToDateTime(dataTable.Rows[0]["SyncTime"]);
result.SyncSuccessBeginTime = Convert.ToDateTime(dataTable.Rows[0]["SyncSuccessBeginTime"]);
result.IsAyncSuccess = Convert.ToInt32(dataTable.Rows[0]["IsAyncSuccess"]);
result.State = Convert.ToInt32(dataTable.Rows[0]["State"]);
result.JobClassName = dataTable.Rows[0]["JobClassName"] + string.Empty;
result.CronExplain = dataTable.Rows[0]["CronExplain"] + string.Empty;
result.HostName = dataTable.Rows[0]["HostName"] + string.Empty;
}
else
{
result = null;
}
return(result);
}
public void ExecuteDataTableSP(string connectionCode, string sql, OThinker.Data.Database.Parameter[] par)
{
var dbObject = OThinker.H3.Controllers.AppUtility.Engine.SettingManager.GetBizDbConnectionConfig(connectionCode);
if (dbObject != null)
{
OThinker.Data.Database.CommandFactory factory = new OThinker.Data.Database.CommandFactory(dbObject.DbType, dbObject.DbConnectionString);
var command = factory.CreateCommand();
command.ExecuteProcedure(sql, par);
}
}
public DataTable ExecuteDataTableSql(string connectionCode, string sql)
{
var dbObject = OThinker.H3.Controllers.AppUtility.Engine.SettingManager.GetBizDbConnectionConfig(connectionCode);
if (dbObject != null)
{
OThinker.Data.Database.CommandFactory factory = new OThinker.Data.Database.CommandFactory(dbObject.DbType, dbObject.DbConnectionString);
var command = factory.CreateCommand();
return(command.ExecuteDataTable(sql));
}
return(null);
}
public static object ExecuteScalar(string connectionCode, string sql)
{
var dbObject = AppUtility.Engine.SettingManager.GetBizDbConnectionConfig(connectionCode);
if (dbObject != null)
{
OThinker.Data.Database.CommandFactory factory = new OThinker.Data.Database.CommandFactory(dbObject.DbType, dbObject.DbConnectionString);
var command = factory.CreateCommand();
return(command.ExecuteScalar(sql));
}
return(null);
}
public int ExecuteCountSql(string connectionCode, string sql)
{
var dbObject = OThinker.H3.Controllers.AppUtility.Engine.SettingManager.GetBizDbConnectionConfig(connectionCode);
if (dbObject != null)
{
OThinker.Data.Database.CommandFactory factory = new OThinker.Data.Database.CommandFactory(dbObject.DbType, dbObject.DbConnectionString);
var command = factory.CreateCommand();
var count = command.ExecuteScalar(sql);
return(Convert.ToInt32(count));
}
return(0);
}
public static void ExecuteNonQuery(string connectionCode, string sql)
{
var dbObject = OThinker.H3.Controllers.AppUtility.Engine.SettingManager.GetBizDbConnectionConfig(connectionCode);
if (dbObject != null)
{
OThinker.Data.Database.CommandFactory factory = new OThinker.Data.Database.CommandFactory(dbObject.DbType, dbObject.DbConnectionString);
var command = factory.CreateCommand();
var rtn = command.ExecuteNonQuery(sql);
}
else
{
//return "{ \"errcode\": \"1\", \"errmsg\": \"未取到数据，connectionCode错误，请联系接口供应商\", \"data\":[]}";
}
}
/// <summary>
/// 返回json数据
/// </summary>
/// <param name="connectionCode"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static string ExecuteDataTableToJsonSql(string connectionCode, string sql)
{
var dbObject = OThinker.H3.Controllers.AppUtility.Engine.SettingManager.GetBizDbConnectionConfig(connectionCode);
if (dbObject != null)
{
OThinker.Data.Database.CommandFactory factory = new OThinker.Data.Database.CommandFactory(dbObject.DbType, dbObject.DbConnectionString);
var command = factory.CreateCommand();
DataTable dt = command.ExecuteDataTable(sql);
StringBuilder JsonString = new StringBuilder();
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
JsonString.Append("{");
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j < dt.Columns.Count - 1)
{
JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\":" + "\"" + (dt.Rows[i][j].ToString().Replace("\"", "\\\"")) + "\",");
}
else if (j == dt.Columns.Count - 1)
{
JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\":" + "\"" + (dt.Rows[i][j].ToString().Replace("\"", "\\\"")) + "\"");
}
}
if (i == dt.Rows.Count - 1)
{
JsonString.Append("}");
}
else
{
JsonString.Append("},");
}
}
return("{ \"errcode\": \"0\", \"errmsg\": \"\", \"data\":[" + JsonString.ToString() + "]}");
}
else
{
return("{ \"errcode\": \"0\", \"errmsg\": \"数据为空\", \"data\":[]}");
}
}
else
{
return("{ \"errcode\": \"1\", \"errmsg\": \"未取到数据，connectionCode错误，请联系接口供应商\", \"data\":[]}");
}
}
public DataTable ExecuteDataTableSql(string connectionCode, string sql)
{
try
{
var dbObject = AppUtility.Engine.SettingManager.GetBizDbConnectionConfig(connectionCode);
if (dbObject != null)
{
OThinker.Data.Database.CommandFactory factory = new OThinker.Data.Database.CommandFactory(dbObject.DbType, dbObject.DbConnectionString);
var command = factory.CreateCommand();
return(command.ExecuteDataTable(sql));
}
return(null);
}
catch (Exception ex)
{
AppUtility.Engine.LogWriter.Write("ExecuteDataTableSql Exception:connectionCode-->" + connectionCode + ",sql-->" + sql + "," + ex);
return(null);
}
}
public object ExecuteScalar(string sql)
{
try
{
string connectionCode = connecting_code;
var dbObject = OThinker.H3.Controllers.AppUtility.Engine.SettingManager.GetBizDbConnectionConfig(connectionCode);
if (dbObject != null)
{
OThinker.Data.Database.CommandFactory factory = new OThinker.Data.Database.CommandFactory(dbObject.DbType, dbObject.DbConnectionString);
var command = factory.CreateCommand();
return(command.ExecuteScalar(sql));
}
}
catch
{
return(null);
}
return(null);
}
/// <summary>
/// 应用程序启动时从新启动已开启的任务
/// </summary>
public static void StartPlan()
{
try
{
string sql = @"SELECT [MissionCode]
,[MissionName]
,[SyncTime]
,[SyncSuccessTime]
,[IsAyncSuccess]
,[JobClassName]
,[State]
,[CronExplain],[SyncSuccessBeginTime],[HostName] FROM [H3Cloud].[dbo].[ZD_SyncTaskConfig] where State=1 and HostName='" + Dns.GetHostName() + "'";
OThinker.Data.Database.CommandFactory factory = OThinker.H3.WorkSheet.AppUtility.Engine.EngineConfig.CommandFactory;
OThinker.Data.Database.ICommand command = factory.CreateCommand();
var dataTable = command.ExecuteDataTable(sql);
if (dataTable.Rows.Count > 0)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
ZD_SyncTaskConfig result = new ZD_SyncTaskConfig();
result.MissionCode = dataTable.Rows[i]["MissionCode"] + string.Empty;
result.MissionName = dataTable.Rows[i]["MissionName"] + string.Empty;
result.SyncSuccessTime = Convert.ToDateTime(dataTable.Rows[i]["SyncSuccessTime"]);
result.SyncTime = Convert.ToDateTime(dataTable.Rows[i]["SyncTime"]);
result.SyncSuccessBeginTime = Convert.ToDateTime(dataTable.Rows[i]["SyncSuccessBeginTime"]);
result.IsAyncSuccess = Convert.ToInt32(dataTable.Rows[i]["IsAyncSuccess"]);
result.State = 1;
result.JobClassName = dataTable.Rows[i]["JobClassName"] + string.Empty;
result.CronExplain = dataTable.Rows[i]["CronExplain"] + string.Empty;
result.HostName = dataTable.Rows[i]["HostName"] + string.Empty;
OThinker.H3.Portal.JobScheduler.Start(result);
}
}
}
catch (Exception e)
{
_logger.InfoFormat(e.Message.ToString());
throw;
}
}
/// <summary>
/// 获取方法列
/// </summary>
/// <param name="serviceCode"></param>
/// <param name="methodName"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public JsonResult GetMethodColumns(string serviceCode, string methodName, string sql, string parameters)
{
return(ExecuteFunctionRun(() =>
{
ActionResult result = new ActionResult(true, "");
OThinker.H3.BizBus.BizService.BizService Service = GetService(serviceCode);
Settings.BizDbConnectionConfig DbConnectionConfig = GetDbConnectionConfig(Service);
if (DbConnectionConfig != null)
{
try
{
string[] ParameterNames = (string[])JsonConvert.DeserializeObject <string[]>(parameters);//前端组合的XML中的Parameters
List <OThinker.Data.Database.Parameter> lstParameters = new List <OThinker.Data.Database.Parameter>();
if (ParameterNames != null)
{
foreach (string p in ParameterNames)
{
lstParameters.Add(new OThinker.Data.Database.Parameter(p, System.Data.DbType.String, null));
}
}
OThinker.Data.Database.ICommand Command = new OThinker.Data.Database.CommandFactory(DbConnectionConfig.DbType, DbConnectionConfig.DbConnectionString).CreateCommand();
System.Data.DataTable dt = Command.ExecuteDataTable(sql, lstParameters.ToArray());
if (dt != null)
{
Dictionary <string, string> ColumnDictionary = new Dictionary <string, string>();
string StringType = typeof(string).FullName + string.Empty;
foreach (System.Data.DataColumn c in dt.Columns)
{
if (!ColumnDictionary.ContainsKey(c.ColumnName))
{
Data.DataLogicType LogicType = Data.DataLogicType.ShortString;
switch (c.DataType.ToString().ToLower())
{
case "system.boolean":
LogicType = Data.DataLogicType.Bool;
break;
case "system.char":
case "system.byte":
case "system.sbyte":
LogicType = Data.DataLogicType.ShortString;
break;
case "system.int":
case "system.int16":
case "system.int32":
case "system.int64":
case "system.uint16":
case "system.uint32":
case "system.uint64":
LogicType = Data.DataLogicType.Int;
break;
case "system.decimal":
case "system.double":
case "system.single":
LogicType = Data.DataLogicType.Decimal;
break;
case "system.object":
break;
case "system.string":
LogicType = Data.DataLogicType.String;
break;
default:
break;
}
ColumnDictionary.Add(c.ColumnName, LogicType.ToString());
}
}
result.Extend = ColumnDictionary;
}
}
catch (Exception ex)
{
result.Success = false;
result.Extend = ex.Message;
}
return Json(result, JsonRequestBehavior.AllowGet);
}
return null;
}));
}
