在ASP.NET中,对存储过程的参数赋值,怎样把赋的“字符串”的值转换为“存储过程的命令”

就是在数据库中定义了一个存储过程,如下:Use PCWEBSITE
If exists(select * from sysobjects where name ='admin_login' and type ='P')
Drop procedure admin_login
go
create procedure admin_login
(
@userName varchar(50),
@passWord varchar(50),
  @userName1 varchar(50),
  @passWord1 varchar(50),
  @Table varchar(50)
)
as
  if not exists(select * from @Table where @userName1 = @userName)
return 1 -- 表示不存在该用户
  else 
  begin
if not exists(select * @Table tb_admin where @userName1 = @userName and @passWord1=@passWord)
return 2 --表示密码错误
else
return 3 --表示登陆成功
  end 
GO

怎样在ASP.NET中给这些参数赋值才能执行这个存储过程,把参数@Table和@userName1、@userName2赋的值转换为表名和列名

请各位高手帮帮忙了,谢谢……

作者: Haixiaoyunxian   发布时间: 2011-06-15

DEMO
SQL code

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[InsertUsers]
(
    @UserName varchar(255),@Password varchar(255),@UserID int output
)
as
insert into Users(UserName,Password)values(@UserName,@Password)
set @UserID=@@Identity



调用
C# code
 using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
            conn.Open();
            SqlCommand cmd = new SqlCommand("[InsertUsers]", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@UserName", TextBox1.Text));
            cmd.Parameters.Add(new SqlParameter("@Password", TextBox2.Text));
            cmd.Parameters.Add(new SqlParameter("@UserID", 0)).Direction = ParameterDirection.Output;
            int result = cmd.ExecuteNonQuery();
            Response.Write(result + " UserID:" + cmd.Parameters["@UserID"].Value);
        }

作者: wxr0323   发布时间: 2011-06-15

楼主的意思是想这样?
C# code

DECLARE @strSql varchar(4000)
set @strSql ='select * from '+ @Table +' where ' + @userName1 +' = '+ @userName
exec( @strSql )



作者: liuchaolin   发布时间: 2011-06-15

大概就是这样吧,详细的要你自己慢慢调试了
SQL code

exec(' if not exists(select * from '+ @Table +' where '+ @userName1 +'=''' + @userName +''')'
+'return 1 -- 表示不存在该用户'
+'  else  '
+'  begin '
+'if not exists(select * '+ @Table + ' where ' + @userName1 +'='''+  @userName +''' and '+ @passWord1 +'='''+ @passWord +''')'
+'return 2 --表示密码错误'
+'else'
+'return 3 --表示登陆成功'
+'  end  ')


作者: liuchaolin   发布时间: 2011-06-16