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();
        }
    }

}