[.net] ADO.net和sql 存储过程实例

最新推荐文章于 2024-06-01 22:44:48 发布

Ivan_linq

最新推荐文章于 2024-06-01 22:44:48 发布

阅读量775

点赞数

分类专栏： sql c#

本文链接：https://blog.csdn.net/xiazetong/article/details/9701643

版权

c# 同时被 2 个专栏收录

26 篇文章 0 订阅

订阅专栏

sql

8 篇文章 0 订阅

订阅专栏

验证用户是否存在

  public int ValidateLogin(string userName, byte[] Password)
        {
            int num;
            SqlParameter[] parameters = new SqlParameter[]
           {
             new SqlParameter("@UserName", SqlDbType.VarChar, 100),
             new SqlParameter("@EncryptedPassword", SqlDbType.Binary, 20) 
          };
            parameters[0].Value = userName;
            parameters[1].Value = Password;
            return DbHelperSQL.RunProcedure("sp_ValidateLogin", parameters, out num);
        }

      public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                int num = (int) command.Parameters["ReturnValue"].Value;
                connection.Close();
                return num;
            }
        }

     private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection) {
                CommandType = CommandType.StoredProcedure
            };
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

存储过程脚本
CREATE PROCEDURE [dbo].[sp_ValidateLogin]
    @UserName VARCHAR(50) = NULL ,
    @Email VARCHAR(100) = NULL ,
    @EncryptedPassword BINARY(20)
AS 
    DECLARE @UserID INT

    IF @Email IS NULL 
        BEGIN
            SELECT  @UserID = UserID
            FROM    Accounts_Users
            WHERE   UserName = @UserName
                    AND Password = @EncryptedPassword
        END
    ELSE 
        BEGIN
            SELECT  @UserID = UserID
            FROM    Accounts_Users
            WHERE   Email = @Email
                    AND Password = @EncryptedPassword
        END


    IF @UserID = NULL 
        RETURN -1
    ELSE 
        RETURN @UserID

GO