Building a DLL (Is there a better way?) 1

[Antithott]

I am building a Dll file for communication between a SQL server and a Asp.net website.

Basically im putting all calls to the SQl in this DLL file, contained in the following way.

ClassName
 - Connection
   - OpenConnection
   - CloseConnection
   - ConnectionStatus
 - Queue
   - GetQueueId
   - GetQueueCalls
 - User
   - GetFullName
   - GetUserName

Then i started to wonder, if this is the correct way to do it? First i Open the connection, then i do all my DB work, and then Close the connection, using the connectionstatus to determine if connection is open already.

In my mind i assume it would be correct because of only 1 connection to the DB, but is it better to actually open and close a connection for each idividual call to the DB?

My second problem is that, the website i use this DLL File with, is just 1 page, refresing every 5min thru the Ajax Updatepanel.

At the refresh of the page, it opens 1 connection to the DB retrieve all the info then close connection.

I Dispose all variables at end, but still my worker process on the server is at 46mb after 2-3 days running.

I hope someone can help me with the worker process or just give an idea how to do the DLL file in a better way.

 

[Sorwen]

Then i started to wonder, if this is the correct way to do it? First i Open the connection, then i do all my DB work, and then Close the connection, using the connectionstatus to determine if connection is open already.

In my mind i assume it would be correct because of only 1 connection to the DB, but is it better to actually open and close a connection for each idividual call to the DB?

That depends.

1) How much work and how long between connections to the SQL?

2) Will anyone or anything else possibly need to make a connection to those tables?

2b) If they will do they need to have the updated data before they make a connection or will they just reconnect later and get the new data/changes.

3) It really depends on what you are actually doing.

4) It really depends on what you are actually doing.

If you gave at least a general idea of what you are processing then I could give you a more specific answer. Generally speaking though you would need to ask yourself those two(b) questions. Often there may not be a right or wrong answer. There can be a lot of reasons to leave the connection open or open/close it.

My second problem is that, the website i use this DLL File with, is just 1 page, refresing every 5min thru the Ajax Updatepanel. 

At the refresh of the page, it opens 1 connection to the DB retrieve all the info then close connection. 

I Dispose all variables at end, but still my worker process on the server is at 46mb after 2-3 days running.

I hope someone can help me with the worker process or just give an idea how to do the DLL file in a better way.

I'm not sure I can be much help on this one as I've not done the whole webpage with programming thing. On thing that comes to mind though is if you instancing a class from that dll make sure you dispose of that instance and not just disposing the variables in the class (if they are instanced and not just shared).

-I hate Microsoft!
-Forever and always forward.

 

[Antithott]

First thanks for the reply, and sorry for not answering quicker. I´ll answer your question and hopefully you could help me better as you wrote.

1)  How much work and how long between connections to the SQL?

Atm, it will refresh a webpage every 5minuts. The computer its open on, is online 24/7.

It gathers information for 16 diffrent users, 1 query pr user to find amount of open cases.

2) Will anyone or anything else possibly need to make a connection to those tables?

There will be up to 96 other users, but average is about 42 online at all times, which work with the same tables, doing Updates and query´s everyday in normal working hours.

2b) If they will do they need to have the updated data before they make a connection or will they just reconnect later and get the new data/changes.

The monitor site only collects data and it doesn´t matter if the information is updated or not. It just needs to check the status every 5 min.

this brings me to another question, about how to return values correctly from a function.

I query the SQL and get 10 rows, each row i put in a string variable, and then do a return StringName.split("$"), im not sure this is the correct way to handle the return of data.?

Again thanks for any help on this.

 

[macleod1021]

If you're using ADO, then the easiest way is to return a DataTable object (if you're able to...again...not a lot of info to go off of). With the DataTable object you can move back and forth between DataRows.

One question I have is that you are using a .split method on your data. Is that how the database is storing the data? (ie "firstname$lastname")?

 

[Sorwen]

I would think it would be easier to do like macleod1021 said and just pass back the same type of object you pull the data into. Either and ADO or ADODB DataTable. Some times I dump the data into a collection, but that is preference. I would say it is a little cleaner to work with than dumping it all into a string especially if you are just going to pull it all back out again later.

Remember this all my opinion, but I would say open/close your table each time. Other applications are going to be dumping data into those tables off/on all day you want to make sure you lessen the chance of those tables getting locked. Even if it seems unlikely it is always best to avoid any chance things going wrong.

-I hate Microsoft!
-Forever and always forward.

 

[Antithott]

macleod1021:
The data is structure like this.
|FirstName|LastName|etc|etc|etc|

I collect the data thru SqlClient.SqlDataReader and then pass it into 1 string, i know it might not be the best way, but atm that is what i use.


Sorwen:
Thanks for the answer, ill keep open/close the DB access each time i collect data i think.

I'll give the Collection a try too.

 

[macleod1021]

Since you are using the SqlDataReader (and I assume a SqlClient.Connection object) it's typically better to call the close method after each access. When you close the connection...it's not REALLY closed. The framework simply puts it back into the pool of connections that are available to you (IMS, the default is 100 connections max).