存储过程与asp 操作数据库比较

 

用ASP.net加sql命令的方法: 

 using (SqlConnection sqlcnn = new SqlConnection(Class1.cnnstring))
        {
            using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
            {
                sqlcmm.CommandText = "insert into newsContent(title,[content],pubuser)values(@t,@c,@p);select @@identity";
                sqlcmm.Parameters.Add(new SqlParameter("@t", txtTitle.Text));
                Class2 user = Session["user"] as Class2;
                sqlcmm.Parameters.Add(new SqlParameter("@p", user.UserName));
                string content = Server.HtmlEncode(txtContent.Text).Replace(" ", "&nbsp;").Replace("\n", "<br/>");
                sqlcmm.Parameters.Add(new SqlParameter("@c", content));
                sqlcnn.Open();
                SqlTransaction trans = sqlcnn.BeginTransaction();
                sqlcmm.Transaction = trans;

                int i = Convert.ToInt32(sqlcmm.ExecuteScalar());
                Response.Write(i.ToString());
                sqlcmm.Parameters.Clear();
                sqlcmm.CommandText = "insert into NewsCategory(newsid,catid)values(@nid,@cid)";
                sqlcmm.Parameters.Add("@cid", System.Data.SqlDbType.Int);
                sqlcmm.Parameters.Add("@nid", i);
                foreach (ListItem item in CheckBoxList1.Items)
                {
                    if (item.Selected)
                    {
                        sqlcmm.Parameters[0].Value = item.Value;
                        sqlcmm.ExecuteNonQuery();
                    }
                }

                trans.Commit();
                try
                {
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                    trans.Rollback();
                }
            }

这是通过存储过程来操作的

 

 

CREATE PROCEDURE insertNews
 -- Add the parameters for the stored procedure here
 @title nvarchar(100),  --新闻标题
 @content TEXT,         --新闻内容
 @pubUser NVARCHAR(50), --发布人
 @catids varchar(200),  --新闻类别列表，用“:”分割
 @error nvarchar(200) OUTPUT  --用来返回错误信息
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 declare @newsid int  --新闻id
 declare @catid varchar(10) --新闻类别id
 declare @pos int  --类别列表中分隔符":"的位置
   
 begin transaction
 begin try
  insert into newscontent (title,[content],pubuser)values(@title,@content,@pubUser)
  set @newsid=@@identity  --获取刚写入的新闻的ID标识
  
  while(len(@catids) > 0)
  begin
   set @pos = charindex(':',@catids)
   if @pos <> 0
   begin
    set @catid=substring(@catids,1,@pos-1)
    set @catids = substring(@catids,@pos+1,len(@catids)-@pos)
   end
   else
   begin
    set @catid=@catids
    set @catids=''
   end
   insert into NewsCategory(newsid,catid)values(@newsid,cast(@catid as int))
  end
  commit transaction
  return 0  --表示写入成功
 END TRY  --end try和begin catch之间不能有其他语句
 begin catch
  set @error = error_message()
  rollback transaction
  return -1  --表示写入失败
 end catch
END
GO

 

个人认为得用存储过程要比得用asp +命令快一些，所以学习存储过程是非常重要的