C#, .NET, LINQ, Silverlight, SQL Server, Web & Windows Development

Category Archives: SQL

In my job I usually need to work with tsql and every query must have the best performance possible, that’s why I used to use the tool within the sql management studio named Graphical Execution Plans but I never know about the tsql commands needed to do it, but here are:

My work is always an adventure, and it’s fun too. I’m currently working with a project where I need to connect to a DBISAM Tables (dat files) to get some data so I thought is a good idea to create a post about it.

The first thing you need to do is to download the DBISAM Drivers (in case you don’t have it installed), I downloaded from here (you need to register first!)

I downloaded the file DBISAM-ODBC-TRIAL – DBISAM ODBC Trial

After the installation we need to configure our data source, for this we will use the ODBC Data Source Administrator we have in Windows, just go to Administrative Tools and open the ODBC (Data Sources) program.

NOTES (In case you have Windows 7 64-bit)
Because I have the Windows 7 Pro 64-bit version I need to use the 32-bit ODBC Data Source Administrator Tool because the drivers are in 32-bits for more information please check this article.

So we are ready to use this data source in our program. First we need to import the libraries we will use.

using System;
using System.Data.Odbc;

As you can see I imported the ODBC library from System.Data Assembly to use this class to connect to our data source. The next step is to create the OdbcCommand that have the SQL query we want to use.

Then the second thing you need to know is that we will use a COM library, so we need to include this library to our project. In case you don’t have this library installed in your computer download here,

From Project menu, select Add Reference.

In the COM tab, select Microsoft SQLXML Bulkload 3.0 Type Library (xblkld3.dll) and click OK. You will see the Interop.SQLXMLBULKLOADLib assembly created in the project.

Then we can use the class SQLXMLBulkLoad3Class to perform this xml bulk load, but we need a xsd schema file to define the SQL XML import that will map the xml fields to the sql fields in the database.

As you can see I created this XSD Schema Document using XSD Elements and sql relationships using Annotations (to map relations between tables) and xs elements to define fields inside sql relations (tables).

As you see is a very simple code, the tricky thing here is to add the attribute [STAThread] to the main method, because I had some errors without it. Basically I found that this attribute is only needed when you are working with COM Interop (see this link).

The SQLXMLBulkLoad3Class object has many options but I used the most important like ConnectionString (Identifies the OLEDB connection string that provides the necessary information to establish a connection to an instance of the database.), ErrorLogFile (Specifies the file name into which the XML Bulk Load logs errors and messages.), KeepIdentity (Specifies how to deal with the values for an Identity type column in the source file.), XMLFragment (Specifies whether the source data is an XML fragment) and the Execute (Bulk loads the data by using the schema file and data file (or stream) that are provided as parameters.) to make this work (If you need more info please check this link).

Then I run the program you can see the results:

As you can see the data has been imported successfully!

So we start this with a XML file from a third application, we transform this XML and then we use this new file to import the data to our datatables, cool isn’t? Now we are ready to make some reports!

Recently I was working on an import project that should import xml data from a third application to our database to make some reports.

The problem with this was that the xml documents from the third application had a format that wasn’t easy to read (At least didn’t have the format I wanted) so my first step was to make a XML Transformation that is basically a fancy name to transform an input xml to an output document (this output document can be a xml document or data stream).