The ideal way to connect to the database?

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_Datalearn.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

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.

Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » The ideal way to connect to the database?

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录