How do I define and use a connection string in my C# app

Question

1.00/5 (1 vote)

See more:

Hi I wish to know how to define a connection string and use it successfully in my App. The Idea is to define once and use as required.
I followed the advice here :

How to get Connection String from App.Config in C#[^]

with little success. when i run my test app, it runs ok but when i ask it to insert sole values into the test database I created for the occasion i have an error message:

" Object reference is not defined to an instance of the object " at the level of the statement

var connectionString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;

What should I do to get this situation sorted out please ?

What I have tried:

1. First I added this code in the config.app file:

<connectionStrings>
    <add name= "con" 
         providerName= "System.Data.SqlClient" 
         connectionString="Data Source=.\sqlexpress;Initial Catalog=practice;Integrated Security=True" />
    </connectionStrings>

2. Next I Added a reference to System.Configuration

3. The click event of my button control has the code :

var connectionString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
            
            string ins = @"insert into employee(name, address, phonenum, profession) values(@nam, @add, @phonen, @job)";

                SqlCommand cmd = new SqlCommand(ins, con);
                SqlParameter NM = new SqlParameter();
                NM.ParameterName = "nam";
                NM.Value = nametxt.Text;

                SqlParameter AD = new SqlParameter();
                AD.ParameterName = "add";
                AD.Value = addresstxt.Text;

                SqlParameter PN = new SqlParameter();
                PN.ParameterName= "phonen";
                PN.Value = telephonetxt.Text;

                SqlParameter PF = new SqlParameter();
                PF.ParameterName = "job";
                PF.Value = professiontxt.Text;

                cmd.Parameters.Add(AD);
                cmd.Parameters.Add(PN);
                cmd.Parameters.Add(NM);
                cmd.Parameters.Add(PF);
                con.Open();
                cmd.ExecuteNonQuery();
                MessageBox.Show("Values Entered", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                con.Close();

Posted 5-Mar-17 23:34pm

Nganku Junior

Updated 8-Mar-17 3:32am

Add a Solution

Comments

F-ES Sitecore 6-Mar-17 5:40am

Make sure you're updating the correct app.config file, especially if you have multiple projects in your solution.

Nganku Junior 6-Mar-17 7:09am

I have a single project in my Solution

Karthik_Mahalingam 6-Mar-17 5:41am

have tested your code, it works fine..
you might be missing something

PeejayAdams 6-Mar-17 5:56am

Yes, that looks fine to me, too. Make sure your <connectionstrings> segment is within the <configuration> tab and as F-ES suggests make sure you're dealing with the correct app.config file.

Nganku Junior 6-Mar-17 6:47am

Comments duly noted. I'll go through it again. Thanks !

Nganku Junior 6-Mar-17 7:10am

I have the same problem i must be missing something. But I can't figure out what

ZurdoDev 6-Mar-17 7:15am

What is the path to your project? What kind of project is it? What is the path of the config file you are updating?

Nganku Junior 6-Mar-17 7:52am

Path to my Project as in the Absolute Path? The Project is a Windows Form Application.

ZurdoDev 6-Mar-17 7:56am

Yes, the file paths.

Nganku Junior 6-Mar-17 8:04am

The project path is ; C:\Users\Le Parrain\Documents\Visual Studio 2015\Projects\TrialApCfg

The config file path is; C:\Users\Le Parrain\Documents\Visual Studio 2015\Projects\TrialApCfg\TrialApCfg\App.config

F-ES Sitecore 6-Mar-17 9:06am

That's the file Visual Studio maintains, the file the app uses when running is probably going to be \Projects\TrialApCfg\TrialApCfg\\bin\Debug\TrialApCfg.exe.config

When you compile your project the app.config is renamed and moved. Check the connection string is in *that* file.

Nganku Junior 6-Mar-17 9:15am

Ya! the connection string is in that file with path;
C:\Users\Le Parrain\Documents\Visual Studio 2015\Projects\TrialApCfg\TrialApCfg\bin\Debug.
This is what it looks like;

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />

<connectionstrings>
<add name="con"
providerName=" System.Data.SqlClient"
connectionString="Data Source=.\sqlexpress;Initial Catalog=practice;Integrated Security=True"/>

Richard MacCutchan 6-Mar-17 8:16am

The solution to this is quite straightforward: first identify which reference variable causes the exception. Second identify why it is null, and correct the code. The debugger will help you to find this.

Also, more importantly, do not do the following:

MessageBox.Show("Values Entered", "", MessageBoxButtons.OK, MessageBoxIcon.Information);

when you are not checking that your SQL statement executed successfully. If you do that on a live database you will surely live to regret it.

Tomas Takac 6-Mar-17 8:36am

You actually don't show how do you create the connection object itself. You just get the connection string and then use a con variable to create your command object. Maybe there is a problem there? BTW SqlConnection and SqlCommand are both disposable so you should enclose them in a using block.

Nganku Junior 6-Mar-17 9:10am

This is a view of the code

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 TrialApCfg
{
public partial class Form1 : Form
{
SqlConnection con;
public Form1()
{
InitializeComponent();
}

private void btn_enter_Click(object sender, EventArgs e)
{
var connectionString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;

string ins = @"insert into employee(name) values(@nam)";

SqlCommand cmd = new SqlCommand(ins, con);
SqlParameter NM = new SqlParameter();
NM.ParameterName = "nam";
NM.Value = txtboxname.Text;
cmd.Parameters.Add(NM);
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Values Entered", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
con.Close();

Tomas Takac 6-Mar-17 10:23am

Well as far as I can see SqlConnection con is never initialized. I guess you want to create a new connection using that connection string you are pulling from your app.config file. I think it should all be clear at this point.

Nganku Junior 6-Mar-17 8:41am

This is a view of the code

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 TrialApCfg
{
public partial class Form1 : Form
{
SqlConnection con;
public Form1()
{
InitializeComponent();
}

private void btn_enter_Click(object sender, EventArgs e)
{
var connectionString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;

string ins = @"insert into employee(name) values(@nam)";

SqlCommand cmd = new SqlCommand(ins, con);
SqlParameter NM = new SqlParameter();
NM.ParameterName = "nam";
NM.Value = txtboxname.Text;
cmd.Parameters.Add(NM);
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Values Entered", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
con.Close();

Karthik_Mahalingam 6-Mar-17 9:04am

Always use Reply button, to post Comments/query to the user, so that the user gets notified and responds to your text.

Nganku Junior 6-Mar-17 9:10am

Roger That

Karthik_Mahalingam 6-Mar-17 9:16am

copy that :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nganku Junior · Accepted Answer · 2017-03-08T03:32:00

Hey Guys, I found the little thing that was lacking:
This actually did it:

private void btn_enter_Click(object sender, EventArgs e)
{
var connectionString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
 
SqlConnection cn= new SqlConnection(connectionString); //This is all I had to add
string ins = @"insert into employee(name) values(@nam)";

SqlCommand cmd = new SqlCommand(ins, cn);//Brought in a few modifications 
SqlParameter NM = new SqlParameter();
NM.ParameterName = "nam";
NM.Value = txtboxname.Text;
cmd.Parameters.Add(NM);
cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Values Entered", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
cn.Close();