the perfect way to connect to database?

Question

public class SqlHelper
{
public SqlHelper()
{
}
public static SqlConnection GetConnection()
{
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=" +     System.Web.HttpContext.Current.Server.MapPath(@"~\App_Data\learn.mdf") + ";Integrated Security=True;User Instance=True";
    return conn;
}
public static SqlDataReader ExecuteReader(string sql)
{
    SqlConnection con = GetConnection();
    con.Open();
    SqlCommand cmd = new SqlCommand(sql, con);
    SqlDataReader dr = null;
    try
    {
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
    catch
    {
        con.Close();
        return null;
    }
    return dr;
}
public static Object ExecuteScalar(string sql)
{
    SqlConnection con = GetConnection();
    con.Open();
    SqlCommand cmd = new SqlCommand(sql, con);
    Object val = null;
    try
    {
        val = cmd.ExecuteScalar();
    }
    catch
    {
        con.Close();
        return null;
    }
    finally
    {
        con.Close();
    }
    return val;

}
public static DataSet ExecuteDataSet(string sql)
{
    SqlConnection con = GetConnection();
    SqlCommand cmd = new SqlCommand(sql, con);
    DataSet ds = new DataSet();
    SqlDataAdapter adapt = new SqlDataAdapter(cmd);
    try
    {
        adapt.Fill(ds);
    }
    catch
    {
        con.Close();
    }
    return ds;
}
public static void ExecuteNonQuery(string sql)
{
    SqlConnection con = GetConnection();
    con.Open();
    SqlCommand cmd = new SqlCommand(sql, con);
    try
    {
        cmd.ExecuteNonQuery();
    }
    finally
    {
        con.Close();
    }
}
}

This is the Class which I use to implement every access to my database . But I think that the way I do connection with the database is a little bit overblown cause I have to hit the Connect function every time I need something . As well as other users going to do the same which kills the performance.
So what is the perfect way to connect with the database - and to stay connected if that better . Note that I use the database in many pages!
Thanks

Answer 1

First you can write a seperate class like this :

Get method for getting data (with a Select query) and Set method for manipulating data (Insert, Update, Delete)

using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;  //using this you can replace instead odbc to sql

// Example SqlCommand, SqlDataAdapter
class DataBaseConnection
{
    private OdbcConnection conn1 = new OdbcConnection(@"FILEDSN=C:/OTPub/Ot.dsn;" + "Uid=sa;" + "Pwd=otdata@123;"); //"DSN=Ot_DataODBC;" + "Uid=sa;" +  "Pwd=otdata@123;"

    //insert,update,delete
    public int SetData(string query)
    {
        try
        {
            conn1.Open();
            OdbcCommand command = new OdbcCommand(query, conn1);
            int rs = command.ExecuteNonQuery();
            conn1.Close();
            return rs;
        }

        catch (Exception ex)
        {
            conn1.Close();
            throw ex;
        }

    }

    //select
    public System.Data.DataTable GetData(string sql)
    {
        try
        {

            conn1.Open();
            OdbcDataAdapter adpt = new OdbcDataAdapter(sql, conn1);
            DataTable dt = new DataTable();
            adpt.Fill(dt);
            conn1.Close();
            return dt;

        }
        catch (Exception ex)
        {
            conn1.Close();
            throw ex;


        }


    }


}

in your form you can make object to that database connection class

   DataBaseConnection db = new DataBaseConnection();

now you cal call get set with your get set method as following

string sqlSpecialHoliyday = "SELECT * FROM  Holiday WHERE   Date_Time='" + selectdate + "' AND  IDH='50'"; 
                DataTable dtAdditionalholily = db.GetData(sqlSpecialHoliyday);

AD you can Set Data Using Set method

string insertloginlog = "INSERT INTO Login_Log (Service_No, Machine_Name) VALUES   ('" + serviceID + "','" + machiname + "')";
                int ret = db.SetData(insertloginlog);

Hope This will help!

Answer 2

One thing that YOu might take into consideration is the Dependency Injection PAttern and some IoC controller. If every page needs to have this connection make this an injectable property (constructor probably wont work unless You implement some kind of infrastructure classes like Request) use some container (Unity, Castle, StructureMap) pack the needed things up (maybe cache, maybe some other things) and let the container do the magic (by magic I mean tons of boilerplate code) for You. luke

Answer 3

Maintaining a database connection is the job of the connection pool, and not the connection consumer. The best practice is to aquire a connection as late as possible and release it as soon as possible.

using(var connection = new SqlConnection(YourConnectionStringHelperFunction())
{

}

Answer 4

First, you should be using "using" statements to ensure that all your ADO.NET objects are properly disposed of in the event of a failure:

public static void ExecuteNonQuery(string sql) 
{     
    using(var con = GetConnection())
    {
        con.Open();     
        using(var cmd = new SqlCommand(sql, con))
        {         
            cmd.ExecuteNonQuery();     
        }     
    }
}

However, having said that, I don't really see a problem with this approach. The advantage is that the connections, commands, adapters and whatnot are properly disposed of every time you execute a bit of SQL. If you were to make a single static SqlConnection instance, you'd escalate the chances that the connection is already in use (when, for example, iterating over the contents of a SqlDataReader).

If you are really concerned about it, provide overloads that take a connection as an extra parameter:

public static void ExecuteNonQuery(string sql, SqlConnection connection) 
{     
    using(var cmd = new SqlCommand(sql, con))
    {         
        cmd.ExecuteNonQuery();     
    }     
}

This way, callers can either execute a bit of SQL that doesn't require multiple calls, or they can call your GetConnectionMethod to obtain a connection, and pass it to multiple calls.

Answer 5

If this is used for a web site then you have to consider that between requests for pages, even from the same browser, your server state will be torn down (in general terms) so there's nothing really to be gained from trying to maintain your SQL connection between pages. That's the first thing.

If each page is the result of a single database connection then you are probably as optimised as you really need to be, if you are making several connections over the generation of a page then you may want to look at keeping a connection alive until you have finished retrieving data; either by maintaining the connection or optimising your data retrieval to limit the back and forth between your app and the db.