none
SQL DataBase Connection with App.Config File RRS feed

  • Question

  • Hi All,

    I am trying to connect a SQL Server Database using Visual Studio C# and SQL Server. To do that I am using App.Config file.

    Source

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
      <connectionStrings>
        <add name="MyDbConnectionString" providerName="System.Data.SqlClient" connectionString="Data Source=.\ChiranthakaPC; Initial Catalog=MyPOS; Trusted_Connection=Yes" />
      </connectionStrings>
    </configuration>

    I also created a C# windows application.

    Source

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Configuration;
    using System.Data.SqlClient;
    
    namespace MyDBApp01
    {
        public partial class frmSQLDBConnectAppDotConfig : Form
        {
    
            SqlConnection mySQLConn;
            SqlDataReader mySQLDataReader;
            SqlCommand mySQLCommand;
    
            public frmSQLDBConnectAppDotConfig()
            {
                InitializeComponent();
            }
    
            private void frmSQLDBConnectAppDotConfig_Load(object sender, EventArgs e)
            {
    
               
            }
    
            private void btnConnect_Click(object sender, EventArgs e)
            {
                 ConnectionStringSettings mySQLConSettings = ConfigurationManager.ConnectionStrings["MyDBConnection"];
    
                string ConnectionString = mySQLConSettings.ConnectionString;
    
                try{
                    
                        mySQLConn = new SqlConnection(ConnectionString);
                        mySQLConn.Open();
                        mySQLCommand = new SqlCommand("select * From Items", mySQLConn);
                        mySQLDataReader = mySQLCommand.ExecuteReader();
                        while(mySQLDataReader.Read())
                        {
    
                            lstItemID.Items.Add(mySQLDataReader[0].ToString());
                            lstDesc.Items.Add(mySQLDataReader[1].ToString());
    
                        }
    
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    mySQLConn.Close();
                }
    
            }
        }
    }

    But when I click on the btnConnect button I get the following error for 'string ConnectionString = mySQLConSettings.ConnectionString;'

    I cannot understand what I have done wrong.

    Could you someone help me o solve this?

    Thanks.



    Thursday, April 7, 2016 6:29 PM

Answers

  • Hi Chiranthaka,

    can you describe in detail what you are doing? You are starting the application from Visual Studio with the integrated debugger?

    If so: Please check the bin\Debug Folder if you see both files in there with the correct names and extensions (Be aware that windows explorer hides file extensions in the default settings. So you may see something like this:

    Name Type

    ConsoleApplication1 Application
    ConsoleApplication1.exe XML Configuration File

    The code you did is exactly the same code what I did in my test:

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <connectionStrings>
            <add name="MyConnectionString"
                connectionString="Data Source=.;Initial Catalog=Test;Integrated Security=True"
                providerName="System.Data.SqlClient" />
        </connectionStrings>
    </configuration>

    using System;
    using System.Configuration;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                Console.Out.WriteLine(ConfigurationManager.ConnectionStrings["MyConnectionString"]?.ConnectionString);
            }
        }
    }
    

    And this wasn't changed in older versions of the .Net Framework. Be aware that you might have problems compiling my code because of the ?. - that is not available in older versions of c# and simply makes sure that the call is not tried if the result was null before. Just remove the ? inside the line if you compile with an older c# version (and risk the exception in case the config wasn't found).

    Saturday, April 9, 2016 5:50 AM

All replies

  • Hi,

    You didn't give the error message but maybe you get an exception because mySQLConSettings is null.

    Inside the config file, you named it: 
    MyDbConnectionString
    but you tried to read:
    MyDBConnection

    Make sure that the name you use in the code matches the one in the config file.

    I hope that this helped you.

    With kind regards,

    Konrad

    Thursday, April 7, 2016 7:23 PM
  • Hi Chiranthaka J,

    Since your problem is more related to the ADO.NET DataSet, I moved your thread to the corresponding forum for better support.

    Thanks for your understanding.

    Best Regards,

    Lake Xiao

    Friday, April 8, 2016 2:48 AM
  • I have updated the question with a screenshot. Also I have tried your solution and it did not worked.

    Please resolve this.

    Friday, April 8, 2016 3:41 PM
  • Hi Chiranthaka,

    I did some tests on my System and everything you showed now should work. 

    But maybe I see the problem in your Screenshot: Your config file is MyDBApp01.config? 

    The Name must be the name of your EXE + ".config". So if you Application is
    MyDBApp01.exe

    MyDBApp01.exe.config must be the name of the config file.

    So in my small test I got
    ConsoleApplication1.exe
    ConsoleApplication1.exe.config

    With kind regards,

    Konrad

    Friday, April 8, 2016 5:18 PM
  • I have tried that either and the error is still occurring.
    Saturday, April 9, 2016 1:26 AM
  • Hi Chiranthaka,

    can you describe in detail what you are doing? You are starting the application from Visual Studio with the integrated debugger?

    If so: Please check the bin\Debug Folder if you see both files in there with the correct names and extensions (Be aware that windows explorer hides file extensions in the default settings. So you may see something like this:

    Name Type

    ConsoleApplication1 Application
    ConsoleApplication1.exe XML Configuration File

    The code you did is exactly the same code what I did in my test:

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <connectionStrings>
            <add name="MyConnectionString"
                connectionString="Data Source=.;Initial Catalog=Test;Integrated Security=True"
                providerName="System.Data.SqlClient" />
        </connectionStrings>
    </configuration>

    using System;
    using System.Configuration;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                Console.Out.WriteLine(ConfigurationManager.ConnectionStrings["MyConnectionString"]?.ConnectionString);
            }
        }
    }
    

    And this wasn't changed in older versions of the .Net Framework. Be aware that you might have problems compiling my code because of the ?. - that is not available in older versions of c# and simply makes sure that the call is not tried if the result was null before. Just remove the ? inside the line if you compile with an older c# version (and risk the exception in case the config wasn't found).

    Saturday, April 9, 2016 5:50 AM
  • Hi Konrad,

    Many thanks for giving me advises and help on this question I raised. However I have solved my question. In the below I will explain how I did solve the issue step by step.

    1. Never use a new app.config file use the default App.config file and the below code snippet if you use a local SQL Server with Windows Authentication.
      Code
    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <startup> 
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
        </startup>
      <connectionStrings>
        <add name="MyDbConnectionString" providerName="System.Data.SqlClient" connectionString="Data Source=CHIRANTHAKAPC;Initial Catalog=MyPOS;Integrated Security=True" />
      </connectionStrings>
    </configuration>
    Sunday, April 17, 2016 3:44 PM