新建存储过程
CREATE PROC Pro_GetNews
AS
SELECT * FROM T_News;
GO
执行存储过程
EXEC Pro_GetNews
利用存储过程添加数据
--参数名称一定要加@前缀
CREATE PROC Pro_GetNewsByTitle
@newstitle VARCHAR(64)
AS
SELECT NewsTitle,SUBSTRING(NewsContent,1,20)+'....' AS NewsContent,CreateTime FROM T_News
WHERE NewsTitle LIKE @newstitle;
GO
根据条件利用存储过程查询数据
--存储过程,根据与内容和标题匹配数据
CREATE PROC Pro_GetNewsByTitleAndContent
@newskey VARCHAR(8)
AS
SELECT T1.NewsTitle,SUBSTRING(T1.NewsContent,1,20)+'......' AS NewsContent,T1.CreateTime FROM T_News T1
WHERE T1.NewsTitle LIKE @newskey OR T1.NewsContent LIKE @newskey;
GO
EXEC Pro_GetNewsByTitleAndContent '%国%'
GO
新建存储过程并返回一个值
--新建存储过程,向新闻表插入一条数据,并返回自动生成的最大编号
CREATE PROC Pro_GetMaxId
@maxid INT OUT
AS
INSERT INTO T_News(NewsTitle,NewsContent,NewsCreator,CreateTime,ClassId)
VALUES
('本周全国大部分地区降温','本周全国大部分地区降温,最高降温达10°','003',GETDATE(),'39ec3206-4467-44b4-8fa1-711443eb4ce5');
SET @maxid=@@IDENTITY;
GO
DECLARE @maxid INT;
EXEC Pro_GetMaxId @maxid OUT;
PRINT @maxid;
GO
VS中调用存储过程显示数据并动态生成一个表
namespace 新闻系统
{
public partial class cunchuguocheng : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataLoad();
}
private void DataLoad()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
string news = TextBox1.Text.Trim();
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Pro_NewsClass";
cmd.Parameters.Add(new SqlParameter("@News", "%" +news+ "%"));
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
StringBuilder sb1 = new StringBuilder();
string newstitle = string.Empty;
string content = string.Empty;
string time = string.Empty;
sb1.Append("<table border=1>");
foreach (DataRow row in dt.Rows)
{
sb1.Append("<tr>");
newstitle = row["NewsTitle"].ToString();
content = row["NewsContent"].ToString();
time = row["CreateTime"].ToString();
//string a = row["@a"].ToString();
sb1.Append("<td>" + newstitle + "</td>");
sb1.Append("<td>"+content+"</td>");
sb1.Append("<td>" + time + "</td>");
sb1.Append("</tr>");
}
sb1.Append("</table>");
divResult.InnerHtml = sb1.ToString();
}
}
}
protected void btnNewsClass_Click(object sender, EventArgs e)
{
DataLoad();
}
}
}