asp.net+Sqlserver 通过存储过程读取数据

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[0].Value = -1;
        para[0].Direction = ParameterDirection.Output;
        SQLHelper.ExecuteScalar("getsitenodeTotal", CommandType.StoredProcedure, para);
        string result = para[0].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)));
    }
}

View Code