步步为营VS 2008 + .NET 3.5(9) - DLINQ(LINQ to SQL)之执行SQL语句的添加、查询、更新和删除

[索引页]
[源码下载]


步步为营VS 2008 + .NET 3.5(9) - DLINQ(LINQ to SQL)之执行SQL语句的添加、查询、更新和删除


作者:webabcd


介绍
以Northwind为示例数据库,DLINQ(LINQ to SQL)之执行指定SQL语句的添加操作、查询操作、更新操作和删除操作


示例
SQL.aspx
<%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="SQL.aspx.cs"
    Inherits
="LINQ_DLINQ_SQL" Title="执行SQL语句的添加、查询、更新和删除" 
%>

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
    
<p>
        分类名称:
<asp:TextBox ID="txtCategoryName" runat="server"></asp:TextBox>
        
&nbsp;&nbsp; 分类描述:<asp:TextBox ID="txtDescription" runat="server"></asp:TextBox>
        
&nbsp;&nbsp;
        
<asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" />
    
</p>
    
<asp:GridView ID="gvCategory" runat="server" DataKeyNames="CategoryID" OnSelectedIndexChanged="gvCategory_SelectedIndexChanged"
        OnRowDeleting
="gvCategory_RowDeleting" OnRowCancelingEdit="gvCategory_RowCancelingEdit"
        OnRowEditing
="gvCategory_RowEditing" OnRowUpdating="gvCategory_RowUpdating">
        
<Columns>
            
<asp:CommandField ShowSelectButton="True" ShowEditButton="True" ShowDeleteButton="True">
            
</asp:CommandField>
        
</Columns>
    
</asp:GridView>
    
<br />
    
<asp:DetailsView ID="dvProduct" runat="server" DataKeyNames="ProductID">
    
</asp:DetailsView>
</asp:Content>

SQL.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;

using DAL;

public partial class LINQ_DLINQ_SQL : System.Web.UI.Page
{
    
// 实例化一个NorthwindDataContext(DataContext)
    NorthwindDataContext _ctx = new NorthwindDataContext();

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

    }


    
private void BindCategory()
    
{
        
// 使用NorthwindDataContext对象的ExecuteQuery<T>()方法,通过指定的SQL语句返回指定对象的集合
        var categories = _ctx.ExecuteQuery<Categories>(@"select * from categories");

        gvCategory.DataSource 
= categories;
        gvCategory.DataBind();
    }


    
protected void btnAdd_Click(object sender, EventArgs e)
    
{
        
// 使用NorthwindDataContext对象的ExecuteCommand()方法,执行指定的SQL语句
        _ctx.ExecuteCommand(@"insert into categories (categoryname, description) values ({0}, {1})",
                            txtCategoryName.Text,
                            txtDescription.Text);

        gvCategory.EditIndex 
= -1;
        BindCategory();
    }


    
protected void gvCategory_SelectedIndexChanged(object sender, EventArgs e)
    
{
        
// 使用NorthwindDataContext对象的ExecuteQuery<T>()方法,通过指定的SQL语句返回指定对象的集合
        var products = _ctx.ExecuteQuery<Products>(@"select * from products where productid={0}", gvCategory.SelectedValue);

        dvProduct.DataSource 
= products;
        dvProduct.DataBind();
    }


    
protected void gvCategory_RowDeleting(object sender, GridViewDeleteEventArgs e)
    
{
        
// 使用NorthwindDataContext对象的ExecuteCommand()方法,执行指定的SQL语句(调用存储过程)
        _ctx.ExecuteCommand(@"exec sp_DeleteCategory {0}", gvCategory.DataKeys[e.RowIndex].Value);

        gvCategory.EditIndex 
= -1;
        BindCategory();
    }


    
protected void gvCategory_RowUpdating(object sender, GridViewUpdateEventArgs e)
    
{
        
// 使用NorthwindDataContext对象的ExecuteCommand()方法,执行指定的SQL语句
        _ctx.ExecuteCommand(@"update categories set categoryname={0}, description={1} where categoryid={2}",
                            ((TextBox)gvCategory.Rows[e.RowIndex].Cells[
2].Controls[0]).Text,
                            ((TextBox)gvCategory.Rows[e.RowIndex].Cells[
3].Controls[0]).Text,
                            gvCategory.DataKeys[e.RowIndex].Value);

        gvCategory.EditIndex 
= -1;
        BindCategory();
    }


    
protected void gvCategory_RowEditing(object sender, GridViewEditEventArgs e)
    
{
        gvCategory.EditIndex 
= e.NewEditIndex;
        BindCategory();
    }


    
protected void gvCategory_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    
{
        gvCategory.EditIndex 
= -1;
        BindCategory();
    }

}



OK
[源码下载]
posted @ 2007-10-19 08:11  webabcd  阅读(9298)  评论(13编辑  收藏  举报