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