If you havent voted, please feel free to vote for what data access strategy here:
http://wildermuth.com/2008/03/21/Data_Access_Strategies.aspx
For those who were interested in the results:
One thing that I took away from this is that some strategies did not get any votes (and I expected them to) like CSLA. I think that's a product more of the fact that Rocky's readers probably don't read my blog...so the poll is very unscientific. I was surprised by the sheer number of LINQ for SQL respondents and the big Entity Framework numbers. Both being new technologies, I didn't expect there to be much usage yet. Go figure.
I had interesting conversations with a number of people about different data access/ORM strategies at MIX recently and was trying to understand where people are spending their efforts in consuming data. The conversation was essentially a discussion of who is using what to access data in .NET applications. I had assumed that certain solutions were widely used and others were not but I didn't have a good idea of what the market was really like. To help me with this I am asking you (my readers) to share with me where you are investing time in data access by taking the following poll:
Its been a busy weekend for Microsoft. First the announcement that EDM is being delayed six months to be after Orcas...now two new dynamic data access layers for web-based apps:
- Astoria: Expose your data as web services for AJAX
- Jasper: Another RAD solution to Data Access
I haven't looked at either of these *yet*, but I thought it was already confusing...now look a the data access toolbox from MS:
DataSets...LINQ for SQL (Server)...EDM...Astoria...Jasper
I can't imagine that the Data team is going to be able to give good enough advice as to where to use what. Hopefully after I dig in deeper, it'll make more sense.
I was reading this codinghorror.com entry today and thought someone else was advocating Guids as keys when I saw mention of something I hadn't seen before:
NEWSEQUENTIALID() Function in SQL Server 2005
This function (which is only valid in a columns's DEFAULT constraint) will make GUIDs that are somewhat sequential which eliminates the biggest complaint of mine (fragmented PK Indexes). It is SQL Server 2005 only, but its a cool feature. To be clear this is how its used:
CREATE TABLE Employee
(EmployeeID uniqueidentifier DEFAULT NEWSEQUENTIALID())
It looks like Microsoft has cancelled FoxPro 10 and will be open-sourcing the work they've done on it. I worked with it, but that was pre-MS and many many years ago. I feel for the FoxPro MVP's that heard this today.
For those of use who have spent any time in the sample AdventureWorks database, I just found a handy Visio (or HTML) database diagram of the database. Its clean and explains some of their ideas about using schemas in SQL Server. Worth a look if you've ever looked at the AW database.
Chris Sells asked me today if there was a re-usable connection string user interface that I knew of. I'd heard that you could use the dialog from Visual Studio, but I had to dig in and remember how. I've put together this quick and dirty example for downloading.
The trick is two fold:
- You need to add Microsoft.Data.ConnectionUI.dll and MicrosoftData.ConnectionUI.Dialog.dll assemblies to your project. (they are found i the VS2005/Common7/IDE directory)
- Next you need to construct the dialog like any other, but first fill it with the standard providers as well as use the static Show method:
DataConnectionDialog dlg = new DataConnectionDialog();
DataSource.AddStandardDataSources(dlg);
if (DataConnectionDialog.Show(dlg) == DialogResult.OK)
{
textBox1.Text = dlg.ConnectionString;
textBox2.Text = dlg.SelectedDataProvider.DisplayName;
}
The sample includes how to find out if the user wants to save the provider and skip that part of the dialog everytime. Let me kow what you think!
I got thinking about writing custom paging code with the new SQL Server 2005 ROW_NUMBER, RANK, and DENSE_RANK functions. I started playing around with code and ended up with this simple ASP.NET 2.0 example. It works with SQL Server 2005 and the Adventure Works sample database. The example uses ad-hoc SQL so it would be easy to show how it works, but moving it into stored procs would be simple.
I'd love any feedback!
PagingDemo.zip
I had an interesting discussion with some members of a class I am teaching right now about how paging is done. It looks like there are four Functions that allow for paging and using Common Table Expressions. They all can optionally include a separate ORDER BY clause to specify how they are sorted (so your result set can be sorted differently than it is ranked).
ROW_NUMBER()
This function returns the exact number of the row number in the result. For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, ROW_NUMBER() as RowNum
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE RowNum BETWEEN 1 AND 5
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (ROW_NUMBER() OVER (ORDER BY SalesYTD)) as RowNum
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE RowNum BETWEEN 1 AND 5
ORDER BY LastName
RANK()
This function returns the ranking of the row in the result. Rows with identical results in the ordering will return the same rank (e.g. Two sales people with sales of $1,000 will get the same ranking). For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, RANK() as Ranking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE Ranking BETWEEN 1 AND 5
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (RANK() OVER (ORDER BY SalesYTD)) as Ranking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE Ranking BETWEEN 1 AND 5
ORDER BY LastName
DENSE_RANK()
Same as RANK above except there will not be any gaps (e.g. If rank 3rd and 4th are tied, they will both be RANK'd 3. With DENSE_RANK the next row will be ranked 4 instead of 5 (as RANK would do). For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, DENSE_RANK() as DenseRanking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE DenseRanking BETWEEN 1 AND 5
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (DENSE_RANK() OVER (ORDER BY SalesYTD)) as DenseRanking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE DenseRanking BETWEEN 1 AND 5
ORDER BY LastName
NTILE(x)
Separates the result into x number of batches. When you specify NTILE you must specify the number of 'tiles' to create. For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, NTILE(5) as PageNumber
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE PageNumber = 1
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (NTILE(5) OVER (ORDER BY SalesYTD)) as PageNumber
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE PageNumber = 1
ORDER BY LastName
This is fun!
