利用存储过程往数据库中插入记录

来源：互联网发布：桥接模式下centos联网编辑：程序博客网时间：2024/06/05 03:21

数据库存储过程代码

CREATE PROC Pro_GetMaxId1
@newstitle VARCHAR(64),
@newscontent VARCHAR(max),
@newscreator VARCHAR(8),
@createtime DATETIME=NULL,
@classname VARCHAR(8),
@maxid INT OUT
AS
--根据类别名称查询出类别编号
DECLARE @classid uniqueidentifier;
SELECT @classid=T1.ClassId FROM T_NewsClass T1 WHERE T1.ClassName=@classname;

IF @createtime IS NULL
BEGIN
  INSERT INTO T_News(NewsTitle,NewsContent,NewsCreator,CreateTime,ClassId)
  VALUES
  (@newstitle,@newscontent,@newscreator,GETDATE(),@classid);
END
ELSE
BEGIN
INSERT INTO T_News(NewsTitle,NewsContent,NewsCreator,CreateTime,ClassId)
VALUES
(@newstitle,@newscontent,@newscreator,@createtime,@classid);
END
SET @maxid=@@IDENTITY;
GO
--执行存储过程
DECLARE @maxid INT;
EXEC Pro_GetMaxId1 '222','222222','004',NULL,'军事',@maxid OUT;
PRINT @maxid;
GO

前台代码

后台代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace 新闻系统
{
    public partial class cunchuguocheng2 : System.Web.UI.Page
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                using (SqlConnection conn = new SqlConnection(constr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "select ClassId,ClassName from T_NewsClass";
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            adapter.Fill(dt);
                            ddlClassName.DataSource = dt;
                            ddlClassName.DataTextField = "ClassName";
                            ddlClassName.DataValueField = "ClassId";
                            ddlClassName.DataBind();
                        }
                    }
                }
                using (SqlConnection conn = new SqlConnection(constr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "select UserId,RealName from T_User";
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            adapter.Fill(dt);
                            ddlNewsCreator.DataSource = dt;
                            ddlNewsCreator.DataTextField = "RealName";
                            ddlNewsCreator.DataValueField = "UserId";
                            ddlNewsCreator.DataBind();
                        }
                    }
                }
            }

        }

        protected void btnupdate_Click(object sender, EventArgs e)
        {
            string newstitle = txtNewsTitle.Text;
            string newscontent = txtNewsContent.Text;
            string classid = ddlClassName.SelectedItem.Value;

            string classname = ddlClassName.SelectedItem.Text;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "Pro_GetMaxId1";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@maxid", SqlDbType.Int);
                    cmd.Parameters["@maxid"].Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(new SqlParameter("@newstitle", newstitle));
                    cmd.Parameters.Add(new SqlParameter("@newscontent", newscontent));
                    cmd.Parameters.Add(new SqlParameter("@newscreator", userid));
                    cmd.Parameters.Add(new SqlParameter("@createtime", Calendar1.SelectedDate));

                    cmd.Parameters.Add(new SqlParameter("@classname", classname));
                    cmd.ExecuteNonQuery();
                    Response.Write(cmd.Parameters["@maxid"].Value.ToString());
                }
            }
        }
    }
}