SQL Problem.
-
Hi, I am developing a C# windows application(.net 2003). The functionality of this application is to import all the records from a csv file to a table in a SQL Server 2000. For this i am opening a sql connection and I am using Sql command. Here i set sqlcommand timeout to 500. I am using a for loop to insert each record into sql table. After finishing all records i am closing and disposing sql connection. This is working fine. For some tables it is necessary to get primary key from another table and insert that primary key along with the current table's data. For extracting that primary key i wrote funtion. This function opens a new sql connection and sql command having timeout 500. After fetching data i am disposing that sql connection. This funtion is called for each record in the csv file. After inserting approximately 200 records it is giving an Exception Message "Timeout Expired. The timeout period elapsed prior to obtaining a connection form the pool. This may have occured because all pooled connections were in use and max pool size was reached". How to overcome this problem. It's urgent Any one please help me. Thanks in Advance. Ramu
-
Hi, I am developing a C# windows application(.net 2003). The functionality of this application is to import all the records from a csv file to a table in a SQL Server 2000. For this i am opening a sql connection and I am using Sql command. Here i set sqlcommand timeout to 500. I am using a for loop to insert each record into sql table. After finishing all records i am closing and disposing sql connection. This is working fine. For some tables it is necessary to get primary key from another table and insert that primary key along with the current table's data. For extracting that primary key i wrote funtion. This function opens a new sql connection and sql command having timeout 500. After fetching data i am disposing that sql connection. This funtion is called for each record in the csv file. After inserting approximately 200 records it is giving an Exception Message "Timeout Expired. The timeout period elapsed prior to obtaining a connection form the pool. This may have occured because all pooled connections were in use and max pool size was reached". How to overcome this problem. It's urgent Any one please help me. Thanks in Advance. Ramu
Ramu.M wrote:
For some tables it is necessary to get primary key from another table and insert that primary key along with the current table's data. For extracting that primary key i wrote funtion. This function opens a new sql connection and sql command having timeout 500.
Are you using stored procedures ? If yes then you can get this primary value through an output variable. This will avoid creating connection again.
Ramu.M wrote:
After inserting approximately 200 records it is giving an Exception Message
Verify your code to find how many instances of SQLConnection will be open when it reaches 200 ? Try to reduce the number of connection objects open. Make sure that all SQLConnection instances are returned back to pool. How you are disposing the objects ?
SqlConObject.Open()
//Do operation
SqlConObject.Close()
SqlConObject.Dispose()Above code will keep the connection in opened state when any error happens on do operations. So it's better to wrap the connection in between
usingstatements. This will dispose object even though any error happens.using (SqlConObject)
{
//Do operation
SqlConObject.Close()
}
-
Hi, I am developing a C# windows application(.net 2003). The functionality of this application is to import all the records from a csv file to a table in a SQL Server 2000. For this i am opening a sql connection and I am using Sql command. Here i set sqlcommand timeout to 500. I am using a for loop to insert each record into sql table. After finishing all records i am closing and disposing sql connection. This is working fine. For some tables it is necessary to get primary key from another table and insert that primary key along with the current table's data. For extracting that primary key i wrote funtion. This function opens a new sql connection and sql command having timeout 500. After fetching data i am disposing that sql connection. This funtion is called for each record in the csv file. After inserting approximately 200 records it is giving an Exception Message "Timeout Expired. The timeout period elapsed prior to obtaining a connection form the pool. This may have occured because all pooled connections were in use and max pool size was reached". How to overcome this problem. It's urgent Any one please help me. Thanks in Advance. Ramu
Hy, Have you tried creating a buffer or such? I mean to create a list with your queries first. When you insert a new query to the list you call the function to get the UID and then fetch the data from the CSV file and then create the query. After you have finished creating the query list just open a connection to the server and loop inside the list to insert the data. Hope it helps
Do your best to be the best
-
Ramu.M wrote:
For some tables it is necessary to get primary key from another table and insert that primary key along with the current table's data. For extracting that primary key i wrote funtion. This function opens a new sql connection and sql command having timeout 500.
Are you using stored procedures ? If yes then you can get this primary value through an output variable. This will avoid creating connection again.
Ramu.M wrote:
After inserting approximately 200 records it is giving an Exception Message
Verify your code to find how many instances of SQLConnection will be open when it reaches 200 ? Try to reduce the number of connection objects open. Make sure that all SQLConnection instances are returned back to pool. How you are disposing the objects ?
SqlConObject.Open()
//Do operation
SqlConObject.Close()
SqlConObject.Dispose()Above code will keep the connection in opened state when any error happens on do operations. So it's better to wrap the connection in between
usingstatements. This will dispose object even though any error happens.using (SqlConObject)
{
//Do operation
SqlConObject.Close()
}
-
Hy, Have you tried creating a buffer or such? I mean to create a list with your queries first. When you insert a new query to the list you call the function to get the UID and then fetch the data from the CSV file and then create the query. After you have finished creating the query list just open a connection to the server and loop inside the list to insert the data. Hope it helps
Do your best to be the best