static void Main(string[] args)
{
var b = new NpgsqlConnectionStringBuilder() { Host = "milkyway", Port = 5433, Username = "******", Password = "******", Database = "db" };
using (DbConnection conn = new NpgsqlConnection(b.ToString()))
{
conn.Open();
var tables = new List<string>();
var all = conn.GetSchema("Tables");
foreach (DataRow r in all.Rows)
{
//table_catalog //table_schema //table_name //table_type
var schema = r["table_schema"];
var table = r["table_name"];
var type = r["table_type"];
if ("kernel".Equals(schema.ToString()))
tables.Add(table.ToString());
}
foreach (var table in tables)
{
Console.WriteLine("Table: " + table);
var tableSchema = conn.GetSchema("Columns", new string[] { null, null, table });
foreach (DataRow row in tableSchema.Rows)
{
Console.WriteLine("Column = {0}. Type = {1}. Default = {2}. Nullable = {3}. Text lenght = {4}. Numeric precision = {5}.",
row["column_name"],
row["data_type"],
row["column_default"],
row["is_nullable"],
row["character_maximum_length"],
row["numeric_precision"]);
}
}
/*var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"SELECT * FROM kernel.users;";
using (var reader = cmd.ExecuteReader())
while (reader.Read())
Console.WriteLine(string.Format("id = {0}, user = {1};", reader.GetString(0), reader.GetString(1)));
*/
}
Console.ReadKey();
}
public override TableSchema[] GetTables(string connectionString, DatabaseSchema database)
{
List<TableSchema> tableSchemas = new List<TableSchema>();
var oleConn = new NpgsqlConnection();
oleConn.ConnectionString = connectionString;
using (var conn = new NpgsqlConnection(connectionString))
{
this.ConnectionString = connectionString;
string[] restrictions = new string[4];
restrictions[0] = conn.Database;
restrictions[1] = "public";
conn.ConnectionString = connectionString;
conn.Open();
//var s = conn.GetSchema("Tables",new string[] { conn.Database, "public", null, null });
List<string> tableNames = new List<string>();
Dictionary<string, Dictionary<string, ForeignKeyInfo>> fkMap = null;
DataTable tt = conn.GetSchema("Tables", restrictions);
foreach (DataRow tRow in tt.Rows)
{
tableNames.Add(tRow[2].ToString());
}
fkMap = this.GetForeignKeys(
conn, tableNames);
foreach (DataRow tRow in tt.Rows)
{
var tableName = tRow[2].ToString();
tableSchemas.Add(this.GetTable(tableName, conn, oleConn, fkMap.ContainsKey(tableName) ? fkMap[tableName] : null));
}
oleConn.Close();
//tt.WriteXml("1.xml");
}
return tableSchemas.ToArray();
}
public void GetSchema()
{
using (NpgsqlConnection c = new NpgsqlConnection())
{
DataTable metaDataCollections = c.GetSchema();
Assert.IsTrue(metaDataCollections.Rows.Count > 0, "There should be one or more metadatacollections returned. No connectionstring is required.");
}
}
private void LoadDatabases()
{
string[] databaseList = new string[] { };
NpgsqlConnection cn = null;
try
{
if ((_connStrBuilder != null) && !String.IsNullOrEmpty(_connStrBuilder.Host))
{
cn = new NpgsqlConnection(_connStrBuilder.ConnectionString);
cn.Open();
DataTable databases = cn.GetSchema("Databases", null);
databaseList = (from r in databases.AsEnumerable()
let dbName = r.Field<string>("database_name")
where !dbName.ToLower().StartsWith("template")
select dbName).OrderBy(t => t).ToArray();
}
}
catch (Exception ex)
{
if (ex is NpgsqlException)
MessageBox.Show(ex.Message, "PostgreSQL Error", MessageBoxButton.OK, MessageBoxImage.Error);
else
MessageBox.Show(ex.Message, "HBIC Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
if ((cn != null) && (cn.State != ConnectionState.Closed)) cn.Close();
_databases = databaseList;
OnPropertyChanged("Databases");
if (_databases.Length == 1) _connStrBuilder.Database = _databases[0];
OnPropertyChanged("Database");
}
}
private TableSchema GetTable(string tableName, NpgsqlConnection conn, NpgsqlConnection pkConn,
Dictionary<string,ForeignKeyInfo> fkOneTable)
{
string[] restrictions = new string[4];
restrictions[0] = conn.Database;
restrictions[1] = "public";
restrictions[2] = tableName;
DataTable tt = conn.GetSchema("Tables", restrictions);
if (tt.Rows.Count == 0)
return null;
TableSchema tableSchema = new PostgresTableSchema();
tableSchema.Name = tt.Rows[0]["TABLE_NAME"].ToString();
restrictions = new string[4];
restrictions[0] = conn.Database;
restrictions[1] = "public";
restrictions[2] = tableSchema.Name;
DataTable ff = conn.GetSchema("Columns", restrictions);
restrictions[2] = tableSchema.Name;
//if (pkConn.State == ConnectionState.Closed)
//{
// pkConn.Open();
//}
//List<string> pkColumns = new List<string>();
//string[] parameters = new string[] { conn.Database, "public", tableSchema.Name };
//DataTable pkTable = pkConn.GetSchema("Index", parameters);
////pkConn.Close();
//foreach (DataRow indexRow in pkTable.Rows)
//{
// pkColumns.Add(indexRow["column_name"].ToString());
//}
string sql = string.Format(@"
select a.attname as column_name,
(case
when atttypmod-4>0 then atttypmod-4
else 0
end)CHARACTER_MAXIMUM_LENGTH,
(case
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y'
else 'N'
end) as P,
(case
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y'
else 'N'
end) as U,
(case
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y'
else 'N'
end) as R,
(case
when a.attnotnull=true then 'Y'
else 'N'
end) as nullable,
col_description(a.attrelid,a.attnum) as comment,'XEditText' as control,
c.relname,a.attname as column_name, (case
when a.attnotnull=true then 'Y'
else 'N'
end) as IS_NULLABLE,format_type(a.atttypid,a.atttypmod) as data_type,0 as NUMERIC_PRECISION,0 as NUMERIC_SCALE,0 as CHARACTER_OCTET_LENGTH,
'' as Value
from pg_attribute a inner join pg_class c on a.attrelid = c.oid where c.relname ='{0}' and a.attstattarget = -1", tableName);
DataSet ds = PostgresSqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
Dictionary<string, bool> dict_IsIdentity = new Dictionary<string, bool>();
Dictionary<string,string> dict_Description = new Dictionary<string,string>();
List<string> pkColumns = new List<string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
dict_IsIdentity.Add((string)row["COLUMN_NAME"], false);
if (row["COMMENT"] != DBNull.Value)
{
dict_Description.Add((string)row["COLUMN_NAME"], (string)row["COMMENT"]);
}
if (row["P"] != DBNull.Value && row["P"].ToString() == "Y")
{
pkColumns.Add((string)row["COLUMN_NAME"]);
}
}
foreach (DataRow fRow in ff.Rows)
{
string name = fRow["COLUMN_NAME"].ToString();
bool allowDBNull = fRow["IS_NULLABLE"].ToString().Equals("NO") ? false : true;
string dataType = fRow["DATA_TYPE"].ToString();
string nativeType = fRow["DATA_TYPE"].ToString();
byte precision = 0;
byte.TryParse(fRow["NUMERIC_PRECISION"].ToString(), out precision);
int scale = 0;
int.TryParse(fRow["NUMERIC_SCALE"].ToString(), out scale);
int size = 0;
int.TryParse(fRow["CHARACTER_MAXIMUM_LENGTH"].ToString(), out size);
if (size == 0)
{
int.TryParse(fRow["CHARACTER_OCTET_LENGTH"].ToString(), out size);
}
bool isPk = pkColumns.Contains(name);
PostgresColumnSchema columnSchema = new PostgresColumnSchema(
isPk, name, dataType, nativeType, size, precision, scale, allowDBNull);
tableSchema.Columns.Add(columnSchema);
columnSchema.IsIdent = fRow["column_default"] != DBNull.Value ?
fRow["column_default"].ToString().StartsWith("nextval('") : false;
columnSchema.Description = dict_Description.ContainsKey(name) ? dict_Description[name] : "";
if (fkOneTable != null)
{
if (fkOneTable.ContainsKey(columnSchema.Name))
{
ForeignKeyInfo fkInfo = fkOneTable[columnSchema.Name];
columnSchema.ForeignKeyTable = fkInfo.FK_Table;
columnSchema.ForeignKeyColumn = fkInfo.FK_Column;
}
}
}
//获得外键
//DataTable fk = conn.GetSchema("ForeignKeys", restrictions);
//foreach (DataRow fkRow in fk.Rows)
//{
// fk.WriteXml("1.xml");
//}
return tableSchema;
}
public static bool DatabaseHelperTableExists(string tableName)
{
NpgsqlConnection connection = new NpgsqlConnection(ConnectionString.GetWriteConnectionString());
string[] restrictions = new string[4];
restrictions[2] = tableName;
connection.Open();
DataTable table = connection.GetSchema("Tables", restrictions);
connection.Close();
if (table != null)
{
return (table.Rows.Count > 0);
}
return false;
}
