codesmith连接postgresql修复bug
转：CodeSmith7代码生成器针对PostgreSQL数据库无法使用的Bug修复全过程
我自己又修改过，完整的PostgreSQLSchemaProvider.cs文件如下
1 using Npgsql; 2 using NpgsqlTypes; 3 using System; 4 using System.Collections.Generic; 5 using System.Data; 6 using System.Text.RegularExpressions; 7 8 namespace SchemaExplorer 9 { 10 public class PostgreSQLSchemaProvider : IDbSchemaProvider, IDbConnectionStringEditor 11 { 12 public string Name 13 { 14 get 15 { 16 return "PostgreSQLSchemaProvider"; 17 } 18 } 19 20 public string Description 21 { 22 get 23 { 24 return "PostgreSQL Schema Provider"; 25 } 26 } 27 28 public string ConnectionString 29 { 30 get 31 { 32 return string.Empty; 33 } 34 } 35 36 public bool EditorAvailable 37 { 38 get 39 { 40 return false; 41 } 42 } 43 44 public bool ShowEditor(string currentConnectionString) 45 { 46 return false; 47 } 48 49 public TableSchema[] GetTables(string connectionString, DatabaseSchema database) 50 { 51 List<TableSchema> list = new List<TableSchema>(); 52 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 53 { 54 npgsqlConnection.Open(); 55 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand("select tablename, tableowner,obj_description(relfilenode,'pg_class') as pg_description from pg_catalog.pg_tables as t left join pg_catalog.pg_class as c on t.tablename = c.relname where t.schemaname = 'public' order by t.tablename", npgsqlConnection)) 56 { 57 using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) 58 { 59 while (npgsqlDataReader.Read()) 60 { 61 if (!npgsqlDataReader.IsDBNull(0) && npgsqlDataReader.GetString(0).ToUpper() != "CODESMITH_EXTENDED_PROPERTIES") 62 { 63 list.Add(new TableSchema(database, npgsqlDataReader.GetString(0), npgsqlDataReader.IsDBNull(1) ? string.Empty : npgsqlDataReader.GetString(1), DateTime.MinValue, new ExtendedProperty[] { new ExtendedProperty("CS_Description", npgsqlDataReader.IsDBNull(2) ? string.Empty : npgsqlDataReader.GetString(2), DbType.String, PropertyStateEnum.ReadOnly) })); 64 } 65 } 66 } 67 } 68 } 69 return list.ToArray(); 70 } 71 72 public IndexSchema[] GetTableIndexes(string connectionString, TableSchema table) 73 { 74 List<IndexSchema> list = new List<IndexSchema>(); 75 if (table.Columns == null) 76 table.Columns.AddRange(GetTableColumns(connectionString, table)); 77 DataSet ds = new DataSet(); 78 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 79 { 80 npgsqlConnection.Open(); 81 string text = string.Format("select schemaname, tablename, indexname, tablespace from pg_catalog.pg_indexes where schemaname='public' and tablename = '{0}'", table.Name); 82 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) 83 { 84 using (NpgsqlDataAdapter nda = new NpgsqlDataAdapter(npgsqlCommand)) 85 { 86 nda.Fill(ds, "pg_indexes"); 87 } 88 89 foreach (DataRow item in ds.Tables["pg_indexes"].Rows) 90 { 91 string @string = string.Empty; 92 @string = item.Field<string>("indexname"); 93 if (!string.IsNullOrEmpty(@string)) 94 { 95 string text2 = string.Format("SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS \"tablespace\", a.attname as \"colname\", x.indisunique as \"unique\", x.indisprimary as \"primary\", x.indisclustered as \"clustered\" FROM pg_catalog.pg_index x JOIN pg_catalog.pg_class c ON c.oid = x.indrelid JOIN pg_catalog.pg_class i ON i.oid = x.indexrelid JOIN pg_catalog.pg_attribute a ON a.attrelid = i.relfilenode LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_tablespace t ON t.oid = i.reltablespace WHERE c.relkind = 'r'::\"char\" AND i.relkind = 'i'::\"char\" AND n.nspname='public' AND c.relname='{0}' AND i.relname ='{1}'", table.Name, @string); 96 97 using (NpgsqlCommand npgsqlCommand2 = new NpgsqlCommand(text2, npgsqlConnection)) 98 { 99 using (NpgsqlDataAdapter nda2 = new NpgsqlDataAdapter(npgsqlCommand2)) 100 { 101 nda2.Fill(ds, "pg_catalog"); 102 } 103 } 104 List<string> list2 = new List<string>(); 105 bool? isPrimaryKey = false; 106 bool? isUnique = false; 107 bool? isClustered = false; 108 foreach (DataRow item2 in ds.Tables["pg_catalog"].Rows) 109 { 110 isPrimaryKey = item2.Field<bool?>("primary");// (!npgsqlDataReader2.IsDBNull(6) && npgsqlDataReader2.GetBoolean(6)); 111 isUnique = item2.Field<bool?>("unique"); //(!npgsqlDataReader2.IsDBNull(5) && npgsqlDataReader2.GetBoolean(5)); 112 isClustered = item2.Field<bool?>("clustered"); //(!npgsqlDataReader2.IsDBNull(7) && npgsqlDataReader2.GetBoolean(7)); 113 list2.Add(item2.Field<string>("colname")); 114 } 115 list.Add(new IndexSchema(table, @string, isPrimaryKey ?? false, isUnique ?? false, isClustered ?? false, list2.ToArray())); 116 list2.Clear(); 117 ds.Tables["pg_catalog"].Clear(); 118 } 119 } 120 } 121 } 122 return list.ToArray(); 123 } 124 125 public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table) 126 { 127 List<ColumnSchema> list = new List<ColumnSchema>(); 128 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 129 { 130 npgsqlConnection.Open(); 131 string text = string.Format("select column_name, is_nullable, character_maximum_length, numeric_precision, numeric_scale, data_type, udt_name,col_description(b.attrelid,b.attnum) as pg_description from information_schema.columns as a join pg_attribute as b on a.column_name=b.attname join pg_class as c on a.table_name=c.relname and b.attrelid = c.oid where b.attnum>0 and a.table_schema = 'public' and a.table_name='{0}'", table.Name); 132 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) 133 { 134 using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) 135 { 136 while (npgsqlDataReader.Read()) 137 { 138 bool allowDBNull = npgsqlDataReader.IsDBNull(1) || npgsqlDataReader.GetString(1) == "YES"; 139 byte precision = (byte)(npgsqlDataReader.IsDBNull(3) ? 0 : npgsqlDataReader.GetInt32(3)); 140 int size = npgsqlDataReader.IsDBNull(2) ? 0 : npgsqlDataReader.GetInt32(2); 141 int scale = npgsqlDataReader.IsDBNull(4) ? 0 : npgsqlDataReader.GetInt32(4); 142 string name = npgsqlDataReader.IsDBNull(0) ? string.Empty : npgsqlDataReader.GetString(0); 143 string text2 = npgsqlDataReader.IsDBNull(5) ? string.Empty : npgsqlDataReader.GetString(5); 144 string type = npgsqlDataReader.IsDBNull(6) ? string.Empty : npgsqlDataReader.GetString(6); 145 list.Add(new ColumnSchema(table, name, PostgreSQLSchemaProvider.GetDbType(type), text2, size, precision, scale, allowDBNull, new ExtendedProperty[] 146 { 147 new ExtendedProperty("NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(text2), DbType.String), 148 new ExtendedProperty("CS_Description",npgsqlDataReader.IsDBNull(7) ? string.Empty : npgsqlDataReader.GetString(7), DbType.String) 149 })); 150 } 151 if (!npgsqlDataReader.IsClosed) 152 { 153 npgsqlDataReader.Close(); 154 } 155 } 156 } 157 } 158 return list.ToArray(); 159 } 160 161 public TableKeySchema[] GetTableKeys(string connectionString, TableSchema table) 162 { 163 List<TableKeySchema> list = new List<TableKeySchema>(); 164 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 165 { 166 npgsqlConnection.Open(); 167 string text = string.Format("SELECT constraint_name as constrname FROM information_schema.table_constraints WHERE table_name = '{0}' AND constraint_type = 'FOREIGN KEY' AND constraint_schema='public'", table.Name); 168 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) 169 { 170 string text2 = string.Format("SELECT px.conname as constrname, att.attname as colname, fore.relname as reftabname, fatt.attname as refcolname, CASE px.confupdtype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END AS on_update, CASE px.confdeltype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END AS on_delete, CASE px.contype WHEN 'p' THEN true WHEN 'f' THEN false END as IsPrimaryKey from pg_constraint px left join pg_class home on (home.oid = px.conrelid) left join pg_class fore on (fore.oid = px.confrelid) left join pg_attribute att on (att.attrelid = px.conrelid AND att.attnum = ANY(px.conkey)) left join pg_attribute fatt on (fatt.attrelid = px.confrelid AND fatt.attnum = ANY(px.confkey)) where (home.relname = '{0}') and px.contype = 'f' order by constrname", table.Name); 171 using (NpgsqlCommand npgsqlCommand2 = new NpgsqlCommand(text2, npgsqlConnection)) 172 { 173 NpgsqlDataAdapter npgsqlDataAdapter = new NpgsqlDataAdapter(npgsqlCommand); 174 DataSet dataSet = new DataSet(); 175 npgsqlDataAdapter.Fill(dataSet, "constraint"); 176 npgsqlDataAdapter.SelectCommand = npgsqlCommand2; 177 npgsqlDataAdapter.Fill(dataSet, "keys"); 178 if (dataSet.Tables[0].Rows.Count > 0) 179 { 180 dataSet.Relations.Add("Contraint_to_Keys", dataSet.Tables[0].Columns["constrname"], dataSet.Tables[1].Columns["constrname"]); 181 foreach (DataRow dataRow in dataSet.Tables[0].Rows) 182 { 183 string name = dataRow["constrname"].ToString(); 184 DataRow[] childRows = dataRow.GetChildRows("Contraint_to_Keys"); 185 string[] array = new string[childRows.Length]; 186 string[] array2 = new string[childRows.Length]; 187 string name2 = table.Name; 188 string primaryKeyTable = childRows[0]["reftabname"].ToString(); 189 for (int i = 0; i < childRows.Length; i++) 190 { 191 array2[i] = childRows[i]["colname"].ToString(); 192 array[i] = childRows[i]["refcolname"].ToString(); 193 } 194 list.Add(new TableKeySchema(table.Database, name, array2, name2, array, primaryKeyTable)); 195 } 196 } 197 } 198 } 199 string text3 = string.Format("SELECT px.conname as constrname FROM pg_constraint px left join pg_class fore on fore.oid = px.confrelid where fore.relname = '{0}'", table.Name); 200 using (NpgsqlCommand npgsqlCommand3 = new NpgsqlCommand(text3, npgsqlConnection)) 201 { 202 string text4 = string.Format("SELECT px.conname as constrname, fatt.attname as colname, home.relname as reftabname, att.attname as refcolname, CASE px.confupdtype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END AS on_update, CASE px.confdeltype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END AS on_delete, CASE px.contype WHEN 'p' THEN true WHEN 'f' THEN false END as IsPrimaryKey from pg_constraint px left join pg_class home on (home.oid = px.conrelid) left join pg_class fore on (fore.oid = px.confrelid) left join pg_attribute att on (att.attrelid = px.conrelid AND att.attnum = ANY(px.conkey)) left join pg_attribute fatt on (fatt.attrelid = px.confrelid AND fatt.attnum = ANY(px.confkey)) where (fore.relname = '{0}') order by constrname", table.Name); 203 using (NpgsqlCommand npgsqlCommand4 = new NpgsqlCommand(text4, npgsqlConnection)) 204 { 205 NpgsqlDataAdapter npgsqlDataAdapter2 = new NpgsqlDataAdapter(); 206 DataSet dataSet2 = new DataSet(); 207 npgsqlDataAdapter2.SelectCommand = npgsqlCommand3; 208 npgsqlDataAdapter2.Fill(dataSet2, "constraint"); 209 npgsqlDataAdapter2.SelectCommand = npgsqlCommand4; 210 npgsqlDataAdapter2.Fill(dataSet2, "keys"); 211 if (dataSet2.Tables[0].Rows.Count > 0) 212 { 213 dataSet2.Relations.Add("Contraint_to_Keys", dataSet2.Tables[0].Columns["constrname"], dataSet2.Tables[1].Columns["constrname"]); 214 foreach (DataRow dataRow2 in dataSet2.Tables[0].Rows) 215 { 216 string name3 = dataRow2["constrname"].ToString(); 217 DataRow[] childRows2 = dataRow2.GetChildRows("Contraint_to_Keys"); 218 string[] array3 = new string[childRows2.Length]; 219 string[] array4 = new string[childRows2.Length]; 220 string foreignKeyTable = childRows2[0]["reftabname"].ToString(); 221 string name4 = table.Name; 222 for (int j = 0; j < childRows2.Length; j++) 223 { 224 array4[j] = childRows2[j]["refcolname"].ToString(); 225 array3[j] = childRows2[j]["colname"].ToString(); 226 } 227 list.Add(new TableKeySchema(table.Database, name3, array4, foreignKeyTable, array3, name4)); 228 } 229 } 230 } 231 } 232 } 233 return list.ToArray(); 234 } 235 236 public PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table) 237 { 238 PrimaryKeySchema result = null; 239 DataSet ds = new DataSet(); 240 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 241 { 242 npgsqlConnection.Open(); 243 string text = string.Format("select constraint_name from information_schema.table_constraints where constraint_schema='public' and table_name='{0}' and constraint_type='PRIMARY KEY'", table.Name); 244 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) 245 { 246 using (NpgsqlDataAdapter nda = new NpgsqlDataAdapter(npgsqlCommand)) 247 { 248 nda.Fill(ds, "table_constraints"); 249 } 250 } 251 252 string text2 = string.Format("select px.conname as ConstraintName, att.attname as ColumnName from pg_constraint px inner join pg_class home on (home.oid = px.conrelid) left join pg_attribute att on (att.attrelid = px.conrelid AND att.attnum = ANY(px.conkey)) where (home.relname = '{0}') and px.contype = 'p'", table.Name); 253 254 255 using (NpgsqlCommand npgsqlCommand2 = new NpgsqlCommand(text2, npgsqlConnection)) 256 { 257 using (NpgsqlDataAdapter nda = new NpgsqlDataAdapter(npgsqlCommand2)) 258 { 259 nda.Fill(ds, "pg_constraint"); 260 } 261 } 262 263 foreach (DataRow item in ds.Tables["table_constraints"].Rows) 264 { 265 List<string> list = new List<string>(); 266 foreach (DataRow item2 in ds.Tables["pg_constraint"].Rows) 267 { 268 list.Add(item2.Field<string>("ColumnName")); 269 } 270 result = new PrimaryKeySchema(table, item.Field<string>("constraint_name"), list.ToArray()); 271 } 272 273 } 274 return result; 275 } 276 277 public DataTable GetTableData(string connectionString, TableSchema table) 278 { 279 DataTable dataTable; 280 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 281 { 282 dataTable = new DataTable(table.Name); 283 string text = string.Format("SELECT * FROM {0}", table.Name); 284 using (NpgsqlDataAdapter npgsqlDataAdapter = new NpgsqlDataAdapter(text, npgsqlConnection)) 285 { 286 npgsqlDataAdapter.Fill(dataTable); 287 } 288 if (npgsqlConnection.State != ConnectionState.Closed) 289 { 290 npgsqlConnection.Close(); 291 } 292 } 293 return dataTable; 294 } 295 296 public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) 297 { 298 List<ExtendedProperty> list = new List<ExtendedProperty>(); 299 if (schemaObject is ColumnSchema) 300 { 301 ColumnSchema columnSchema = schemaObject as ColumnSchema; 302 string text = string.Format("select pg_get_serial_sequence(c.table_name, c.column_name) as EXTRA, COLUMN_DEFAULT, data_type \r\n from pg_tables t\r\n INNER JOIN information_schema.columns c on t.tablename = c.table_name\r\n WHERE schemaname = '{0}' \r\n AND table_name = '{1}'\r\n AND COLUMN_NAME = '{2}'\r\n order by ordinal_position", columnSchema.Table.Database.Name, columnSchema.Table.Name, columnSchema.Name); 303 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 304 { 305 npgsqlConnection.Open(); 306 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) 307 { 308 using (IDataReader dataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) 309 { 310 while (dataReader.Read()) 311 { 312 string text2 = dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0).ToLower(); 313 string value = dataReader.IsDBNull(1) ? null : dataReader.GetString(1).ToUpper(); 314 string value2 = dataReader.GetString(2).ToUpper(); 315 bool flag = !string.IsNullOrEmpty(text2); 316 list.Add(new ExtendedProperty("CS_IsIdentity", flag, columnSchema.DataType)); 317 if (flag) 318 { 319 list.Add(new ExtendedProperty("CS_IdentitySeed", 1, columnSchema.DataType)); 320 list.Add(new ExtendedProperty("CS_IdentityIncrement", 1, columnSchema.DataType)); 321 } 322 list.Add(new ExtendedProperty("CS_Default", value, DbType.String)); 323 list.Add(new ExtendedProperty("CS_SystemType", value2, DbType.String)); 324 list.Add(new ExtendedProperty("CS_Sequence", text2.ToUpper(), DbType.String)); 325 } 326 if (!dataReader.IsClosed) 327 { 328 dataReader.Close(); 329 } 330 } 331 } 332 if (npgsqlConnection.State != ConnectionState.Closed) 333 { 334 npgsqlConnection.Close(); 335 } 336 } 337 } 338 return list.ToArray(); 339 } 340 341 public void SetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) 342 { 343 throw new NotImplementedException(); 344 } 345 346 public ViewColumnSchema[] GetViewColumns(string connectionString, ViewSchema view) 347 { 348 List<ViewColumnSchema> list = new List<ViewColumnSchema>(); 349 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 350 { 351 npgsqlConnection.Open(); 352 string text = string.Format("select column_name, is_nullable, character_maximum_length, numeric_precision, numeric_scale, data_type, udt_name,col_description(b.attrelid,b.attnum) as pg_description from information_schema.columns as a join pg_attribute as b on a.column_name=b.attname join pg_class as c on a.table_name=c.relname and b.attrelid = c.oid where b.attnum>0 and a.table_schema = 'public' and a.table_name='{0}' ORDER BY ordinal_position", view.Name); 353 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) 354 { 355 using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) 356 { 357 while (npgsqlDataReader.Read()) 358 { 359 bool allowDBNull = npgsqlDataReader.IsDBNull(1) || npgsqlDataReader.GetString(1) == "YES"; 360 int size = npgsqlDataReader.IsDBNull(2) ? 0 : npgsqlDataReader.GetInt32(2); 361 byte precision = (byte)(npgsqlDataReader.IsDBNull(3) ? 0 : npgsqlDataReader.GetInt32(3)); 362 int scale = npgsqlDataReader.IsDBNull(4) ? 0 : npgsqlDataReader.GetInt32(4); 363 string text2 = npgsqlDataReader.IsDBNull(5) ? string.Empty : npgsqlDataReader.GetString(5); 364 string type = npgsqlDataReader.IsDBNull(6) ? string.Empty : npgsqlDataReader.GetString(6); 365 list.Add(new ViewColumnSchema(view, npgsqlDataReader.GetString(0), PostgreSQLSchemaProvider.GetDbType(type), text2, size, precision, scale, allowDBNull, new ExtendedProperty[] 366 { 367 new ExtendedProperty("NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(text2), DbType.String) 368 })); 369 } 370 } 371 } 372 } 373 return list.ToArray(); 374 } 375 376 public DataTable GetViewData(string connectionString, ViewSchema view) 377 { 378 DataTable dataTable; 379 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 380 { 381 dataTable = new DataTable(view.Name); 382 string text = string.Format("SELECT * FROM {0}", view.Name); 383 using (NpgsqlDataAdapter npgsqlDataAdapter = new NpgsqlDataAdapter(text, npgsqlConnection)) 384 { 385 npgsqlDataAdapter.Fill(dataTable); 386 } 387 } 388 return dataTable; 389 } 390 391 public ViewSchema[] GetViews(string connectionString, DatabaseSchema database) 392 { 393 List<ViewSchema> list = new List<ViewSchema>(); 394 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 395 { 396 npgsqlConnection.Open(); 397 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand("select viewname, viewowner from pg_catalog.pg_views where schemaname = 'public' order by viewname;", npgsqlConnection)) 398 { 399 DataTable dt = new DataTable(); 400 401 using (NpgsqlDataAdapter nda = new NpgsqlDataAdapter(npgsqlCommand)) 402 { 403 nda.Fill(dt); 404 nda.Dispose(); 405 } 406 foreach (DataRow item in dt.Rows) 407 { 408 list.Add(new ViewSchema(database, item.Field<string>("viewname"), item.Field<string>("viewowner"), DateTime.MinValue)); 409 } 410 411 } 412 } 413 return list.ToArray(); 414 } 415 416 public string GetViewText(string connectionString, ViewSchema view) 417 { 418 string result; 419 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 420 { 421 npgsqlConnection.Open(); 422 string text = string.Format("select view_definition from information_schema.views where table_schema='public' and table_name = '{0}'", view.Name); 423 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) 424 { 425 result = (string)npgsqlCommand.ExecuteScalar(); 426 } 427 } 428 return result; 429 } 430 431 public CommandSchema[] GetCommands(string connectionString, DatabaseSchema database) 432 { 433 List<CommandSchema> list = new List<CommandSchema>(); 434 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 435 { 436 npgsqlConnection.Open(); 437 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand("SELECT routine_name, rolname, specific_name, data_type from information_schema.routines LEFT JOIN pg_catalog.pg_proc p ON p.proname = routine_name INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace INNER JOIN pg_catalog.pg_authid a on a.oid = proowner WHERE routine_schema='public' ORDER BY routine_name ", npgsqlConnection)) 438 { 439 using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) 440 { 441 while (npgsqlDataReader.Read()) 442 { 443 bool flag = !npgsqlDataReader.IsDBNull(3) && npgsqlDataReader.GetString(3).Trim().Equals("VOID", StringComparison.InvariantCultureIgnoreCase); 444 if (!flag || database.IncludeFunctions) 445 { 446 List<ExtendedProperty> list2 = new List<ExtendedProperty> 447 { 448 new ExtendedProperty("CS_Name", npgsqlDataReader.GetString(2), DbType.String, PropertyStateEnum.ReadOnly), 449 new ExtendedProperty("CS_IsScalarFunction", flag, DbType.Boolean, PropertyStateEnum.ReadOnly), 450 new ExtendedProperty("CS_IsProcedure", flag, DbType.Boolean, PropertyStateEnum.ReadOnly), 451 new ExtendedProperty("CS_IsTrigger", npgsqlDataReader.GetString(3).Equals("TRIGGER", StringComparison.InvariantCultureIgnoreCase), DbType.Boolean, PropertyStateEnum.ReadOnly) 452 }; 453 list.Add(new CommandSchema(database, npgsqlDataReader.GetString(0), npgsqlDataReader.GetString(1), DateTime.MinValue, list2.ToArray())); 454 } 455 } 456 if (!npgsqlDataReader.IsClosed) 457 { 458 npgsqlDataReader.Close(); 459 } 460 } 461 } 462 if (npgsqlConnection.State != ConnectionState.Closed) 463 { 464 npgsqlConnection.Close(); 465 } 466 } 467 return list.ToArray(); 468 } 469 470 public ParameterSchema[] GetCommandParameters(string connectionString, CommandSchema commandSchema) 471 { 472 string arg = commandSchema.ExtendedProperties["CS_Name"].Value as string; 473 List<ParameterSchema> list = new List<ParameterSchema>(); 474 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 475 { 476 npgsqlConnection.Open(); 477 string text = string.Format("select * from information_schema.parameters where specific_schema='public' and specific_name = '{0}' order by ordinal_position", arg); 478 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) 479 { 480 using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) 481 { 482 while (npgsqlDataReader.Read()) 483 { 484 string name = npgsqlDataReader.IsDBNull(7) ? string.Empty : npgsqlDataReader.GetString(7); 485 int size = npgsqlDataReader.IsDBNull(9) ? 0 : npgsqlDataReader.GetInt32(9); 486 int scale = npgsqlDataReader.IsDBNull(19) ? 0 : npgsqlDataReader.GetInt32(19); 487 byte precision = npgsqlDataReader.IsDBNull(17) ? (byte)0 : npgsqlDataReader.GetByte(17); 488 string @string = npgsqlDataReader.GetString(8); 489 list.Add(new ParameterSchema(commandSchema, name, PostgreSQLSchemaProvider.GetParameterDirection(npgsqlDataReader.GetString(4)), PostgreSQLSchemaProvider.GetDbType(@string), @string, size, precision, scale, false, new ExtendedProperty[] 490 { 491 new ExtendedProperty("NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(@string), DbType.String) 492 })); 493 } 494 if (!npgsqlDataReader.IsClosed) 495 { 496 npgsqlDataReader.Close(); 497 } 498 } 499 } 500 if (npgsqlConnection.State != ConnectionState.Closed) 501 { 502 npgsqlConnection.Close(); 503 } 504 } 505 return list.ToArray(); 506 } 507 508 public CommandResultSchema[] GetCommandResultSchemas(string connectionString, CommandSchema command) 509 { 510 CommandResultSchema[] array = null; 511 string arg = command.ExtendedProperties["CS_Name"].Value as string; 512 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 513 { 514 npgsqlConnection.Open(); 515 string text = string.Format("select data_type from information_schema.routines where specific_schema='public' and specific_name = '{0}'", arg); 516 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) 517 { 518 using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) 519 { 520 while (npgsqlDataReader.Read()) 521 { 522 string @string = npgsqlDataReader.GetString(0); 523 if (@string == "void") 524 { 525 array = new CommandResultSchema[0]; 526 } 527 else if (@string == "USER-DEFINED") 528 { 529 NpgsqlCommand npgsqlCommand2 = new NpgsqlCommand(string.Format("SELECT t.typname, attname, a.typname from pg_type t JOIN pg_class on (reltype = t.oid) JOIN pg_attribute on (attrelid = pg_class.oid) JOIN pg_type a on (atttypid = a.oid) WHERE t.typname = (SELECT t.typname FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace INNER JOIN pg_type t ON p.prorettype = t.oid WHERE n.nspname = 'public' and proname = '{0}' ORDER BY proname);", command.Name), npgsqlConnection); 530 using (NpgsqlDataReader npgsqlDataReader2 = npgsqlCommand2.ExecuteReader(CommandBehavior.CloseConnection)) 531 { 532 string text2 = null; 533 List<CommandResultColumnSchema> list = new List<CommandResultColumnSchema>(); 534 while (npgsqlDataReader2.Read()) 535 { 536 if (string.IsNullOrEmpty(text2)) 537 { 538 text2 = npgsqlDataReader2.GetString(0); 539 } 540 string string2 = npgsqlDataReader2.GetString(2); 541 list.Add(new CommandResultColumnSchema(command, npgsqlDataReader2.GetString(1), PostgreSQLSchemaProvider.GetDbType(string2), string2, 0, 0, 0, true, new ExtendedProperty[] 542 { 543 new ExtendedProperty("NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(string2), DbType.String) 544 })); 545 } 546 array = new CommandResultSchema[] 547 { 548 new CommandResultSchema(command, text2, list.ToArray()) 549 }; 550 } 551 } 552 } 553 } 554 } 555 } 556 return array ?? new CommandResultSchema[0]; 557 } 558 559 public string GetCommandText(string connectionString, CommandSchema commandSchema) 560 { 561 object result = string.Empty; 562 string arg = commandSchema.ExtendedProperties["CS_Name"].Value as string; 563 using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) 564 { 565 npgsqlConnection.Open(); 566 string text = string.Format("select routine_definition from information_schema.routines where specific_schema='public' and specific_name = '{0}'", arg); 567 using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) 568 { 569 result = npgsqlCommand.ExecuteScalar(); 570 } 571 } 572 if (result != null) 573 return string.Empty; 574 return result.ToString(); 575 } 576 577 public string GetDatabaseName(string connectionString) 578 { 579 Regex regex = new Regex("Database\\W*=\\W*(?<database>[^;]*)", RegexOptions.IgnoreCase); 580 string result; 581 if (regex.IsMatch(connectionString)) 582 { 583 result = regex.Match(connectionString).Groups["database"].ToString(); 584 } 585 else 586 { 587 result = connectionString; 588 } 589 return result; 590 } 591 592 private static DbType GetDbType(string type) 593 { 594 DbType result; 595 switch (type) 596 { 597 case "bit": 598 case "bool": 599 case "boolean": 600 result = DbType.Boolean; 601 return result; 602 case "bytea": 603 result = DbType.Binary; 604 return result; 605 case "bpchar": 606 case "char": 607 case "character": 608 case "text": 609 case "varchar": 610 case "character varying": 611 result = DbType.String; 612 return result; 613 case "date": 614 result = DbType.Date; 615 return result; 616 case "float4": 617 case "single precision": 618 case "real": 619 result = DbType.Single; 620 return result; 621 case "float8": 622 case "double precision": 623 result = DbType.Double; 624 return result; 625 case "int2": 626 case "smallint": 627 result = DbType.Int16; 628 return result; 629 case "int4": 630 case "integer": 631 result = DbType.Int32; 632 return result; 633 case "int8": 634 case "bigint": 635 result = DbType.Int64; 636 return result; 637 case "money": 638 case "numeric": 639 result = DbType.Decimal; 640 return result; 641 case "time": 642 case "timetz": 643 case "time without time zone": 644 case "time without timezone": 645 case "time with time zone": 646 case "time with timezone": 647 result = DbType.Time; 648 return result; 649 case "interval": 650 case "timestamp": 651 case "timestamptz": 652 case "timestamp without time zone": 653 case "timestamp without timezone": 654 case "timestamp with time zone": 655 case "timestamp with timezone": 656 result = DbType.DateTime; 657 return result; 658 case "uuid": 659 result = DbType.Guid; 660 return result; 661 case "box": 662 case "circle": 663 case "inet": 664 case "line": 665 case "lseg": 666 case "path": 667 case "point": 668 case "polygon": 669 case "refcursor": 670 result = DbType.Object; 671 return result; 672 } 673 result = DbType.Object; 674 return result; 675 } 676 677 private static NpgsqlDbType GetNativeDbType(string type) 678 { 679 string key; 680 switch (key = type.ToLower()) 681 { 682 case "array": 683 return (NpgsqlDbType)(-2147483648); 684 case "bit": 685 return (NpgsqlDbType)25; 686 case "box": 687 return (NpgsqlDbType)3; 688 case "bool": 689 case "boolean": 690 return (NpgsqlDbType)2; 691 case "bytea": 692 return (NpgsqlDbType)4; 693 case "char": 694 return (NpgsqlDbType)6; 695 case "bpchar": 696 case "character": 697 case "varchar": 698 case "character varying": 699 return (NpgsqlDbType)22; 700 case "date": 701 return (NpgsqlDbType)7; 702 case "float4": 703 case "single precision": 704 case "real": 705 return (NpgsqlDbType)17; 706 case "float8": 707 case "double precision": 708 case "double": 709 return (NpgsqlDbType)8; 710 case "int2": 711 case "smallint": 712 return (NpgsqlDbType)18; 713 case "int4": 714 case "integer": 715 return (NpgsqlDbType)9; 716 case "int8": 717 case "bigint": 718 return (NpgsqlDbType)1; 719 case "money": 720 return (NpgsqlDbType)12; 721 case "name": 722 return (NpgsqlDbType)32; 723 case "numeric": 724 return (NpgsqlDbType)13; 725 case "text": 726 case "user-defined": 727 return (NpgsqlDbType)19; 728 case "oidvector": 729 return (NpgsqlDbType)29; 730 case "abstime": 731 return (NpgsqlDbType)33; 732 case "time": 733 case "time without time zone": 734 case "time without timezone": 735 return (NpgsqlDbType)20; 736 case "timetz": 737 case "time with time zone": 738 case "time with timezone": 739 return (NpgsqlDbType)31; 740 case "interval": 741 return (NpgsqlDbType)30; 742 case "timestamp": 743 case "timestamptz": 744 case "timestamp without time zone": 745 case "timestamp without timezone": 746 return (NpgsqlDbType)21; 747 case "timestamp with time zone": 748 case "timestamp with timezone": 749 return (NpgsqlDbType)26; 750 case "uuid": 751 return (NpgsqlDbType)27; 752 case "circle": 753 return (NpgsqlDbType)5; 754 case "inet": 755 return (NpgsqlDbType)24; 756 case "line": 757 return (NpgsqlDbType)10; 758 case "lseg": 759 return (NpgsqlDbType)11; 760 case "path": 761 return (NpgsqlDbType)14; 762 case "point": 763 return (NpgsqlDbType)15; 764 case "polygon": 765 return (NpgsqlDbType)16; 766 case "refcursor": 767 return (NpgsqlDbType)23; 768 case "xml": 769 return (NpgsqlDbType)28; 770 } 771 throw new ArgumentOutOfRangeException(); 772 } 773 774 private static ParameterDirection GetParameterDirection(string direction) 775 { 776 ParameterDirection result; 777 if (direction != null) 778 { 779 if (direction == "IN") 780 { 781 result = ParameterDirection.Input; 782 return result; 783 } 784 if (direction == "OUT") 785 { 786 result = ParameterDirection.Output; 787 return result; 788 } 789 } 790 result = ParameterDirection.InputOutput; 791 return result; 792 } 793 } 794 }
