internal static DataTable GetDatabasePrimaryKeysDataSource(sqlservers server, DbConnection con)
{
DataTable data = new DataTable();
DataColumn c0 = new DataColumn("Column");
DataColumn c1 = new DataColumn("Table");
data.Columns.Add(c0);
data.Columns.Add(c1);
List <string> tables = DbDataHelper.getTables(con);
foreach (string table in tables)
{
string table_info = new SqlBuilderFactory(server).Create(con.Database).describeTableSql(table);
using (var r = new DbCommandFactory(con, table_info).Create().ExecuteReader())
{
while (r.Read())
{
if (r.GetInt32(5) >= 1)
{
DataRow row = data.NewRow();
row["Column"] = r.GetString(1);
row["Table"] = table;
data.Rows.Add(row);
}
}
}
}
return(data);
}
internal static int getIntSingleResult(DbConnection con, string sql)
{
int result = 0;
using (var reader = new DbCommandFactory(con, sql).Create().ExecuteReader())
{
while (reader.Read())
{
result = reader.GetInt32(0);
}
}
return(result);
}
internal static List <int> getIntData(DbConnection con, string sql)
{
var data = new List <int>();
try
{
using (var reader = new DbCommandFactory(con, sql).Create().ExecuteReader())
{
while (reader.Read())
{
data.Add(reader.GetInt32(0));
}
}
}
catch (DbException ex)
{
sql = ex.Message;
#if DEBUG
Console.WriteLine(ex.Message);
#endif
}
Terminal.MainTerminal.AppendText(sql);
return(data);
}
internal static List <string> getTableFields(DbConnection con, string table)
{
var data = new List <string>();
string command = "";
try
{
command = new SqlBuilderFactory(con).Create(con.Database).describeTableSql(table);
using (var reader = new DbCommandFactory(con, command).Create().ExecuteReader())
{
if (!sql.Utils.IsDbEmbedded(sql.Utils.GetDbTypeEnum(con)))
{
while (reader.Read())
{
data.Add(reader.GetString(0) + " " + reader.GetString(1) + ", Nullable:" + reader.GetString(2));
}
}
else
{
while (reader.Read())
{
data.Add(reader.GetString(1) + " " + reader.GetString(2) + ", Nullable:" + (reader.GetInt32(3) == 1 ? "NO" : "YES"));
}
}
}
}
catch (DbException ex)
{
command = ex.Message;
#if DEBUG
Console.WriteLine(ex.Message);
#endif
}
Terminal.MainTerminal.AppendText(command);
return(data);
}
//returns all fields from all tables
internal static List <Table> getTablesInfo(sqlservers server, DbConnection con)
{
var list = new List <Table>();
try
{
if (sql.Utils.IsDbEmbedded(server.db_type))
{
//case of embedded like sqlite the process to get all fields from all tables is very different.
//first get all tables
List <string> tables = getTables(con);
//and then for every each one get table fields
foreach (string table in tables)
{
string table_info = new SqlBuilderFactory(server).Create(con.Database).describeTableSql(table);
using (var r = new DbCommandFactory(con, table_info).Create().ExecuteReader())
{
while (r.Read())
{
list.Add(new Table(table, r.GetString(1), r.GetString(2), r.GetInt32(3) == 0 ? "YES" : "NO", 0));
}
}
Terminal.MainTerminal.AppendText(table_info);
}
}
else
{
string sql = new SqlBuilderFactory(server).Create(con.Database).getAllFieldsFromAllTablesInDb();
using (var r = new DbCommandFactory(con, sql).Create().ExecuteReader())
{
while (r.Read())
{
list.Add(new Table(r.GetString(0), r.GetString(1), r.GetString(2), r.GetString(3),
r.GetValue(4) != DBNull.Value ? r.GetInt64(4) : default));
}
}
Terminal.MainTerminal.AppendText(sql);
}
}
catch (DbException ex)
{
Terminal.MainTerminal.AppendText(ex.Message);
#if DEBUG
Console.WriteLine(ex.Message);
#endif
}
return(list);
}