在数据库级别验证使用try catch块 [英] Validate at database level Using try catch block

问题描述

每当我点击登录按钮提供错误的值时，就会抛出错误。所以我需要提出尝试捕获块。任何人都可以帮助我以下是我的代码

whenever i click on Login Button by giving wrong values it is throwing an error. so i need to come up with try catch blocks. could anybody help me. below is my code

public List<string> Authenticate(Login objlog)
        {
            string ConnectionString = connection();            
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "spValidate_LoginUser";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@UserName", objlog.UserName);
            cmd.Parameters.AddWithValue("@Password", objlog.Password);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;

            DataSet ds = new DataSet();
            da.Fill(ds);
          
 //getting error here
                string name = ds.Tables[0].Rows[0][0].ToString();
                string id = ds.Tables[0].Rows[0][1].ToString();
                string type = ds.Tables[0].Rows[0][2].ToString();
                string branch = ds.Tables[0].Rows[0][3].ToString();

                List<string> employee = new List<string>();

                employee.Add(name);
                employee.Add(id);
                employee.Add(type);
                employee.Add(branch);
                return employee;
             
        }

推荐答案

这些代码行有一些有用的东西：

These lines of code have some useful things:

public List<string> Authenticate(Login objlog)
{
    List<string> employee = new List<string>();
    DataTable dt = new DataTable("Table1");
    
    using (SqlConnection con = new SqlConnection(connection()))
    {
       using (SqlCommand cmd = new SqlCommand("spValidate_LoginUser", con))
       {
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.AddWithValue("@UserName", objlog.UserName);
           cmd.Parameters.AddWithValue("@Password", objlog.Password);
           
           using (SqlDataAdapter da = new SqlDataAdapter(cmd))
           {
               try
               {
                   con.Open();
                   da.Fill(dt);
               }
               catch{
                   //Do something here
               }
               finally{if (con.State != ConnectionState.Closed) con.Close();}               
           }
       }
    }
    if (dt.Rows.Count > 0)
    {
        foreach(DataRow dr in dt.Rows)
        {
            string name = dr[0] != DBNull.Value ? dr[0].ToString() : string.Empty;
            string id = dr[1] != DBNull.Value ? dr[1].ToString() : string.Empty;
            string type = dr[2] != DBNull.Value ? dr[2].ToString() : string.Empty;
            string branch = dr[3] != DBNull.Value ? dr[3].ToString() : string.Empty;

            employee.Add(name);
            employee.Add(id);
            employee.Add(type);
            employee.Add(branch);
        } 
    }

    return employee;
}
</string></string></string>

使用：在使用后处置对象。

Try / Catch ：仅在访问DataBase时才需要。

DataTable ：数据集不那么重。

foreach ：为了避免额外的计算，ds.Table.Rows [0] [0] .ToString（）不是一个非常好的形式。

DBNull ：为了避免在没有数据返回时出错。

我不同意List< string>，最好创建一个Employee类来解析。



希望它有任何帮助。

Using: To dispose object after its use.
Try/Catch: Only needed when access to DataBase.
DataTable: Less heavy that DataSet.
foreach: To avoid extra calculations ds.Table.Rows[0][0].ToString() is not the very good form to do it.
DBNull: To avoid errors when no data returns.
I'm not agree with List<string>, is better to create a Employee class to parse with.

Hope it helps in any way.

public List<string> Authenticate(Login objlog)
        {
            string ConnectionString = connection();            
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "spValidate_LoginUser";
            cmd.CommandType = CommandType.StoredProcedure;
 
            cmd.Parameters.AddWithValue("@UserName", objlog.UserName);
            cmd.Parameters.AddWithValue("@Password", objlog.Password);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
 
            DataSet ds = new DataSet();
             try 
             {
            da.Fill(ds);
    
           //Added Code here
           if(ds==null)
               return null;
           if(ds.Tables[0].Rows.Count<=0) 
               return null;
          
           
 //getting error here
                string name = ds.Tables[0].Rows[0][0].ToString();
                string id = ds.Tables[0].Rows[0][1].ToString();
                string type = ds.Tables[0].Rows[0][2].ToString();
                string branch = ds.Tables[0].Rows[0][3].ToString();
 
                List<string> employee = new List<string>();
 
                employee.Add(name);
                employee.Add(id);
                employee.Add(type);
                employee.Add(branch);
               }
               catch(Exception ex)
               {
                   //Handle your exception Here!!
               }
                return employee;
               //Added Code Ends
        }

</string></string></string>

通常当您访问可能产生异常的代码（如数据库访问，文件，内存分配等）时，您应该管理可能的异常。



我在下一篇文章中详细解释了这一点： MVC基本站点：第2步 - 例外管理 [ ^ ]文章和提供的源代码适用于ASP.NET MVC应用程序，但所有Windows应用程序的规则都相同。 />


在您的情况下，必须使用 try-catch 管理的关键区域是： da.Fill（ds）;



您还应通过添加下一个代码来管理查询中没有结果的情况之后的 try-catch块：

In generally when you are accessing code that could generate exception (like database access, files, memory allocation, etc) you should manage the possible exceptions.

I have explained this in details in my next article: MVC Basic Site: Step 2 - Exceptions Management[^] The article and the provided source code is for an ASP.NET MVC application but the rules are the same for all windows applications.

In your case the critical zone that must be managed by using try-catch is: da.Fill(ds);

You should also manage the case when there are no results in your query by adding the next code just after the try-catch block:

if(ds.Tables[0].Rows.Count<=0)
   return new List<string>();

这篇关于在数据库级别验证使用try catch块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！