We have already looked into this in the first ado.net application - however there are some things that could be worth noting.
string sConn = "Data Source=(local);Network Library=DBMSSOCN;Initial Catalog=Northwind;User ID=sa;Password=stefan;";
string sSQL = "select * from products";
DataTable dt = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(sConn,
CommandType.Text, sSQL).Tables[0];
dataGridView1.DataSource = dt;
We never need to explicitly open/close the connections. Typically you write code like
SqlConnection oConn = new SqlConnection(sConn);
oConn.Open();
//Create a command
//Create dataadapter
//Create dataset
//Fill dataset using dataadapter
oConn.Close
SQLHelper.cs takes care of all that. That's a good thing. Now you might ask - if we have multiple calls to make, for example first read table 1, then read table2, then update table 3, then read table4. Wouldn't it be better to write code like
SqlConnection oConn = new SqlConnection(sConn);
oConn.Open();
Read1(oConn);
Read2(oConn);
Update3(oConn);
Read4(oConn);
oConn.Close();
Well it isn't better. Performancewise it's pretty much the same because of .NET runtime connection pooling.The biggest thing is scalability though. You are holding on to a connection (which is a limited resource) for a much longer time than needed. I mean, maybe in the Update3 function you call out to fetch some extra values via RSS. It might take seconds, who knows?
So the easy way out - always use sqlhelper.cs. It is not without a reason it is considered "best practice"
