public CommandResultSchema[] GetCommandResultSchemas(string connectionString, CommandSchema command) { CommandResultSchema[] array = null; string arg = command.ExtendedProperties["CS_Name"].Value as string; using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) { npgsqlConnection.Open(); string text = string.Format("select data_type from information_schema.routines where specific_schema='public' and specific_name = '{0}'", arg); using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) { using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) { while (npgsqlDataReader.Read()) { string @string = npgsqlDataReader.GetString(0); if (@string == "void") { array = new CommandResultSchema[0]; } else if (@string == "USER-DEFINED") { 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); using (NpgsqlDataReader npgsqlDataReader2 = npgsqlCommand2.ExecuteReader(CommandBehavior.CloseConnection)) { string text2 = null; List <CommandResultColumnSchema> list = new List <CommandResultColumnSchema>(); while (npgsqlDataReader2.Read()) { if (string.IsNullOrEmpty(text2)) { text2 = npgsqlDataReader2.GetString(0); } string string2 = npgsqlDataReader2.GetString(2); list.Add(new CommandResultColumnSchema(command, npgsqlDataReader2.GetString(1), PostgreSQLSchemaProvider.GetDbType(string2), string2, 0, 0, 0, true, new ExtendedProperty[] { new ExtendedProperty("NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(string2), DbType.String) })); } array = new CommandResultSchema[] { new CommandResultSchema(command, text2, list.ToArray()) }; } } } if (!npgsqlDataReader.IsClosed) { npgsqlDataReader.Close(); } } } if (npgsqlConnection.State != ConnectionState.Closed) { npgsqlConnection.Close(); } } return(array ?? new CommandResultSchema[0]); }
public ViewColumnSchema[] GetViewColumns(string connectionString, ViewSchema view) { List <ViewColumnSchema> list = new List <ViewColumnSchema>(); using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) { npgsqlConnection.Open(); string text = string.Format("SELECT column_name, is_nullable, character_maximum_length, numeric_precision, numeric_scale, data_type, udt_name FROM information_schema.columns WHERE table_schema='public' AND table_name='{0}' ORDER BY ordinal_position", view.Name); using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) { using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) { while (npgsqlDataReader.Read()) { bool allowDBNull = npgsqlDataReader.IsDBNull(1) || npgsqlDataReader.GetString(1) == "YES"; int size = npgsqlDataReader.IsDBNull(2) ? 0 : npgsqlDataReader.GetInt32(2); byte precision = (byte)(npgsqlDataReader.IsDBNull(3) ? 0 : npgsqlDataReader.GetInt32(3)); int scale = npgsqlDataReader.IsDBNull(4) ? 0 : npgsqlDataReader.GetInt32(4); string text2 = npgsqlDataReader.IsDBNull(5) ? string.Empty : npgsqlDataReader.GetString(5); string type = npgsqlDataReader.IsDBNull(6) ? string.Empty : npgsqlDataReader.GetString(6); list.Add(new ViewColumnSchema(view, npgsqlDataReader.GetString(0), PostgreSQLSchemaProvider.GetDbType(type), text2, size, precision, scale, allowDBNull, new ExtendedProperty[] { new ExtendedProperty("NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(text2), DbType.String) })); } if (!npgsqlDataReader.IsClosed) { npgsqlDataReader.Close(); } } } if (npgsqlConnection.State != ConnectionState.Closed) { npgsqlConnection.Close(); } } return(list.ToArray()); }
public ParameterSchema[] GetCommandParameters(string connectionString, CommandSchema commandSchema) { string arg = commandSchema.ExtendedProperties["CS_Name"].Value as string; List <ParameterSchema> list = new List <ParameterSchema>(); using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) { npgsqlConnection.Open(); string text = string.Format("select * from information_schema.parameters where specific_schema='public' and specific_name = '{0}' order by ordinal_position", arg); using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) { using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) { while (npgsqlDataReader.Read()) { string name = npgsqlDataReader.IsDBNull(7) ? string.Empty : npgsqlDataReader.GetString(7); int size = npgsqlDataReader.IsDBNull(9) ? 0 : npgsqlDataReader.GetInt32(9); int scale = npgsqlDataReader.IsDBNull(19) ? 0 : npgsqlDataReader.GetInt32(19); byte precision = npgsqlDataReader.IsDBNull(17) ? (byte)0 : npgsqlDataReader.GetByte(17); string @string = npgsqlDataReader.GetString(8); list.Add(new ParameterSchema(commandSchema, name, PostgreSQLSchemaProvider.GetParameterDirection(npgsqlDataReader.GetString(4)), PostgreSQLSchemaProvider.GetDbType(npgsqlDataReader.GetString(8)), @string, size, precision, scale, false, new ExtendedProperty[] { new ExtendedProperty("NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(@string), DbType.String) })); } if (!npgsqlDataReader.IsClosed) { npgsqlDataReader.Close(); } } } if (npgsqlConnection.State != ConnectionState.Closed) { npgsqlConnection.Close(); } } return(list.ToArray()); }
public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table) { List <ColumnSchema> list = new List <ColumnSchema>(); using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) { npgsqlConnection.Open(); string text = string.Format("select column_name, is_nullable, character_maximum_length, numeric_precision, numeric_scale, data_type, udt_name from information_schema.columns where table_schema = 'public' and table_name='{0}'", table.Name); using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) { DataTable dt = new DataTable(); using (NpgsqlDataAdapter nda = new NpgsqlDataAdapter(npgsqlCommand)) { nda.Fill(dt); nda.Dispose(); } foreach (DataRow item in dt.Rows) { bool allowDBNull = item["is_nullable"] == null ? false : item["is_nullable"].ToString().Equals("YES"); int? numeric_precision = item.Field <int?>("numeric_precision"); byte precision = (byte)(numeric_precision ?? 0); int? size = item.Field <int?>("character_maximum_length"); int? scale = item.Field <int?>("numeric_scale"); string name = item["column_name"] == null ? string.Empty : item["column_name"].ToString(); string text2 = item["data_type"] == null ? string.Empty : item["data_type"].ToString(); string type = item["udt_name"] == null ? string.Empty : item["udt_name"].ToString(); list.Add(new ColumnSchema(table, name, PostgreSQLSchemaProvider.GetDbType(type), text2, size ?? 0, precision, scale ?? 0, allowDBNull, new ExtendedProperty[] { new ExtendedProperty("NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(text2), DbType.String) })); } //using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) //{ // while (npgsqlDataReader.Read()) // { // bool allowDBNull = npgsqlDataReader.IsDBNull(1) || npgsqlDataReader.GetString(1) == "YES"; // byte precision = (byte)(npgsqlDataReader.IsDBNull(3) ? 0 : npgsqlDataReader.GetInt32(3)); // int size = npgsqlDataReader.IsDBNull(2) ? 0 : npgsqlDataReader.GetInt32(2); // int scale = npgsqlDataReader.IsDBNull(4) ? 0 : npgsqlDataReader.GetInt32(4); // string name = npgsqlDataReader.IsDBNull(0) ? string.Empty : npgsqlDataReader.GetString(0); // string text2 = npgsqlDataReader.IsDBNull(5) ? string.Empty : npgsqlDataReader.GetString(5); // string type = npgsqlDataReader.IsDBNull(6) ? string.Empty : npgsqlDataReader.GetString(6); // list.Add(new ColumnSchema(table, name, PostgreSQLSchemaProvider.GetDbType(type), text2, size, precision, scale, allowDBNull, new ExtendedProperty[] // { // new ExtendedProperty("NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(text2), DbType.String) // })); // } // if (!npgsqlDataReader.IsClosed) // { // npgsqlDataReader.Close(); // } //} } if (npgsqlConnection.State != ConnectionState.Closed) { npgsqlConnection.Close(); } } return(list.ToArray()); }