Share via

The connection is already Open (state=Open).

  • Article

Question

Thursday, July 24, 2008 11:01 AM

Hi,

I'm new to c# so forgive me for bad coding etc.....I am tyying to create a file watcher that monitors file creation using SystemFileWatcher.

The create event calls InsertFileData (below) to insert the data into a SQL table every time a file is created. The problem I'm having is that I'm getting The connection is already Open (state=Open) error when I created several files in several folders. Does anyone know of a way around this?

private void InsertFileData(string @folderPath, string @name, string @fileCreated)

{

// write created file to table in sql server

try

{

// Open the connection

sqlConn.Open();

// prepare command string

string sInsertCommand = @"

insert into dbo.ftp_Files

(folder, fileName, fileSize, fileDate, isLocked)

values (" + @folderPath + ", " + @name + ", 0, " + @fileCreated + ", 1)";

//Console.WriteLine(sInsertCommand);

// 1. Instantiate a new command with a query and connection

SqlCommand cmd = new SqlCommand(sInsertCommand, sqlConn);

// 2. Call ExecuteNonQuery to send command

cmd.ExecuteNonQuery();

}

catch(Exception ex)

{

logger.Write("InsertFileData: " + ex.ToString(),"Error");

}

finally

{

// Close the connection

if (sqlConn != null)

{

sqlConn.Close();

}

}

}

 

All replies (4)

Thursday, July 24, 2008 2:07 PM ✅Answered

* Does anyone know of a way around this?

*Yes, The most reliable way to open and close connections is to use using blocks. It assures you that every connection is closed on time and nothing is missed.

The sample below performs logically the same thing you are trying to accomplish. It detects all actively running  processes (that part is not shown) and stores the collection in Sql Server. I hope I did not miss any curly brackets though while copying

                using ( SqlConnection conn = new SqlConnection ( Globals.connStrCentrContr ) )  
                {  
                    SqlCommand cmdm = new SqlCommand ( );  
                    if ( conn.State == ConnectionState.Closed )  
                    {  
                        try  
                        {  
                            conn.Open ( );  
                        }  
                        catch  
                        {  
                            return;  
                        }  
                    }  
                    cmdm.Connection = conn;  
                    cmdm.CommandType = CommandType.StoredProcedure;  
                    cmdm.CommandText = "CentrCtrl_InsertOfficeProcesses";  
                    cmdm.Parameters.Clear ( );  
                    cmdm.Parameters.Add ( "@procName", SqlDbType.NVarChar, 50, "procName" );  
                    for ( int ii = 0; ii < prc.Length; ii++ )  
                    {  
                        int indexed1 = ( prc[ ii ].ToString ( ) ).LastIndexOf ( "(" );  
                        int indexed2 = ( prc[ ii ].ToString ( ) ).LastIndexOf ( ")" );  
                        string procName = ( prc[ ii ].ToString ( ) ).Substring ( indexed1 + 1, indexed2 - indexed1 - 1 );  
                        cmdm.Parameters[ 0 ].Value = procName;  
                        cmdm.ExecuteNonQuery ( );  
                    }  
                }  
            

AlexB

Thursday, July 24, 2008 2:08 PM ✅Answered

you are creating only one SqlConnection ie sqlConn and u have instantiated it somewhere at the begining of the code.
             Now every time u call InsertFileData(),  you open the connection ie sqlConn.Open() and proceed with your stuff.
             So for the first time it will open the connection, and while it is open (i.e executing the first call), again InsertFileData() is called
             (second time), this time it is trying to open the connection which is already opened.
             * So you are getting this error

Wife is like a Software, Lots of bugs.

Thursday, July 24, 2008 11:23 AM

private void InsertFileData(string @folderPath, string @name, string @fileCreated) 
 
{ 
 
// write created file to table in sql server 
 
try 
{ 
.... 
// Open the connection if it is closed 
if(sqlConn.State == ConnectionState.Closed) 
     sqlConn.Open(); 
... 
} 
 
..... 

Wife is like a Software, Lots of bugs.

Thursday, July 24, 2008 11:50 AM

I'm not quite sure what is causing this though as the InsertFileData is being called several times at the same time. Does this mean that it will not open several connection and insert the file data using different connections.

Also I see this would stop the current error, but wouldn't there be a posibility that the connection gets closed half way through one of the updates.

Or is one of the other process blocking the connection i.e I have a timer event that scans the table into a dataset then loops the dataset and polls the file to see if its locked if it is then it updates the dataset and then the dataset is updated back to the sql database. Could this be locking the connection as I use a sqlDataAdapter using the same sqlconnection.