基本信息

---

下载

   友情提示：（无需注册或充值，赞助后即可获取资源下载链接）

---

     嘿，亲！知识可是无价之宝呢，但咱这精心整理的资料也耗费了不少心血呀。小小地破费一下，绝对物超所值哦！如有下载和支付问题，请联系我们QQ(微信同号)：78630559

本次赞助数额为： 2 元　

支付宝支付

微信支付

   源码介绍

---

/* ----------------------------------------------------------
 * 文件名称：NpgsqlHandler.cs
 *
 * 开发环境：
 *      Visual Studio V2015
 *      .NET Framework 4.5.2
 *      PostgreSQL 9.5.3
 *  
 *  版本历史：
 *      V1.0    2016年06月22日
 *              基于Npgsql实现与PostgreSQL数据库交互
 ------------------------------------------------------------ */
using Npgsql;

namespace Com.FirstSolver
{
    public class NpgsqlHandler
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string ConnectionString;

        /// <summary>
        /// 构造函数
        /// </summary>
        public NpgsqlHandler()
        {
            // 生成数据库连接字符串
            ConnectionString = new NpgsqlConnectionStringBuilder()
            {
                Database = "postgres",

                Host = "localhost",
                Port = 5432,

                Username = "postgres",
                Password = "wantgirl"
            }.ToString();

            // 创建产品信息表
            using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
            {
                conn.Open();
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    cmd.CommandText = "CREATE TABLE IF NOT EXISTS Products(ProductID SERIAL PRIMARY KEY, Name TEXT, Description TEXT, Price MONEY, Category TEXT)";
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 删除产品
        /// </summary>
        /// <param name="id">产品编号</param>
        /// <returns>
        ///     true：成功
        ///     false：失败
        /// </returns>
        public bool Delete(int id)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
            {
                conn.Open();
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {   // 更新键值
                    cmd.CommandText = string.Format("DELETE FROM Products WHERE ProductID={0}", id);
                    cmd.Connection = conn;
                    return cmd.ExecuteNonQuery() == 1;
                }
            }
        }

        /// <summary>
        /// 新增产品
        /// </summary>
        /// <param name="name">产品名称</param>
        /// <param name="description">产品描述</param>
        /// <param name="price">产品价格</param>
        /// <param name="category">产品分类</param>
        /// <returns>
        ///     true：成功
        ///     false：失败
        /// </returns>
        public bool Insert(string name, string description, decimal price, string category)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
            {
                conn.Open();
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {   // 更新键值
                    cmd.CommandText = string.Format("INSERT INTO Products(Name, Description, Price, Category) VALUES('{0}', '{1}', {2}, '{3}')", name, description, price, category);
                    cmd.Connection = conn;
                    return cmd.ExecuteNonQuery() == 1;
                }
            }
        }

        /// <summary>
        /// 产品列表
        /// </summary>
        public System.Collections.IEnumerable Products
        {
            get
            {
                using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
                {
                    conn.Open();
                    using (NpgsqlCommand cmd = new NpgsqlCommand())
                    {
                        cmd.CommandText = "SELECT * FROM Products";
                        cmd.Connection = conn;
                        using (NpgsqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                yield return new Product(dr.GetInt32(0), dr.GetString(1), dr.GetString(2), dr.GetDecimal(3), dr.GetString(4));
                            }

                            yield break;
                        }
                    }
                }
            }
            set
            {
                using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
                {
                    conn.Open();
                    using (NpgsqlCommand cmd = new NpgsqlCommand())
                    {
                        cmd.Connection = conn;
                        foreach (Product item in value)
                        {
                            if (item.ProductID == 0)
                            {   // 新增
                                cmd.CommandText = string.Format("INSERT INTO Products(Name, Description, Price, Category) VALUES('{0}', '{1}', {2}, '{3}')", item.Name, item.Description, item.Price, item.Category);
                            }
                            else
                            {   // 更新
                                cmd.CommandText = string.Format("UPDATE Products SET Name='{0}', Description='{1}', Price={2}, Category='{3}' WHERE ProductID={4}", item.Name, item.Description, item.Price, item.Category, item.ProductID);
                            }

                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }
    }
}