How to central the db connection in c# .net?

Question

Does anyone know how can I central the db connection in c# .net (perhaps in the mian.master)?

I have the following code for the db connection and used to call to the difference stored proc to retrieved data.

    string strConnString = ConfigurationManager.ConnectionStrings["testString"].ConnectionString;
    SqlConnection mySqlConnection = new SqlConnection(strConnString);
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.CommandText = "EXEC app_campaign_select @CampaignID=" + Request.QueryString["ixCampaign"].ToString();
    mySqlConnection.Open();
    SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();

Instead of coding it in every pages and connection to the db multiple times, any way I can code the following connection code in the master page and only connecting to the db once, then each page can call it when need to be connect into the db

    string strConnString = ConfigurationManager.ConnectionStrings["testString"].ConnectionString;
    SqlConnection mySqlConnection = new SqlConnection(strConnString);
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    SqlDataReader mySqlDataReader;

score 1 · Answer 1 · answered Oct 22 '12 at 23:45

Add it to a static DAL class and add parameters as needed.

public static void YourFunction() {
    // your code
}

Note:

Beware this line: mySqlCommand.CommandText = "EXEC app_campaign_select @CampaignID=" + Request.QueryString["ixCampaign"].ToString();

If that QueryString comes from a user entered value somewhere, you could be open to SQL Injection.

score 1 · Answer 2 · answered Oct 22 '12 at 23:48

Sure. Just create a helper class that has a few static methods in them for each type of return type you could have. Pass to one a string for stored procedure name and have an overloaded one that takes a string for a sql statment that you would pass in. You can also have one that just returns back a scalar value as well. So you would probably have 3 or so static methods you could call from any page.

How to central the db connection in c# .net?

2 Answers2

Note: