HOW TO:Access的身份验证

Author：水如烟  

Public   Class  SimpleWorksDatabase

     Private   Const  gConnectionString  As   String   =  _
     " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|SimpleWorks.mdb;Persist Security Info=True "

     Private   Function  GetConnection()  As  OleDb.OleDbConnection
         Return   New  OleDb.OleDbConnection(gConnectionString)
     End Function

     Private   Function  GetCommand( ByVal  commandText  As   String )  As  OleDb.OleDbCommand
         Return   New  OleDb.OleDbCommand(commandText, GetConnection)
     End Function

     ' 这种用法不好
     Public   Function  GetLoginIDBySql( ByVal  name  As   String ,  ByVal  pass  As   String )  As   Integer
         Dim  mUserID  As  Nullable( Of   Integer )

         Using  cm  As  OleDb.OleDbCommand  =  GetCommand _
        ( String .Format( " SELECT ID FROM users WHERE userName= '{0}' AND password = '{1}'     " , name, pass))
             With  cm
                Console.WriteLine(cm.CommandText)
                cm.Connection.Open()
                mUserID  =  cm.ExecuteScalar
                cm.Connection.Close()
             End   With
         End   Using

         If  mUserID.HasValue  Then
             Return  mUserID.Value
         End   If

         Return   - 1
     End Function

     ' 可以选取下面三种方法
     Public   Function  GetLoginIDUseParameters( ByVal  name  As   String ,  ByVal  pass  As   String )  As   Integer
         Dim  mUserID  As  Nullable( Of   Integer )

         Using  cm  As  OleDb.OleDbCommand  =  GetCommand( " SELECT ID FROM users WHERE userName= ? AND password = ? " )
             With  cm
                .Parameters.Add( " @UserName " , OleDb.OleDbType.VarChar).Value  =  name
                .Parameters.Add( " @Pass " , OleDb.OleDbType.VarChar).Value  =  pass

                cm.Connection.Open()
                mUserID  =  cm.ExecuteScalar
                cm.Connection.Close()

             End   With
         End   Using

         If  mUserID.HasValue  Then
             Return  mUserID.Value
         End   If

         Return   - 1
     End Function

     Public   Function  GetLoginIDUseParameters2( ByVal  name  As   String ,  ByVal  pass  As   String )  As   Integer
         Dim  mUserID  As  Nullable( Of   Integer )

         Using  cm  As  OleDb.OleDbCommand  =  GetCommand( _
         " PARAMETERS [@UserName] Text ( 50 ), [@Password] Text ( 50 );  "   &  _
         " SELECT ID FROM Users WHERE UserName=[@UserName] And Password=[@Password]; " )

             With  cm
                .Parameters.Add( " @UserName " , OleDb.OleDbType.VarChar).Value  =  name
                .Parameters.Add( " @Pass " , OleDb.OleDbType.VarChar).Value  =  pass

                cm.Connection.Open()
                mUserID  =  cm.ExecuteScalar
                cm.Connection.Close()

             End   With
         End   Using

         If  mUserID.HasValue  Then
             Return  mUserID.Value
         End   If

         Return   - 1
     End Function

     ' 如果将
     ' PARAMETERS [@UserName] Text ( 50 ), [@Password] Text ( 50 );
     ' SELECT ID FROM Users WHERE UserName=[@UserName] And Password=[@Password];
     ' 存在mdb里作为一个查询，查询命名为sp_LoginID
     ' 那么
     Public   Function  GetLoginIDByProcedure( ByVal  name  As   String ,  ByVal  pass  As   String )  As   Integer
         Dim  mUserID  As  Nullable( Of   Integer )

         Using  cm  As  OleDb.OleDbCommand  =  GetCommand( " Sp_LoginID " )

             With  cm
                .CommandType  =  CommandType.StoredProcedure  ' 作为存储过程处理

                .Parameters.Add( " @UserName " , OleDb.OleDbType.VarChar).Value  =  name
                .Parameters.Add( " @Pass " , OleDb.OleDbType.VarChar).Value  =  pass

                cm.Connection.Open()
                mUserID  =  cm.ExecuteScalar
                cm.Connection.Close()

             End   With
         End   Using

         If  mUserID.HasValue  Then
             Return  mUserID.Value
         End   If

         Return   - 1
     End Function
End Class