Recently began to do the work of C # dotnet, because of the interest in PostgreSQL database, so I studied how to access the database of PostgreSQL problem. 1. In order to access the PostgreSQL database, you need to download the npgsql. Net Data Provider for PostgreSQL component from the Pgfoundry website. Access url:http://pgfoundry.org/frs/?group_id=1000140 Note: Because DotNet3.5 is used, Npgsql2.0.6-bin-ms.net3.5sp1.zip is downloaded. 2, unzip the zip file, the extracted directory Npgsql.dll, Mono.Security.dll copied to the C # project obj directory, and then in VS2008, the Npgsql.dll file added to the references. 3, in the need to use Npgsql C # file header, add the following using statement. View Plaincopy to Clipboardprint?
Using Npgsql;
Using Npgsql;4. Create a PostgreSQL database connection view plaincopy to Clipboardprint?
/* To instantiate a Npsqlconnection object with the specified connectionstring */ 
String connectionString = "server=127.0.0.1; port=5432; User id=test; Password=test;database=testdb; "  
Npgsqlconnection conn = new Npgsqlconnection (string connectionString) ;  
//Open a database connection and call Conn. Open ();  
//close a database connection and call Conn. Close ();  
/* To instantiate a ConnectionString object with the specified Npsqlconnection */
String connectionString = "server=127.0.0.1; port=5432; User id=test; Password=test;database=testdb; "
Npgsqlconnection conn = new Npgsqlconnection (string connectionString);
//Open a database connection and call
Conn before executing the relevant SQL. Open ();
//closes a database connection and calls
Conn after the relevant SQL has been executed. Close (); 5. Use Npgsqlcommand.executescalar () method to obtain unique values for the results of the search view Plaincopy to Clipboardprint?
Try
{ 
String sql = "SELECT COUNT (*) from Test";  
Conn. Open ();  
Npgsqlcommand objcommand = new Npgsqlcommand (SQL, conn);  
int count = Convert.ToInt32 (Objcommand.executescalar ());  
} 
Finally 
{ 
Conn. Close ();  
} 
Try
{
String sql = "SELECT COUNT (*) from Test";
Conn. Open ();
Npgsqlcommand objcommand = new Npgsqlcommand (SQL, conn);
int count = Convert.ToInt32 (Objcommand.executescalar ());
}
Finally
{
Conn. Close ();
} 6. Use the Npgsqlcommand.executereader () method to obtain the results of a result set (conn open and close call omitted) view Plaincopy to Clipboardprint?
String sql = "SELECT * from Test";
Npgsqlcommand objcommand = new Npgsqlcommand (sql,conn);
Npgsqldatareader dr = command. ExecuteReader ();
while (Dr. Read ())
{
for (i = 0; i < Dr. FieldCount; i++)&NBSP;
{&NBSP;
Console.Write ("{0 } \ T ", dr[i]); Get the field name &NBSP;
}&NBSP;
int testId = dr["id"]; Gets the value of the specified field. (ID is a field of the test table) &NBSP;
... &NBSP;
Console.WriteLine (); }&NBSP;
Dr. Close (); &NBSP;
String sql = "SELECT * from Test";
Npgsqlcommand objcommand = new Npgsqlcommand (sql,conn);
Npgsqldatareader dr = command. ExecuteReader (); The
while (Dr. Read ())
{
for (i = 0; i < Dr. FieldCount; i++)
{
Console.Write ("{0} \ t", dr[i]);//Get field name
}
int testid = dr["id"];//Get the value of the specified field. (ID is a field of the test table)
...
Console.WriteLine ();
}
Dr. Close (); 7. Add, update, and delete a record using the Npgsqlcommand.executenonquery () method adds a record to the specified table: View Plaincopy to Clipboardprint?
String sql = "Insert test values (1,200)";
Npgsqlcommandobjcommand = new Npgsqlcommand (SQL, conn);
Objcommand.executenonquery ();
String sql = "Insert test values (1,200)";
Npgsqlcommandobjcommand = new Npgsqlcommand (SQL, conn);
Objcommand.executenonquery (); Update record view Plaincopy to Clipboardprint?
sql = "Update test set price=300 where id=1";
Npgsqlcommandobjcommand = new Npgsqlcommand (SQL, conn);
Objcommand.executenonquery ();
sql = "Update test set price=300 where id=1";
Npgsqlcommandobjcommand = new Npgsqlcommand (SQL, conn);
Objcommand.executenonquery (); Delete Record view plaincopy to Clipboardprint?
sql = "Delete from test where id=1";
Npgsqlcommandobjcommand = new Npgsqlcommand (SQL, conn);
Objcommand.executenonquery ();
sql = "Delete from test where id=1";
Npgsqlcommandobjcommand = new Npgsqlcommand (SQL, conn);
Objcommand.executenonquery (); 8. Using the Npgsqldataadapter.fill method, put the retrieved result set into the DataSet object so that you can use the DataSet object to set the DataSource property of the Dotnet DataGridView Control. This displays all records taken from the table in the DataGridView. View Plaincopy to Clipboardprint?
String sql = "Select Id,price from Test";
DataSet ds = new DataSet ();
Npgsqldataadapter objadapter = new Npgsqldataadapter (SQL, conn);
Objadapter.fill (ds, "a"); "A" This table is custom
Dgvbaseresult.datasource = ds. Tables["a"]; Dgvbaseresult is an object of DataGridView.
String sql = "Select Id,price from Test";
DataSet ds = new DataSet ();
Npgsqldataadapter objadapter = new Npgsqldataadapter (SQL, conn);
Objadapter.fill (ds, "a"); "A" This table is custom
Dgvbaseresult.datasource = ds. Tables["a"]; Dgvbaseresult is an object of DataGridView. NOTE: SQL statement settings can also be used with the following statement objAdapter.SelectCommand.CommandText = SQL;
