public static TrasenClasses.DatabaseAccess.RelationalDatabase GetDb_InFormix()
{
string connectionString = "";//"Host=192.168.0.50; Service=8888; Server=cxhosp_tcp; Database=pu_hos; User ID=ypgl; Password=ypgl2233; Protocol=olsoctcp;";
//connectionString = "PROVIDER=MSDASQL;Host=192.168.0.50;Server=cxhosp_tcp;Service=8888;Protocol=olsoctcp;Database=pu_hos;Uid=ypgl;Pwd=ypgl2233";
////connectionString = "Provider=ifxoledbc;Data Source=pu_hos@cxhosp_tcp;User ID=ypgl;Password=ypgl2233";
connectionString =
"DSN=oldhis_official;UID=ypgl;PWD=ypgl2233;CommitOnDisconnect='Yes'";
//"DSN=cxhosp_tcp;UID=ypgl;PWD=ypgl2233;CommitOnDisconnect='Yes'";
////IBM.Data.Informix.IfxConnection conn = new IfxConnection(ConString))
//IBM INFORMIX 3.82 32 BIT
//connectionString = "Database=pu_hos;Host Name=192.168.0.50;Server=192.168.0.50:8888;User ID=ypgl;Password=ypgl2233;";
////connectionString = "Driver={IBM INFORMIX 3.82 32 BIT};Server=cxhosp_tcp:8888; Database=pu_hos; User ID=ypgl=ypgl;Password=ypgl2233;";
////connectionString = "Database=pu_hos;Server=cxhosp_tcp; Service=8888;User ID=ypgl;Password=ypgl2233;";
//connectionString = "Dsn=whzxyyoldhis;uid=ypgl;database=pu_hos;host=192.168.0.50;srvr=cxhosp_tcp;serv=8888;UID=ypgl;PWD=ypgl2233;pro=olsoctcp;cloc=en_US.819;dloc=en_US.819;vmb=0;curb=0;scur=0;icur=0;oac=1;optofc=0;rkc=0;odtyp=0;ddfp=0;dnl=0;rcwc=0";
//TrasenFrame.Forms.FrmMdiMain.Jgbm = 1001;
//TrasenFrame.Forms.FrmMdiMain.CurrentDept = new Department();
//TrasenFrame.Forms.FrmMdiMain.CurrentUser = new User();
RelationalDatabase db = new Odbc();
db.Initialize(connectionString);
return(db);
}
private static RegistryKey CriaDsn(string dsnName, Odbc odbc)
{
// Verifica se o ODBC já existe
if (DsnExiste(dsnName))
{
throw new Exception("Já existe DSN com este nome");
}
var driverKey = Registry.LocalMachine.CreateSubKey(OdbcinstIniRegPath + odbc.Driver);
if (driverKey == null)
{
throw new Exception(string.Format("Driver de fonte de dados {0} não existe", odbc.Driver));
}
var datasourcesKey = Registry.LocalMachine.CreateSubKey(OdbcIniRegPath + "ODBC Data Sources");
if (datasourcesKey == null)
{
throw new Exception("Chave do Registro ODBC para fontes de dados não existe.");
}
datasourcesKey.SetValue(dsnName, odbc.Driver);
RegistryKey dsnKey = Registry.LocalMachine.CreateSubKey(OdbcIniRegPath + dsnName);
if (dsnKey == null)
{
throw new Exception("Chave do Registro ODBC para o DSN não foi criado.");
}
return(dsnKey);
}
// Método que cria o DSN de acordo com o banco utilizado
public static void CreateDsn(string dsnName, Odbc odbc)
{
var dsnKey = CriaDsn(dsnName, odbc);
var driverKey = CaminhoDriverOdbc(odbc);
switch (odbc.Banco)
{
case "Oracle":
CreateDsnOracle(dsnKey, driverKey, dsnName, odbc);
break;
case "MySQL":
CreateDsnMySql(dsnKey, driverKey, dsnName, odbc);
break;
case "SqlServer":
CreateDsnSqlServer(dsnKey, driverKey, dsnName, odbc);
break;
case "PostgreSQL":
CreateDsnPostgreSql(dsnKey, driverKey, dsnName, odbc);
break;
}
TestaConexaoOdbc(dsnName, odbc);
}
/// <summary>
/// 根据程序集名称和数据访问对象类型创建一个新的数据访问对象实例。
/// </summary>
/// <param name="HelperAssembly">程序集名称</param>
/// <param name="HelperType">数据访问对象类型</param>
/// <param name="ConnectionString">连接字符串</param>
/// <returns>数据访问对象</returns>
public static AdoHelper GetDBHelper(string HelperAssembly, string HelperType, string ConnectionString)
{
AdoHelper helper = null;// CommonDB.CreateInstance(HelperAssembly, HelperType);
if (HelperAssembly == "PWMIS.Core")
{
switch (HelperType)
{
case "PWMIS.DataProvider.Data.SqlServer": helper = new SqlServer(); break;
case "PWMIS.DataProvider.Data.Oracle": helper = new Oracle(); break;
case "PWMIS.DataProvider.Data.OleDb": helper = new OleDb(); break;
case "PWMIS.DataProvider.Data.Odbc": helper = new Odbc(); break;
case "PWMIS.DataProvider.Data.Access": helper = new Access(); break;
//case "PWMIS.DataProvider.Data.SqlServerCe": helper = new SqlServerCe(); break;
default: helper = new SqlServer(); break;
}
}
else
{
helper = CommonDB.CreateInstance(HelperAssembly, HelperType);
}
helper.ConnectionString = ConnectionString;
return(helper);
}
private static RegistryKey CaminhoDriverOdbc(Odbc odbc)
{
var driverKey = Registry.LocalMachine.CreateSubKey(OdbcinstIniRegPath + odbc.Driver);
if (driverKey == null)
{
throw new Exception(string.Format("Driver de fonte de dados {0} não existe", odbc.Driver));
}
return(driverKey);
}
private static bool ChangeObjectClassifier(Odbc odbc, int srcId, int targetId)
{
// change type of an object (classifier) to target
string sql = $"update t_object set Classifier = {targetId} " +
$" where Classifier = {srcId} ;";
if (odbc.OdbcCmd(sql) == false)
{
return(false);
}
return(true);
}
private static bool ChangeTypeOperationParameter(Odbc odbc, int srcId, EA.Element target)
{
// change type to target
string sql = $"update t_operationparams set Classifier = '{target.ElementID}', Type = '{target.Name}'" +
$" where Classifier = '{srcId}' ;";
if (odbc.OdbcCmd(sql) == false)
{
return(false);
}
return(true);
}
private static bool ChangeTypeAttribute(Odbc odbc, int srcId, EA.Element target)
{
// change type of attribute to target
string sql = $"update t_attribute set classifier = '{target.ElementID}', Type = '{target.Name}'" +
$" where classifier = '{srcId}' ;";
if (odbc.OdbcCmd(sql) == false)
{
return(false);
}
return(true);
}
/// <summary>
/// Change occurrence in diagram of source to target. It doesn't change the current diagram.
/// </summary>
/// <param name="odbc"></param>
/// <param name="diaId"></param>
/// <param name="srcId"></param>
/// <param name="targetId"></param>
/// <returns></returns>
private static bool ChangeDiagramUsage(Odbc odbc, int diaId, int srcId, int targetId)
{
// all source elements on diagrams are to exchange with target
// as starting point and as target point
string sql = $"Update t_diagramobjects set Object_ID = {targetId}" +
$" where object_id = {srcId} AND diagram_id <> {diaId} ;";
if (odbc.OdbcCmd(sql) == false)
{
return(false);
}
return(true);
}
/// <summary>
/// Sets the parent id, package id to the new target
/// </summary>
/// <param name="odbc"></param>
/// <param name="srcId"></param>
/// <param name="targetId"></param>
/// <param name="targetPkgId"></param>
/// <returns></returns>
private static bool ChangeObjectParent(Odbc odbc, int srcId, int targetId, int targetPkgId)
{
// change parent_id to target
// change package_id to target package
string sql = "update t_object set ParentID = " + targetId +
" , Package_id = " + targetPkgId +
" where parentID = " + srcId + " ;";
if (odbc.OdbcCmd(sql) == false)
{
return(false);
}
return(true);
}
private static bool DeleteSource(EA.Repository rep, Odbc odbc, EA.Element srcEl, EA.Element targetEl)
{
EA.Package pkg = rep.GetPackageByID(srcEl.PackageID);
short i = 0;
int srcId = srcEl.ElementID;
foreach (EA.Element el in pkg.Elements)
{
if (srcId == el.ElementID)
{
pkg.Elements.DeleteAt(i, true);
return(true);
}
i += 1;
}
return(false);
}
private static bool Delete_t_xref(Odbc odbc, string srcGuid)
{ // delete GUID in description
// search for all description fields in xref where the source GUID is used
string sql = "SELECT x.description, x.xrefID " +
" FROM t_xref x" +
$" WHERE x.description like '%{srcGuid.Trim()}%';";
try
{ odbc.Rs.Close(); }
catch (Exception)
{
// ignored
}
odbc.Rs.Open(sql, odbc.Cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
while (odbc.Rs.EOF == false)
{
// delete the source ID
string description = odbc.Rs.Fields[0].Value.ToString();
description = description.Replace("," + srcGuid, "");
description = description.Replace(srcGuid + ",", "");
description = description.Replace(srcGuid, "");
// change source class to target of found elements if there are still signals on flow
// if no signals on flow delete t_xref entry
if (description == "")
{
sql = "delete from t_xref where xrefID = '" + odbc.Rs.Fields[1].Value.ToString() + "' " +
" ;";
}
else
{
sql = "Update t_xref" +
" set description = '" + description + "' " +
" where xrefID = '" + odbc.Rs.Fields[1].Value.ToString() + "' " +
" ;";
}
if (odbc.OdbcCmd(sql) == false)
{
odbc.Rs.Close();
return(false);
}
odbc.Rs.MoveNext();
}
odbc.Rs.Close();
return(true);
}
private static void TestaConexaoOdbc(string dsnName, Odbc odbc)
{
OdbcConnection conexaoBase;
conexaoBase = new OdbcConnection(String.Format("DSN={0};UID={1};PWD={2}",
new object[] { dsnName, odbc.Usuario, odbc.Senha }));
try
{
conexaoBase.Open();
conexaoBase.Close();
}
catch (Exception ex)
{
RemoveDsn(dsnName);
throw new Exception(ex.Message);
}
}
/// <summary>
/// Change the connector from source to target
/// - As source
/// - As target
/// </summary>
/// <param name="odbc"></param>
/// <param name="srcId"></param>
/// <param name="targetId"></param>
/// <returns></returns>
private static bool ChangeConnector(Odbc odbc, int srcId, int targetId)
{
// Change the connection from slave to master in all connectors
// as starting point and as target point
// It solves:
// - Inheritance
// - Source and target of a connection
// Note: An inheritance/generalization from source signal to target signal is not moved
// object is target of connector
string sql = "Update t_connector set end_Object_ID = " + targetId + " where EXISTS (" +
" Select * " +
" From t_object o, t_connector c " +
" Where " +
" c.Connector_type <> 'Dependency' " +
" AND c.End_Object_ID = " + srcId +
" AND o.object_id = c.start_object_id " +
" AND c.connector_ID = t_connector.connector_id " +
" );";
if (odbc.OdbcCmd(sql) == false)
{
return(false);
}
// object is source of connector
sql = "Update t_connector set start_Object_ID = " + targetId + " where EXISTS (" +
" Select * " +
" From t_object o, t_connector c " +
" Where " +
" c.Connector_type <> 'Dependency' " +
" AND c.Start_Object_ID = " + srcId +
" AND o.object_id = c.end_object_id " +
" AND c.connector_ID = t_connector.connector_id " +
" AND c.Connector_Type <> 'Generalization' " +
" );";
if (odbc.OdbcCmd(sql) == false)
{
return(false);
}
return(true);
}
private static bool Change_t_xref(Odbc odbc, string srcGuid, string targetGuid)
{ // Change GUID in description
// Change GUID in client
// for the following types of behavior
// -
// search for all description fields in xref where the source GUID is used
string sql = "SELECT x.description, x.xrefID " +
" FROM t_xref x" +
$" WHERE x.description like '%{srcGuid}%' ;";
odbc.Rs.Open(sql, odbc.Cn, ADODB.CursorTypeEnum.adOpenForwardOnly,
ADODB.LockTypeEnum.adLockOptimistic, -1);
while (odbc.Rs.EOF == false)
{
// replace the source ID by the target ID
string description = odbc.Rs.Fields[0].Value.ToString();
description = description.Replace(srcGuid, targetGuid);
odbc.Rs.Update("description", description);
odbc.Rs.MoveNext();
}
odbc.Rs.Close();
// replace the client ID by the target ID
sql = "Update t_xref" +
" set client = '" + targetGuid + "' " +
" where client = '" + srcGuid + "' " +
" ;";
if (odbc.OdbcCmd(sql) == false)
{
return(false);
}
return(true);
}
public static void CreateDsnOracle(RegistryKey dsnKey, RegistryKey driverKey, string dsnName, Odbc odbc)
{
dsnKey.SetValue("Description", odbc.Banco);
dsnKey.SetValue("Driver", driverKey);
dsnKey.SetValue("UserID", odbc.Usuario);
dsnKey.SetValue("Password", odbc.Senha);
dsnKey.SetValue("ServerName", odbc.Dsn);
dsnKey.SetValue("Trusted_Connection", "Yes");
}
private static bool DeleteInvalidSignals(Odbc odbc)
{
// search for all description fields in xref where the source GUID is used
string sql = "SELECT has.ea_GUID, x.xrefID, x.description " +
" FROM ho_ConHasItem has, t_connector con, t_xref x" +
" WHERE has.object_id = 0 AND " +
" has.connector_id = con.connector_id AND " +
" x.client = con.ea_GUID " +
" order by x.xrefID " +
";";
string xrefIdOld = "";
string description = "";
try
{ odbc.Rs.Close(); }
catch (Exception)
{
// ignored
}
odbc.Rs.Open(sql, odbc.Cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
while (odbc.Rs.EOF == false)
{
string srcGuid = odbc.Rs.Fields[0].Value.ToString().Trim();
//check GUID is a GUID
if (srcGuid.Length != 38 | srcGuid.Substring(0, 1) != "{")
{
odbc.Rs.MoveNext();
continue;
}
// delete the source ID
// check if new xref_id, elsewhere use old description
if (xrefIdOld != odbc.Rs.Fields[1].Value.ToString())
{
description = odbc.Rs.Fields[2].Value.ToString();
xrefIdOld = odbc.Rs.Fields[1].Value.ToString();
}
// stored procedure works only in the range to 7994 bytes
if (description.Length <= 7990)
{
description = description.Replace("," + srcGuid, "");
description = description.Replace(srcGuid + ",", "");
description = description.Replace(srcGuid, "");
// change source class to target of found elements if there are still signals on flow
// if no signals on flow delete t_xref entry
if (description == "")
{
sql = "delete from t_xref where xrefID = '" + odbc.Rs.Fields[1].Value.ToString().Trim() + "' " +
" ;";
}
else
{
sql = "Update t_xref" +
" set description = '" + description + "' " +
" where xrefID = '" + odbc.Rs.Fields[1].Value.ToString().Trim() + "' " +
" ;";
}
if (odbc.OdbcCmd(sql) == false)
{
odbc.Rs.Close();
return(false);
}
}
odbc.Rs.MoveNext();
}
odbc.Rs.Close();
return(true);
}
/// <summary>
/// 根据Provider字符串获取数据类型
/// </summary>
/// <param name="provider"></param>
/// <returns></returns>
public static DatabaseType GetDatabaseType(string provider)
{
DatabaseType dbType = DatabaseType.None;
provider = provider.ToLower();
if (provider == SQLServer.ToLower() || provider == SQLAzure.ToLower())
{
dbType = DatabaseType.SqlServer;
}
else if (provider == SqlServerCe.ToLower() || provider == SqlServerCe40.ToLower())
{
dbType = DatabaseType.SqlCe;
}
else if (provider == SQLite.ToLower())
{
dbType = DatabaseType.SQLite;
}
else if (provider == MySql.ToLower())
{
dbType = DatabaseType.MySql;
}
else if (provider == Oracle.ToLower())
{
dbType = DatabaseType.Oracle;
}
else if (provider == OracleDataAccess.ToLower())
{
dbType = DatabaseType.Oracle;
}
else if (provider == OracleManagedDataAccess.ToLower())
{
dbType = DatabaseType.Oracle;
}
else if (provider == Access.ToLower())
{
dbType = DatabaseType.Access;
}
else if (provider == OleDb.ToLower())
{
dbType = DatabaseType.OleDb;
}
else if (provider == Odbc.ToLower())
{
dbType = DatabaseType.OleDb;
}
else if (provider == PostgreSQL.ToLower())
{
dbType = DatabaseType.PostgreSQL;
}
else if (provider == DB2.ToLower())
{
dbType = DatabaseType.DB2;
}
else if (provider == FirebirdSql.ToLower())
{
dbType = DatabaseType.Firebird;
}
else if (provider == SybaseASA.ToLower())
{
dbType = DatabaseType.SybaseASA;
}
else if (provider == SybaseASE.ToLower())
{
dbType = DatabaseType.SybaseASE;
}
else if (provider == SybaseUltraLite.ToLower())
{
dbType = DatabaseType.SybaseUltraLite;
}
else if (provider == DM.ToLower())
{
dbType = DatabaseType.DM;
}
return(dbType);
}
public static Odbc RetornaInformacoesDsnSistema(string nomeOdbc /*, ComandosContatosSql comandosContatosSql*/)
{
Odbc odbc = new Odbc();
// Busca DSN do sistema
var driverKey = Registry.LocalMachine.OpenSubKey(OdbcIniRegPath + nomeOdbc);
if (driverKey != null)
{
var driverKeyDataSource = Registry.LocalMachine.OpenSubKey(OdbcIniRegPath + "ODBC Data Sources");
if (driverKeyDataSource == null)
{
throw new Exception("Erro na utilização do driver para conexão com o banco. Favor Contactar com o suporte da Teclan.");
}
odbc.Nome = nomeOdbc;
odbc.Banco = "MySQL";
odbc.Base = (string)driverKey.GetValue("DATABASE");
odbc.Driver = (string)driverKeyDataSource.GetValue(nomeOdbc);
switch (odbc.Banco)
{
case "Oracle":
odbc.Usuario = (string)driverKey.GetValue("UserID");
odbc.Senha = (string)driverKey.GetValue("Password");
odbc.Dsn = (string)driverKey.GetValue("ServerName");
break;
case "MySQL":
odbc.Usuario = (string)driverKey.GetValue("UID");
odbc.Senha = (string)driverKey.GetValue("PWD");
if (odbc.Usuario == null)
{
odbc.Usuario = (string)driverKey.GetValue("User");
odbc.Senha = (string)driverKey.GetValue("Password");
}
odbc.Ip = (string)driverKey.GetValue("SERVER");
odbc.Porta = (string)driverKey.GetValue("PORT");
odbc.Base = (string)driverKey.GetValue("DATABASE");
break;
case "SqlServer":
odbc.Usuario = (string)driverKey.GetValue("User");
odbc.Senha = (string)driverKey.GetValue("Password");
odbc.Ip = (string)driverKey.GetValue("Server");
odbc.Porta = (string)driverKey.GetValue("PORT");
odbc.Base = (string)driverKey.GetValue("DataBase");
break;
case "PostgreSQL":
odbc.Usuario = (string)driverKey.GetValue("Username");
odbc.Senha = (string)driverKey.GetValue("Password");
odbc.Ip = (string)driverKey.GetValue("Servername");
odbc.Porta = (string)driverKey.GetValue("Port");
odbc.Base = (string)driverKey.GetValue("DataBase");
break;
}
}
if (odbc.Usuario == null || odbc.Usuario == "")
{
odbc.Usuario = /*comandosContatosSql.OdbcContatosUsuario */ "root";
odbc.Senha = /*comandosContatosSql.OdbcContatosSenha */ "root";
}
// Se a senha do ODBC esta em branco usa a do parametro (caso do Oracle)
if (odbc.Senha == "")
{
odbc.Senha = /*comandosContatosSql.OdbcContatosSenha*/ "";
}
return(odbc);
}
public static void CreateDsnMySql(RegistryKey dsnKey, RegistryKey driverKey, string dsnName, Odbc odbc)
{
dsnKey.SetValue("DATABASE", odbc.Base);
dsnKey.SetValue("DESCFIPTION", dsnName);
dsnKey.SetValue("Driver", driverKey.GetValue("Driver").ToString());
dsnKey.SetValue("UID", odbc.Usuario);
dsnKey.SetValue("PWD", odbc.Senha);
dsnKey.SetValue("SERVER", odbc.Ip);
dsnKey.SetValue("PORT", odbc.Porta);
dsnKey.SetValue("Trusted_Connection", "Yes");
}
public static void CreateDsnPostgreSql(RegistryKey dsnKey, RegistryKey driverKey, string dsnName, Odbc odbc)
{
dsnKey.SetValue("Database", odbc.Base);
dsnKey.SetValue("Description", dsnName);
dsnKey.SetValue("Driver", driverKey);
dsnKey.SetValue("Username", odbc.Usuario);
dsnKey.SetValue("Password", odbc.Senha);
dsnKey.SetValue("Servername", odbc.Ip);
dsnKey.SetValue("Port", odbc.Porta);
dsnKey.SetValue("Trusted_Connection", "Yes");
}
public static bool MoveClassifier(EA.Repository rep, EA.Diagram dia, EA.Element srcEl, EA.Element targetEl)
{
Odbc odbc = new Odbc(rep);
// change each occurrence of source by target
// - flow in t_xref
// - connector in diagram links
// - diagram usage
// - object has parent
// - object has classifier
// - attribute has classifier
// - operation has classifier
// - operation param has classifier
int srcId = srcEl.ElementID;
srcEl.Name = $"{srcEl.Name}_DeleteMe";
srcEl.Update();
int targetId = targetEl.ElementID;
int targetPkgId = targetEl.PackageID;
// change flow in t_xref
if (!Change_t_xref(odbc, srcEl.ElementGUID, targetEl.ElementGUID))
{
return(false);
}
// change connector of diagram flow
if (!ChangeConnector(odbc, srcId, targetId))
{
return(false);
}
// change signal in diagram (source, target, generalization, dependency)
if (!ChangeDiagramUsage(odbc, dia.DiagramID, srcId, targetId))
{
return(false);
}
// change if type is used as parent
if (!ChangeObjectParent(odbc, srcId, targetId, targetPkgId))
{
return(false);
}
// change if type is used as classifier
if (!ChangeObjectClassifier(odbc, srcId, targetId))
{
return(false);
}
// change type of attribute
if (!ChangeTypeAttribute(odbc, srcId, targetEl))
{
return(false);
}
// change type of operation
if (!ChangeTypeOperation(odbc, srcId, targetEl))
{
return(false);
}
// change type is operation parameter
if (!ChangeTypeOperationParameter(odbc, srcId, targetEl))
{
return(false);
}
//Repository.GetProjectInterface().ReloadProject();
string msg = "Delete source element '" + srcEl.Name + "' " + srcEl.Type + "' " + srcEl.Stereotype + " ?";
if (MessageBox.Show(msg, "Proceed with delete?", MessageBoxButtons.YesNo)
== DialogResult.No)
{
return(true);
}
// update project to ensure that EA notice all changes
if (!DeleteSource(rep, odbc, srcEl, targetEl))
{
return(false);
}
return(true);
}
public static void CreateDsnSqlServer(RegistryKey dsnKey, RegistryKey driverKey, string dsnName, Odbc odbc)
{
dsnKey.SetValue("Database", odbc.Base);
dsnKey.SetValue("Description", dsnName);
dsnKey.SetValue("Driver", driverKey);
dsnKey.SetValue("User", odbc.Usuario);
dsnKey.SetValue("Password", odbc.Senha);
dsnKey.SetValue("Server", odbc.Ip);
try
{
dsnKey.SetValue("Port", odbc.Porta);
}
catch (Exception)
{
// ignored
}
dsnKey.SetValue("LastUser", odbc.Usuario);
dsnKey.SetValue("Trusted_Connection", "No");
}
