using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Mvc1.Models
{
    public class ProductInfo
    {
        public int? ProductId { get; set; }
        public string ProductName { get; set; }
        public decimal? UnitPrice { get; set; }
        public string Type { get; set; }

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Mvc1.Models
{
    public class BaseList
    {
        /// <summary>
        /// 产品实体类集合
        /// </summary>
        public IList<ProductInfo> Prods { get; set; }


    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data.OracleClient;
namespace Mvc1.Models
{
    public class Product
    {
        /// <summary>
        /// ylb: 1,GetAll
        /// remark: 获取所有产品，并以productId降序排列
        /// </summary>
        /// <returns></returns>
        public IList<ProductInfo> GetAll()
        {

            IList<ProductInfo> dals = new List<ProductInfo>();

            string sql = "select productId,productName,unitPrice,type from Product order by productId desc";

            OracleConnection conn = new DBConnection().Conn;
            OracleCommand com = conn.CreateCommand();

            com.CommandText = sql;
            conn.Open();
            try
            {
                OracleDataReader sdr = com.ExecuteReader();
                while (sdr.Read())
                {
                    ProductInfo dal = new ProductInfo()
                    {
                        ProductId = sdr.GetInt32(0),
                        ProductName = sdr.GetString(1),
                        UnitPrice = sdr.GetDecimal(2),
                        Type = sdr.GetString(3)
                    };

                    dals.Add(dal);
                }
            }
            finally
            {
                conn.Close();
            }
            return dals;
        }

        /// <summary>
        /// ylb: 2,Add
        /// remark: 添加一个产品
        /// field: productName,unitPrice,type
        /// </summary>
        /// <param name="dal"></param>
        public void Add(ProductInfo dal)
        {

            string sql = "insert into Product(productId,productName,unitPrice,type) values(seqProduct.nextval,:productName,:unitPrice,:type)";

            OracleConnection conn = new DBConnection().Conn;
            OracleCommand com = conn.CreateCommand();

            com.Parameters.Add(new OracleParameter(":productName", dal.ProductName));
            com.Parameters.Add(new OracleParameter(":unitPrice", dal.UnitPrice));
            com.Parameters.Add(new OracleParameter(":type", dal.Type));
            com.CommandText=sql;

            conn.Open();
            try
            {
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }

        }


        /// <summary>
        /// ylb: 3,GetModel
        /// remark: 获得一个实体对象，根据productId
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public ProductInfo GetModel(int productId)
        {
            ProductInfo dal = null;

            string sql = "select productId,productName,unitPrice,type from Product where productId=:productId";

            OracleConnection conn = new DBConnection().Conn;
            OracleCommand com = conn.CreateCommand();

            com.Parameters.Add(new OracleParameter(":productId", productId));
            com.CommandText = sql;
            conn.Open();
            try
            {
                OracleDataReader sdr = com.ExecuteReader();
                while (sdr.Read())
                {
                    dal = new ProductInfo()
                     {
                         ProductId = sdr.GetInt32(0),
                         ProductName = sdr.GetString(1),
                         UnitPrice = sdr.GetDecimal(2),
                         Type = sdr.GetString(3)
                     };
                }
            }
            finally
            {
                conn.Close();
            }
            return dal;
        }

        /// <summary>
        /// ylb: 4,Update
        /// remark: 修改一条信息 ，根据productId</summary>
        /// <param name="dal"></param>
        public void Update(ProductInfo dal)
        {

            string sql = "update Product set productName=:productName,unitPrice=:unitPrice,type=:type where productId=:productId";

            OracleConnection conn = new DBConnection().Conn;
            OracleCommand com = conn.CreateCommand();

            com.Parameters.Add(new OracleParameter(":productName", dal.ProductName));
            com.Parameters.Add(new OracleParameter(":unitPrice", dal.UnitPrice));
            com.Parameters.Add(new OracleParameter(":type", dal.Type));
            com.Parameters.Add(new OracleParameter(":productId", dal.ProductId));
            com.CommandText = sql;

            conn.Open();
            try
            {
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }

        }

        /// <summary>
        /// ylb: 5,Delete
        /// remark: 删除一条信息，根据productId
        /// </summary>
        /// <param name="productId"></param>
        public void Delete(int productId)
        {

            string sql = "delete Product where productId=:productId";

            OracleConnection conn = new DBConnection().Conn;
            OracleCommand com = conn.CreateCommand();

            com.Parameters.Add(new OracleParameter(":productId", productId));
            com.CommandText = sql;

            conn.Open();
            try
            {
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }

        }

    }
}