| Author |
Topic |
|
alekseyd
Starting Member
5 Posts |
 Posted - 2004-08-25 : 12:59:15
|
| I need to write a data acess layer to port exsiting code from another database to Yukon. I am trying to figure out what data access method to use. The first choice is ADO.NET and SqlClient. This would work fine, but apparently it is missing some key features like cursors, positioned update, and "describe parameters". Since I don't have to sue .NET, I also consider using SQL Native Client (OLEDB), which I think provides the features I need. The recommendations I get from MS are confusing. Does anyone have a better idea which client to use?--Aleksey |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2004-08-25 : 15:43:05
|
| If you use .NET I'd suggest using ADO.NET. You might also look into some of the factory patterns that let you write generic code on top of either the OLEDB or the .NET client.OLEDB isn't the native SQL Server client. It's actually one of the slowest ways to access SQL Server.I'd look through this article http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnadonet/html/adocnet4.asp. I think it may answer many of your questions. The MSDN Data Access Homepage (http://msdn.microsoft.com/data/) has quite a few similar articles that might be very helpful.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-25 : 17:47:08
|
| aleksey,You are operating in the old paradigm with cursors and positioned updates. The new sqlclient in ADO.NET is similar to the disconnected recordset in ADO, however it handles updates differently. Now you use the DataSet and DataTable objects instead, they operate as in memory tables. -ec |
|
|
|
alekseyd
Starting Member
5 Posts |
Posted - 2004-08-25 : 17:52:19
|
As I said, I don't care if it is .NET or not. My application code is not managed and will stay that way. If I chose to use ADO.NET/SqlClient, there will be a bridge form my unmanaged code to managed SqlClient. If SQL Native Client (OLEDB or ODBC) for Yukon is not native, then what is?WHAT IS THE NATIVE API THAT PROVIDES MOST FEATURES FOR YUKON?I repeat, there are key features missing in SqlClient that I need. I need something like ODBC's SQLDescribeParam() or OLEDB's ICommandWithParameters::GetParameterInfo() methods or extend SqlCommandBuilder.DeriveParameters to support any SQL statemment.The usual answer I get is a peptalk about how great the disconnect paradigm of ADO.NET is and how bad is my code that was written 6 or so years ago following the MS recommendations du jour at that time.--AlekseyP.S. I think I am gonna write an article about it myself...quote:
Originally posted by graz
If you use .NET I'd suggest using ADO.NET. You might also look into some of the factory patterns that let you write generic code on top of either the OLEDB or the .NET client.OLEDB isn't the native SQL Server client. It's actually one of the slowest ways to access SQL Server.I'd look through this article http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnadonet/html/adocnet4.asp. I think it may answer many of your questions. The MSDN Data Access Homepage (http://msdn.microsoft.com/data/) has quite a few similar articles that might be very helpful.===============================================Creating tomorrow's legacy systems today.One crisis at a time.
--Aleksey |
|
|
|
alekseyd
Starting Member
5 Posts |
Posted - 2004-08-25 : 17:55:39
|
Ok, I have a million lines of code written for Oracle that relies on that very old paradigm. I need to migrate my code to support Yukon. What do you suggest?quote:
Originally posted by eyechart
aleksey,You are operating in the old paradigm with cursors and positioned updates. The new sqlclient in ADO.NET is similar to the disconnected recordset in ADO, however it handles updates differently. Now you use the DataSet and DataTable objects instead, they operate as in memory tables. -ec
--Aleksey |
|
|
|
alekseyd
Starting Member
5 Posts |
Posted - 2004-08-25 : 17:59:05
|
I forgot to add a quotation from MS SQL Server 2005 Beta 2 Books Online: SQL Native Client (OLEDB) is a low-level, COM API that is used for accessing data. SQLNCLI is recommended for developing tools, utilities, or low-level components that need high performance. The SQLNCLI is a native, high performance provider that accesses the SQL Server TDS protocol directly. quote:
Originally posted by alekseydIf SQL Native Client (OLEDB or ODBC) for Yukon is not native, then what is?WHAT IS THE NATIVE API THAT PROVIDES MOST FEATURES FOR YUKON?--Aleksey
--Aleksey |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-25 : 18:39:56
|
| Since we can't seem to help you, maybe it would be best for you to ask your question in the SQL 2005 newsgroups hosted by Microsoft.-ec |
|
|
|
euan_garden
Microsoft SQL Server Product Team
34 Posts |
Posted - 2004-08-27 : 01:26:04
|
| Couple of things.1/ I disagree with Graz, SQl Native Client is not one of the slowest ways to access SQL Server. a) We have not finished tuning the SNAC code it will only get faster.b) SQL Native access includes OLE DB and ODBC, both are pretty damn fast even in SQL2000.2/ SNAC is the preferred way for native apps to connect to SQL Server, you can use ODBC, OLE DB or ADO there are trade offs in these APIs that have been discussed outwith the Yukon context that are still valid. Features such as MARS and the XML-DT are available in all, SQLCLR UDT support is limited in them all.The docs are wrong(its a beta) SNAC is not COM, OLE DB is, SNAC also contains ODBC which is not COM.We do not support access to TDS directly which is our wire protocol.-EuanEuan GardenProduct Unit ManagerSQL Server ToolsMicrosoft Corp |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-27 : 01:28:49
|
quote:
Originally posted by euan_gardenWe do not support access to TDS directly which is our wire protocol.
Your docs seem to indicate that you do. Apparently they are wrong.btw, good to have you here.-ec |
|
|
|
euan_garden
Microsoft SQL Server Product Team
34 Posts |
Posted - 2004-08-27 : 01:32:49
|
| Can you tell me where the docs say you can reference TDS so I can get it fixed please?Thx-Euan |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-27 : 01:41:59
|
quote:
Originally posted by euan_garden
Can you tell me where the docs say you can reference TDS so I can get it fixed please?Thx-Euan
It is in the SQL 2005 BOL. I think aleksey actually quoted it above quote:
SQL Native Client (OLEDB) is a low-level, COM API that is used for accessing data. SQLNCLI is recommended for developing tools, utilities, or low-level components that need high performance. The SQLNCLI is a native, high performance provider that accesses the SQL Server TDS protocol directly.
-ec |
|
|
|
euan_garden
Microsoft SQL Server Product Team
34 Posts |
Posted - 2004-08-27 : 01:45:17
|
| Ah I may have confused things.You can use SNAC to access SQL Server, that goes via TDS as SQLClient does. The below is saying that the native libs (SNAC) go to TDS directly which means they are fast.However we do not support you writing code to TDS, which is what I was saying.-Euan |
|
|
|
alekseyd
Starting Member
5 Posts |
Posted - 2004-08-28 : 15:38:19
|
| Euan,Do the SNAC ODBC and OLEDB provide identical functionally? I remember a few years ago, when SQL 2000 was released, MS said that ODBC would be frozen and new functionality would go to OLEDB. My employer at that time had to rewrite ODBC code to OLEDB. Now it sounds like MS promises that functionality in ODBC and OLEDB is the same and it supports all new features. I wish I knew it at that time, I would not bother migrating to OLEDB then. --Aleksey |
|
|
|
euan_garden
Microsoft SQL Server Product Team
34 Posts |
Posted - 2004-08-28 : 18:47:40
|
| That was the decision we made at the time, but we have tried to add what we can to ODBC, although we have not been able to add everything that was added to OLE DB or SQLClient.-Euan |
|
|
|
|
SqlClient vs. Native Client