An ideal way to connect to a 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 that I use to implement every access to my database. But I think that the way I make the connection to the database is a bit bloated, I have to click on the Connect function every time I need something. Like other users who will do the same thing that kills performance.
So, what is the ideal way to connect to the database - and stay in touch if it's better. Please note that I use the database on many pages!
Thanks

Answer 1

-, "using", , ADO.NET :

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

, , . , , , , SQL. SqlConnection, , (, SqlDataReader).

, , :

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

, SQL, , GetConnectionMethod .

Answer 2

-, , , , ( ), SQL- . .

, , , , , , ; , , db.

Answer 3

- , . , .

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

}

Answer 4

, , - PATITER Dependency Injection PAttern IoC. , (, , , - , Request), - (Unity, Castle, StructureMap), (, , , ), ( , ) .

Answer 5

:

Get ( Select) Set (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 an object for this database connection class

   DataBaseConnection db = new DataBaseConnection();

you are now calling get using the get set method, as shown below:

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

AD you can set data using the Set method

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

Hope this helps!