|
I have a class to which I can pass any SQL query and it can return “DataReader” object or “DataSet” etc. To achieve this I have different methods in that class.
What is the best way to use database connection in ASP.NET application? I would like to use connection pooling too. Database is SQL Server 2000. Should we use singleton class in this, if yes, how can we use?
I appreciate your help.
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
- ashish
|
|
|
|
|
Connection pooling in ASP.NET happens all by itself, you can just create connections as you need them, I believe.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Yes, while using SqlClient and in connection string saying "connection pooling=true" automatically create connection pool for the same connection string.
What if there are multiple threads needs connection at same time? The error I get is, all the connection in connection pool are in use, and connection timed out. So I thought that this should not be the best way to use ADO.NET in ASP.NET
- ashish
|
|
|
|
|
You can change how many connections are in the pool, I believe. Other than that, I'm not sure. We just create connections as we need them and don't have any troubles.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
You can also specify "Max Pool Size" and "Min Pool Size" to adjust your pool. But I think this is specific to SQL Server only.
Thanks.
modified 17-Jul-19 21:02pm.
|
|
|
|
|
Here's my code:
public void dgTickets_Update(Object sender, DataGridCommandEventArgs e){<br />
string tdate, tnum, tcID, tter, tsc, tcu, tloc, tinfo, tid;<br />
<br />
tdate=((TextBox)e.Item.Cells[1].Controls[0]).Text;<br />
tnum=((TextBox)e.Item.Cells[2].Controls[0]).Text;<br />
tcID=((TextBox)e.Item.Cells[3].Controls[0]).Text;<br />
tter=((TextBox)e.Item.Cells[4].Controls[0]).Text;<br />
tsc=((TextBox)e.Item.Cells[5].Controls[0]).Text;<br />
tcu=((TextBox)e.Item.Cells[6].Controls[0]).Text;<br />
tloc=((TextBox)e.Item.Cells[7].Controls[0]).Text;<br />
tinfo=((TextBox)e.Item.Cells[8].Controls[0]).Text;<br />
tid=((TextBox)e.Item.Cells[9].Controls[0]).Text;<br />
<br />
string sSqlCmd = "UPDATE MCI_Tickets SET t_date = ?, t_num = ?, t_circuitID = ?, t_ter = ?, t_sc = ?, t_cu = ?, t_loc = ?, t_info = ? WHERE t_id = ?";<br />
SqlConnection updCon = new SqlConnection(Conn);<br />
SqlCommand updCmd = new SqlCommand(sSqlCmd, updCon);<br />
<br />
updCmd.Parameters.Add(new SqlParameter("@t_date", SqlDbType.Char, 10));<br />
updCmd.Parameters["@t_date"].Value = tdate;<br />
updCmd.Parameters.Add(new SqlParameter("@t_num", SqlDbType.Char, 5));<br />
updCmd.Parameters["@t_num"].Value = tnum;<br />
updCmd.Parameters.Add(new SqlParameter("@t_circuitID", SqlDbType.Char, 15));<br />
updCmd.Parameters["@t_circuitID"].Value = tcID;<br />
updCmd.Parameters.Add(new SqlParameter("@t_ter", SqlDbType.Char, 5));<br />
updCmd.Parameters["@t_ter"].Value = tter;<br />
updCmd.Parameters.Add(new SqlParameter("@t_sc", SqlDbType.Char, 6));<br />
updCmd.Parameters["@t_sc"].Value = tsc;<br />
updCmd.Parameters.Add(new SqlParameter("@t_cu", SqlDbType.Char, 6));<br />
updCmd.Parameters["@t_cu"].Value = tcu;<br />
updCmd.Parameters.Add(new SqlParameter("@t_loc", SqlDbType.NVarChar, 50));<br />
updCmd.Parameters["@t_loc"].Value = tloc;<br />
updCmd.Parameters.Add(new SqlParameter("@t_info", SqlDbType.NVarChar, 50));<br />
updCmd.Parameters["@t_info"].Value = tinfo;<br />
updCmd.Parameters.Add(new SqlParameter("@t_id", SqlDbType.Int, 4));<br />
updCmd.Parameters["@t_id"].Value = tid;<br />
<br />
updCon.Open();<br />
updCmd.ExecuteNonQuery();<br />
updCon.Close();
I keep getting the error:
Incorrect syntax near '?'
all the values show correct when i run through the Locals in the debugger. Would it be a problem with the database?
TIA,
Scott
|
|
|
|
|
sonicsqwirl wrote:
string sSqlCmd = "UPDATE MCI_Tickets SET t_date = ?, t_num = ?, t_circuitID = ?, t_ter = ?, t_sc = ?, t_cu = ?, t_loc = ?, t_info = ? WHERE t_id = ?";
sonicsqwirl wrote:
Incorrect syntax near '?'
Obviously, the ? are still in the code. I've never seen this syntax anywhere, do you have a reference to indicate that it should work ?
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
yes we used it in school on an application that updates an Access database ex. Code:
string sSqlCmd = "UPDATE Parts SET Name = ?, Description = ?, Price = ? WHERE PartID = ?";<br />
<br />
updCmd.Parameters.Add(new OleDbParameter("@PartDescription", OleDbType.VarWChar, 100));<br />
updCmd.Parameters["@PartDescription"].Value = sPartDescription;<br />
updCmd.Parameters.Add(new OleDbParameter("@PartPrice", OleDbType.Currency));<br />
updCmd.Parameters["@PartPrice"].Value = sPrice;<br />
updCmd.Parameters.Add(new OleDbParameter("@PartID", OleDbType.Integer));<br />
updCmd.Parameters["@PartID"].Value = sPartID;<br />
<br />
updCon.Open();<br />
updCmd.ExecuteNonQuery();<br />
updCon.Close();
any ideas on how i can do this differently?
|
|
|
|
|
Well, the most obvious way would be to use a stringbuilder to build the SQL, or even the string.Format method. I would have expected to only add parameters when calling a stored procedure. If a stored proc is an option here, I'd go with that instead.
I've just checked, and MSDN says this:
The .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to a Transact-SQL statement or a stored procedure called by a command of CommandType.Text. In this case, named parameters must be used. For example:
SELECT * FROM Customers WHERE CustomerID = @CustomerID
In other words, if you change the ? to the same paramater names as the names you're using to add paramaters, then this approach will work, and is probably the best way to go outside of using a stored procedure. The main problem with this approach is that it opens you to SQL Injection attacks.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Isn't that the syntax for using the ODBC adapter with named paramters?
|
|
|
|
|
Yes, so I learned. I've never used the ODBC adapter before. Beats me why it differs.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
After installing the Oracle ODT I find that now I can access all my Oracle 10 databases perfectly, but the older Oracle 8i systems are no longer available.
I understand this is a known problem, has anyone else encountered this and knows of a work around ?
Thanks
|
|
|
|
|
One thing to look at is the tnsnames.ora files. The ODT stuff creates a new set for 10g (separate from the ones for 9i etc.), and they may be different but the 10g version is sometimes the only one used after it is created. If the name resolutions of all your databases are not in the 10g version of tnsnames.ora, then you might consider merging the tnsnames.ora files.
Another potential problem is that ODT also creates a sqlnet.ora file that works sort of like your hosts file (helps resolve server names). In my case, the sqlnet.ora file had incorrect domain information that was overriding what was in my tnsnames.ora file and causing server names not to resolve. After some fiddling with the file, I finally just deleted it, reasoning that it had not been there prior to ODT, and everything had worked fine anyway. Everything worked after that.
Hope this helps.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
hi guys,
i have been playing with the DSOFile component from MS. One of the things I realised it couldnt do was to read properties of PDF documents. So I have managed to rectify this by simply using the shell32.dll and using the GetFileInfo method from that dll.
However, my problem is that how do i save the summary file properties of a PDF document if I am using shell32.dll ??
I have tried looking through MSDN but couldnt find anything. Any suggestions will be appreciated.
Thanks
|
|
|
|
|
I need to load several forms in the background of an MDI app. My problem is that the forms display themselves when I try to read a property from them. I want them to be fully loaded but invisible until the time they are needed.
Is it possible to fully load a form without having it display? The code I'm starting with looks like this:
long hndl;
System.Windows.Forms.Form frmIt = new Form();
frmIt.MdiParent = this;
frmIt.Visible = false;
//not yet visible
//read the handle
hndl = (long) frmIt.Handle;
//form is now visible.
Thanks,
|
|
|
|
|
Is this a C# question ? If so, set up delegates between the forms, one that children use to tell the parent of the change, and one the parent uses to notify all children.
If it's C++, you can do the same with function pointers.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Well this thread just goes to prove the deleting posts is a bad idea. Non of the original posters posts exist now.
How can we help people if they delete the questions when they don't like the answers given.
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
Here[^] is a tutorial on delegates. Basically, you create a varaible which represents a method call ( actually a chain of method calls ), to a specific signature. So you create a variable in the child form which represents an event. On the main form, you create this event, and every time a child form is created, it sets it's variable to be that main form event. So, when a variable changes in the child, it calls the event, which is a method *on the main form*. So, the main form get's called, and it can then notify all the child forms however it likes.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
No my friend is not a bad idea. If you just read the replies you will see how "good" the answers are. There are no answers.
|
|
|
|
|
Nick932 wrote:
No my friend is not a bad idea. If you just read the replies you will see how "good" the answers are. There are no answers.
You are, hands down, the stupidest person ever to post here. My original post answered the question for any programmer on the planet. If cut and paste is your thing, if you're illiterate, then yeah, my answer was not useful. Even then, I tried to keep the dialog going so I could help further, and you acted like a spoiled child, as if you PAID me for code and all I offered you was good advice on how to write it yourself.
Then, when no-one here takes your side, you continue to assume that even though you're too dumb to write 10 lines of basic event handling code in C#, you're the only one here who has any idea, so you delete all your posts, like a crying baby.
Man, I can't believe you're smart enough to turn on a computer, let alone use the Internet.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hey Christian, thanks for the great tutorial, helped me a lot! 
"..Commit yourself to quality from day one..it's better to do nothing at all than to do something badly.."
-- Mark McCormick
|
|
|
|
|
The forms are irrelevant. Events work the same in all classes. To watch the change of a variable, make it a property, so you can write code that is called when it changes. Then, fire your events, which are created and subscribed to in accordance with the sample I gave you. If you look for model view controller in this site, there's at least one C# sample using delegates to create this design pattern, and it's sure to be a forms app, although that just adds a lot of code that distracts from what you need to see, which is how delegates work.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
ROTFL - glad it helped someone....
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
He deleted them all almost as soon as he realised that I was suggesting he read some stuff for himself instead of asking me for a full code example.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
You know what's really funny ? He's asked three questions before this one ( I answered at least one, about events as it happens, and he post voted me a 5 for the answer ), this morning his post count was in the mid twenties, and now it's down to four ( three once he deletes his reply to this post of yours ).
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
