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 string scriptFK(string col, string tab)
{
string v = "";
string cref = "";
string tref = "";
if (!isfk(col, tab))
{
return("Nada");
}
DB2Command cmd = new DB2Command("select ref.constname as FK, ref.tabname as Tabla, key.colname as Ref_Col, key.tabname as Ref_Tabla from syscat.keycoluse as key inner join syscat.references as ref on key.constname = ref.refkeyname where ref.constname = '" + col + "_FK' and ref.tabname = '" + tab + "'", connect);
connect.Open();
using (DB2DataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
cref = dr.GetString(2);
tref = dr.GetString(3);
}
dr.Close();
}
connect.Close();
v = "alter table " + tab + " add constraint " + col + "_FK foreign key (" + col + ") references " + tref + " (" + cref + ") not enforced";
return(v);
}
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 bool isPK(string col, string tab)
{
string v = "";
try
{
DB2Command cmd = new DB2Command("select colname from syscat.keycoluse where colname = '" + col + "' and tabname = '" + tab + "'", connect);
if (!connect.IsOpen)
{
connect.Open();
}
using (DB2DataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
v = dr.GetString(0);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
if (v == "")
{
return(false);
}
else
{
return(true);
}
}
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 bool isindex(string col, string tab)
{
string v = "";
try
{
DB2Command cmd = new DB2Command("select name from sysibm.sysindexes where name = '" + col + "' and tbname = '" + tab + "' and tbcreator = 'USUARIO'", connect);
connect.Open();
using (DB2DataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
v = dr.GetString(0);
}
dr.Close();
}
connect.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
connect.Close();
}
if (v == "")
{
return(false);
}
else
{
return(true);
}
}
public bool isfk(string col, string tab)
{
string v = "";
try
{
DB2Command cmd = new DB2Command("select constname from syscat.references where constname = '" + col + "_FK' and tabname = '" + tab + "'", connect);
connect.Open();
using (DB2DataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
v = dr.GetString(0);
}
dr.Close();
}
connect.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
connect.Close();
}
if (v == "")
{
return(false);
}
else
{
return(true);
}
}
private string Type(string col, string tab)
{
string v = "";
try
{
DB2Command cmd = new DB2Command("select typename from syscat.columns where tabname = '" + tab + "' and colname = '" + col + "'", connect);
if (!connect.IsOpen)
{
connect.Open();
}
using (DB2DataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
v = dr.GetString(0);
}
}
}
catch (DB2Exception e)
{
System.Windows.Forms.MessageBox.Show(e.ToString());
}
return(v);
}
public string PoC(string name)
{
string v = "";
DB2Command cmd = new DB2Command(" select routinetype from syscat.routines where routinename = '" + name + "'", connect);
try
{
connect.Open();
using (DB2DataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
v = dr.GetString(0);
}
dr.Close();
}
connect.Close();
}
catch (DB2Exception e)
{
System.Windows.Forms.MessageBox.Show(e.ToString());
connect.Close();
}
return(v);
}
/// <summary>
/// 获取登记日切日期
/// </summary>
/// <returns></returns>
public string GetDjrqrq()
{
string djrq = string.Empty;
string cmdString = "select * from djrqb";
this.Open();
try
{
db2Cmd = new DB2Command(cmdString, this.db2Conn);
DB2DataReader dr = db2Cmd.ExecuteReader();
while (dr.Read())
{
djrq = dr.GetString(7);
}
}
catch (Exception ex)
{
LogHelper.WriteLogException("Execute sql command error in Db2Operation.GetDjrqrq()", ex);
throw;
}
finally
{
this.Close();
}
return(djrq);
}
public List <string[]> getAllTables()
{
List <string[]> ret = new List <string[]>();
string sql = @"SELECT TRIM(TABNAME), VALUE(REMARKS,'') FROM syscat.tables WHERE TABSCHEMA = '" + m_TableSchema + @"' AND TYPE = 'T' ORDER BY TABNAME";
if (!select(sql))
{
return(ret);
}
while (m_reader.Read())
{
string tblname = m_reader.GetString(0);
string note = m_reader.GetString(1);
string[] sarr = { tblname, note };
ret.Add(sarr);
}
return(ret);
}
/// <summary>
/// 根据日切日期查询zbmxz结果集
/// </summary>
/// <param name="rqrq">日切日期</param>
/// <returns></returns>
public List <ZbmxzModel> GetZbmxzByRqrq(string rqrq)
{
List <ZbmxzModel> list = new List <ZbmxzModel>();
ZbmxzModel zbmxz;
string cmdString = "select * from zbmxz where djrq >= '" + rqrq + "'";
this.Open();
try
{
db2Cmd = new DB2Command(cmdString, this.db2Conn);
DB2DataReader dr = db2Cmd.ExecuteReader();
while (dr.Read())
{
zbmxz = new ZbmxzModel();
zbmxz.Bc = dr.GetInt32(1).ToString();
zbmxz.Zh = dr.GetString(2);
zbmxz.Jyrq = dr.GetDate(3).ToShortDateString();
zbmxz.Jysj = dr.GetDateTime(4).ToLongTimeString();
zbmxz.Fse = dr.GetDecimal(5).ToString();
zbmxz.Ye = dr.GetDecimal(6).ToString();
zbmxz.Yhls = dr.GetString(7);
zbmxz.Pjhm = dr.GetString(8);
zbmxz.Jdbz = dr.GetString(9);
zbmxz.Ywlx = dr.GetString(10);
zbmxz.Dfzh = dr.GetString(11);
zbmxz.Dfhm = dr.GetString(12);
zbmxz.Zxjsh = dr.GetString(13);
list.Add(zbmxz);
}
}
catch (Exception ex)
{
LogHelper.WriteLogException("Execute sql command error in Db2Operation.GetZbmxzByRqrq()", ex);
throw;
}
finally
{
this.Close();
}
return(list);
}
public string coltab(string col)
{
string v = "";
DB2Command cmd = new DB2Command("select tabname from syscat.columns where colname = '" + col + "'", connect);
if (!connect.IsOpen)
{
connect.Open();
}
using (DB2DataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
v = dr.GetString(0);
}
dr.Close();
}
connect.Close();
return(v);
}
public string ddl(string q)
{
string v = "";
DB2Command cmd = new DB2Command(q, connect);
if (!connect.IsOpen)
{
connect.Open();
}
using (DB2DataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
v = dr.GetString(0);
}
dr.Close();
}
connect.Close();
return(v);
}
private string tabValuesScript(string tab)
{
string c = "";
string q = "";
int x = 0;
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 (x == 0)
{
if (colCant(tab) == 1)
{
q = c + " " + Type(c, tab) + colLength(c, tab);
x++;
}
else
{
q = q + c + " " + Type(c, tab) + colLength(c, tab);
x++;
}
}
else
{
q = q + ", " + c + " " + Type(c, tab) + colLength(c, tab);
x++;
}
}
}
return(q);
}
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));
}
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();
}
}
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);
}
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));
}
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);
}
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);
}
public override char GetChar(int ordinal)
{
return(source.GetString(ordinal)[0]);
}
