ADO4——多表操作,同步更新
TABLES:
Titles id1 TitleName UserID UserName PostTime HitNum Reply
TitleDetail id2 TitleID(FK:id1) CommentTitle UserName PostTime IP Content
1>写存储过程
create procedure usp_AddNewTitle
@TitleName nvarchar (50),
@Content nvarchar(200),
@PostTime datetime,
@UserName nvarchar(50),
@TitleID int output
AS
declare @newid int
insert into Titles(TitleName,UserName,PostTime)values(@TitleName,@UserName,@PostTime);
select @newid=max(id1) from Titles
insert into TitleDetail(TitleID,UserName,PostTime,Content)values(@newid,@UserName,@PostTime,@Content)
select TitleID= @newid
2>在查询分析器中测试sp是否正确
declare @id int
exec usp_AddNewTitle 'abc','shshwrwh','2009-8-16','YC',@id output
3> 发帖函数
public void NewTitle(string title,string content, DateTime posttime ,ref int TitleID,ref string err)
{ SqlConnection cnx=................;
SqlCommand cmd=new SqlCommand(usp_AddNewTitle,cnx);
cmd.CommandType=CommandType.StoredProcedure;
//添参数,传值
cmd.Parameters.Add("@TitleName",.......,.......);
cmd.Parameters["@TitleName"].Value=title;
//依此类推................
//TitleID为存储过程的输出参数,另类传值如下:
cmd.Parameters.Add("@TitleID",........,.......);
cmd.Parameters["@TitleID"].Direction=Parameters.Direction.Output;
try
{ cnx.Open();
cnx.ExecuteNonQuery();
cnx.Close();
TitleID=Convert.ToInt32(cmd.Parameters["@TitleID"].Value.ToString());
err=" ";
}
catch(Exception ex)
{ err=ex.Message;}
}
4>调用操作
..........
int newid=-1;
string err=" ";
dac.NewTitle("111","12342356",DateTime.Now, "YC", ref newid, ref err);
Response.Write(newid);