求一段使用存储过程（并且存储过程有返回值）的源代码。。。

漏了内容
Create Procedure up_test
@inputOne int,
@outputOne int output
as
select @outputOne=@inputOne+1

vb：
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %><html>

<script language="VB" runat="server">    Sub Page_Load(Sender As Object, E As EventArgs)         Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlDataAdapter        MyConnection = New SqlConnection("server=(local)\NetSDK;database=northwind;Trusted_Connection=yes")
        MyCommand = New SqlDataAdapter("Ten Most Expensive Products", MyConnection)        MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure        DS = new DataSet()
        MyCommand.Fill(DS, "产品")        MyDataGrid.DataSource=DS.Tables("产品").DefaultView
        MyDataGrid.DataBind()
    End Sub</script><body>  <h3><font face="宋体">DataGrid 控件的简单存储过程选择</font></h3>  <ASP:DataGrid id="MyDataGrid" runat="server"
    Width="360"
    BackColor="#ccccff"
    BorderColor="black"
    ShowFooter="false"
    CellPadding=3
    CellSpacing="0"
    Font-Name="宋体"
    Font-Size="8pt"
    HeaderStyle-BackColor="#aaaadd"
    EnableViewState="false"
  /></body>
</html>
----------------
c#
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %><html>

<script language="C#" runat="server">    protected void Page_Load(Object Src, EventArgs E)
    {
        SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;database=northwind;Trusted_Connection=yes");
        SqlDataAdapter myCommand = new SqlDataAdapter("Ten Most Expensive Products", myConnection);        myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;        DataSet ds = new DataSet();
        myCommand.Fill(ds, "产品");        MyDataGrid.DataSource=ds.Tables["产品"].DefaultView;
        MyDataGrid.DataBind();
    }</script><body>  <h3><font face="宋体">DataGrid 控件的简单存储过程选择</font></h3>  <ASP:DataGrid id="MyDataGrid" runat="server"
    Width="360"
    BackColor="#ccccff"
    BorderColor="black"
    ShowFooter="false"
    CellPadding=3
    CellSpacing="0"
    Font-Name="宋体"
    Font-Size="8pt"
    HeaderStyle-BackColor="#aaaadd"
    EnableViewState="false"
  /></body>
</html>

带参数的：
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %><html>

<script language="VB" runat="server">    Sub GetEmployees_Click(Sender As Object, E As EventArgs)         Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlDataAdapter        MyConnection = New SqlConnection("server=(local)\NetSDK;database=northwind;Trusted_Connection=yes")
        MyCommand = New SqlDataAdapter("SalesByCategory", MyConnection)        MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure        MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@CategoryName", SqlDbType.NVarChar, 15))
        MyCommand.SelectCommand.Parameters("@CategoryName").Value = SelectCategory.Value        MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@OrdYear", SqlDbType.NVarChar, 4))
        MyCommand.SelectCommand.Parameters("@OrdYear").Value = SelectYear.Value        DS = new DataSet()
        MyCommand.Fill(DS, "销售额")        MyDataGrid.DataSource=DS.Tables("销售额").DefaultView
        MyDataGrid.DataBind()
    End Sub</script><body style="font: 10.5pt 宋体">  <form runat="server">  <h3><font face="宋体">对 DataGrid 控件的参数化存储过程选择</font></h3>  选择类别:  <select id="SelectCategory" runat="server">
    <option>Beverages</option>
    <option>Condiments</option>
    <option>Confections</option>
    <option>Dairy Products</option>
    <option>Grains/Cereals</option>
    <option>Meat/Poultry</option>
    <option>Produce</option>
    <option>Seafood</option>
  </select>     选择年份:  <select id="SelectYear" runat="server">
    <option>1996</option>
    <option>1997</option>
    <option>1998</option>
  </select>     <input type="submit" OnServerClick="GetEmployees_Click" Value="获取销售额" runat="server"/><p>  <ASP:DataGrid id="MyDataGrid" runat="server"
    Width="650"
    BackColor="#ccccff"
    BorderColor="black"
    ShowFooter="false"
    CellPadding=3
    CellSpacing="0"
    Font-Name="宋体"
    Font-Size="8pt"
    HeaderStyle-BackColor="#aaaadd"
    EnableViewState="false"
  />  </form></body>
</html>
--------------
c#
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %><html>

<script language="C#" runat="server">    public void GetEmployees_Click(Object sender, EventArgs E)
    {
        SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;database=northwind;Trusted_Connection=yes");
        SqlDataAdapter myCommand = new SqlDataAdapter("SalesByCategory", myConnection);        myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;        myCommand.SelectCommand.Parameters.Add(new SqlParameter("@CategoryName", SqlDbType.NVarChar, 15));
        myCommand.SelectCommand.Parameters["@CategoryName"].Value = SelectCategory.Value;        myCommand.SelectCommand.Parameters.Add(new SqlParameter("@OrdYear", SqlDbType.NVarChar, 4));
        myCommand.SelectCommand.Parameters["@OrdYear"].Value = SelectYear.Value;        DataSet ds = new DataSet();
        myCommand.Fill(ds, "销售额");        MyDataGrid.DataSource=ds.Tables["销售额"].DefaultView;
        MyDataGrid.DataBind();
    }</script><body style="font: 10.5pt 宋体">  <form runat="server">  <h3><font face="宋体">对 DataGrid 控件的参数化存储过程选择</font></h3>  选择类别:  <select id="SelectCategory" runat="server">
    <option>Beverages</option>
    <option>Condiments</option>
    <option>Confections</option>
    <option>Dairy Products</option>
    <option>Grains/Cereals</option>
    <option>Meat/Poultry</option>
    <option>Produce</option>
    <option>Seafood</option>
  </select>     选择年份:  <select id="SelectYear" runat="server">
    <option>1996</option>
    <option>1997</option>
    <option>1998</option>
  </select>     <input type="submit" OnServerClick="GetEmployees_Click" Value="获取销售额" runat="server"/><p>  <ASP:DataGrid id="MyDataGrid" runat="server"
    Width="650"
    BackColor="#ccccff"
    BorderColor="black"
    ShowFooter="false"
    CellPadding=3
    CellSpacing="0"
    Font-Name="宋体"
    Font-Size="8pt"
    HeaderStyle-BackColor="#aaaadd"
    EnableViewState="false"
  />  </form></body>
</html>

public int exeProcInt(string ProcName,SqlParameter[] strParams)
{
int UserID;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = this.conn;
if(inTransaction)
   　　　　 cmd.Transaction = trans;
cmd.CommandText = ProcName;
   　　 if(strParams!=null)
{
   　　 for(int i=0;i<strParams.Length;i++)
cmd.Parameters.Add(strParams[i]);
}
cmd.Parameters.Add(new SqlParameter("ReturnValue",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));
   　 cmd.ExecuteNonQuery();
UserID = (int)cmd.Parameters["ReturnValue"].Value;
return UserID;
}

没有看到有output类型的参数
我想知道output类型的参数是如何用的。。

Create procedure aaa
@aa int,
@bb output
as
declare @b int
select @b = 1
select @bb = @b
update table set aa = @aa
return(0)

SqlParameter[] array ={new SqlParameter("@user",SqlDbType.VarChar,100),
new SqlParameter("@password",SqlDbType.VarChar,500)};
int UserId;
UserId = exeProcInt("sp_TestUser",array);//我返回的是登陆用户的权限
if(UserId == 1)
{
.....
}
else
{
.....
}
//可以记住用户名Session["username"] = txtBox.Text;
//最好在判断密码时，加个MD5加密

哦，忘了给数组赋值了：
SqlParameter[] array ={new SqlParameter("@user",SqlDbType.VarChar,100),
     new SqlParameter("@password",SqlDbType.VarChar,500)};
array[0].Value = username.Text.Trim();
array[1].Value = JiaMi(pwd.Text.Trim());//此处返回的值是加密过的值int UserId;
UserId = exeProcInt("sp_TestUser",array);//我返回的是登陆用户的权限
if(UserId == 1)
{
   .....
}
else
{
   .....
}

谢谢 lar99(短裤浪子) 孜孜不倦的教诲

存储过程：
CreateProcedure ClientDetail
(
    @CID      int            = 0    OUTPUT ,
    @Cxm      nvarchar(50)   = ''   OUTPUT ,
     @Czjh     nvarchar(50)   = ''   OUTPUT ,
    @zzdz     nvarchar(50) = '' OUTPUT,
    @Cdh      varchar(50)    = ''   OUTPUT ,
   @csj  varchar(50)='' OUTPUT,
    @Czz      nvarchar(50)   = ''   OUTPUT ,
    @Clrr     int            = 0    OUTPUT ,
    @Clrsj    datetime       = ''   OUTPUT ,
    @Cbz      nvarchar(200)  = ''   OUTPUT ,
    @Cgxsj    Datetime       = ''   OUTPUT ,
    @Error    tinyint        = 0    OUTPUT

)
AS

SELECT
      @CID      = CID       ,
      @Cxm      = Cxm       ,
        @Czjh     = Czjh      ,
     @zzdz = zzdz,
      @Cdh      = Cdh       ,
     @csj = csj,
      @Czz      = Czz       ,
      @Clrr     = Clrr      ,
      @Clrsj    = Clrsj     ,
      @Cbz      = Cbz       ,
      @Cgxsj    = Cgxsj     ,
      @Error    = 0
FROM
    ClientInfoWHERE
    CID = @CID

if @@ROWCOUNT<1 Select @Error = 1
GO

调用存储过程：public ClientInfo GetClientInfo(int cid)
{
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("ClientDetail", myConnection);
myCommand.CommandType = CommandType.StoredProcedure; SqlParameter parameterCid = new SqlParameter("@cid", SqlDbType.Int);
parameterCid.Value = cid;
myCommand.Parameters.Add(parameterCid); SqlParameter parameterErr = new SqlParameter("@error", SqlDbType.TinyInt);
parameterErr.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterErr); SqlParameter parameterCxm = new SqlParameter("@cxm", SqlDbType.NVarChar, 50);
parameterCxm.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterCxm);
SqlParameter parameterCzjh = new SqlParameter("@czjh", SqlDbType.NVarChar, 50);
parameterCzjh.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterCzjh);

SqlParameter parameterZzdz = new SqlParameter("@zzdz", SqlDbType.NVarChar, 50);
parameterZzdz.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterZzdz);

SqlParameter parameterCdh = new SqlParameter("@cdh", SqlDbType.VarChar, 50);
parameterCdh.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterCdh);

SqlParameter parameterCsj = new SqlParameter("@csj", SqlDbType.VarChar, 50);
parameterCsj.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterCsj);

SqlParameter parameterCzz = new SqlParameter("@czz", SqlDbType.NVarChar, 50);
parameterCzz.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterCzz); SqlParameter parameterClrr = new SqlParameter("@clrr", SqlDbType.Int);
parameterClrr.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterClrr);

SqlParameter parameterClrsj = new SqlParameter("@clrsj", SqlDbType.DateTime);
parameterClrsj.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterClrsj);

SqlParameter parameterCgxsj = new SqlParameter("@cgxsj", SqlDbType.DateTime);
parameterCgxsj.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterCgxsj); SqlParameter parameterCbz = new SqlParameter("@cbz", SqlDbType.NVarChar, 200);
parameterCbz.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterCbz);
// Open the connection and execute the Command
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

// Create and Populate StoreInfo Struct using
// Output Params from the SPROC
ClientInfo myClientInfo = new ClientInfo();
myClientInfo.error = (System.Byte)parameterErr.Value;
if ( myClientInfo.error == 0 )
{
myClientInfo.cid  = (Int32)parameterCid.Value;
myClientInfo.cxm  = parameterCxm.Value.ToString();
myClientInfo.czjh  = parameterCzjh.Value.ToString();
myClientInfo.zzdz  = parameterZzdz.Value.ToString();
myClientInfo.cdh = parameterCdh.Value.ToString();
myClientInfo.csj = parameterCsj.Value.ToString();
myClientInfo.czz  = parameterCzz.Value.ToString();
myClientInfo.clrr  = (int)parameterClrr.Value;
myClientInfo.clrsj  = parameterClrsj.Value.ToString();
myClientInfo.cgxsj  = parameterCgxsj.Value.ToString();
myClientInfo.cbz = parameterCbz.Value.ToString();

}
return myClientInfo;
}

Create Table UserTable(
UserID int primary key, --用户ID
UserName char(10), --用户名
UserPassword varchar(16), --用户密码
RegMode int , --注册方式（1、本单位；2、工程相关；3、一般公众）
SSBD int, --用户所在的部门或所属标段
Impower int not null, --该用户的授权人
ImpowerDate datetime, --授权时间
)
--检查登陆用户的合法性
Create procedure proc_ValidUser
@UserName char(10),
@Password varchar(16),
@UserID int output,
@Ssbd int output
as
declare @RegMode int
declare verity_cursor CURSOR FOR
select RegMode,UserID,SSBD
from UserTable
where UserName=RTrim(LTrim(@UserName))
and @Password=UserPassword
Open verity_cursor
FETCH Next from verity_cursor into @RegMode,@UserID,@Ssbdif @@FETCH_STATUS <> 0
set @RegMode=-1
CLOSE verity_cursor
Deallocate verity_cursor
return @RegMode--------
string strCon=System.Configuration.ConfigurationSettings.AppSettings["SqlConnectionString"];
SqlConnection sqlConn=new SqlConnection(strCon);
try
{
sqlConn.Open();
}
catch(Exception e)
{
lblMsg.Text="建立连接失败。"+e.Message ;
}
//Create procedure proc_ValidUser @UserName char(10),@Password varchar(16)
//@UserID int output, @Ssbd int output
if(sqlConn.State ==ConnectionState.Open)Response.Write("Connection is ok"); SqlCommand sqlCmd=new SqlCommand();
sqlCmd.Connection =sqlConn;
sqlCmd.CommandType=CommandType.StoredProcedure ;
sqlCmd.CommandText ="proc_ValidUser"; SqlParameter retParam=sqlCmd.Parameters.Add ("RETURN_VALUE",SqlDbType.Int );
retParam.Direction =ParameterDirection.ReturnValue ; SqlParameter unmParam=sqlCmd.Parameters.Add ("@UserName",SqlDbType.Char,10);
unmParam.Direction =ParameterDirection.Input ;
unmParam.Value =strUID;

SqlParameter pwdParam=sqlCmd.Parameters.Add ("@Password",SqlDbType.VarChar,16);
pwdParam.Direction =ParameterDirection.Input ;
pwdParam.Value =strPwd;

SqlParameter uidParam=sqlCmd.Parameters.Add ("@UserID",SqlDbType.Int );
uidParam.Direction =ParameterDirection.Output; SqlParameter bdParam=sqlCmd.Parameters.Add("@Ssbd",SqlDbType.Int);
bdParam.Direction =ParameterDirection.Output;
//SqlDataReader verity= sqlCmd.ExecuteReader();
try
{
//SqlDataReader verity= sqlCmd.ExecuteReader();//均可
sqlCmd.ExecuteNonQuery();
}
catch(Exception e)
{
Response.Write("Exec Query Error:"+e.Message+"<br>" );
}
Response.Write("<br>RegMode="+sqlCmd.Parameters["RETURN_VALUE"].Value.ToString());
Response.Write("<br>UserID="+sqlCmd.Parameters["@UserID"].Value.ToString());
Response.Write("<br>SSBD="+sqlCmd.Parameters["@SSBD"].Value.ToString());
if( Convert.ToInt32(sqlCmd.Parameters["RETURN_VALUE"].Value) <0)
{
//lblMsg.Text="不是合法用户";
sqlConn.Close();
return false;
}
else
{
//lblMsg.Text="通过验证";
Session.Add("UID",sqlCmd.Parameters["@UserID"].Value);
Session.Add("SSBD",sqlCmd.Parameters["@SSBD"].Value );
Session.Add("RegMode",sqlCmd.Parameters["RETURN_VALUE"].Value);//公司帐户、一般帐户、公用帐户
sqlConn.Close();
return true;
}

调试易

求一段使用存储过程（并且存储过程有返回值）的源代码。。。

解决方案 »