Prev: Returning a range of serial numbers when consecutive, otherwisea range of one.
Next: SSMS Won't Open Database
From: orgilhp on 17 Jan 2010 23:12
I have two ways to use connection with sqlcommands. here is sample
codes in C#:
The Way First:
DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection(" .... ");
conn.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from table1", conn);
da.Fill(ds, "table1");
da.SelectCommand.CommantText = "select * from table2";
da.Fill(ds, "table2");
da.SelectCommand.CommandText = "select * from table3";
da.Fill(ds, "table3");
Conn.Close();

The Way Second:
DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection(" .... ");
SqlDataAdapter da = new SqlDataAdapter("select * from table1", conn);
conn.Open();
da.Fill(ds, "table1");
conn.Close();
da.SelectCommand.CommantText = "select * from table2";
conn.Open();
da.Fill(ds, "table2");
conn.Close();
da.SelectCommand.CommandText = "select * from table3";
conn.Open();
da.Fill(ds, "table3");
Conn.Close();

Now, I wish to know which way uses more memory on Databases Server?
I am using MSSQL2005.
Do I need to use one public connection which is opened at the start of
the application for all SqlCommands? or
Do I need to use local connection which is opened for the one
SqlCommand and then closed?

Please help me,
Any suggestion would be highly appreciate!

Orgil

From: Jeroen Mostert on 18 Jan 2010 01:45
On 2010-01-18 5:12, orgilhp wrote:
> I have two ways to use connection with sqlcommands. here is sample
> codes in C#:
> The Way First:
> DataSet ds = new DataSet();
> SqlConnection conn = new SqlConnection(" .... ");
> conn.Open();
> SqlDataAdapter da = new SqlDataAdapter("select * from table1", conn);
> da.Fill(ds, "table1");
> da.SelectCommand.CommantText = "select * from table2";
> da.Fill(ds, "table2");
> da.SelectCommand.CommandText = "select * from table3";
> da.Fill(ds, "table3");
> Conn.Close();
>
> The Way Second:
> DataSet ds = new DataSet();
> SqlConnection conn = new SqlConnection(" .... ");
> SqlDataAdapter da = new SqlDataAdapter("select * from table1", conn);
> conn.Open();
> da.Fill(ds, "table1");
> conn.Close();
> da.SelectCommand.CommantText = "select * from table2";
> conn.Open();
> da.Fill(ds, "table2");
> conn.Close();
> da.SelectCommand.CommandText = "select * from table3";
> conn.Open();
> da.Fill(ds, "table3");
> Conn.Close();
>
> Now, I wish to know which way uses more memory on Databases Server?

There's no practical difference. .NET uses connection pooling. When you
create a new connection object, an existing physical connection is taken
from the pool and associated with the object, when you close the connection
object, the physical connection is returned to the pool.

> Do I need to use one public connection which is opened at the start of
> the application for all SqlCommands?

You shouldn't do this because you complicate error handling tremendously. If
a statement fails, the connection may become useless (depending on what kind
of error occurred). You cannot re-open a connection once it's in a failed
state, so you have to create a new one. Repeat this for every single
statement you execute and it's easy to see why this is not the preferred
approach. Instead, use the following pattern:

using (SqlConnection connection = ...) {
connection.Open();
// Use connection here, do not .Close(), the using will take care of it
}

This will not create new physical connections all the time, only
SqlConnection objects.

You can, on the other hand, reuse data adapters and SqlCommands, but this
has no effect on server memory either, only on client memory (and possibly
execution speed).

--
J.

From: Uri Dimant on 18 Jan 2010 03:51
Why do you use SELECT * ? is it just for demonstraiting purposes?

How big are those tables? Why do not you have a WHERE clause?


"orgilhp" <orgilhp(a)gmail.com> wrote in message
news:73b81ac7-f31d-4369-9b6a-b468ea03aece(a)m25g2000yqc.googlegroups.com...
>I have two ways to use connection with sqlcommands. here is sample
> codes in C#:
> The Way First:
> DataSet ds = new DataSet();
> SqlConnection conn = new SqlConnection(" .... ");
> conn.Open();
> SqlDataAdapter da = new SqlDataAdapter("select * from table1", conn);
> da.Fill(ds, "table1");
> da.SelectCommand.CommantText = "select * from table2";
> da.Fill(ds, "table2");
> da.SelectCommand.CommandText = "select * from table3";
> da.Fill(ds, "table3");
> Conn.Close();
>
> The Way Second:
> DataSet ds = new DataSet();
> SqlConnection conn = new SqlConnection(" .... ");
> SqlDataAdapter da = new SqlDataAdapter("select * from table1", conn);
> conn.Open();
> da.Fill(ds, "table1");
> conn.Close();
> da.SelectCommand.CommantText = "select * from table2";
> conn.Open();
> da.Fill(ds, "table2");
> conn.Close();
> da.SelectCommand.CommandText = "select * from table3";
> conn.Open();
> da.Fill(ds, "table3");
> Conn.Close();
>
> Now, I wish to know which way uses more memory on Databases Server?
> I am using MSSQL2005.
> Do I need to use one public connection which is opened at the start of
> the application for all SqlCommands? or
> Do I need to use local connection which is opened for the one
> SqlCommand and then closed?
>
> Please help me,
> Any suggestion would be highly appreciate!
>
> Orgil
>


From: sloan on 18 Jan 2010 11:50
Instead of trying to figure out everything on this type of
stuff...............
I would suggest getting and learning the EnterpriseLibrary.Data helper
framework.

It encapsulates many best practices, and has very good people writing it,
and alot/alot/alot of people using it.

The principles from the other posts are good to know.

However,......you can make your life simpler by getting and using a
framework that has already been developed using many best practices.


You can see a sample here:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry

http://msdn.microsoft.com/en-us/library/cc467894.aspx
The versions? You match the EnterpriseLibrary version against the Framework
version you have.


"orgilhp" <orgilhp(a)gmail.com> wrote in message
news:73b81ac7-f31d-4369-9b6a-b468ea03aece(a)m25g2000yqc.googlegroups.com...
>I have two ways to use connection with sqlcommands. here is sample
> codes in C#:
> The Way First:
> DataSet ds = new DataSet();
> SqlConnection conn = new SqlConnection(" .... ");
> conn.Open();
> SqlDataAdapter da = new SqlDataAdapter("select * from table1", conn);
> da.Fill(ds, "table1");
> da.SelectCommand.CommantText = "select * from table2";
> da.Fill(ds, "table2");
> da.SelectCommand.CommandText = "select * from table3";
> da.Fill(ds, "table3");
> Conn.Close();
>
> The Way Second:
> DataSet ds = new DataSet();
> SqlConnection conn = new SqlConnection(" .... ");
> SqlDataAdapter da = new SqlDataAdapter("select * from table1", conn);
> conn.Open();
> da.Fill(ds, "table1");
> conn.Close();
> da.SelectCommand.CommantText = "select * from table2";
> conn.Open();
> da.Fill(ds, "table2");
> conn.Close();
> da.SelectCommand.CommandText = "select * from table3";
> conn.Open();
> da.Fill(ds, "table3");
> Conn.Close();
>
> Now, I wish to know which way uses more memory on Databases Server?
> I am using MSSQL2005.
> Do I need to use one public connection which is opened at the start of
> the application for all SqlCommands? or
> Do I need to use local connection which is opened for the one
> SqlCommand and then closed?
>
> Please help me,
> Any suggestion would be highly appreciate!
>
> Orgil
>


From: orgilhp on 19 Jan 2010 03:00
Big thanks to replies from you guys. I got very significant experience
from you guys.
 |  Next  |  Last
Pages: 1 2
Prev: Returning a range of serial numbers when consecutive, otherwisea range of one.
Next: SSMS Won't Open Database