C# Database The ConnectionString property has not been initialized

Question

I'm trying to insert values in my database and whenever I try to add it, my program just crash, giving me an error that The ConnectionString property has not been initialized, but I properly initialise it. Here is my code:

private static string conStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=c:\users\aldrin\documents\visual studio 2013\Projects\MidtermAssignment_Ramirez\MidtermAssignment_Ramirez\MasterFile.mdf;Integrated Security=True";
    SqlConnection myCon = new SqlConnection();

    private int studId, year, units; 
    private string fName, lName, mName, course, payment;

    public void insertRecord()
    {
        myCon.Open();
        SqlCommand insertInfo = new SqlCommand("spInsertStudentInformation", myCon);
        insertInfo.CommandType = CommandType.StoredProcedure;
        insertInfo.Parameters.Add("@studId", SqlDbType.Int).Value = studId;
        insertInfo.Parameters.Add("@fName", SqlDbType.VarChar).Value = fName;
        insertInfo.Parameters.Add("@lName", SqlDbType.VarChar).Value = lName;
        insertInfo.Parameters.Add("@mName", SqlDbType.VarChar).Value = mName;
        insertInfo.ExecuteNonQuery();
        myCon.Close();
        myCon.Open();
        SqlCommand insertData = new SqlCommand("spInsertStudentData", myCon);
        insertData.CommandType = CommandType.StoredProcedure;
        insertData.Parameters.Add("@studId", SqlDbType.Int).Value = studId;
        insertData.Parameters.Add("@course", SqlDbType.VarChar).Value = course;
        insertData.Parameters.Add("@year", SqlDbType.Int).Value = year;
        insertData.Parameters.Add("@units", SqlDbType.Int).Value = units;
        insertData.Parameters.Add("@payment", SqlDbType.VarChar).Value = payment;
        insertData.ExecuteNonQuery();
        myCon.Close();
    }

Here is the code in my button:

myData.StudId = Convert.ToInt32(txtStudId.Text);
        myData.FName = txtFName.Text;
        myData.LName = txtLName.Text;
        myData.MName = txtMName.Text;
        myData.Course = cboCourse.SelectedItem.ToString();
        myData.Year = Convert.ToInt32(cboYear.SelectedItem.ToString());
        myData.Units = Convert.ToInt32(txtUnits.Text);
        myData.Payment = cboPayment.SelectedItem.ToString();
        myData.insertRecord();

and here is my stored procedures:

CREATE PROCEDURE [dbo].spInsertStudentData
@studId int,
@course varchar(50),
@year int,
@units int,
@payment varchar(50)
AS
    INSERT INTO StudentData VALUES (@studId, @course, @year, @units, @payment)
RETURN 0

CREATE PROCEDURE [dbo].spInsertStudentInformation
@studId int,
@fName varchar(50),
@lName varchar(50),
@mName varchar(50)
AS
    INSERT INTO StudentInformation VALUES (@studId, @fName, @lName, @mName)
RETURN 0

I'm studying databases recently in ASP.NET and this is what I'm doing, but I don't know why this is not running fine in C#.

Solution 1

The error message is clear. An SqlConnection object needs a connectionstring before trying to open the connection. So when you build the connection you pass the connectionstring or set the ConnectionString property before opening.

But I really suggest you to start using local connection variables instead of global ones. And keep this declaration inside a using statement

private static string conStr = @".....";
public void insertRecord()
{
    using(SqlConnection myCon = new SqlConnection(conStr))
    using(SqlCommand insertInfo = new SqlCommand("spInsertStudentInformation", myCon))
    {
        myCon.Open();
        insertInfo.CommandType = CommandType.StoredProcedure;
        ....
        insertInfo.ExecuteNonQuery();

        // No need to close the connection here....


        SqlCommand insertData = new SqlCommand("spInsertStudentData", myCon);
        insertData.CommandType = CommandType.StoredProcedure;
        ....
        insertData.ExecuteNonQuery();
    }
}

The SqlConnection uses the Connection Pooling infrastructure and thus is better to have local variables that can be recycled by the pool of connection without keeping an important resource locked for your program

Finally the using statement avoids dangerous memory leaks closing and disposing the disposable objects like the connection and the command also in case of exceptions

There is another point to note in your code. You execute two related commands. I am sure that you don't want to insert the student info if, for some reason, you are not able to insert the student data. These scenarios call for the opening of a SqlTransaction that keeps your data atomic (meaning that you don't want to change anything in the database if any the operations fail)

    using(SqlConnection myCon = new SqlConnection(conStr))
    using(SqlCommand insertInfo = new SqlCommand("spInsertStudentInformation", myCon))
    {
        myCon.Open();
        using(SqlTransaction ts = myCon.BeginTransaction())
        {
           insertInfo.CommandType = CommandType.StoredProcedure;
           insertInfo.Transaction = ts;
           ....
           insertData.CommandType = CommandType.StoredProcedure;
           insertData.Transaction = ts;
           ....
           insertData.ExecuteNonQuery();
           ts.Commit();
       }
    }

The final Commit will persist everything in the database while, exiting the using block without calling the Commit, will automatically Rollback every change made to your tables

Solution 2

You need to provide the connection string you created in your first line to your connection constructor:

SqlConnection myCon = new SqlConnection(conStr);

Solution 3

Using .net core and Dependency injection:

First, initialize a private readonly string, here i call mine _connString. Then create a constructor of the class where you want to inject the connection string like so:

private readonly string _connString;
    public ClassNameRepository(IConfiguration configuration)
    {
        _connString = configuration.GetConnectionString("DatabaseNameConnString");
    }

Import using statement using Microsoft.Extensions.Configuration; to make sure there are no errors indicated by squiggly lines

In your appSettings.json file, add a connection string like this:

"ConnectionStrings": {
"DatabaseNameConnString": "add your connection string here"}

*You can get your connection string by clicking on "SQL Server Object Explorer" on your visual studio (i'm using 2019), then right click on the database object you wish to connect to, scroll down to "properties" and click on it. A properties window opens by the right. find and copy "Connection String" value, then past the copied value your appSettings.json file.

try
        {
            using (SqlConnection con = new SqlConnection(_connString))
            {
                using (SqlCommand cmd = new SqlCommand("storedProc", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();

                    return object;
                }
            }
        }
        catch (Exception)
        {

            throw;
        }

I hope this help.

Happy Coding..

Solution 4

Error message is so clear;

The ConnectionString property has not been initialized

You can set it's ConnectionString property to conStr as;

myCon.ConnectionString = conStr;

or you can specify it on constructor as a parameter as;

SqlConnection myCon = new SqlConnection(conStr);

As Steve said; saving your SqlConnection as a global variable is not a good idea. Use local variables for that and use using statement to dispose your connections and commands.

using(SqlConnection myCon = new SqlConnection(conStr))
using(SqlCommand insertInfo = myCon.CreateCommand())
{
   ...
}

View more solutions