outstore.cs

利用vs2008+sql2000开发的物资管理系统

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

namespace login
{
    public partial class outstore : Form
    {
        SqlDataAdapter sqlDataAdapter1;
        //存取数据库的主要类
        SqlCommand sqlCommand1;
        //SQL语句处理的类
        SqlConnection sqlConnection1;
        // 表示是否处于插入新记录的状态
        private bool bNewRecord = false;
        DataSet dataSet1;
       
        // 获取所有客户的ID
        private void GetCustomID()
        {
            SqlDataReader sdr;
            sqlConnection1.Open();	// 打开连接
            sdr = sqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
            cbxID.Items.Clear();
            while (sdr.Read())
            {
                // 把客户ID插入到组合框控件中
                cbxID.Items.Add(sdr.GetValue(0));
            }
            sdr.Close();			// 关闭SqlDataReader对象和数据库连接
            cbxID.SelectedIndex = 0;
        }
        public outstore()
        {
            InitializeComponent();
            //SQL Server 登录机制
            //String  sConnString = "server=VS-LV;uid=sa;pwd=;database=Northwind";
            // Windows 安全登录机制
            String sConnString = "Data Source=localhost;Initial Catalog=WZGL_20052070;Integrated Security=True";
            //SQL语句
            String sSQL = "SELECT * FROM room";
            //创建一个数据库连接对象
            sqlConnection1 = new SqlConnection(sConnString);
            sqlCommand1 = new SqlCommand(sSQL, sqlConnection1);

            //创建一个SqlDataAdapter对象
            sqlDataAdapter1 = new SqlDataAdapter(sSQL, sqlConnection1);
            // 创建一个DataSet对象
            dataSet1 = new DataSet();
            sqlDataAdapter1.Fill(dataSet1, "admin");
            dataGridView1.DataSource = dataSet1.Tables["admin"];
            sqlCommand1.CommandText = "SELECT pName FROM room order by pName";
            GetCustomID();
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
             // 创建SQL命令对象
            SqlCommand sqlcmd = new SqlCommand(
                "SELECT quantity ,storage FROM room WHERE pName = @ID",
                sqlConnection1);
            // 设置参数


            
            sqlcmd.Parameters.AddWithValue("@ID", cbxID.Text);

            SqlDataReader sdr;
            sqlConnection1.Open();
            sdr = sqlcmd.ExecuteReader();
            if (sdr.Read())
            {
                textBox1.Text = sdr["quantity"].ToString();
                textBox4.Text = sdr["storage"].ToString();
            }
          
            sdr.Close();
            sqlConnection1.Close();
          
            //   处理到 datagridview行的定位
            
            for (int i = 0; i < dataGridView1.RowCount - 1; i++)
            {
                if (dataGridView1.Rows[i].Cells[0].Value.ToString() == cbxID.Text)
                    dataGridView1.Rows[i].Selected = true;
            }
        }

        private void update_Click(object sender, EventArgs e)
        {
        
            //textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            cbxID.DropDownStyle = ComboBoxStyle.DropDown;
            //cbxID.Text = "";
            bNewRecord = true;		
        }

        private void select_Click(object sender, EventArgs e)
        {  
            try
            {
                String constr = "Data Source=localhost; Initial Catalog=WZGL_20052070;Integrated Security=true";
            SqlConnection scon = new SqlConnection(constr);

            SqlDataAdapter sa = new SqlDataAdapter();

            SqlCommand storescommand = new SqlCommand();
         string sqlStatement;
         int a = int.Parse(textBox2.Text);
         int b = int.Parse(textBox1.Text);
         int c = b - a;
            // 根据是否正在添加新记录来建立适当的查询语句
            if (bNewRecord == true)
            {

                sqlStatement = "INSERT INTO outstore VALUES(" +
                    "'" + cbxID.Text + "'," +
                    "'" + textBox2.Text + "'," +
                     c + "," +
                    "'" + textBox3.Text + "'," +
                    "'" + textBox4.Text + "')";
            }
            else
            {
                sqlStatement = "UPDATE outstore SET " +
                    "outquantity='" + textBox2.Text + "'," +
                    "nowquantity="+c + "," +
                    "outstoretime='" + textBox3.Text + "',"+
                    "storage='" + textBox4.Text + "'"+
                    "WHERE pName = '" + cbxID.Text + "'";
            }
            // 创建SQL命令

          
            storescommand.CommandText = sqlStatement;
            storescommand.Connection = scon;

            sa.SelectCommand = storescommand;

            DataSet ds = new DataSet();

            sa.Fill(ds, "admin");

            dataGridView1.DataSource = ds.Tables["admin"];
            
        
               
                MessageBox.Show("已经更新", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                          
            }
            catch (SqlException ex)
            {
                MessageBox.Show("更新错误：" + ex.Message, "出现错误",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
           
            if (bNewRecord == true)
            {

                cbxID.DropDownStyle = ComboBoxStyle.DropDownList;
                bNewRecord = false;
                cbxID.SelectedIndex = cbxID.Items.Count - 1;
            }

        }

       private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
        //  cbxID.Text = ((DataTable)dataGridView1.DataSource).Rows[dataGridView1.CurrentRow.Index].ItemArray[0].ToString();
            cbxID.Text = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells[0].Value .ToString ();
        }
       
        private void button1_Click(object sender, EventArgs e)
        {
            //MainFrm mainform = new MainFrm();
            //mainform.Show();
            //this.Hide();
            this.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            String constr = "Data Source=localhost; Initial Catalog=WZGL_20052070;Integrated Security=true";
            SqlConnection scon = new SqlConnection(constr);

            SqlDataAdapter sa = new SqlDataAdapter();

            SqlCommand storescommand = new SqlCommand();
            string sqlStatement = "select *from outstore ";

            storescommand.CommandText = sqlStatement;
            storescommand.Connection = scon;

            sa.SelectCommand = storescommand;

            DataSet ds = new DataSet();

            sa.Fill(ds, "admin");

            dataGridView1.DataSource = ds.Tables["admin"];
        }

        private void outstore_Load(object sender, EventArgs e)
        {

        }

        private void textBox4_TextChanged(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }
    }
}