C#Winform Linq_to_Sql 实现增删改查功能

PS:

Linq_to_Sql数据查询语言为强数据类型的语言，操作的基本单位与DataSet一样都是以数据实体，所以操作数据前所有要更新的数据值都要与实体的数据类型相同。如下例中为price和typeids赋值

1.按条件查询表Room绑定到DataGridView

//按条件查询表Room绑定到DataGridView
        protected void LoadData()
        {
            string roomid = this.txtRoomid.Text.Trim().ToString();
            string price_start = this.txtPrice_Start.Text.Trim().ToString();
            string price_end = this.txtPrice_End.Text.Trim().ToString();


            var r = from n in ctx.rooms select n;

            //查询条件，逐个筛选，范围越来越小
            if (roomid.Length>0)
            {
                r = from n in ctx.rooms where n.roomid == roomid select n;
            }
            if (price_start.Length > 0)
            {
                r = from n in ctx.rooms where n.price >= decimal.Parse(price_start) select n;
            }
            if (price_end.Length > 0)
            {
                r = from n in ctx.rooms where n.price <= decimal.Parse(price_end) select n;
            }

            this.dataGridView1.DataSource=r; //绑定数据到DataGridView
            this.dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.LightBlue;
        
        }

2.添加数据到room表

  //添加数据到room表
        private void btnAdd_Click(object sender, EventArgs e)
        {
            room r = new room();//实例化room表  --因为Linq作为强数据类型的语言，操作数据都是以实体为单位

            //各个字段进行复制
            r.roomid = this.txtRoomid.Text.Trim().ToString();
            r.price = decimal.Parse(this.txtPrice.Text.Trim().ToString());
            r.typeids = int.Parse(this.txtTypeids.Text.Trim().ToString());
            r.status = 0;
            r.isdeleted = 0;

            ctx.rooms.InsertOnSubmit(r); //执行Insert方法
            ctx.SubmitChanges();        //这一步很重要，保存所做的操作

            this.refresh();
        }

3.修改room表数据

  private void btnUpdate_Click(object sender, EventArgs e)
        {
            room r = ctx.rooms.Single(room => room.roomid == gl_roomid);  //难点，得到room表中roomid=gl_roomid的room实体，即要更新的数据行

            //更新数据赋值
            r.roomid = this.txtRoomid.Text.Trim().ToString();
            r.typeids =int.Parse( this.txtTypeids.Text.Trim().ToString());
            r.price = decimal.Parse(this.txtPrice.Text.Trim().ToString());

            ctx.SubmitChanges();        //这一步很重要，保存所做的操作
            this.refresh();

        }

4.删除room表中数据

  private void butDelete_Click(object sender, EventArgs e)
        {
            if (gl_roomid.Length==0)
            {
                MessageBox.Show("Please select the rows!");
                return;
            }
           
            room r = ctx.rooms.Single(room => room.roomid == gl_roomid); //获取要删除的roomid为gl_roomid的room实体
            
            ctx.rooms.DeleteOnSubmit(r);        //执行删除操作
            ctx.SubmitChanges();                //保存更改
            this.LoadData();                    //再次加载数据
        }