CODEWITHSUNDEEP
c#model-view-controllerdatabase-connection
tCoe
tCoe

Reputation: 401

MVC SQL connection initialization

I am working on a MVC web page that edits a SQL DB table. In my controller, I have a DB call to increment an entity table. Then if successful, creates a new row in my target table (not the entity table). The problem I am running into is I keep getting the following error:

The ConnectionString property has not been initialized.

However this only happens after the entity table has been incremented. Not sure where to go on this, so I am hoping that by posting some code, someone would be able to help me find my error.

so here is the obligatory code:

My SQL Connection:

private SqlConnection con;
public BaseRepository()
{
   con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServerConnection"].ToString());
}

My Entity Table Increment Call:

public int GetNextId()
{
    try
    {
        using (con)
        {

            DynamicParameters dynParam= new DynamicParameters();
            dynParam.Add("@entity_name", "insert_object ");
            con.Open();
            var value = con.Execute(SP_GET_NEW_ID, dynParam, commandType: CommandType.StoredProcedure);
            con.Close();
            return value;

        }
    }
    catch (Exception ex) { throw ex; }
}

Finally, here is the Row Insert Code:

public int InsertRowCode(InsertObject ccModel, UserModel appUser)
{
    var value = GetNextId();
    if (value == 1)
    {
        try
        {

            using (con) 
            //this is where the code breaks and jumps the the exception ex in my catch
            {
                con.Open();

                var dP = new DynamicParameters();
                //(add 14 dynamic Parameters here)
                var result = con.Execute(SP_SAVE_CORRECTION_CODES, dP, commandType: CommandType.StoredProcedure);
                con.Close();
                return result;
            }
        }

        catch (Exception ex)
        {
            throw ex;
        }
    }
    else { throw new Exception("Busted"); }
}

Any help is greatly appreciated. TIA

Upvotes: 0

Views: 220

Answers (1)

David
David

Reputation: 218950

Don't use shared connection objects.

When you exit this block:

using (con)
{
    //...
}

That connection object is now disposed and can't be used anymore. Don't worry about trying to optimize your connections, the connection pool does a very good job of that already. Create your connection objects where you need them, use them, and dispose them in a tight scope:

using (var con = new SqlConnection(connectionString))
{
    //...
}

As a side note, this is superfluous:

catch (Exception ex)
{
    throw ex;
}

That catch block isn't doing anything for you, and is actually deleting important information about the exception. Just remove that try/catch entirely.

If, on the other hand, you ever do want to do something with an exception before re-throwing it, just use the keyword throw by itself:

catch (Exception ex)
{
    // log something, etc.
    throw;
}

This would allow the exception to continue up the stack unmodified, preserving the actual error information.

Upvotes: 1

Related Questions