C# console application Invalid Operation Exception

Question

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;

namespace BissUpdater
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Data Source=H....; 
                Initial Catalog=LANDesk; Persist Security Info=True; 
                User ID=Mainstc; Password=xxxxxxxx";

            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
        }
    }
}

The SQL Connection threw a invalid operation exception.

"Invalid Operation. The connection is closed".

This is my complete Code. In a other program, it works perfect.

That is the second time, that doesnt work. Im working with VS2005...maybe my program is damaged?

Stacktrace:

at System.Data.SqlClient.SqlConnection.GetOpenConnection()
at System.Data.SqlClient.SqlConnection.get_ServerVersion()

Can you post the full stack trace, to make it clear where the Exception is thrown? — RB., Mar 22 '13 at 09:04

BizApps · Accepted Answer · 2013-03-22T10:03:30.883

18

The correct way doing that should be something like:

static void Main(string[] args) {
    string connectionString = "Data Source=H....; 
    Initial Catalog=LANDesk;User ID=Mainstc; Password=xxxxxxxx"; 
    // removed Persist Security Info=True; 


    using(SqlConnection con = new SqlConnection(connectionString))
    {
      if (con.State==ConnectionState.Closed)
      {                      
          con.Open();   
      }
    }


}

Using Using Statement it will automatically dispose your SQL connection.

Check this also: Best Practices for Using ADO.NET on MSDN

Other things to do: Use SQL Management Studio and try to use your sql authentication login credential from your connection string and if you have successfully connected to your database using that account the above code should work for you.

Best Regards

edited Mar 22 '13 at 10:03

answered Mar 22 '13 at 09:03

BizApps

6,048
9
40
62

3

I agree, that this code is better, because of the using statement, but I don't think this is going to solve the problem. – Max Mar 22 '13 at 09:11
1

Now, I get the same exception but when the con.open() statement executed, the connection is open and the sql connection says state "open". It works but...why I got first the exception :O... – mnlfischer Mar 22 '13 at 09:44
8

What is the point of checking whether the `con` is `Open`? Clearly it isn't - you just `new`ed up the object. – mjwills Nov 14 '18 at 01:00

score 2 · Answer 2 · answered Mar 22 '13 at 09:03

2

The code should read

using (SqlConnection con = new SqlConnection(connectionString))
{
    con.Open();

    ...
}

answered Mar 22 '13 at 09:03

Thorsten Dittmar

55,956
8
91
139

score 0 · Answer 3 · answered Mar 22 '13 at 09:02

Try add this code. You probably has open connection and while rerun program you try to again open connection or you have problem with server or your connection string

con.Close();

See there for more info http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.open.aspx

score 0 · Answer 4 · answered Mar 22 '13 at 09:03

0

you can check connection state before opening ittry this :

SqlConnection con = new SqlConnection(connectionString);
if (con.State==ConnectionState.Closed)
{                      
    con.Open();   
}

// here your code goes for sql operations

con.Close();

answered Mar 22 '13 at 09:03

Mohammad Arshad Alam

9,694
6
38
61

score 0 · Answer 5 · answered Mar 22 '13 at 09:03

Try to use using statements. Direct manually opening and closing data bases in case of large databases is bad idea.

using(SqlConnection con = new SqlConnection(connectionString))

Try to do like this for open and close connections>>

public DB(string conStr):base()
{
con = new OracleConnection(conStr);
con.Open();
}


public void Close()
{
con.Close();
//con.Dispose();

}

Hope Helpful.

score 0 · Answer 6 · answered Jun 19 '19 at 09:24

I was having same problem, my solution in VB is:

Dim db As New Database

' ... Some Work with EF without procedures (90 seconds)

db.SaveChanges()

For Each p In list

    If db.Database.Connection.State <> ConnectionState.Open Then
        ' This is only executed 1 time
        db.Database.Connection.Open()
    End If

    ' ... Some Work with EF but calling a mapped procedure (1 or 2 seconds each call)
    db.MyProcedure(p.FieldId)

Next

db.Dispose()

But the total time was 200 seconds, so I had to change this in my WebConfig of my WebService:

<system.web>
    <httpRuntime executionTimeout="600" /> <!--10 min-->
...

C# console application Invalid Operation Exception

6 Answers6

Linked

Related