SqlConnection vs IDbConnection

[JoeW]

I normally use SqlConnection, SqlDataReader, SqlCommand for most of my
queries but recently a friend of mine who is a long time java
programmer suggested I use the IDbConnection, IDbCommand interfaces
because the queries where compiled instead of just executed like on
the open SqlConnection. Is he correct and if so does it really make a
difference as far as performance/security, etc?

Thanks for any suggestions

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

JoeW,

No, he isn't correct. IDbConnection, IDbCommand, and the like are just
interfaces to abstract functionality on the provider-specific
implementations (like SqlConnection, SqlDataReader, SqlCommand, etc, etc).
It doesn't imply anything.

If your command points to a stored procedure, then it is already
compiled. If you have a command that is a string that you are just passing,
and need it prepared by the underlying provider, you would call the Prepare
method on the IDbCommand implementation (this method is exposed publically
on the implementation as well, most likely). That will prepare the query
for you with the underlying provider.

Note, if your command is a stored procedure, you don't want to call
Prepare. The execution plan for the stored proc has already been generated.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"JoeW" <te********@gma il.comwrote in message
news:11******** *************@y 80g2000hsf.goog legroups.com...

>I normally use SqlConnection, SqlDataReader, SqlCommand for most of my
queries but recently a friend of mine who is a long time java
programmer suggested I use the IDbConnection, IDbCommand interfaces
because the queries where compiled instead of just executed like on
the open SqlConnection. Is he correct and if so does it really make a
difference as far as performance/security, etc?

Thanks for any suggestions

[PS]

"JoeW" <te********@gma il.comwrote in message
news:11******** *************@y 80g2000hsf.goog legroups.com...

>I normally use SqlConnection, SqlDataReader, SqlCommand for most of my
queries but recently a friend of mine who is a long time java
programmer suggested I use the IDbConnection, IDbCommand interfaces
because the queries where compiled instead of just executed like on
the open SqlConnection. Is he correct and if so does it really make a
difference as far as performance/security, etc?

The reason you would code against the interface (or the abstract class
DbCommand in .Net 2.0) is so that your code could be used across multiple
data providers. In 2.0 there are factories provided to make this easier. If
you are only supporting Sql Server as a database then I see no reason to
chnage anything (YAGNI).

PS

>
Thanks for any suggestions

[JoeW]

On Apr 13, 2:24 pm, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail] d.caspershouse. comwrote:

JoeW,

No, he isn't correct. IDbConnection, IDbCommand, and the like are just
interfaces to abstract functionality on the provider-specific
implementations (like SqlConnection, SqlDataReader, SqlCommand, etc, etc).
It doesn't imply anything.

If your command points to a stored procedure, then it is already
compiled. If you have a command that is a string that you are just passing,
and need it prepared by the underlying provider, you would call the Prepare
method on the IDbCommand implementation (this method is exposed publically
on the implementation as well, most likely). That will prepare the query
for you with the underlying provider.

Note, if your command is a stored procedure, you don't want to call
Prepare. The execution plan for the stored proc has already been generated.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail] .caspershouse.c om

"JoeW" <teh.sn1...@gma il.comwrote in message

news:11******** *************@y 80g2000hsf.goog legroups.com...

I normally use SqlConnection, SqlDataReader, SqlCommand for most of my
queries but recently a friend of mine who is a long time java
programmer suggested I use the IDbConnection, IDbCommand interfaces
because the queries where compiled instead of just executed like on
the open SqlConnection. Is he correct and if so does it really make a
difference as far as performance/security, etc?

Thanks for any suggestions- Hide quoted text -

- Show quoted text -

Ahh, I see. So this code would be fine?

string selectQuery = "SELECT time FROM " + TABLE_PREFIX + "referrers
WHERE url = @url AND ip = @ip";

_sqlCommand = new SqlCommand(sele ctQuery, _sqlConnector);

_sqlCommand.Par ameters.AddWith Value("@url", pUrl);
_sqlCommand.Par ameters.AddWith Value("@ip", pIp);
_sqlCommand.Pre pare();

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

JoeW,

Yep, that looks fine to me.

Just remember that you have to keep the connection open as long as you
want to use the prepared statement.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"JoeW" <te********@gma il.comwrote in message
news:11******** **************@ w1g2000hsg.goog legroups.com...

On Apr 13, 2:24 pm, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail] d.caspershouse. comwrote:

>JoeW,

No, he isn't correct. IDbConnection, IDbCommand, and the like are
just
interfaces to abstract functionality on the provider-specific
implementation s (like SqlConnection, SqlDataReader, SqlCommand, etc,
etc).
It doesn't imply anything.

If your command points to a stored procedure, then it is already
compiled. If you have a command that is a string that you are just
passing,
and need it prepared by the underlying provider, you would call the
Prepare
method on the IDbCommand implementation (this method is exposed
publically
on the implementation as well, most likely). That will prepare the query
for you with the underlying provider.

Note, if your command is a stored procedure, you don't want to call
Prepare. The execution plan for the stored proc has already been
generated.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail] .caspershouse.c om

"JoeW" <teh.sn1...@gma il.comwrote in message

news:11******* **************@ y80g2000hsf.goo glegroups.com.. .

>I normally use SqlConnection, SqlDataReader, SqlCommand for most of my
queries but recently a friend of mine who is a long time java
programmer suggested I use the IDbConnection, IDbCommand interfaces
because the queries where compiled instead of just executed like on
the open SqlConnection. Is he correct and if so does it really make a
difference as far as performance/security, etc?

Thanks for any suggestions- Hide quoted text -

- Show quoted text -

Ahh, I see. So this code would be fine?

string selectQuery = "SELECT time FROM " + TABLE_PREFIX + "referrers
WHERE url = @url AND ip = @ip";

_sqlCommand = new SqlCommand(sele ctQuery, _sqlConnector);

_sqlCommand.Par ameters.AddWith Value("@url", pUrl);
_sqlCommand.Par ameters.AddWith Value("@ip", pIp);
_sqlCommand.Pre pare();

[JoeW]

On Apr 13, 2:49 pm, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail] d.caspershouse. comwrote:

JoeW,

Yep, that looks fine to me.

Just remember that you have to keep the connection open as long as you
want to use the prepared statement.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail] .caspershouse.c om

"JoeW" <teh.sn1...@gma il.comwrote in message

news:11******** **************@ w1g2000hsg.goog legroups.com...

On Apr 13, 2:24 pm, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail] d.caspershouse. comwrote:

JoeW,

No, he isn't correct. IDbConnection, IDbCommand, and the like are
just
interfaces to abstract functionality on the provider-specific
implementations (like SqlConnection, SqlDataReader, SqlCommand, etc,
etc).
It doesn't imply anything.

If your command points to a stored procedure, then it is already
compiled. If you have a command that is a string that you are just
passing,
and need it prepared by the underlying provider, you would call the
Prepare
method on the IDbCommand implementation (this method is exposed
publically
on the implementation as well, most likely). That will prepare the query
for you with the underlying provider.

Note, if your command is a stored procedure, you don't want to call
Prepare. The execution plan for the stored proc has already been
generated.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail] .caspershouse.c om

"JoeW" <teh.sn1...@gma il.comwrote in message

>news:11******* **************@ y80g2000hsf.goo glegroups.com.. .

I normally use SqlConnection, SqlDataReader, SqlCommand for most of my
queries but recently a friend of mine who is a long time java
programmer suggested I use the IDbConnection, IDbCommand interfaces
because the queries where compiled instead of just executed like on
the open SqlConnection. Is he correct and if so does it really make a
difference as far as performance/security, etc?

Thanks for any suggestions- Hide quoted text -

- Show quoted text -

Ahh, I see. So this code would be fine?

string selectQuery = "SELECT time FROM " + TABLE_PREFIX + "referrers
WHERE url = @url AND ip = @ip";

_sqlCommand = new SqlCommand(sele ctQuery, _sqlConnector);

_sqlCommand.Par ameters.AddWith Value("@url", pUrl);
_sqlCommand.Par ameters.AddWith Value("@ip", pIp);
_sqlCommand.Pre pare();- Hide quoted text -

- Show quoted text -

Does it matter if the connection is opened before or after the prepare
statement?

As of now I have it like this:

string selectQuery = "SELECT time FROM " + TABLE_PREFIX + "referrers
WHERE url = @url AND ip = @ip";

_sqlCommand = new SqlCommand(sele ctQuery, _sqlConnector);

_sqlCommand.Par ameters.AddWith Value("@url", pUrl);
_sqlCommand.Par ameters.AddWith Value("@ip", pIp);
_sqlCommand.Pre pare();

try
{
_sqlConnector.O pen();

_sqlReader = _sqlCommand.Exe cuteReader();

while (_sqlReader.Rea d())
{
// do something
}
}

Thanks again for the help, I appreciate it

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

JoeW,

Well, the documentation for the Prepare method on the SqlCommand class
states that an InvalidOperatio nException will be thrown if you call the
Prepare method when the connection is not open, so it might not be a good
idea =)
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"JoeW" <te********@gma il.comwrote in message
news:11******** **************@ p77g2000hsh.goo glegroups.com.. .

On Apr 13, 2:49 pm, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail] d.caspershouse. comwrote:

>JoeW,

Yep, that looks fine to me.

Just remember that you have to keep the connection open as long as
you
want to use the prepared statement.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail] .caspershouse.c om

"JoeW" <teh.sn1...@gma il.comwrote in message

news:11******* *************** @w1g2000hsg.goo glegroups.com.. .

On Apr 13, 2:24 pm, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail] d.caspershouse. comwrote:
JoeW,

> No, he isn't correct. IDbConnection, IDbCommand, and the like are
just
interfaces to abstract functionality on the provider-specific
implementation s (like SqlConnection, SqlDataReader, SqlCommand, etc,
etc).
It doesn't imply anything.

> If your command points to a stored procedure, then it is already
compiled. If you have a command that is a string that you are just
passing,
and need it prepared by the underlying provider, you would call the
Prepare
method on the IDbCommand implementation (this method is exposed
publically
on the implementation as well, most likely). That will prepare the
query
for you with the underlying provider.

> Note, if your command is a stored procedure, you don't want to
call
Prepare. The execution plan for the stored proc has already been
generated.

> Hope this helps.

>--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail] .caspershouse.c om

>"JoeW" <teh.sn1...@gma il.comwrote in message

>>news:11****** *************** @y80g2000hsf.go oglegroups.com. ..

>I normally use SqlConnection, SqlDataReader, SqlCommand for most of
my
queries but recently a friend of mine who is a long time java
programmer suggested I use the IDbConnection, IDbCommand interfaces
because the queries where compiled instead of just executed like on
the open SqlConnection. Is he correct and if so does it really make
a
difference as far as performance/security, etc?

Thanks for any suggestions- Hide quoted text -

>- Show quoted text -

Ahh, I see. So this code would be fine?

string selectQuery = "SELECT time FROM " + TABLE_PREFIX + "referrers
WHERE url = @url AND ip = @ip";

_sqlCommand = new SqlCommand(sele ctQuery, _sqlConnector);

_sqlCommand.Par ameters.AddWith Value("@url", pUrl);
_sqlCommand.Par ameters.AddWith Value("@ip", pIp);
_sqlCommand.Pre pare();- Hide quoted text -

- Show quoted text -

Does it matter if the connection is opened before or after the prepare
statement?

As of now I have it like this:

string selectQuery = "SELECT time FROM " + TABLE_PREFIX + "referrers
WHERE url = @url AND ip = @ip";

_sqlCommand = new SqlCommand(sele ctQuery, _sqlConnector);

_sqlCommand.Par ameters.AddWith Value("@url", pUrl);
_sqlCommand.Par ameters.AddWith Value("@ip", pIp);
_sqlCommand.Pre pare();

try
{
_sqlConnector.O pen();

_sqlReader = _sqlCommand.Exe cuteReader();

while (_sqlReader.Rea d())
{
// do something
}
}

Thanks again for the help, I appreciate it

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

JoeW wrote:

I normally use SqlConnection, SqlDataReader, SqlCommand for most of my
queries but recently a friend of mine who is a long time java
programmer suggested I use the IDbConnection, IDbCommand interfaces
because the queries where compiled instead of just executed like on
the open SqlConnection. Is he correct and if so does it really make a
difference as far as performance/security, etc?

No difference for performance or security.

Big difference for portability between databases.

If you use only IDb interfaces and the .NET 2.0+
DbProvideFactor y then you code is database independent.

It can be a very good benefit.

Obviously you also loose the capability to use
database specific features.

Code snippet:

private static void test(string provider, string constr)
{
DbProviderFacto ry dbf = DbProviderFacto ries.GetFactory (provider);
IDbConnection con = dbf.CreateConne ction();
con.ConnectionS tring = constr;
con.Open();
IDbCommand cmd = con.CreateComma nd();
cmd.CommandText = "SELECT * FROM T1";
IDataReader rdr = cmd.ExecuteRead er();
while(rdr.Read( )) {
int f1 = (int)rdr[0];
string f2 = (string)rdr[1];
Console.WriteLi ne(f1 + " " + f2);
}
con.Close();
}

It is not my impression that the usage of IDb interfaces
is that big.

Arne

[JoeW]

On Apr 14, 12:29 pm, Arne Vajhøj <a...@vajhoej.d kwrote:

JoeW wrote:

I normally use SqlConnection, SqlDataReader, SqlCommand for most of my
queries but recently a friend of mine who is a long time java
programmer suggested I use the IDbConnection, IDbCommand interfaces
because the queries where compiled instead of just executed like on
the open SqlConnection. Is he correct and if so does it really make a
difference as far as performance/security, etc?

No difference for performance or security.

Big difference for portability between databases.

If you use only IDb interfaces and the .NET 2.0+
DbProvideFactor y then you code is database independent.

It can be a very good benefit.

Obviously you also loose the capability to use
database specific features.

Code snippet:

private static void test(string provider, string constr)
{
DbProviderFacto ry dbf = DbProviderFacto ries.GetFactory (provider);
IDbConnection con = dbf.CreateConne ction();
con.ConnectionS tring = constr;
con.Open();
IDbCommand cmd = con.CreateComma nd();
cmd.CommandText = "SELECT * FROM T1";
IDataReader rdr = cmd.ExecuteRead er();
while(rdr.Read( )) {
int f1 = (int)rdr[0];
string f2 = (string)rdr[1];
Console.WriteLi ne(f1 + " " + f2);
}
con.Close();
}

It is not my impression that the usage of IDb interfaces
is that big.

Arne

Great advice guys, thanks again for your help. That helped me out a
lot.