|
ms
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SqlConnection vs IDbConnectionI 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 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. -- Show quoteHide quote- Nicholas Paldino [.NET/C# MVP] - mvp@spam.guard.caspershouse.com "JoeW" <teh.sn1***@gmail.com> wrote in message news:1176487111.667041.30200@y80g2000hsf.googlegroups.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 > On Apr 13, 2:24 pm, "Nicholas Paldino [.NET/C# MVP]"
<m...@spam.guard.caspershouse.com> wrote: Show quoteHide quote > JoeW, Ahh, I see. So this code would be fine?> > 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] > - m...@spam.guard.caspershouse.com > > "JoeW" <teh.sn1***@gmail.com> wrote in message > > news:1176487111.667041.30200@y80g2000hsf.googlegroups.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 - 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,
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. -- Show quoteHide quote- Nicholas Paldino [.NET/C# MVP] - mvp@spam.guard.caspershouse.com "JoeW" <teh.sn1***@gmail.com> wrote in message news:1176489766.029398.229220@w1g2000hsg.googlegroups.com... > On Apr 13, 2:24 pm, "Nicholas Paldino [.NET/C# MVP]" > <m...@spam.guard.caspershouse.com> wrote: >> 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] >> - m...@spam.guard.caspershouse.com >> >> "JoeW" <teh.sn1***@gmail.com> wrote in message >> >> news:1176487111.667041.30200@y80g2000hsf.googlegroups.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(selectQuery, _sqlConnector); > > _sqlCommand.Parameters.AddWithValue("@url", pUrl); > _sqlCommand.Parameters.AddWithValue("@ip", pIp); > _sqlCommand.Prepare(); > On Apr 13, 2:49 pm, "Nicholas Paldino [.NET/C# MVP]"
<m...@spam.guard.caspershouse.com> wrote: Show quoteHide quote > JoeW, Does it matter if the connection is opened before or after the prepare> > 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] > - m...@spam.guard.caspershouse.com > > "JoeW" <teh.sn1***@gmail.com> wrote in message > > news:1176489766.029398.229220@w1g2000hsg.googlegroups.com... > > > > > On Apr 13, 2:24 pm, "Nicholas Paldino [.NET/C# MVP]" > > <m...@spam.guard.caspershouse.com> wrote: > >> 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] > >> - m...@spam.guard.caspershouse.com > > >> "JoeW" <teh.sn1***@gmail.com> wrote in message > > >>news:1176487111.667041.30200@y80g2000hsf.googlegroups.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(selectQuery, _sqlConnector); > > > _sqlCommand.Parameters.AddWithValue("@url", pUrl); > > _sqlCommand.Parameters.AddWithValue("@ip", pIp); > > _sqlCommand.Prepare();- Hide quoted text - > > - Show quoted text - 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 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 =) -- Show quoteHide quote- Nicholas Paldino [.NET/C# MVP] - mvp@spam.guard.caspershouse.com "JoeW" <teh.sn1***@gmail.com> wrote in message news:1176513020.939917.305690@p77g2000hsh.googlegroups.com... > On Apr 13, 2:49 pm, "Nicholas Paldino [.NET/C# MVP]" > <m...@spam.guard.caspershouse.com> wrote: >> 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] >> - m...@spam.guard.caspershouse.com >> >> "JoeW" <teh.sn1***@gmail.com> wrote in message >> >> news:1176489766.029398.229220@w1g2000hsg.googlegroups.com... >> >> >> >> > On Apr 13, 2:24 pm, "Nicholas Paldino [.NET/C# MVP]" >> > <m...@spam.guard.caspershouse.com> wrote: >> >> 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] >> >> - m...@spam.guard.caspershouse.com >> >> >> "JoeW" <teh.sn1***@gmail.com> wrote in message >> >> >>news:1176487111.667041.30200@y80g2000hsf.googlegroups.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(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 > "JoeW" <teh.sn1***@gmail.com> wrote in message The reason you would code against the interface (or the abstract class news:1176487111.667041.30200@y80g2000hsf.googlegroups.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? 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 Show quoteHide quote > > Thanks for any suggestions > JoeW wrote:
> I normally use SqlConnection, SqlDataReader, SqlCommand for most of my No difference for performance or security.> 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? 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 On Apr 14, 12:29 pm, Arne Vajhøj <a***@vajhoej.dk> wrote:
Show quoteHide quote > JoeW wrote: Great advice guys, thanks again for your help. That helped me out a> > 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 lot.  
Other interesting topics
Marshall a call onto an instance of Thread
Pressing Enter on ListBox should invoke DoubleClick how to do a call back between forms Why public method in internal class is allowed Login failed SQL Express interface to web service .NET Remoting, Event Subscription to the Remoting Object / Subscribed event does not fire... How to make a form of main application reuseable by others? Page load vs. reload ? double or single \\ in config file |
|||||||||||||||||||||||
