Making life easier 2 (Postgresql + asp.net)

Just over a year ago, there was the task of writing a web application working with Postgresql database. To my regret, there was no standard adapter for working with the PostgreSQL database, and with the fact that it was found on the Internet (free) it was impossible to work.
As a result, it was decided to do with the Npgsql library.
How to work with this library, there is documentation and many examples on the Internet. Immediately after starting work with her, I did not like the fact that I needed to write a lot of the same type of code. To facilitate the work, an additional layer was created in the form of a separate class for working with the database through this library.

Features:
1. connection data is taken from the web.config file
2. there is an opportunity to work with multiple connections. (for example, if a project has a modular system, and each module only works with its own scheme)


sample part from web.config

<appSettings> <!--    --> <add key="ProviderName" value="PostgreSQL" /> <add key="DataBase" value="DataBaseName" /> <add key="Port" value="80" /> <add key="Host" value="DataBaseHost" /> <!--        --> <add key="UserName0" value="UserNameCore" /> <add key="Password0" value="PasswordCore" /> <add key="Schema0" value="SchemaCore" /> <!--      1   --> <add key="UserName1" value="UserNameModule1" /> <add key="Password1" value="PasswordModule1" /> <add key="Schema1" value="SchemaModule1" /> </appSettings> 

')
in a separate class, you can also declare constants, for further convenience

  public static int mkCore = 0; public static int mkModule1 = 1; 

public methods of the WRKDataBase class

  public WRKDataBase(int module) //   public bool Connected() // ,         public NpgsqlConnection CreateConnection() //     public DbCommand CreateCommand(string SQLString) // "" private bool RepairCommand(DbCommand Command, string SQLString) //  "" ,       public void AddCommandParam(ref DbCommand Command, string Param_name, object Param_value, DbType Type) //   "" public void AddCommandParamNull(ref DbCommand Command, string Param_name) //     NULL public bool CommandExecute(string SQLString="") //  sql  public bool CommandExecute(DbCommand Command) //  "" public DbDataReader CreateReader(string SQLString) // ""  sql  public DbDataReader CreateReader(DbCommand Command) //  ""  "" public DbDataAdapter CreateDataAdapter(string SQLString) //  "",  SQL  public bool UpdateAdapter(ref DbDataAdapter dbDataAdapter, DataSet dataSet) //   ""  "" public void AddCommandToAdapter( DbDataAdapter datadapter, DbCommand Command, string action ) // "" "" public bool CreateDataSet(DataSet Dataset, string SQLString) //     sql  public bool FillDataSet(ref DataSet dataSet, string SQLString, string datasetTableName) //  ""  sql  public void Dispose() //   

in SQL queries, the names of tables, views, functions, etc. need to be wrapped in "~", for example

  Select id, name from ~years~ 

then it will be enough to connect to the system core

  WRKDataBase wrkDataBase = new WRKDataBase("".mkCore); 

for work, however, we are not already using a DataSource, but an ObjectDataSource. ( Description of ObjectDataSource on msdn )

Example:
object description class

  public class sYear { public string god { get; set; } public string naimen { get; set; } public sYear(IDataRecord rec) { if (rec["god"] != null) { god = rec["god"].ToString(); }; if (rec["naimen"] != null) { naimen = rec["naimen"].ToString(); }; } public sYear(string pgod, string pnaimen) { god = pgod; naimen = pnaimen; } } 

class of work with the database

  public class sYearDB { public List<sYear> SelectListYear() { WRKDataBase wrkDataBase; wrkDataBase = new WRKDataBase("".mkModule1); List<sYear> ret = new List<sYear>(); ret.Add(new sYear("0", "")); var rdr = wrkDataBase.CreateReader("Select god, god as naimen from ~vyear~ order by god"); while (rdr.Read()) ret.Add(new sYear(rdr)); rdr.Close(); wrkDataBase.Dispose(); return ret; } } 

Datasource:

  <asp:ObjectDataSource ID="dsYear" runat="server" SelectMethod="SelectListYear" TypeName="DBNameSpace.sYearDB" DataObjectTypeName="DBNameSpace.sYear"> </asp:ObjectDataSource> 

UPD: Promised class reference