Validate at database level Using try catch block

Question

0.00/5 (No votes)

See more:

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

C#

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

Posted 24-Mar-14 21:37pm

Venkat Kumar chigulla

Add a Solution

Comments

Raul Iloc 25-Mar-14 15:43pm

I've just added some new info in my Solution1.

CHill60 25-Mar-14 16:21pm

You having problems posting comments too? ;-)

Raul Iloc 26-Mar-14 2:38am

Yes, sorry about this, it seems that the site was overloaded last night!

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Amado · Answer 1 · 2014-03-25T10:16:00

These lines of code have some useful things:

C#

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>

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.

Dinesh.V.Kumar · Answer 2 · 2014-03-24T21:43:00

C#

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>

Raul Iloc · Answer 3 · 2014-03-24T21:43:00

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:

C#

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

Kornfeld Eliyahu Peter · Answer 4 · 2014-03-24T22:53:00

Solution 4

In generally it's not wise to use try-catch for validation, and that because try-catch is a very expensive code in performance.
In your case the problem that the SQL returns an empty dataset (as there is no such user). All you have to do is check (using if), if you got anything and display a well phrased error when not, and handling user when do...

Posted 24-Mar-14 22:53pm

Kornfeld Eliyahu Peter

Comments

Raul Iloc 25-Mar-14 9:35am

You are partially right, one of my rules from my article indicated in my solution (see Solution1 above) said also this:
"3.Do not use try-catch where not needed, use another approach like if-else sentences. For example checking for null before performing any operation over an object that may be null can significantly increase performance by avoiding exceptions."

But when you are accessing some resources like database, even the DB server could generate DB Exception, and this exceptions should be managed by using try-catch!
One of t

Kornfeld Eliyahu Peter 25-Mar-14 9:41am

It's true in case when DB may generate exception. In OG's case it's only an empty DataSet. It's clear from his code that ds.Tables[0].Rows[0] is null! I would not advise him to wrap da.Fill(ds) in try-catch without good reason...

Raul Iloc 25-Mar-14 9:50am

This is the line of code that use the SQL command and connection (that was set above) and run the command to the SQL server, then read the results from the server and use it to fill the data set. So this single line generate behind a lot of communication with DB server that may generate SQL exceptions.
So the conclusion is, that in this example, only this line must be in a try-catch!
PS: I think that now you agree with me and change your vote from 1 to 5!

Kornfeld Eliyahu Peter 25-Mar-14 10:22am

First of all - I didn't vote for you solution...
Second - putting da.Fill(ds) (only) in try catch will not remove the error. You have to read carefully OP's code. He's reading data from the SQL than try to put it's values to local variables. There is nothing wrong with the SQL. It returns nothing (empty results) because of the where clause will find nothing if user name or password are invalid. However, when SQL returns empty results (which is perfectly correct and will not cause no exception) you can not read data (there is no data) from that dataset. Checking that SQL returned something is a MUST-HAVE validation, it tells if user authentication succeeded or not...

Raul Iloc 25-Mar-14 15:11pm

Try-catch role is not to validate an SQL, but to manage an exceptional situation that could occure at the database communication level, and you arev right there should be an "if" to test if there are results or not.

Validate at database level Using try catch block

4 solutions

Solution 5

Solution 2

Solution 1

Solution 4

Add your solution here

Preview 0