cancel
Showing results for 
Search instead for 
Did you mean: 

Program in C# in Visual Studio application using EDB .NET connectors

In this article, I wanted to cover how to use C# in conjunction with PostgreSQL throught the EDB .NET connector. We will create a simple program that you write in C# Visual Studio and run that program to connect to EDB Postgres database using EDB .NET connectors.

 

Personally, I have faced a lot of difficulties when it comes to application coding and connectors (That's the position of a DBA I believe) to understand where the code is failing to connect to the database. Whether the fault is in application code or connectors are not behaving properly or the database system it is trying to connect to is the culprit.

 

So to start with, I started digging more at the connectors and the application coding part and after all the work, I managed to somewhat interpret the code that is written in C# and wrote a simple program that connects to the database to retrieve some result and do some checking. Hope this helps.

 

Pre-requisites

  1. A PostgreSQL database, EnterpriseDB PostgreSQL Advanced Server should be installed on the server; we recommend installing the most recent version (https://www.enterprisedb.com/software-downloads-postgres
    Image1.png








  2. The EnterpriseDB .NET connectors, which can be installed from the EnterpriseDB Stackbuilder utility that comes with EnterpriseDB PostgreSQL Advanced Server. You'll want to ensure that it's included in your installation, and after it is installed, go to the EDB Postgres folder in the Start Menu and run Stackbuilder in order to add the .NET connector. Please remember where you choose to have the .NET connector installed; you will need that information later.
    Image2.png
    Image3.png
    Image4.png
  3. Microsoft's Visual Studio (https://www.visualstudio.com/downloads/)

 

Sample Login Check Program with C# and Visual Studio

The program that I will explain in the example is a simple credential check program in which we will create a login page where you will enter your credentials and the program will check the mentioned credentials in the database. If any of them matches the credential specified, it will simply take you to another page. Otherwise, it will display a box that password or username is incorrect. So let's start.

  1. Open the Microsoft Visual Studio and click on the File tab to open a new project. Click on the Newoption and select Project. From the list, select Windows Forms App (.NET Framework). If you don’t see it in the list, it may be because you do not have the .NET connector added as part of the Visual Studios installation. Scroll to the bottom of the page and click on the link for Install more tools and features to add the .NET Framework; we have the screenshots on how to do this here:
    Image5.png
    Image6.png
    Image7.png

  2. Now let’s do some designing. We need to add two labels, two text boxes and two buttons on the form. Select the toolbox section from which you can select all the controllers that you want on the form.
    Image9.png
    Image12.png

  3. After doing designing, we need to add another blank form as we need that if the login is successful then we will just display a form of some sort. In this example, we will be calling it the SuccessForm. For this, you need to right click on your project in the Solution Explorer and click on ADD and select ADD ITEM - WINDOWS FORM.

  4. Let’s start some coding now. But before that, you need to add a reference to the .NET drivers that we will be using to connect to the database. For doing that, you need to right click on your project in the Solution Explorer and click on ADD and click on the reference. Go to the below location and add both the DLL’s as those are the connectors that will help the connection to go through C:\Program Files\edb\dotnet\net40.
    Image16.png
    Image17.png
    Image18.png

  5. First of all, you need to add the EnterpriseDB’s .NET connector’s reference in the code so that we can use various functionality in the following code that we will write. Double click on Form1 and it will take you to the coding window. Go to the top where all the Generic libraries are added. Add the reference to the .NET connectors also adding the below line :
    using EnterpriseDB.EDBClient;
    Image19.png

  6. Let’s start the coding now. First of all the easy one. Double click on the button named “Cancel”, this will take you to the coding window for that button. We just need to exit the application if anyone clicks this button. Just add the below line in the coding window that opens :
    Application.Exit();
    This will allow the application to exit the code whenever any user presses the Cancel button.






  7. You’ll need to make sure that the PostgreSQL database has a table to connect to, for our example. Here, I show the process of signing into my database and creating a table that our C# form will query.

    Server [localhost]:
    Database [edb]: postgres
    Port [5444]:
    Username [enterprisedb]: postgres
    Password for user postgres:
    edb-psql (11.5.12)
    
    postgres=#
    
    postgres=# create table login (ID numeric, name varchar(30), password varchar(30));
    CREATE TABLE
    postgres=# insert into login (ID, name, password) values (1, 'good_user', 'correct_pw');
    INSERT 0 1
    postgres=# select * from login;
     id |   name    |  password
    ----+-----------+------------
      1 | good_user | correct_pw
    (1 row)
    

     

  8. Now the main button which has the logic of the login i.e. “Login” button. Whenever the user presses this button, what the application needs to do? It goes to the database, check the values that are passed in textbox1 and textbox2, if any entry matches from the login table in the PostgreSQL database, it displays another form (our SuccessForm); otherwise, we will see an error saying that the credentials are incorrect. The code for that button is as follows :

         private void Button1_Click(object sender, EventArgs e)
         {
             bool blnfound = false;
             EDBConnection conn = new EDBConnection("Server=localhost;Port=5444;Username=postgres;Password=abc123;Database=postgres");
             conn.Open(); //Open the connection
             MessageBox.Show("connection open!");
             EDBCommand cmd = new EDBCommand("select * from login where name = '" + textBox1.Text + "' and password = '" + textBox2.Text + "'", conn);
             EDBDataReader dr = cmd.ExecuteReader();
    
             if (dr.Read())
             {
                 blnfound = true;
                 SuccessForm f5 = new SuccessForm();
                 f5.Show();
                 this.Hide();
             }
    
             if (blnfound == false)
                 try
                 {
                     MessageBox.Show("Username or Password is incorrect", "Ankit Shukla's Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                     dr.Close();
                 }
    
                 catch (EDBException exp)
                 {
                     throw exp;
                 }
    
                 finally
                 {
                     conn.Close();
                 }
         }
    
    

     

Explanation of the above code
I have a declared a boolean variable blnfound for which the values is FALSE by default.
We now open a connection with the database using the “EDBConnection” feature of our driver. Remember, you will not be able to use these features if you have not added the reference to the .NET connectors as shown earlier.

You need to give the following in the connection string so that the program knows where you want to connect :

  • Server on which the database is running
  • Port on which the database is running
  • Username using which you want to make connection
  • Password of that User
  • Database name to which you want to connect

I have also included a message box which it pops up if the connection is successful.

Now, I want to run a command(query) in the database to retrieve the data which is passed by the user i.e. Username and Password in the form. I have already created a table in the database called login which 3 columns ID(numeric), Name(varchar) and Password(varchar).

We use the EDBCommand feature of the .NET driver to send a query to the database :

EDBCommand cmd = new EDBCommand("select * from login where name = '" + textBox1.Text + "' and password = '" + textBox2.Text + "'", conn);

If you see the above command, created a new “cmd” command using EDBCommand feature in which I am selecting the contents of login table from the database which matches values of textbox1 and textbox2 entered by the user.

Now I want to capture that result of the command somewhere, so I have used EDBDataReader feature to store the result in dr which is a variable after executing the command which is in the previous line. I have used ExecuteReader to execute the command.

I will check now if the executed command has returned any value in dr or not. If the credentials passed are matching some entry in the table then I will make the boolean variable blnfound to true and I will show the SuccessForm by hiding the current form. Below is code that does that logic :

if (dr.Read())
               {
                   blnfound = true;
                   SuccessFrom f5 = new SuccessForm();
                   f5.Show();
                   this.Hide();
   }

If I have not found any matches in the table, then it should say that “Username or Password is incorrect” in the popped up message box. Below is the code for that :

if (blnfound == false)
try
               {
                   MessageBox.Show("Username or Password is incorrect", "Ankit Shukla's Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                   dr.Close();
               }

Finally, I am catching any exceptions if I have during the runtime of the program and then I am closing the connection which I have opened the database.

 

 

Results

To run the code, use the Start button on the top of the screen:

Image22.png

Bad Username/Password:

Image23.png

Image24.png

 

Good Username/Password:

Image25.png
Image26.png
 
 
 
Version history
Revision #:
12 of 12
Last update:
a week ago
Updated by:
 
Labels (1)