Category Archives: SQL Server

Related to SQL Server

SQL CASE Statement

Maybe knew of it, but hadn’t had reason until now to use a CASE statement in SQL:

UPDATE Devices

SET InUse = (CASE UserId WHEN 3

THEN 1

ELSE 0 END)

WHERE DevGuid = '94F74150-26B0-4396-982C-675A14A66FAD';

In this situation, there are multiple rows with the same device GUID because more than one user may use the same app on the device (but not simultaneously…unless they’re extraordinarily close). What this query does is get all the rows that share the same device GUID, then sets the InUse bit to true (1) if the UserId matches (3). Any row within that set with a different user ID gets its InUse bit set to false (0). This avoids having to make more than one call to the server.

The “Evil” GEOMETRYCOLLECTION

Recently we encountered a bug in SQL Server’s spatial aggregation algorithms. Sets of polygons that should have been combined strictly into WKT (well-known-text) POLYGON or MULTIPOLYGONs were being stored as GEOMETRYCOLLECTIONs instead. Turns out the algorithm was inserting one or two LINESTRINGs (of two points each) into the collection, forcing it to be stored this way. The bug was supposedly fixed a couple years ago, but I’m going to submit a new bug and sample dataset to Microsoft. Because I’ve double-checked we’re using the latest version of the DLL, etc.

So why do I think the GEOMETRYCOLLECTION is “evil” (or at least don’t think highly of it)? Primarily because it caused our customer to question both the integrity of our software and the use of SQL Server to store AND manipulate spatial data instead of relying on purely an ESRI ArcGIS solution. We were unable to open the SQL Server datasets via ArcCatalog or ArcMap (using the Database Connection in 10.1). On a broader note, I tend to side with ESRI in not supporting more than one data type in a layer, so I’m perplexed that the creators of the WKT standard even thought up such a datatype. I can see where it might make sense to group related geometric objects but believe there are better ways to do that.

I’m tempted (hey, in a post where the word evil is mentioned it seems appropriate word use) to say that this never would have happened in the first place if a GEOMETRYCOLLECTION wasn’t a possibility. But I know that even if the bug gets fixed, Microsoft and others will still adhere to the WKT specification for storing data and ESRI will continue to invalidate coverages that contain multiple datatypes. So we will improve on the hack below to ensure that what gets output in the end is valid by our customer’s definition.


// Well, semi-verified anyway. At least it won't contain any LINESTRINGs.
public SqlGeometry VerifiedPolygonGeometry(SqlGeometry inputGeometry)
{
 SqlGeometry outputGeometry = null;

try
 {
 var goodReservoirGeometriesList = new List<SqlGeometry>();

int numGeometries = (int)inputGeometry.STNumGeometries();

for (int i = 1; i <= numGeometries; i++)
 {

var geometry = inputGeometry.STGeometryN(i);

if (geometry.STGeometryType() != "LineString")
 {
 goodReservoirGeometriesList.Add(inputGeometry.STGeometryN(i));
 }
 }

var agg = new GeometryUnionAggregate();

agg.Init();

foreach (SqlGeometry geometry in goodReservoirGeometriesList)
 {
 agg.Accumulate(geometry);
 }

outputGeometry = agg.Terminate();
 }
 catch (Exception exception)
 {
 Debug.Write(exception.Message);
 }

return outputGeometry;
}

Using SMO to Create a Spatial Index

With the advent of ArcGIS 10.1, you can now direct-connect in a read-only fashion to spatial data on SQL Server. That has worked well and while the read-only aspect means no editing, it also means no locks on the data, which is a problem with File Geodatabases. I’ve found that I can programmatically refresh or rebuild a table in SQL Server and just by panning or zooming have ArcMap redraw it. I’d never be able to leave an FGDB open in ArcMap while trying to do the same.

However, I ran into one problem with really large datasets brought into ArcMap. They couldn’t be exported to FGDB or Shape format without a spatial index on the table in SQL Server. Understandable and really it’s better for performance if a spatial index exists. Since it’s an application that creates the tables and populates them, it fell to the application to also create a spatial index.

I could, of course, have used a parameterized SQL command to do this, but since I’m already using SMO to create a tabular index thought I’d try it for a spatial geometry column. Of course, as I soon realized, there’s a catch…one that almost makes it just as well to use parameterized SQL. But if you like being able to specify things like:

 SpatialIndexType = SpatialIndexType.GeometryAutoGrid 

as needed, then there is some case to be made for mixing SMO with SQL…and mix you have to. Because here’s the catch, you can’t create a spatial index if you don’t already know the bounding box (or envelope) coordinates. The only way I know to do this is via a SQL query to the server first. So here’s a code sample:


var serverConn = new ServerConnection("servername");

serverConn.ConnectTimeout = 180;

// provide appropriate login credentials here

var srv = new Server(serverConn);

Database db = srv.Databases["tablename"];
var tb = db.Tables["tableName"];
try
{
 if (db != null)
 {
 // Perform spatial query to get the bounding box
 var sql = String.Format(@"SELECT
 geometry::EnvelopeAggregate(GEOM).STPointN(1).STX AS MinX,
 geometry::EnvelopeAggregate(GEOM).STPointN(1).STY AS MinY,
 geometry::EnvelopeAggregate(GEOM).STPointN(3).STX AS MaxX,
 geometry::EnvelopeAggregate(GEOM).STPointN(3).STY AS MaxY
FROM {0}", "tableName");

var dataSet = db.ExecuteWithResults(sql);

if ((dataSet != null) && (dataSet.Tables.Count > 0) && (dataSet.Tables[0].Rows.Count > 0))
 {
 var boundingBoxXMin = (Double)dataSet.Tables[0].Rows[0]["MinX"];
 var boundingBoxYMin = (Double)dataSet.Tables[0].Rows[0]["MinY"];
 var boundingBoxXMax = (Double)dataSet.Tables[0].Rows[0]["MaxX"];
 var boundingBoxYMax = (Double)dataSet.Tables[0].Rows[0]["MaxY"];
 //spatial index
 var spatialIndex = new Index(tb, "Spatial" + tableName)
 {
 SpatialIndexType = SpatialIndexType.GeometryAutoGrid,
 BoundingBoxXMax = boundingBoxXMax,
 BoundingBoxXMin = boundingBoxXMin,
 BoundingBoxYMax = boundingBoxYMax,
 BoundingBoxYMin = boundingBoxYMin,
 CellsPerObject = 16,
 PadIndex = false,
 CompactLargeObjects = false,
 DisallowPageLocks = false,
 SortInTempdb = false,
 OnlineIndexOperation = false,
 DisallowRowLocks = false
 };

spatialIndex.IndexedColumns.Add(new IndexedColumn(spatialIndex, "GEOM"));

spatialIndex.Create();

return true;
 }
 }
}
catch (Exception err)
{

Debug.WriteLine(err.message)
}

Using .NET 4.5 Task Async and Await with WPF and SQL Server

Most of the examples I’ve seen of  using the new async, await and Task methods in .NET 4.5 have been aimed at Windows 8 (Metro or “UI Style”) apps. This is understandable, given Microsoft’s push towards both a new OS and to populate the Windows 8 Store. But for some (many?) developers there is also a need to support both Windows 7 and Windows 8 Desktop. Notably missing from the WinRT scenario is the ability to directly connect to SQL Server. I (mostly) get the reasons for that, although it seems against Microsoft’s interests to cede that space to SQLite instead of a sandboxed, light version of SQL Server.

Anyway, I put together a sample WPF application (download below) that supports asynchronous connections to SQL Server. You can cancel or restart the demo query, which populates a ListView with data from the AdventureWorks DB Production.WorkOrder table, which had a decent number of records for testing. I really wanted to simulate some real world conditions, such as server-side delays, so that’s included too. One of my beefs with many of the samples I come across is that they never include decent error handling, especially to accomodate network latency or timeouts. This sample also includes the Microsoft.Data.ConnectionUI SQL Server connection dialog box.

One advantage of making a WPF app asynchronous is it no longer locks up while running a process. Try dragging the window around while the ListView is being populated with data. Yay, no UI-thread blocking!

Clicking the Start button triggers the StartTest method. Notice that in the query, I’ve embedded an artificial server-side delay of 3 seconds – WAITFOR DELAY ’00:00:03′; – which will simulate a slow server return. Also, a queryTimeout has been set to 120 seconds. If you set the WAITFOR delay longer than that, you will see an error occurs and is handled. I did my best to include specific SQL Server error handling so we get actual error details, rather than just “some crap happened and that’s all we know.”


private async void StartTest(object sender, RoutedEventArgs e)
{
try
{
 if (_connectionString == String.Empty)
 {
 var scsb = new SqlConnectionStringBuilder(Settings.Default.DBConnString);

_connectionString = scsb.ConnectionString;

if (_connectionString == String.Empty)
 {
 throw new Exception("Please use Change DB to first select a server and database.");
 }
 }

if (DbInfo.DbName == String.Empty)
 {
 throw new Exception("Please use Change DB to first select a database from the server.");
 }

// Note: If the start test button was disabled, this would be unnecessary
 _cts.Cancel();

// Re-initialize this. Otherwise, StartTest won't work.
 _cts = new CancellationTokenSource();

DataItemsList.Clear();

// Note the simulated delay, which will happen on the DB server. You can vary this to see its effect.
 // Try dragging the window around after hitting the start test button.
 const string query =
 @"WAITFOR DELAY '00:00:03'; SELECT * FROM [Production].[WorkOrder];";

// Note that setting the WAITFOR delay to exceed this, eg. 00:02:01, will trigger an exception
 int queryTimeout = 120;

RequestStatus = "started";

await ProcessQueryCancelleable(_connectionString, query, queryTimeout, _cts.Token);
}
catch (Exception ex)
{
 MessageBox.Show(ex.Message + " (from StartTest)");

Debug.WriteLine(ex.Message);
}
}

The ProcessQueryCancelleable method. Note the use of the CancellationToken…’cause users might actually want to cancel a long-running query. Inside this method is another simulated delay (await Task.Delay(TimeSpan.FromMilliseconds(1));):

/// <summary>
/// Runs a cancelleable query
/// </summary>
/// <param name="connectionString">SQL Server connection string</param>
/// <param name="query">SQL query</param>
/// <param name="timeout">SqlDataReader timeout (maximum time in seconds allowed to run query). Note: Try varying this in conjunction
/// with the WAITFOR DELAY in the SQL query, e.g. make it shorter than the WAITFOR or maybe a second longer</param>
/// <param name="cancellationToken">Allows cancellation of this operation</param>
/// <returns></returns>
private async Task ProcessQueryCancelleable(string connectionString, string query, int timeout,
 CancellationToken cancellationToken)
{
 // await Task.Delay(TimeSpan.FromSeconds(5));

try
 {
 // Keep sqlConnection wrapped in using statement so disposal is handled automatically
 using (var sqlConnection = new SqlConnection(connectionString))
 {
 await sqlConnection.OpenAsync(cancellationToken);

using (SqlCommand cmd = sqlConnection.CreateCommand())
 {
 cmd.CommandTimeout = timeout;

cmd.CommandText = query;

using (SqlDataReader dataReader = await cmd.ExecuteReaderAsync(cancellationToken))
 {
 while (!dataReader.IsClosed)
 {
 // While the dataReader has not reached its end keep adding rows to the DataItemsList
 while (await dataReader.ReadAsync(cancellationToken))
 {
 // Process dataReader row
 WorkOrder workOrder = GetWorkOrderFromReader(dataReader);

DataItemsList.Add(workOrder);

// Another simulated delay...this one internal
 await Task.Delay(TimeSpan.FromMilliseconds(1));
 }

if (!dataReader.NextResult())
 {
 dataReader.Close();
 }
 }

Debug.WriteLine("done with reader");

RequestStatus = "finished";
 }
 }
 }
 }
 catch (SqlException sqlEx)
 {
 Debug.WriteLine(sqlEx.Message);
 }
}

Download C# Project (an MDF with just the WorkOrders table is included)

Computed, int, not null…what does that mean?

I didn’t even know this was possible, but noticed a column in Microsoft’s AdventureWorks sample DB Workorders table with the attributes of “Computed, int, not null” while other similar columns just had “int, not null”. A peek at the CREATE TABLE code:


CREATE TABLE [Production].[WorkOrder](

[WorkOrderID] [int] IDENTITY(1,1) NOT NULL,

[ProductID] [int] NOT NULL,

[OrderQty] [int] NOT NULL,

[StockedQty]  AS (isnull([OrderQty]-[ScrappedQty],(0))),

[ScrappedQty] [smallint] NOT NULL,

[StartDate] [datetime] NOT NULL,

[EndDate] [datetime] NULL,

[DueDate] [datetime] NOT NULL,

[ScrapReasonID] [smallint] NULL,

[ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED

(

[WorkOrderID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO