使用 Npgsql 提供程式從 .NET 訪問 Postgresql
Postgresql 最流行的 .NET 提供程式之一是 Npgsql ,它與 ADO.NET 相容,幾乎與其他 .NET 資料庫提供程式一樣使用。
通過建立命令,繫結引數,然後執行命令來執行典型查詢。在 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