//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)
{
}
}
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()
});
}
}
static void Main(string[] args)
{
DbConnection connection = new DB2Connection("Server=10.0.0.51:50000;Database=SAMPLE;UID=Administrator;PWD=***;CurrentSchema=DBO");
Test.Run(connection);
}
public async Task InitializeAsync()
{
const string connString = "Server=127.0.0.1:9089;Database=dummyifx;UID=informix;PWD=in4mix;Persist Security Info=True;Authentication=Server;";
_connection = new DB2Connection(connString);
await _connection.OpenAsync();
}
/// <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 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();
}
}
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 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 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 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);
}
/// <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);
}
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 void ReadCSVFile(DB2Connection conn)
{
try
{
for (int i = 0; i < 53; i++)
{
string filePath = @"G:\Dataset\language_filtered_reviews\english_reviews_";
filePath = filePath + i + ".csv";
if (!File.Exists(filePath))
{
break;
}
Console.WriteLine(i + " -Started");
DataTable dt = null;
bool fh = true;
dt = CsvFileToDatatable(filePath, fh);
PushToDatabase(dt, conn);
Console.WriteLine(i + " -Completed");
}
}
catch (Exception)
{
}
}
/// <summary>
/// 核心类
/// </summary>
/// <param name="cmd">表示要对数据源执行的SQL或存储过程</param>
/// <param name="conn">表示是数据源的连接是打开的</param>
/// <param name="trans">表示是数据源的SQL事务,不能被继承</param>
/// <param name="cmdType">指定如何解释命令字符串</param>
/// <param name="cmdText">字符串</param>
/// <param name="cmdParms">参数</param>
private static void PrepareCommand(DB2Command cmd, DB2Connection conn, DB2Transaction trans, CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
{
cmd.Connection = conn;
cmd.CommandText = cmdText;
//判断是否需要事务处理
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
if (cmdParms == null)
{
return;
}
foreach (var parameter in cmdParms.Cast <DB2Parameter>())
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
/// <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();
}
}
}
private bool innerBeginTransaction(string connectionString)
{
try
{
if (string.IsNullOrEmpty(connectionString))
{
return(false);
}
if (TransConnection == null)
{
TransConnection = new DB2Connection(connectionString);
}
if (TransConnection == null)
{
return(false);
}
if (TransConnection.State != ConnectionState.Open)
{
TransConnection.Open();
}
Transaction = TransConnection.BeginTransaction();
return(true);
}
catch (Exception ex)
{
Logger?.LogError(ex, "Error at BeginTransaction");
throw new DbDataException(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 async Task <object> innerGetScalarAsync(string query, CancellationToken cancellationToken, int timeout = -1)
{
try
{
return(await Task.Run(async() =>
{
object obj;
using (var asyncConnection = new DB2Connection(StringProvider.ConnectionString))
{
using (var cmd = asyncConnection.CreateCommand())
{
cmd.CommandText = query;
if (!IsValidTimeout(cmd, timeout))
{
throw new ArgumentException("Invalid CommandTimeout value", nameof(timeout));
}
await asyncConnection.OpenAsync(cancellationToken);
obj = await cmd.ExecuteScalarAsync(cancellationToken);
}
}
return obj;
}, cancellationToken));
}
catch (Exception ex)
{
Logger?.LogError(ex, $"Error at GetScalarAsync; command text: {query}");
throw new DbDataException(ex, query);
}
}
public Task DisposeAsync()
{
_connection?.Close();
_connection?.Dispose();
_connection = null;
return(Task.FromResult(0));
}
public DataSet Request(DataSet ds, IncludeSchema isEnum, string nombreProcedimiento, IDictionaryEnumerator parametros)
{
DB2Connection lc = OpenConnection();
try
{
DB2Command comm = new DB2Command(nombreProcedimiento, lc);
comm.CommandType = System.Data.CommandType.StoredProcedure;
if (parametros != null)
{
while (parametros.MoveNext())
{
comm.Parameters.Add(parametros.Key.ToString(), parametros.Value);
}
}
DB2DataAdapter adapter = new DB2DataAdapter(comm);
if (isEnum == IncludeSchema.YES)
{
adapter.FillSchema(ds, SchemaType.Mapped);
adapter.Fill(ds);
}
else
{
adapter.Fill(ds, "result_ " + ds.Tables.Count.ToString());
}
}
catch (Exception e)
{
exceptions = "Error ejecutando SQL." + cambioLinea + cambioLinea;
exceptions += e.ToString();
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, nombreProcedimiento, e, exceptions);
}
CloseConnection(lc);
return(ds);
}
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 int NonQuery(string sql, byte[] blob)
{
DB2Connection ncc = OpenConnection();
DB2Command com = new DB2Command(sql, ncc);
try
{
DB2Parameter parm1 = new DB2Parameter();
parm1.DbType = DbType.Binary;
parm1.ParameterName = "@blob";
parm1.Value = (byte[])blob;
com.Parameters.Add(parm1);
affectedRows = com.ExecuteNonQuery();
HistorialSeguimientoTabla(TABLA_SEGUIR, sql); //Almacenamiento historial de seguimiento a tabla.
}
catch (Exception e)
{
//AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql, e, e.Message);
//exceptions += e.Message + cambioLinea;
affectedRows = 0;
}
finally
{
CloseConnection(ncc);
}
return(affectedRows);
}
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 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();
}
protected IList <QuerySourceCategory> GetSchemas(DB2Connection conn, string excludeSchemas)
{
string sql = string.Format(@"SELECT SCHEMANAME
FROM SYSCAT.SCHEMATA
WHERE SCHEMANAME NOT IN({0});", excludeSchemas);
var querySourceCategories = conn.Query <dynamic>(sql)
.Select(s => new QuerySourceCategory {
Name = s.SCHEMANAME
})
.ToList();
return(querySourceCategories);
//var tables = conn.GetSchema(DB2MetaDataCollectionNames.Schemas);
//var result = new List<QuerySourceCategory>();
//foreach (var row in tables.Rows.OfType<DataRow>())
//{
// var name = row["TABLE_SCHEMA"].ToString();
// if (!excludeSchemas.Contains(name))
// {
// result.Add(
// new QuerySourceCategory
// {
// Name = name
// });
// }
//}
//return result;
}
void IProvider.Initialize(IDataServices dataServices, object connection)
{
if (dataServices == null)
{
throw SqlClient.Error.ArgumentNull("dataServices");
}
DbConnection conn;
if (connection is string)
{
conn = new DB2Connection((string)connection);
}
else
{
conn = ((DbConnection)connection);
}
connectionString = conn.ConnectionString;
var builder = new DB2ConnectionStringBuilder(connectionString);
dbName = builder.Database;
if (string.IsNullOrEmpty(dbName))
{
dbName = dataServices.Model.DatabaseName;
}
if (string.IsNullOrEmpty(dbName))
{
throw SqlClient.Error.CouldNotDetermineCatalogName();
}
Initialize(dataServices, conn);
}
/// <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 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();
}
