C# - Connecting to database and inserting data with SQL Server stored procedure

Question

I'm in the process of trying to teach myself some C# (used to code in C++ in the late 90's if that tells you anything). So anyway, I keep running into a test failure in Visual Studio 2017 when attempting to test one of my classes.

The setup is as follows:

And within my ErrorLog.cs file:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Configuration;

namespace ManagementLibrary {
    public class ErrorLog {
        private int log_id { get; set; }
        private string logException {get; set;}
        private string error { get; set; }
        private string date_time { get; set; }
        private string logObject { get; set; }

        public ErrorLog(string logException, string error, string date_time, string logObject) {
            this.logException = logException;
            this.error = error;
            this.date_time = date_time;
            this.logObject = logObject;
        }

        public int LogError() {
            using (SqlConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("DataConnection"))) {

                SqlCommand command = new SqlCommand("InsertErrorLog", connection);
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter();
                p1.ParameterName = "@Exception";
                p1.SqlDbType = SqlDbType.VarChar;
                p1.Direction = ParameterDirection.Input;
                p1.Value = this.logException;

                SqlParameter p2 = new SqlParameter();
                p2.ParameterName = "@Error";
                p2.SqlDbType = SqlDbType.VarChar;
                p2.Direction = ParameterDirection.Input;
                p2.Value = this.error;

                SqlParameter p3 = new SqlParameter();
                p3.ParameterName = "@DateTime";
                p3.SqlDbType = SqlDbType.VarChar;
                p3.Direction = ParameterDirection.Input;
                p3.Value = this.date_time;

                SqlParameter p4 = new SqlParameter();
                p4.ParameterName = "@logObject";
                p4.SqlDbType = SqlDbType.VarChar;
                p4.Direction = ParameterDirection.Input;
                p4.Value = this.logObject;

                command.Parameters.Add(p1);
                command.Parameters.Add(p2);
                command.Parameters.Add(p3);
                command.Parameters.Add(p4);

                connection.Open();
                var identity = command.ExecuteNonQuery();
                return identity;
            }

        }

    }
}

Essentially I'm trying to create an audit trail for when an error is encountered it simply adds a record to the database. So not only am I attempting to teach myself some C#, but I'm trying to learn how to do unit testing within Visual Studio. I've followed some of the tutorials on setting up and doing unit tests, but when I do I keep getting the following failure:

Message: Test method ManagementLibrary.Tests.ErrorLogTests.LogErrorTest threw exception: System.NullReferenceException: Object reference not set to an instance of an object.

And when I "debug" the failure it "goes to" my Helper.cs file. Now this class simply reads the App.config file and returns the connection string.

namespace ManagementLibrary {
    public static class Helper {
        public static string CnnVal(string name) {
            return ConfigurationManager.ConnectionStrings[name].ConnectionString;
        }
    }
}

And shows the error: 'Object reference not set to an instance of an object'.

I don't even know what this means.

Thoughts?

Hi , good on you for trying to teach yourself something new :)! my guess is that it cant find the connection string in the app.config and returns null which is being used further on in the code hence you get the object ref error . Can you post your app.config for clarity? — cl0ud
– cl0ud, Commented Oct 2, 2018 at 13:15
Add a app.config file to the test project and copy over the connection string section from the main project's app.config. The error is because the test runs in its own application domain context and is unable to find the requested connection string. — Nkosi
– Nkosi, Commented Oct 2, 2018 at 13:22
@Nkosi after reviewing the project structure this is indeed the case. — cl0ud
– cl0ud, Commented Oct 2, 2018 at 13:24
Now while a more advanced topic, your experienced issues reveals some problematic design choices with tight coupling to implementation concerns. Those are things you can read up on later when you get more familiar with the platform. — Nkosi
– Nkosi, Commented Oct 2, 2018 at 13:27
I love this site! Thank you for the information provided and indeed you are all correct. I copied the main project's app.config to my test project and I got past the initial error (I have another but I'll hunt that down today). It is a painful and slow journey teaching myself, but I'll get there. Thanks for the input! — CodeMoto
– CodeMoto, Commented Oct 2, 2018 at 13:49

willwolfram18 · Accepted Answer · 2018-10-02 13:24:01Z

1

When you use ConfigurationManager it reads the app.config for the entry assembly/project. So in this case when you're running unit tests, the entry is your test project which does not have an app.config. Because of that, you're going to run into errors whenever you try to use the ConfigurationManager.

answered Oct 2, 2018 at 13:24

willwolfram18

1,9071 gold badge16 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# - Connecting to database and inserting data with SQL Server stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related