视图：
USE [Train]
GO
/****** 对象: View [dbo].[BookDetails] 脚本日期: 01/04/2012 10:18:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER view [dbo].[BookDetails] as (select distinct users_traininfo_bookinfo.trainid,
seatname,userid,bookinfo.bookid,booktime,bookinfo.seatid,startstation,endstation,price
from users_traininfo_bookinfo,bookinfo,traininfo_seat,seat,sestation,traininfo_sestation_type_seat
where
bookinfo.bookid =users_traininfo_bookinfo.bookid
and users_traininfo_bookinfo.trainid=traininfo_seat.trainid
and bookinfo.seatid=traininfo_seat.seatid
and seat.seatid=bookinfo.seatid
and users_traininfo_bookinfo.trainid=traininfo_sestation_type_seat.trainid
and traininfo_sestation_type_seat.seid=sestation.seid
and users_traininfo_bookinfo.bookstate='1'
and bookinfo.bookid not in (select distinct bookinfo_reg.bookid from bookinfo_reg )
)
使用：
SqlConnection con = DB.createConnection();
con.Open();
sql = "select trainid,typename,starttime,endtime,startstation,endstation,seatname,price from Details where startstation='" + start + "'and endstation='" + end + "'and typename='" + style + "'";
SqlDataAdapter objCommand = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
objCommand.Fill(ds);
if (ds.Tables[0].Rows.Count == 0)
{
this.lbmess.Text = "对不起，目前没有你要查询的列车信息！！";
}
//对PagedDataSource 对象的相关属性赋值
PagedDataSource objPds = new PagedDataSource();
objPds.DataSource = ds.Tables[0].DefaultView;
//把PagedDataSource 对象赋给Repeater控件
this.dldetail.DataSource = objPds;
this.dldetail.DataBind();
con.Close();
存储过程：（传参并获取返回值）
过程定义:
USE [Train]
GO
/****** 对象: StoredProcedure [dbo].[BOOK] 脚本日期: 01/04/2012 10:23:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--//RETURN 0： 表示已经定了5张，不允许再订了；
--//RETURN 1： 表示已经没有客户选择类型的座位了；
--//RETURN 2： 表示订票成功；
--//////////////////////////////////////////////////////////
ALTER PROCEDURE [dbo].[BOOK] --订票存储过程
@P_TRAINID VARCHAR(5), --火车编号
@P_USERID VARCHAR(20), --用户编号
@P_SEATID VARCHAR(5) , --座位类型编号
@DESCRIP TEXT --描述
--////////////////////////////////////////////////////////////
AS
DECLARE @P_LEFTSEATNUM INT; --变量声明
DECLARE @P_NOWTIME DATETIME; --系统当前时间
DECLARE @P_HAVEDNUM INT;
--DECLARE @P_NOW DATETIME;
DECLARE @P_NOW VARCHAR(50);
--当前已订有效票的数量
SET @P_NOWTIME = GETDATE();
SELECT @P_NOW=CONVERT(VARCHAR(50), @P_NOWTIME,21);--类型转换
SELECT @P_NOW=REPLACE(@P_NOW,':','');
SELECT @P_NOW=REPLACE(@P_NOW,'-','');
SELECT @P_NOW=REPLACE(@P_NOW,'.','');
--计算有效票的数量，并赋值给@HAVEDNUM
SELECT @P_HAVEDNUM=COUNT(*)
FROM USERS_TRAININFO_BOOKINFO
WHERE USERS_TRAININFO_BOOKINFO.USERID = @P_USERID AND
USERS_TRAININFO_BOOKINFO.BOOKSTATE = '1' AND
USERS_TRAININFO_BOOKINFO.BOOKID NOT IN(
SELECT BOOKINFO_REG.BOOKID
FROM BOOKINFO_REG
)
IF @P_HAVEDNUM>=5
BEGIN
RETURN 0; --超过允许订票数量最大值
END
ELSE
BEGIN
SELECT @P_LEFTSEATNUM = TRAININFO_SEAT.LEFTSEATNUM
FROM TRAININFO_SEAT
WHERE TRAININFO_SEAT.TRAINID = @P_TRAINID AND TRAININFO_SEAT.SEATID = @P_SEATID;
IF @P_LEFTSEATNUM>0
BEGIN
UPDATE TRAININFO_SEAT
SET TRAININFO_SEAT.LEFTSEATNUM=TRAININFO_SEAT.LEFTSEATNUM-1
WHERE TRAININFO_SEAT.TRAINID = @P_TRAINID AND TRAININFO_SEAT.SEATID = @P_SEATID;
INSERT INTO BOOKINFO
VALUES(@P_NOW,@P_SEATID,'');
INSERT INTO USERS_TRAININFO_BOOKINFO
VALUES(@P_USERID,@P_TRAINID,@P_NOW,@P_NOWTIME,'1',@DESCRIP);
RETURN 2;
END --成功订票
ELSE
BEGIN
RETURN 1;
END --该座位类型的票售完
END
使用 实例代码：
int Index = ((GridViewRow)(myB.NamingContainer)).RowIndex;//获得行号
string zan = ((Label)GridView1.Rows[Index].Cells[0].FindControl("Lbooks")).Text.ToString();
SqlConnection con = DB.createConnection();
con.Open();
SqlCommand cmd = new SqlCommand("P_REG", con);
cmd.CommandType = CommandType.StoredProcedure;
//SqlParameter trainid = cmd.Parameters.Add("@BOOKID", SqlDbType.SmallDateTime);
SqlParameter trainid = cmd.Parameters.Add("@BOOKID", SqlDbType.NVarChar,50);
SqlParameter userid = cmd.Parameters.Add("@USERID", SqlDbType.NVarChar, 20);
SqlParameter des = cmd.Parameters.Add("@P_REGREASON", SqlDbType.NText, 100);
SqlParameter ret = cmd.Parameters.Add("@return", SqlDbType.Int);
cmd.Parameters["@BOOKID"].Direction = ParameterDirection.Input;
cmd.Parameters["@USERID"].Direction = ParameterDirection.Input;
cmd.Parameters["@P_REGREASON"].Direction = ParameterDirection.Input;
cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
// DateTime temt = new DateTime();
// ((Label)e.Rows[1].Cells[0].FindControl("Lbooks")).Text.ToString();
// string zan = this.GridView1.DataKeys.[e.RowIndex]Columns[1].TFindControl("Lbooks")).Text.ToStri
// zan= Convert.ToDateTime(zan).ToShortDateString();
// temt = DateTime.Parse(zan);
//cmd.Parameters["@BOOKID"].Value = temt;
// cmd.Parameters["@USERID"].Value = Session["LoginID"].ToString();
cmd.Parameters["@BOOKID"].Value = zan;
cmd.Parameters["@USERID"].Value = "01";
cmd.Parameters["@P_REGREASON"].Value = this.TBreason.Text;
cmd.ExecuteNonQuery();
int tem = (int)ret.Value;
if (tem == 1)
{
Response.Write(" <script language=JavaScript> if(confirm( '点击后跳转到退订记录？ ')) ; </script> ");
// Response.Write("<script > alert('退订成功！');</script>");
Response.Redirect("~/regnotes.aspx");
}
else
{
Response.Write("<script language=JavaScript>if(confirm( '退订失败！ ')) ;</script>");
Response.Redirect("~/regnotes.aspx");
}