使用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 LINQUpdataSql
{
    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 btnAdd_Click(object sender, EventArgs e)
        {
            linq = new linqtosqlClassDataContext(strCon);    //创建Linq连接对象
            tb_stock stock = new tb_stock();   //创建tb_stock类对象
            //为tb_stock类中的商品实体赋值
            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.tb_stock.InsertOnSubmit(stock);  //添加商品信息
            linq.SubmitChanges(); //提交操作
            MessageBox.Show("数据添加成功");
            BindInfo();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            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 label3_Click(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)
        {

        }
    }
}