Linq to sql之简单应用
普通方式之增删改查,如:
View Code
public partial class Index : System.Web.UI.Page
{
Linq.Linq_I_TableDataContext Li;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetBind();
GetId();
}
}
//添加数据
protected void Button1_Click(object sender, EventArgs e)
{
if (Request["id"] != null)
{
UpdateMessage();
}
else
{
Li = new Linq.Linq_I_TableDataContext();
//实例化一个表,并绑定数据
Linq.I_Table II = new Linq.I_Table();
II.I_Type = TypeText.Value;
II.I_Fund = FundText.Value;
Li.I_Table.InsertOnSubmit(II);
Li.SubmitChanges();
GetBind();
}
}
private void GetBind()
{
GridView1.DataSource = GetData();
GridView1.DataBind();
}
//获取Gridview内容
public IQueryable GetData()
{
Li = new Linq.Linq_I_TableDataContext();
var Data = from I_Table in Li.I_Table orderby I_Table.ID descending select I_Table;
var ReData = Data.Skip(0).Take(10);
return ReData;
}
//获取编辑内容
private void GetId()
{
if (Request["id"] != null)
{
Li = new Linq.Linq_I_TableDataContext();
Linq.I_Table IT = Li.I_Table.Single(n => n.ID == Convert.ToInt32(Request["id"]));
TypeText.Value = IT.I_Type;
FundText.Value = IT.I_Fund;
}
}
//修改信息
private void UpdateMessage()
{
Li = new Linq.Linq_I_TableDataContext();
Linq.I_Table II = Li.I_Table.Single(n => n.ID == Convert.ToInt32(Request["id"]));
II.I_Type = TypeText.Value;
II.I_Fund = FundText.Value;
Li.SubmitChanges();
GetBind();
}
//删除一行
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
Li = new Linq.Linq_I_TableDataContext();
//lll.ObjectTrackingEnabled = false;
if (e.CommandName.Equals("del"))
{
int id = Convert.ToInt32(e.CommandArgument);
//从表中取出这一行数据
Linq.I_Table II = Li.I_Table.Single(n => n.ID == id);
Li.I_Table.DeleteOnSubmit(II);
Li.SubmitChanges();
}
GetBind();
}
//输出5条数据
public string OutData()
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
Li = new Linq.Linq_I_TableDataContext();
var a = from I_Table in Li.I_Table orderby I_Table.ID descending select I_Table;
var c = a.Skip(0).Take(5);
foreach (var b in c)
{
sb.Append("<li>"+b.I_Type+"</li>");
}
return sb.ToString();
}
}
LINQ调用存储过程 增删改查,如:
剩下的如方法一样调用,可以有返回值(两种)。
LINQ判断存储过程的返回值,如:
1,删除的存储过程如下:
View Code
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--Return返回值
--ALTER proc [dbo].[DeleteWithParam]
--@id int
--as
--if exists(select id from dbo.I_Table where id=@id)
--begin
--delete from I_Table where id=@id
--return 1
--end
--else
--return 0
--output返回值
ALTER proc [dbo].[DeleteWithParam]
@id int,
@Outp int output
as
if exists(select id from dbo.I_Table where id=@id)
begin
delete from I_Table where id=@id
set @Outp=1
end
else
set @Outp=0
LINQ调用代码如下:
View Code
//Return返回值,1为正确,0为错误
Li = new Linq.Linq_I_TableDataContext();
// int aa = Li.DeleteWithParam(10);
//output返回值,直接判断output的值
int? aa = null;
Li.DeleteWithParam1(12, ref aa);
string bb = aa.ToString();
2,插入的存储过程如下:
@@identity 为新插入的ID值,成功返回ID值,不成功为NULL
View Code
create proc InsertReturn
@I_Type nvarchar(50),
@OP bigint output
as
insert into I_Table(I_Type) values(@I_Type)
set @OP=@@identity
LINQ调用代码如下:
View Code
Li = new Linq.Linq_I_TableDataContext();
long? bb = null;
Li.InsertReturn("2", ref bb);
string aa = bb.ToString();
3,修改的存储过程如下:
View Code
create proc UpdateReturn
@I_Type nvarchar(50),
@id int,
@OP bigint output
as
update dbo.I_Table set I_Type=@I_Type where id=@id
set @OP=@@rowcount
--@@rowcount判断影响的行数
LINQ调用代码如下:
View Code
Li = new Linq.Linq_I_TableDataContext();
long? bb = null;
Li.UpdateReturn("11", 13, ref bb);
string aa = bb.ToString();