作为一名 .NET C# 开发者，一直在寻找一种更简便的方式来处理 PostgreSQL 数据库。在长时间的使用 MS SQL Server 之后，遇到了一个需要使用 PostgreSQL 的项目需求。通过阅读博客、文章和相关资源，最终发现了 Npgsql 这个库，它几乎可以完成使用 ADO.NET 时的所有操作。幸运的是，手头已经有了一个名为 SQLHandler 的库，它在 ADO.NET 中表现非常出色。所要做的，就是修改代码，调整几行代码，然后测试它是否适用于 Npgsql。结果是，它确实可以。
这个 SQLHandler 类是从一个名为 SageFrame 的开源 ASP.NET CMS 和 Web 应用开发框架中派生出来的。现在，将向展示如何设置和使用 DynamixPostgreSQLHandler 库。
以下示例使用的是 dvdrental 数据库。以下是设置数据库的步骤：
pg_restore -U postgres -n public -d dvdrental dvdrental.tar
以下示例使用 dvdrental 数据库中的数据。可以自己探索代码，并根据需要进行定制。
以下 C# 代码展示了如何执行一个简单的 SQL 查询：
try
{
SQLHandler sqlHandler = new SQLHandler("ConnectionString");
DataTable dtCountries = sqlHandler.ExecuteSQL("SELECT * FROM country;");
}
catch (System.Exception ex)
{
// HANDLE YOUR EXCEPTION HERE
}
假设想要从数据库中提取一个城市列表到 Info 类中。以下是类文件可能的样子（遵循 PostgreSQL 的小写约定）。
public class City
{
public int city_id { get; set; }
public string city { get; set; }
public int country_id { get; set; }
public DateTime last_update { get; set; }
}
以下是 C# 代码示例：
try
{
SQLHandler sqlHandler = new SQLHandler("ConnectionString");
List<City> lstCities = sqlHandler.ExecuteAsListUsingQuery<City>("SELECT * FROM city;");
}
catch (System.Exception ex)
{
// HANDLE YOUR EXCEPTION HERE
}
如果想使用函数名称从数据库中提取记录列表，这里有一个更简单的方法。
CREATE OR REPLACE FUNCTION getactors()
RETURNS SETOF actor
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
RETURN query execute 'select * from actor;';
END
$BODY$;
首先，需要为 Actor 表创建一个类，如下所示：
public class Actor
{
public int actor_id { get; set; }
public string first_name { get; set; }
public string last_name { get; set; }
public DateTime last_update { get; set; }
}
以下是 C# 代码示例：
try
{
SQLHandler sqlHandler = new SQLHandler("ConnectionString");
List<Actor> lstActors = sqlHandler.ExecuteAsList<Actor>("getactors");
}
catch (System.Exception ex)
{
// HANDLE YOUR EXCEPTION HERE
}
使用参数提取数据列表：
CREATE OR REPLACE FUNCTION getinventory_by_filmid_and_storeid(p_film_id integer, p_store_id integer)
RETURNS SETOF inventory
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
RETURN QUERY (select * from inventory WHERE film_id = p_film_id AND store_id = p_store_id);
END
$BODY$;
以下是 C# 代码示例：
try
{
SQLHandler sqlHandler = new SQLHandler("ConnectionString");
List<Inventory> lstInventory = sqlHandler.ExecuteAsList<Inventory>("getinventory_by_filmid_and_storeid", new { p_film_id = 1, p_store_id = 2 });
}
catch (System.Exception ex)
{
// HANDLE YOUR EXCEPTION HERE
}
如果想从数据库中获取一个特定对象，那么可以使用 Execute as Object 方法。
public class Film
{
public long film_id { get; set; }
public string title { get; set; }
public string description { get; set; }
public int release_year { get; set; }
public int language_id { get; set; }
public int rental_duration { get; set; }
public decimal rental_rate { get; set; }
public int length { get; set; }
public decimal replacement_cost { get; set; }
public string rating { get; set; }
public DateTime last_update { get; set; }
public string[] special_features { get; set; }
public object fulltext { get; set; }
}
以下是 C# 代码示例：
try
{
string query = "SELECT * FROM film WHERE film_id = 384;";
SQLHandler sqlHandler = new SQLHandler("ConnectionString");
Film film = sqlHandler.ExecuteAsObjectUsingQuery<Film>(query);
}
catch (System.Exception ex)
{
// HANDLE YOUR EXCEPTION HERE
}
甚至可以使用与 Execute As List 相同的方法传递参数。
CREATE OR REPLACE FUNCTION getactor_detail_by_id(p_actor_id integer)
RETURNS SETOF actor
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
RETURN QUERY (SELECT * FROM actor WHERE actor_id = p_actor_id);
END
$BODY$;
以下是 C# 代码示例：
try
{
SQLHandler sqlHandler = new SQLHandler("ConnectionString");
Actor actor = sqlHandler.ExecuteAsObject<Actor>("getactor_detail_by_id", new { p_actor_id = 1 });
}
catch (System.Exception ex)
{
// HANDLE YOUR EXCEPTION HERE
}
假设想要通过传递参数到函数来向 actor 表中插入一个新的 actor。
CREATE OR REPLACE FUNCTION add_new_actor(fname character varying(45), lname character varying(45))
RETURNS VOID
AS $$
BEGIN
INSERT INTO actor (first_name, last_name) values (fname, lname);
END
$$
LANGUAGE 'plpgsql';