Database

Re: Server -- keep connection open? Or open/close as needed?

9-Dec-04 6:32

I have a server app that opens a connection to the database, does some SQL queries, then closes the connection.

I'm wondering if simply keeping the connection open for the duration of the server's lifetime is a better idea. Any opinions?

My thinking behind this: opening and closing the connection in a multithreaded server with an asynchronous MSDE connection is difficult to do; it'd be easier just to keep the connection open at all times. What do you guys think?

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

Alex Korchemniy9-Dec-04 10:55

Re: Server -- keep connection open? Or open/close as needed?

9-Dec-04 10:55

Bad bad!

Always close the connection. There is no point in tying up server resources. Besides, framework does connection pooling for you. Smile | :)

This posting is provided "AS IS" with no warranties, and confers no rights.

Alex Korchemniy

Judah Gabriel Himango9-Dec-04 11:45

Re: Server -- keep connection open? Or open/close as needed?

9-Dec-04 11:45

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();
   // do some lengthy operation here
   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

Mike Dimmick10-Dec-04 2:28

Mike Dimmick

10-Dec-04 2:28

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

Re: Server -- keep connection open? Or open/close as needed?

Judah Gabriel Himango10-Dec-04 4:29

Re: Server -- keep connection open? Or open/close as needed?

10-Dec-04 4:29

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

Dave Kreskowiak10-Dec-04 9:56

Dave Kreskowiak

10-Dec-04 9:56

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

Re: Server -- keep connection open? Or open/close as needed?

Judah Gabriel Himango10-Dec-04 10:00

Stored Procedure using Disconnected Recordset

10-Dec-04 10:00

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

web_app_developer9-Dec-04 4:00

web_app_developer

9-Dec-04 4:00

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. Blush | :O

<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
%>

<%
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>

Re: Stored Procedure using Disconnected Recordset

tojamismis10-Dec-04 12:42

tojamismis

10-Dec-04 12:42

Try specifying your cursor as forward only, and use Batch Optimistic locking. Not sure if it'll work, but it's worth a shot.

SQL Server 2000 / Access

| Muhammad Waqas Butt |9-Dec-04 1:08

| Muhammad Waqas Butt |

9-Dec-04 1:08

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

Insert photos and files

CNU8-Dec-04 14:46

CNU

8-Dec-04 14:46

Hi,
I am a newbie to SQL and all I know is insert, delete and update Smile | :)

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

Alex Korchemniy8-Dec-04 16:55

8-Dec-04 16:55

Take a look at this article : Uploading and downloading pictures from a SQL server[^]

This posting is provided "AS IS" with no warranties, and confers no rights.

Alex Korchemniy

CNU8-Dec-04 17:39

CNU

8-Dec-04 17:39

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

Alex Korchemniy9-Dec-04 10:53

9-Dec-04 10:53

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

Mekong River9-Dec-04 4:18

Mekong River

9-Dec-04 4:18

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

SQL query

Paps28-Dec-04 3:49

Paps2

8-Dec-04 3:49

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

Re: SQL query

Yulianto.8-Dec-04 15:58

Yulianto.

8-Dec-04 15:58

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.

Re: SQL query

Alex Korchemniy8-Dec-04 17:02