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);
}
}
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);
}
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();
}
public DBSource LoadSchema(string connectionString)
{
using (var conn = new DB2Connection(connectionString))
{
conn.Open();
var querySourceCategories = GetSchemas(conn, ExcludeSchemas);
foreach (var category in querySourceCategories)
{
category.QuerySources = new List <QuerySource>();
// Load Tables
category.QuerySources.AddRange(GetTables(conn, category.Name));
// Load Views
category.QuerySources.AddRange(GetViews(conn, category.Name));
// Load Procedures
category.QuerySources.AddRange(GetProcedures(conn, category.Name));
// Load Functions
category.QuerySources.AddRange(GetFunctions(conn, category.Name));
// Sort by name
category.QuerySources = category.QuerySources.OrderBy(s => s.Name).ToList();
}
return(new DBSource
{
QuerySources = querySourceCategories.ToList()
});
}
}
/// <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();
}
}
}
}
protected override DbConnection CreateDbConnection()
{
DB2ConnectionStringBuilder connStrBuilder = new DB2ConnectionStringBuilder();
connStrBuilder.Server = HostExePath;
connStrBuilder.Database = Database;
connStrBuilder.UserID = DbUsername;
connStrBuilder.Password = DbPassword;
string connectionString = connStrBuilder.ConnectionString;
/*
* string connectionString = string.Format("Server={0};Database={1};UID={2};PASSWORD={3};", HostExePath, Database, DbUsername, DbPassword);
* if (Port != null && !Port.Equals(""))
* {
* connectionString = string.Format("Server={0};Database={1};Port={2};UID={3};PASSWORD={4};", HostExePath, Database, Port, DbUsername, DbPassword);
* }*/
DB2Connection connection = new DB2Connection(connectionString);
try
{
connection.Open();
}
catch (Exception ex)
{
Log(ex.Message);
return(null);
}
return(connection);
}
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 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();
}
public TableInfoList(string connectionString, string schema = null)
{
//_connectionString = connectionString;
using (var cn = new DB2Connection(connectionString))
{
cn.Open();
using (var cm = cn.CreateCommand())
{
cm.CommandType = CommandType.Text;
if (string.IsNullOrWhiteSpace(schema))
{
cm.CommandText = SQL_WO_SCHEMA;
}
else
{
cm.CommandText = string.Format(SQL_W_SCHEMA, schema);
}
using (var a = new DB2DataAdapter(cm))
{
_data = new DataTable();
a.Fill(_data);
}
}
}
}
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));
}
public static void SetServerDisOutPort(string newoutportcode)
{
string orderdate = SortingLineTask.GetSortingLineTaskDate();
string taskno = SortingLineTask.GetSortingLineTaskNo();
using (var cn = new DB2Connection(AppUtility.AppUtil._FjInfoConnectionString))
{
cn.Open();
using (var tran = cn.BeginTransaction())
{
try
{
using (var cm = cn.CreateCommand())
{
cm.Transaction = tran;
StringBuilder SQL = new StringBuilder();
SQL.Append("UPDATE t_sorting_line_task SET OUTPORT = '" + newoutportcode + "' where orderdate = '" + orderdate + "' and taskno= '" + taskno + "' ");
cm.CommandText = SQL.ToString();
cm.ExecuteNonQuery();
tran.Commit();
}
}
catch (Exception e)
{
tran.Rollback();
throw e;
}
}
}
}
private static String getGeometryColumnName(string connectionString, String schemaName, String tableName)
{
String columnName = DB2SpatialExtenderProviderStatic.DefaultGeometryColumnName;
using (DB2Connection cn = new DB2Connection(connectionString))
{
cn.Open();
try
{
columnName = (String) new DB2Command(
String.Format(
@"
SELECT COLS.COLUMN_NAME
FROM {0}.ST_GEOMETRY_COLUMNS AS COLS
WHERE (COLS.TABLE_SCHEMA = '{1}' AND COLS.TABLE_NAME = '{2}');",
DB2SpatialExtenderProviderStatic.DefaultSpatialSchema,
schemaName,
tableName),
cn).ExecuteScalar();
}
catch
{
columnName = "";
}
}
return(columnName);
}
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();
}
/// <summary>
/// Gets the configuration.
/// </summary>
/// <param name="environment">The connection string.</param>
/// <param name="configKey">The connection string.</param>
/// <returns></returns>
public static T LoadApplicationsConfiguration <T>(this EnvironmentsSection environment, string configKey)
where T : class
{
T apps = null;
using (var connection = new DB2Connection(environment.ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT XMLVALUE FROM EUREKA_TOOLS.CONFIGURATION WHERE KEY = ?";
command.Parameters.Add(new DB2Parameter {
Value = configKey
});
var xmlConfiguration = command.ExecuteScalar();
if (xmlConfiguration == null)
{
return(null);
}
var stringConfiguration = xmlConfiguration.ToString();
var deserializer = new XmlSerializer(typeof(T));
var reader = new StringReader(stringConfiguration);
apps = (T)deserializer.Deserialize(reader);
}
}
return(apps);
}
public bool Connect(Database db)
{
string security = db.Encrypt ? ";Security=SSL" : "";
LogTitle = $"{db.Server}:{db.Port}/{db.DB}";
Schema = db.Schema;
Timeout = db.Timeout;
try
{
conn.Close();
conn.ConnectionString = $"Server={db.Server}:{db.Port};Database={db.DB};User ID={db.User}"
+ $";Password={db.Pwd};Pooling=false;Persist Security Info=True{security}";
conn.Open();
if (!string.IsNullOrEmpty(Schema))
{
Execute($"SET SCHEMA {Schema}", null, out _);
}
return(true);
}
catch (Exception ex)
{
LastError = ex.Message;
Logger.WriteLogExcept(LogTitle, ex);
return(false);
}
}
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));
}
public override List <string> CollectSample(DataEntity dataEntity, int sampleSize)
{
var result = new List <string>();
using (var conn = new DB2Connection(dataEntity.Container.ConnectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"SELECT {dataEntity.Name} FROM {dataEntity.Collection.Name} ORDER BY RAND() LIMIT {sampleSize}";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var val = reader[0];
if (val is DBNull)
{
result.Add(null);
}
else
{
result.Add(val.ToString());
}
}
}
}
}
return(result.Take(sampleSize).ToList());
}
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();
}
/// <summary>
/// 更新分拣优化程序中当前订单状态为已下载
/// </summary>
/// <param name="status"></param>
static public void UploadSortingStatus(int status)
{
string sortingdate = "";
string taskno = "";
using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString))
{
cn.Open();
using (var cm = cn.CreateCommand())
{
cm.CommandText = "select * from t_sorting_line_task limit 1";
using (var dr = new SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
taskno = dr.GetString("SORTINGTASKNO");
sortingdate = dr.GetString("ORDERDATE");
}
}
}
}
using (var remotecn = new DB2Connection(AppUtility.AppUtil._FjInfoConnectionString))
{
remotecn.Open();
using (var remotecm = remotecn.CreateCommand())
{
remotecm.CommandText = "UPDATE tsp_plan SET pickStatus = " + status + " WHERE orderDate = '" + sortingdate + "' and taskNo = '" + taskno + "'";
remotecm.ExecuteNonQuery();
}
}
}
//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)
{
}
}
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 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 SortingLineList GetSortingLines(bool isaddnull)
{
SortingLineList sortingLineList = new SortingLineList();
if (isaddnull)
{
sortingLineList.Add(new SortingLine());
}
using (var cn = new DB2Connection(AppUtility.AppUtil._FjInfoConnectionString))
{
cn.Open();
using (var cm = cn.CreateCommand())
{
cm.CommandText = "select * from T_SORTINGLINE order by Linecode";
using (var dr = new Csla.Data.SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
SortingLine sortingLine = new SortingLine();
sortingLine.ID = dr.GetString("LineCode");
sortingLine.Name = dr.GetString("LineName");
sortingLine.Type = dr.GetString("LineType");
sortingLineList.Add(sortingLine);
}
}
}
}
return(sortingLineList);
}
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 DataPortal_Fetch()
{
RaiseListChangedEvents = false;
using (var cn = new DB2Connection(AppUtility.AppUtil._FjInfoConnectionString))
{
cn.Open();
using (var cm = cn.CreateCommand())
{
StringBuilder SQL = new StringBuilder();
SQL.Append("SELECT c.CIGARETTENO,c.CIGARETTENAME,c.BARCODE,(case when b.boxCode is null then 9999 else b.boxcode*1 end) boxCODE ,b.boxName FROM t_ciginfo c LEFT JOIN (SELECT * from t_linebox b ) b on c.CIGARETTENO = b.bindCig where c.ISSEIZURE = 0 ORDER BY boxCODE");
cm.CommandText = SQL.ToString();
//cm.Parameters.AddWithValue("@ciginfo",ciginfo);
using (var dr = new SafeDataReader(cm.ExecuteReader()))
{
IsReadOnly = false;
while (dr.Read())
{
var info = new CigInfo(dr);
// apply filter if necessary
this.Add(info);
}
IsReadOnly = true;
}
}
}
RaiseListChangedEvents = true;
}
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);
}
}
/// <summary>
/// Conecta a la base de datos dejando la misma abierta
/// </summary>
public void ConectarBase()
{
string _pathLogs = System.Configuration.ConfigurationManager.AppSettings["PathLogs"];
string _newArchivo = _pathLogs + "\\ConexionDB_Log" + string.Format("{0:yyyyMMdd}", DateTime.Now.Date) + ".txt";
System.IO.StreamWriter sw = new StreamWriter(_newArchivo, true);
//ToDo: Logeo para verificar errores - sacar luego de tener ok la comunicacion
sw.WriteLine(string.Format("{0:dd/MM/yyyy HH:mm:ss}", DateTime.Now) + "-" + " Obtengo Conexion string");
sw.WriteLine(string.Format("{0:dd/MM/yyyy HH:mm:ss}", DateTime.Now) + "-" + " ConnString: " + System.Configuration.ConfigurationManager.ConnectionStrings["DB2Base"].ConnectionString);
sw.Flush();
_myConn = new DB2Connection(System.Configuration.ConfigurationManager.ConnectionStrings["DB2Base"].ConnectionString);
//ToDo: Logeo para verificar errores - sacar luego de tener ok la comunicacion
sw.WriteLine(string.Format("{0:dd/MM/yyyy HH:mm:ss}", DateTime.Now) + "-" + " IDB2Connection inicializada");
sw.WriteLine(string.Format("{0:dd/MM/yyyy HH:mm:ss}", DateTime.Now) + "-" + " Version IBM DB2: " + _myConn.ServerVersion);
sw.Flush();
_myConn.Open();
//ToDo: Logeo para verificar errores - sacar luego de tener ok la comunicacion
sw.WriteLine(string.Format("{0:dd/MM/yyyy HH:mm:ss}", DateTime.Now) + "-" + " Conexion abierta");
sw.Close();
}
public List <Citizen> GetCitizenList(CitizenViewModel model)
{
List <Citizen> citizenList = new List <Citizen>();
using (conn = new DB2Connection(connectionString))
{
conn.Open();
using (DB2Command cmd = conn.CreateCommand())
{
cmd.CommandText = CitizenHelper.CitizenSearchQueryBuilder(model);
rd = cmd.ExecuteReader();
rd.Read();
do
{
if (rd.HasRows)
{
Citizen citizen = new Citizen
{
Id = int.Parse(rd[0].ToString()),
Surname = rd[1].ToString().Trim(' '),
CitizenName = rd[2].ToString().Trim(' '),
Middlename = rd[3].ToString().Trim(' '),
BirthDate = DateTime.Parse(rd[4].ToString())
};
citizenList.Add(citizen);
}
} while (rd.Read());
}
}
return(citizenList);
}