C#基础学习25（ProductManagmentSystem）

商品管理系统

1.按照三层架构创建项目：

2.引用

3.制作窗体界面 

4. 新建实体模型Model

public class Product
{
    private int id;

    public int Id
    {
        get { return id; }
        set { id = value; }
    }
    private string name;

    public string Name
    {
        get { return name; }
        set { name = value; }
    }
    private string madeTime;

    public string MadeTime
    {
        get { return madeTime; }
        set { madeTime = value; }
    }
    private int price;

    public int Price
    {
        get { return price; }
        set { price = value; }
    }
}

5.UI层代码

public partial class Form1 : Form
{
    private string selectId;
    ProductInfoBLL product_info_bll = new ProductInfoBLL();

    public Form1()
    {
        InitializeComponent();
    }
    //窗体加载事件
    private void Form1_Load(object sender, EventArgs e)
    {
        //调用查询
        LoadProductInfo();
    }
        
    //点击添加按钮
    private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            Product product = new Product();
            product.Name = txtAddName.Text.Trim();
            product.MadeTime = txtAddTime.Text.Trim();
            product.Price = Convert.ToInt32(txtAddPrice.Text.Trim());
            MessageBox.Show(product_info_bll.AddProduct(product));
            LoadProductInfo();
        }
        catch
        {
            MessageBox.Show("不能为空,请输入正确的格式");
        }
    }
    //点击修改按钮
    private void button2_Click(object sender, EventArgs e)
    {
        //调用BLL层修改方法
        try
        {
            Product product = new Product();
            product.Id = Convert.ToInt32(selectId);
            product.Name = txtUpdateName.Text.Trim();
            product.MadeTime = txtUpdateTime.Text.Trim();
            product.Price = Convert.ToInt32(txtUpdatePrice.Text.Trim());
            MessageBox.Show(product_info_bll.UpdataProduct(product));
            LoadProductInfo();
        }
        catch
        {
            MessageBox.Show("不能为空,请输入正确的格式");
        }
    }
    //点击删除按钮
    private void 删除该商品ToolStripMenuItem_Click(object sender, EventArgs e)
    {
        //调用BLL层删除商品
        MessageBox.Show(product_info_bll.DeleteProduct(selectId));
        LoadProductInfo();
    }
    //查询商品信息
    private void LoadProductInfo()
    {
        //调用BLL层，给BLL层要数据
        List<Product> list = product_info_bll.GetProductInfo();
        //绑定数据
        dgv.AutoGenerateColumns = false;
        dgv.DataSource = list;
    }
    //点击数据监听
    private void dgv_CellClick(object sender, DataGridViewCellEventArgs e)
    {
        //获取删除信息所需要的id
        if (dgv.SelectedRows.Count > 0)
        {
            //获取选中的第一行的id
            selectId = dgv.SelectedRows[0].Cells[0].Value.ToString();
        }
        //点击后将信息显示到修改控件上
        txtUpdateName.Text = dgv.SelectedRows[0].Cells[1].Value.ToString();
        txtUpdateTime.Text = dgv.SelectedRows[0].Cells[2].Value.ToString();
        txtUpdatePrice.Text = dgv.SelectedRows[0].Cells[3].Value.ToString();
    }
}

6.BLL层代码

public class ProductInfoBLL
{
    /// <summary>
    /// 查询商品操作
    /// </summary>
    /// <returns></returns>
    public List<Product> GetProductInfo()
    {
        return ProductInfoDAL.GetProductInfo();
    }
    /// <summary>
    /// 添加商品操作
    /// </summary>
    /// <param name="product"></param>
    /// <returns></returns>
    public string AddProduct(Product product)
    {
        if (product.Name.Length > 0 && product.MadeTime.Length > 0 && product.Price.ToString().Length > 0)
        {
            if (ProductInfoDAL.AddProduct(product)>0)
            {
                return "添加成功";
            }
            else
            {
                return "添加失败";
            }
        }
        else
        {
            return "请填写数据";
        }
    }
    /// <summary>
    /// 删除商品操作
    /// </summary>
    /// <param name="deleteId"></param>
    public string DeleteProduct(string deleteId)
    {
        if (deleteId!=null && deleteId.Length>0)
        {
            if (ProductInfoDAL.DeleteProduct(deleteId)>0)
            {
                return "删除成功";
            }
            else
            {
                return "删除失败";
            }
        }
        else
        {
            return "不能为空";
        }
    }
    /// <summary>
    /// 修改商品操作
    /// </summary>
    /// <param name="selectId"></param>
    public string UpdataProduct(Product product)
    {
        if (product.Name.Length > 0 && product.MadeTime.Length > 0 && product.Price.ToString().Length > 0)
        {
            if (ProductInfoDAL.UpdataProduct(product) > 0)
            {
                return "修改成功";
            }
            else
            {
                return "修改失败";
            }
        }
        else
        {
            return "请填写数据";
        }
    }
}

7.DAL层代码

public class ProductInfoDAL
{
    /// <summary>
    /// 数据库查询商品
    /// </summary>
    /// <returns></returns>
    public static List<Product> GetProductInfo()
    {
        List<Product> list = new List<Product>();
        string sql = "select * from product";
        MySqlDataReader reader = SQLHelper.ExecuteReader(sql);
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                list.Add(new Product() { 
                    Id = Convert.ToInt32(reader["number"]),
                    Name = reader["name"].ToString(),
                    MadeTime = reader["madetime"].ToString(),
                    Price = Convert.ToInt32(reader["price"])
                });
            }
        }
        return list;
    }
    /// <summary>
    /// 数据库添加商品
    /// </summary>
    /// <param name="product"></param>
    /// <returns></returns>
    public static int AddProduct(Product product)
    {
        string sql = "insert into product (name,madetime,price) values(@name,@madeTime,@price)";
        MySqlParameter[] msp = 
        {
            new MySqlParameter("@name",product.Name),
            new MySqlParameter("@madeTime",product.MadeTime),
            new MySqlParameter("@price",product.Price)
        };
        return SQLHelper.ExecuteNonQuery(sql,msp);
    }
    /// <summary>
    /// 数据库删除商品
    /// </summary>
    /// <param name="deleteId"></param>
    /// <returns></returns>
    public static int DeleteProduct(string deleteId)
    {
        string sql = "delete from product where number="+deleteId;
        return SQLHelper.ExecuteNonQuery(sql);
    }
    /// <summary>
    /// 数据库更新商品
    /// </summary>
    /// <param name="product"></param>
    /// <returns></returns>
    public static int UpdataProduct(Product product)
    {
        string sql = "update product set name=@name,madeTime=@madeTime,price=@price where number=@id";
        MySqlParameter[] msp =
        {
            new MySqlParameter("@name",product.Name),  
            new MySqlParameter("@madeTime",product.MadeTime),  
            new MySqlParameter("@price",product.Price),  
            new MySqlParameter("@id",product.Id)
        };
        return SQLHelper.ExecuteNonQuery(sql,msp);
    }
}

8.效果图

 

附：SQLHelper代码

public class SQLHelper
{
    //连接字符串，从App.config中获取
    private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

    public static int ExecuteNonQuery(string sql, params MySqlParameter[] msp)
    {
        using (MySqlConnection conn = new MySqlConnection(connStr))
        {
            using (MySqlCommand mcmd = new MySqlCommand(sql, conn))
            {
                if (msp != null)
                {
                    //如果传过来了Parameter则赋值
                    mcmd.Parameters.AddRange(msp);
                }
                conn.Open();
                return mcmd.ExecuteNonQuery();
            }
        }
    }

    public static object ExecuteScalar(string sql, params MySqlParameter[] msp)
    {
        using (MySqlConnection conn = new MySqlConnection(connStr))
        {
            using (MySqlCommand mcmd = new MySqlCommand(sql, conn))
            {
                if (msp != null)
                {
                    mcmd.Parameters.AddRange(msp);
                }
                conn.Open();
                return mcmd.ExecuteScalar();
            }
        }
    }

    public static MySqlDataReader ExecuteReader(string sql,params MySqlParameter[] msp)
    {
        MySqlConnection conn = new MySqlConnection(connStr);
        using (MySqlCommand mcmd = new MySqlCommand(sql, conn))
        {
            if (msp != null)
            {
                mcmd.Parameters.AddRange(msp);
            }
            try
            {
                conn.Open();
                return mcmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//当ExecuteReader对象用完关闭时，也把它关联的Connection对象一块关闭
            }
            catch (Exception ex)
            {
                conn.Close();
                conn.Dispose();
                throw ex;
            }
        }
    }
    public static DataSet MySqlDataSet(string sql, DataSet ds, string tableName, params MySqlParameter[] msp)
    {
        using (MySqlConnection conn = new MySqlConnection(connStr))
        {
            using (MySqlDataAdapter msda = new MySqlDataAdapter(sql, conn))
            {
                if (msp != null)
                {
                    msda.SelectCommand.Parameters.AddRange(msp);
                }
                conn.Open();
                ds.Clear();
                msda.Fill(ds, tableName);
                return ds;
            }
        }
    }
    public static int MySqlDataAdapter(string sql, DataSet ds, string tableName, params MySqlParameter[] msp)
    {
        using (MySqlConnection conn = new MySqlConnection(connStr))
        {
            using (MySqlDataAdapter msda = new MySqlDataAdapter(sql, conn))
            {
                if (msp != null)
                {
                    msda.SelectCommand.Parameters.AddRange(msp);
                }
                MySqlCommandBuilder builder = new MySqlCommandBuilder(msda);
                return msda.Update(ds, tableName);
            }
        }
    }
}