1,安装Npgsql
2, 代码
/// <summary>
/// PostgreSql数据库操作帮助类
/// </summary>
public class PostgreSqlHelper
{
public static string ConnectionString = ConfigurationManager.ConnectionStrings["postgre"].ToString();
/// <summary>
/// 增 删 改
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteSQL(string sql)
{
int back = -1;
using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
{
using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
{
try
{
connection.Open();
back = command.ExecuteNonQuery();
}
catch (NpgsqlException exception)
{
throw new Exception(exception.Message);
}
finally
{
connection.Close();
}
}
}
return back;
}
/// <summary>
/// 增 删 改
/// </summary>
/// <param name="sql"></param>
/// <param name="paras">如果没有传null</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, params NpgsqlParameter[] paras)
{
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 查询操作,返回查询结果中的第一行第一列的值
/// </summary>
/// <param name="sql"></param>
/// <param name="paras">如果没有传null</param>
/// <returns></returns>
public static object ExecuteScalar(string sql, params NpgsqlParameter[] paras)
{
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 查询并返回结果集DataTable
/// </summary>
/// <param name="sql"></param>
/// <param name="paras">如果没有传null</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql, params NpgsqlParameter[] paras)
{
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0];
}
}
}
}