/// <summary>
/// 得到审核主表最新的一个实体
/// </summary>
/// <param name="conn">OracleConnection</param>
/// <param name="formid">formid</param>
/// <returns></returns>
public FLOW_FLOWRECORDMASTER_T GetFlowerMasterIDByFormid(OracleConnection conn, string formid)
{
try
{
FLOW_FLOWRECORDMASTER_T model = new FLOW_FLOWRECORDMASTER_T();
string selSql = "SELECT INSTANCEID,FORMID,EDITUSERID,EDITUSERNAME FROM FLOW_FLOWRECORDMASTER_T WHERE FORMID=:FORMID ORDER BY CREATEDATE DESC";
OracleParameter[] pageparm =
{
new OracleParameter(":FORMID", OracleType.NVarChar, 100)
};
pageparm[0].Value = formid;
DataTable dt = OracleDataProvider.GetDataTable(conn, selSql, pageparm);
if (dt.Rows.Count > 0)
{ //多次提交单据的时候，取最新的一条数据
model.INSTANCEID = dt.Rows[0]["INSTANCEID"].ToString(); //
model.FORMID = dt.Rows[0]["FORMID"].ToString(); //
model.EDITUSERID = dt.Rows[0]["EDITUSERID"].ToString(); //
model.EDITUSERNAME = dt.Rows[0]["EDITUSERNAME"].ToString(); //
}
return(model);
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
/// <summary>
/// 运维人员处理异常记录
/// </summary>
/// <param name="conn">OracleConnection</param>
/// <param name="model">FLOW_EXCEPTIONLOG</param>
/// <returns></returns>
public int Update(OracleConnection conn, FLOW_EXCEPTIONLOG model)
{
try
{
string updSql = "UPDATE FLOW_EXCEPTIONLOG SET STATE=:STATE,UPDATEDATE=:UPDATEDATE,UPDATENAME=:UPDATENAME,REMARK=:REMARK WHERE ID=:ID";
OracleParameter[] pageparm =
{
new OracleParameter(":ID", OracleType.NVarChar, 100),
new OracleParameter(":STATE", OracleType.NVarChar, 100),
new OracleParameter(":UPDATEDATE", OracleType.DateTime),
new OracleParameter(":UPDATENAME", OracleType.NVarChar, 100),
new OracleParameter(":REMARK", OracleType.NVarChar, 4000)
};
pageparm[0].Value = OracleDataProvider.GetValue(model.ID); //主键ID
pageparm[1].Value = OracleDataProvider.GetValue(model.STATE); //状态:未处理;已处理
pageparm[2].Value = OracleDataProvider.GetValue(DateTime.Now); //处理日期
pageparm[3].Value = OracleDataProvider.GetValue(model.UPDATENAME); //处理人
pageparm[4].Value = OracleDataProvider.GetValue(model.REMARK); //备注
return(ExecuteSQL(conn, updSql, pageparm));
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
/// <summary>
/// 得到流程持久化实例一个对象实体
/// </summary>
/// <param name="conn">OracleConnection</param>
/// <param name="instance_id">实例ID</param>
/// <returns></returns>
public FLOW_INSTANCE_STATE GetInstanceModel(OracleConnection conn, string instance_id)
{
FLOW_INSTANCE_STATE model = new FLOW_INSTANCE_STATE();
string selSql = "SELECT INSTANCE_ID,STATE,STATUS,UNLOCKED,BLOCKED,INFO,MODIFIED,OWNER_ID,OWNED_UNTIL,NEXT_TIMER FROM INSTANCE_STATE WHERE INSTANCE_ID=:INSTANCE_ID";
OracleParameter[] pageparm =
{
new OracleParameter(":INSTANCE_ID", OracleType.Char, 36)
};
pageparm[0].Value = instance_id;
OracleDataReader dr = OracleDataProvider.ExecuteReader(conn, selSql, pageparm);
if (dr.Read())
{
model.INSTANCE_ID = dr["INSTANCE_ID"].ToString(); //
model.STATE = (byte[])dr["STATE"]; //
model.STATUS = dr["STATUS"].ToString() != "" ? Convert.ToDecimal(dr["STATUS"]) : 0; //
model.UNLOCKED = dr["UNLOCKED"].ToString() != "" ? Convert.ToDecimal(dr["UNLOCKED"]) : 0; //
model.BLOCKED = dr["BLOCKED"].ToString() != "" ? Convert.ToDecimal(dr["BLOCKED"]) : 0; //
model.INFO = dr["INFO"].ToString(); //
model.MODIFIED = dr["MODIFIED"].ToString() != "" ? Convert.ToDateTime(dr["MODIFIED"]) : DateTime.Now; //
model.OWNER_ID = dr["OWNER_ID"].ToString(); //
model.OWNED_UNTIL = dr["OWNED_UNTIL"].ToString() != "" ? Convert.ToDateTime(dr["OWNED_UNTIL"]) : DateTime.Now; //
model.NEXT_TIMER = dr["NEXT_TIMER"].ToString() != "" ? Convert.ToDateTime(dr["NEXT_TIMER"]) : DateTime.Now; //
}
dr.Close();
conn.Close();
return(model);
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="conn">OracleConnection</param>
/// <param name="sql">SQL语句</param>
/// <returns></returns>
public int ExecuteSQL(OracleConnection conn, string sql)
{
try
{
return(OracleDataProvider.ExecuteSQL(conn, sql, null));
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="conn">OracleConnection</param>
/// <param name="sql">SQL语句</param>
/// <param name="pageparm">数参</param>
/// <returns></returns>
public int ExecuteSQL(OracleConnection conn, string sql, OracleParameter[] pageparm)
{
try
{
return(OracleDataProvider.ExecuteSQL(conn, sql, pageparm));
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
/// <summary>
/// 增加异常记录(以实体传值)
/// </summary>
/// <param name="conn">OracleConnection </param>
/// <param name="model">FLOW_EXCEPTIONLOG</param>
/// <returns></returns>
public int Add(OracleConnection conn, FLOW_EXCEPTIONLOG model)
{
try
{
string insSql = "INSERT INTO FLOW_EXCEPTIONLOG (ID,FORMID,MODELCODE,CREATEDATE,CREATENAME,SUBMITINFO,LOGINFO,MODELNAME,OWNERID,OWNERNAME,OWNERCOMPANYID,OWNERCOMPANYNAME,OWNERDEPARMENTID,OWNERDEPARMENTNAME,OWNERPOSTID,OWNERPOSTNAME,AUDITSTATE) VALUES (:ID,:FORMID,:MODELCODE,:CREATEDATE,:CREATENAME,:SUBMITINFO,:LOGINFO,:MODELNAME,:OWNERID,:OWNERNAME,:OWNERCOMPANYID,:OWNERCOMPANYNAME,:OWNERDEPARMENTID,:OWNERDEPARMENTNAME,:OWNERPOSTID,:OWNERPOSTNAME,:AUDITSTATE)";
OracleParameter[] pageparm =
{
new OracleParameter(":ID", OracleType.NVarChar, 100),
new OracleParameter(":FORMID", OracleType.NVarChar, 100),
new OracleParameter(":MODELCODE", OracleType.NVarChar, 100),
new OracleParameter(":CREATEDATE", OracleType.DateTime),
new OracleParameter(":CREATENAME", OracleType.NVarChar, 100),
new OracleParameter(":SUBMITINFO", OracleType.NVarChar, 4000),
new OracleParameter(":LOGINFO", OracleType.Clob),
new OracleParameter(":MODELNAME", OracleType.NVarChar, 100),
new OracleParameter(":OWNERID", OracleType.NVarChar, 100),
new OracleParameter(":OWNERNAME", OracleType.NVarChar, 100),
new OracleParameter(":OWNERCOMPANYID", OracleType.NVarChar, 100),
new OracleParameter(":OWNERCOMPANYNAME", OracleType.NVarChar, 100),
new OracleParameter(":OWNERDEPARMENTID", OracleType.NVarChar, 100),
new OracleParameter(":OWNERDEPARMENTNAME", OracleType.NVarChar, 100),
new OracleParameter(":OWNERPOSTID", OracleType.NVarChar, 100),
new OracleParameter(":OWNERPOSTNAME", OracleType.NVarChar, 100),
new OracleParameter(":AUDITSTATE", OracleType.NVarChar, 100)
};
pageparm[0].Value = OracleDataProvider.GetValue(model.ID); //主键ID
pageparm[1].Value = OracleDataProvider.GetValue(model.FORMID); //业务ID
pageparm[2].Value = OracleDataProvider.GetValue(model.MODELCODE); //模块代码
pageparm[3].Value = OracleDataProvider.GetValue(model.CREATEDATE); //创建日期
pageparm[4].Value = OracleDataProvider.GetValue(model.CREATENAME); //创建人
pageparm[5].Value = OracleDataProvider.GetValue(model.SUBMITINFO); //提交信息
pageparm[6].Value = OracleDataProvider.GetValue(model.LOGINFO); //异常日志信息
pageparm[7].Value = OracleDataProvider.GetValue(model.MODELNAME); //模块名称
pageparm[8].Value = OracleDataProvider.GetValue(model.OWNERID); //单据所属人ID
pageparm[9].Value = OracleDataProvider.GetValue(model.OWNERNAME); //单据所属人姓名
pageparm[10].Value = OracleDataProvider.GetValue(model.OWNERCOMPANYID); //单据所属人公司ID
pageparm[11].Value = OracleDataProvider.GetValue(model.OWNERCOMPANYNAME); //单据所属人公司名称
pageparm[12].Value = OracleDataProvider.GetValue(model.OWNERDEPARMENTID); //单据所属人部门ID
pageparm[13].Value = OracleDataProvider.GetValue(model.OWNERDEPARMENTNAME); //单据所属人部门名称
pageparm[14].Value = OracleDataProvider.GetValue(model.OWNERPOSTID); //单据所属人岗位ID
pageparm[15].Value = OracleDataProvider.GetValue(model.OWNERPOSTNAME); //单据所属人岗位名称
pageparm[16].Value = OracleDataProvider.GetValue(model.AUDITSTATE); //审核状态;审核通过,审核不通过
return(ExecuteSQL(conn, insSql, pageparm));
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
public static void OSPDemo()
{
Console.WriteLine("\n\nOpen Close Principle Demo ");
DataProvider DataProviderObject = new SqlDataProvider();
DataProviderObject.OpenConnection();
DataProviderObject.ExecuteCommand();
DataProviderObject.CloseConnection();
DataProviderObject = new OracleDataProvider();
DataProviderObject.OpenConnection();
DataProviderObject.ExecuteCommand();
DataProviderObject.CloseConnection();
}
/// <summary>
/// 获取元数据
/// </summary>
/// <param name="formid">formid</param>
/// <returns></returns>
public static string GetMetadataByFormid(string formid)
{
try
{
string sql = "select businessobject from FLOW_FLOWRECORDMASTER_T where formid='" + formid + "' order by createdate desc ";
using (OracleConnection con = new OracleConnection(ADOHelper.ContextOracleConnection))
{
try
{
con.Open();
DataTable dt = OracleDataProvider.GetDataTable(con, sql);
if (dt != null && dt.Rows.Count > 0)
{
return(dt.Rows[0]["businessobject"].ToString());//取新新的一条
}
else
{
return("");
}
}
catch (Exception ex)
{
LogHelper.WriteLog("获取元数据:GetMetadataByFormid-> OracleDataProvider.GetDataTable:异常信息：" + ex.Message);
return("");
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
LogHelper.WriteLog("获取元数据:GetMetadataByFormid-> SQL=" + sql);
}
}
}
catch (Exception ex)
{
LogHelper.WriteLog("获取元数据:GetMetadataByFormid:异常信息：" + ex.Message);
return("");
}
}
/// <summary>
/// 增加一条数据(以实体传值)
/// </summary>
/// <param name="conn">OracleConnection</param>
/// <param name="model">FLOW_INSTANCE_STATE</param>
/// <returns></returns>
public int Add(OracleConnection conn, FLOW_INSTANCE_STATE model)
{
string insSql = "INSERT INTO FLOW_INSTANCE_STATE (INSTANCE_ID,STATE,STATUS,UNLOCKED,BLOCKED,INFO,MODIFIED,OWNER_ID,OWNED_UNTIL,NEXT_TIMER,FORMID,CREATEID,CREATENAME,EDITID,EDITNAME) VALUES (:INSTANCE_ID,:STATE,:STATUS,:UNLOCKED,:BLOCKED,:INFO,:MODIFIED,:OWNER_ID,:OWNED_UNTIL,:NEXT_TIMER,:FORMID,:CREATEID,:CREATENAME,:EDITID,:EDITNAME)";
OracleParameter[] pageparm =
{
new OracleParameter(":INSTANCE_ID", OracleType.Char, 36),
new OracleParameter(":STATE", OracleType.Blob),
new OracleParameter(":STATUS", OracleType.Number, 22),
new OracleParameter(":UNLOCKED", OracleType.Number, 22),
new OracleParameter(":BLOCKED", OracleType.Number, 22),
new OracleParameter(":INFO", OracleType.NClob),
new OracleParameter(":MODIFIED", OracleType.DateTime),
new OracleParameter(":OWNER_ID", OracleType.Char, 36),
new OracleParameter(":OWNED_UNTIL", OracleType.DateTime),
new OracleParameter(":NEXT_TIMER", OracleType.DateTime),
new OracleParameter(":FORMID", OracleType.NVarChar, 100),
new OracleParameter(":CREATEID", OracleType.NVarChar, 100),
new OracleParameter(":CREATENAME", OracleType.NVarChar, 100),
new OracleParameter(":EDITID", OracleType.NVarChar, 100),
new OracleParameter(":EDITNAME", OracleType.NVarChar, 100)
};
pageparm[0].Value = OracleDataProvider.GetValue(model.INSTANCE_ID); //
pageparm[1].Value = OracleDataProvider.GetValue(model.STATE); //
pageparm[2].Value = OracleDataProvider.GetValue(model.STATUS); //
pageparm[3].Value = OracleDataProvider.GetValue(model.UNLOCKED); //
pageparm[4].Value = OracleDataProvider.GetValue(model.BLOCKED); //
pageparm[5].Value = OracleDataProvider.GetValue(model.INFO); //
pageparm[6].Value = OracleDataProvider.GetValue(model.MODIFIED); //
pageparm[7].Value = OracleDataProvider.GetValue(model.OWNER_ID); //
pageparm[8].Value = OracleDataProvider.GetValue(model.OWNED_UNTIL); //
pageparm[9].Value = OracleDataProvider.GetValue(model.NEXT_TIMER); //
pageparm[10].Value = OracleDataProvider.GetValue(model.FORMID); //
pageparm[11].Value = OracleDataProvider.GetValue(model.CREATEID); //创建人ID
pageparm[12].Value = OracleDataProvider.GetValue(model.CREATENAME); //创建人姓名
pageparm[13].Value = OracleDataProvider.GetValue(model.EDITID); //下一个审核人ID
pageparm[14].Value = OracleDataProvider.GetValue(model.EDITNAME); //下一个审核人姓名
return(ExecuteSQL(conn, insSql, pageparm));
}
static DataProvider()
{
DataProvider dataProvider;
if (Common.GetDBType == "MsSql")
{
dataProvider = new SqlDataProvider();
}
else if (Common.GetDBType == "Access")
{
dataProvider = new AccessDataProvider();
}
else if (Common.GetDBType == "Oracle")
{
dataProvider = new OracleDataProvider();
}
else
{
throw new ApplicationException("数据库配置不对！");
}
_defaultInstance = dataProvider;
}
internal StringBuilder LlamadaBase()
{
int parametersCount = 0;
short localMode = 0;
parametersCount = this.transaccion.Parameters.Count;
localMode = this.transaccion.ExecMode;
OracleDataProvider dataProvider;
if (parametersCount > 0)
{
dataProvider = new OracleDataProvider(this.transaccion.Source, this.transaccion.Name, parametersCount, localMode);
for (int i = 0; i < parametersCount; i++)
{
if (this.transaccion.Parameters[i].Flag)
{
dataProvider.AddParameter(this.transaccion.Parameters[i].Name,
this.transaccion.Parameters[i].Direction,
this.transaccion.Parameters[i].Type,
this.transaccion.Parameters[i].Size,
this.transaccion.Parameters[i].Value);
}
else
{
dataProvider.AddParameter(this.transaccion.Parameters[i].Name,
this.transaccion.Parameters[i].Direction,
this.transaccion.Parameters[i].Type,
this.transaccion.Parameters[i].Size);
}
}
}
else
{
dataProvider = new OracleDataProvider(this.transaccion.Source, this.transaccion.Name, localMode);
}
IDataReader dataReader = null;
// Almacena objeto JSON
StringBuilder sb = new StringBuilder();
sb.Append("{ \"respuesta\" : {");
if (InferExecType(this.transaccion.ExecType) == ExecType.NoResultSet)
{
int rowAffect = dataProvider.ExecuteNoResultSet();
if (rowAffect == -1)
{
goto CreateRsErrorJson;
}
sb.Append(dataProvider.BindParametersOutValuesString());
}
else
{
// xlv 20070119 IDataReader dataReader = dataProvider.ExecuteResultSet();
dataReader = dataProvider.ExecuteResultSet();
// GMV 10/03/2020
// Pasar de DataReader a JSON
/*
* Si la ejecuci�n no hubo errores
*/
if (dataReader != null)
{
sb.Append("\"cursor\": [");
int filasDataReader = 0;
int numCampos = dataReader.FieldCount;
int idxCampo = 0;
string[] titulos = new string[numCampos];
string[] tipos = new string[numCampos];
for (idxCampo = 0; idxCampo < numCampos; idxCampo++)
{
titulos[idxCampo] = dataReader.GetName(idxCampo);
tipos[idxCampo] = dataReader.GetFieldType(idxCampo).Name;
}
while (dataReader.Read())
{
++filasDataReader;
sb.Append("{");
for (idxCampo = 0; idxCampo < numCampos; idxCampo++)
{
switch (tipos[idxCampo])
{
case "Int64":
sb.Append(String.Format("\"{0}\" : {1}", titulos[idxCampo], dataReader.GetValue(idxCampo)));
break;
case "String":
sb.Append(String.Format("\"{0}\" : \"{1}\"", titulos[idxCampo], dataReader.GetValue(idxCampo)));
break;
default:
sb.Append(String.Format("\"{0}\" : \"{1}\"", titulos[idxCampo], dataReader.GetValue(idxCampo)));
break;
}
sb.Append(",");
}
sb.Remove(sb.Length - 1, 1);
sb.Append("}");
sb.Append(",");
}
if (filasDataReader > 0)
{
sb.Remove(sb.Length - 1, 1);
}
sb.Append("]");
string parametrosSalida = dataProvider.BindParametersOutValuesString();
if (!parametrosSalida.Equals(string.Empty))
{
sb.Append(",");
sb.Append(parametrosSalida);
}
}
else
{
goto CreateRsErrorJson;
}
}
if (dataReader != null)
{
dataReader.Close();
dataReader = null;
}
goto CloseConn;
CreateRsErrorJson:
sb.Append("\"error\": {");
sb.Append(String.Format("\"{0}\" : \"{1}\",", "Number", dataProvider.Error.Number));
sb.Append(String.Format("\"{0}\" : \"{1}\",", "Source", dataProvider.Error.Source));
sb.Append(String.Format("\"{0}\" : \"{1}\"", "Message", dataProvider.Error.Message));
sb.Append("}");
CloseConn:
dataProvider.CloseConnection();
dataProvider = null;
sb.Append("}}");
return(sb);
}
/// <summary>
/// Get connection string of EA database
/// </summary>
/// <param name="rep"></param>
/// <param name="provider"></param>
/// <returns></returns>
/// string dsnName = "DSN=MySqlEa;Trusted_Connection=Yes;";
// dsnName = "DSN=MySqlEa;";
public static string GetConnectionString(EA.Repository rep, out IDataProvider provider)
{
provider = null;
var(connectionString, dbType) = GetConnectionStringFromRepository(rep);
string dsnConnectionString;
// EAP file
// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\hoData\Work.eap;"
switch (dbType)
{
case "JET":
provider = new AccessDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
if (connectionString.ToLower().EndsWith(".eap") || connectionString.ToLower().EndsWith(".eapx"))
{
return($"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={connectionString};");
}
break;
case @"SQLSVR":
provider = new SqlServerDataProvider("", SqlServerVersion.v2012);
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
case "MYSQL":
provider = new MySqlDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
case "ACCESS2007":
provider = new AccessDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
case "ASA":
provider = new SybaseDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
case "ORACLE":
provider = new OracleDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
case "POSTGRES":
provider = new PostgreSQLDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
}
MessageBox.Show($"Database: {rep.RepositoryType()}\r\nConnectionString:{connectionString} ", "DataBase not supported, only Access (*.eap/*.eapx), SqlServer and MySQL");
return("");
}
/// <summary>
/// 获取数据库连接对象,并打开
/// </summary>
/// <param name="connectionKey">数据库连接关键字</param>
/// <returns></returns>
public OracleConnection GetOracleConnection(string connectionKey)
{
return(OracleDataProvider.CreateOracleConnection(OracleDataProvider.GetConnectionString(connectionKey)));
}
/// <summary>
/// 获取数据库连接对象
/// </summary>
/// <returns></returns>
public OracleConnection GetOracleConnection()
{
return(OracleDataProvider.CreateOracleConnection(OracleDataProvider.GetConnectionString("ContextOracleConnection")));
}
