How to: Centralize Connection Objects

[Guest]

I have developed an application for WM 2003, which frequently transacts with
a sql server ce 2.0 database. I have several procedures which utilize the
following code:

cn = new SqlCeConnection(@"<< sdf file >>");
cn.Open();
cmd = new SqlCeCommand(<< sql string >>, cn);
cmd.ExecuteNonQuery();
cn.Close()

My question is: How can i best encapsulate this as it is repeated several
times in my program?

All of the examples i find declare both connection objects and command
objects in *each* procedure, but i can only assume that this is not efficient?

I have made the "cn" connection object and "cmd" command object private to
the entire class in hopes to eliminate the number of objects which are
declared.

Any other suggestions? Thanks.

 

[Nicholas Paldino [.NET/C# MVP]]

Charlie,

You could do something like this:

public static void ExecuteNonQuery(string sql)
{
// Open a connection.
using (SqlCeConnection cn = new SqlCeConnection(@"file"))
{
// Open the connection.
cn.Open();

// Create a command.
using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
{
// Execute.
cmd.ExecuteNonQuery();
}
}
}

And that should work fine. I would also say that you should have a
method that will generate your connection for you, that way you can change
the way that the connection information is obtained (registry, file, etc,
etc), and it makes it available elsewhere (instead of you having to perform
the logic everywhere).

Hope this helps.

 

[Guest]

Thanks Nicholas. The example you've provided seems ideal for executing
*individual* or standalone commands.

In my program, however, procedures execute several commands using the same
connection. (My sample code was a little too generic - sorry). My guess is
that the sample you provided would not be ideal for this scenario as creating
a new connection and command object so many times in the same procedure must
affect performance on a pocket pc?

When i originally asked this question (although i didn't specify this) i was
thinking along the lines of encapsulating the connection object and then
returning the connection object to the calling procedure, then executing my
commands, and then close the connection object somehow. But i haven't seen
this done anywhere.

Can you let me know if i am on the right track? Or, if what i am thinking
isn't possible/recommended. Thanks.

Charlie,

You could do something like this:

public static void ExecuteNonQuery(string sql)
{
// Open a connection.
using (SqlCeConnection cn = new SqlCeConnection(@"file"))
{
// Open the connection.
cn.Open();

// Create a command.
using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
{
// Execute.
cmd.ExecuteNonQuery();
}
}
}

And that should work fine. I would also say that you should have a
method that will generate your connection for you, that way you can change
the way that the connection information is obtained (registry, file, etc,
etc), and it makes it available elsewhere (instead of you having to perform
the logic everywhere).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have developed an application for WM 2003, which frequently transacts
with
a sql server ce 2.0 database. I have several procedures which utilize the
following code:

cn = new SqlCeConnection(@"<< sdf file >>");
cn.Open();
cmd = new SqlCeCommand(<< sql string >>, cn);
cmd.ExecuteNonQuery();
cn.Close()

My question is: How can i best encapsulate this as it is repeated several
times in my program?

All of the examples i find declare both connection objects and command
objects in *each* procedure, but i can only assume that this is not
efficient?

I have made the "cn" connection object and "cmd" command object private to
the entire class in hopes to eliminate the number of objects which are
declared.

Any other suggestions? Thanks.

 

[Nicholas Paldino [.NET/C# MVP]]

Charlie,

This is exactly what I was thinking, which is why I made the last
comment:

I would also say that you should have a method that will generate your
connection for you, that way you can change
the way that the connection information is obtained (registry, file, etc,
etc), and it makes it available elsewhere (instead of you having to perform
the logic everywhere).

The implication here (which is not obvious, I am sorry) is that you can
get the connection from anywhere, if you need to, and then use it. So you
could do something like this:

// Get the connection.
using (SqlCeConnection conn = MyLib.GetConnection())
{
// Open the connection.
conn.Open();

// Use it multiple times.
}

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Thanks Nicholas. The example you've provided seems ideal for executing
*individual* or standalone commands.

In my program, however, procedures execute several commands using the same
connection. (My sample code was a little too generic - sorry). My guess is
that the sample you provided would not be ideal for this scenario as
creating
a new connection and command object so many times in the same procedure
must
affect performance on a pocket pc?

When i originally asked this question (although i didn't specify this) i
was
thinking along the lines of encapsulating the connection object and then
returning the connection object to the calling procedure, then executing
my
commands, and then close the connection object somehow. But i haven't seen
this done anywhere.

Can you let me know if i am on the right track? Or, if what i am thinking
isn't possible/recommended. Thanks.

Charlie,

You could do something like this:

public static void ExecuteNonQuery(string sql)
{
// Open a connection.
using (SqlCeConnection cn = new SqlCeConnection(@"file"))
{
// Open the connection.
cn.Open();

// Create a command.
using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
{
// Execute.
cmd.ExecuteNonQuery();
}
}
}

And that should work fine. I would also say that you should have a
method that will generate your connection for you, that way you can
change
the way that the connection information is obtained (registry, file, etc,
etc), and it makes it available elsewhere (instead of you having to
perform
the logic everywhere).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have developed an application for WM 2003, which frequently transacts
with
a sql server ce 2.0 database. I have several procedures which utilize
the
following code:

cn = new SqlCeConnection(@"<< sdf file >>");
cn.Open();
cmd = new SqlCeCommand(<< sql string >>, cn);
cmd.ExecuteNonQuery();
cn.Close()

My question is: How can i best encapsulate this as it is repeated
several
times in my program?

All of the examples i find declare both connection objects and command
objects in *each* procedure, but i can only assume that this is not
efficient?

I have made the "cn" connection object and "cmd" command object private
to
the entire class in hopes to eliminate the number of objects which are
declared.

Any other suggestions? Thanks.

 

[Guest]

Many thanks.

Charlie,

This is exactly what I was thinking, which is why I made the last
comment:

I would also say that you should have a method that will generate your
connection for you, that way you can change
the way that the connection information is obtained (registry, file, etc,
etc), and it makes it available elsewhere (instead of you having to perform
the logic everywhere).

The implication here (which is not obvious, I am sorry) is that you can
get the connection from anywhere, if you need to, and then use it. So you
could do something like this:

// Get the connection.
using (SqlCeConnection conn = MyLib.GetConnection())
{
// Open the connection.
conn.Open();

// Use it multiple times.
}

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Thanks Nicholas. The example you've provided seems ideal for executing
*individual* or standalone commands.

In my program, however, procedures execute several commands using the same
connection. (My sample code was a little too generic - sorry). My guess is
that the sample you provided would not be ideal for this scenario as
creating
a new connection and command object so many times in the same procedure
must
affect performance on a pocket pc?

When i originally asked this question (although i didn't specify this) i
was
thinking along the lines of encapsulating the connection object and then
returning the connection object to the calling procedure, then executing
my
commands, and then close the connection object somehow. But i haven't seen
this done anywhere.

Can you let me know if i am on the right track? Or, if what i am thinking
isn't possible/recommended. Thanks.

Charlie,

You could do something like this:

public static void ExecuteNonQuery(string sql)
{
// Open a connection.
using (SqlCeConnection cn = new SqlCeConnection(@"file"))
{
// Open the connection.
cn.Open();

// Create a command.
using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
{
// Execute.
cmd.ExecuteNonQuery();
}
}
}

And that should work fine. I would also say that you should have a
method that will generate your connection for you, that way you can
change
the way that the connection information is obtained (registry, file, etc,
etc), and it makes it available elsewhere (instead of you having to
perform
the logic everywhere).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)




I have developed an application for WM 2003, which frequently transacts
with
a sql server ce 2.0 database. I have several procedures which utilize
the
following code:

cn = new SqlCeConnection(@"<< sdf file >>");
cn.Open();
cmd = new SqlCeCommand(<< sql string >>, cn);
cmd.ExecuteNonQuery();
cn.Close()

My question is: How can i best encapsulate this as it is repeated
several
times in my program?

All of the examples i find declare both connection objects and command
objects in *each* procedure, but i can only assume that this is not
efficient?

I have made the "cn" connection object and "cmd" command object private
to
the entire class in hopes to eliminate the number of objects which are
declared.

Any other suggestions? Thanks.

 

[Guest]

Is there a significant performance hit in declaring the connection and
command objects over and over again? Every example i find declare these
objects as class level variables, versus procedural vars. Thanks.

Charlie,

This is exactly what I was thinking, which is why I made the last
comment:

I would also say that you should have a method that will generate your
connection for you, that way you can change
the way that the connection information is obtained (registry, file, etc,
etc), and it makes it available elsewhere (instead of you having to perform
the logic everywhere).

The implication here (which is not obvious, I am sorry) is that you can
get the connection from anywhere, if you need to, and then use it. So you
could do something like this:

// Get the connection.
using (SqlCeConnection conn = MyLib.GetConnection())
{
// Open the connection.
conn.Open();

// Use it multiple times.
}

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Thanks Nicholas. The example you've provided seems ideal for executing
*individual* or standalone commands.

In my program, however, procedures execute several commands using the same
connection. (My sample code was a little too generic - sorry). My guess is
that the sample you provided would not be ideal for this scenario as
creating
a new connection and command object so many times in the same procedure
must
affect performance on a pocket pc?

When i originally asked this question (although i didn't specify this) i
was
thinking along the lines of encapsulating the connection object and then
returning the connection object to the calling procedure, then executing
my
commands, and then close the connection object somehow. But i haven't seen
this done anywhere.

Can you let me know if i am on the right track? Or, if what i am thinking
isn't possible/recommended. Thanks.

Charlie,

You could do something like this:

public static void ExecuteNonQuery(string sql)
{
// Open a connection.
using (SqlCeConnection cn = new SqlCeConnection(@"file"))
{
// Open the connection.
cn.Open();

// Create a command.
using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
{
// Execute.
cmd.ExecuteNonQuery();
}
}
}

And that should work fine. I would also say that you should have a
method that will generate your connection for you, that way you can
change
the way that the connection information is obtained (registry, file, etc,
etc), and it makes it available elsewhere (instead of you having to
perform
the logic everywhere).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)




I have developed an application for WM 2003, which frequently transacts
with
a sql server ce 2.0 database. I have several procedures which utilize
the
following code:

cn = new SqlCeConnection(@"<< sdf file >>");
cn.Open();
cmd = new SqlCeCommand(<< sql string >>, cn);
cmd.ExecuteNonQuery();
cn.Close()

My question is: How can i best encapsulate this as it is repeated
several
times in my program?

All of the examples i find declare both connection objects and command
objects in *each* procedure, but i can only assume that this is not
efficient?

I have made the "cn" connection object and "cmd" command object private
to
the entire class in hopes to eliminate the number of objects which are
declared.

Any other suggestions? Thanks.

 

[Nicholas Paldino [.NET/C# MVP]]

charlie,

There isn't a significant hit in declaring them, I would think, bit it
becomes much easier to manage them if you factor out the connection
generation (and command generation) to a single point of entry, so that you
don't have to worry about changing code everywhere should the requirements
change.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Is there a significant performance hit in declaring the connection and
command objects over and over again? Every example i find declare these
objects as class level variables, versus procedural vars. Thanks.

Charlie,

This is exactly what I was thinking, which is why I made the last
comment:

I would also say that you should have a method that will generate your
connection for you, that way you can change
the way that the connection information is obtained (registry, file, etc,
etc), and it makes it available elsewhere (instead of you having to
perform
the logic everywhere).

The implication here (which is not obvious, I am sorry) is that you
can
get the connection from anywhere, if you need to, and then use it. So
you
could do something like this:

// Get the connection.
using (SqlCeConnection conn = MyLib.GetConnection())
{
// Open the connection.
conn.Open();

// Use it multiple times.
}

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Thanks Nicholas. The example you've provided seems ideal for executing
*individual* or standalone commands.

In my program, however, procedures execute several commands using the
same
connection. (My sample code was a little too generic - sorry). My guess
is
that the sample you provided would not be ideal for this scenario as
creating
a new connection and command object so many times in the same procedure
must
affect performance on a pocket pc?

When i originally asked this question (although i didn't specify this)
i
was
thinking along the lines of encapsulating the connection object and
then
returning the connection object to the calling procedure, then
executing
my
commands, and then close the connection object somehow. But i haven't
seen
this done anywhere.

Can you let me know if i am on the right track? Or, if what i am
thinking
isn't possible/recommended. Thanks.



:

Charlie,

You could do something like this:

public static void ExecuteNonQuery(string sql)
{
// Open a connection.
using (SqlCeConnection cn = new SqlCeConnection(@"file"))
{
// Open the connection.
cn.Open();

// Create a command.
using (SqlCeCommand cmd = new SqlCeCommand(sql, cn))
{
// Execute.
cmd.ExecuteNonQuery();
}
}
}

And that should work fine. I would also say that you should have
a
method that will generate your connection for you, that way you can
change
the way that the connection information is obtained (registry, file,
etc,
etc), and it makes it available elsewhere (instead of you having to
perform
the logic everywhere).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)




I have developed an application for WM 2003, which frequently
transacts
with
a sql server ce 2.0 database. I have several procedures which
utilize
the
following code:

cn = new SqlCeConnection(@"<< sdf file >>");
cn.Open();
cmd = new SqlCeCommand(<< sql string >>, cn);
cmd.ExecuteNonQuery();
cn.Close()

My question is: How can i best encapsulate this as it is repeated
several
times in my program?

All of the examples i find declare both connection objects and
command
objects in *each* procedure, but i can only assume that this is not
efficient?

I have made the "cn" connection object and "cmd" command object
private
to
the entire class in hopes to eliminate the number of objects which
are
declared.

Any other suggestions? Thanks.