ASP.NET中调用存储过程带(输入/输出)参数和返回值的一些常用的例子

王朝学院·作者佚名  2009-11-04
窄屏简体版  字體: |||超大  

有些初学者在调用存储过程时容易出现一些错误,下面我就不同的返回参数而言举个用户登录常用的例子做一些简单的讨论:(由简单--->>深入)

创建数据库:(Student)

创建用户表:(Users)

view plaincopy to clipboardprint?

use Student

go

create table Users

(

id int identity(1, 1) primary key,

name nvarchar(20) not null,

password nvarchar(20) not null

)

use Student

go

create table Users

(

id int identity(1, 1) primary key,

name nvarchar(20) not null,

password nvarchar(20) not null

)

1.带输入参数的存储过程

view plaincopy to clipboardprint?

create procedure proc_login

@name nvarchar(20),

@password nvarchar(20)

as

begin

select *

from Users

where name = @name and password = @password

end

go

create procedure proc_login

@name nvarchar(20),

@password nvarchar(20)

as

begin

select *

from Users

where name = @name and password = @password

end

go

调用带输入参数的存储过程

view plaincopy to clipboardprint?

//连接数据库的字符串

private string connectionString = ConfigurationManager.AppSetting["connectionString"];

//数据层的登录方法

public static User Login(string name, string password)

{

User user = null;

using (SqlConnection conn = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand("proc_login", conn);

cmd.CommandType = CommandType.StoredProcedure; //指定调用存储过程

//指定参数

cmd.Parameters.Add("@name", SqlDbType.nvarchar, 20).Value = name;

cmd.Parameters.Add("@password", SqlDbType.nvarchar, 20).Value = password;

conn.open();

using(SqlDataReader sdr = cmd.ExecuteReader())

{

if(sdr.Read())

{

user = new User((int)sdr["id"], sdr["name"].ToString(), sdr["password"].ToString());

}

}

conn.Close();

}

return user;

}

//连接数据库的字符串

private string connectionString = ConfigurationManager.AppSetting["connectionString"];

//数据层的登录方法

public static User Login(string name, string password)

{

User user = null;

using (SqlConnection conn = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand("proc_login", conn);

cmd.CommandType = CommandType.StoredProcedure; //指定调用存储过程

//指定参数

cmd.Parameters.Add("@name", SqlDbType.nvarchar, 20).Value = name;

cmd.Parameters.Add("@password", SqlDbType.nvarchar, 20).Value = password;

conn.open();

using(SqlDataReader sdr = cmd.ExecuteReader())

{

if(sdr.Read())

{

user = new User((int)sdr["id"], sdr["name"].ToString(), sdr["password"].ToString());

}

}

conn.Close();

}

return user;

}

2.带输入输出参数的存储过程

view plaincopy to clipboardprint?

create procedure proc_login

@name nvarchar(20),

@password nvarchar(20),

@message nvarchar(50) output //用于输出用户登录的信息

as

begin

//判断用户名是否存在

if exists (select * from Users where name = @name)

begin

//判断密码是否正确

select * from Users where name = @name and password = @password

//返回的行数〉0

if @@rowcount > 0

begin

set @message = '登录成功!'

end

else

begin

set @message = '密码错误!'

end

end

else

begin

set @message = '用户名不存在!'

end

//在过程中如果有错误

if @@error <> 0

begin

set @message = '登录失败!'

end

end

go

create procedure proc_login

@name nvarchar(20),

@password nvarchar(20),

@message nvarchar(50) output //用于输出用户登录的信息

as

begin

//判断用户名是否存在

if exists (select * from Users where name = @name)

begin

//判断密码是否正确

select * from Users where name = @name and password = @password

//返回的行数〉0

if @@rowcount > 0

begin

set @message = '登录成功!'

end

else

begin

set @message = '密码错误!'

end

end

else

begin

set @message = '用户名不存在!'

end

//在过程中如果有错误

if @@error <> 0

begin

set @message = '登录失败!'

end

end

go

调用带输入输出参数的存储过程

view plaincopy to clipboardprint?

//数据层的登录方法

public static User Login(string name, string password, out string message)

{

User user = null;

using (SqlConnection conn = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand("proc_login", conn);

cmd.CommandType = CommandType.StoredProcedure; //指定调用存储过程

//指定参数

cmd.Parameters.Add("@name", SqlDbType.nvarchar, 20).Value = name;

cmd.Parameters.Add("@password", SqlDbType.nvarchar, 20).Value = password;

SqlParameter mes = new SqlParameter("@message", SqlDbType.nvarchar, 50);

mes.Direction = ParameterDiraction.Output;

cmd.Add(mes);

conn.open();

using(SqlDataReader sdr = cmd.ExecuteReader())

{

message = mes.Value.ToString();

if(sdr.Read())

{

user = new User((int)sdr["id"], sdr["name"].ToString(), sdr["password"].ToString());

}

}

conn.Close();

}

return user;

}

//数据层的登录方法

public static User Login(string name, string password, out string message)

{

User user = null;

using (SqlConnection conn = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand("proc_login", conn);

cmd.CommandType = CommandType.StoredProcedure; //指定调用存储过程

//指定参数

cmd.Parameters.Add("@name", SqlDbType.nvarchar, 20).Value = name;

cmd.Parameters.Add("@password", SqlDbType.nvarchar, 20).Value = password;

SqlParameter mes = new SqlParameter("@message", SqlDbType.nvarchar, 50);

mes.Direction = ParameterDiraction.Output;

cmd.Add(mes);

conn.open();

using(SqlDataReader sdr = cmd.ExecuteReader())

{

message = mes.Value.ToString();

if(sdr.Read())

{

user = new User((int)sdr["id"], sdr["name"].ToString(), sdr["password"].ToString());

}

}

conn.Close();

}

return user;

}

3.带返回值得存储过程

view plaincopy to clipboardprint?

create procedure proc_login

@name nvarchar(20),

@password nvarchar(20)

as

declare @message nvarchar(50) output //用于返回用户登录的信息

begin

//判断用户名是否存在

if exists (select * from Users where name = @name)

begin

//判断密码是否正确

select * from Users where name = @name and password = @password

//返回的行数〉0

if @@rowcount > 0

begin

set @message = '登录成功!'

end

else

begin

set @message = '密码错误!'

end

end

else

begin

set @message = '用户名不存在!'

end

//在过程中如果有错误

if @@error <> 0

begin

set @message = '登录失败!'

end

return @message

end

go

create procedure proc_login

@name nvarchar(20),

@password nvarchar(20)

as

declare @message nvarchar(50) output //用于返回用户登录的信息

begin

//判断用户名是否存在

if exists (select * from Users where name = @name)

begin

//判断密码是否正确

select * from Users where name = @name and password = @password

//返回的行数〉0

if @@rowcount > 0

begin

set @message = '登录成功!'

end

else

begin

set @message = '密码错误!'

end

end

else

begin

set @message = '用户名不存在!'

end

//在过程中如果有错误

if @@error <> 0

begin

set @message = '登录失败!'

end

return @message

end

go

调用带返回值得存储过程

view plaincopy to clipboardprint?

//数据层的登录方法

public static User Login(string name, string password, out string message)

{

User user = null;

using (SqlConnection conn = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand("proc_login", conn);

cmd.CommandType = CommandType.StoredProcedure; //指定调用存储过程

//指定参数

cmd.Parameters.Add("@name", SqlDbType.nvarchar, 20).Value = name;

cmd.Parameters.Add("@password", SqlDbType.nvarchar, 20).Value = password;

SqlParameter mes = new SqlParameter("@message", SqlDbType.nvarchar, 50);

mes.Direction = ParameterDiraction.ReturnValue; //注意这里指定返回值

cmd.Add(mes);

conn.open();

using(SqlDataReader sdr = cmd.ExecuteReader())

{

message = mes.Value.ToString();

if(sdr.Read())

{

user = new User((int)sdr["id"], sdr["name"].ToString(), sdr["password"].ToString());

}

}

conn.Close();

}

return user;

}

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航