C # how to access the PostGreSQL database

I am only a bit familiar with PostGreSQL. I want to record this process in the future. If people in Microsoft's technical direction encounter similar requirements, they can have a direct reference.
In the unfamiliar field of knowledge, there is always a search engine that can help me.

A preliminary understanding of PostGreSQL databases and data forms

First, I want to see what the PostGreSQL database looks like and the data format I want to obtain. I am not familiar with the two keywords Linux and PostGreSQL, I found a Windows client that can connect to the PostGreSQL database, called pgAdmin. I installed Version III, which should be relatively new. After downloading and installing it, I can see the interface:

Click the logo of the power plug to create a new server connection.

The name is the display name on the PGAdmin client, which can be customized.
What about SSL? There are so many options that I don't know exactly what to use. I chose to allow it.

Port number. The default PostGreSQL port number is 5432. Of course, it can be modified and customized to another port number.
After entering all the information, click OK to view the database.
I am not at the customer site and cannot connect to the PostGreSQL database, so the following will not work.

In PGAdmin, you can write query statements. If you have the permission, you can see the returned result set.
I opened a window and tried to write a query statement as follows:
Select top 10 * from TableObj
Run the command and find that the result set window prompts a syntax error. Ask the brothers in the group and find that the correct syntax should be as follows:
Select * from TableObj limit 5
Syntax rules are different from SQL statements.
Run the command. The system prompts that the object "TableObj" cannot be found and the container "ContainerObj" at the previous layer of TableObj is found. Modify the following:
SELECT * FROM ContainerObj. TableObj limit 5
Click execute to view the result set.
Compared with sqlserver, the header of the PGAdmin result set display interface has two rows, one row is the field name and the other row is the field data type. This is very loving.
The query statements in PostGreSQL are case-insensitive.
For PostgreSql syntax rules, I found the PostgreSql syntax detailed manual on CSDN, but this guy has three resource points.
Now that you see the PostGreSQL database, I will try to test the C # code to retrieve the data of PostGreSQL.
C # connecting to the PostGreSQL database

There is a plug-in called Npgsql, which is a PostgreSQL. NET data provider. First, download it. In fact, we only need to reference two dll files:

After my tests, if Npgsql. dll is referenced only, an error will be reported after your program runs, prompting you That the dependencies referenced by Mono. Security. dll cannot be found.
After referencing these two dll files, you also need to add using to your code.
Using Npgsql;
The code is simple.
I wrote a spelling string. Txt is a textbox of Winform.

Copy codeThe Code is as follows :///
/// Spell the database connection string based on the entered information
///
/// Database connection string
Private string getConnectStr ()
{
StringBuilder sb = new StringBuilder ();
String str = string. Empty;
Sb. Append ("Server = ");
Sb. Append (txtServer. Text );
Sb. Append ("; Port = ");
Sb. Append (txtPort. Text );
Sb. Append ("; User Id = ");
Sb. Append (txtUID. Text );
Sb. Append ("; Password = ");
Sb. Append (txtPassword. Text );
Sb. Append ("; Database = ");
Sb. Append (txtDB. Text );
Sb. Append (";");
Str = sb. ToString ();
Return str;
}

In fact, database connection strings are in such a format.Copy codeThe Code is as follows: "Server = 192.168.1.100; Port = 5432; UserId = mike; Password = secret; Database = mikedb ;"

///
/// Test the connection to the PostGreSQL database
///
/// Success/Failure
Private string TestConnection ()
{
String str = getConnectStr ();
String strMessage = string. Empty;
Try

{
NpgsqlConnection conn = new NpgsqlConnection (str );
Conn. Open ();
StrMessage = "Success ";
Conn. Close ();
}
Catch
{
StrMessage = "Failure ";
}
Return strMessage;
}

///
/// Test to obtain data (use try or catch)
///
Private void TestGetData ()
{
IDbConnection dbcon;
Dbcon = newNpgsqlConnection (getConnectStr ());
Dbcon. Open ();
IDbCommand dbcmd = dbcon. CreateCommand ();
Dbcmd. CommandText = "SELECT * FROM ContainerObj. TableObj limit 5 ";
IDataReader dr = dbcmd. ExecuteReader ();
String strResult = string. Empty;
While (dr. Read ())
{
StringstrRecord_Time_Int = dr [0]. ToString ();
StringstrIP = dr [1]. ToString ();
StringstrFullPath = dr [2]. ToString ();
StringstrUpload_Size = dr [3]. ToString ();
StringstrDownLoad_Size = dr [4]. ToString ();
StrResult + = "record_time_int:" + strRecord_Time_Int + "| ip:" + strIP + "| fullpath:" + strFullPath + "| upload_size:" + strUpload_Size + "| download_size: "+ strDownLoad_Size +" \ n ";
}
Dr. Close ();
Dr = null;
LblRes. Text = strResult;
}

Form is very simple. It is only a test function. The following is what was previously intercepted at the site. After processing it, some information was removed.