本文概述
LINQ to SQL具有维护更改的功能, 无论我们对对象执行什么操作, 例如添加, 删除或更新对象集合中的项目, 直到我们通过使用SubmitChanges()方法提交更改为止。提交更改后, LINQ to SQL将我们的操作转换为SQL并将更改提交到数据库。
LINQ to SQL插入/更新和删除的语法
这是在LINQ to SQL中使用的插入/更新或删除操作的语法。
LINQ to SQL插入操作
这是在C＃中使用LINQ to SQL插入操作查询在数据库中插入数据的语法。
EmployeeDBDataContext db = new EmployeeDBDataContext();
EmployeeDetail emp = new EmployeeDetail();
emp.EmpName = txtname.Text;
emp.Location = txtlocation.Text;
db.EmployeeDetails.InsertOnSubmit(emp);
db.SubmitChanges();LINQ to SQL更新操作
这是使用LINQ to SQL更新操作查询来更新数据库中数据的语法。
EmployeeDBDataContext db = new EmployeeDBDataContext();
EmployeeDetail emp = new EmployeeDetail();
emp = db.EmployeeDetails.Single(x => x.EmpId == empid);
emp.EmpName = txtname.Text;
emp.Location = txtlocation.Text;
emp.Gender = txtgender.Text;
db.SubmitChanges();LINQ to SQL删除操作
这是在C＃中使用LINQ to SQL删除操作删除数据库中数据的语法。
EmployeeDBDataContext db = new EmployeeDBDataContext();
EmployeeDetail emp = new EmployeeDetail();
emp = db.EmployeeDetails.Single(x => x.EmpId == empid);
db.EmployeeDetails.DeleteOnSubmit(emp);
db.SubmitChanges();LINQ to SQL插入, 更新, 删除操作
在我们开始实施LINQ to SQL的插入, 更新和删除操作之前, 首先需要创建一个具有所需表的数据库, 并将这些表映射到LINQ to SQL文件(.dbml)。
现在创建所需表并将其映射到.dbml文件后, 我们想在应用程序中显示数据。为此, 我们将右键单击应用程序->选择添加->新建项目->选择Web表单->给定名称为Default.aspx, 然后单击确定按钮。
现在打开Default.aspx页面并编写如下代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div class="GridviewDiv">
<asp:GridView runat="server" ID="gvDetails" ShowFooter="true" AllowPaging="true" PageSize="10" AutoGenerateColumns="false" DataKeyNames="Id, Name" OnPageIndexChanging="gvDetails_PageIndexChanging" OnRowCancelingEdit="gvDetails_RowCancelingEdit"
OnRowEditing="gvDetails_RowEditing" OnRowUpdating="gvDetails_RowUpdating" OnRowDeleting="gvDetails_RowDeleting" OnRowCommand ="gvDetails_RowCommand" >
<HeaderStyle CssClass="headerstyle" />
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="true" />
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblName" runat="server" Text='<%# Eval("Name")%>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Name")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfName" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText = "Location">
<ItemTemplate>
<asp:Label ID="lblLocation" runat="server" Text='<%# Eval("Location")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtLocation" runat="server" Text='<%# Eval("Location")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfLocation" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Gender">
<ItemTemplate>
<asp:Label ID="lblGender" runat="server" Text='<%# Eval("Gender")%>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtGender" runat="server" Text='<%# Eval("Gender")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfGender" runat="server" />
<asp:Button ID="btnAdd" CommandName="AddNew" runat="server" Text="Add" />
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" ShowDeleteButton="true" />
</Columns>
</asp:GridView>
<asp:Label ID="lblresult" runat="server"></asp:Label>
</div>
</form>
</body>
</html>现在打开文件后面的代码并编写代码, 如下所示：
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
EmployeeDataContext db = new EmployeeDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGridview();
}
}
protected void BindGridview()
{
var result = from ed in db.EmployeeDetails
select new
{
Id = ed.Id, Name = ed.Name, Location = ed.Location, Gender = ed.Gender
};
gvDetails.DataSource = result;
gvDetails.DataBind();
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtname = (TextBox)gvDetails.FooterRow.FindControl("txtfName");
TextBox txtlocation = (TextBox)gvDetails.FooterRow.FindControl("txtfLocation");
TextBox txtgender = (TextBox)gvDetails.FooterRow.FindControl("txtfGender");
EmployeeDetail emp = new EmployeeDetail();
emp.Name = txtname.Text;
emp.Location = txtlocation.Text;
emp.Gender = txtgender.Text;
db.EmployeeDetails.InsertOnSubmit(emp);
db.SubmitChanges();
lblresult.ForeColor = Color.Green;
lblresult.Text = txtname.Text + " details inserted successfully";
BindGridview();
}
}
protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
gvDetails.EditIndex = e.NewEditIndex;
BindGridview();
}
protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvDetails.EditIndex = -1;
BindGridview();
}
protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvDetails.PageIndex = e.NewPageIndex;
BindGridview();
}
protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int empid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["Id"].ToString());
TextBox txtname = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtName");
TextBox txtlocation = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtLocation");
TextBox txtgender = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtGender");
EmployeeDetail emp = new EmployeeDetail();
emp = db.EmployeeDetails.Single(x => x.Id == empid);
emp.Name = txtname.Text;
emp.Location = txtlocation.Text;
emp.Gender = txtgender.Text;
db.SubmitChanges();
gvDetails.EditIndex = -1;
BindGridview();
lblresult.ForeColor = Color.Green;
lblresult.Text = txtname.Text + " details updated successfully";
}
protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int empid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["Id"].ToString());
string empname = gvDetails.DataKeys[e.RowIndex].Values["Name"].ToString();
EmployeeDetail emp = new EmployeeDetail();
emp = db.EmployeeDetails.Single(x => x.Id == empid);
db.EmployeeDetails.DeleteOnSubmit(emp);
db.SubmitChanges();
BindGridview();
lblresult.ForeColor = Color.Green;
lblresult.Text = empname + " details deleted successfully";
}
}在以上示例中, 我们正在实现LINQ to SQL的选择, 插入以及更新, 删除操作。现在, 我们将运行该应用程序并查看结果。
