postgresql Accessing Data Programmatically Accessing Postgresql from .NET using the Npgsql provider
Example
One of the more popular .NET providers for Postgresql is Npgsql, which is ADO.NET compatible and is used nearly identically as other .NET database providers.
A typical query is performed by creating a command, binding parameters, and then executing the command. In C#:
var connString = "Host=myserv;Username=myuser;Password=mypass;Database=mydb";
using (var conn = new NpgsqlConnection(connString))
{
var querystring = "INSERT INTO data (some_field) VALUES (@content)";
conn.Open();
// Create a new command with CommandText and Connection constructor
using (var cmd = new NpgsqlCommand(querystring, conn))
{
// Add a parameter and set its type with the NpgsqlDbType enum
var contentString = "Hello World!";
cmd.Parameters.Add("@content", NpgsqlDbType.Text).Value = contentString;
// Execute a query that returns no results
cmd.ExecuteNonQuery();
/* It is possible to reuse a command object and open connection instead of creating new ones */
// Create a new query and set its parameters
int keyId = 101;
cmd.CommandText = "SELECT primary_key, some_field FROM data WHERE primary_key = @keyId";
cmd.Parameters.Clear();
cmd.Parameters.Add("@keyId", NpgsqlDbType.Integer).Value = keyId;
// Execute the command and read through the rows one by one
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read()) // Returns false for 0 rows, or after reading the last row of the results
{
// read an integer value
int primaryKey = reader.GetInt32(0);
// or
primaryKey = Convert.ToInt32(reader["primary_key"]);
// read a text value
string someFieldText = reader["some_field"].ToString();
}
}
}
} // the C# 'using' directive calls conn.Close() and conn.Dispose() for us
