|
Thanks for the reply. As I mentioned in the first post, my current code opens and closes on demand, just as you suggest. However, once I've got simultaneous SQL commands being executed/queried, what then?
For example, consider the following:
void DoTwoThingsAtOnce()
{
Thread reader = ReadSomeStuff();
Thread doer = DoSomeStuff();
reader.Start();
doer.Start();
}
SqlConnection connection;
void ReadSomeStuff()
{
connection.Open();
...
connection.Close();
}
void DoSomeStuff()
{
connection.Open();
connection.Close();
}
The above code will begin executing ReadSomeStuff and DoSomeStuff both on different threads. When ReadSomeStuff finishes, it will close the connection -- but alas, DoSomeStuff is not finished with the connection, so closing it will cause DoSomeStuff to fail.
Should I instead be creating connections objects for each operation?
Any remotely useful information on my blog will be removed immediately. There are 10 kinds of people in the world. Those who have heard of the ubiquitous, overused, worn-out-like-an-old-shoe binary "joke" and those who haven't.
Judah Himango
|
|
|
|
|
Yes, you should have separate connection objects. A connection is normally associated with some resource - for example, in SQL Server a connection is associated with a 'server process', which is itself tied to a User Mode Scheduler thread, which can only run on a single CPU.
Your concurrent operation here is a really bad idea as SqlConnection objects are not thread-safe. You're likely to corrupt the state of the object entirely if you call Open simultaneously on both threads. Even if you passed an opened connection to both threads, they're likely to stomp all over each others' internal TDS buffers. The SQL Server Managed Provider does all the high-level command formatting in managed code, then uses the Net-Library stack to submit the command to SQL Server.
Use SqlConnection's connection pooling. The cost of retrieving an existing connection from the pool is minimal.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks, that's exactly the information I needed. Yeah, as far as the concurrent operations go, I was simply locking the connection object, and doing synchronous reads and writes.
So to retrieve an existing connection from the connection pool, do I just call connection.Open again? Or is there some other specific way of doing this?
*edit* nevermind, MSDN says yes, connection.Open does in fact use pooling as long as the connection string is the same. Thanks for your help Mike.
Any remotely useful information on my blog will be removed immediately. There are 10 kinds of people in the world. Those who have heard of the ubiquitous, overused, worn-out-like-an-old-shoe binary "joke" and those who haven't.
Judah Himango
|
|
|
|
|
That's the worst possible thing you could do... Open the connection only for as long as you need it, then close as soon as possible. There is no excuse or reason to ever hold a connection open if your not using it.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Right, and my previous code was in fact opening/closing as needed; I guess I was asking for options in the face of asynchronous operations more than anything. It looks like connection pooling gives me what I want, and that is what I'll be using. Thanks for your reply.
Any remotely useful information on my blog will be removed immediately. There are 10 kinds of people in the world. Those who have heard of the ubiquitous, overused, worn-out-like-an-old-shoe binary "joke" and those who haven't.
Judah Himango
|
|
|
|
|
Is it possible to use a Stored Procedure with a Disconnected Recordset? The code below gives me the error: <b>Operation is not allowed when the object is open.</b>
I have been trying to figure this out for a few hours now... and am hoping someone can give me some guidance.
<code><% @Language = "VBScript" %>
<%
Option Explicit
Response.Buffer = true
Response.Expires = 0
Dim objConn
Dim objCmd
Dim objParams
Dim objRs
Dim item
Dim rsFirstName
Dim rsLastName
%>
<!-- #include file="_inc/adovbs.inc" -->
<%
Set objConn = Server.CreateObject("ADODB.CONNECTION")
objConn.Open(Application("StoredProcedures_ConnectionString"))
Set objCmd = Server.CreateObject("ADODB.COMMAND")
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "usp_authors_perm"
objCmd.CommandType = adCmdStoredProc
Set objParams = objCmd.CreateParameter "au_lname",advarChar,adParamInput,40)
objCmd.Parameters.Append objParams
objParams.value = "m"
Set objRs = Server.CreateObject("ADODB.RECORDSET")
objRs.CursorLocation = adUseClient
Set objRs = objCmd.Execute
Set objRs.ActiveConnection = Nothing 'Problem occurs here: Operation is not allowed when the object is open.
objRs.Close
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Set rsFirstName = objRs("au_fname")
Set rsLastName = objRs("au_lname")
objRs.CacheSize = 10
while NOT objRs.eof
Response.Write rsLastName & "<br/>"
objRs.MoveNext
wend
%></code>
|
|
|
|
|
Try specifying your cursor as forward only, and use Batch Optimistic locking. Not sure if it'll work, but it's worth a shot.
|
|
|
|
|
Hi,
Does any body know how to take SQL Sever 2000 or Access Database backup in c#.net? Mean what program we write in c#.net to take whole database [Table,Relation etc] backup.
Please help me...
Muhammad Waqas Butt
waqasb4all@yahoo.com
Muhammad Waqas Butt
|
|
|
|
|
Hi,
I am a newbie to SQL and all I know is insert, delete and update 
I have to upload some resources (images and files) to the database. For this purpose I have created a column with datatype varbinary and length 8000.
But I have some problems in this process :
1. Is there any way that we can upload files of size more then 8000 ?
2. I tried uploading a file of size 2393 KB on disk. But I get the following error:
System.Data.SqlClient.SqlException: Cannot create a row of size 8077 which is greater than the allowable maximum of 8060.
This is how I create the byte array :
FileStream fs = new FileStream(aFilePath, FileMode.Open);
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, 0, (int)fs.Length );
fs.Close();
this is how I upload to db :
SqlParameter sqlParam = new SqlParameter("@" + fieldName, sqlDbType.VarBinary, 8000);
sqlParam.Value = buffer;
mySqlCommand.Add(sqlParam);
mySqlCommand.ExecuteNonQuery();
Sorry, the above code is in bits and pieces since I cannot put all here...
Please help me...
Cheers
CNU
|
|
|
|
|
|
Thanks a lot...
I changed the sqlparameter staement by removing the size. It is working now.
But I have another problem....
This is how I am displaying the downloaded image on a lable.
System.IO.MemoryStream ms = new MemoryStream();
ms.Write(byteArray, 0, byteArray.Length);
ms.Flush();
this.lblImage.Image = System.Drawing.Image.FromStream(ms);
ms.Close();
The exception is as follows:
System.ArgumentException: Invalid parameter used.
at System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement, Boolean validateImageData)
at System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement)
at System.Drawing.Image.FromStream(Stream stream)
at InitBasicInfo() in d:\projects\tc project\tcstudio\frmservermodel.cs:line 769
Thanks.
Cheers
CNU
|
|
|
|
|
You are trying to hard to construct your MemoryStream! This works just fine:
System.IO.MemoryStream ms = new MemoryStream(byteArray);
As a sidenote, ms.Flush() is overriden to do nothing at all.
This posting is provided "AS IS" with no warranties, and confers no rights.
Alex Korchemniy
|
|
|
|
|
I have an expereince similar to you. But what I have don't is working with access database. I go the tip from my teacher that don't store an object (if you want to store an image/picture) in your database. Because it would increase a lot of space when your record increase. The best way to do this just create a field with text string and hold the path that point to the picture file. When you load the record on the form just load the picture in the picture box base on the path that you store in the database. This will decrease a lot of space from your database and improve a lot of database performance. I also test it and it is work very well.
But remember! if you use the method from me, becareful when you image is change it file name or file path. You have to handle the RUN TIME error in your application to do that.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Hi all,
Why wont my sql query work.
It returns with no records.
SELECT pkey, description
FROM Test
WHERE (((test.description) Like 'DEL%'))
Yet there is a record with 'DELIVER' in the description col of the Test table.
Tnx
|
|
|
|
|
Try testing it with the SQL builder
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Test your query in Query Analyzer.
By the way those parenthesis are extra 
This posting is provided "AS IS" with no warranties, and confers no rights.
Alex Korchemniy
|
|
|
|
|
I have been trying to get some batch files to create and show tables details, but the only file I can get to work is one that populates a table;
i.e. load data local infile C:\\mysql\\data\\testDB\\data.sql"
I have created a few batch files as follows:-
File 1 (creatTest.sql)
CREATE TABLE Test(name varchar(15), price float(6,12)); and
File 2 (showTables.sql)
show tables; If I now try and run the showTables.sql batch file: e.g
mysql> source showTables;
I get error 2 if I miss out the path or
this gives the error Unknown command '\\' (for each '\\') if I use
mysql> source C:\\mysql\data\\mydb\\showTable.sql;
Is it possible to create/run batch files and are they any good examples (for Windows) please!
Does anyone have sample batch files to run for mySQL 4.1 in a Windows environment.
|
|
|
|
|
Are you saying that the SQL Server is on another machine, or the same machine?
You can generally specify a host name for the server, and your client having a static or dynamic IP is largely irrelevant, providing the name can be resolved (relies on either DNS or HOSTS lookup), and there is a network path available.
Is it possible that there's a firewall in the way which is blocking the SQL Server port?
Steve S
Developer for hire
|
|
|
|
|
Try to check the connection library[^] for the method that you should be connect to your SQL server database. On the other hand, your application should be able to give an opportunity for the user to type in the database server that you database reside on. This method could be reduce an error in your application.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Hi,
I'm writing an windows application that access SQL SERVER database. But I have a problem when trying to run the program in a computer which has an internet connection with dynamic IP, it can't connect to the SQL database.
Is anyone have solutions for this problem?
thanks b4
|
|
|
|
|
i`m looking information about progress database, anybody ever use it? What kind of database is it? I`ve look it at google, but couldn`t find what I need. Thanks
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
|
Hi all,
Help – to be honest I’m not sure if this is an oracle question or a .net one, I am trying to invoke a job in my oracle 9i db from a web app. The job is created fine and runs fine when scheduled in oem. However I want to run the job from my vb.net web app. I have imported a dll, oemjobcreator, and invoked the Run command passing in what I believe to be the correct parameters but the job does not run. How do I invoke the job, or define the schedule from code? Has anyone tackled the problem of invoking oracle jobs from code?
Cheers,
Rob
|
|
|
|
|
I'm trying to insert a Hashtable in my database, and the way I found was first to serialize the Hashtable to a file before inserting the file (as a BLOB) into the database.
Is there a direct way to do this? That instead of going Object->File->BLOB, I can go from Object to BLOB directly.
Rafferty
|
|
|
|
|
Depends what your programming platform is. For instance, in C++ using OLE DB, you could serialise to an IStream object (or to memory and then create a stream on it), and use the IStream object directly from the provider...
Steve S
Developer for hire
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
