How to add and access connection string from web.config

Question

I want to add connection string to connect to mysql that are define in web.config file and access the same connection in my C# code how can I do this?

Here is my code sample that run after onclick of a button to connect to database.

protected void Button2_Click(object sender, EventArgs e)
    {
        String a = DropDownList1.SelectedItem.Value;
        String b = DropDownList3.SelectedItem.Value.PadLeft(3, '0');      
        String c = TextBox2.Text.PadLeft(5,'0').ToString();
        String d = TextBox3.Text.ToString();
        String digit = a+ b  + c + d;
        try
        {
         myConn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=testcase;User=root;Password=root;Option=3;");
         myConn.Open();
            //**
            string sql = "select * from testcase.main where reg_no =?";            
            //**
            OdbcCommand cmd = new OdbcCommand(sql, myConn);            
            //**
            cmd.Parameters.AddWithValue("?", digit);
            MyReader = cmd.ExecuteReader();
            //**
            while (MyReader.Read())
            {
                String f = MyReader["pet_name"].ToString();
                String g = MyReader["res_name"].ToString();

                Label9.Visible = true;
                Label9.Text = f;

                Label10.Visible = true;
                Label10.Text = "VS";

                //Label11.Visible = true;
                Label11.Text = g;

            }

            MyReader.Close();
        }
        catch (Exception e1)
        {
            Response.Write(e1.ToString());
        }
        finally
        {
            if (MyReader != null && !MyReader.IsClosed)
            {
                MyReader.Close();   
            }

            if (myConn != null && myConn.State == ConnectionState.Open)
            {
                myConn.Close();
            }
        }

FYI it is bad practise to include this code in your presentation layer logic, consider refactoring so that your data layer is in seperate classes which you can call from your presentation (UI) layer. — Darbio, Nov 18 '10 at 05:39
can you please help me out with a small example,would be great if you show using my code — Ishan, Nov 18 '10 at 06:26
have a look at this question for some starters: http://stackoverflow.com/questions/304828/where-can-i-find-clear-examples-of-mvc — Darbio, Nov 21 '10 at 06:59

score 3 · Accepted Answer · answered Nov 18 '10 at 05:38

If you know the connection string name you can use the ConnectionStrings property of the ConfigurationManager class

E.g.

using System.Configuration;
string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString.ToString();

Where, your web.config would contain a connectionstring named ConnectionStringName

score 3 · Answer 2 · edited Aug 25 '12 at 02:46

Here is an Example of Creating and adding connectionString to the web.config for SQL Server you can change the sql Connection to OleDb connection

SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
        connectionStringBuilder.DataSource = "localhost";
        connectionStringBuilder.IntegratedSecurity = true;
        connectionStringBuilder.InitialCatalog = "SampleDB";

        ConnectionStringSettings connSttng = new ConnectionStringSettings();
        connSttng.Name = "ConnectionStringName";
        connSttng.ProviderName = "Providername";
        connSttng.ConnectionString = String.Format("DataSource={0};InitialCatalog={1};IntegratedSecurity={2}", connectionStringBuilder.DataSource, connectionStringBuilder.InitialCatalog, connectionStringBuilder.IntegratedSecurity);

        Configuration config = WebConfigurationManager.OpenWebConfiguration("~");
        config.ConnectionStrings.ConnectionStrings.Add(connSttng);
        config.Save(ConfigurationSaveMode.Modified, true);
        ConfigurationManager.RefreshSection("connectionStrings");

Hope this will be use full

score 1 · Answer 3 · edited Feb 18 '12 at 09:53

in web config:

<connectionStrings>
<add 
    name="NorthwindConnectionString"
    connectionString="Data Source=serverName;Initial 
    Catalog=Northwind;Persist Security Info=True;User
    ID=userName;Password=password"
    providerName="System.Data.SqlClient"/>
 </connectionStrings>

in view:

using System.Configuration;
string ConnectionString =ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString.ToString();

score 0 · Answer 4 · answered Apr 25 '14 at 09:30

0

In all this answer static is missing

import this namespace:

using System.Configuration;

Get connection string:

static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString.ToString();

answered Apr 25 '14 at 09:30

Somnath Kharat

3,570
2
27
51

score 0 · Answer 5 · edited Jun 10 '16 at 11:00

0

web config

<connectionStrings>   
     <add name="cn"connectionString="server=localhost;database=database_name;uid=username;password=your password"/> 
</connectionStrings>

aspx page

string constr=ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
MySqlConnection con = new MySqlConnection(constr);
MySqlCommand cmd =new MySqlCommand("select * from  tbl_mastercampaign", con);

edited Jun 10 '16 at 11:00

DanielBarbarian

5,093
12
35
44

answered Jun 10 '16 at 10:40

Priyanka Arora

1
1

How to add and access connection string from web.config

5 Answers5