Stories Tagged with 'Databases'


ROW_NUMBER, RANK, DENSERANK and NTILE

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()

Oracle 11g Announced

Url: http://www.computerworld.com/action/article.do?...

SQL Server 2005 Paging Demo

Url: http://wilderminds.blob.core.windows.net/downloads/pagingdemo.zip

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. 

Using the Visual Studio Connection String Dialog

Url: http://wilderminds.blob.core.windows.net/downloads/TestDataConnect...

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.

AdventureWorks Database Diagram (Visio or HTML)

Url: http://www.microsoft.com/downloads/details.aspx...

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.

Vista RC1 Doesn't work with SQL Server 2005

If you upgrade to RC1 be aware that SQL Server 2005 requires SP2 before it will work with Vista RC1.  Yeah, I know there is no SP2...but that's the case.  It just doesn't work. I am trying to hack around the problem so I'll let you know if I find a solution...

 

Atlanta C# Users Group - SQLCLR Presenation

Url: http://www.atlantacsharp.org/NextMeeting.aspx

If you're in Atlanta and want to see me talk about the SQL Server CLR Integration, come by the Atlanta C# Users Group on May 1st (That's today).  Meeting starts at 6pm.  Here's a link to the User Group's Site:

SQL Packager and SQL Server MDF Formats

Url: http://www.red-gate.com/products/SQL_Packager/i...

In a current project I am working on with a distributed team, we use a set of detached database (mdf/ldf) files to keep current the latest version of the database we're working on. I use SQL Server 2000 and 2005 on my local dev-box, but since this customer is going to use SQL Server 2000 I've been trying to keep the work on SQL Server 2000. 

SQL Server Timestamps and ADO.NET Concurrency

Url: http://msdn2.microsoft.com/en-US/library/system...

I was talking with a bright guy inside the ADO.NET team today when he told me that the DbCommandBuilder supports a new option called ConflictOption. This option alerts the DbCommandBuilder to use one of three methods for detecting concurrency conflicts:

What ADO.NET 2.0 Book Do I Recommend?

Url: http://www.amazon.com/exec/obidos/ASIN/15905951...

Since my announcement last year that there would not be an update to my book ("Pragmatic ADO.NET") to show the changes in ADO.NET 2.0, I have been asked what book I would recommend. I have scoured the ADO.NET 2.0 books and finally have a firm recommendation:

Another xkdc Gem for the Data Lover in All of Us

Url: http://xkcd.com/327/

I loves the funny!

OMG! Too Many Ways to do Data Access with .NET?

Url: http://visitmix.com

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:

RIP: FoxPro, 1984-2007

Url: http://entmag.com/news/rss.asp?editorialsid=8320#1

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.

Guids as Keys

Url: http://www.codinghorror.com/blog/archives/00081...

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:

VS.NET and SQL Server "Express"?

Url: http://news.com.com/Microsoft+reaches+beyond+pr...

Looks like MS is going after the 'hobbyist' with their new “Express” line of development products.  Wonder if this will really stem the MySQL tide or not.  Perhaps the SQL Server Express edition will be a better solution than MSDE.

No Yukon and VS.NET Betas at TechEd Europe?

Url: http://news.com.com/Microsoft+reaches+beyond+pr...

Evidently MS slipped on thier pledge to deliver the betas at the conference.  This article implies that they are claiming that the betas are “imminent“...wonder what that really means?  Q3?  Q4?

UML/Database Modeler that I actually like!

Url: http://www.sparxsystems.com

While trolling the DevelopMentor .NET Lists, William D. Bartholomew mentioned a new UML tool that does round-tripping to and from code.  I've used Rational's (IBM's) XDE before to do this, but it is buggy and hates source code control. 

Connections, Command and Transactions...oh my!

I was taking a refresher MCSD test today to get ready to take one of the tests and came upon a question that is wrong.  But it does infer that there is some confusion about how transactions are propogated to commands...or may be evidence that it is a bug.  For example:

SqlConnection conn = null;
SqlTransaction tx = null;
try
{
  // Create a new Connection
  conn = new SqlConnection("Server=.;Database=Northwind;Integrated Security=true;");

  // Open the Connection
  conn.Open();

  // Start a Transaction and create a new Command
  tx = conn.BeginTransaction();
  using (SqlCommand cmd = new SqlCommand())
  {
    // Set the Connection to the command
    cmd.Connection = conn;

    // NOTE: I do not explicitly set the TX to the Command
    //cmd.Transaction = tx;

    // Insert new values and execute it 
    // (within the transaction)
    cmd.CommandText = @"INSERT INTO Customers (CustomerID, CompanyName) 
                        VALUES ('ZZZZY', 'My New Company');";
    cmd.ExecuteNonQuery();

    // Insert new values, but the table name is wrong
    cmd.CommandText = @"INSERT INTO Companies (CompanyID) VALUES('ANother Company')";
    cmd.ExecuteNonQuery();

    // We should never get here since the query is wrong
    tx.Commit();
  }
}
catch (Exception ex)
{
  // Rollback the tx if error'd
  if (tx != null) tx.Rollback();
}
finally
{
  tx.Dispose();
  // Close the connection just in case
  if (conn != null)
  {
    conn.Close();
    conn.Dispose();
  }
}

This code fails because I do not explicitly set the transaction to the command.  Unfortuately, you must set the connection and the transaction.  This seems like a bug because you cannot execute a command on the connection (that has an pending transaction) without throwing an error. 

Data Part 1: Business Objects, Messages and DataSets...

I've had time lately to think about the nature of data in development lately.  I've been talking with Rocky Lhotka and Michael Earls about it (as well as number of others) about the issues with dealing with data in applications. 

The first camp is all about writing Business Objects.  In this camp, you write classes that encapsulate the data, the data access and business rules about that data.  This camp was the way to do it for years now.  It proliferated in the Client-Server and n-Tier architecture camps. 

Data Part 2: n-Tier...Gone Tomorrow

Recently I was talking with Rocky Lhotka and he said something interesting:

Just when we got good at Client-Server, they switched things and had us doing n-Tier applications.  Just when we got good at n-Tier development, internet applications took off.

Is Data Access Really This Hard?

I've been spending some time lately reviewing how companies are doing data access in .NET.  When I look at how most of them have crufted up solutions, I am amazed.  The model that Microsoft supports seems so obvious to me, but I am neck deep in it.  I'd like to hear from my readers their specific experience with creating data access in .NET; with an eye to why or why not use COM+ for transactions; Typed DataSet or DataReaders; Business Objects or Messages.  I am trying to understand where the community is.

Thanks in advance...

Yukon and User Defined Types (UDTs)

Url: http://codebetter.com/blogs/sahil.malik/archive...

In response to Sahil Malik's recent post on CLR Types as UDT's in Yukon, I have to say I prefer the Typed Xml in Yukon to CLR Types. 


Application Name WilderBlog Environment Name Production
Application Ver 1.1.0.0 Runtime Framework .NETCoreApp,Version=v1.1
App Path D:\home\site\wwwroot Runtime Version .NET Core 4.6.24628.01
Operating System Microsoft Windows 6.2.9200 Runtime Arch X86