在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赋的值转换为表名和列名
请各位高手帮帮忙了,谢谢……
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
调用
C# code
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
C# code
DECLARE @strSql varchar(4000) set @strSql ='select * from '+ @Table +' where ' + @userName1 +' = '+ @userName exec( @strSql )
作者: liuchaolin 发布时间: 2011-06-15
大概就是这样吧,详细的要你自己慢慢调试了
SQL code
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