CODEWITHSUNDEEP
c#.net
Jin Yong
Jin Yong

Reputation: 43778

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

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;

Upvotes: 0

Views: 371

Answers (2)

sjramsay
sjramsay

Reputation: 555

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.

Upvotes: 1

Yatrix
Yatrix

Reputation: 13775

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.

Upvotes: 1

Related Questions