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.

 

[PS]

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

 

[JoeW]

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 address removed)

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(selectQuery, _sqlConnector);

_sqlCommand.Parameters.AddWithValue("@url", pUrl);
_sqlCommand.Parameters.AddWithValue("@ip", pIp);
_sqlCommand.Prepare();

 

[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]
- (e-mail address removed)

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 address removed)

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(selectQuery, _sqlConnector);

_sqlCommand.Parameters.AddWithValue("@url", pUrl);
_sqlCommand.Parameters.AddWithValue("@ip", pIp);
_sqlCommand.Prepare();

 

[JoeW]

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 address removed)

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 address removed)

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(selectQuery, _sqlConnector);

_sqlCommand.Parameters.AddWithValue("@url", pUrl);
_sqlCommand.Parameters.AddWithValue("@ip", pIp);
_sqlCommand.Prepare();- 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(selectQuery, _sqlConnector);

_sqlCommand.Parameters.AddWithValue("@url", pUrl);
_sqlCommand.Parameters.AddWithValue("@ip", pIp);
_sqlCommand.Prepare();

try
{
_sqlConnector.Open();

_sqlReader = _sqlCommand.ExecuteReader();

while (_sqlReader.Read())
{
// 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 InvalidOperationException 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]
- (e-mail address removed)

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 address removed)

On Apr 13, 2:24 pm, "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.

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(selectQuery, _sqlConnector);

_sqlCommand.Parameters.AddWithValue("@url", pUrl);
_sqlCommand.Parameters.AddWithValue("@ip", pIp);
_sqlCommand.Prepare();- 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(selectQuery, _sqlConnector);

_sqlCommand.Parameters.AddWithValue("@url", pUrl);
_sqlCommand.Parameters.AddWithValue("@ip", pIp);
_sqlCommand.Prepare();

try
{
_sqlConnector.Open();

_sqlReader = _sqlCommand.ExecuteReader();

while (_sqlReader.Read())
{
// do something
}
}

Thanks again for the help, I appreciate it

 

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

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+
DbProvideFactory 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)
{
DbProviderFactory dbf = DbProviderFactories.GetFactory(provider);
IDbConnection con = dbf.CreateConnection();
con.ConnectionString = constr;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM T1";
IDataReader rdr = cmd.ExecuteReader();
while(rdr.Read()) {
int f1 = (int)rdr[0];
string f2 = (string)rdr[1];
Console.WriteLine(f1 + " " + f2);
}
con.Close();
}

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

Arne

 

[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?

No difference for performance or security.

Big difference for portability between databases.

If you use only IDb interfaces and the .NET 2.0+
DbProvideFactory 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)
{
DbProviderFactory dbf = DbProviderFactories.GetFactory(provider);
IDbConnection con = dbf.CreateConnection();
con.ConnectionString = constr;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM T1";
IDataReader rdr = cmd.ExecuteReader();
while(rdr.Read()) {
int f1 = (int)rdr[0];
string f2 = (string)rdr[1];
Console.WriteLine(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.