Slow Query on 2nd query of the same command

AesopTurtle

I've created a class containing a method to make a connection with a database and do SELECT, INSERT, UPDATE, DELETE. The class works just fine... only for the first query. The problem occurs when the second query is made. The result of the second query is just fine but at VERY VERY slow rate (almost not responding). Here's my class:

class MyClass
{
public string SQL2KConnStr = "..(my connection string)..";
public object ExecuteSql(string sql)
{
SqlConnection conn = new SqlConnection(this.SQLExpressConnStr);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
switch(sql.Trim().Substring(0,6).ToLower()){
case "select":
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
adapter.Fill(ds, "QueriedTab");
return ds.Tables["QueriedTab"]; //Return a data table.
break;
default:
SqlCommand cmd = new SqlCommand();
//cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.CommandText = sql;
return cmd.ExecuteNonQuery(); //Return a number of affected rows.
}
}

And this's another class that makes use of the class above:

string SqlSelect = "select * from ...";
MyClass mc = new MyClass();
DataTable tab = (DataTable)mc.ExecuteSql(SqlSelect);
myDataGrid.DataSource = tab;
tab.Dispose();

I display the resultin a datagrid. The correctness of the result is fine. But the speed of the second and after is SO SO slow even though i've disposed the returned table every time. Anybody help me please... KiT

leppie

KiTsuNeKo wrote:

if (conn.State == ConnectionState.Open) { conn.Close(); }

Rather close the connection after your query. xacc-ide 0.0.99-preview7 now with C#, C, C++, IL, XML, Nemerle, IronPython, Perl, Caml, SML, Ruby, Flex, Yacc, Java, Javascript, Lua, Prolog and Boo highlighting support!

Rob Graham

You are creating a new connection object each call to the function, and you're not closing it when done (garbage collection will eventually do so, but this is not good practice. try this:

public class MyClass
{
	private SqlConnection conn = new SqlConnection(this.SQLExpressConnStr);
	public string SQL2KConnStr = "..(my connection string)..";
	public object ExecuteSql(string sql)
	{
		
		if (conn.State != ConnectionState.Open)
		{
			conn.Open();
		}
		switch(sql.Trim().Substring(0,6).ToLower())
		{
			case "select":  
				try
				{
					SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
					ds = new DataSet();
                                            adapter.Fill(ds, "QueriedTab");
				}
				finally
				{
					conn.Close();
				}
				return ds.Tables\["QueriedTab"\]; //Return a data table.
				break;   
			default:      
				SqlCommand cmd = new SqlCommand();      
				//cmd.CommandType = CommandType.Text;      
				cmd.Connection = conn;      
				cmd.CommandText = sql; 
				long rtn = 0;
				try
				{
					rtn = cmd.ExecuteNonQuery(); //Return a number of affected rows.
				}
				finally
				{
					conn.Close();
				}
				return rtn;
				break;
		}																																														   
	}
}

Only create the connection once, open and close it with each use. You might want to add catch(SqlException e) blocks before each finally block to handle errors. probably want to move the declaration of the dataset to class or function scope (out of the try block) Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke

AesopTurtle

I've tried the solution but the problem still exists... T-T There're only 130 records in the database and it shouldn't take so long to query them all. KiT