C# 操作PostgreSQL 數據庫

一、第三方類庫

Npgsql提供 .NET 操作postgreSQL數據庫的相關類。支持postgreSQL7.x 及以上版本。

二、自定義接口及操作類

2.1接口類

[csharp] 
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 操作類

[csharp]
/// <summary> 
    /// 數據庫操作基類(for PostgreSQL) 
    /// </summary> 
    public class PostgreHelper : IDBHelper 
    { 
        /// <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); 
                } 
            } 
        } 
    } 

三、數據庫操作實例
寫入實例：

[csharp] 
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); 
br.Close();fs.Close(); 
 
int r = dbHelper.ExecuteNonQuery(connectionString, CommandType.Text,sql,new Npgsql.NpgsqlParameter("@file",raw_log.File)); 

注意：
1.查詢語句中表名、字段名需用中括號[ ] 封起來，在操作類中會統一替換為雙引號。

2.命令語句中的參數名格式為：@或？+ 參數名，在操作類中為統一替換為冒號。
3.對於二進制流、日期等特殊類型需采用2中的參數形式

4.經多次測試postgreSQL數據庫只直接支持（冒號+參數名）的參數方式