Multiple try catch

[Brandoe85]

Hello all, in my app which connects to a database....I have multiple buttons, or places where I need to run a query, im using this to connect and issue a query:

try
{
   //connect to database and run query
}
catch(Exception ex)
{
   MessageBox.Show(ex.Message);
}

In my code it seems im just copying and pasting that in multiple spots, but running a different query, anyone have any ideas on how to optimize this? Maybe create a function to connect and such, but i'm not sure how...any suggestions? Thanks

[mendhak]

One, I think you should catch an sqlException or Oledbexception (whatever your case is).

Second, I also think that you should place your database manipulation functions in a separate class and call them from your form.

But that's just my opinion, so I'm right.

[Brandoe85]

Ah yes, Oledbexception makes perfect sense thanks. I had been thinking thats exactly what I should be doing using a class, it just seemed wrong to be using the same code over and over, i'm glad you had some input for me, thanks for the reply.

Of course you're right...

[nebulom]

He's not right, he can never be right.

Just use something like

void NonQuery(string cmdtext,
            sql.Parameters param)
        {
            try
            {
                sql.Connection.Open(cn);
                new sql.Command(cmdtext,cn,param).ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                sql.Connection.Close(cn);
            }
        }

where sql.Parameters is a collection of type SqlParameter, something like this

using System;
using System.Data.SqlClient;
using System.Collections;
namespace lguis_0_1.sql
{
    public class Parameters:CollectionBase 
    {
        public Parameters(){}
        public SqlParameter this[int i]
        {
            get{ return (SqlParameter)this.InnerList[i];}
        }
        public void Add(SqlParameter v)
        {
            this.InnerList.Add(v);
        }
    }
}

and the sql.Command is something like

using System;
using System.Data;
using System.Data.SqlClient;
namespace lguis_0_1.sql
{
    public class Command
    {
        SqlCommand cm=new SqlCommand();
        public Command(){}
        public Command(string cmdtext,
            SqlConnection cn)
        {
            cm.CommandText=cmdtext;
            cm.CommandType=CommandType.StoredProcedure;
            cm.Connection=cn;
        }
        public Command(string cmdtext,
            SqlConnection cn,
            Parameters param)
        {
            cm.CommandText=cmdtext;
            cm.CommandType=CommandType.StoredProcedure;
            cm.Connection=cn;
            foreach(SqlParameter t in param) cm.Parameters.Add(t);
        }
        public void ExecuteNonQuery()
        {
            cm.ExecuteNonQuery();
        }
        public SqlDataReader ExecuteReader()
        {
            return cm.ExecuteReader();
        }
        public object ExecuteScalar()
        {
            return cm.ExecuteScalar();
        }
    }
}

and the sql.Connection is

using System;
using System.Data;
using System.Data.SqlClient;
namespace lguis_0_1.sql
{
    public class Connection
    {
        public Connection(){}
        public static void Close(SqlConnection cn)
        {
            if(cn.State==ConnectionState.Open) cn.Close();
        }
        public static void Open(SqlConnection cn)
        {
            if(cn.State==ConnectionState.Closed) cn.Open();
        }
    }
}

I could be totally wrong.

[Brandoe85]

Thanks alot nebulom! That helps me alot as I started making my database class. I'll post back on how it goes..thanks again!

But can you both be right!?!?

[Pirate]

Small tip about multiple catch structure : it should fall from the most specific to the most general(generic) . Like you catch different expected db exceptions then the base class Exception .

[kalaHasti]

You can make use of Database Application block provided by Microsoft. That's already a tested,compiled assembly and easy to use. You dont need to write your database classes all over from start.
I am using that application block and it's quite handy to use. I have created by Data base layer where I am calling the methods provided by the Application Blick to achieve my target.