使用LINQ修改SQL Server 数据库

案例练习-修改库存商品信息

创建一个Windows应用程序，主要用来对库存商品信息进行修改。设计Form1用来向库存商品信息表中添加数据。在当前项目中创建一个LINQ to SQL类文件，然后在Form1窗体中定义一个String类型的变量，用来记录数据库连接字符串，并声明LINQ连接对象。

在窗体中点击修好按钮，要先创建LINQ连接对象，创建与之对应的类，再赋值，最后调用LINQ连接对象中的InsertOnSubmit方法修改商品信息,最后再把修改商品操作提交给服务器。

完整示例代码：

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

namespace LINQUpdateSql2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        #region 定义公共变量及Linq连接对象
        //定义数据库连接字符串
        string strCon = "Data Source=UerDate-pc\\SQL2019;Database=db_Use;Uid=sa;Pwd=123456;";
        linqtosqlClassDataContext linq;          			//声明Linq连接对象
        #endregion

        private void Form1_Load(object sender, EventArgs e)
        {
            BindInfo();
        }

        private void dgvInfo_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            linq = new linqtosqlClassDataContext(strCon);//实例化Linq连接对象
            //获取选中的商品编号
            txtID.Text = Convert.ToString(dgvInfo[0, e.RowIndex].Value).Trim();
            //根据选中的商品编号获取其详细信息，并重新成成一个表
            var result = from info in linq.tb_stock
                         where info.tradecode == txtID.Text
                         select new
                         {
                             ID = info.tradecode,
                             Name = info.fullname,
                             Unit = info.unit,
                             Type = info.type,
                             Standard = info.standard,
                            Produce = info.produce,
                             Qty = info.qty,
                             Price = info.price
                         };
            //相应的文本框及下拉列表中显示选中商品的详细信息
            foreach (var item in result)
            {
                txtName.Text = item.Name;
                cbox.Text = item.Unit;
                txtType.Text = item.Type;
                txtISBN.Text = item.Standard;
                txtAddress.Text = item.Produce;
                txtNum.Text = item.Qty.ToString();
                txtPrice.Text = item.Price.ToString();
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            if (txtID.Text == "")
            {
                MessageBox.Show("请选择要修改的记录");
                return;
            }
            linq = new linqtosqlClassDataContext(strCon);    //创建Linq连接对象
            //查找要修改的商品信息
            var result = from stock in linq.tb_stock
                         where stock.tradecode == txtID.Text
                         select stock;
            //对指定的商品信息进行修改
            foreach (tb_stock stock in result)
            {
                stock.tradecode = txtID.Text;
                stock.fullname = txtName.Text;
                stock.unit = cbox.Text;
                stock.type = txtType.Text;
                stock.standard = txtISBN.Text;
                stock.produce = txtAddress.Text;
                stock.qty = Convert.ToInt32(txtNum.Text);
                stock.price = Convert.ToDouble(txtPrice.Text);
                linq.SubmitChanges();
            }
            MessageBox.Show("商品信息修改成功");
            BindInfo();
        }

        #region 显示所有商品信息
        /// <summary>
        /// 显示所有商品信息
        /// </summary>
        private void BindInfo()
        {
            linq = new linqtosqlClassDataContext(strCon);//创建Linq连接对象
            //获取所有商品信息
            var result = from info in linq.tb_stock
                         select new
                         {
                             商品编号 = info.tradecode,
                             商品全称 = info.fullname,
                             商品型号 = info.type,
                             商品规格 = info.standard,
                             单位 = info.unit,
                             产地 = info.produce,
                             库存数量 = info.qty,
                             进货时的最后一次进价 = info.price,
                             加权平均价 = info.averageprice
                         };
            dgvInfo.DataSource = result;//对DataGridView控件进行数据绑定
        }
        #endregion

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void txtID_TextChanged(object sender, EventArgs e)
        {

        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void txtName_TextChanged(object sender, EventArgs e)
        {

        }

        private void cbox_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void label5_Click(object sender, EventArgs e)
        {

        }

        private void txtType_TextChanged(object sender, EventArgs e)
        {

        }

        private void label4_Click(object sender, EventArgs e)
        {

        }

        private void txtISBN_TextChanged(object sender, EventArgs e)
        {

        }

        private void label6_Click(object sender, EventArgs e)
        {

        }

        private void txtAddress_TextChanged(object sender, EventArgs e)
        {

        }

        private void label8_Click(object sender, EventArgs e)
        {

        }

        private void txtNum_TextChanged(object sender, EventArgs e)
        {

        }

        private void label7_Click(object sender, EventArgs e)
        {

        }

        private void txtPrice_TextChanged(object sender, EventArgs e)
        {

        }

        private void dgvInfo_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}