cancel
Showing results for 
Search instead for 
Did you mean: 

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

 

Below is the explanation of 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 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. PPAS database should be installed on the server (https://www.enterprisedb.com/software-downloads-postgres)

2. Connectors that comes as a module with PPAS database server, should also be installed (https://www.enterprisedb.com/software-downloads-postgres)

3. Microsoft's Visual Studio 2015 (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 NEW option and select PROJECT. See the Screen1 snap from the attached screenshots document.

 

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. Attached (Screen2 snap) from screenshots document shows the form I have made.

 

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 blank form. For this, you need to right click on your project in the Solution Explorer and click on ADD and select ADD ITEM. You will see from the attached (Screen3 snap) screenshots document from which you need to select 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\PostgresPlus\connectors\dotnet\4.5”. Attached is Screenshots document(Screen4 snap) that I have captured to show this.

 

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;

 

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 this button.

 

7. 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 testbox1 and textbox2, if any entry matches from the LOGIN table, it displays another form otherwise an error saying 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=enterprisedb;Password=edb;Database=edb");

               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;

                   Form2 f5 = new Form2();

                   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.

==> Then the most important part, to 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).

 

I have used 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 form2 by hiding the current form. Below is code that does that logic :

 

if (dr.Read())

               {

blnfound = true;

                   Form2 f5 = new Form2();

                   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.

 

 

Attached document “Final Run” shows the complete execution of the program.

 
Version history
Revision #:
1 of 1
Last update:
2 weeks ago
Updated by:
 
Labels (1)
Contributors