c# linq to sql 的基本操作

最新推荐文章于 2023-08-06 12:39:16 发布

曹振华

最新推荐文章于 2023-08-06 12:39:16 发布

阅读量1.3k

点赞数

分类专栏： c# 文章标签： c# linq

本文链接：https://blog.csdn.net/chadcao/article/details/5989384

版权

c# 专栏收录该内容

82 篇文章 0 订阅

订阅专栏

1）创建数据库School,数据表Student,表中的字段id、name。

2）右键点击网站项目，选择添加新项，然后选择“Linq to sql Classes”命名为School，然后打开App_Code里面的School.dbml，设计视图上的文字提示你可以从服务器资源管理器或者攻击箱拖动项到设计界面上来创建实体类。

3）在服务器资源管理器中创建一个指向School数据库的数据连接，然后把Student表拖动到School.dbml的设计视图上，按CTRL+S保存,打开School.designer.cs可以发现系统自动创建了School数据库中Student表的映射。

4）现在，我们就可以使用Linq to sql完成简易增、删、改、查实现以下功能代码如下：

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.Linq" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Linq" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
    SchoolDataContext ctx = new SchoolDataContext("server=.;database=School;uid=sa;pwd=123456");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            btnEdit.Enabled = false;
            BindStudents();
        }
    }
    //绑定学生信息
    private void BindStudents()
    {
        //以日志形式记录DataContext生成的SQL
        StreamWriter sw = new StreamWriter(Server.MapPath("sql.txt"), true);
        ctx.Log = sw;
        //获取全部学生的信息作为数据源和Repeater绑定
        Table<Student> students = ctx.GetTable<Student>();
        rptStudent.DataSource = from student in students  select student;
        rptStudent.DataBind();
        sw.Close();
    }
    //清空控件信息
    private void ClearInfo()
    {
        txtName.Text = string.Empty;
        txtEditName.Text = string.Empty;
        lblId.Text = string.Empty;
    }
    //添加学生信息
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        Student student = GetStudent();//获取输入学生信息
        if (student.name != "")
        {
            try
            {
                //添加学生信息
                ctx.Student.InsertOnSubmit(student);
                ctx.SubmitChanges();
                Page.ClientScript.RegisterStartupScript(GetType(), "key", "alert('添加学生成功!')", true);
            }
            catch (Exception ex)
            {
                DoLog(ex.ToString());//调用工作日志
            }
        }
        ClearInfo();//清空控件信息
        BindStudents();
    }
    //获取输入的学生信息
    private Student GetStudent()
    {
        Student student = new Student();
        student.id = Guid.NewGuid();
        student.name = txtName.Text.Trim().ToString();
        if (student.name == "")//学生姓名为空
        {
            Page.ClientScript.RegisterStartupScript(GetType(), "key", "alert('请输入姓名!')", true);
        }
        return student;
    }
    //生成工作日志
    private void DoLog(string message)
    {
        StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"),true);
        sw.WriteLine("{0} {1}", System.DateTime.Now, message);
        sw.Close();
    }
    
    protected void rptStudent_ItemCommand(object source, RepeaterCommandEventArgs e)
    {
        
        if (e.CommandName == "DelStudent")//删除学生信息
        {
            try
            {
                //根据学生的id获取学生
                Student student = ctx.Student.SingleOrDefault(s => s.id == new Guid(e.CommandArgument.ToString()));
                //删除学生
                ctx.Student.DeleteOnSubmit(student);
                ctx.SubmitChanges();
                Page.ClientScript.RegisterStartupScript(GetType(), "key", "alert('删除学生成功!')", true);
            }
            catch (Exception ex)
            {
                DoLog(ex.ToString());//调用工作日志
                Page.ClientScript.RegisterStartupScript(GetType(), "key", "alert('该学生已不存在!')", true);
            }
            BindStudents();
        }
        if (e.CommandName == "EditStudent")//修改学生信息
        {
            lblId.Text =  e.CommandArgument.ToString();
            btnEdit.Enabled=true;
        }
        if (e.CommandName == "SelStudent")//根据学生的学好获取学生姓名
        {
            //根据学生的id获取学生
            Student student = ctx.Student.SingleOrDefault(s => s.id == new Guid(e.CommandArgument.ToString()));
            lblGetName.Text = student.name.ToString();
        }
    }
    //修改学生信息
    protected void btnEdit_Click(object sender, EventArgs e)
    {
        try
        {
            //根据学生的id获取学生
            Student student = ctx.Student.SingleOrDefault(s => s.id == new Guid(lblId.Text.ToString()));
            student.name = txtEditName.Text.ToString();//输入修改的数据
            ctx.SubmitChanges();//修改学生信息
            Page.ClientScript.RegisterStartupScript(GetType(), "key", "alert('修改学生成功!')", true);
        }
        catch (Exception ex)
        {
            DoLog(ex.ToString());//调用工作日志
            Page.ClientScript.RegisterStartupScript(GetType(), "key", "alert('修改学生失败!')", true);          
        }
        BindStudents();
        ClearInfo();//清空控件信息
        btnEdit.Enabled = false;
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        姓名:<asp:TextBox ID="txtName" runat="server"></asp:TextBox><br />
        <asp:Button ID="btnSubmit" runat="server" Text="提交" onclick="btnSubmit_Click" />
        <br />
        <asp:Repeater ID="rptStudent" runat="server" 
            onitemcommand="rptStudent_ItemCommand">
            <ItemTemplate>
                <table>
                    <tr>
                        <td>
                            <asp:LinkButton ID="lbtnSelect" runat="server" Text='<%#Eval("id") %>'
                            CommandName="SelStudent" CommandArgument='<%#Eval("id") %>'/>
                        </td>
                        <td><%#Eval("name") %></td>
                        <td>
                            <asp:LinkButton ID="lbtnDelStudent" runat="server" Text="删除"
                            CommandName="DelStudent" CommandArgument='<%#Eval("id") %>'/>
                        </td>
                        <td>
                            <asp:LinkButton ID="lbtnEditStudent" runat="server" Text="修改"
                            CommandName="EditStudent" CommandArgument='<%#Eval("id") %>'/>
                        </td>
                    </tr>
                </table>
            </ItemTemplate>
        </asp:Repeater>
    </div>
    学号:<asp:Label ID="lblId" runat="server"></asp:Label><br />
    姓名:<asp:TextBox ID="txtEditName" runat="server"></asp:TextBox><br />
    <asp:Button ID="btnEdit" runat="server" Text="修改" onclick="btnEdit_Click" />
    <br />
    <asp:Label ID="lblGetName" runat="server"></asp:Label>
    </form>
</body>
</html>