Linq To Sql 简记

Linq To Sql 简记

用于以前的.net framework

1. 引用System.Data.Linq
2. 创建数据库对应的实体类

using System;
using System.Collections.Generic;
using System.Data.Linq.Mapping; //要引入这个，下面的字段要对应大小写
using System.Linq;
using System.Web;
 
[Table(Name = "admin")]
public class Admin
{
    [Column(IsPrimaryKey = true,   IsDbGenerated = true)]
    public int Id { set; get; }
    [Column(Name ="createdate")]
    public DateTime Createdate { set; get; }
    [Column(Name = "username")]
    public string Username { set; get; }
    [Column(Name = "password")]
    public string Password { set; get; }
    [Column(Name = "remark")]
    public string Remark { set; get; }
}

 

3.增删查改，记得最后要SubmitChanges

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : Page
{
    DataContext db = new DataContext
     (@"server=.\sqlexpress;uid=sa;pwd=123456;database=niunan");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        { 
            binddata();
        }
    }

    private void binddata()
    {
        Table<Admin> admins = db.GetTable<Admin>();
        rep.DataSource = admins;
        rep.DataBind();
    }

    protected void btnDel_Click(object sender, EventArgs e)
    {
        try
        {
            Table<Admin> admins = db.GetTable<Admin>();

            int id = int.Parse((sender as Button).CommandArgument);
            Admin a1 = admins.Where(a => a.Id == id).FirstOrDefault();
            if (a1 == null)
            {
                throw new Exception("没有对应数据");
            }
            admins.DeleteOnSubmit(a1);
            db.SubmitChanges();
            Response.Write("删除【" + a1.Id + " " + a1.Username + "】成功");
            binddata();
        }
        catch (Exception ex)
        {
            Response.Write("删除失败：" + ex.Message);
        }
    }

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        try
        {
            Admin a = new Admin()
            {
                Username = "新用户" + new Random().Next(0, 1000),
                Password = "123456",
                Remark = "",
                Createdate = DateTime.Now
            };
            db.GetTable<Admin>().InsertOnSubmit(a);
            db.SubmitChanges();
            Response.Write("新增用户【" + a.Id + " +" + a.Username + "】成功");
            binddata();
        }
        catch (Exception ex)
        {
            Response.Write("新增失败：" + ex.Message);
        }
    
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        try
        {
            Table<Admin> admins = db.GetTable<Admin>();
            int id = int.Parse(txtid.Text.Trim());
            Admin a1 = admins.Where(a => a.Id == id).FirstOrDefault();
            if (a1 == null)
            {
                throw new Exception("没有对应数据");
            }
            a1.Username = txtusername.Text.Trim();
            a1.Remark = txtremark.Text.Trim();
            a1.Password = txtpassword.Text.Trim();  
            db.SubmitChanges();
            Response.Write("保存修改成功");
            binddata();
        }
        catch (Exception ex)
        {
            Response.Write("出错：" + ex.Message);
        }
    }

    protected void btnShowEdit_Click(object sender, EventArgs e)
    {
        try
        {
            Table<Admin> admins = db.GetTable<Admin>();
            int id = int.Parse((sender as Button).CommandArgument);
            Admin a1 = admins.Where(a => a.Id == id).FirstOrDefault();
            if (a1 == null)
            {
                throw new Exception("没有对应数据");
            }
            txtid.Text = a1.Id.ToString();
            txtusername.Text = a1.Username.ToString();
            txtpassword.Text = a1.Password.ToString();
            txtremark.Text = a1.Remark.ToString();

        }
        catch (Exception ex)
        {
            Response.Write("出错：" + ex.Message);
        }
    }
}