CODEWITHSUNDEEP
c#asp.net
Sagotharan
Sagotharan

Reputation: 2626

Single declaration of connection string in application

In my asp.net application I have added the connection string in each page Page_Load function and declared the string as public in top of the page.

I know this is wrong thing. The whole application is running the same connection, so I want to declare the connection string in only one place. Where should I declare the connection string? How can I access the connection string in each Connection?

public string connstring = "";

protected void Page_Load(object sender, EventArgs e)
{
    if (IsPostBack != true)
    {
        connstring = ConfigurationManager.ConnectionStrings["connString"].ToString();
        MySqlConnection connection = new MySqlConnection(connstring);
        // ----
    }
}

Upvotes: 0

Views: 11016

Answers (7)

Habib
Habib

Reputation: 223422

You already have defined the connection string in web.config, you don't need to define it again in each page, just use the connection string from ConfigurationManager instead of creating a new string variable on each page. 

ConfigurationManager.ConnectionStrings["connString"].ToString()

its better if you check for null prior to using the connection string. 

if(ConfigurationManager.ConnectionStrings.Count > 0 && 
ConfigurationManager.ConnectionStrings["connString"] != null)

Upvotes: 3

Abhishek gupta
Abhishek gupta

Reputation: 463

You can decalre in webconfig file as

<configuration>
  < connectionstring>
     <add name="Name of Your Connection" connectionstring=""/>
  <connectionstring>
<configuration>

after declaring this in Webonfig, you can call the connection string in your different .Aspx page like,

add 

system.web.configuration;

then you can call your connection string as

    string connection = WebConfigurationManager.ConnectionStrings["Name Of your Connection"].ConnectionString;
sqlconnection con=new sqlconnection[con];
...

you can create a dataUtility class and can define a connectionstring once for whole project, like

class dataUtility
{
 string connection = WebConfigurationManager.ConnectionStrings["Name Of your   Connection"].ConnectionString;

Public void Insert()
{
sqlconnection con=new sqlconnection(connection);
...
}
}

now you have to create just a object of a class and call the insert method. You will be require to write whole DAL at one place.

Upvotes: 2

Mahdi Tahsildari
Mahdi Tahsildari

Reputation: 13602

add a web config file and put your connection in ConnectionString part : 

<configuration>
    <connectionStrings>
        <add name="ConnectionName" connectionString="your connection string" providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

in a public class add a static SqlConnection: 

public class myClass
{
    public static System.Data.SqlClient.SqlConnection con;
}

and instanciate it once in the Application Load (in global.asax): 

void Application_Start(object sender, EventArgs e)
{
    myClass.con = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ToString());
}

Upvotes: 1

Denys Wessels
Denys Wessels

Reputation: 17049

Why don't you create a separate class which instantiates the connection, something like this

Upvotes: 1

Moha Dehghan
Moha Dehghan

Reputation: 18472

You have not defined a connection string in each page, you are reading it in each page. If you don't want to do this, a better approach is to have a static class (or singleton) that manages that for you.

Usually, you should not do any database related operation in your UI code. A better approach is to delegate the work to a set of classes (Data Layer). A simple data layer could be a simple singleton class that does all the data access:

public class DataManager
{
    private static DataManager _instance;

    private DataManager() {}

    public static DataManager Instance
    {
        get
        {
            if (_instance == null)
                _instance = new DataManager();
            return _instance;
        }
    }

    public DbConnection GetConnection()
    {
        return new MySqlConnection(ConfigurationManager.ConnectionStrings["connString"].ToString());
    }

    ...
}

Obviously, this is a very simple class, not suitable for large projects. You should consider using an ORM tool such as EntityFramework.

You should absolutely consider putting relating things in a single place. This is the rule: Never repeat any code more than a method call (In this case, reading the connection string from web.config). What if you change the name of the connection string in web.config?

For guidelines in designing a data access layer, see this links:

http://en.wikipedia.org/wiki/Data_access_layer
http://msdn.microsoft.com/en-us/library/ee658127.aspx
http://weblogs.asp.net/cumpsd/archive/2005/04/24/404058.aspx

Upvotes: 1

Behroz Sikander
Behroz Sikander

Reputation: 4049

Here is what i do:

I add a UTILITIES project in my every .Net application. I add a file named Misc.cs in the utilities project.

In this file i add all the functions that are generic to the whole application and are going to be used somehow. Add a new function there like 

Public string GetConnectionString()
{
    return ConfigurationManager.ConnectionStrings["connString"].ToString();
}

Now, this is a common place where you have changed the connection string. Use it wherever you want. If you still need more abstraction, write a DAL (data access layer). Define properties in the DAL class like connection string, command string and others. Add methods like FetchData (write generic select method in it), Save data(write genric update/save logic here). From your front end just you the following to call

DataTable/DataSet lobj = [You_Dal_Class_Object].FetchData([Your_select_command_here];

Links: http://www.mssqltips.com/sqlservertip/2034/writing-a-data-access-layer-dal-for-sql-server/ http://msdn.microsoft.com/en-us/library/aa581778.aspx

Upvotes: -1

Dev
Dev

Reputation: 6720

Add it in web.config 

<connectionStrings>
    <add name="StrConnection" connectionString="Server=SERVER_NAME;Database=DATABASE_NAME;user id=USER_NAME;password=PASSWORD;"/>
</connectionStrings>

Make a static class in your data access layer like : 

public static class sqlConnectionProvider
    {
        public static string GetConnection()
        {
            return ConfigurationManager.ConnectionStrings["StrConnection"].ConnectionString;
        }

    }

and call it from business access layer like : 

return DataAccessLayer.ExecuteDataset(sqlConnectionProvider.GetConnection(), CommandType.StoredProcedure, spName, parameter);

Thanks

Upvotes: 1

Related Questions