|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
OLEDBCommand vs. SQLCommandWhen does one use one or the other? And why? I cannot imagine too many
innstances where you would not want to use SQL statements when you communicating with a database - even on a read-only basis. Plus there is the ODBCCommand class too - I understand that is an older implementation and is not used in .Net much, correct? Thanx for any clarifications and perhaps web links to discussion on this. I might be wrong here, but someone will surely correct me if I am:
As far as SQL statements go, all three commands use them to talk to the database, the prefix before "Command" or "Connection" is an indicator of what it can access, not how. With that said, the OleDbCommand's are for communicating with any OLEDB providing databases, like Oracle, Access, SQL Server, Excel, etc. However the SQLCommand is created especially for Microsoft's SQL Server databases, providing some SQL server specific features and better performance. Finally ODBC is used for ODBC complaint databases (almost all databases are). However, ODBC requires that you set up a DSN (data source name) before using it - or at least it used to - and is less efficient than using OleDbCommands or SQLCommands, but may be your only choice depending on the database. Does that help? Thanks, Seth Rowe Anil Gupte wrote: Show quoteHide quote > When does one use one or the other? And why? I cannot imagine too many > innstances where you would not want to use SQL statements when you > communicating with a database - even on a read-only basis. Plus there is > the ODBCCommand class too - I understand that is an older implementation and > is not used in .Net much, correct? > > Thanx for any clarifications and perhaps web links to discussion on this. > -- > Anil Gupte > www.keeninc.net > www.icinema.com rowe_newsgroups ha scritto:
> I might be wrong here, but someone will surely correct me if I am: I have been always wondering about that. I have been using the OLEDB> > As far as SQL statements go, all three commands use them to talk to the > database, the prefix before "Command" or "Connection" is an indicator > of what it can access, not how. With that said, the OleDbCommand's are > for communicating with any OLEDB providing databases, like Oracle, > Access, SQL Server, Excel, etc. However the SQLCommand is created > especially for Microsoft's SQL Server databases, providing some SQL > server specific features and better performance. against SQL server and it is fast. Is there any reason why , for instance, a query sent by the SQLCommand should be faster than the same query sent via an OLEDBcommand ? And id the sql reader faster than the OLEdb reader ? I do not have this feeling. Has anyone some test about that? I think that using OLEDB may give the advantage that if you change the underlying DB you do not have to change the code. And this seems to be a huge reason to use it whenever possible. Also the possibility to access the GUIDs is fundamental. And this is another huge reason. Actually I am not clear why the need sort of specialized connectors. If there is a form of a specialization a generic connector should be smart enough to use specialized function when connected to specific DBMS. What not make all the oledb way? What do you guys think ?? Finally ODBC is used Show quoteHide quote > for ODBC complaint databases (almost all databases are). However, ODBC > requires that you set up a DSN (data source name) before using it - or > at least it used to - and is less efficient than using OleDbCommands or > SQLCommands, but may be your only choice depending on the database. > > Does that help? > > Thanks, > > Seth Rowe > > > Anil Gupte wrote: > > When does one use one or the other? And why? I cannot imagine too many > > innstances where you would not want to use SQL statements when you > > communicating with a database - even on a read-only basis. Plus there is > > the ODBCCommand class too - I understand that is an older implementation and > > is not used in .Net much, correct? > > > > Thanx for any clarifications and perhaps web links to discussion on this. > > -- > > Anil Gupte > > www.keeninc.net > > www.icinema.com Personally, I almost always use the OleDb classes. This is mainly
because when I wrote my "superclass" I wrote it to be compatible with all the servers my company uses, meaning either OleDb or ODBC (and I hate ODBC). If all we had were SQL Server databases I might change my story but until then I'll stick with Oledb. I did a few searches for the differences and one post from Bill Vaughn said the the OleDb class realies on COM interop while the SQLClient is a truely native to SQL Server versions 7 and up. Hopefully someone will post a link to the differences between the two. Thanks, Seth Rowe pamelaflue***@libero.it wrote: Show quoteHide quote > rowe_newsgroups ha scritto: > > > I might be wrong here, but someone will surely correct me if I am: > > > > As far as SQL statements go, all three commands use them to talk to the > > database, the prefix before "Command" or "Connection" is an indicator > > of what it can access, not how. With that said, the OleDbCommand's are > > for communicating with any OLEDB providing databases, like Oracle, > > Access, SQL Server, Excel, etc. However the SQLCommand is created > > especially for Microsoft's SQL Server databases, providing some SQL > > server specific features and better performance. > > I have been always wondering about that. I have been using the OLEDB > against SQL server and it is fast. Is there any reason why , for > instance, > a query sent by the SQLCommand should be faster than the same query > sent via an OLEDBcommand ? And id the sql reader faster than the OLEdb > reader ? > > I do not have this feeling. Has anyone some test about that? > > I think that using OLEDB may give the advantage that if you change > the underlying DB you do not have to change the code. And this seems to > be a huge > reason to use it whenever possible. > Also the possibility to access the GUIDs is fundamental. And this is > another huge reason. > > Actually I am not clear why the need sort of specialized connectors. If > there is a form of a specialization a generic connector should be smart > enough to use specialized function when connected to specific DBMS. > > What not make all the oledb way? What do you guys think ?? > > > Finally ODBC is used > > for ODBC complaint databases (almost all databases are). However, ODBC > > requires that you set up a DSN (data source name) before using it - or > > at least it used to - and is less efficient than using OleDbCommands or > > SQLCommands, but may be your only choice depending on the database. > > > > Does that help? > > > > Thanks, > > > > Seth Rowe > > > > > > Anil Gupte wrote: > > > When does one use one or the other? And why? I cannot imagine too many > > > innstances where you would not want to use SQL statements when you > > > communicating with a database - even on a read-only basis. Plus there is > > > the ODBCCommand class too - I understand that is an older implementation and > > > is not used in .Net much, correct? > > > > > > Thanx for any clarifications and perhaps web links to discussion on this. > > > -- > > > Anil Gupte > > > www.keeninc.net > > > www.icinema.com Thanx Seth & Pamela, that does help. I also realized that MS Acesss is not
just a scaled down SQL server, but accorind to my book it has a different (JET) database engine. It is just an assumption that I made, thinking Access is SQL Server lite. Show quoteHide quote "rowe_newsgroups" <rowe_em***@yahoo.com> wrote in message news:1165414658.905575.17400@79g2000cws.googlegroups.com... > Personally, I almost always use the OleDb classes. This is mainly > because when I wrote my "superclass" I wrote it to be compatible with > all the servers my company uses, meaning either OleDb or ODBC (and I > hate ODBC). If all we had were SQL Server databases I might change my > story but until then I'll stick with Oledb. I did a few searches for > the differences and one post from Bill Vaughn said the the OleDb class > realies on COM interop while the SQLClient is a truely native to SQL > Server versions 7 and up. > > Hopefully someone will post a link to the differences between the two. > > Thanks, > > Seth Rowe > > > pamelaflue***@libero.it wrote: >> rowe_newsgroups ha scritto: >> >> > I might be wrong here, but someone will surely correct me if I am: >> > >> > As far as SQL statements go, all three commands use them to talk to the >> > database, the prefix before "Command" or "Connection" is an indicator >> > of what it can access, not how. With that said, the OleDbCommand's are >> > for communicating with any OLEDB providing databases, like Oracle, >> > Access, SQL Server, Excel, etc. However the SQLCommand is created >> > especially for Microsoft's SQL Server databases, providing some SQL >> > server specific features and better performance. >> >> I have been always wondering about that. I have been using the OLEDB >> against SQL server and it is fast. Is there any reason why , for >> instance, >> a query sent by the SQLCommand should be faster than the same query >> sent via an OLEDBcommand ? And id the sql reader faster than the OLEdb >> reader ? >> >> I do not have this feeling. Has anyone some test about that? >> >> I think that using OLEDB may give the advantage that if you change >> the underlying DB you do not have to change the code. And this seems to >> be a huge >> reason to use it whenever possible. >> Also the possibility to access the GUIDs is fundamental. And this is >> another huge reason. >> >> Actually I am not clear why the need sort of specialized connectors. If >> there is a form of a specialization a generic connector should be smart >> enough to use specialized function when connected to specific DBMS. >> >> What not make all the oledb way? What do you guys think ?? >> >> >> Finally ODBC is used >> > for ODBC complaint databases (almost all databases are). However, ODBC >> > requires that you set up a DSN (data source name) before using it - or >> > at least it used to - and is less efficient than using OleDbCommands or >> > SQLCommands, but may be your only choice depending on the database. >> > >> > Does that help? >> > >> > Thanks, >> > >> > Seth Rowe >> > >> > >> > Anil Gupte wrote: >> > > When does one use one or the other? And why? I cannot imagine too >> > > many >> > > innstances where you would not want to use SQL statements when you >> > > communicating with a database - even on a read-only basis. Plus >> > > there is >> > > the ODBCCommand class too - I understand that is an older >> > > implementation and >> > > is not used in .Net much, correct? >> > > >> > > Thanx for any clarifications and perhaps web links to discussion on >> > > this. >> > > -- >> > > Anil Gupte >> > > www.keeninc.net >> > > www.icinema.com > I think under certain circumstances, the SqlClient namespace classes are
going to provide you with better performance. They may also provide you with more access to SQL Server specific functionality. If your application does not need any of this functionality, and what you are doing is not something that the SqlClient class can optimize for you, then I suppose you can use OleDb so that you can swap out different types of databases. If you are always using SQL Server though, then I see no reason to not just always use the classes in SqlClient. Show quoteHide quote "Anil Gupte" <anil-l***@icinema.com> wrote in message news:uqMw73SGHHA.4116@TK2MSFTNGP05.phx.gbl... > When does one use one or the other? And why? I cannot imagine too many > innstances where you would not want to use SQL statements when you > communicating with a database - even on a read-only basis. Plus there is > the ODBCCommand class too - I understand that is an older implementation > and is not used in .Net much, correct? > > Thanx for any clarifications and perhaps web links to discussion on this. > -- > Anil Gupte > www.keeninc.net > www.icinema.com > > Thanx for the clarification. By the way, it surprises me that Oracle is not
SQL compliant - assuming that SQL is a general specification, not something invented by MS. Show quoteHide quote "Marina Levit [MVP]" <someone@nospam.com> wrote in message news:OeaZDNUGHHA.4712@TK2MSFTNGP04.phx.gbl... >I think under certain circumstances, the SqlClient namespace classes are >going to provide you with better performance. They may also provide you >with more access to SQL Server specific functionality. > > If your application does not need any of this functionality, and what you > are doing is not something that the SqlClient class can optimize for you, > then I suppose you can use OleDb so that you can swap out different types > of databases. If you are always using SQL Server though, then I see no > reason to not just always use the classes in SqlClient. > > "Anil Gupte" <anil-l***@icinema.com> wrote in message > news:uqMw73SGHHA.4116@TK2MSFTNGP05.phx.gbl... >> When does one use one or the other? And why? I cannot imagine too many >> innstances where you would not want to use SQL statements when you >> communicating with a database - even on a read-only basis. Plus there is >> the ODBCCommand class too - I understand that is an older implementation >> and is not used in .Net much, correct? >> >> Thanx for any clarifications and perhaps web links to discussion on this. >> -- >> Anil Gupte >> www.keeninc.net >> www.icinema.com >> >> > > Sorry, I don't know what you mean. Most of the things that run on SQL Server
are going to run on Oracle too. But every database system has its own functions or additional syntax. Show quoteHide quote "Anil Gupte" <anil-l***@icinema.com> wrote in message news:ekYE99VGHHA.5000@TK2MSFTNGP03.phx.gbl... > Thanx for the clarification. By the way, it surprises me that Oracle is > not SQL compliant - assuming that SQL is a general specification, not > something invented by MS. > > -- > Anil Gupte > www.keeninc.net > www.icinema.com > > "Marina Levit [MVP]" <someone@nospam.com> wrote in message > news:OeaZDNUGHHA.4712@TK2MSFTNGP04.phx.gbl... >>I think under certain circumstances, the SqlClient namespace classes are >>going to provide you with better performance. They may also provide you >>with more access to SQL Server specific functionality. >> >> If your application does not need any of this functionality, and what you >> are doing is not something that the SqlClient class can optimize for you, >> then I suppose you can use OleDb so that you can swap out different types >> of databases. If you are always using SQL Server though, then I see no >> reason to not just always use the classes in SqlClient. >> >> "Anil Gupte" <anil-l***@icinema.com> wrote in message >> news:uqMw73SGHHA.4116@TK2MSFTNGP05.phx.gbl... >>> When does one use one or the other? And why? I cannot imagine too many >>> innstances where you would not want to use SQL statements when you >>> communicating with a database - even on a read-only basis. Plus there >>> is the ODBCCommand class too - I understand that is an older >>> implementation and is not used in .Net much, correct? >>> >>> Thanx for any clarifications and perhaps web links to discussion on >>> this. >>> -- >>> Anil Gupte >>> www.keeninc.net >>> www.icinema.com >>> >>> >> >> > > On Wed, 6 Dec 2006 23:23:45 +0530, Anil Gupte wrote:
> Thanx for the clarification. By the way, it surprises me that Oracle is not How do you mean SQL Compliant?> SQL compliant - assuming that SQL is a general specification, not something > invented by MS. All vendors support a standard SQL dialect, and then each vendor extends this in their own way. > Thanx for the clarification. By the way, it surprises me that Oracle is not I think you're slightly confused. "SQL" stands for structured query> SQL compliant - assuming that SQL is a general specification, not something > invented by MS. language and is used by almost all types of databases. Each database molds the language in order to fit their product better, but all "flavors" closely resemble each other. For example, Microsoft's SQL Server uses T-SQL (transact SQL) to execute queries against it's tables, while Oracle uses "Oracle SQL" to execute their queries. An example of one of these changes in T-SQL is the "Inner Join" and "TOP #" statements, these are not supported in Oracle, but Oracle can do the same thing with just a few changes: i.e. //T-SQL Command SELECT TOP 10 * FROM tbl_Whatever INNER JOIN tbl_Something ON tbl_Something.Somefield = tbl_Whatever.SomeField //The Same Command in Oracle SELECT * FROM tbl_Whatever, tbl_Something WHERE tbl_Something.Somefield = tbl_Whatever.SomeField AND rownum <= 10 Both of the above are considered to be SQL statements and both do the same thing in there respective enviroments. The SQLClient classes are just optimized to work with SQLServer, and will only work with SQL Server. If you want to connect to Oracle database you have to use the OleDb class. You can send SQL statements through the OleDb class to sn Oracle database without any trouble - as long as they are valid SQL statements (see above - if you pass the first SQL statement to Oracle it will throw an error since it doesn't support Transact SQL) In summary: You can use either the SQLClient class, OleDb class, or ODBC class to pass SQL statements to Microsoft SQL Server You can use either the OleDb class or ODBC class to pass SQL statements to any OleDb or ODBC complaint database (like Oracle, Access, Excel, etc) You can use the ODBC class to pass SQL statements to a ODBC compliant database (Mimer, Firebird, etc) Does that help explain it? Thanks, Seth Rowe Anil Gupte wrote: Show quoteHide quote > Thanx for the clarification. By the way, it surprises me that Oracle is not > SQL compliant - assuming that SQL is a general specification, not something > invented by MS. > > -- > Anil Gupte > www.keeninc.net > www.icinema.com > > "Marina Levit [MVP]" <someone@nospam.com> wrote in message > news:OeaZDNUGHHA.4712@TK2MSFTNGP04.phx.gbl... > >I think under certain circumstances, the SqlClient namespace classes are > >going to provide you with better performance. They may also provide you > >with more access to SQL Server specific functionality. > > > > If your application does not need any of this functionality, and what you > > are doing is not something that the SqlClient class can optimize for you, > > then I suppose you can use OleDb so that you can swap out different types > > of databases. If you are always using SQL Server though, then I see no > > reason to not just always use the classes in SqlClient. > > > > "Anil Gupte" <anil-l***@icinema.com> wrote in message > > news:uqMw73SGHHA.4116@TK2MSFTNGP05.phx.gbl... > >> When does one use one or the other? And why? I cannot imagine too many > >> innstances where you would not want to use SQL statements when you > >> communicating with a database - even on a read-only basis. Plus there is > >> the ODBCCommand class too - I understand that is an older implementation > >> and is not used in .Net much, correct? > >> > >> Thanx for any clarifications and perhaps web links to discussion on this. > >> -- > >> Anil Gupte > >> www.keeninc.net > >> www.icinema.com > >> > >> > > > > One clarification. You do not *have* to use OleDb for Oracle. Microsoft
ships an oracle proider with the 2.0 framework, and Oracle also has its own which you can download from their site. Show quoteHide quote "rowe_newsgroups" <rowe_em***@yahoo.com> wrote in message news:1165434821.761265.283470@j44g2000cwa.googlegroups.com... >> Thanx for the clarification. By the way, it surprises me that Oracle is >> not >> SQL compliant - assuming that SQL is a general specification, not >> something >> invented by MS. > > I think you're slightly confused. "SQL" stands for structured query > language and is used by almost all types of databases. Each database > molds the language in order to fit their product better, but all > "flavors" closely resemble each other. For example, Microsoft's SQL > Server uses T-SQL (transact SQL) to execute queries against it's > tables, while Oracle uses "Oracle SQL" to execute their queries. An > example of one of these changes in T-SQL is the "Inner Join" and "TOP > #" statements, these are not supported in Oracle, but Oracle can do the > same thing with just a few changes: > > i.e. > > //T-SQL Command > > SELECT TOP 10 * > FROM tbl_Whatever > INNER JOIN tbl_Something ON tbl_Something.Somefield = > tbl_Whatever.SomeField > > //The Same Command in Oracle > > SELECT * > FROM tbl_Whatever, tbl_Something > WHERE tbl_Something.Somefield = tbl_Whatever.SomeField > AND rownum <= 10 > > Both of the above are considered to be SQL statements and both do the > same thing in there respective enviroments. The SQLClient classes are > just optimized to work with SQLServer, and will only work with SQL > Server. If you want to connect to Oracle database you have to use the > OleDb class. You can send SQL statements through the OleDb class to sn > Oracle database without any trouble - as long as they are valid SQL > statements (see above - if you pass the first SQL statement to Oracle > it will throw an error since it doesn't support Transact SQL) > > In summary: > > You can use either the SQLClient class, OleDb class, or ODBC class to > pass SQL statements to Microsoft SQL Server > > You can use either the OleDb class or ODBC class to pass SQL statements > to any OleDb or ODBC complaint database (like Oracle, Access, Excel, > etc) > > You can use the ODBC class to pass SQL statements to a ODBC compliant > database (Mimer, Firebird, etc) > > Does that help explain it? > > Thanks, > > Seth Rowe > > > Anil Gupte wrote: >> Thanx for the clarification. By the way, it surprises me that Oracle is >> not >> SQL compliant - assuming that SQL is a general specification, not >> something >> invented by MS. >> >> -- >> Anil Gupte >> www.keeninc.net >> www.icinema.com >> >> "Marina Levit [MVP]" <someone@nospam.com> wrote in message >> news:OeaZDNUGHHA.4712@TK2MSFTNGP04.phx.gbl... >> >I think under certain circumstances, the SqlClient namespace classes are >> >going to provide you with better performance. They may also provide you >> >with more access to SQL Server specific functionality. >> > >> > If your application does not need any of this functionality, and what >> > you >> > are doing is not something that the SqlClient class can optimize for >> > you, >> > then I suppose you can use OleDb so that you can swap out different >> > types >> > of databases. If you are always using SQL Server though, then I see no >> > reason to not just always use the classes in SqlClient. >> > >> > "Anil Gupte" <anil-l***@icinema.com> wrote in message >> > news:uqMw73SGHHA.4116@TK2MSFTNGP05.phx.gbl... >> >> When does one use one or the other? And why? I cannot imagine too >> >> many >> >> innstances where you would not want to use SQL statements when you >> >> communicating with a database - even on a read-only basis. Plus there >> >> is >> >> the ODBCCommand class too - I understand that is an older >> >> implementation >> >> and is not used in .Net much, correct? >> >> >> >> Thanx for any clarifications and perhaps web links to discussion on >> >> this. >> >> -- >> >> Anil Gupte >> >> www.keeninc.net >> >> www.icinema.com >> >> >> >> >> > >> > > On Wed, 6 Dec 2006 15:11:52 -0500, Marina Levit [MVP] wrote:
> One clarification. You do not *have* to use OleDb for Oracle. Microsoft From what I gather the Oracle one works better with the dizzying array of> ships an oracle proider with the 2.0 framework, and Oracle also has its own > which you can download from their site. > things Oracle does ... might be a better bet for an Oracle application Right, that is what I have heard too.
However, if one does not really want to deal with downloading and installing it, then at least there is the one that Microsoft ships. Show quoteHide quote "Rad [Visual C# MVP]" <nospam@nospam.com> wrote in message news:ix0q5pwvu8tn$.hdbugdmonuyt$.dlg@40tude.net... > On Wed, 6 Dec 2006 15:11:52 -0500, Marina Levit [MVP] wrote: > >> One clarification. You do not *have* to use OleDb for Oracle. Microsoft >> ships an oracle proider with the 2.0 framework, and Oracle also has its >> own >> which you can download from their site. >> > > From what I gather the Oracle one works better with the dizzying array of > things Oracle does ... might be a better bet for an Oracle application > -- > Bits.Bytes > http://bytes.thinkersroom.com Anil,
The normal advice is Use a for a Database special made provider if that is available they are mostly better scaled for that than OleDB. If that is not, than use OleDB as long as you have the change to create the connection (like password etc). If it is completely closed than you can use ODBC. To overcome the problems with different types of databases there is now the factory. What I have seen in this thread as confusion. If there would be a price for giving the worst names to products, Microsoft would win in my idea the first the second and the thirth place. SQL server is just a name for a database server. It uses the SQL transact language to process that in the same way as most DataBase servers as Oracle do. Cor Show quoteHide quote "Anil Gupte" <anil-l***@icinema.com> schreef in bericht news:uqMw73SGHHA.4116@TK2MSFTNGP05.phx.gbl... > When does one use one or the other? And why? I cannot imagine too many > innstances where you would not want to use SQL statements when you > communicating with a database - even on a read-only basis. Plus there is > the ODBCCommand class too - I understand that is an older implementation > and is not used in .Net much, correct? > > Thanx for any clarifications and perhaps web links to discussion on this. > -- > Anil Gupte > www.keeninc.net > www.icinema.com > > That is what I thought - SQL is a standard. But like every other standard,
I guess each vendor has their own version of it. My confusion was thinking that SQL is the standard connection to use (not OLEDB) since SQL would be a standard. Anyway, I get it now. Thanx (everyone) for your input. Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message news:eU2MEJcGHHA.5104@TK2MSFTNGP03.phx.gbl... > Anil, > > The normal advice is > Use a for a Database special made provider if that is available they are > mostly better scaled for that than OleDB. > > If that is not, than use OleDB as long as you have the change to create > the connection (like password etc). > > If it is completely closed than you can use ODBC. > > To overcome the problems with different types of databases there is now > the factory. > > What I have seen in this thread as confusion. > > If there would be a price for giving the worst names to products, > Microsoft would win in my idea the first the second and the thirth place. > > SQL server is just a name for a database server. It uses the SQL transact > language to process that in the same way as most DataBase servers as > Oracle do. > > Cor > > > > > "Anil Gupte" <anil-l***@icinema.com> schreef in bericht > news:uqMw73SGHHA.4116@TK2MSFTNGP05.phx.gbl... >> When does one use one or the other? And why? I cannot imagine too many >> innstances where you would not want to use SQL statements when you >> communicating with a database - even on a read-only basis. Plus there is >> the ODBCCommand class too - I understand that is an older implementation >> and is not used in .Net much, correct? >> >> Thanx for any clarifications and perhaps web links to discussion on this. >> -- >> Anil Gupte >> www.keeninc.net >> www.icinema.com >> >> > > 
Other interesting topics
Using XPath Against A Node
Close a form after x amount of time HTML help files how to use table in a dataset as data source for combobox? cUpdate Custom attribut with reflection Confusing Arraylist BinarySearch problem Need some info about the Load event of Forms and Usercontrols Insert key press in text box Re: Which do you prefer? IE toolbar show/hide |
|||||||||||||||||||||||
