How can I use try/catch with sql connection?

Question

0.00/5 (No votes)

See more:

How can I use try/catch with this to handle the error if there is no connection with server?

This is my database class:

C#

 public static string connectionString = ConfigurationManager.ConnectionStrings["AlimamTreeFinal"].ConnectionString ;

  public  static SqlCommand Intialize(string query, params SqlParameter[] prmArray)
        {
           
                
                SqlConnection cn = new SqlConnection(connectionString);

                SqlCommand cmd = new SqlCommand(query, cn);

                if (!query.Contains(" "))
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                if (prmArray.Length > 0)
                    cmd.Parameters.AddRange(prmArray);

                cn.Open();

                return cmd;
   
        }

        public  int ExcuteNonQuery(string query, params SqlParameter[] prmArray)
        {
            try
            {
                SqlCommand cmd = Intialize(query, prmArray);

                int affectedRows = cmd.ExecuteNonQuery();

                cmd.Connection.Close();

                return affectedRows;
            }
            catch (SqlException ex)
            {
                return ex.Number;
            }
        }

        public object ExcuteScalar(string query, params SqlParameter[] prmArray)
        {
            try
            {
                SqlCommand cmd = Intialize(query, prmArray);

                object value = cmd.ExecuteScalar();

                cmd.Connection.Close();

                return value;
            }
            catch (SqlException ex)
            {
                return ex.Number;
            }
        }

        public  SqlDataReader ExcuteReader(string query, params SqlParameter[] prmArray)
        {
            
                SqlCommand cmd = Intialize(query, prmArray);

                SqlDataReader sqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                return sqlDataReader;

        }

        public  DataTable ExcuteDataTable(string query, params SqlParameter[] prmArray)
        {
          
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, connectionString);

                if (!query.Contains(" "))
                    sqlDataAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

                if (prmArray.Length > 0)
                    sqlDataAdapter.SelectCommand.Parameters.AddRange(prmArray);

                DataTable dt = new DataTable();
                sqlDataAdapter.Fill(dt);

                return dt;
         
        }

        public  DataSet ExcuteDataSet(string query, params SqlParameter[] prmArray)
        {
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, connectionString);

            if (!query.Contains(" "))
                sqlDataAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

            if (prmArray.Length > 0)
                sqlDataAdapter.SelectCommand.Parameters.AddRange(prmArray);

            DataSet ds = new DataSet();
            sqlDataAdapter.Fill(ds);

            return ds;
        }


    }
}

Posted 1-Oct-14 0:09am

mrimamm

Updated 1-Oct-14 0:21am

CPallini

v2

Add a Solution

Comments

[no name] 1-Oct-14 6:13am

Not all exceptions are SqlExceptions and you can have multiple catches...

2 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)

Raul Iloc · Answer 1 · 2014-10-01T00:41:00

1.Your Intialize method should be made private/protected!

2.You should use try-catch-finally block in all your public methods ;

3.You must take care at some disposable resources, like SqlConnection and SqlReader, that must be disposed/closed in finally block, not only the the case of success but also in the case of exceptions. Here is an example of improvements:

C#

public  void ExcuteReader(string query, params SqlParameter[] prmArray, UseReaderDelegate UseReader)
        {
SQLConnection connection = null;
SQLDataReader sqlDataReader = null;
//
try{
                SqlCommand cmd = Intialize(query, prmArray);
                connection = cmd.Connection; //!!!                    
                sqlDataReader = cmd.ExecuteReader();
                UseReader(sqlDataReader);    
}
catch(SqlException)
{
 //Put here your code for managing the SQL exceptions 
 //...
}
finally
{
  //Dispose the possible used resources!
  if(sqlDataReader != null)
      sqlDataReader.Close();
  //
  if( connection != null)
     connection.Close();  
} 
}

PS: For more details about exception management you could read my next article (even the examples are from an ASP.NET MVC4 application the rules are the same):
MVC Basic Site: Step 2 - Exceptions Management[^]

MukeshSagar · Answer 2 · 2014-10-01T00:28:00

Solution 1

Use the code as :

C#

try
{
   // Code Here
}
catch(Exception ee)
{
  // Exception Handle block
}

Posted 1-Oct-14 0:28am

MukeshSagar