CODEWITHSUNDEEP
c#sqlasp.nett-sql
Subpar Web Dev
Subpar Web Dev

Reputation: 3280

Is the correct way to leave no unclosed connections?

I'm trying to debug the source of my

Exception: System.InvalidOperationException

Message: Internal .Net Framework Data Provider error 1.

StackTrace: at System.Data.ProviderBase.DbConnectionInternal.PrePush(Object expectedOwner) at System.Data.ProviderBase.DbConnectionPool.PutObject(DbConnectionInternal obj, Object owningObject) at System.Data.ProviderBase.DbConnectionInternal.CloseConnection(DbConnection owningObject, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.CloseInnerConnection() at System.Data.SqlClient.SqlConnection.Close() at System.Data.SqlClient.SqlConnection.Dispose(Boolean disposing) at System.ComponentModel.Component.Dispose()

errors that I'm seeing in my log files and that I believe are leading to 502 errors. All my interactions with the database (I'm trying to do this in the most old-school way possible) are like

    public List<VersionInfo> GetAllVersions ( )
    {
        List<VersionInfo> Versions = new List<VersionInfo>();
        using (SqlConnection con = new SqlConnection(SurveyDbModel._conn))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand("GetAllVersions",con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                using (SqlDataReader dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        Versions.Add(new VersionInfo
                        {
                            Id = !dataReader.IsDBNull(0) ? dataReader.GetInt32(0) : default(int),
                            Title = !dataReader.IsDBNull(1) ? dataReader.GetString(1) : String.Empty
                        });
                    }
                }
            }
            con.Close();
        }
        return Versions;
    }

Am I doing the usings right? Anything nested wrong?

Upvotes: 3

Views: 304

Answers (1)

simme
simme

Reputation: 1564

The error you are receiving is not because of invalid disposing of connections. By using the using() { } scope you implement it just fine as the connection will be disposed automatically upon leaving the scope. However, GetAllVersions is not a valid operation, either because of the procedure logic being faulty or because it doesn't exist, which is the reason for the error.

If you in fact have a stored procedure named this way (which is not good practice), you should prepend it with exec, like:

exec GetAllVersions

However, you should rename it to follow common guidelines: exec usp_GetAllVersions

or similar, see https://www.mssqltips.com/sqlservertutorial/169/naming-conventions-for-sql-server-stored-procedures/ for examples on good naming.

EDIT 1: I suggest that you post your stored procedure together with your question, or that you check whether the stored procedure is runnable in a user session. My money is on that its not.

Upvotes: 1

Related Questions