ASP.NET中調用存儲過程帶(輸入/輸出)參數和返回值的一些常用的例子

2019-11-17 04:07:56

字體：大中小

來源：轉載

供稿：網友

   有些初學者在調用存儲過程時容易出現一些錯誤，下面我就不同的返回參數而言舉個用戶登錄常用的例子做一些簡單的討論：(由簡單--->>深入)

創建數據庫：(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;
}

上一篇： C#日期格式化

下一篇：C# Excel 交互類