c# - Oracle.DataAccess.Client.OracleException C#

Question

我在这个类中从数据库中选择两个值并将其与用户提供的文本框值进行比较。下面是我的课。

     public void Userlogin(TextBox username, TextBox pwd)
    {
        int _failedAttempt = 0;

        OpenConnection();

        command = new OracleCommand();
        command.CommandText = "SELECT username, user_pwd FROM dinein_system_users WHERE username:usrname AND user_pwd:pwd";
        command.Connection = dbconnect;
        command.BindByName = true;

        try
        {
            command.Parameters.Add("usrname", username.Text);
            command.Parameters.Add("pwd", pwd.Text);
        }
        catch (NullReferenceException NRE)
        {
            MessageBox.Show("Please contact your developer about this error. Thank you " + NRE);
        }

        _reader = command.ExecuteReader();
        if (_reader.Read() != true)
        {
            _failedAttempt = _failedAttempt + 1;
            while (_failedAttempt < 3)
            {
                MessageBox.Show("Incorrect Username or Password. Please try again " + "Attempts: " + _failedAttempt);
                username.ResetText();
                pwd.ResetText();
            }
        }
        else
        {
            MessageBox.Show("Welcome");
        }
    }

我的连接字符串

this._connectionString = "Data Source=xe;Max Pool Size=50;Min Pool Size=1;Connection Lifetime=120;Enlist=true;User Id=hr;Password=hr";

所以当程序执行时我得到这个错误

An unhandled exception of type 'Oracle.DataAccess.Client.OracleException' occurred in Oracle.DataAccess.dll

附加信息:外部组件抛出异常。在过去的一个小时里，我一直在处理这个问题，如有任何帮助，我们将不胜感激。

更新

打开连接方式

public void OpenConnection()
    {
        try
        {
            if (dbconnect == null)
            {
                dbconnect = new OracleConnection(this._connectionString);
                dbconnect.Open();
                return;
            }

            switch (dbconnect.State)
            {
                case ConnectionState.Closed:
                case ConnectionState.Broken:
                    dbconnect.Close();
                    dbconnect.Dispose();
                    dbconnect = new OracleConnection(this._connectionString);
                    dbconnect.Open();
                    return;
            }
        }
        catch (OracleException oracleException)
        {
            MessageBox.Show("Database connectionString is null. Contact your developer! " + oracleException);
        }

    }

异常堆栈跟踪

   at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
   at DINEIN.OracleDB_Connection.Userlogin(TextBox username, TextBox pwd) in f:\My Documents\Projects\DINEIN\DINEIN\OracleDB_Connection.cs:line 92
   at DINEIN.Login.btn_login_Click(Object sender, EventArgs e) in f:\My Documents\Projects\DINEIN\DINEIN\Login.cs:line 31
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.PerformClick()
   at System.Windows.Forms.Form.ProcessDialogKey(Keys keyData)
   at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData)
   at System.Windows.Forms.Control.PreProcessMessage(Message& msg)
   at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg)
   at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg)
       at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponent.FPreTranslateMessage(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.Run(Form mainForm)
   at DINEIN.Program.Main() in f:\My Documents\Projects\DINEIN\DINEIN\Program.cs:line 19
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

Answer 1

与其使用单独的 OpenConnection() 方法，不如考虑使用 using() 语句进行编码。它将确保您与其他数据库相关对象的连接始终被释放。

例如:

int _failedAttempt = 0;

public void Userlogin(TextBox username, TextBox pwd)
{
  try
  {
    using (var connection = new OracleConnection(_connectionString))
    {
      connection.Open();

      using (var command = new OracleCommand())
      {
        command.CommandText = "SELECT username, user_pwd FROM dinein_system_users WHERE username= :usrname AND user_pwd= :pwd";
        command.Connection = connection;
        command.BindByName = true;

        command.Parameters.Add("usrname", username.Text);
        command.Parameters.Add("pwd", pwd.Text);

        using (var reader = command.ExecuteReader())
        {
          if (reader.Read() != true)
          {
            _failedAttempt += 1;
            if (_failedAttempt < 3)
            {
              MessageBox.Show("Incorrect Username or Password. " +
                              "Please try again. " + 
                              $"Attempts: {_failedAttempt}");
              username.ResetText();
              pwd.ResetText();
            }
            else
            {
              // 3 failed attempts
            }
          }
          else
          {
            _failedAttempt = 0;
            MessageBox.Show("Welcome");
          }
        }
      }
    }
  }
  catch(OracleException ex)
  {
    MessageBox.Show("Error: {ex}");
  }
}