public void Execute(ISemanticDatabase sdb, string sql) { NpgsqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; sdb.LogSqlStatement(cmd.CommandText); cmd.ExecuteNonQuery(); cmd.Dispose(); }
public void CreateTable(ISemanticDatabase sdb, string st, List <Tuple <string, Type> > fieldTypes) { StringBuilder sb = new StringBuilder("create table " + st + " ("); List <string> fields = new List <string>(); fields.Add("ID INTEGER PRIMARY KEY AUTOINCREMENT"); fields.AddRange(fieldTypes.Select(ft => ft.Item1)); string fieldList = String.Join(", ", fields); sb.Append(fieldList); sb.Append(");"); Execute(sdb, sb.ToString()); }
/// <summary> /// Return a list of all tables in the database. /// </summary> public List<string> GetTables(ISemanticDatabase sdb) { NpgsqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT table_name FROM information_schema.tables WHERE table_schema='public'"; sdb.LogSqlStatement(cmd.CommandText); NpgsqlDataReader reader = cmd.ExecuteReader(); List<string> tableNames = new List<string>(); while (reader.Read()) { tableNames.Add(reader[0].ToString()); } return tableNames; }
/// <summary> /// Return a list of all tables in the database. /// </summary> public List<string> GetColumns(ISemanticDatabase sdb, string tableName) { NpgsqlCommand cmd = conn.CreateCommand(); // currval() only works after an INSERT (which has executed nextval() ), in the same session. cmd.CommandText = "select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = " + tableName.SingleQuote(); sdb.LogSqlStatement(cmd.CommandText); NpgsqlDataReader reader = cmd.ExecuteReader(); List<string> columnNames = new List<string>(); while (reader.Read()) { columnNames.Add(reader[0].ToString()); } return columnNames; }
/// <summary> /// Return a list of all tables in the database. /// </summary> public List <string> GetTables(ISemanticDatabase sdb) { NpgsqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT table_name FROM information_schema.tables WHERE table_schema='public'"; sdb.LogSqlStatement(cmd.CommandText); NpgsqlDataReader reader = cmd.ExecuteReader(); List <string> tableNames = new List <string>(); while (reader.Read()) { tableNames.Add(reader[0].ToString()); } return(tableNames); }
/// <summary> /// Return a list of all tables in the database. /// </summary> public List <string> GetColumns(ISemanticDatabase sdb, string tableName) { SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = "pragma table_info('" + tableName + "')"; sdb.LogSqlStatement(cmd.CommandText); SQLiteDataReader reader = cmd.ExecuteReader(); List <string> columnNames = new List <string>(); while (reader.Read()) { columnNames.Add(reader[1].ToString()); } return(columnNames); }
/// <summary> /// Return a list of all tables in the database. /// </summary> public List <string> GetTables(ISemanticDatabase sdb) { SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table'"; sdb.LogSqlStatement(cmd.CommandText); SQLiteDataReader reader = cmd.ExecuteReader(); List <string> tableNames = new List <string>(); while (reader.Read()) { tableNames.Add(reader[0].ToString()); } return(tableNames); }
/// <summary> /// Return a list of all tables in the database. /// </summary> public List <string> GetColumns(ISemanticDatabase sdb, string tableName) { NpgsqlCommand cmd = conn.CreateCommand(); // currval() only works after an INSERT (which has executed nextval() ), in the same session. cmd.CommandText = "select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = " + tableName.SingleQuote(); sdb.LogSqlStatement(cmd.CommandText); NpgsqlDataReader reader = cmd.ExecuteReader(); List <string> columnNames = new List <string>(); while (reader.Read()) { columnNames.Add(reader[0].ToString()); } return(columnNames); }
public void CreateTable(ISemanticDatabase sdb, string st, List <Tuple <string, Type> > fieldTypes) { Dictionary <Type, string> typeMap = new Dictionary <Type, string>() { { typeof(int), "integer" }, { typeof(long), "int8" }, { typeof(bool), "boolean" }, { typeof(string), "text" }, { typeof(char), "char" }, { typeof(float), "float8" }, { typeof(double), "float8" }, { typeof(decimal), "decimal" }, { typeof(DateTime), "timestamptz" }, }; StringBuilder sb = new StringBuilder("create table " + st + " ("); List <string> fields = new List <string>(); fields.Add("ID SERIAL PRIMARY KEY"); // Everybody does it differently! fieldTypes.ForEach(ft => { string typeName; if (!typeMap.TryGetValue(ft.Item2, out typeName)) { throw new Exception("Type " + ft.Item2.ToString() + " not supported."); } fields.Add(ft.Item1 + " " + typeName); }); string fieldList = String.Join(", ", fields); sb.Append(fieldList); sb.Append(");"); Execute(sdb, sb.ToString()); }
public void DropTable(ISemanticDatabase sdb, string tableName) { Execute(sdb, "drop table " + Delimited(tableName)); }
public void CreateTable(ISemanticDatabase sdb, string st, List<Tuple<string, Type>> fieldTypes) { Dictionary<Type, string> typeMap = new Dictionary<Type, string>() { {typeof(int), "integer"}, {typeof(long), "int8"}, {typeof(bool), "boolean"}, {typeof(string), "text"}, {typeof(char), "char"}, {typeof(float), "float8"}, {typeof(double), "float8"}, {typeof(decimal), "decimal"}, {typeof(DateTime), "timestamptz"}, }; StringBuilder sb = new StringBuilder("create table " + st + " ("); List<string> fields = new List<string>(); fields.Add("ID SERIAL PRIMARY KEY"); // Everybody does it differently! fieldTypes.ForEach(ft => { string typeName; if (!typeMap.TryGetValue(ft.Item2, out typeName)) { throw new Exception("Type " + ft.Item2.ToString() + " not supported."); } fields.Add(ft.Item1 + " " + typeName); }); string fieldList = String.Join(", ", fields); sb.Append(fieldList); sb.Append(");"); Execute(sdb, sb.ToString()); }