Database connection

[0x3c0]

Connection pooling basically caches the connection. It removes most of the overhead from creating a connection. Most of the database drivers use it. The main advantage of this is speed; there's no need to create a connection and read the data when the database driver's got some of the data already cached

[EmZan]

oh yee i got it it remembers me deferred execution Linq in .net3.5 deferred execution doesn't have connection pooling :) thanks a lot

[Colin Angus Mackay]

Option 2 always. I can't think of a good enough reason for option 1.

*Developer Day Scotland - Free community conference Delegate Registration Open

[Colin Angus Mackay]

Member 4697348 wrote:

it [connection pooling] remembers me deferred execution Linq in .net3.5

What has deferred execution got to do with connection pooling?

*Developer Day Scotland - Free community conference Delegate Registration Open

[Fernando Soto]

Option 2 is the way to go. No need to tie up system resources when it is not being used.

[EmZan]

deferred execution doesn't keep data retrieved from database at RAM so at every time you need somthing u will get the database data not the memory data that is good when ur database is being updated a lot which is an advantage of linq

[Colin Angus Mackay]

That was not my question. My question was "What has deferred execution got to do with connection pooling?" While deferred execution has the effect you describe for LINQ to SQL, deferred execution is not about keeping whether the data is in RAM or not. Deferred execution is about running the LINQ query at the point you want the data from it, not at the point you define the query. If you run your LINQ query on objects that exist only in RAM then it will filter based on the most recent state of the objects rather than the state they were in when you defined the query.

*Developer Day Scotland - Free community conference Delegate Registration Open

[EmZan]

yes , but that is for link to objects but in link to sql the linq queries deals directly with database with no lookong for data at RAm at first. am i right??

[Colin Angus Mackay]

EmZan wrote:

but that is for link to objects

It is for all types of LINQ. I used LINQ to Objects as an example of how it works when all the data is in RAM. The principle is the same regardless of where the data is. That was my point. I was expanding your definition as it was too narrow and dealt only with LINQ to SQL when your previous post simply mentions LINQ (without detailing what it was LINQing to). You have still avoided my question on connection pooling. What does deferred execution in LINQ have to do with connection pooling?

*Developer Day Scotland - Free community conference Delegate Registration Open

[EmZan]

as i understand from the obove discussion connection pooling : to keep track of last queried data cashed so that i will no use the database engine to execute a prev. executed query. and that is done using the DataAdapter Object(which fills the DataSet Object) and that what i got from ur prev reply btw DataAdapter is automatically closes the connection after execution(reading or writing) Deffered Execution : as linq queries the data and stores it using a varible , then ther is no need for DataAdapter(the pooling hand). and i'm sure that that variable will be removed after reaing the data ,and the connection is closed. so there is no relation between conn-pooling and deff. exec. as i understand

[Colin Angus Mackay]

EmZan wrote:

connection pooling : to keep track of last queried data cashed so that i will no use the database engine to execute a prev. executed query.

Nope. It keeps track of the previously query CONNECTION only so it doesn't have to reestablish a connection to the database. This makes it faster to establish a new connection. Any queries are done against the database, even if it is the same query as the previous time.

EmZan wrote:

and that is done using the DataAdapter Object(which fills the DataSet Object) and that what i got from ur prev reply

I've never mentioned the DataAdapter. It is an evil thing.

EmZan wrote:

DataAdapter is automatically closes the connection after execution(reading or writing)

Well, it says it closes the connection, but just like everything else it simply returns it too the pool. Connection Pooling happens on the SqlConnection object which everything that connects to a database must use. LINQ or not.

*Developer Day Scotland - Free community conference Delegate Registration Open

[EmZan]

thanks for clarification ,it's realy a good info but i have many questions plz 1.why did u call the data adapter an evil :confused: i'm always using it in my application(actually my 4 applications)i'm a junior btw 2.u said that any query is done against the database! what i know is that dataAdapters keeps queried data and i use it many times without going to the database again and again for the same query and if it's wrong , i'd say that i read before at a limq tutorial that reading the most recent data is an advantage that it means that there is a technology that reads old data many times without going to the database , as i mentioned :sigh:

[Colin Angus Mackay]

EmZan wrote:

1.why did u call the data adapter an evil i'm always using it in my application(actually my 4 applications)i'm a junior btw

Data Adapters are used to copy data into a DataSet or DataTable. These are large clunky constructs that almost always are under-utilised. So they end up taking more memory than they actually need for the job you use them for. The prefered solution is to take the data and put it in a domain model without the use of DataAdapters. LINQ to SQL goes some way to help achieve that easily. Previously you needed to use a DataReader (which a Data Adapters uses internally anyway) to get the data out and into your model.

EmZan wrote:

2.u said that any query is done against the database! what i know is that dataAdapters keeps queried data and i use it many times without going to the database again and again for the same query

The Data Adapter dumps a disconnected copy of the data into a DataSet or DataTable. You are querying against that disconnected data. You never go back to the DataAdapter, you go back to the DataSet or DataTable. A DataAdapter's roll is to suck the data out of the database and make a copy of it. If it was a proper caching mechanism it would be relatively seamless. In otherwords you wouldn't know if it were cached or not.

*Developer Day Scotland - Free community conference Delegate Registration Open

[EmZan]

thanks a lot :D , it's very useful information

[PIEBALDconsult]

Yep, number two. But what that means depends on what the application does.

[dojohansen]

Hi, the answer is that in ADO.NET you should always close the connection when it isn't in use. The connection classes manage the underlying TCP connection for you, so although you are "logically" closing the connection you are not in fact incurring the cost of tearing down and reestablishing the database connection each time. In practice this makes the most difference in server apps where connection pooling is of great use. Whenever your code closes a connection it in fact simply releases that connection to the pool, and the next time an instance of a connection is constructed that uses the exact same connection string the pre-existing connection is returned. (There is something involved to make the connection state as if it was a freshly established one - see 'sp_reset' in SQL Server, not sure exactly how this is implemented with the other providers.) But even in a desktop app where each client has a dedicated connection to the database and no pooling takes place it's considered good practice to open and close the connection. I think it is; it makes error handling a little easier. You still have to catch exceptions and perhaps log and present errors, but if the user wishes to retry an operation there's no additional logic to check the state of the connection or find out if it's necessary to open it first, because you simply *always* open it where you need it and close it when you're done with it. I personally use a simple connection wrapper class to centralize the code implementing the patterns I wish to use. You may not bother doing this if you use code generation for most of your data access code, but if you hand-code this stuff it makes a huge difference - much less code, far fewer errors, and much easier debugging. And if you just put this class in a separate library and never put anything app-specific in it you'll start building reusable code that has applications everywhere. For example, with my Connection class you can do very common tasks like these very easily:

// Get a connection from configuration. Why rewrite the same code all over the place?
Connection c = Connection.FromConfig("mainDB");

// Similar with the open-and-close logic. Why not centralize it so it's transparent?
int count = c.ExecuteScalar("select count(*) from [table] where [col] < @p0", value);

// SqlTransaction is a mess; it doesn't support nesting, and you must write code to associate each
// SqlCommand with the transaction object before executing it. Why not automate this ted

[dojohansen]

This advice leaves me feeling rather ambivalent. On the one hand Colin appears to know what he's talking about, on the other he appears to be one of those dogmatic people who think that there is one way that is "the best" regardless of circumstances. I must emphasize that this is merely my impression, how I think the posts read, not a claim that Colin IS such a dogmatic person. (Perhaps he will reply and we will find out.) Personally I think data adapters are useful and the disconnected data model can be enough for many things. Sure, if you have a fancy entity layer it would be ideal if the data can be persisted and reloaded as efficiently as possible, without any dataset intermediaries, but in many applications the truth is whether or not such an intermediary exists makes absolutely no difference to the value or usefulness of your application. In my view, very few dogmas are of any use in programming. Sure, you should use StringBuilder and not string if manipulating string data, but even this makes little difference if the strings are small and modifications few. :)

[Colin Angus Mackay]

dojohansen wrote:

he appears to be one of those dogmatic people who think that there is one way that is "the best" regardless of circumstances.

I don't think I am. I mearly dislike DataAdapters/DataSets/DataTables. I'm find them incredibly cluncky for what they do. I much prefer to get the data into my business model as quickly as I can. I do appreciate that for quick throwaway applications DataAdapters can server a very good shortcut and I do use them on those occasion. But, for most things I think they are too unweildly and clunky. If I am going for a purely ADO.NET approach I'd use a DataReader with a factory pattern to generate my objects. Alternatively I'd use an ORM like NHibernate.

dojohansen wrote:

In my view, very few dogmas are of any use in programming.

That's true. Framework features exist for a reason and while I think that the number of use cases for certain features are quite low, there are times where I think they work best for the given circumstances.

*Developer Day Scotland - Free community conference Delegate Registration Open

[dojohansen]

We largely agree then. You're definitely right that using a reader and assigning the properties/fields of a class is faster to load than datasets. I just don't think the difference actually matters in all applications. More importantly, data sets have some very nice features. They lend themselves to AJAX and web services rather well since their internal representation is XML, meaning they serialize and deserialize to and from XML very efficiently. You can save them to files and modify them in disconnected mode, such as on a laptop on the road, and easily sync back to some other store ("the central database") at a later time. You can filter the data and sort it very easily, add relations between entities of data dynamically, verify constraints, cascade deletes, and detect concurrency violations. All of this is out-of-the-box functionality you get just by using datasets and adapters. If you want to get the same functionality but use your own custom data objects you run into a bunch of other constraints. Of course it is possible to recreate this sort of flexible and rich functionality in your entity objects, but trust me, if you do they will not be as lightweight anymore. To implement things like the DataTable.Select() method or DataViews and filters, or relations, you need to start including metadata in the classes or use reflection to discover it, and you then lose the raw speed of primitive data objects with hardcoded relations, as in obj.Name = reader.GetString("name");. Sometimes this extra speed is necessary or at least highly desireable for an app to do it's job properly, but other times the user wouldn't even be able to tell any difference at all. So for me, dismissing datasets and adapters isn't a decision to be taken before you've asked yourself - and answered - this question: How much of the functionality it offers is useful to me? How much might become useful down the road? A specific example where datasets may be an excellent fit: You're writing an AJAX-enabled web app and have this idea: What if we simply use the same schema for the XML data on the wire as that used by the DataSet itself? We can have a client-side component (such as a table with in-place editing capability) effectively perform Insert, Update, Delete operations on the disconnected dataset without having to contact the server at all, neither by postback nor any AJAX callback. The UI would then basically just become a specialized XML editor. After multiple edits have been made and are ready to be persisted, the

[Pete OHanlon]

dojohansen wrote:

Personally I think data adapters are useful and the disconnected data model can be enough for many things.

Don't forget memory intensive, and a real no-no when it comes to interoperability. Speaking as somebody who spent a lot of time writing code that communicates with Java based systems, I can tell you that DataSets/etc, are just plain evil.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx