formspxx.cs

超市进销存管理系统 Visual Studio 2005+SQL Sever2005

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace _10901BS
{
    public partial class formSpxx : Form
    {
        int d= 0;
        SqlConnection cn = new SqlConnection();
        SqlCommand cm = new SqlCommand();
        SqlDataAdapter da,da1,da2;
        SqlDataReader ddd;
        DataSet dd = new DataSet();
        DataSet dd1 = new DataSet();
        DataSet dd2 = new DataSet();
        public formSpxx()
        {
            InitializeComponent();
        }

        private void spxxBiao_Load(object sender, EventArgs e)
        {
            if (cn.State == ConnectionState.Closed)
            {
                cn.ConnectionString = "Data Source=.;Initial Catalog=110901DB;Integrated Security=True";
                cn.Open();
            }
            da = new SqlDataAdapter("select spID as '商品编号',spName as '商品名称',spGgxh as '商品规格型号',spJldw as '商品计量单位',spBzsj as '商品标准售价',spDztj as '商品打折特价',spLbName as '商品类别名称',spKcsx as '商品库存上限',spKcXX as '商品库存下限',spScrq as '商品生产日期',spBzrq as '商品保质日期',beizhu as '备注' from spxqBiao", cn);
            dd.Clear();
            da.Fill(dd);
            this.dataGridView1.DataSource = dd.Tables[0];

            da1 = new SqlDataAdapter("select distinct spJldw from spxqBiao where spJldw not like ''", cn);
            dd1.Clear();
            da1.Fill(dd1);
            this.txtspJldw.Items.Clear();
            foreach(DataRow row in dd1.Tables[0].Rows )
            {
                this.txtspJldw.Items.Add(row.ItemArray[0] );
            }

            da2 = new SqlDataAdapter("select distinct spLbName from spxqBiao where spJldw not like ''", cn);
            dd2.Clear();
            da2.Fill(dd2);
            this.txtLbName.Items.Clear();
            foreach (DataRow row in dd2.Tables[0].Rows)
            {
                this.txtLbName.Items.Add(row.ItemArray[0]);
            }

            this.textBox1.Hide();
            this.label1.Hide();
            this.pictureBox1.Show();
            this.txtspID.Text =""; 
            this.txtspName.Text =""; 
            this.txtspGgxh.Text =""; 
            this.txtspJldw.Text ="";
            this.txtLbName.Text ="";
            this.txtbeiZhu.Text = "";
            this.txtspScrq.Text = "";
            this.txtspBcrq.Text = "";
            this.txtKcsx.Text = "";
            this.txtspKcXX.Text = "";
            this.txtspBzsj.Text = "";
            this.txtspDztj.Text = "";
            this.txtspScrq1.Hide();
            this.txtspBcrq1.Hide();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            this.txtspID.Text = this.dataGridView1.CurrentRow.Cells[0].Value.ToString();
            this.txtspName.Text = this.dataGridView1.CurrentRow.Cells[1].Value.ToString();
            this.txtspGgxh.Text = this.dataGridView1.CurrentRow.Cells[2].Value.ToString();
            this.txtspJldw.Text = this.dataGridView1.CurrentRow.Cells[3].Value.ToString();
            this.txtspBzsj.Text = this.dataGridView1.CurrentRow.Cells[4].Value.ToString();
            this.txtspDztj.Text = this.dataGridView1.CurrentRow.Cells[5].Value.ToString();
            this.txtLbName.Text = this.dataGridView1.CurrentRow.Cells[6].Value.ToString();
            this.txtKcsx.Text = this.dataGridView1.CurrentRow.Cells[7].Value.ToString();
            this.txtspKcXX.Text = this.dataGridView1.CurrentRow.Cells[8].Value.ToString();
            this.txtspScrq.Text = this.dataGridView1.CurrentRow.Cells[9].Value.ToString();
            this.txtspBcrq.Text = this.dataGridView1.CurrentRow.Cells[10].Value.ToString();
            this.txtbeiZhu.Text = this.dataGridView1.CurrentRow.Cells[11].Value.ToString();

        }

        private void 添加ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (this.txtspID.Text == "")
            {
                cm.Connection = cn;
                cm.CommandText = "select * from spxqBiao order by spID desc";
                this.txtspID.Text = Convert.ToString(Convert.ToInt16(cm.ExecuteScalar().ToString()) + 1).PadLeft(4, '0');
            }
            else
            {
                cm.Connection = cn;
                cm.CommandText = "select * from spxqBiao where spID like '%" + this.txtspID.Text + "%' ";
                ddd = cm.ExecuteReader();
                if (ddd.Read())
                {
                    ddd.Close();
                    MessageBox.Show("此商品编号已被占用!");
                    this.txtspID.Text = "";
                    this.txtspID.Focus();
                    return;
                }

                ddd.Close();
            }

            if( this.txtKcsx.Text == "" )
            {
                this.txtKcsx.Text = "0";
            }
            if( this.txtspKcXX.Text == "" )
            {
                this.txtspKcXX.Text = "0";
            }
            if( this.txtspBzsj.Text == "" )
            {
                this.txtspBzsj.Text ="0.00";
            }
            if( this.txtspDztj.Text == "")
            {
                this.txtspDztj.Text = "0.00";
            }
           
            cm.Connection = cn;
            cm.CommandText = "insert spxqBiao values('" + this.txtspID.Text + "','" + this.txtspName.Text + "','" + this.txtspGgxh.Text + "','" + this.txtspJldw.Text + "','" + Convert.ToDecimal(this.txtspBzsj.Text) + "','" + Convert.ToDecimal(this.txtspDztj.Text) + "','" + this.txtLbName.Text + "','" + Convert.ToInt32(this.txtKcsx.Text) + "','" + Convert.ToInt32(this.txtspKcXX.Text) + "','" + this.txtspScrq.Text + "','" + this.txtspBcrq.Text + "','" + this.txtbeiZhu.Text + "')";
            cm.ExecuteNonQuery();
            MessageBox.Show("添加成功!");
            spxxBiao_Load(sender, e);

        }

        private void 修改ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            
            cm.Connection = cn;
            cm.CommandText = "update spxqBiao set spName='" + this.txtspName.Text + "',spGgxh='" + this.txtspGgxh.Text + "',spJldw='" + this.txtspJldw.Text + "',spBzsj='" + this.txtspBzsj.Text + "',spDztj='" + this.txtspDztj.Text + "',spLbName='" + this.txtLbName.Text + "',spKcsx='" + this.txtKcsx.Text + "',spKcXX='" + this.txtspKcXX.Text + "',spScrq='" + this.txtspScrq.Text + "',spBzrq='" + this.txtspBcrq.Text + "',beiZhu='" + this.txtbeiZhu.Text + "'where spID='" + this.dataGridView1.CurrentRow.Cells[0].Value.ToString() + "'";
            cm.ExecuteNonQuery();
            MessageBox.Show("修改成功!");
            spxxBiao_Load(sender, e);

        }

        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (d == 0)
            {
                if(this.txtspID.Text=="")
                {
                    MessageBox.Show("不能确认您所要删除的信息,请您核对后再删除!");
                    this.txtspID.Focus();
                    return;
                }
                cm.Connection = cn;
                cm.CommandText = "select * from xsxqBiao where spID like '%" + this.txtspID.Text + "%' ";
                ddd = cm.ExecuteReader();
                if (ddd.Read())
                {
                    ddd.Close();
                    MessageBox.Show("此商品已发生销售业务,信息不能被删除!");
                    return;
                }
                ddd.Close();

                cm.Connection = cn;
                cm.CommandText = "select * from jhxqBiao where spID like '%" + this.txtspID.Text + "%' ";
                ddd = cm.ExecuteReader();
                if (ddd.Read())
                {
                    ddd.Close();
                    MessageBox.Show("此商品已发生进货业务,信息不能被删除!");
                    return;
                }
                ddd.Close();

                DialogResult c = MessageBox.Show("真的要删除吗?", "删除", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (c == DialogResult.Yes)
                {
                    
                    cm.Connection = cn;
                    cm.CommandText = "delete spxqBiao where spID='" + this.txtspID.Text + "'";
                    cm.ExecuteNonQuery();
                    MessageBox.Show("删除成功!");
                    spxxBiao_Load(sender, e);

                }
            }
            else
            {
                 MessageBox.Show("您的操作未完成，请点击 取消 后进行此操作!");
            }
        }

        private void 刷新ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            spxxBiao_Load(sender, e);

        }
        //查询功能
        private void 查询_Click(object sender, EventArgs e)
        {
            if (txtspID.Text == "" && txtspName.Text == "" && txtspGgxh.Text == "" && txtspJldw.Text == "" && txtspBzsj.Text == "" && txtspDztj.Text == "" && txtLbName.Text == "" && txtKcsx.Text == "" && txtspKcXX.Text == "" && txtspScrq.Text == "" && txtspBcrq.Text == "")
            {
                MessageBox.Show("请输入查询信息!");
                return;
            }
            
            else 
            {
                cm.Connection = cn;
                cm.CommandText = "select * from spxqBiao where spID like '%" + this.txtspID.Text + "%' ";
                cm.CommandText +="and spName like '%" + this.txtspName.Text + "%' and spGgxh like '%" + this.txtspGgxh.Text + "%' ";
                cm.CommandText +="and spJldw like '%" + this.txtspJldw.Text + "%' and spBzsj like '%" + this.txtspBzsj.Text + "%'  ";
                cm.CommandText +="and spDztj like '%" + this.txtspDztj.Text + "%' and spLbName like '%" + this.txtLbName.Text + "%' ";
                cm.CommandText +="and spKcsx like '%" + this.txtKcsx.Text + "%' and spKcXX like '%" + this.txtspKcXX.Text + "%' ";
                cm.CommandText +="and spScrq like '%" + this.txtspScrq.Text + "%' and spBzrq like '%" + this.txtspBcrq.Text + "%' ";

                ddd = cm.ExecuteReader();
                if (ddd.Read())
                {
                    dd.Clear();
                    ddd.Close();


                    //网格显示数据

                    cm.CommandText = "select spID as '商品编号',spName as '商品名称',spGgxh as '商品规格型号',spJldw as '商品计量单位',spBzsj as '商品标准售价',spDztj as '商品打折特价',spLbName as '商品类别名称',spKcsx as '商品库存上限',spKcXX as '商品库存下限',spScrq as '商品生产日期',spBzrq as '商品保质日期',beizhu as '备注' ";