C# 操作PostgreSQL 数据库

一、第三方类库

Npgsql提供 .NET 操作postgreSQL数据库的相关类。支持postgreSQL7.x 及以上版本。

二、自定义接口及操作类

2.1接口类

public interface IDBHelper      {          /// <summary>          /// 执行 Transact-SQL 语句并返回受影响的行数。          /// </summary>          int ExecuteNonQuery(string connectionString, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);            /// <summary>          /// 在事务中执行 Transact-SQL 语句并返回受影响的行数。          /// </summary>          int ExecuteNonQuery(System.Data.Common.DbTransaction trans, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);            /// <summary>          /// 在事务中执行查询，返回DataSet          /// </summary>          DataSet ExecuteQuery(System.Data.Common.DbTransaction trans, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);            /// <summary>          /// 执行查询，返回DataSet          /// </summary>          DataSet ExecuteQuery(string connectionString, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);            /// <summary>          /// 在事务中执行查询，返回DataReader          /// </summary>          DbDataReader ExecuteReader(System.Data.Common.DbTransaction trans, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);            /// <summary>          /// 执行查询，返回DataReader          /// </summary>          DbDataReader ExecuteReader(string connectionString, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);            /// <summary>          /// 在事务中执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。          /// </summary>          object ExecuteScalar(System.Data.Common.DbTransaction trans, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);            /// <summary>          /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。          /// </summary>          object ExecuteScalar(string connectionString, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);            /// <summary>          /// 得到数据条数          /// </summary>          /// <param name="tblName">表名</param>          /// <param name="condition">条件(不需要where)</param>          /// <returns>数据条数</returns>          int GetCount(string connectionString, string tblName, string condition);      }

2.2 操作类

/// <summary>      /// 数据库操作基类(for PostgreSQL)      /// </summary>      public class PostgreHelper : PageHelper, IDBHelper      {          /// <summary>          /// 分页获取数据          /// </summary>          /// <param name="connectionString">连接字符串</param>          /// <param name="tblName">表名</param>          /// <param name="fldName">字段名</param>          /// <param name="pageSize">页大小</param>          /// <param name="pageIndex">第几页</param>          /// <param name="fldSort">排序字段</param>          /// <param name="sort">升序{False}/降序(True)</param>          /// <param name="condition">条件(不需要where)</param>          public DbDataReader GetPageList(string connectionString, string tblName, int pageSize,              int pageIndex, string fldSort, bool sort, string condition)          {              string sql = GetPagerSQL(condition, pageSize, pageIndex, fldSort, tblName, sort);              return ExecuteReader(connectionString, CommandType.Text, sql, null);          }            /// <summary>          /// 得到数据条数          /// </summary>          public int GetCount(string connectionString, string tblName, string condition)          {              StringBuilder sql = new StringBuilder("select count(*) from " + tblName);              if (!string.IsNullOrEmpty(condition))                  sql.Append(" where " + condition);                object count = ExecuteScalar(connectionString, CommandType.Text, sql.ToString(), null);              return int.Parse(count.ToString());          }            /// <summary>          /// 执行查询，返回DataSet          /// </summary>          public DataSet ExecuteQuery(string connectionString, CommandType cmdType, string cmdText,              params DbParameter[] cmdParms)          {              using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))              {                  using (NpgsqlCommand cmd = new NpgsqlCommand())                  {                      PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);                      using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))                      {                          DataSet ds = new DataSet();                          da.Fill(ds, "ds");                          cmd.Parameters.Clear();                          return ds;                      }                  }              }          }            /// <summary>          /// 在事务中执行查询，返回DataSet          /// </summary>          public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText,              params DbParameter[] cmdParms)          {              NpgsqlCommand cmd = new NpgsqlCommand();              PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);              NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);              DataSet ds = new DataSet();              da.Fill(ds, "ds");              cmd.Parameters.Clear();              return ds;          }            /// <summary>          /// 执行 Transact-SQL 语句并返回受影响的行数。          /// </summary>          public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,              params DbParameter[] cmdParms)          {              NpgsqlCommand cmd = new NpgsqlCommand();                using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))              {                  PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);                  int val = cmd.ExecuteNonQuery();                  cmd.Parameters.Clear();                  return val;              }          }            /// <summary>          /// 在事务中执行 Transact-SQL 语句并返回受影响的行数。          /// </summary>          public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText,              params DbParameter[] cmdParms)          {              NpgsqlCommand cmd = new NpgsqlCommand();              PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);              int val = cmd.ExecuteNonQuery();              cmd.Parameters.Clear();              return val;          }            /// <summary>          /// 执行查询，返回DataReader          /// </summary>          public DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,              params DbParameter[] cmdParms)          {              NpgsqlCommand cmd = new NpgsqlCommand();              NpgsqlConnection conn = new NpgsqlConnection(connectionString);                try              {                  PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);                  NpgsqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);                  cmd.Parameters.Clear();                  return rdr;              }              catch              {                  conn.Close();                  throw;              }          }            /// <summary>          /// 在事务中执行查询，返回DataReader          /// </summary>          public DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText,              params DbParameter[] cmdParms)          {              NpgsqlCommand cmd = new NpgsqlCommand();              PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);              NpgsqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);              cmd.Parameters.Clear();              return rdr;          }            /// <summary>          /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。          /// </summary>          public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText,              params DbParameter[] cmdParms)          {              NpgsqlCommand cmd = new NpgsqlCommand();                using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))              {                  PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);                  object val = cmd.ExecuteScalar();                  cmd.Parameters.Clear();                  return val;              }          }            /// <summary>          /// 在事务中执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。          /// </summary>          public object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText,              params DbParameter[] cmdParms)          {              NpgsqlCommand cmd = new NpgsqlCommand();              PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);              object val = cmd.ExecuteScalar();              cmd.Parameters.Clear();              return val;          }            /// <summary>          /// 生成要执行的命令          /// </summary>          /// <remarks>参数的格式：冒号+参数名</remarks>          private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType,              string cmdText, DbParameter[] cmdParms)          {              if (conn.State != ConnectionState.Open)                  conn.Open();                cmd.Connection = conn;              cmd.CommandText = cmdText.Replace("@", ":").Replace("?", ":").Replace("[", "\"").Replace("]", "\"");                if (trans != null)                  cmd.Transaction = trans;                cmd.CommandType = cmdType;                if (cmdParms != null)              {                  foreach (NpgsqlParameter parm in cmdParms)                  {                      parm.ParameterName = parm.ParameterName.Replace("@", ":").Replace("?",":");                        cmd.Parameters.Add(parm);                  }              }          }      }

写入实例：

IDBHelper dbHelper = new PostgreHelper();  string connectionString = "User ID=postgres;Password=admin;Server=192.168.0.226;Port=5432;Database=MonitorDB;";  string sql = "insert into [RawLog]([ProjectID],[File],[Note]) values('项目编号',@file,'备注')";    string file = this.textBox1.Text;  if (!File.Exists(file)) return;    //获取文件二进制流  System.IO.FileStream fs = new System.IO.FileStream(file, System.IO.FileMode.Open);  BinaryReader br = new BinaryReader(fs);  byte[] b = br.ReadBytes((int)fs.Length);  fs.Close();    int r = dbHelper.ExecuteNonQuery(connectionString, CommandType.Text,sql,new Npgsql.NpgsqlParameter("@file",raw_log.File));

1.查询语句中表名、字段名需用中括号[ ] 封起来，在操作类中会统一替换为双引号。

2.命令语句中的参数名格式为：@或？+ 参数名，在操作类中为统一替换为冒号。

3.对于二进制流、日期等特殊类型需采用2中的参数形式

4.经多次测试postgreSQL数据库只直接支持（冒号+参数名）的参数方式