|
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"];
break;
default:
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
return cmd.ExecuteNonQuery();
}
}
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
|
|
|
|
|
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!
|
|
|
|
|
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"];
break;
default:
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
long rtn = 0;
try
{
rtn = cmd.ExecuteNonQuery();
}
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
|
|
|
|
|
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
|
|
|
|
|
 Dear Experts,
I installed my own created Windows Service by using the command "installUtil". Now I would like to un-install that service but I don't know how to do so. Could you please advise?
Many thanks.
Tigger
|
|
|
|
|
you need to pass an extra parameter to installutill
That might be -u
- ashish
|
|
|
|
|
i've been researching for a technique on how to create an excel-compatible dbf file. so far, i've downloaded a sample program in http://www.c-sharpcorner.com/Code/2002/Feb/xBaseEngineRFV.asp.
the output file is good and can be opened in visual foxpro 9, but my problem is my client wants to open it in excel. he's used to opening dbf's in excel.
can somebody help me please?
i'm not always on here, so please email your answer to xmione@yahoo.com
tnx
Solomio S. Sisante
c# Senior Developer
|
|
|
|
|
In MFC you can assign a pointer or other 4 byte value to each item in a list control via GetItemData / SetItemData. Is there an equivilent in dot net?
|
|
|
|
|
I think I figured it out. You add your actual object to the list control and then override the ToString() on that object rather than Items.Add(object.ToString());
pretty cool
|
|
|
|
|
or you can go one better and create your own listitem object that inherits listitem and add your own public propeties
like this
public class MyListItem : ListViewItem
{
public string myvalue;
}
then use MyListItem class when creating ListviewItems and you'll have access to myvalue!
C# is very cool
-= Technomancer =-
|
|
|
|
|
Thank you.
Can you do in place editing of List Box Items?
|
|
|
|
|
Hi!!
I have a call to my data access class and I do it like this (from my displayNews.aspx page:
News objNews = NewsDA.GetNewsDetails(m_iNewsID);
m_iNewsID is the id number which I got from the query string
And my GetNewsDetails in my NewsDA class looks like this:
public static News GetNewsDetails(int intNewsID)
{
News objNews = new News();
// Create instance of connection and command object
SqlConnection objConn = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
SqlCommand objCmd = new SqlCommand("sp_GetNewsByID", objConn);
// Mark the command as a SPROC
objCmd.CommandType = CommandType.StoredProcedure;
// Add parameters to SPROC
SqlParameter paramNewsID = new SqlParameter("@NewsID", SqlDbType.Int, 4);
paramNewsID.Value = intNewsID;
objCmd.Parameters.Add(paramNewsID);
// Execute the statement
try
{
objConn.Open();
SqlDataReader objDr = objCmd.ExecuteReader();
try
{
while (objDr.Read())
{
objNews.NewsID = Convert.ToInt32(objDr["PKNewsID"]);
objNews.CategoryID = Globals.CheckDBIntForDBNulls(objDr["FKNewsCategoryID"]);
objNews.Title = objDr["NewsTitle"].ToString();
}
}
finally
{
if(!objDr.IsClosed)
{
objDr.Close();
}
}
}
finally
{
if(objConn.State == ConnectionState.Open)
{
objConn.Close();
}
objConn.Dispose();
objCmd.Dispose();
}
// Return the news object
return objNews;
}
If no records are found, then I should probably set objNews to null?? And the where I call this function, then I should probably test if the news object is equal to null?? I was thinking of putting in an if statement there in the second try block, if the dataset has rows, then do the while statement, otherwise set the objNews = null??
Please can some one advise. If there is a better way of testing if an object of News has any records (rows) in it, please let me know.
Regards,
ma se
|
|
|
|
|
Use a DataAdapter to fill a Datatable/Dataset.
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!
|
|
|
|
|
Yes, but I am not returning a lot of records, just one based on the ID, so won't the data reader be the best way?
If I were to use the data reader, where and how would I test if any records were found?
|
|
|
|
|
If you are going to put the data into your business object (News) then I do not suggest using a SqlDataAdapter to fill a DataTable/DataSet because all you are going is wasting time creating extra copies of the data that you will throw away. (The SqlDataAdapter uses a SqlDataReader internally to populate the DataTable/DataSet)
You could look at the SqlDataReader.HasRows[^] property.
At the top of your method replace
News objNews = new News(); with
News objNews = null; . Wait until you perform the ExecuteReader() then, inside your try block (replacing the while loop), do something like this:
if (objDr.HasRows)
{
objNews = new News();
objDr.Read()
objNews.NewsID = Convert.ToInt32(objDr["PKNewsID"]);
objNews.CategoryID = Globals.CheckDBIntForDBNulls(objDr["FKNewsCategoryID"]);
objNews.Title = objDr["NewsTitle"].ToString();
}I took out the while because it wasn't really doing anything useful. If you expect only one row to be returned then the above will be fine. If you expect more than one row returned and you are just looking for the results of the last row then your old while loop will work, but it suggests there is something wrong with the SQL that it is returning more than it should.
Now, if no rows are returned from SQL Server, a null is returned from the method.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Hi..
Im doing a user interface using C# web application program. When i add a new user to database, after adding it goes to the next page and i can choose to edit and confirm add user.. But my problem here is if i wanted to edit i created a edit button and goes to my previous page. How can i maintain the previos page particulars?? Coz i have tried clicking it and when it return to the previous page the particulars i fill in has lost.. Thankz.. =)
Smile alwyz =p
|
|
|
|
|
You can't maintain state between pages without using session variables, and you should avoid it. Instead, pass a parameter on the URL which your page then uses to restore it's state from the database.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
How collections (Queue/Stack/Hashtable...) store complex object?
Do they store object reference or object gets serialize?
Appreciate your help.
- ashish
|
|
|
|
|
I'm pretty sure they just store a reference to the object, I know that types such as int get boxed to be stored in a C# 1.1 container.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
It means if I dispose the object instance, the reference in Queue will return false values
Is there any way I can store the full object?
- ashish
|
|
|
|
|
AshishBasran wrote: It means if I dispose the object instance, the reference in Queue will return false values
Of course. No different to a vector in C++, if you store a pointer, don't delete it elsewhere.
AshishBasran wrote: Is there any way I can store the full object?
Don't dispose of stuff you want to keep. I don't see the problem here.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Depends on what you store in the collection. Value types (structs and other primitive data types except string) get boxed, which implicitly creates a copy of the original object. Reference types (classes) are stored by reference.
Regards
Senthil
_____________________________
My Blog | My Articles | WinMacro
|
|
|
|
|
Hi, This is Deepak. Friends I need you help. I just want to open the View Source Code of a HTML File through C# Code. Please Help me ASAP.
Deepak
|
|
|
|
|
Open your HTML file as text should give you the source code.
Cheers!!!!
- ashish
|
|
|
|
|
Hi
I have an application that has many different forms. All these forms use a certain kind of a TreeView that has drag/drop rules, copy/paste rules and other functionalities. I'm trying to create one inherited TreeView control instead of doing this for every TreeView in my application. Where do I start? How do I go about accomplishing that?
Thank you for your help.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
