C#中调用SQL存储过程实现登录认证

存储过程如下：

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER

procedure [dbo].[security_check](@user_sort int,@userID nchar(16),@userPWD nchar(16) )

as

declare @uid nchar(16);

declare @pwd nchar(16);

declare @state bit;

set @state=0;

if( @user_sort=1)

begin

declare cursor_temp cursor local for --定义游标

select 学号,密码 from 学生表where 学号 = @userID and 密码 = @userPWD;

open cursor_temp; --打开游标

fetch cursor_temp into @uid,@pwd; --推进游标

close cursor_temp; --关闭游标

end

if( @uid =@userID and @pwd=@userPWD )

begin

set @state=1;

return @state;

end;

else

begin

set @state=0;

return @state;

end;

----------------------------------------------------------------------------------------------------------------------------------------------

C#代码如下：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace 密码验证
{
class security
{
public static DataSet check(string uid)
{
SqlConnection mySqlConnection = new SqlConnection("server=.//SqlExpress;database=XSXK;integrated security=SSPI");

mySqlConnection.Open();

SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

mySqlCommand.CommandText = "select * from 学生表 where 学号 =" + uid;

SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();

DataSet myDataSet = new DataSet() ;

mySqlDataAdapter.SelectCommand = mySqlCommand ;

mySqlDataAdapter.Fill (myDataSet ,"学生表");

mySqlConnection.Close();

return myDataSet;

}

public static bool secuirty_check(int user_select, string uid, string pwd)
{
int result = 0;

SqlConnection mySqlConnection = new SqlConnection("server=.//SqlExpress;database=XSXK;integrated security=SSPI");

SqlCommand mySqlCommand = new SqlCommand( "security_check",mySqlConnection );

mySqlCommand.CommandType = CommandType.StoredProcedure;

//-----------------------------------------//关键部分！！ ----------------------------------------

SqlParameter param = new SqlParameter();

param.Direction = System.Data.ParameterDirection.ReturnValue;

mySqlCommand.Parameters.Add(param);

//------------------------------------------------------------------------------------------------
mySqlCommand.Parameters.Add( "@user_sort",SqlDbType .Int ).Value = user_select ;

mySqlCommand.Parameters.Add("@userID",SqlDbType.NChar ,16 ).Value = uid ;

mySqlCommand.Parameters.Add("@userPWD",SqlDbType.NChar,16 ).Value = pwd ;

//mySqlCommand.Parameters["@state"].Direction = ParameterDirection.ReturnValue ;

mySqlConnection.Open();

mySqlCommand.ExecuteScalar();

mySqlConnection.Close();

result = (int)param.Value;

if ( result == 0)
return false;
else
return true;

}

}
class Program
{
static void Main(string[] args)
{

/*
string uid;
Console.WriteLine("请输入用户名");
uid=Console.ReadLine();


DataSet myDataSet = security.check(uid);

foreach (DataRow thisDataRow in myDataSet.Tables["学生表"].Rows)
{

Console.WriteLine("学号:/t" + thisDataRow["学号"]);
Console.WriteLine("姓名:/t" + thisDataRow["姓名"]);
Console.WriteLine("密码:/t" + thisDataRow["密码"]);

}
*/

string uid;
string pwd;
Console.WriteLine("请输入用户名：");
uid = Console.ReadLine();
Console.WriteLine("请输入密码：");
pwd = Console.ReadLine();
if (security.secuirty_check(1, uid, pwd))
Console.WriteLine("登录成功");
else
Console.WriteLine("用户名或密码错误！");
Console.Read();
}
}

}