存储过程练习

最新推荐文章于 2024-05-16 11:13:53 发布

wangjingjing1014

最新推荐文章于 2024-05-16 11:13:53 发布

阅读量1k

点赞数

本文链接：https://blog.csdn.net/wangjingjing1014/article/details/8660325

版权

存储过程练习
--创建存储过程
create proc Pro_GetNews（存储过程的名字）
----调用存储过程
exec Pro_GetNews
--包含输入参数的存储过程
CREATE PROC Pro_GetNewsByTitle
@newstitele varchar(50)
as
select NewsTitle,substring(NewsContent,1,20),CreateTime from T_News where NewsTitle like
@newitle
--新建存储过程，向表中插入一条数据，并返回自动生成的最大号
create proc proc_Getmaxid
@maxid int output
as
insert into T_News（NewsTitle，NewsContent，NewsCreator,CreateTime,ClassId）values('本周全国大部分地区降温','本周全国大部分地区降温，最高降温达°','003',GETDATE(),'39ec3206-4467-44b4-8fa1-711443eb4ce5');
set @maxid=@@identity;
go
declare @maxid int;
EXEC proc_Getmaxid @maxid OUT;
PRINT @maxid;

------------接受用户输入的参数,插入数据,返回最大编号
CREATE PROC Pro_GetMaxId2
@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_GetMaxId2 '222','11111111','005',null,'娱乐',@maxid OUT;
PRINT @maxid;
GO

--在ADO中调用存储过程

--这是WebList.的前台

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebList.aspx.cs" Inherits="新闻管理系统.WebList" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtTitle" runat="server"></asp:TextBox>
<asp:Button ID="BtnSearch"
runat="server" Text="搜索" οnclick="BtnSearch_Click" />
<div id="divResult" runat="server"></div>
</div>
</form>
</body>
</html>

--这是这是WebList.的后台代码

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

namespace 新闻管理系统
{
public partial class WebList : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataLoad();
}
}
private void DataLoad()
{
string Constr = "data source=LOVE-PC\\SQLEXPRESSPC;initial catalog=News;user id=sa;password=admin";

#region 方法2
using (SqlConnection conn = new SqlConnection(Constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
/*调用存储过程*/
//cmd.CommandText = "Pro_GetNews";
//cmd.CommandType = CommandType.StoredProcedure;

//cmd.CommandText = "Pro_GetNewsByTitle";
//cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Pro_GetNewsTitleAddContent";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@newkey","%" + txtTitle.Text + "%");

SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
cmd.Dispose();
conn.Dispose();
//将数据拼接成字符串发送到前台
StringBuilder sb1 = new StringBuilder();
string newstitle = string.Empty;
string newscontent = string.Empty;
string createtime = string.Empty;
sb1.Append("<table border=1>");
sb1.Append("<tr><td>新闻标题</td><td>新闻内容</td><td>创建时间</td></tr>");
foreach (DataRow row in dt.Rows)
{
sb1.Append("<tr>");
newstitle = row["NewsTitle"].ToString();
sb1.Append("<td>" + newstitle + "</td>");
newscontent=row["NewsContent"].ToString();
sb1.Append("<td>"+newscontent+"</td>");
createtime= Convert.ToDateTime( row["CreateTime"]).ToString("yy-MM-dd hh:mm:ss");
sb1.Append("<td>"+createtime+"</td>");
sb1.Append("</tr>");
}
sb1.Append("</table>");
divResult.InnerHtml = sb1.ToString();
}

}
#endregion

}

protected void BtnSearch_Click(object sender, EventArgs e)
{
DataLoad();
}
}
}