LINQ to SQL 插入、删除和更新操作

17 Mar 2025 | 4 分钟阅读

LINQ to SQL 具有维护我们对对象所做的更改的功能，例如在对象集合中添加、删除或更新项目，直到我们使用 SubmitChanges() 方法 提交更改。提交更改后，LINQ to SQL 将把我们的操作转换为 SQL 并将更改提交到数据库。

LINQ to SQL 插入/更新和删除的语法

这是使用 LINQ to SQL 的插入/更新或删除操作的语法。

LINQ to SQL 插入操作

以下是使用 LINQ to SQL 插入操作查询在 C# 中插入数据库数据的语法。

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 删除操作

以下是使用 LINQ to SQL 删除操作在 C# 中删除数据库数据的语法。

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 的选择、插入、更新和删除操作。现在我们将运行应用程序并查看结果。