public void fillcomboDB(ComboBox c, string query)
{
try
{
connect.Open();
c.Items.Clear();
DB2Command cmd = new DB2Command(query, connect);
DB2DataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read())
{
string tName = reader.GetString(0);
c.Items.Add(tName);
}
reader.Close();
connect.Close();
} catch (DB2Exception e)
{
System.Windows.Forms.MessageBox.Show(e.ToString());
connect.Close();
}
}
private void ModificarCheck_Load(object sender, EventArgs e)
{
textBox1.Text = arbol.SelectedNode.Text;
nombre_real = arbol.SelectedNode.Text;
PantallaPrincipal pn = new PantallaPrincipal();
DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Parent.Text);
try {
connection.Open();
DB2Command cmd = new DB2Command(@"SELECT TEXT FROM SYSIBM.SYSCHECKS WHERE TBCREATOR='DB2ADMIN' AND NAME ='" + arbol.SelectedNode.Text + "';", connection);
DB2DataReader buffer = cmd.ExecuteReader();
while (buffer.Read())
{
var function = buffer ["TEXT"].ToString();
richTextBox1.Text = function;
checkviejo = function;
break;
}
} catch (DB2Exception ex) {
MessageBox.Show("Error al mostrar Check\n" + ex.Message);
}
connection.Close();
}
private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
{
comboBox4.Items.Clear();
PantallaPrincipal pn = new PantallaPrincipal();
DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Text);
try {
connection.Open();
int posicion = comboBox3.SelectedIndex;
string nombre_tabla = comboBox3.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();
comboBox4.Items.Add(nombre_campo);
}
buffer.Close();
} catch (DB2Exception ex) {
MessageBox.Show("Ha ocurrido un error\n" + ex.Message);
}
connection.Close();
}
public static List <DatosDeConsultaCondenadoProcesado> ConsultaCondenadoProcesado(decimal cuil)
{
log.InfoFormat("Se ejecuta el método YH_ConsultaCondenadoProcesado con el parámetro {0}", cuil);
List <DatosDeConsultaCondenadoProcesado> response = new List <DatosDeConsultaCondenadoProcesado>();
DB2Command cmd = AdministradorDeConexion.ObtenerComando();
using (DB2Connection conexion = AdministradorDeConexion.ObtenerConnexion())
{
try
{
cmd.CommandText = string.Format("{0}.YH_ConsultaCondenadoProcesado", AdministradorDeConexion.GetSchema());
cmd.Parameters.Add(new DB2Parameter("@CUIL", cuil));
cmd.Connection = conexion;
conexion.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
response.Add(Adapter.toConsultaCondenadoProcesadoResponse(reader));
}
}
catch (Exception ex)
{
log.ErrorFormat("Se produjo un error en el método YH_ConsultaCondenadoProcesado {0} - {1} - {2}", ex.Message, ex.Source, ex.StackTrace);
throw ex;
}
finally
{
if (conexion.IsOpen)
{
conexion.Close();
}
conexion.Dispose();
cmd.Dispose();
}
}
return(response);
}
protected override CustomListColumnCollection GetColumnsOverride(CustomListData data)
{
data.Properties.TryGetValue("SQL Statement", StringComparison.OrdinalIgnoreCase, out var SQLStatement);
var cols = new CustomListColumnCollection();
var con = GetConnection(data);
var command = new DB2Command(SQLStatement, con);
var reader = command.ExecuteReader();
var schemaTable = reader.GetSchemaTable();
foreach (DataRow db2col in schemaTable.Rows)
{
var columnName = (string)db2col["ColumnName"];
var dataType = (Type)db2col["DataType"];
var listColumnType = CustomListColumnTypes.String;
// We convert the types to one of the three Peakboard types (string, number or boolean)
if (dataType == typeof(string))
{
listColumnType = CustomListColumnTypes.String;
}
else if (dataType == typeof(bool))
{
listColumnType = CustomListColumnTypes.Boolean;
}
else
{
listColumnType = DataTypeHelper.IsNumericType(dataType) ? CustomListColumnTypes.Number : CustomListColumnTypes.String;
}
cols.Add(new CustomListColumn(columnName, listColumnType));
}
con.Close();
return(cols);
}
private DBReaderController ExecuteQuery(DBRequest request)
{
//Controllo
if (!registered.Contains(request.Controller))
{
throw new Exception("Il Controller non è registrato all'inizio dell'esecuzione");
}
//Inizio
DBReaderController result;
DB2DataReader reader;
DB2Transaction transaction = databaseConnection.GetTransaction();
DB2Command command = databaseConnection.GetCommand();
command.CommandText = request.Command;
command.Transaction = transaction;
//Esecuzione
try {
reader = command.ExecuteReader();
}
catch (Exception e) {
throw new Exception("Errore in lettura: " + e.Message);
}
//Controllo
if (!registered.Contains(request.Controller))
{
transaction.Rollback();
throw new Exception("Il Controller non è registrato alla fine dell'esecuzione");
}
//Fine
transaction.Commit();
return(result = new DBReaderController(reader));
}
private void eliminarCheckToolStripMenuItem_Click(object sender, EventArgs e)
{
DB2Connection connection = obtenerConexion(arbol_conexiones.SelectedNode.Parent.Parent.Text);
try {
connection.Open();
DB2Command cmd = new DB2Command("SELECT TBNAME FROM SYSIBM.SYSCHECKS WHERE TBCREATOR='DB2ADMIN' AND NAME='" + arbol_conexiones.SelectedNode.Text + "'", connection);
DB2DataReader buffer = cmd.ExecuteReader();
while (buffer.Read())
{
var nombre_tabla = buffer ["TBNAME"].ToString();
buffer.Close();
cmd.CommandText = "ALTER TABLE " + nombre_tabla + " DROP CHECK " + arbol_conexiones.SelectedNode.Text + ";";
cmd.ExecuteNonQuery();
MessageBox.Show("Check Borrado!");
arbol_conexiones.SelectedNode.Remove();
break;
}
} catch (DB2Exception ex) {
MessageBox.Show("Error al borrar Check!\n" + ex.Message);
}
connection.Close();
}
public DB2DataReader DataReader(string sql)
{
DB2Command oc = new DB2Command();
try
{
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
oc.CommandText = sql;
DB2Connection con = OpenConnection();
connection = con;
oc.Connection = con;
DB2DataReader dr = oc.ExecuteReader();
CloseConnection(con);
return(dr);
}
catch (Exception e)
{
exceptions = "Error ejecutando SQL." + cambioLinea + cambioLinea;
exceptions += e.ToString();
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql, e, exceptions);
return(null);
}
}
/// <summary>
/// Executes for schema.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <param name="type">The type.</param>
/// <param name="categoryName">Name of the schema.</param>
/// <param name="querySourceName">Name of the specific.</param>
/// <param name="parameters">The parameters.</param>
/// <returns>
/// list of QuerySourceField
/// </returns>
private List <QuerySourceField> LoadFieldsFromProcedure(string connectionString, string type, string categoryName, string querySourceName, List <QuerySourceParameter> parameters = null, bool ignoreError = true, BI.Logging.ILog log = null)
{
var result = new List <QuerySourceField>();
var dataTypeAdaptor = new DB2SupportDataType();
if (parameters == null)
{
parameters = GetQuerySourceParameters(connectionString, categoryName, querySourceName);
}
parameters = parameters.OrderBy(x => x.Position).ToList();
using (var transaction = new TransactionScope(TransactionScopeOption.RequiresNew))
{
using (var connection = new DB2Connection(connectionString))
{
connection.Open();
string sql = String.Format("{0}.{1}", categoryName, querySourceName);
DB2Command cmd = new DB2Command(sql, connection);
if (type == SQLQuerySourceType.Procedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (parameters != null && parameters.Count() > 0)
{
foreach (var parameter in parameters)
{
var db2Parameter = cmd.Parameters.Add(parameter.Name, parameter.Value);
}
}
try
{
var reader = cmd.ExecuteReader();
DataTable schema = reader.GetSchemaTable();
if (schema != null)
{
var colNames = schema.Rows.OfType <DataRow>().FirstOrDefault().Table.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToList();
var colStr = string.Join(", ", colNames.ToArray());
result.AddRange(schema
.Rows
.OfType <DataRow>()
.Select
(
(c, i) => new QuerySourceField
{
Id = Guid.NewGuid(),
GroupPosition = 1,
Position = i,
Name = c["ColumnName"].ToString(),
DataType = reader.GetDataTypeName(int.Parse(c["ColumnOrdinal"].ToString())),
IzendaDataType = dataTypeAdaptor.GetIzendaDataType(reader.GetDataTypeName(int.Parse(c["ColumnOrdinal"].ToString()))),
AllowDistinct = dataTypeAdaptor.GetAllowDistinct(reader.GetDataTypeName(int.Parse(c["ColumnOrdinal"].ToString()))),
Type = (int)QuerySourceFieldType.Field
}
)
.Where(x => !string.IsNullOrEmpty(x.IzendaDataType)));
}
reader.Close();
return(result);
}
catch (Exception ex)
{
log?.Debug(ex);
// ignore error when execute stored proc from customer connectionString
if (ignoreError)
{
return(result);
}
else
{
throw;
}
}
}
}
}
private void extract_document_locations(String fileNumber, String consolNumber, String hbNumber, String masterNumber)
{
String ConString = "Database = edoc; User ID = inetsoft; Password = etl5boxes; server = edoc.khi.ei:50002; Max Pool Size = 100; Persist security info = False; Pooling = True ";
String masterFileType = "";
String houseFileType = "";
string docQuery = @"SELECT DISTINCT
EDOC.FOLDER.KEY_,
EDOC.FOLDER.DESCRIPTION,
EDOC.DOCUMENT.DESCRIPTION,
EDOC.DOCUMENT.CREATION_TIME,
EDOC.DOCUMENT.FILE_NAME_IMAGE,
EDOC.FOLDER.KEY_TYPE,
EDOC.DOCUMENT.DOC_TYPE,
EDOC.DOCUMENT.IMAGE_FILE_TYPE,
EDOC.DOCUMENT.CREATED_BY_NAME
from(EDOC.FOLDER INNER JOIN EDOC.FOLDER_DOCUMENT ON EDOC.FOLDER.G_U_I_D = EDOC.FOLDER_DOCUMENT.FOLDER__P_K)
LEFT OUTER JOIN EDOC.DOCUMENT ON EDOC.DOCUMENT.G_U_I_D = EDOC.FOLDER_DOCUMENT.DOCUMENT__P_K and EDOC.DOCUMENT.DOC_TYPE IN('HOU', 'HOR', 'MOR', 'MOB')
where EDOC.FOLDER.KEY_ IN('" + fileNumber + "','" + consolNumber + "','" + hbNumber + "')AND EDOC.DOCUMENT.FILE_NAME_IMAGE IS NOT NULL";
//List<String> columnData = new List<String>();
//Connecting to DB
using (DB2Connection myconnection = new DB2Connection(ConString))
{
myconnection.Open();
DB2Command cmd = myconnection.CreateCommand();
cmd.CommandText = docQuery;
//DB2DataReader rd = cmd.ExecuteReader();
using (DB2DataReader reader = cmd.ExecuteReader())
{
string host = "ftp://edoc.khi.ei//";
string user = "******";
string pass = "******";
bool checkHouse = false;
bool checkMaster = false;
while (reader.Read())
{
string rf = folder + reader.GetString(4);
//MetroFramework.MetroMessageBox.Show(this,reader.GetString(6));
string lf = "";
if ((reader.GetString(6) == "HOU") && ((reader.GetString(7) == "TIFF") && (!checkHouse)))
{
lf = downLocation + "/" + hbNumber + ".tiff";
download(rf, lf, host, user, pass);
checkHouse = true;
houseFileType = ".tiff";
}
if ((reader.GetString(6) == "HOR") && ((reader.GetString(7) == "TIFF") && (!checkHouse)))
{
lf = downLocation + "/" + hbNumber + ".tiff";
download(rf, lf, host, user, pass);
checkHouse = true;
houseFileType = ".tiff";
}
if ((reader.GetString(6) == "HOU") && ((reader.GetString(7) == "PDF") && (!checkHouse)))
{
lf = downLocation + "/" + hbNumber + ".pdf";
download(rf, lf, host, user, pass);
checkHouse = true;
houseFileType = ".pdf";
}
if ((reader.GetString(6) == "HOR") && ((reader.GetString(7) == "PDF") && (!checkHouse)))
{
lf = downLocation + "/" + hbNumber + ".pdf";
download(rf, lf, host, user, pass);
checkHouse = true;
houseFileType = ".pdf";
}
if ((reader.GetString(6) == "MOR") && ((reader.GetString(7) == "PDF") && (!checkMaster)))
{
lf = downLocation + "/" + masterNumber + ".pdf";
download(rf, lf, host, user, pass);
checkMaster = true;
masterFileType = ".pdf";
}
if ((reader.GetString(6) == "MOR") && ((reader.GetString(7) == "TIFF") && (!checkMaster)))
{
lf = downLocation + "/" + masterNumber + ".tiff";
download(rf, lf, host, user, pass);
checkMaster = true;
masterFileType = ".tiff";
}
if ((reader.GetString(6) == "MOB") && ((reader.GetString(7) == "PDF") && (!checkMaster)))
{
lf = downLocation + "/" + masterNumber + ".pdf";
download(rf, lf, host, user, pass);
checkMaster = true;
masterFileType = ".pdf";
}
if ((reader.GetString(6) == "MOB") && ((reader.GetString(7) == "TIFF") && (!checkMaster)))
{
lf = downLocation + "/" + masterNumber + ".tiff";
download(rf, lf, host, user, pass);
checkMaster = true;
masterFileType = ".tiff";
}
}
}
}
create_Email_Draft(downLocation + "/" + masterNumber + ".xlsx", downLocation + "/" + masterNumber + masterFileType, downLocation + "/" + hbNumber + houseFileType);
//Show
}
public Task <IList <Column> > GetTableDetails(Table table, string owner)
{
IList <Column> columns = new List <Column>();
using (var sqlCon = new DB2Connection(_connectionStr))
{
try
{
sqlCon.Open();
using (DB2Command tableDetailsCommand = sqlCon.CreateCommand())
{
tableDetailsCommand.CommandText = $@"
select c.colno, c.colname, c.coltype
, cast(case when c.collength >= 256 then null else c.collength end as int) collength
, cast(case when c.collength >= 256 then c.collength / 256 else null end as int) colprecision
, cast(case when c.collength >= 256 then c.collength - (cast(c.collength / 256 as int) * 256) else null end as int) colscale
-- , cast(case when c.collength >= 256 then c.collength / 256 else c.collength end as int) collength
-- , cast(case when c.collength >= 256 then c.collength / 256 else c.collength end as int) colprecision
-- , cast(case when c.collength >= 256 then c.collength - (cast(c.collength / 256 as int) * 256) else 0 end as int) colscale
, case when pkidx.idxtype is not null then 'P' else '-' end ispk
, nvl(nl.constrtype, '-') isnullable
, (select nvl(min(uidx.idxtype), '-') uniq
from sysindexes uidx
where uidx.tabid = c.tabid and uidx.idxtype = 'U'
and c.colno in (uidx.part1, uidx.part2, uidx.part3, uidx.part4, uidx.part5, uidx.part6, uidx.part7, uidx.part8,
uidx.part9, uidx.part10, uidx.part11, uidx.part12, uidx.part13, uidx.part14, uidx.part15, uidx.part16)
) isunique
, case when c.coltype in (6, 18, 262, 274) then 'I' else '-' end isidentity
from syscolumns c
inner join systables t on c.tabid = t.tabid
left outer join syscoldepend d on d.tabid = c.tabid and d.colno = c.colno
left outer join sysconstraints nl on nl.constrid = d.constrid and nl.constrtype = 'N'
left outer join sysconstraints pk on pk.tabid = c.tabid and pk.constrtype = 'P'
left outer join sysindexes pkidx on pkidx.tabid = c.tabid and pkidx.idxname = pk.idxname
and c.colno in (pkidx.part1, pkidx.part2, pkidx.part3, pkidx.part4, pkidx.part5, pkidx.part6, pkidx.part7, pkidx.part8,
pkidx.part9, pkidx.part10, pkidx.part11, pkidx.part12, pkidx.part13, pkidx.part14, pkidx.part15, pkidx.part16)
where t.owner = '{owner}'
and t.tabname = '{table.Name}'
";
using (DB2DataReader reader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
{
var m = new DataTypeMapper();
while (reader.Read())
{
string dataType = GetColumnType(reader.Get <short>("coltype"));
int? dataLength = reader.Get <int?>("collength");
int? dataPrecision = reader.Get <int?>("colprecision");
int? dataScale = reader.Get <int?>("colscale");
columns.Add(new Column
{
Name = reader.GetString("colname"),
DataType = dataType,
DataLength = dataLength,
DataPrecision = dataPrecision,
DataScale = dataScale,
IsNullable = reader.GetString("isnullable") != "N",
IsPrimaryKey = reader.GetString("ispk") == "P",
IsUnique = reader.GetString("isunique") == "U",
IsIdentity = reader.GetString("isidentity") == "I",
MappedDataType = m.MapFromDBType(ServerType.Informix, dataType, dataLength, dataPrecision, dataScale),
});
}
}
}
table.Owner = owner;
table.Columns = columns;
}
finally
{
sqlCon.Close();
}
}
table.PrimaryKey = DeterminePrimaryKeys(table);
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
return(Task.FromResult(columns));
}
public string Read(string jsonQuery)
{
string selectSQL = this._sqlParser.ReadSqlParser(jsonQuery);
string objName = this._sqlParser.ObjName;
bool isReadToList = this._sqlParser.IsReadToList;
string jsonDataSet = string.Empty;
if (!string.IsNullOrEmpty(selectSQL))
{
DB2Command dbCmd = new DB2Command(selectSQL, this._connection);
this.OpenConnection();
DB2DataReader dbReader = dbCmd.ExecuteReader();
while (dbReader.Read())
{
// add LIST seperator jsonDataSet
if (isReadToList && !string.IsNullOrEmpty(jsonDataSet))
{
jsonDataSet += ",";
}
int fieldCount = dbReader.FieldCount;
// open json row
jsonDataSet += "{";
for (int fieldIndex = 0; fieldIndex < fieldCount; fieldIndex++)
{
// GET NAME
string fieldName = dbReader.GetName(fieldIndex);
string JOIN_PREFIX = "JOIN_";
bool isJoinField = false;
if (fieldName.ToUpper().StartsWith("JOIN_"))
{
isJoinField = true;
fieldName = fieldName.Substring(fieldName.IndexOf(JOIN_PREFIX) + JOIN_PREFIX.Length);
}
fieldName = $"\"{fieldName}\"";
// GET VALUE
string fieldValue = dbReader.IsDBNull(fieldIndex) ? "\"\"" : dbReader.GetString(fieldIndex);
if (!isJoinField)
{
fieldValue = $"\"{fieldValue}\"";
}
jsonDataSet += $"{fieldName}:{fieldValue}";
if (fieldIndex < fieldCount - 1)
{
jsonDataSet += ",";
}
}
// close json row
jsonDataSet += "}";
}
//close
dbReader.Close();
dbReader.Dispose();
this.CloseConnection();
}
return(StringHelper.Simpler(string.IsNullOrEmpty(jsonDataSet) ? null : ("{" + $"\"{objName}\"" + ":" + ((isReadToList) ? "[" + jsonDataSet + "]" : jsonDataSet) + "}"), Pattern.PATTERN_SPECIAL_CHARS));
}
public IList <ForeignKey> DetermineForeignKeyReferences(Table table)
{
var foreignKeys = new List <ForeignKey>();
using (var sqlCon = new DB2Connection(_connectionStr))
{
try
{
sqlCon.Open();
using (DB2Command tableDetailsCommand = sqlCon.CreateCommand())
{
tableDetailsCommand.CommandText = $@"
select co.constrname, t.tabname, c.colno, c.colname, t2.tabname tabname2, c2.colno colno2, c2.colname colname2
from sysreferences r
inner join sysconstraints co on co.constrid = r.constrid and co.constrtype = 'R'
inner join systables t on t.tabid = co.tabid
inner join sysindexes i on i.idxname = co.idxname
inner join syscolumns c on c.tabid = co.tabid
inner join sysconstraints co2 on co2.constrid = r.primary
inner join systables t2 on t2.tabid = co2.tabid
inner join sysindexes i2 on i2.idxname = co2.idxname
inner join syscolumns c2 on c2.tabid = co2.tabid
where (
(c.colno = i.part1 and c2.colno = i2.part1)
or (c.colno = i.part2 and c2.colno = i2.part2)
or (c.colno = i.part3 and c2.colno = i2.part3)
or (c.colno = i.part4 and c2.colno = i2.part4)
or (c.colno = i.part5 and c2.colno = i2.part5)
or (c.colno = i.part6 and c2.colno = i2.part6)
or (c.colno = i.part7 and c2.colno = i2.part7)
or (c.colno = i.part8 and c2.colno = i2.part8)
or (c.colno = i.part9 and c2.colno = i2.part9)
or (c.colno = i.part10 and c2.colno = i2.part10)
or (c.colno = i.part11 and c2.colno = i2.part11)
or (c.colno = i.part12 and c2.colno = i2.part12)
or (c.colno = i.part13 and c2.colno = i2.part13)
or (c.colno = i.part14 and c2.colno = i2.part14)
or (c.colno = i.part15 and c2.colno = i2.part15)
or (c.colno = i.part16 and c2.colno = i2.part16)
)
and t.owner = '{table.Owner}'
and t.tabname = '{table.Name}'
";
using (DB2DataReader reader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
{
ForeignKey foreignKey = null;
while (reader.Read())
{
string constraintName = reader.GetString("constrname");
if (foreignKey != null && foreignKey.Name != constraintName)
{
foreignKeys.Add(foreignKey);
foreignKey = null;
}
if (foreignKey == null)
{
foreignKey = new ForeignKey
{
Name = constraintName,
References = reader.GetString("tabname2"),
Columns = new List <Column>(),
UniquePropertyName = null,
IsNullable = true
};
}
Column column = FindTableColumn(table, reader.GetString("colname"));
column.IsForeignKey = true;
column.ConstraintName = foreignKey.Name;
column.ForeignKeyTableName = foreignKey.References;
column.ForeignKeyColumnName = reader.GetString("colname2");
foreignKey.Columns.Add(column);
}
if (foreignKey != null)
{
foreignKeys.Add(foreignKey);
}
}
}
}
catch
{
sqlCon.Close();
throw;
}
}
Table.SetUniqueNamesForForeignKeyProperties(foreignKeys);
return(foreignKeys);
}
IList <HasMany> DetermineHasManyRelationships(Table table)
{
var hasManys = new List <HasMany>();
using (var sqlCon = new DB2Connection(_connectionStr))
{
try
{
sqlCon.Open();
using (DB2Command tableDetailsCommand = sqlCon.CreateCommand())
{
tableDetailsCommand.CommandText = $@"
select co.constrname, t.tabname, c.colno, c.colname, t2.tabname tabname2, c2.colno colno2, c2.colname colname2
from sysreferences r
inner join sysconstraints co on co.constrid = r.constrid and co.constrtype = 'R'
inner join systables t on t.tabid = co.tabid
inner join sysindexes i on i.idxname = co.idxname
inner join syscolumns c on c.tabid = co.tabid
inner join sysconstraints co2 on co2.constrid = r.primary
inner join systables t2 on t2.tabid = co2.tabid
inner join sysindexes i2 on i2.idxname = co2.idxname
inner join syscolumns c2 on c2.tabid = co2.tabid
where (
(c.colno = i.part1 and c2.colno = i2.part1)
or (c.colno = i.part2 and c2.colno = i2.part2)
or (c.colno = i.part3 and c2.colno = i2.part3)
or (c.colno = i.part4 and c2.colno = i2.part4)
or (c.colno = i.part5 and c2.colno = i2.part5)
or (c.colno = i.part6 and c2.colno = i2.part6)
or (c.colno = i.part7 and c2.colno = i2.part7)
or (c.colno = i.part8 and c2.colno = i2.part8)
or (c.colno = i.part9 and c2.colno = i2.part9)
or (c.colno = i.part10 and c2.colno = i2.part10)
or (c.colno = i.part11 and c2.colno = i2.part11)
or (c.colno = i.part12 and c2.colno = i2.part12)
or (c.colno = i.part13 and c2.colno = i2.part13)
or (c.colno = i.part14 and c2.colno = i2.part14)
or (c.colno = i.part15 and c2.colno = i2.part15)
or (c.colno = i.part16 and c2.colno = i2.part16)
)
and t2.owner = '{table.Owner}'
and t2.tabname = '{table.Name}'
";
using (DB2DataReader reader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
{
HasMany hasMany = null;
while (reader.Read())
{
string constraintName = reader.GetString("constrname");
if (hasMany != null && hasMany.ConstraintName != constraintName)
{
hasManys.Add(hasMany);
hasMany = null;
}
if (hasMany == null)
{
hasMany = new HasMany
{
ConstraintName = constraintName,
Reference = reader.GetString("tabname2"),
PKTableName = reader.GetString("tabname")
};
}
hasMany.AllReferenceColumns.Add(reader.GetString("colname"));
}
if (hasMany != null)
{
hasManys.Add(hasMany);
}
}
}
}
catch
{
sqlCon.Close();
throw;
}
}
return(hasManys);
}
protected override string GenerateSelectSql(IList <ProviderPropertyExpression> properties,
ExpressionTreeToSqlCompilerBase <TOid> compiler)
{
_dataPageNumber = GetProviderPropertyValue <DataPageNumberExpression, int>(properties, -1);
_dataPageSize = GetProviderPropertyValue <DataPageSizeExpression, int>(properties, 0);
string sql = "";
//string orderByCols = String.Join(",",
// Enumerable.ToArray(Processor.Select(
// GetProviderPropertyValue
// <OrderByCollectionExpression,
// CollectionExpression<OrderByExpression>>(
// properties,
// new CollectionExpression<OrderByExpression>(
// new OrderByExpression[] {})),
// delegate(OrderByExpression o) { return o.ToString("\"{0}\""); })));
string orderByClause = string.IsNullOrEmpty(compiler.OrderByClause) ? "" : " ORDER BY " + compiler.OrderByClause;
string mainQueryColumns = string.Join(",", Enumerable.ToArray(
FormatColumnNames(true, true,
compiler.ProjectedColumns.Count > 0
? compiler.ProjectedColumns
: SelectAllColumnNames())));
sql = String.Format("\nSELECT {0}\nFROM {1}\n{2}\n{3} {4}\n{5};",
mainQueryColumns,
QualifiedTableName + " AS \"" + Table + "\"",
compiler.SqlJoinClauses,
string.IsNullOrEmpty(compiler.SqlWhereClause) ? "" : " WHERE ",
compiler.SqlWhereClause,
orderByClause);
//}
#if DEBUG && EXPLAIN
if (sql.StartsWith("SELECT"))
{
using (DB2Connection cn = new DB2Connection(ConnectionString))
{
cn.Open();
DB2Command cm = new DB2Command(String.Format("EXPLAIN ANALYZE {0}", sql), cn);
foreach (IDataParameter par in compiler.ParameterCache.Values)
{
cm.Parameters.Add(par);
}
Debug.WriteLine("");
DB2DataReader dr = cm.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
Debug.WriteLine(dr.GetString(0));
}
}
Debug.WriteLine("");
}
}
#endif
return(sql);
}
protected void DB2Connection()
{
string Text1 = TextBox1.Text;
string inputPattern = @"^[\s\;]*\w+[^\;]*[\;\s]*$";
Regex input = new Regex(inputPattern);
Match inputmatch = input.Match(TextBox1.Text);
if (inputmatch.Success)
{
string s = @"Server=localhost:50000;Database=" + DropDownList2.SelectedItem.Text + ";UID=db2admin;PWD=***;CurrentSchema=db2admin";
DB2Connection myConnection = new DB2Connection(s);
DB2Command cmd = new DB2Command();
DB2DataReader r = null;
DB2DataAdapter adapter = new DB2DataAdapter(Text1, myConnection);
//string usertype = User.Identity.Name.Substring(4);
string usertype = Session["Role"].ToString().ToLower();
string pattern = "";
switch (usertype)
{
case "level1":
pattern = @"((^|\;)\s*(?i)(\binsert\b|\bupdate\b|\bdelete\b|\bmerge\b|\bcreate\b|\balter\b|\bdrop\b)(?i))|(?i)\binto\b(?i)";
break;
case "level2":
pattern = @"(^|;|\*\/)\s*(?i)(\bcreate\b|\balter\b|\bdrop\b)(?i)";
break;
default:
pattern = @"((^|;|\*\/)\s*(?i)(\bdrop\b\s*database)(?i))";
break;
}
Regex rgx = new Regex(pattern);
Match match = rgx.Match(TextBox1.Text);
if (match.Success)
{
msg.Text = "Permission was denied on database " + DropDownList2.SelectedItem.Text;
}
else
{
try
{
cmd.CommandText = TextBox1.Text;
cmd.Connection = myConnection;
myConnection.Open();
string firstWord = Regex.Match(TextBox1.Text, @"\w+\b").ToString().ToLower();
if (firstWord == "select")
{
DataTable dt = new DataTable();
adapter.Fill(dt);
GridView1.DataSource = dt;
r = cmd.ExecuteReader();
int rowCount = 0;
if (r.HasRows) //results>0
{
while (r.Read())
{
rowCount++;
}
msg.Text = "Result: " + rowCount + " row(s) found";
}
else
{
msg.Text = "Result: 0 row(s) found.<br /><span style='background-color:#339933; color:black; font-size: 15pt'>";
for (int i = 0; i < r.FieldCount; i++)
{
msg.Text += r.GetName(i) + " |";
}
msg.Text += "</span><br />";
}
r.Close();
}
else
{
int numberOfRecords = cmd.ExecuteNonQuery();
msg.Text = "Result: " + numberOfRecords + " row(s) affected.";
}
}
catch (Exception ex)
{
msg.Text = ex.Message;
}
finally
{
myConnection.Close();
GridView1.DataBind();
FormatView();
}
}
}
else
{
//msg.Text = "Only one statement allowed";
Header.Controls.Add(new LiteralControl("<script type=\"text/javascript\">alert('Only one statement allowed');</script>"));
}
}
public void GetData()
{
myAggLabels = new ObservableCollection <AggregatedLabel>();
myContactLists = new ObservableCollection <ContactList>();
DB2Connection conn = null;
try
{
conn = new DB2Connection("XXXX;");
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message + " " + ex.InnerException);
}
//get all contactLists and their labels
DB2Command command = new DB2Command("SQL SELECT statement");
command.Connection = conn;
conn.Open();
//get all labels from database
using (DB2DataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
AggregatedLabel aggLabel = new AggregatedLabel();
aggLabel.ID = Convert.ToInt32(dr["LABEL_ID"]);
aggLabel.Name = dr["LABEL_NAME"].ToString();
myAggLabels.Add(aggLabel);
}
}
//Add unique contactLists to dictionary
Dictionary <int, ContactList> myContactDictionary = new Dictionary <int, ContactList>();
using (DB2DataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
int id = Convert.ToInt32(dr["CONTACT_LIST_ID"]);
if (!myContactDictionary.ContainsKey(id))
{
ContactList contactList = new ContactList();
contactList.ContactListID = id;
contactList.ContactListName = dr["CONTACT_LIST_NAME"].ToString();
contactList.AggLabels = new ObservableCollection <AggregatedLabel>()
{
new AggregatedLabel()
{
ID = Convert.ToInt32(dr["LABEL_ID"]),
Name = dr["LABEL_NAME"].ToString()
}
};
myContactDictionary.Add(id, contactList);
}
else
{
//populate existing contact lists with remaining labels
ContactList contactList = myContactDictionary[id];
contactList.AggLabels.Add
(
new AggregatedLabel()
{
ID = Convert.ToInt32(dr["LABEL_ID"]),
Name = dr["LABEL_NAME"].ToString()
}
);
}
}
}
//add to observable collection
foreach (KeyValuePair <int, ContactList> contactKeyValue in myContactDictionary)
{
ContactList contactList = contactKeyValue.Value;
myContactLists.Add(contactList);
}
conn.Close();
}
static void Main(string[] args)
{
try
{
DB2Connection con = new DB2Connection("Server = /*IP*/; UID=/*Instanz*/; PWD=*****; DATABASE=TPCH");
con.Open();
Console.Write("Land (in Großbuchstaben angeben) ? ");
String input = "";
while (input.Length == 0 || input == null)
{
input = Console.In.ReadLine();
}
Console.WriteLine();
/***********************************************************************
**
** Aufgabe 1.1 (a)
**
***********************************************************************/
Console.WriteLine("***************");
Console.WriteLine("Aufgabe 1.1 (a)");
Console.WriteLine("***************");
Console.WriteLine();
DB2Command cmd = con.CreateCommand();
cmd.CommandText = "SELECT c_name FROM customer, nation WHERE n_nationkey = c_nationkey AND n_name = '" + input + "' ";
DB2DataReader reader1 = cmd.ExecuteReader();
while (reader1.Read())
{
Console.WriteLine("Kunde: " + reader1.GetString(0));
}
reader1.Close();
Console.WriteLine();
Console.WriteLine();
/***********************************************************************
**
** Aufgabe 1.1 (b)
**
***********************************************************************/
Console.WriteLine("***************");
Console.WriteLine("Aufgabe 1.1 (b)");
Console.WriteLine("***************");
Console.WriteLine();
cmd.CommandText = "SELECT c_name FROM customer, nation WHERE n_nationkey = c_nationkey AND n_name = ? ";
cmd.Prepare();
cmd.Parameters.Add("n_name", DB2Type.VarChar, 25);
cmd.Parameters["n_name"].Value = input;
DB2DataReader reader2 = cmd.ExecuteReader();
while (reader2.Read())
{
Console.WriteLine("Kunde: " + reader2.GetString(0));
}
reader2.Close();
Console.WriteLine();
Console.WriteLine();
/***********************************************************************
**
** Aufgabe 1.2
**
***********************************************************************/
Console.WriteLine("***********");
Console.WriteLine("Aufgabe 1.2");
Console.WriteLine("***********");
Console.WriteLine();
cmd.CommandText = "SELECT c_name, c_custkey, COUNT(o_totalprice) FROM customer, nation, orders WHERE n_nationkey = c_nationkey AND c_custkey = o_custkey AND n_name = '" + input + "' GROUP BY c_name, c_custkey";
DB2DataReader reader3 = cmd.ExecuteReader();
while (reader3.Read())
{
DB2Command SPCMD = con.CreateCommand();
SPCMD.CommandText = "orders_of"; //Geb ich den namen der SP an
SPCMD.CommandType = CommandType.StoredProcedure; //Man muss noch zeigen das es SP ist
SPCMD.Parameters.Add("IN_table", DB2Type.Integer).Value = reader3.GetDB2Int32(1);
SPCMD.Parameters.Add("OUT_sql", DB2Type.Integer).Direction = ParameterDirection.Output;
DB2DataReader dr = SPCMD.ExecuteReader();
Console.WriteLine("Kunde: " + reader3.GetString(0));
Console.WriteLine("Kundenr.: " + reader3.GetString(1));
Console.WriteLine("AnzahlBestellungen: " + SPCMD.Parameters["OUT_sql"].Value);
decimal summe = 0;
bool first = true;
int days = 0;
while (dr.Read())
{
summe += Convert.ToDecimal(dr["TOTALPRICE"]);
}
Console.WriteLine("Gesamtumsatz: " + summe + " $ (USD)");
dr.Close();
}
reader1.Close();
Console.WriteLine();
Console.WriteLine();
/***********************************************************************
**
** Aufgabe 2.1
**
***********************************************************************/
Console.WriteLine("***********");
Console.WriteLine("Aufgabe 2.1");
Console.WriteLine("***********");
Console.WriteLine();
Console.WriteLine("ServerType: " + con.ServerType); //Typ des Servers
Console.WriteLine("ServerVersion: " + con.ServerVersion); //Version des Servers
Console.WriteLine("Version: " + con.ServerMajorVersion + "." + con.ServerMinorVersion); //übergeordnete Version und untergeordnete Version des Servers
Console.WriteLine("Verbindung: " + con.ConnectionString); //Ruft die Zeichenfolge ab, die zum Öffnen einer Datenbankverbindung verwendet wird
Console.WriteLine("Datenbankname: " + con.Database); //Ruft den Namen der aktuellen Datenbank ab
Console.WriteLine("InProperty1: " + con.InternalProperty1);
Console.WriteLine("InProperty7: " + con.InternalProperty7);
Console.WriteLine("BuildVersion: " + con.ServerBuildVersion); //Buildversion des Servers
Console.WriteLine("RevVersion: " + con.ServerRevisionVersion); //Überarbeitungsversion des Servers
Console.WriteLine("Status: " + con.State); //aktueller Status der Verbindung
Console.WriteLine("UserId: " + con.UserId); //UserId
Console.WriteLine();
Console.WriteLine();
/***********************************************************************
**
** Aufgabe 2.2
**
***********************************************************************/
Console.WriteLine("***********");
Console.WriteLine("Aufgabe 2.2");
Console.WriteLine("***********");
Console.WriteLine();
cmd.CommandText = "SELECT n_nationkey, n_name, n_regionkey FROM nation";
DB2DataReader reader5 = cmd.ExecuteReader();
printResult(reader5, 3);
reader5.Close();
Console.WriteLine();
Console.WriteLine();
/***********************************************************************
**
** Aufgabe 3
**
***********************************************************************/
Console.WriteLine("***********");
Console.WriteLine("Aufgabe 3");
Console.WriteLine("***********");
Console.WriteLine();
DataSet ds = new DataSet();
DB2DataAdapter da = new DB2DataAdapter("SELECT o_orderkey, o_custkey, o_orderpriority, o_totalprice FROM orders", con); //benötigt zum Füllen des ds
DB2CommandBuilder cb = new DB2CommandBuilder(da);
Console.Write("Kundennummer(o_custkey) eingeben: ");
String input2 = "";
while (input2.Length == 0 || input == null)
{
input2 = Console.In.ReadLine();
}
Console.WriteLine();
con.Close();
Console.WriteLine();
Console.WriteLine();
Console.ReadKey();
}
catch (DB2Exception e)
{
Console.Error.WriteLine(e.Message);
Console.ReadKey();
}
}
private void metroButton2_Click(object sender, EventArgs e)
{
string ConStr = "Database = " + metroTextBox3.Text.Trim() + ";User ID=inetsoft;Password = etl5boxes; server = " + metroTextBox3.Text.Trim().ToLower() + "." + metroTextBox3.Text.Trim().ToLower() + ".ei:50000; Max Pool Size=100;Persist security info =False;Pooling = True ";
string Query = @"Select
sh.SHPMNT_REF,
ship.NAME,
ship.ADDR_LINE1,
ship.ADDR_LINE2,
CONCAT(CONCAT(ship.CITY, ','), ship.COUNTRY),
cons.NAME,
cons.ADDR_LINE1,
cons.ADDR_LINE2,
CONCAT(CONCAT(cons.CITY, ','), cons.COUNTRY),
hdr.ORIGIN_NAME_AWB,
hdr.DESTIN_NAME_AWB,
hdr.FLIGHT_TO_AWB_1,
hdr.FLIGHT_BY_AWB_1,
hdr.CHRG_CODE_FRGHT_1,
hdr.CHRG_CODE_OTHER_1,
sh.PCS_GRS,
CAST(ROUND(sh.WGT_GRS_2 / 10000, 0) AS DECIMAL(18, 1)),
CAST(ROUND(sh.WGT_CHRG_2 / 10000, 0) AS DECIMAL(18, 1)),
hdr.FLIGHT_NUM_AWB_1,
hdr.HNDL_INFO_AWB_1,
hdr.HNDL_INFO_AWB_2,
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( '!',concat(concat(itm.REMARKS_AWB_PFX,' '),itm.REMARKS_AWB)))) as VARCHAR(1024)), 3),
sl.MAWB_BL_NO
From
EXPORT.SHPMNT_HDR sh
inner join IASDB.SHIP_LOG sl on sl.INVOICE_NO = sh.SHPMNT_REF
inner join HELPDB.CLIENT ship on ship.CLIENT_NO = sh.SHIPPER_ID
inner join HELPDB.CLIENT cons on cons.CLIENT_NO = sh.CONSIGN_ID
inner join EXPORT.AIR_FRGHT_HDR hdr on hdr.INVOICE_REF = sh.SHPMNT_REF
inner join EXPORT.AIR_FRGHT_ITEM itm on itm.CARRIER_REF = hdr.CARRIER_REF
where sh.SHPMNT_REF = '" + metroTextBox1.Text.Trim() + @"'
Group by
ship.NAME,
ship.ADDR_LINE1,
ship.ADDR_LINE2,
ship.CITY,
ship.COUNTRY,
sh.SHPMNT_REF,
cons.NAME,
cons.ADDR_LINE1,
cons.ADDR_LINE2,
cons.city,
cons.COUNTRY,
CAST(ROUND(sh.WGT_GRS_2 / 10000, 0) AS DECIMAL(18, 1)),
CAST(ROUND(sh.WGT_CHRG_2 / 10000, 0) AS DECIMAL(18, 1)),
hdr.ORIGIN_NAME_AWB,
hdr.DESTIN_NAME_AWB,
hdr.FLIGHT_TO_AWB_1,
hdr.FLIGHT_BY_AWB_1,
hdr.CHRG_CODE_FRGHT_1,
hdr.CHRG_CODE_OTHER_1,
hdr.FLIGHT_NUM_AWB_1,
sh.PCS_GRS,
hdr.HNDL_INFO_AWB_1,
hdr.HNDL_INFO_AWB_2,
sl.MAWB_BL_NO
";
using (DB2Connection myconnection = new DB2Connection(ConStr))
{
myconnection.Open();
DB2Command cmd = myconnection.CreateCommand();
cmd.CommandText = Query;
DB2DataReader rd = cmd.ExecuteReader();
if (rd.HasRows)
{
while (rd.Read())
{
//richTextBox1.Text = richTextBox1.Text + rd.GetValue(0).ToString() + " " + rd.GetValue(1).ToString();
Excel.Application app = new Excel.Application();
//string filepath = @"HAWBTemp.xlsx";
//Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + "/HAWBTemplate.xlsx", ReadOnly: false, Editable: true);
Excel.Worksheet worksheet = workbook.Worksheets.Item[1];
//worksheet.Cells[5, 7].Value = rd.GetValue(1).ToString();
//Shipper Update
worksheet.Range["D1"].Value = rd.GetValue(22).ToString();
worksheet.Range["N1"].Value = rd.GetValue(0).ToString();
worksheet.Range["M51"].Value = rd.GetValue(0).ToString();
worksheet.Range["B3"].Value = rd.GetValue(1).ToString();
worksheet.Range["B4"].Value = rd.GetValue(2).ToString();
worksheet.Range["B5"].Value = rd.GetValue(3).ToString();
worksheet.Range["B6"].Value = rd.GetValue(4).ToString();
//Consignee Update
worksheet.Range["B8"].Value = rd.GetValue(5).ToString();
worksheet.Range["B9"].Value = rd.GetValue(6).ToString();
worksheet.Range["B10"].Value = rd.GetValue(7).ToString();
worksheet.Range["B11"].Value = rd.GetValue(8).ToString();
//Origin-Destination
worksheet.Range["B18"].Value = rd.GetValue(9).ToString();
worksheet.Range["B21"].Value = rd.GetValue(10).ToString();
//Dest-First Flight
worksheet.Range["B19"].Value = rd.GetValue(11).ToString();
worksheet.Range["C19"].Value = rd.GetValue(12).ToString();
//Prepaid-Collect
if (rd.GetValue(13).ToString() == "P")
{
worksheet.Range["K19"].Value = " P";
}
else
{
worksheet.Range["K19"].Value = " C";
}
if (rd.GetValue(14).ToString() == "P")
{
worksheet.Range["L19"].Value = "P";
}
else
{
worksheet.Range["L19"].Value = " C";
}
//Weight-Pieces
worksheet.Range["B27"].Value = rd.GetValue(15).ToString();
worksheet.Range["C27"].Value = rd.GetValue(16).ToString();
worksheet.Range["G27"].Value = rd.GetValue(17).ToString();
worksheet.Range["B37"].Value = rd.GetValue(15).ToString();
worksheet.Range["C37"].Value = rd.GetValue(16).ToString();
//Flight
worksheet.Range["F21"].Value = rd.GetValue(18).ToString();
//Handling Information
worksheet.Range["C23"].Value = rd.GetValue(19).ToString();
worksheet.Range["C24"].Value = rd.GetValue(20).ToString();
worksheet.Range["O46"].Value = metroTextBox2.Text;
//Data
DateTime dt = DateTime.Now;
worksheet.Range["I49"].Value = dt.ToShortDateString();
//Description
String description = rd.GetValue(21).ToString();
List <string> descResult = description.Split('!').ToList();
descResult = descResult.Where(s => !string.IsNullOrWhiteSpace(s)).Distinct().ToList();
//richTextBox1.Text = richTextBox1.Text + descResult[0] +"///////" + descResult[1]+ "//////" + descResult[2];
for (int i = descResult.Count; i <= 7; i++)
{
descResult.Add("");
}
worksheet.Range["B28"].Value = descResult[0];
worksheet.Range["B29"].Value = descResult[1];
worksheet.Range["B30"].Value = descResult[2];
worksheet.Range["B31"].Value = descResult[3];
worksheet.Range["B32"].Value = descResult[4];
worksheet.Range["B33"].Value = descResult[5];
worksheet.Range["B34"].Value = descResult[6];
worksheet.Range["B35"].Value = descResult[7];
/*String remarks = rd.GetValue(22).ToString();
* List<string> remarksResult = remarks.Split('!').ToList();
* remarksResult = remarksResult.Where(s => !string.IsNullOrWhiteSpace(s)).Distinct().ToList();
* for (int i = remarksResult.Count; i <= 7; i++)
* {
* remarksResult.Add("");
* }
*
* MetroMessageBox.Show(this,remarksResult[1]);
* MetroMessageBox.Show(this, remarksResult[2]);
* MetroMessageBox.Show(this, remarksResult[3]);
* MetroMessageBox.Show(this, remarksResult[4]);
* MetroMessageBox.Show(this, remarksResult[5]);
*
* worksheet.Range["M28"].Value = remarksResult[0];
* worksheet.Range["M29"].Value = remarksResult[1];
* worksheet.Range["M30"].Value = remarksResult[2];
* worksheet.Range["M31"].Value = remarksResult[3];
* worksheet.Range["M32"].Value = remarksResult[4];
* worksheet.Range["M33"].Value = remarksResult[5];
* worksheet.Range["M34"].Value = remarksResult[6];
* worksheet.Range["M35"].Value = remarksResult[7];*/
workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "/" + metroTextBox1.Text.Trim() + ".pdf");
app.Application.ActiveWorkbook.Save();
workbook.Close(0);
app.Application.Quit();
app.Quit();
MetroFramework.MetroMessageBox.Show(this, "House Created!");
}
}
}
}
