事务处理功能,以维护数据的一致性:
CREATE PROCEDURE dbo.delUserAll
as
set nocount on
begin
BEGIN TRANSACTION
delete from dbo.[userinfo]
IF @@error=0 --0是执行正常的返回值
begin
COMMIT TRANSACTION
return 1
end
ELSE
begin
ROLLBACK TRANSACTION--回滚
return 0
end
return
end
vs2005 取存储过程返回的多结果集:
SqlConnection sqlconn = new SqlConnection();
sqlconn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["xerp_xiaomangerConnectionString"].ToString();//这个是我在WEB.CONFIG里设置的。
sqlconn.Open();
SqlCommand sqlcomm = new SqlCommand();
sqlcomm.CommandType = CommandType.Text;//StoredProcedure,因为不知道怎么传参数,所以改为TEXT了。
sqlcomm.Connection = sqlconn;
sqlcomm.CommandText = " exec xxx '010'";
SqlDataAdapter sqlda = new SqlDataAdapter(sqlcomm);
DataSet ds = new DataSet();
sqlda.Fill(ds);
if (ds.Tables.Count > 0)
{
//Do sth ds.Tables[0]
Response.Write(ds.Tables[0].Rows[0][0]);
//Do sth ds.Tables[1]
Response.Write("<br />");
Response.Write(ds.Tables[1].Rows[0][0]);
Response.Write("<br />");
Response.Write(ds.Tables[1].Rows[0][1]);
Response.Write("<br />");
Response.Write(ds.Tables[1].Rows[0][2]);
}