学习笔记4（Linq实现增删改查详解）

源代码下载地址：http://download.csdn.net/source/3469012

首先创建一个不能再简单的数据库

use master
go

if exists(select * from sysdatabases where name='_user')
drop database _user
go

create database _user
go

use _user
go

create table _userinfo
(
_id int identity(1,1) primary key,
_username varchar(50) not null,
_password varchar(50) not null
)
go

insert _userinfo values('test1','test1')
insert _userinfo values('test2','test2')
insert _userinfo values('test3','test3')
insert _userinfo values('test4','test4')
insert _userinfo values('test5','test5')
go

select * from _userinfo

然后创建项目，使用linq链接数据库

命名：DB.dbml

然后创建类DBM.cs用来封装DBDataContext

public class DBM
{
public static DBDataContext db
{
get
{
return new DBDataContext();
}
}
}

再然后创建UserinfoDAL类

public class UserinfoDAL
{
//增
public static bool Add(_userinfo u)
{
try
{
var db = DBM.db;
db._userinfo.InsertOnSubmit(u);
db.SubmitChanges();
return true;
}
catch
{
return false;
}
}
//删
public static bool Del(int id)
{
try
{
var db = DBM.db;
_userinfo u = db._userinfo.Single(d => d._id == id);
db._userinfo.DeleteOnSubmit(u);
db.SubmitChanges();
return true;
}
catch
{
return false;
}
}
//改
public static bool Upd(_userinfo u)
{
try
{
var db = DBM.db;
_userinfo user = db._userinfo.Single(d => d._id == u._id);
user._username = u._username;
user._password = u._password;
db.SubmitChanges();
return true;
}
catch
{
return false;
}
}
//查
public static List<_userinfo> FindAll()
{
return DBM.db._userinfo.ToList();
}
}

最后就是表示层了

页面：LinqToSql.aspx

两个panel

一个放gridview显示

一个是添加的

后台代码：

public partial class LinqToSql : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
BD();
}
}

private void BD()
{
List<_userinfo> lst = UserinfoDAL.FindAll();
GridView1.DataSource = lst;
GridView1.DataBind();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);
if (UserinfoDAL.Del(id))
{
msg.Text = "删除成功！";
BD();
}
else
{
msg.Text = "删除失败！";
}

}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BD();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BD();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
_userinfo u = new _userinfo();
u._id = Convert.ToInt32(GridView1.DataKeys[GridView1.EditIndex].Value);
u._username = (GridView1.Rows[GridView1.EditIndex].Cells[1].Controls[0] as TextBox).Text;
u._password = (GridView1.Rows[GridView1.EditIndex].Cells[2].Controls[0] as TextBox).Text;
if (UserinfoDAL.Upd(u))
{
GridView1.EditIndex = -1;
BD();
msg.Text = "修改成功！";
}
else
{
msg.Text = "修改失败！";
}
}
protected void LinkButton1_Click(object sender, EventArgs e)
{
Panel1.Visible = false;
Panel2.Visible = true;
}
protected void Button2_Click(object sender, EventArgs e)
{
Panel2.Visible = false;
Panel1.Visible = true;
BD();
}
protected void Button1_Click(object sender, EventArgs e)
{
_userinfo u = new _userinfo();
u._username = TextBox1.Text;
u._password = TextBox2.Text;
if (UserinfoDAL.Add(u))
{
TextBox1.Text = "";
TextBox2.Text = "";
msg2.Text = "增加成功！";
}
else
{
msg2.Text = "增加失败！";
}
}
}