初始数据库 连接数据库开发的简易零食系统

这周刚学过数据库,再通过老师讲的一些三层架构的原理,就开始了零食系统的制作

比较简易的代码   大家多给意见

首先是   MODEL  层

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MODEL
{
    public enum SType 
    {
        零食,
        饮料
    }
    
    public class Snacks
    {
        public int ID {  get; set; }
        public string Name { get; set; }

        public SType SnacksType { get; set; }
        public double Price { get; set; }

    }
}

model层对应的是数据库的内容

第二个是    DAL    层

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace DAL
{
    public class Snackslist
    {
        public string conn = ConfigurationManager.ConnectionStrings["con"].ConnectionString.ToString();
        
        public DataSet GetDataSet()
        {
            SqlConnection sqlConnection = new SqlConnection(conn);
            DataSet ds = SqlHelper.ExecuteDataset(sqlConnection,CommandType.Text, "select * from SnacksTable");
            return ds;
        }
   
    }
}

DAL层还添加了微软的sqlHelper工具   大家可以去微软官方找

https://www.cnblogs.com/liwuyi/archive/2012/05/16/2505461.html

这是一个翻译的   仅供参考

第三个层是   BLL   层

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using DAL;
using Microsoft.ApplicationBlocks.Data;
using MODEL;

namespace BLL
{
    public class SnackLogic : Snackslist
    {
        public void Add(string name, string type, double prices)
        {
            string sql = "insert into [SnacksTable](SnacksName,SnacksType,SnacksPrice) values (@SnacksName,@SnacksType,@SnacksPrice)";
            SqlParameter[] parameters = new SqlParameter[] {
                    new SqlParameter("@SnacksName", name),
                    new SqlParameter("@SnacksType", type),
                    new SqlParameter("@SnacksPrice",prices)
                };
            int row = SqlHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text, sql, parameters);
        }
        public void Delete(int id)
        {
            string sql = "delete from [SnacksTable] where SnacksId = @SnacksId";
            SqlParameter parameters = new SqlParameter("@SnacksId", id);
            int row = SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, parameters);
        }
        public void Update(int id, string name, string type, double prices)
        {
            string sql = "Update  [SnacksTable] set SnacksName = @SnacksName,SnacksType = @SnacksType,SnacksPrice = @SnacksPrice where SnacksId = @SnacksId";
            SqlParameter[] parameters = new SqlParameter[] {
                    new SqlParameter("@SnacksName", name),
                    new SqlParameter("@SnacksType", type),
                    new SqlParameter("@SnacksPrice",prices),
                    new SqlParameter("@SnacksId",id)
                };
            int row = SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, parameters);
        }
        public string sqlSearch = "select * from [SnacksTable] where (1=1)";
        public void Search(string type ,string name)
        {
            if (!string.IsNullOrWhiteSpace(type)) 
            {
                sqlSearch += $" and SnacksType = {type}";
            }
            if (!string.IsNullOrWhiteSpace(name))
            {
                sqlSearch += $" and SnacksName like '%{name}%'";
            }
        }
        public DataSet GetSearch()
        {
            SqlConnection sqlConnection = new SqlConnection(conn);
            DataSet ds = SqlHelper.ExecuteDataset(sqlConnection, CommandType.Text, sqlSearch);
            return ds;
        }
    }
}

BLL层写的是一些逻辑

第四层是    UI    层  也是我们的窗体层

j

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Windows.Forms.VisualStyles;
using BLL;
using MODEL;
using DAL;
using System.Configuration;
using static System.Net.Mime.MediaTypeNames;
using System.Xml.Linq;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace UI
{
    public partial class Master : Form
    {
        private SnackLogic snacksUI = new SnackLogic();
        private Snackslist snackslistview = new Snackslist();
        public Master()
        {
            InitializeComponent();

            Add_Com();
        }
        private void Master_Load(object sender, EventArgs e)
        {
            viewDate();
        }

        private void viewDate()
        {
            dataGridView1.DataSource = null;
            dataGridView1.DataSource = snackslistview.GetDataSet().Tables[0];
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            if(string.IsNullOrEmpty(txtName.Text))
            {
                MessageBox.Show("请输入零食的名称");
                return;
            }
            if(string.IsNullOrEmpty(txtPrice.Text))
            {
                MessageBox.Show("请输入正确的价格");
                return;
            }
            try
            {
                
                snacksUI.Add(txtName.Text, comType.Text, Convert.ToDouble(txtPrice.Text));
                viewDate();
                txtName.Text = "";
                comType.SelectedIndex = 0;
                txtPrice.Text = "";
            }
            catch
            {
                MessageBox.Show("您输入的格式有误");
                return;
            }
            
        }
        private void Add_Com()
        {
            comType.Items.Add(SType.零食);
            comType.Items.Add(SType.饮料);

            comSearchType.Items.Add(SType.零食);
            comSearchType.Items.Add(SType.饮料);
        }

        private void btnRevise_Click(object sender, EventArgs e)
        {
            int id = (int)dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells[0].Value;
            try
            {
                snacksUI.Update(id, txtName.Text, comType.Text, Convert.ToDouble(txtPrice.Text));
                viewDate();
            }
            catch
            {
                MessageBox.Show("您输入的格式有误");
                return;
            }
        }
        private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                int id = (int)dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells[0].Value;
                snacksUI.Delete(id);
                viewDate();
            }
            catch 
            {
                MessageBox.Show("已清空");
            }
            
        }
        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            int index = dataGridView1.CurrentRow.Index;
            txtName.Text = dataGridView1.Rows[index].Cells[1].Value.ToString();
            /*comType.SelectedItem = dataGridView1.Rows[index].Cells[2].Value.ToString();*/
            comType.SelectedIndex = dataGridView1.Rows[index].Cells[2].Value.ToString() == "饮料  " ? 1 :0 ;
            txtPrice.Text = dataGridView1.Rows[index].Cells[3].Value.ToString();
        }

        private void Search_Click(object sender, EventArgs e)
        {
            snacksUI.Search(comSearchType.Text, txtSearchName.Text);
            SearchDate();
        }
        public void SearchDate()
        {
            dataGridView1.DataSource = null;
            dataGridView1.DataSource = snacksUI.GetSearch().Tables[0];
        }

        private void viewAll_Click(object sender, EventArgs e)
        {
            txtSearchName.Text = null;
            comSearchType.Text = null;
            viewDate();
        }
    }
}

记得别忘了配置连接字符串

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
    </startup>
	<connectionStrings>
		<add name ="con" connectionString="server =.;database = Snacks;uid = sa;pwd = ******;"/>
	</connectionStrings>
</configuration>

 ******是自己的密码哟

写代码就是一个不断试错的过程  会有很多的失败   但失败累计的也是经验  也会精进你的水平  

希望我们都不要气馁  努力学习