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); }