Sqlserver代码 创建存储过程如下：
/*根据父id获取类别总数*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'getsitenodeTotal' AND type = 'P')
DROP PROCEDURE getsitenodeTotal
GO
create proc getsitenodeTotal
@notecount varchar(10) output,
@ParentID varchar(10)
as
select @notecount = count(1) from siteserver_Node where ParentID=@ParentID
go
/*执行*/
DECLARE @notecount varchar(10) /*调用时必须带OUTPUT关键字 ，返回结果将存放在变量@sum中*/
EXEC getsitenodeTotal @notecount OUTPUT ,52
print @notecount /*查询项目案例中的类别*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'getsitenodeall' AND type = 'P')
DROP PROCEDURE getsitenodeall
GO
create proc getsitenodeall
(
@ParentID varchar(10),
@countnode varchar(10)
)
as
begin
declare @sql nvarchar(500)
set @sql = 'select top '+str(@countnode)+' NodeID,NodeName,ParentID,ImageUrl,[Content],Description
from siteserver_Node where ParentID='+str(@ParentID)
execute(@sql)
end
go
/*执行*/
EXEC getsitenodeall 52,10 /*根据类别查询案例信息*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'getsitecontent' AND type = 'P')
DROP PROCEDURE getsitecontent
GO
create proc getsitecontent
(
@NodeID varchar(10),
@countnode varchar(10)
)
as
begin
declare @sql nvarchar(500)
set @sql = 'select top '+ @countnode + ' ID,NodeID,Title,Summary,SettingsXML,AddUserName,AddDate,ImageUrl,Content,LinkUrl'
+' from siteserver_Content where NodeID='+@NodeID+' order by ID desc'
execute(@sql)
end
go
/*执行*/
EXEC getsitecontent 53,10 /*根据类别获取新闻总数*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'getsitecontentTotal' AND type = 'P')
DROP PROCEDURE getsitecontentTotal
GO
create proc getsitecontentTotal
@nodecount varchar(10) output,
@NodeID varchar(10)
as
select @nodecount = count(1) from siteserver_Content where NodeID=@NodeID
go
/*执行*/
DECLARE @nodecount varchar(10) /*调用时必须带OUTPUT关键字 ，返回结果将存放在变量@sum中*/
EXEC getsitecontentTotal @nodecount OUTPUT ,53
print @nodecount /*根据id查询单条项目案例信息*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'getsitecontentById' AND type = 'P')
DROP PROCEDURE getsitecontentById
GO
create proc getsitecontentById
(
@ID varchar(10)
)
as
begin
declare @sql nvarchar(500)
set @sql = 'select ID,NodeID,Title,Summary,SettingsXML,AddUserName,AddDate,ImageUrl,Content,LinkUrl from siteserver_Content where ID='+@ID
execute(@sql)
end
go
/*执行*/
EXEC getsitecontentById 240
存储过程
.net中返回json数据
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Data;
using Newtonsoft.Json;
using System.Web.Script.Serialization; /// <summary>
///WebService 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
//若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释。
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService { public WebService () { //如果使用设计的组件，请取消注释以下行
//InitializeComponent();
} [WebMethod]
public string HelloWorld() {
return "Hello World";
} [WebMethod(Description = "根据父id获取类别总数")]
public void getsitenodeTotal(string ParentID)
{
HttpContext.Current.Response.ContentType = "application/json;charset=utf-8";
string jsonCallBackFunName = string.Empty;
//jsonCallBackFunName = HttpContext.Current.Request.Params["jsoncallback"].ToString();
string jsonStr = string.Empty;
//@notecount
SqlParameter[] para ={
new SqlParameter("@notecount",SqlDbType.VarChar),
new SqlParameter("@ParentID", ParentID)
};
para[].Value = -;
para[].Direction = ParameterDirection.Output;
SQLHelper.ExecuteScalar("getsitenodeTotal", CommandType.StoredProcedure, para);
string result = para[].Value.ToString();
jsonStr = JsonConvert.SerializeObject(result); HttpContext.Current.Response.Write(string.Format("{0}({1})", jsonCallBackFunName, new JavaScriptSerializer().Serialize(jsonStr)));
} [WebMethod(Description = "查询项目案例中的类别")]
public void getsitenodeall(string ParentID, string countnode)
{
HttpContext.Current.Response.ContentType = "application/json;charset=utf-8";
string jsonCallBackFunName = string.Empty;
//jsonCallBackFunName = HttpContext.Current.Request.Params["jsoncallback"].ToString();
string jsonStr = string.Empty;
List<siteserver_Node> sitenodes = new List<siteserver_Node>();
SqlParameter[] para = {
new SqlParameter("@ParentID", ParentID),
new SqlParameter("@countnode", countnode)
};
using (SqlDataReader dr = SQLHelper.ExecuteReader("getsitenodeall", CommandType.StoredProcedure, para))
{
while (dr.Read())
{
siteserver_Node sitenode = new siteserver_Node(
Convert.ToInt32(dr["NodeID"]),
dr["NodeName"].ToString(),
Convert.ToInt32(dr["ParentID"]),
dr["ImageUrl"].ToString(),
dr["Content"].ToString(),
dr["Description"].ToString()
);
sitenodes.Add(sitenode);
}
}
jsonStr = JsonConvert.SerializeObject(sitenodes);
HttpContext.Current.Response.Write(string.Format("{0}({1})", jsonCallBackFunName, new JavaScriptSerializer().Serialize(jsonStr)));
} [WebMethod(Description = "根据类别查询案例信息")]
public void getsitecontent(string NodeID, string countnode)
{
HttpContext.Current.Response.ContentType = "application/json;charset=utf-8";
string jsonCallBackFunName = string.Empty;
//jsonCallBackFunName = HttpContext.Current.Request.Params["jsoncallback"].ToString();
string jsonStr = string.Empty;
List<siteserver_Content> sitecontents = new List<siteserver_Content>();
SqlParameter[] para = {
new SqlParameter("@NodeID", NodeID),
new SqlParameter("@countnode", countnode)
};
using (SqlDataReader dr = SQLHelper.ExecuteReader("getsitecontent", CommandType.StoredProcedure, para))
{
while (dr.Read())
{
siteserver_Content sitecontent = new siteserver_Content(
Convert.ToInt32(dr["ID"]),
Convert.ToInt32(dr["NodeID"]),
dr["Title"].ToString(),
dr["Summary"].ToString(),
dr["SettingsXML"].ToString(),
dr["AddUserName"].ToString(),
dr["AddDate"].ToString(),
dr["ImageUrl"].ToString(),
dr["Content"].ToString(),
dr["LinkUrl"].ToString()
);
sitecontents.Add(sitecontent);
}
}
jsonStr = JsonConvert.SerializeObject(sitecontents);
HttpContext.Current.Response.Write(string.Format("{0}({1})", jsonCallBackFunName, new JavaScriptSerializer().Serialize(jsonStr)));
} [WebMethod(Description = "根据类别获取新闻总数")]
public void getsitecontentTotal(string NodeID)
{
HttpContext.Current.Response.ContentType = "application/json;charset=utf-8";
string jsonCallBackFunName = string.Empty;
//jsonCallBackFunName = HttpContext.Current.Request.Params["jsoncallback"].ToString();
string jsonStr = string.Empty;
SqlParameter para =new SqlParameter("@NodeID", NodeID);
string sql = "select count(1) from siteserver_Content where NodeID=@NodeID";
string result =SQLHelper.ExecuteScalar(sql, CommandType.Text, para).ToString();
jsonStr = JsonConvert.SerializeObject(result);
HttpContext.Current.Response.Write(string.Format("{0}({1})", jsonCallBackFunName, new JavaScriptSerializer().Serialize(jsonStr)));
} [WebMethod(Description = "根据id查询单条项目案例信息")]
public void getsitecontentById(string ID)
{
HttpContext.Current.Response.ContentType = "application/json;charset=utf-8";
string jsonCallBackFunName = string.Empty;
//jsonCallBackFunName = HttpContext.Current.Request.Params["jsoncallback"].ToString();
string jsonStr = string.Empty;
siteserver_Content sitecontent = new siteserver_Content();
SqlParameter para = new SqlParameter("@ID", ID);
using (SqlDataReader dr = SQLHelper.ExecuteReader("getsitecontentById", CommandType.StoredProcedure, para))
{
while (dr.Read())
{
sitecontent = new siteserver_Content(
Convert.ToInt32(dr["ID"]),
Convert.ToInt32(dr["NodeID"]),
dr["Title"].ToString(),
dr["Summary"].ToString(),
dr["SettingsXML"].ToString(),
dr["AddUserName"].ToString(),
dr["AddDate"].ToString(),
dr["ImageUrl"].ToString(),
dr["Content"].ToString(),
dr["LinkUrl"].ToString()
);
break;
}
}
jsonStr = JsonConvert.SerializeObject(sitecontent);
HttpContext.Current.Response.Write(string.Format("{0}({1})", jsonCallBackFunName, new JavaScriptSerializer().Serialize(jsonStr)));
}
}