封装Npgsql

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Npgsql;
using System.Configuration;
using System.Data;

namespace PostgreSQLTest.DAO
{
    public class NpgsqlHelper
    {
        private static string connectionString = ConfigurationManager.ConnectionStrings["PgConnection"].ConnectionString;

        public NpgsqlHelper() { }

        public static int ExecuteSql(string sql)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(sql, connection))
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
            }
        }
        
        public static int GetScalar(string sql)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(sql, connection);
                connection.Open();
                object obj = cmd.ExecuteScalar();
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    return 0;
                }
                else
                {
                    return Convert.ToInt32(obj);
                }
            }
        }

        public static DataTable Query(string sql)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                connection.Open();
                NpgsqlDataAdapter command = new NpgsqlDataAdapter(sql, connection);
                command.Fill(ds, "ds");
                return ds.Tables[0];
            }
        }


        #region 带参数的sql


        public static int ExecuteSql(string sql, params NpgsqlParameter[] param)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                NpgsqlCommand cmd = new NpgsqlCommand();
                PrepareCommand(cmd, connection, null, sql, param);
                int rows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return rows;
            }
        }


        public static int GetScalar(string sql, params NpgsqlParameter[] param)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(sql, connection);
                PrepareCommand(cmd, connection, null, sql, param);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    return 0;
                }
                else
                {
                    return Convert.ToInt32(obj);
                }
            }
        }

        public static DataTable Query(string sql, params NpgsqlParameter[] param)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(sql, connection);
                PrepareCommand(cmd, connection, null, sql, param);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "ds");
                cmd.Parameters.Clear();
                return ds.Tables[0];
            }
        }


        #endregion


        #region 存储过程
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                NpgsqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
                connection.Open();
                return command.ExecuteNonQuery();
            }
        }


        #endregion



        #region private


        private static void PrepareCommand(NpgsqlCommand cmd, NpgsqlConnection conn, NpgsqlTransaction trans, string cmdText, NpgsqlParameter[] param)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (param != null)
            {
                foreach (NpgsqlParameter parameter in param)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
        private static NpgsqlCommand BuildQueryCommand(NpgsqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            NpgsqlCommand command = new NpgsqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (NpgsqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }
        #endregion

    }
}