private string tabCols(string tab)
{
string c = "";
string col = "";
DB2Command cmd = new DB2Command("select colname from syscat.columns where tabname = '" + tab + "'", connect);
if (!connect.IsOpen)
{
connect.Open();
}
using (DB2DataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
c = dr.GetString(0);
if (isFK(c, tab))
{
col = col + " " + script_FK(c, tab);
}
if (isPK(c, tab))
{
col = col + " " + scriptPk(c, tab);
}
if (isIND(c, tab))
{
col = col + " " + scriptInd(c, tab);
}
}
dr.Close();
}
connect.Close();
return(col);
}
public void Release()
{
if (conn != null)
{
conn.Close();
}
}
public static bool queryUser(string user, string pass)
{
DataTable dt = new DataTable();
string queryUser = "******"USER\" u ";
queryUser += "LEFT JOIN CGS.USER_PASSWORD p ";
queryUser += "ON p.USER_KEY=u.USER_KEY ";
queryUser += "WHERE u.USER_ID='" + user + "'";
ConnectDB2CGS.Open();
DB2DataAdapter adapter = new DB2DataAdapter(queryUser, ConnectDB2CGS);
adapter.Fill(dt);
ConnectDB2CGS.Close();
string userId = dt.Rows[0][0].ToString();
string usuario = dt.Rows[0][1].ToString();
string password = dt.Rows[0][2].ToString();
string usuarioFormateado = usuario.Replace(" ", "");
if ((Hash.getHash(pass, password)) && (user == usuarioFormateado))
{
return(true);
}
else
{
return(false);
}
}
public async Task <User> GetUser(string userId, CancellationToken ct = default)
{
User returnUser = new User();
try
{
if (!_conn.IsOpen)
{
_conn.Open();
}
DB2Transaction trans = _conn.BeginTransaction();
DB2Command cmd = _conn.CreateCommand();
try
{
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"SELECT * FROM USER_TABLE WHERE ID = '{userId}'";
using (DB2DataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
await reader.ReadAsync();
returnUser.FirstName = reader[0].ToString().Trim();
returnUser.MiddleName = reader[1].ToString().Trim();
returnUser.LastName = reader[2].ToString().Trim();
returnUser.UserId = reader[3].ToString().Trim().Replace(".", "");
}
else
{
return(new User());
}
}
cmd.Transaction.Commit();
return(returnUser);
}
catch (Exception ex)
{
cmd.Transaction.Rollback();
throw ex;
}
}
catch (Exception ex)
{
if (_conn.IsOpen)
{
_conn.Close();
}
throw ex;
}
finally
{
_conn.Close();
}
}
public void Close()
{
try
{
conn.Close();
}
catch { }
}
public void Close()
{
try
{
Conn.Close();
}
catch (Exception)
{
throw;
}
}
public Task <IList <string> > GetOwners()
{
IList <string> owners = new List <string>();
using (var sqlCon = new DB2Connection(_connectionStr))
{
sqlCon.Open();
try
{
using (DB2Command tableDetailsCommand = sqlCon.CreateCommand())
{
tableDetailsCommand.CommandText =
"select distinct owner from systables where tabtype in ('T', 'E', 'V', 'S') and owner != 'informix' order by owner";
using (DB2DataReader reader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
{
while (reader.Read())
{
owners.Add(reader.GetString("owner"));
}
}
}
}
finally
{
sqlCon.Close();
}
}
return(Task.FromResult(owners));
}
static public int ExeSqlCmd(string sqlTxt)
{
int iResult = 0;
DB2Connection cn = new DB2Connection();
try
{
if (Common.strConn == null || Common.strConn.Trim() != String.Empty)
{
strConn = GetConnString(constData.DBName);
}
cn = new DB2Connection(strConn);
DB2Command cmd = new DB2Command(sqlTxt, cn);
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
iResult = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
cn.Close();
}
return(iResult);
}
private void ModificarPrimaryKey_Load(object sender, EventArgs e)
{
PantallaPrincipal pn = new PantallaPrincipal();
DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Parent.Parent.Text);
try {
connection.Open();
DB2Command cmd = new DB2Command(@"select tab.tabname from syscat.tables tab inner join syscat.tabconst const on const.tabschema = tab.tabschema and const.tabname = tab.tabname and const.type = 'P' join syscat.keycoluse key on const.tabschema = key.tabschema and const.tabname = key.tabname and const.constname = '" + arbol.SelectedNode.Text + "' where tab.type = 'T' and tab.tabschema like 'DB2ADMIN' group by tab.tabschema, const.constname, tab.tabname order by tab.tabschema, const.constname;", connection);
DB2DataReader buffer = cmd.ExecuteReader();
while (buffer.Read())
{
var nombre_tabla = buffer ["TABNAME"].ToString();
buffer.Close();
richTextBox1.Text = "ALTER TABLE " + nombre_tabla + " DROP PRIMARY KEY;";
break;
}
} catch (DB2Exception ex) {
MessageBox.Show("Error \n" + ex.Message);
}
connection.Close();
TreeNodeCollection cl = arbol.SelectedNode.Parent.Parent.Parent.Nodes [0].Nodes;
foreach (TreeNode tabla in cl)
{
comboBox1.Items.Add(tabla.Text);
}
}
private void caja_SelectedIndexChanged(object sender, EventArgs e)
{
data_set.Rows.Clear();
Expresion.Items.Clear();
PantallaPrincipal pn = new PantallaPrincipal();
DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Text);
try {
connection.Open();
int posicion = caja.SelectedIndex;
string nombre_tabla = caja.Items [posicion].ToString();
DB2Command cmd = new DB2Command(@"SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = '" + nombre_tabla + "';", connection);
DB2DataReader buffer = cmd.ExecuteReader();
while (buffer.Read())
{
var nombre_campo = buffer ["NAME"].ToString();
Expresion.Items.Add(nombre_campo);
}
buffer.Close();
} catch (DB2Exception ex) {
MessageBox.Show("Ha ocurrido un error\n" + ex.Message);
}
connection.Close();
}
private void ingresar_Click(object sender, EventArgs e)
{
string ids = "";
string values = "";
for (int i = 0; i < id.Count; i++)
{
if (i < (id.Count - 1))
{
ids += id [i].Text + ",";
values += "'" + id_caja [i].Text + "',";
}
else
{
ids += id [i].Text;
values += "'" + id_caja [i].Text + "'";
}
}
string query = "INSERT INTO " + arbol_conexiones.SelectedNode.Text + " (" + ids + ") VALUES (" + values + ");";
PantallaPrincipal pn = new PantallaPrincipal();
DB2Connection connection = pn.obtenerConexion(arbol_conexiones.SelectedNode.Parent.Parent.Text);
try {
connection.Open();
DB2Command cmd = new DB2Command(query, connection);
cmd.ExecuteNonQuery();
MessageBox.Show("Registro insertado correctamente");
} catch (DB2Exception ex) {
MessageBox.Show("Ha ocurrido un error al insertar\n" + ex.Message);
}
connection.Close();
}
private void ModificarVista_Load(object sender, EventArgs e)
{
PantallaPrincipal pn = new PantallaPrincipal();
DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Parent.Text);
try {
connection.Open();
DB2Command cmd = new DB2Command(@"select NAME, TEXT from SYSIBM.SYSVIEWS WHERE CREATOR='DB2ADMIN' and NAME='" + arbol.SelectedNode.Text + "';", connection);
DB2DataReader buffer = cmd.ExecuteReader();
while (buffer.Read())
{
var function = buffer ["TEXT"].ToString().ToUpper();
valor = buffer ["NAME"].ToString();
int pl = function.IndexOf("SELECT");
function = function.Substring(pl);
richTextBox1.Text = function;
textBox1.Text = valor;
break;
}
buffer.Close();
} catch (DB2Exception ex) {
MessageBox.Show("Error al mostrar Vista\n" + ex.Message);
}
connection.Close();
}
public void TableCountTest()
{
DbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb");
//var count = OutputRowsCount(conn);
conn = new SQLiteConnection(@"Data Source=C:\Northwind.db3;Version=3;");
conn = new MySqlConnection(@"server=localhost;password=test;User Id=root;Persist Security Info=True;database=northwind");
conn = new FbConnection(@"data source=vpc1;initial catalog=northwind;user id=sysdba;password=masterkey");
conn = new OracleConnection("DATA SOURCE=vpc1;PERSIST SECURITY INFO=True;USER ID=NORTHWIND;password=test");
conn = new Oracle.DataAccess.Client.OracleConnection("DATA SOURCE=vpc1;PERSIST SECURITY INFO=True;USER ID=NORTHWIND;password=test");
conn = new NpgsqlConnection("HOST=localhost;User ID=postgres;PASSWORD=test;DATABASE=northwind");
conn = new DB2Connection(@"DataBase=SAMPLE;USER ID=db2admin;Password=test;Server=VPC1");
conn.Open();
try
{
OutputRowsCount(conn);
}
finally
{
conn.Close();
}
}
public Task <List <Table> > GetTables(string owner)
{
var tables = new List <Table>();
using (var sqlCon = new DB2Connection(_connectionStr))
{
sqlCon.Open();
try
{
using (DB2Command tableDetailsCommand = sqlCon.CreateCommand())
{
tableDetailsCommand.CommandText =
$"select tabname, owner from systables where tabtype in ('T', 'E', 'V', 'S') and owner = '{owner}' order by tabname";
using (DB2DataReader reader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
{
while (reader.Read())
{
tables.Add(new Table
{
Name = reader.GetString("tabname"),
Owner = reader.GetString("owner")
});
}
}
}
}
finally
{
sqlCon.Close();
}
}
return(Task.FromResult(tables));
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">ArrayList</param>
public static void ExecuteSqlTran(ArrayList sqlList, string ConString)
{
bool mustCloseConnection = false;
using (DB2Connection conn = new DB2Connection(ConString))
{
conn.Open();
using (DB2Transaction trans = conn.BeginTransaction())
{
DB2Command cmd = new DB2Command();
try
{
for (int i = 0; i < sqlList.Count; i++)
{
string cmdText = sqlList[i].ToString();
PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, null, out mustCloseConnection);
int val = cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
finally
{
conn.Close();
cmd.Dispose();
}
}
}
}
public Task DisposeAsync()
{
_connection?.Close();
_connection?.Dispose();
_connection = null;
return(Task.FromResult(0));
}
public void CrearTabla(string script)
{
TreeNode node = arbol.SelectedNode;
DB2ConnectionStringBuilder cn = new DB2ConnectionStringBuilder();
cn.UserID = "db2admin";
cn.Password = "******";
cn.Database = node.Parent.Text;
cn.Server = "localhost";
DB2Connection connect = new DB2Connection(cn.ToString());
string query = @"CREATE TABLE " + nombre_tabla.Text + "( " + script + ");";
try {
connect.Open();
DB2Command cmd = new DB2Command(query, connect);
cmd.ExecuteNonQuery();
TreeNode nodo = arbol.SelectedNode.Nodes.Add(nombre_tabla.Text);
nodo.ImageIndex = 2;
nodo.SelectedImageIndex = 2;
nodo.ContextMenuStrip = subMenus [2];
MessageBox.Show("Su tabla ha sido creada correctamente!");
this.Hide();
} catch (DB2Exception e) {
MessageBox.Show("Ha ocurrido un error al crear su tabla!\n" + e.Message);
}
connect.Close();
}
public DataTable Getda_excel(string sql, string path)
{
string constr = "Database=cig;Password=Shaorong01;User ID=db2admin";
DB2Connection conn = new DB2Connection(constr);
try
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
DB2Command sqlcon = new DB2Command(sql, conn);
DB2DataAdapter ds = new DB2DataAdapter(sqlcon);
DataTable dt = new DataTable();
ds.Fill(dt);
return(dt);
}
else
{
return(null);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
finally
{
conn.Close();
}
}
private static DataTable connectAndExecute(string Query, string envId)
{
try
{
var allEnvs = ConfigurationManager.GetSection("connStr") as System.Collections.Specialized.NameValueCollection;
var env = allEnvs[envId];
DataTable results = new DataTable();
_connection = new DB2Connection(env);
_connection.Open();
if (_connection.IsOpen)
{
_command = new DB2Command(Query, _connection);
_reader = _command.ExecuteReader();
results.Load(_reader);
}
_connection.Close();
return(results);
}
catch (Exception ex)
{
MessageBox.Show("Error connecting database: \n" + ex.Message);
return(null);
}
}
private void button3_Click(object sender, EventArgs e)
{
PantallaPrincipal pn = new PantallaPrincipal();
DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Parent.Text);
try {
connection.Open();
if (checkBox2.Checked)
{
DB2Command cmd = new DB2Command("ALTER TABLE " + arbol.SelectedNode.Text + " ALTER COLUMN " + nombre_campo + " DROP NOT NULL;", connection);
cmd.ExecuteNonQuery();
}
else
{
DB2Command cmd = new DB2Command("ALTER TABLE " + arbol.SelectedNode.Text + " ALTER COLUMN " + nombre_campo + " SET NOT NULL;", connection);
cmd.ExecuteNonQuery();
}
MessageBox.Show("Campo modificado");
} catch (DB2Exception ex) {
MessageBox.Show("Error al modificar\n" + ex.Message);
}
connection.Close();
}
private void ModificarForeignKey_Load(object sender, EventArgs e)
{
PantallaPrincipal pn = new PantallaPrincipal();
DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Parent.Parent.Text); try {
connection.Open();
DB2Command cmd = new DB2Command(@"select tabname from syscat.references where constname = '" + arbol.SelectedNode.Text + "';", connection);
DB2DataReader buffer = cmd.ExecuteReader();
while (buffer.Read())
{
var nombre_tabla = buffer ["TABNAME"].ToString();
buffer.Close();
richTextBox1.Text = "ALTER TABLE " + nombre_tabla + " DROP FOREIGN KEY " + arbol.SelectedNode.Text + ";";
break;
}
} catch (DB2Exception ex) {
MessageBox.Show("Error\n" + ex.Message);
}
connection.Close();
TreeNodeCollection cl = arbol.SelectedNode.Parent.Parent.Parent.Nodes [0].Nodes;
foreach (TreeNode tabla in cl)
{
comboBox1.Items.Add(tabla.Text);
}
}
public void Refrescar(string db)
{
DB2ConnectionStringBuilder cn = new DB2ConnectionStringBuilder();
cn.UserID = "db2admin";
cn.Password = "******";
cn.Database = db;
cn.Server = "localhost";
DB2Connection connect = new DB2Connection(cn.ToString());
try {
connect.Open();
CrearConexion(cn.Database);
obtenerTablas(connect);
obtenerIndices(connect);
obtenerProcedimientos(connect);
obtenerFunciones(connect);
obtenerVistas(connect);
obtenerTriggers(connect);
obtenerChecks(connect);
obtenerUsuarios(connect);
obtenerPrimaryKeys(connect);
obtenerForeginKeys(connect);
} catch (DB2Exception error) {
MessageBox.Show("A ocurrido un error!\n" + error.Message);
}
connect.Close();
}
private void button2_Click(object sender, EventArgs e)
{
DB2ConnectionStringBuilder cn = new DB2ConnectionStringBuilder();
cn.UserID = usuario.Text;
cn.Password = contrasena.Text;
cn.Database = name_db.Text;
cn.Server = server.Text;
DB2Connection connect = new DB2Connection(cn.ToString());
try {
connect.Open();
MessageBox.Show("Conexion Exitosa!\n" + "Version servidor: " + connect.ServerVersion + " Base de datos: " + connect.Database.ToString());
CrearConexion(cn.Database);
obtenerTablas(connect);
obtenerIndices(connect);
obtenerProcedimientos(connect);
obtenerFunciones(connect);
obtenerVistas(connect);
obtenerTriggers(connect);
obtenerChecks(connect);
obtenerUsuarios(connect);
obtenerPrimaryKeys(connect);
obtenerForeginKeys(connect);
this.Hide();
} catch (DB2Exception error) {
MessageBox.Show("A ocurrido un error!\n" + error.Message);
}
connect.Close();
}
//public static T GetDataUtil<T>(string query, params object[] paramsList)
//{
// try
// {
// var val = (IDataObject)Activator.CreateInstance<T>();
// using (var informixConn = new DB2Connection(InformixConnectionString))
// {
// using (var selectCommand = new IfxCommand(query))
// {
// selectCommand.Connection = informixConn;
// if (paramsList != null)
// {
// foreach (var param in paramsList)
// {
// switch ((param))
// {
// case string _:
// selectCommand.Parameters.Add(new IfxParameter()).Value = param.ToString();
// break;
// case int _:
// selectCommand.Parameters.Add(new IfxParameter()).Value = int.Parse(param.ToString());
// break;
// }
// }
// }
// informixConn.Open();
// using (var reader = selectCommand.ExecuteReader(CommandBehavior.Default))
// {
// while (reader.Read())
// {
// val.FillData(reader);
// }
// reader.Close();
// reader.Dispose();
// }
// }
// informixConn.Close(); informixConn.Dispose();
// }
// return (T)val;
// }
// catch (Exception ex)
// {
// return default(T);
// }
//}
//public static List<T> GetListData<T>(string query, params string[] paramsList)
//{
// try
// {
// var respone = new List<T>();
// using (var informixConn = new IfxConnection(InformixConnectionString))
// {
// informixConn.Open();
// var selectCommand = new IfxCommand(query) { Connection = informixConn };
// if (paramsList != null)
// {
// foreach (var param in paramsList)
// {
// selectCommand.Parameters.Add(param);
// }
// }
// using (var reader = selectCommand.ExecuteReader(CommandBehavior.Default))
// {
// while (reader.Read())
// {
// var val = (IDataObject)Activator.CreateInstance<T>();
// val.FillData(reader);
// respone.Add((T)val);
// }
// reader.Close();
// reader.Dispose();
// }
// informixConn.Close(); informixConn.Dispose();
// }
// return respone;
// }
// catch (Exception ex)
// {
// return new List<T>();
// }
//}
//public static List<T> GetListDataUtil<T>(string query, params string[] paramsList)
//{
// try
// {
// var respone = new List<T>();
// using (var informixConn = new IfxConnection(InformixConnectionString))
// {
// informixConn.Open();
// var selectCommand = new IfxCommand(query) { Connection = informixConn };
// if (paramsList != null)
// {
// foreach (var param in paramsList)
// {
// selectCommand.Parameters.Add(param);
// }
// }
// using (var reader = selectCommand.ExecuteReader(CommandBehavior.Default))
// {
// while (reader.Read())
// {
// var val = (IDataObject)Activator.CreateInstance<T>();
// val.FillData(reader);
// respone.Add((T)val);
// }
// reader.Close();
// reader.Dispose();
// }
// informixConn.Close(); informixConn.Dispose();
// }
// return respone;
// }
// catch (Exception ex)
// {
// return new List<T>();
// }
//}
//public static T GetStoredProcedureData<T>(string query, params KeyValuePair<string, string>[] paramsList)
//{
// try
// {
// var val = (IDataObject)Activator.CreateInstance<T>();
// using (var informixConn = new IfxConnection(InformixConnectionString))
// {
// var selectCommand = new IfxCommand("", informixConn);
// selectCommand.CommandType = CommandType.StoredProcedure;
// selectCommand.CommandText = query;
// if (paramsList != null)
// {
// foreach (var param in paramsList)
// {
// selectCommand.Parameters.Add(new IfxParameter(param.Key, param.Value));
// }
// }
// informixConn.Open();
// using (var reader = selectCommand.ExecuteReader(CommandBehavior.Default))
// {
// while (reader.Read())
// {
// val.FillData(reader);
// }
// reader.Close();
// reader.Dispose();
// }
// informixConn.Close(); informixConn.Dispose();
// }
// return (T)val;
// }
// catch (Exception ex)
// {
// return default(T);
// }
//}
//public static void SaveData(string query, params string[] paramsList)
//{
// try
// {
// using (var informixConn = new IfxConnection(InformixConnectionString))
// {
// using (var selectCommand = new IfxCommand(query))
// {
// selectCommand.Connection = informixConn;
// if (paramsList != null)
// {
// foreach (var param in paramsList)
// {
// selectCommand.Parameters.Add(new IfxParameter()).Value = param;
// }
// }
// informixConn.Open();
// selectCommand.ExecuteNonQuery();
// selectCommand.Parameters.Clear();
// }
// informixConn.Close(); informixConn.Dispose();
// }
// }
// catch (Exception ex)
// {
// throw;
// }
//}
//public static void SaveDataWindowApp(string query, params string[] paramsList)
//{
// try
// {
// using (var informixConn = new IfxConnection(InformixConnectionString))
// {
// using (var selectCommand = new IfxCommand(query))
// {
// selectCommand.Connection = informixConn;
// if (paramsList != null)
// {
// foreach (var param in paramsList)
// {
// selectCommand.Parameters.Add(new IfxParameter()).Value = param;
// }
// }
// informixConn.Open();
// selectCommand.ExecuteNonQuery();
// selectCommand.Parameters.Clear();
// }
// informixConn.Close(); informixConn.Dispose();
// }
// }
// catch (Exception ex)
// {
// throw;
// }
//}
public static void ExecuteQuery(string query, params string[] paramsList)
{
try
{
using (var informixConn = new DB2Connection(InformixConnectionString))
{
using (var selectCommand = new DB2Command(query))
{
selectCommand.Connection = informixConn;
if (paramsList != null)
{
foreach (var param in paramsList)
{
selectCommand.Parameters.Add(new DB2Parameter()).Value = param;
}
}
informixConn.Open();
selectCommand.ExecuteNonQuery();
selectCommand.Parameters.Clear();
}
informixConn.Close(); informixConn.Dispose();
}
}
catch (Exception ex)
{
}
}
/// <summary>
/// Todas las Acciones realizadas al invocar la funcion de cuarentena en Cogiscan
/// </summary>
/// <param name="itemId"></param>
/// <param name="titulo"></param>
/// <returns></returns>
#region Acciones_Cuarentena
//***************************//
// Agregar item a Cuarentena //
//***************************//
public static bool SetQuarantine(string itemId, string titulo)
{
try
{
string[] itemInfo = WS.getContainerInfo(itemId);
string pnKey = getPNKey(itemInfo[0]);
string descripcion = "Esperando validación de Control de Calidad";
string setQuarantine = "INSERT INTO CGS.\"QUARANTINE_RULE\"";
setQuarantine += " (CREATE_TMST,CREATE_USER_ID,TITLE,DESCRIPTION,ITEM_CLASS_KEY,PART_NUMBER_KEY,ITEM_ID,QUARANTINE_RULE_ENABLED,LAST_UPDATE_TMST,LAST_UPDATE_USER_ID)";
setQuarantine += " VALUES";
setQuarantine += " (CURRENT_TIMESTAMP,'" + Global.userLogged + "','" + titulo + "','" + descripcion + "',1,'" + pnKey + "','" + itemId + "','Y',CURRENT_TIMESTAMP,'" + Global.userLogged + "')";
DB2Command cmd = new DB2Command();
cmd.Connection = ConnectDB2CGS;
cmd.CommandText = setQuarantine;
ConnectDB2CGS.Open();
if (cmd.ExecuteNonQuery() == 1)
{
ConnectDB2CGS.Close();
string lastQuarantine = "SELECT MAX(QUARANTINE_RULE_KEY) FROM CGS.\"QUARANTINE_RULE\"";
DataTable maxQid = new DataTable();
DB2DataAdapter da = new DB2DataAdapter(lastQuarantine, ConnectDB2CGS);
da.Fill(maxQid);
ConnectDB2CGSDW.Open();
//Inserto en tabla QUARANTINE_RULE_HIST
if (insertQRhistory(maxQid.Rows[0][0].ToString(), titulo, descripcion, itemInfo[0], itemId, "Y", Global.userLogged))
{
//Inserto en tabla ITEM_HISTORY_025
insertIhistory(itemId, "QUARANTINE LOCK", Global.userLogged);
}
ConnectDB2CGSDW.Close();
return(true);
}
else
{
return(false);
}
return(true);
}
catch (Exception e)
{
Console.Write(e.Message);
return(false);
}
}
private void button1_Click(object sender, EventArgs e)
{
string expresiones = obtenerQuery();
string query = "";
int posicion = caja.SelectedIndex;
int posicion2 = caja2.SelectedIndex;
if (posicion == -1 || posicion2 == -1)
{
MessageBox.Show("Te falta seleccionar correctamente el tipo de indice\n");
return;
}
string nombre_tabla = caja.Items [posicion].ToString();
string tipo_indice = caja2.Items [posicion2].ToString();
if (tipo_indice == "NO UNICO")
{
query = "CREATE INDEX " + nombre_indice.Text + " ON " + nombre_tabla + " " + expresiones + ";";
}
else
{
query = "CREATE UNIQUE INDEX " + nombre_indice.Text + " ON " + nombre_tabla + " " + expresiones + ";";
}
PantallaPrincipal pn = new PantallaPrincipal();
DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Text);
try {
connection.Open();
DB2Command cmd = new DB2Command(query, connection);
cmd.ExecuteNonQuery();
MessageBox.Show("Indice creado correctamente");
TreeNode nodo = arbol.SelectedNode.Nodes.Add(nombre_indice.Text);
nodo.ImageIndex = 3;
nodo.SelectedImageIndex = 3;
nodo.ContextMenuStrip = subMenus [4];
connection.Close();
this.Hide();
} catch (DB2Exception ex) {
MessageBox.Show("Error al crear el indice\n" + ex.Message);
}
connection.Close();
}
public static List <T> GetListDataUtil <T>(string query, params string[] paramsList)
{
try
{
Console.Out.WriteLine("1");
var respone = new List <T>();
Console.Out.WriteLine("2");
string InformixConnectionString = "Server=10.90.18.232:1540;UID=ttester;PWD=Trc2019!;Database=TIRS;TimeOut=300";
Console.Out.WriteLine("3");
using (var informixConn = new DB2Connection(InformixConnectionString))
{
Console.Out.WriteLine("4");
informixConn.Open();
Console.Out.WriteLine("5");
var selectCommand = new DB2Command(query)
{
Connection = informixConn
};
if (paramsList != null)
{
foreach (var param in paramsList)
{
selectCommand.Parameters.Add(param);
}
}
using (var reader = selectCommand.ExecuteReader(CommandBehavior.Default))
{
while (reader.Read())
{
var val = (IDataObject)Activator.CreateInstance <T>();
val.FillData(reader);
respone.Add((T)val);
Console.Out.WriteLine("Filling data----");
}
reader.Close();
reader.Dispose();
}
informixConn.Close(); informixConn.Dispose();
}
Console.Out.WriteLine("Return response----22");
return(respone);
}
catch (Exception ex)
{
Console.WriteLine("Exceptin Asfand");
Console.Out.WriteLine(ex);
return(new List <T>());
}
}
private void button2_Click(object sender, EventArgs e)
{
DataTable ds;
DB2DataAdapter adapter;
DB2Connection connection = obtenerConexion(bd);
try {
connection.Open();
DB2Command cmd = new DB2Command(richTextBox1.Text, connection);
adapter = new DB2DataAdapter(cmd);
ds = new DataTable();
adapter.Fill(ds);
dataGridView1.DataSource = ds;
connection.Close();
} catch (DB2Exception ex) {
MessageBox.Show("Error !\n" + ex.Message);
}
connection.Close();
}
void IProvider.CreateDatabase()
{
if (DatabaseExists())
{
throw SqlClient.Error.CreateDatabaseFailedBecauseSqlCEDatabaseAlreadyExists(dbName);
}
//创建数据库文件
var connection = new DB2Connection(GetConnectionString(null));
connection.Open();
var sqlBuilder = new DB2Builder(this);
var transaction = connection.BeginTransaction();
Execute(connection, transaction, sqlBuilder.GetCreateDatabaseCommand(dbName, null));
try
{
MetaModel model = services.Model;
foreach (MetaTable table in model.GetTables())
{
string createTableCommand = sqlBuilder.GetCreateTableCommand(table);
if (!string.IsNullOrEmpty(createTableCommand))
{
Execute(connection, transaction, createTableCommand);
}
}
foreach (MetaTable table in model.GetTables())
{
foreach (string commandText in sqlBuilder.GetCreateForeignKeyCommands(table))
{
if (!string.IsNullOrEmpty(commandText))
{
Execute(connection, transaction, commandText);
}
}
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
public ICustomActivityResult Execute()
{
DB2Connection con = null;
DB2DataAdapter adapter = null;
DataTable dt = new DataTable("resultSet");
try
{
DB2ConnectionStringBuilder cnb = new DB2ConnectionStringBuilder(ConnectionString);
if (!string.IsNullOrEmpty(UserName))
{
cnb.UserID = UserName;
cnb.Password = Password;
}
con = new DB2Connection(cnb.ConnectionString);
con.Open();
using (DB2Command command = new DB2Command(Query, con))
{
command.CommandType = System.Data.CommandType.Text;
command.CommandTimeout = Convert.ToInt32(TimeInSeconds);
adapter = new DB2DataAdapter(command);
adapter.Fill(dt);
}
return(this.GenerateActivityResult(dt));
}
finally
{
if (adapter != null)
{
adapter.Dispose();
}
adapter = null;
if (con != null)
{
con.Close();
con.Dispose();
}
con = null;
dt.Dispose();
dt = null;
}
}