For those of you who are lucky enough to work with DB2 and .NET, you're probably already aware that you can run into some issues if you're  not using the correct DB2 Driver.

I've been writing some SSIS packages that move some data from a DB2 environment into a SQL Server 2005  Database. I also had to write a few .NET applications that spoke to the DB2 database. Here's a couple drivers that I used and some of the notes regarding them. I've also included a few links that help in regards to development.

Drivers

• Microsoft OLE DB2: DB2OLEDB Driver 
  • This driver is part of the Feature Pack for SQL Server 2005.
  • This Driver worked for most of the time until I needed to work with Large Objects (BLOB/CLOB). Unfortunately I found this after I did a bunch of work -
    • "The OLE DB Provider for DB2 does not currently support the DB2 Large Object (LOB) types." - Distributed Query Support Using the OLE DB Provider for DB2

• IBM DB2 Driver for ODBC and CLI
  • Please note, I COULD NOT get the IBM web site to work with IE7. I had to use Firefox to download the file.  
    • Link Name: IBM DB2 Driver for ODBC and CLI
    • File Name: v9fp2_win_db2driver_for_odbc_cli.zip
  • Download the file, install and you'll have the drivers on your machine.
  • The thing is, this worked great for the BLOB/CLOB fields, but when the SSIS Package went to transform the DateTime fields it complained about data loss because DB2 has more DateTime precision than SQL Server 2005.
    • This was solved by using the Microsoft OLE DB2 Provider as it did not complain about the DateTime. The team I worked with knew that we'd lose some millionths of a second, but that was acceptable for the project. Basically the Microsoft Provider didn't complain about it like the DB2 provider did. The data came through the conversion correctly, so we were fine with it.

Other Useful Information/Tools

• SQLState Error Message Reference
  • Sometimes SSIS can throw some nasty HRESULT errors your way and you're left scratching your head trying to figure out what went wrong. If the message includes a SQLState, look in this reference, it will probably be in here, or will point you in the right direction.

• WinSQL Lite
  • I like to say its like the SQL Management Studio for DB2. A very nice GUI that allows you to interact with the DB2 system. A lot of "old schoolies" (as I call them) like to use the command line to hammer away at DB2. Me? I like to look at the GUI when I'm looking at tabular data. This tool will make your life 10 times easier when looking for data anomalies.

The Problem

I needed to write something like this ...

SELECT * FROM CustomerTest WHERE Customer_Id = 1;

The data returned needed to return the row as well as the data inside of the XML Column in that row, in the same set. (The data in the XML column needed to be returned as sets of records as well.) The XML Contained was simple XML that looked like this:

(click for larger image)

The XML Column was called "Extra_Info". This is where the above XML was stored.

Ok, lets get to the point... here's what its supposed to look like when the query is run:

(click for larger image)

A little more about the problem domain..

Awhile back I had a client who wanted to store data in an XML Field, but the XML for each row could be extremely different in each row. One row might have a XML Document that has 1 set of data, while the next might have 10. These XML documents that were being stored did not share a common schema -- not at all. But we needed to return the data in an SQL Query or SPROC that would allow the user to view the data as if it were tables in the database.

Solution

So how do we do that with one Query?

Easy - SQL CLR Integration.

We can create a CLR Stored Procedure to do this for us.
Here are the steps the CLR SPROC will go through to return the data.

1. Query the DB for the record.
2. Get the XML from the column.
3. Load the XML into a DataSet object.
4. Send the original record results of the row to the client.
5. Create record sets off of the DataSet using SqlDataRecord object.
6. Send each record back to the client.

Code time baby...

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections.Generic;
using System.Data.Common;
using System.Xml;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void TestProcedure(SqlInt32 recordId)
    {
        DataSet dataset = null; 

        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            string xml = string.Empty;
            using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.CustomerTest WHERE Customer_Id = @recordId"))
            {
                command.Parameters.Add(new SqlParameter("@recordId", recordId));
                command.Connection = connection;
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        xml = reader["Extra_Info"].ToString();
                        
                        // Load in the XML. 
                        XmlReader xmlReader = new XmlTextReader(xml, XmlNodeType.Document, null);
                        dataset = new DataSet();
                        dataset.ReadXml(xmlReader);
                    }

                }
                SqlContext.Pipe.ExecuteAndSend(command);

            }

            
        }

        if (dataset != null)
        {
            foreach (DataTable table in dataset.Tables)
            {
                // Set up the record 
                List<SqlMetaData> metaData = new List<SqlMetaData>();
                foreach (DataColumn column in table.Columns)
                {
                    metaData.Add(new SqlMetaData(column.ColumnName, SqlDbType.Variant));
                }
                SqlDataRecord record = new SqlDataRecord(metaData.ToArray());

                // Mark the begining of the result-set.
                SqlContext.Pipe.SendResultsStart(record);

                foreach (DataRow row in table.Rows)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        record.SetValue(table.Columns.IndexOf(column), row[table.Columns.IndexOf(column)]);
                    }

                    // Send the row back 
                    SqlContext.Pipe.SendResultsRow(record);
                }
                // Mark the end of the result-set.
                SqlContext.Pipe.SendResultsEnd();
            }
        }

    }

}

Code Review

The sproc is created with the code above.

If we find a record we load the xml into a variable and then load the DataSet with the XML using the ReadXml Method of the DataSet.

if (reader.Read())
{
  xml = reader["Extra_Info"].ToString();
                      
  // Load in the XML. 
  XmlReader xmlReader = new XmlTextReader(xml, XmlNodeType.Document, null);
  dataset = new DataSet();
  dataset.ReadXml(xmlReader);
}

Next, we loop through each data table and each record in the data table to create a SqlDataRecord. To build an SqlDataRecord we need a Record definition.These record definitions are defined as SqlMetaData objects. Therefore we need populate an array of SqlMetaData objects by looping through the columns to get the column information. At this point we're building the columns in which the data will exist in.

// Set up the record 
List<SqlMetaData> metaData = new List<SqlMetaData>();
foreach (DataColumn column in table.Columns)
{
  metaData.Add(new SqlMetaData(column.ColumnName, SqlDbType.Variant));
}
SqlDataRecord record = new SqlDataRecord(metaData.ToArray());

From here, we will build the actual records. This is done by the following method:

• Inform the context that we're going to begin sending a record by using the SendResultsStart method of the Pipe class.
• When a record is built, we want to send it to the client by calling the SendResultsRow method. This sends the row to the client. We will do this for each record built.
• After the records have been sent we want to tell the client we're done sending records for this set. We do this by calling the SendResultsEnd method.

This happens for each DataTable in the DataSet. And then finally the results are sent back to the client.

Resolution

Once deployed, the sproc (which I've named "TestProcedure") will show up under the StoredProcedures area of SQL Server, as shown here:

And now you have a stored procedure that will return results of the row as well as the data that is in the XML column, all in the same result set.

Uses: You could use this as an extensibility point in your application  by allowing users to add custom fields to the application at run time and then storing the application fields and field data in an XML file which is stored in an SQL Server column. Think of it as a database inside of a database. You could then use it to query the row/column to get the results into a readable form for Crystal or Reporting Services.

UPDATE: I added the publications I also read at the bottom. :)

I've been asked what blogs I read, podcasts I listen to and screencasts I watch. So here's a comprehensive list for ya'll. :)

Blogs (in no particular order)

• Scott Hanselman
• Jeff Atwood
• Martin Fowler
• Jeremy D. Miller
• K. Scott Allen
• Mads Kristensen
• Sahil Malik
• Roy Osherove
• Jean-Paul S. Boodhoo
• Bil Simser
• Dino Esposito

Then at times I'll browse the MSDN Blogs. Also note, these are what I call my "essential" blogs. I read these daily. I have about 30 other blogs that I read if I have time.

Podcasts (in no particular Order)

• .NET Rocks
• Hanselminutes
• ARCast with Ron Jacobs
• Polymorphic Podcast
• this Week in Tech
• Agile Talk
• Agile Toolkit Podcast
• Confessions of an Agile Activist
• Lean Agile Straight Talk
• Microsoft Technet Podcasts
• Nitobis Enterprise AJAX Podcast

Other Podcasts I listen to when I can

• Ruby on Rails Podcast
• Rubyology
• Railscast

Screencasts/Virtual Labs (in no particular order)

• DNR TV
• Microsoft Office 2007 Screencasts - How Do I? Screencasts
• Best Tech Videos (Microsoft)
• Microsoft Virtual Labs

Publications

• MSDN Magazine
• ASP.NET Pro
• Visual Studio Magazine
• CoDe Magazine
• Dr. Dobb's Journal

During the development of any new greenfield project you're going to probably need to interact with a datastore.
From my experience, 8 out of 10 times this is going to be a database acting as the datastore, and in my case, it's normally SQL Server.

Database Modeling

When in the design phase we're modeling. Creating a representation of what we're going to build. In the design of a database I use Visio to do this by building an ERD Diagram.

If you're like me you're still probably using Visio 2002/2003. If you're also like me, you don't have the Enterprise Architect version of Visio that allows you to forward engineer the database from the ERD Diagram that you've created.

So, therein lies a problem. I have to design my database in Visio, then manually create the SQL and run it in query analyzer to get my database. Seem's like double the work to me.

1. Create the diagram and all the types and relationshiops
2. Copy it by hand into SQL Server Management Studio or the Query Window

I've always felt that Microsoft should have included forward engineering for Visio, regardless of the version. Unfortunately they didnt feel the same way.

Model to SQL

Fortunately there is a solution. A company called Orthogonal Software has a Visio plug-in called Orthogonal Toolbox that will allow you to export the data from the Visio document to an XML File.

Orthogonal has provided a couple XSLT's that you can apply (look for XSLT on that page). One of which has been altered to export SQL.

The XSLT file that originally was found with the product exported the SQL into one long string. No line breaks, nothing. Just a long string. After some futz'n around with the XSLT I was able to format the SQL that was transformed into readable SQL.

For example, a XSLT generated SQL Code that looks like this:

CREATE TABLE dbo.Employee( 
   [Employee_Id] bigint NOT NULL,
   [Name] nvarchar(100) NOT NULL
);
GO

How to implement

1. Download Orthogonal Toolbox and install it.
2. Create your Visio ERD Diagram.
3. Click the Orthogonal Toolbox Button in the toolbar.
4. Export to XML.
5. Put the XSLT files (download at the bottom of this post) into the same file as the XML File.
6. Open the newly created XML File and add this line after the XML Definition.
    <?xml-stylesheet type="text/xsl" href="ERD-SQL2005.xslt"?>
6. Open the XML file in Internet Explorer
7. Copy/Paste what you see into Query Analyzer and run.

Thats it. You've now gone from Database ERD Model to SQL Code in a few quick steps. TONS of time saved.

Update for SQL Server 2005

The original XSLT was for use with SQL Server 2000. Schemas were not supported in 2000, therefore everything started with "dbo". To get around this, I reworked the XSLT and changed it to use the table name.

When I'm designing for a SQL Server 2005 database I'll give my tables names such as [My_Schema].[Table_Name]. Therefore when I export and apply my XSLT, the new exported SQL will look like this:

CREATE TABLE [HumanResources].[Employee]( 
   [Employee_Id] bigint NOT NULL,
   [Name] nvarchar(100) NOT NULL   
);
GO

Where "HumanResources" is the schema and "Employee" is the table name.

Downloads

2000and2005XSLTs.zip (2.96 KB)

During their time in the game, almost all developers are going to have to manipulate a database. This can be a good thing, or an absolute disaster. Unfortunately it seems to be the latter in most cases. This is not due to a developer not caring, but sometimes some developers are not formally trained in how to design a database. For example, just look at most programming books, when they get to the data access section they barely cover the database design. I've read many programming books on VB, Classic ASP, PHP, Perl, .NET, etc, and I've seen very few examples where the author actually makes an attempt to advise the developer to ensure that the database is properly normalized during their design of the database.
I understand that not all developers are not going to work with databases directly. Some companies have dedicated DBA's  that handle all communication with the database and they don't let the developers get involved with the database. Even though this may be the case, it's still good to know the basics so you can be aware of what's going on in the background. That's why we're all developers right? We all wanted to know how it worked, so we figured it out, right? Well, maybe not, but you get the point...

So, why is lacking Database design knowledge a bad thing? 
Its simple ... if you're using a database as your datastore, the database is the foundation of your application. Regardless of how cool your application is, regardless of how many design patterns you use, regardless if you are the worlds best coder in the last 10 years, it comes down to this...

A great application can not cover up a poor data foundation.

Think of it like this: (I'm no construction contractor so don't flame me here) When you build a house, you need a solid foundation. You need solid ground, a good cement base. You need the plumbing and electricity to be routed in from the street. Once this good foundation is built, a good contractor can build a very sturdy house on top of the foundation, hell, they could build a mansion if they wanted to. All of this takes planning and foresight. Where does the plumbing and electricty come from? Is the ground solid enough? Or are we on a swamp? etc, etc.

Now what would happen if we did not know what we were doing, if we did not have the foresight into the plan? We might end up building a foundation on top of a swamp, therefore the house sinks (the application breaks down and gets abandoned by the users). We might forget to pull the plumbing up before we pour cement (A new feature has to be installed all the way through the system, a major cost for the client, and a huge pain in the butt). We might forget about the electricity until we need it (Same thing as the plumbing problem!). More problems = more money that your client has to spend. This client might be your boss, or if you consult it could be the guy signing your checks. Either way, your performance is less than stellar in this situation (if you built the foundation of the system)and you're not looking like the golden child they once thought you were.

Other issues...
Most of all developers are under a time crunch, and in time crunches we take shortcuts. We don't do things correctly like we normally would. If these projects involve changing a database and the developer is not exactly sure how to do correctly they will do it the easiest way they can, which, in my experience is the wrong way.

This stems from these lines we've all heard:
"Well, we needed it yesterday and its the highest of priority, we need it ASAP!"
Or...
"When you're done with that project, we have another than needs to be done ASAP. Please make it snappy. Oh yeah, we need those TPS reports in an hour."

That's normally the case in a lot of development projects. The project target date is well beyond what the raealistic development time actually is. When this happens developer tend to take shortcuts. But when shortcuts need to be taken, it should not be done at the database level (it should be avoided at all costs, but we know thats not possible).

Examples of problematic changes

Example 1 -
Client: I need you add another contact record to the "ACME Corporation".
You then look at the database, and notice that the Organization table schema looks like this:

Organization
--------------------------------
Organization_Id  int
Organization_Name varchar(100)
Org_Contact_Name nvarvhar(100)
Org_Contact_Phone nvarchar(50)
Org_Contact_Email nvarchar(100)
... (other columns ommitted for example use)

What this tells us is that each Organizaiton only has ONE and only ONE Contact record that can be associated with it. You can solve this a couple different ways. I'll show you a wrong way and then a correct way.

The Wrong Way (You'd be surprised how many times I've seen this. Which is the reason for this post.)

Organization
--------------------------------
Organization_Id  int
Organization_Name varchar(100)
Org_Contact_Name nvarvhar(100)
Org_Contact_Phone nvarchar(50)
Org_Contact_Email nvarchar(100)
Org_Contact_Name2 nvarvhar(100)
Org_Contact_Phone2 nvarchar(50)
Org_Contact_Email2 nvarchar(100)
... (other columns ommitted for example use)

Well, we did what we wanted right? We added another contact to the organization table. Enabling "ACME Corporation" to have another contact. Cool. I'm done. Unfortunately this also lends itself to a lot of null fields floating around in your database (when a company doesnt have two contacts, the *2 fields will be null). Plus, next time the same developer needs to add another contact they're probably just going to add three more columns to hold the data, aka: Contact_Name3, etc. This is NOT what you want to do, because this design would be breaking a lot of basic fundamental database design guidelines (more on this below).

The Right Way

What the developer wants to do is to separate the Contacts into a different table and reference the Organization in the contact table. This says "This contact is part of xxxx organization". This is done through foreign keys. Again, this is a very basic concept, but a lot of developers do not know this (which is unfortunate).

Organization
--------------------------------
Organization_Id  int
Organization_Name varchar(100)
... (other columns ommitted for example use)

Contact
---------------------------------
Org_Contact_Id  int
Org_Contact_Name nvarvhar(100)
Org_Contact_Phone nvarchar(50)
Org_Contact_Email nvarchar(100)
Organization_Id  int  (a foriegn key, that points to the organization database).

In this very basic example we have removed a few columns from the Organization table and put them into the Contact table. Then in the contact table we've referenced the Organization that the contact is part of. This allows the organization to have ZERO to n amount of contacts a zero to many relationship. Now, we have no null data lying around in the organization. Plus, if we want to add 10 contacts to one organization and 2 to another, we can do so, VERY easily.

The Overall Solution

Basic Database Design - Normalization

What is normalization? Its a design technique that helps eliminate inconsistencies and anomalies inside of databases. When designing/changing a database the developer needs to try to keep things in a proper normal form. I strive for a minimum of Third Normal Form or further (4th or 5th).

There are a ton of sites that cover database design so I'm not going to attempt cover it in depth, just Google the topic. I just wanted to say how important it is for a developer to know the basics. Normalization IS something each developer needs to know in order to develop systems that are scalable and extensible. Shorcuts as noted in the example above are done on a daily basis by developers around the world, and why?  Most likely because its the quick and easy way to get the project done. Even though refactoring the data into a new table might take longer (initially) the return on that investment is tenfold, at minimum. Hopefully more people start to take pride in the code that they write and databases they design/alter.

Think of it like this, would you want to be the guy who has to come in and fix an application because its slow and hard to extend, due to the database not even being in first normal form, and the database has 1 million+ rows? I've been in that situation before and I can tell you, its a TON of work to get things fixed. It could have been avoided very easily with a small amount of planning and foresight, and it could have saved my client A TON of money. So, before you alter that table, think... "is this really the 'right way' to do this?" You may be saving hundreds of thousands of dollars, with that one decision.

:)

I had been wondering if this was possible as of lately. After not seeing any examples on the web I decided to tackle the task myself.

I wanted to place some files (doc, xls, ppt, htm, txt) into SQL Servers BLOB Field and then expose them to SharePoint 2007 (MOSS). This would allow me to search the BLOB data through the SharePoint seach functionality. Pretty much, I wanted expose a document stored in SQL Server to enable it for searching on MOSS.

A lot of the time applications will store the file path to the file and then store the actual file on a file server. This isnt something I wanted to do, mainly because I was only going to be storing, maybe, 30-50 documents a month. Each of these documents were maybe 1 to 2 pages long, nothing huge. Therefore I decided to keep it with my SQL data, in the datbaase as a BLOB field. This has many advantages, you can keep certain documents with your custom systems. Therefore, if you decide to move the SQL Database later on down the road, you dont have to copy the file share to another server and possibly update the database records with their new location.

So here's what I needed to do:
1. Create a BLOB field in a database table that could be full text searched.
2. Create the Full-Text Index
3. Insert some test documents
4. Create the query to search within the blob documents. (still in sql at this point).
5. Expose the SQL Table information to MOSS through the Business Data Catalog.
6. Allow the content to be searched from within the MOSS site.

After that, WE'RE DONE!

HOW TO DO THIS

Blob Table

I created the SQL Table with the following SQL Code:

CREATE TABLE [dbo].[BinaryTable](
   [Row_Id] [int] IDENTITY(1,1) NOT NULL,
   [Blob_Type] [char](3) NOT NULL,
   [Attachment] [varbinary](max) NOT NULL,
   [File_Name] [varchar](100) NULL,
CONSTRAINT [PK_BinaryTable] PRIMARY KEY CLUSTERED 
   (
      [Row_Id] ASC
   )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Please note that the Blob_Type is actually the file extension of the document.  SQL Server only supports searching the following BLOBS:
- .doc
- .txt
- .xls
- .ppt
- .htm

From BLOBS in Special Operations in MSDN:

Full-text index and search operations

You can index and search certain types of data stored in BLOB columns. When a database designer decides that a table will contain a BLOB column and the column will participate in a full-text index, the designer must create, in the same table, a separate character-based data column that will hold the file extension of the file in the corresponding BLOB field. During the full-text indexing operation, the full-text service looks at the extensions listed in the character-based column, applies the corresponding filter to interpret the binary data, and extracts the textual information needed for indexing and querying.

When a field in a BLOB column contains documents with one of the following file extensions, the full-text search service uses a filter to interpret the binary data and extract the textual information.
• .doc
• .txt
• .xls
• .ppt
• .htm

The extracted text is indexed and becomes available for querying.

Create the Full Text Index

I created a full text catalog.

Then I defined the full text index.

The table was now ready for data.

Inserting Blob Data.

I followed this example to insert a few documents into the system (just alter the connectionstring and column names. I manually altered it by hand an inserted some documents within a few moments. This was for testing only, in a real world app you'd create a robust method for inserting documents into the system.

The documents I inserted all had greeked text for testing purposes.

Querying the Blob.

Once the blob data was inserted, I was then able to query it with this command.

SELECT Row_Id, File_Name FROM BinaryTable WHERE (CONTAINS (Attachment, 'Lorem'))

This searched all the BLOBS for 'Lorem'.

After I got the results I was looking for it was then time to expose it to MOSS.

Exposing the BLOB data to MOSS/SharePoint 2007

In order to expose the data, I had to expose certain metadata entities in the database and certain methods (queries) that allowed SharePoint to understand the search request. This was done through the Business Data Catalog.

This metadata definition had to be built by hand to do enable this functionality. I'm not going to get into how and why things are where they are, all of that info can be found here (expect to spend a good day, or two, to wrap your head around this metadata model).

Here's the code:

BlobSearch.xml

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
- <LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd" Type="Database" Version="1.0.0.6" Name="BlobSearchSample" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
- <Properties>
<Property Name="WildcardCharacter" Type="System.String">%</Property>
</Properties>
- <LobSystemInstances>
- <LobSystemInstance Name="BlobSearchSampleInstance">
- <Properties>
<Property Name="AuthenticationMode" Type="System.String">WindowsCredentials</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">TESTSERVER</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">BlobTest</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
<Property Name="RdbConnection Pooling" Type="System.String">false</Property>
<Property Name="SsoApplicationId" Type="System.String">BlobTest</Property>
<Property Name="SsoProviderImplementation" Type="System.String">Microsoft.SharePoint.Portal.SingleSignon.SpsSsoProvider, Microsoft.SharePoint.Portal.SingleSignon, Version=12.0.0.0, Culture=neutral,PublicKeyToken=71e9bce111e9429c</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
- <Entities>
- <Entity EstimatedInstanceCount="10000" Name="File">
- <Properties>
<Property Name="Title" Type="System.String">File_Name</Property>
</Properties>
- <Identifiers>
<Identifier Name="Row_Id" TypeName="System.Int32" />
</Identifiers>
- <Methods>
- <Method Name="GetFiles">
- <Properties>
<Property Name="RdbCommandText" Type="System.String">SELECT Row_Id, File_Name FROM BinaryTable WHERE (CONTAINS (Attachment, @textToSearchFor))</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
</Properties>
- <FilterDescriptors>
- <!-- Define the filters supported by the back-end method (or sql query) here.
-->
- <FilterDescriptor Type="Wildcard" Name="TextToSearchFor">
- <Properties>
<Property Name="UsedForDisambiguation" Type="System.Boolean">true</Property>
</Properties>
</FilterDescriptor>
</FilterDescriptors>
- <Parameters>
- <Parameter Direction="In" Name="@textToSearchFor">
- <TypeDescriptor TypeName="System.String" AssociatedFilter="TextToSearchFor" Name="TextToSearchFor">
- <DefaultValues>
<DefaultValue MethodInstanceName="FileFinderInstance" Type="System.String">%</DefaultValue>
<DefaultValue MethodInstanceName="FileSpecificFinderInstance" Type="System.String">%</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
- <Parameter Direction="Return" Name="Files">
- <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="FileDataReader">
- <TypeDescriptors>
- <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="FileDataRecord">
- <TypeDescriptors>
<TypeDescriptor TypeName="System.Int32" IdentifierName="Row_Id" Name="Row_Id" />
<TypeDescriptor TypeName="System.String" Name="File_Name" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
- <MethodInstances>
<MethodInstance Name="FileFinderInstance" Type="Finder" ReturnParameterName="Files" />
<MethodInstance Name="FileSpecificFinderInstance" Type="SpecificFinder" ReturnParameterName="Files" />
</MethodInstances>
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>

Now that it is exposed I was able to import the Metadata into the BDC on SharePoint, also known as Adding an Application Definition to the Business Data Catalog (click link to learn how to import it into SharePoint)

Searching within SharePoint

Now that the data is defined through the metadata model in SharePoint I can actually search it. I tested this by adding a Business Data List to a page in SharePoint.
I was then able to search for "Lorem" and results came back.

I then searched for another key word "Test" that I put in a single document, and that worked as well. See the screen shots below.

Conclusion

You could expand this functionality by adding it to the Content Sources and Search Scopes in SharePoint to allow for enterprise search too. But I think I'll save that for another day. :) Or just watch the videos provided in the links.

And that folks, is how you expose BLOB data to SharePoint Search.

(Download Excel Spreadsheet example at the bottom.)

Sometimes when developing I need to work with data. Sometimes this data is from external sources such as other partners in the business. Unfortunately when I get this data does not follow a consistent format therefore I'm left with formatting it for entry into a data store, usually a SQL Server.

In my last post about how to create store locator, I had this same problem. I had to import a list of company addresses for that tutorial. The only place I knew where to get a good list of locations for a store locator would be off of Starbucks Store Locator site. Through the trusty copy/paste method, I was able to grab about 200 store results in a matter of minutes from the website.

Now back to the original problem, the data format. The data looked like this after is was copied...

1. 1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx NY, 10462
United States

I needed it to look like this (for ease of use, plus its easier to debug):

1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx
NY
10462
 
Which will finally transform into this:

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('1385 Metropolitan Ave (UCO)', '1385 Metropolitan Avenue', 'Bronx', 'NY', '10462')

Hmmm... problem. Now I could take my time, write an SQL insert script for each and every one of these, but that would take forever.

Instead we can use Excel to do this for us.

First off, all credit for this sweet little trick goes to Chris Lawson (a developer I worked with during my time at Todd McFarlane Producitons, SPAWN.COM). He's the one who showed it to me, I'm just showing it to you. :)

To get the data formatted into SQL insert scripts we need to use a few functions from Excel. We'll mainly use LEFT, MID, RIGHT, TRIM and SEARCH. Using those 4 methods you can pretty much get anything you want out of the string, and thats what I've done.

Note: It might be very helpful if you downloaded my sample at the bottom of this post and read this while looking at the sample, just so you can see how it was done.

Step 1: Separate the Data

We're going to want to take our data from this format:

1. 1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx NY, 10462

and organize it to:
 
1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx
NY
10462

Which will eventually get transformed into SQL, like this ...

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('92nd & 3rd', '1642 Third Avenue', 'New York', 'NY', '101283618')

a) Get the Title to look like: 1385 Metropolitan Ave (UCO)

Tasks:
 - Get rid of the "1. " 

This will return the text in A1 without "1. " in it.

This takes the contents in cell A1 (the text with the 1. 1385 Metro...) and gets rid of the 1. through some simple text manipulation. The SEARCH method takes a parameter to search for. Utilizing the single character wildcard "?" I'm able to find where the numbering 'ends' and then get the text after that utilizing the LEN and RIGHT functions.

I'm going to use the same method for each line...
 

Address

City

State

Zip

Step 2. - Creating the SQL

Now that we have all the fields separated, we can easily create a SQL Script off of this.
Simply concatinate them together.

(click for larger view)

Now that we have all of this done, we can copy, and paste the this code (or click and drag the bottom right corner) throughout the sheet.
This will create the fields and SQL for us.

Then, two lines down...

Which gives us...

Now, select the entire "C" COLUMN and copy it ...

Now move that to any column down the line from column "C", lets say, "E". Right click and click "Paste Special"

Then click "Values"

Step 3 - Extracting the SQL

Now all the actual text values will be placed into the columns. You can now select Column "E" and copy all of those values.

The reason we have to paste special is because otherwise we'd be copying the column caluculations, instead of a pretty SQL script, we'd get a row with no data, such as:

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('', '', '', '', '')

By pasting special, we're telling Excel we want the VALUES of the cells, not the actual calculations.

Now that you've "pasted as special" with values, you can now copy column "E" and then paste that into your SQL Management Studio and run. For simplicity sake, here are a few rows from the actual Excel file that is attached to this post.

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('1385 Metropolitan Ave (UCO)', '1385 Metropolitan Avenue', 'Bronx', 'NY', '10462')
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('Whitestone', '138-11 20th Avenue', 'Whitestone', 'NY', '113562451')
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('Target Flushing T-1150', '13505 20th Ave', 'Flushing', 'NY', '11356')
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('LGA - Delta Flight Ctr-Delta Term', 'La Guardia International Airport', 'Flushing', NY', '11371')
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('Flushing - Main Street', '41-02 Main Street', 'Flushing', 'NY', '11372')

Then execute your script and you're on your way.

Conclusion

When is it appropiate to use this method? IT DEPENDS. If you have 10 records, no. If you have 1000 records? Yes. Its all about productivity. Ask yourself, is it going to be faster for me to spend 5 minutes writing this in EXCEL or is it going to take me 5 minutes to import them manually? This is up to you to answer. Hopefully this helps you on your next project. :)

Download File

starbucks-locations-xls.zip (52.71 KB)

Full Visual Studio 2005 solution included at the bottom of this post.

Many organizations have multiple locations throughout the country. A lot of the time the organizations customers will want to know where the closet location of that store is. We’ve all become accustomed to going to a companies website and finding the store locator and locating the store that’s closest to us. This is a simple thing for a user to do. But building a store locator is far from simple and can become quite complex, real quick. I’ve put together a simple example that will enable you to add store locator functionality to your ASP.NET website in no time.

Let’s start by covering the basics….

Requirements for store locator functionality: 
   - Database to store your store locations
   - Each store location needs a Longitude / Latitude 
   - A method to retrieve Latitude / Longitude, also known as Geocoding
   - A Web Front end that allows users to search and view the results.

Screen Shots

Search Screen:

Results Screen (click for larger view):

The Database

The database is very simple. We have one table, two sprocs, and 3 functions. It is in the App_Data folder of this solution.

The Location table holds the store locations.

The GetNearbyLocations sproc gets the locations that are closest to the user through a mathematical calculation that is inside of the sproc.

The InsertLocation sproc inserts a new location into the Location table (we’ll get to that near the end).

The three functions: XAxis, YAxis, ZAxis are used in the distance calculation that is inside of the sproc.

Important Note: I’m not the genius that wrote this mathematical calculation. Therefore, I can’t take credit for it. The database table, functions and distance sproc are all modeled after this post on MSDN. I have altered quite a few things especially the GetNearbyLocations sproc where I actually calculate the Earths X,Y, and Z axis’ at runtime. 

The Geocoding – Getting the Latitude / Longitude

The latitude and longitude are needed to perform the distance calculation in the GetNearbyLocations stored procedure. The Latitude and Longitude of each store is stored in the Locations table (see the database image above) with the location record itself.

How to do we get the Latitude / Longitude?

That’s where the Google Maps API comes into place. Google Maps allows you to sign up for a free API key.

Important Note: If you are going to test this locally, you will need to get a API key that is associated with your local machine, e.g.: http://localhost:port/  where “port” is your port number, such as: localhost:4688, localhost:1637, etc).

Where put your Google Maps API Key
The key is located in two places (I know, its not best practice) but, it’s the only way I could get it to work without spending hours on figuring it out. It’s located in the web.Config and in the Default.aspx page.

Default.aspx page (click for larger view)

web.Config

In a previous post I had built a class that retrieved the latitude and longitude of an address. By using this class while a new location is being added, (AddNewStore.aspx), the Lat/Lon coordinates are then retrieved and saved.

protected void addNewStoreButton_Click(object sender, EventArgs e)
{
   string address = String.Format("{0}, {1}, {2} {3}", 
   addressTextBox.Text,
   cityTextBox.Text,
   stateTextBox.Text,
   zipTextBox.Text);
   Coordinate addressCoordinates = Geocode.GetCoordinates(address);
   ... // Other code ommitted
}

The Web Front End

The web front end is simple (errrrrrrr… kind of). The application requires 2 things:
1. An address
2. A distance to search within.

The user enters this information, it’s passed to the database, the calculation is performed and the records that match the distance compared to the address are returned. Simple, right?

Now its time for the “somewhat” confusing part: the task of integrating the Google map with clickable markers. Google doesn’t offer markers that are numbered (from what I could find). So I found some on the net and included them in this project. Which leads me to the next point…

A maximum of 100 results will be returned.

This is set within the stored procedure. The reason we only return 100 records is because we only have 100 icon images (1 through 100). Plus, placing more markers on the page would have a negative impact on the memory and processor on the user’s machine. Furthermore, who’s really going to look at 100 results? The user is probably only looking for the closest couple of stores. Not the top 100.

Google Marker Placement

In order to place markers on the screen we needed lat/lon points to pass to Google’s API. This poses a problem because our data is in the DB, but Google Maps API needs it on the client to process it. Therefore, while on the server, while we have the data in a dataset (below) ...

/// <summary>
/// Gets the store locations and the coordinate for the "from" area (the address the user entered).
/// </summary>
/// <param name="coordinate">A coordinate.</param>
/// <param name="data">A LocationsData dataset.</param>
private LocationsData GetLocationData(Coordinate coordinate)
{
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["StoresDb"].ConnectionString);
connection.Open();

string SQL = "GetNearbyLocations";
SqlCommand command = new SqlCommand(SQL, connection);
command.Parameters.Add(new SqlParameter("@CenterLatitude", coordinate.Latitude));
command.Parameters.Add(new SqlParameter("@CenterLongitude", coordinate.Longitude));
command.Parameters.Add(new SqlParameter("@SearchDistance", distanceDropDown.SelectedValue));
command.Parameters.Add(new SqlParameter("@EarthRadius", 3961)); // In Miles
command.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(command);
LocationsData data = new LocationsData();
da.Fill(data.Location);
return data;
}

... we have to create a string representation of a JSON object array that holds all this info, this is done in the GetJSONLocations method. Please see the method for more documentation on how that variable is created.

Info that the JSON object holds: 
   - Location Name
   - Address 
   - Url Encoded Address
   - Latitude
   - Longitude

This info was written to the screen using the RegisterClientScriptBlock.

Using the same method of as before we create another JSON variable (homeSpatialInfo) that holds the “home info”. This info is the address that the user typed in.

This information is used for the popup marker, as well as the link that is provided within the popup marker. Clicking on the “directions” link (within the popup marker) will take you to Google’s map site with the “to” and “from” address parameters already populated, therefore giving you a directional map from the location you typed in, to the location that you clicked on the map. Smooth, eh?

(click for larger view)

Adding a new Location

To add a new location, fire up the AddNewStore.aspx page and type in the required info (in this case I’m requiring all fields, but this can be changed to fit your own needs) and submit. If the address cannot be found, (Google Maps API returns “0” for both the Lat/Lon coordinates that it cannot find) it will inform you. Otherwise the address will be added and then upon your next search, it will be returned in the results (if the location is within the distance specified).

Database Note: The locations that are in the Stores.mdf that come with this solution are 200 Starbucks stores that are within the New York Area. This is not a comprehensive list, this is just a list I happened to have for testing purposes.

And that’s all folks!

Download and enjoy. :)

StoreLocator.zip (521.92 KB)

In my previous post I showed you how to return XML from the SQL Server Database. To further this topic, you can also format your XML to use Xml elements. This time, we're going to take a list of Customers and view their orders along with some information from the order, thats right, its straight from MSDN (with a couple tweaks).

This SQL Query will return Xml formatted as formatted elements.

Returns:

<Customers>
   <Customer>
      <CustomerID>1</CustomerID>
      <CustomerType>S</CustomerType>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>43860</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>44501</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>45283</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>46042</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
      </Customer>
      ...
<Customers>

Explanation

The XML AUTO mode tells SQL Server to return the data in a XML format. We have provided the Sales.Customer table an alias of "Customer". If I had not done this, the results would have printed each element as:

<Sales.Customer>...</Sales.Customer>

This is not desirable.

The ELEMENTS option tells SQL Server to return the data as Xml elements, not as attributes. Had we eliminated this from the query, our query would have returned this:

This can work, but this time I wanted to demostrate the ELEMENT option.

The ROOT('Customers') allows us to set a root Xml element named "Customers".

Conclusion

While this may not be the next best thing you've ever seen, its still cool and it can save you time in the long run. Possible uses might include a quick way to get data out of your system and