Writing relational SQL data as XML

How can you get XML from SQL Server? The FOR XML methods allow you do this.
This is a simplified example of what you need to do.

Sql Server Code:

CREATE TABLE parent
  (
     parent_id   INT IDENTITY PRIMARY KEY,
     parent_name NVARCHAR(100)
  );

CREATE TABLE child
  (
     child_id   INT IDENTITY PRIMARY KEY,
     parent_id  INT REFERENCES parent(parent_id),
     child_name NVARCHAR(100)
  );

INSERT INTO parent
            (parent_name)
VALUES     ('JOHN');

INSERT INTO parent
            (parent_name)
VALUES     ('TOM');

INSERT INTO parent
            (parent_name)
VALUES     ('STACY');

INSERT INTO child
            (parent_id,
             child_name)
VALUES     (1,
            'Emily')

INSERT INTO child
            (parent_id,
             child_name)
VALUES     (1,
            'Ryan')

INSERT INTO child
            (parent_id,
             child_name)
VALUES     (2,
            'Krusna')

INSERT INTO child
            (parent_id,
             child_name)
VALUES     (2,
            'Uma')

INSERT INTO child
            (parent_id,
             child_name)
VALUES     (2,
            'Kali')

INSERT INTO child
            (parent_id,
             child_name)
VALUES     (3,
            'Jimbo')

INSERT INTO child
            (parent_id,
             child_name)
VALUES     (3,
            'Howard')

SELECT parent.parent_id   AS "@parentId",
       parent.parent_name AS "@parentname",
       (SELECT child_id   AS "@childId",
               child_name AS "@childName" FROM child WHERE child.parent_id = parent.parent_id
        FOR xml path ('Child'), type)
FROM   parent 
FOR xml path ('Parent'), root ('Container'), type

Random Dates in Sql Server

One of our testing requirements for queries requires us to generate random dates in a table.
This is my solution.
Since the introduction of table valued parameters, I believe SQL Server has made developer’s lives a little bit easier.

The first step is to create a type in the database.

--Create table type for dates and integers
CREATE TYPE DBO.mydatesandintegers AS TABLE
	(	nu int NOT NULL PRIMARY KEY, 
		dt DateTime NULL)

Then I make use of the random function to generate a random number. I use this number as parameter to the date add function. Several variations are possible here.

DECLARE @myint mydatesandintegers, @i int = 0
begin
while @i < 50
	BEGIN
		--SELECT RAND() AS [RandomNumber]
		SET @i = @i + 1
		INSERT @myint(nu, dt) SELECT RAND() * 100000 AS [RandomNumber], 
		DATEADD(mi,RAND() * 1000000 * -1, GETDATE()) /*Date part value can also be changed.*/
	END
	SELECT * FROM @myint
END

How to move a rectangle on a canvas in WPF.

I am currently taking an online course to learn video game programming at coursera.org I am half-way through the course and I decided to do something similar in C# using WPF. This is my first foray in WPF. I might think of seriously converting all the python code into C# WPF if I get enough time on my hands. Here’s a rectangle that bounces on the screen.

using System;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Shapes;

namespace Pong
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void DispatcherTimerTick(object sender, EventArgs e) 
        {
            var random = new Random();
            double w = (double) (random.Next(0, (int) canvas.Width));
            double h = (double)(random.Next(0, (int) canvas.Height));
            Canvas.SetLeft(this.Rect, w - (this.Rect.Width / 2));
            Canvas.SetTop(this.Rect, h - (this.Rect.Height / 2));
            
            if (DetectCollision(this.Rect, new Point(w - (this.Rect.Width / 2), h - (this.Rect.Height / 2))))
            {
                ResetPosition();
            }
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            System.Windows.Threading.DispatcherTimer dispatcherTimer = new System.Windows.Threading.DispatcherTimer();
            dispatcherTimer.Tick += new EventHandler(DispatcherTimerTick);
            dispatcherTimer.Interval = new TimeSpan(0, 0, 0, 10);
            dispatcherTimer.Start();
        }

        private void ResetPosition()
        {
            /*Brings shape to the center of the canvas.*/
            Canvas.SetLeft(this.Rect, (canvas.Width / 2) - (this.Rect.Width / 2));
            Canvas.SetTop(this.Rect, (canvas.Height / 2) - (this.Rect.Height / 2));
        }

        private bool DetectCollision(Rectangle s, Point p)
        {
            if(Canvas.GetLeft(s) > p.X || Canvas.GetTop(s) < p.Y)
            {
                return true;
            }
            else if (Canvas.GetRight(s) < p.X || Canvas.GetBottom(s) > p.Y)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}

BITWISE operations in databases.

I was surprised to find a bit-product column in the product we were using.
I have rarely seen this in a database. The features column is a bit-product column.

In our fleet we have vehicles and sales people always ask for a list of vehicles having certain features.
The features are stored in a single column, where the total of the column represents the feature.

vehicle_type_feature_id vehicle_type
1 Nav-Sat
2 SunPass
4 Chrome Wheels
8 XM Radio
16 DVD System

So how do you list all vehicles that have a Nav-Sat system? That depends on the database you’re using. Both Oracle & Sql Server support bit-wise operations that make this possible.

[Oracle]
DROP TABLE tmp_vehicle_type; 

CREATE TABLE tmp_vehicle_type 
  ( 
     vehicle_type            VARCHAR2(20), 
     vehicle_type_feature_id NUMBER(3) 
  ); 

INSERT INTO tmp_vehicle_type 
            (vehicle_type, 
             vehicle_type_feature_id) 
VALUES     ('Nav-Sat', 1); 

INSERT INTO tmp_vehicle_type 
            (vehicle_type, 
             vehicle_type_feature_id) 
VALUES     ('SunPass', 2); 

INSERT INTO tmp_vehicle_type 
            (vehicle_type, 
             vehicle_type_feature_id) 
VALUES     ('Chrome Wheels', 4); 

INSERT INTO tmp_vehicle_type 
            (vehicle_type, 
             vehicle_type_feature_id) 
VALUES     ('XM Radio', 8); 

INSERT INTO tmp_vehicle_type 
            (vehicle_type, 
             vehicle_type_feature_id) 
VALUES     ('DVD System', 16); 

COMMIT; 

DROP TABLE tmp_vehicles; 

CREATE TABLE tmp_vehicles 
  ( 
     vehicle_id   NUMBER(3), 
     vehicle_name VARCHAR2(20), 
     features     NUMBER(10) 
  ); 

INSERT INTO tmp_vehicles 
VALUES     (100, 'HONDA-CRV', 5); 

INSERT INTO tmp_vehicles 
VALUES     (200, 'CAMRY', 9); 

INSERT INTO tmp_vehicles 
VALUES     (300, 'FIAT', 17); 

INSERT INTO tmp_vehicles 
VALUES     (400, 'CHRYSLER 300', 24); 

INSERT INTO tmp_vehicles 
VALUES     (500, 'CHRYSLER 200', 25);             

COMMIT;

Now if you wanted to find all vehicles that have a Nav-Sat you need to use the following query:

SELECT * FROM tmp_vehicles where BITand(FEATURES,1) > 0

If you wanted to do something similar in SQL Server, it is equally easy.

[SQL Server]
DROP TABLE tmp_vehicle_type; 

CREATE TABLE tmp_vehicle_type 
  ( 
     vehicle_type            NVARCHAR(20), 
     vehicle_type_feature_id INT 
  ); 

INSERT INTO tmp_vehicle_type 
            (vehicle_type, vehicle_type_feature_id) 
VALUES     ('Nav-Sat', 1); 

INSERT INTO tmp_vehicle_type 
            (vehicle_type, 
             vehicle_type_feature_id) 
VALUES     ('SunPass', 2); 

INSERT INTO tmp_vehicle_type 
            (vehicle_type, 
             vehicle_type_feature_id) 
VALUES     ('Chrome Wheels', 4); 

INSERT INTO tmp_vehicle_type 
            (vehicle_type, vehicle_type_feature_id) 
VALUES     ('XM Radio', 8); 

INSERT INTO tmp_vehicle_type 
            (vehicle_type, vehicle_type_feature_id) 
VALUES     ('DVD System', 16); 

DROP TABLE tmp_vehicles; 

CREATE TABLE tmp_vehicles 
  ( 
     vehicle_id   int, 
     vehicle_name NVARCHAR(20), 
     features     int 
  ); 

INSERT INTO tmp_vehicles 
VALUES     (100, 'HONDA-CRV', 5); 

INSERT INTO tmp_vehicles 
VALUES     (200, 'CAMRY', 9); 

INSERT INTO tmp_vehicles 
VALUES     (300, 'FIAT', 17); 

INSERT INTO tmp_vehicles 
VALUES     (400, 'CHRYSLER 300', 24); 

INSERT INTO tmp_vehicles 
VALUES     (500,'CHRYSLER 200',25);

To find out all vehicles having a Nav-Sat, use the following query:

select * from tmp_vehicles where features = features | 1

There is a slight advantage to using this kind of logic to store multiple values in a single column.
The main advantages are data storage and database performance.

Create XML Files using Linq

In one my previous blog posts, I had briefly touched upon XSD to create XML files. LINQ offers a similar, yet much faster feature to create XML files.

Here’s how you can quickly create an XML file from a database table.
The database table is created in a database named Movies on my SQLServer Express database. It has two columns MovieId and Title.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Linq;

namespace XMLFileCreator
{
    class XmlCreator
    {
        private static SqlConnection _sqlConn; 
        static void Main()
        {
            DbConnect();
            CreateXml(FetchRecords());
            _sqlConn.Close();
        }
        static DataTable FetchRecords()
        {
            const string sqlStr = "SELECT * FROM Movies";
            var sqlCommand = new SqlCommand()
            {
                Connection = _sqlConn,
                CommandText = sqlStr,
                CommandType = CommandType.Text
            };

            var adapter = new SqlDataAdapter(sqlCommand);
            var dt = new DataTable();
            adapter.Fill(dt);
            Console.WriteLine(String.Format("No of Movie Titles:\t{0}", dt.Rows.Count));
            return dt;
        }
        static void DbConnect()
        {
            _sqlConn = new SqlConnection()
                          {
                              ConnectionString = @"Server=.\SQLExpress;Database=Movies;Trusted_Connection=True;"
                          };
            _sqlConn.Open();
        }
        static void CreateXml(DataTable dataTable)
        {
            var movieDoc = new XDocument
                (new XElement("Root", new XAttribute("DataType", "MovieListing"),
                     new XElement("MovieCollection")));
            foreach (DataRow dataRow in dataTable.Rows)
            {
                    var movieTitle = new XElement("Id", new XAttribute("MovieId",dataRow["MovieId"]), 
                                        new XElement("Title", dataRow["Title"]));
                    if (movieDoc.Root != null) movieDoc.Root.Element("MovieCollection").Add(movieTitle);
            }
            XDocument outputDoc = XDocument.Load(movieDoc.CreateReader());
            outputDoc.Save(@"movieList.xml");

        }
    }
}

VBScript GetExtension

This is how you can read file extensions in VBScript.

Function GetExt(fileName)
    Dim pos, name, xtn
    pos = InstrRev(fileName,".")
    xtn = Mid(fileName,pos+1)
    name = Mid(fileName,1,pos-1)
    GetExt = xtn
    'Wscript.Echo(name)
    'Wscript.Echo(xtn)
End Function

VBScript LPad function.

I needed this for some work related stuff. Since VBScript doesn’t have a native lpad function, I wrote this one.

'Test Padding
Wscript.Echo Lpad(12456,10,"*")

Function Lpad(strInput, length, character)
    Dim strOutput
    If Len(strInput) >= length Then
        strOutput = strInput
    Else
        Do While Len(strOutput) <= length - Len(strInput) -1
            strOutput = character & strOutput 
        Loop
        strOutput = strOutput & strInput
    End if
    Lpad = strOutput
End Function

How to get the double!

Precision:

Precision is a property of a number format and refers to the amount of information used to represent a number. Better or higher precision means more numbers can be represented, and also means a better resolution: the numbers that are represented by a higher precision format are closer together. 1.3333 is a number represented with a precision of five decimal digits: one before and four after the decimal point. 1.333300 is the same number represented with 7-digit precision.
The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle, up to 38 digits of precision.
In .NET, the data types for storing precision are decimal and double. Neither of them have the precision that the Oracle Number datatype has. So how do you retrieve double values from an Oracle DB and get them to display in your application.

Here’s a simple test. I am using Oracle 9i for this.

create table random_numbers AS SELECT dbms_random.normal, object_name FROM all_objects;
private static void FunWithDoubles(OracleConnection oracleConnection)
        {
            const string strSql = "random_numbers";
            var cmd = new OracleCommand(strSql, oracleConnection) {CommandType = CommandType.TableDirect};
            var recSet = cmd.ExecuteReader();
            while (recSet.Read())
            {
                //This fails because oracle precision is too high and you're going to see a casting error.
                Console.WriteLine(recSet["NORMAL"].ToString(), recSet["OBJECT_NAME"].ToString());
            }
        }
This code fails with an exception.

An implicit conversion between .NET and Oracle has caused it to happen. The fix for this is not use a datareader and use a dataAdapter with the ReturnProviderSpecificTypes property set to true.

private static void FunWithDoubles(OracleConnection oracleConnection)
        {
            const string strSql = "random_numbers";
            var cmd = new OracleCommand(strSql, oracleConnection) {CommandType = CommandType.TableDirect};
            var oracleDataAdapter = new OracleDataAdapter(cmd) {ReturnProviderSpecificTypes = true};
            var dataTable = new DataTable();
            oracleDataAdapter.Fill(dataTable);
            foreach (DataRow dataRow in dataTable.Rows)
            {
                Console.WriteLine("{0} {1}", dataRow[0],dataRow[1]);
            }
        }
Note:Oracle provides two numeric datatypes exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE. They support all of the basic functionality provided by the NUMBER datatype. However, while NUMBER uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE use binary precision. This enables faster arithmetic calculations and usually reduces storage requirements. However these data types are not available in Oracle 9i.

Some History: Half a Century of Failure

Some History: Half a Century of Failure.

Bulk Uploads in .NET

We are in the middle of migrating data from an Oracle database to a SQL Server database. After the data has been migrated we are required to verify the data. There are three ways to do this.

The quick way:

Export your table in sql server to a comma delimited file.

Use DataPump in Oracle to import the file. This method is relatively quick. Unfortunately we are on a version of Oracle, where Datapump is not yet available to us.

The easy way:

Write a select query that will create an insert statement. This has been a tried and tested method of yours truly. Then export the results to a text file. Convert the EOL to a Unix Format. FTP the file to Unix and then execute it. This approach works very well if there are very few records to be inserted. When we are talking about millions of records, the export operation (actually called save as) in sql server takes time. The EOL conversion takes a long time and then the insert operation seems like it is going on forever.

The lazy way:

There is another way which I like to call the lazy way. For the activity that we are doing, we are able to do this within a single executable that migrates the data and also fetches the records from sql server and bulk inserts it to the oracle database, does a comparison and spits out the missing records. Within ODP .Net there is a Bulkcopy class that makes things easier for us. Here’s the code to do something like this. For the purposes of this example, assume that the SUPPLIERS table has the same structure on both databases – Oracle and Sql Server.

     string strSql = "SELECT * FROM suppliers";
try
    {
    var sqlCommand = new SqlCommand()
    {
        CommandText = strSql,
        Connection = sqlConnection,
        CommandType = CommandType.Text
    };

    var sqlDataAdapter = new SqlDataAdapter() { SelectCommand = sqlCommand };
    sqlDataAdapter.Fill(dataTable);
    return dataTable;
    }
catch (Exception exception)
    {
    Console.WriteLine(string.Format("Fetch Supplier Records:\t{0}", exception.Message));
    }   

   try
{
    
    new OracleCommand("truncate table SUPPLIERS", oracleConnection).ExecuteNonQuery();


    var oracleBulkCopy = new OracleBulkCopy(oracleConnection)
    {
        DestinationTableName = "SUPPLIERS",
        BulkCopyOptions = OracleBulkCopyOptions.UseInternalTransaction
    };
     oracleBulkCopy.WriteToServer(dataTable);
     oracleBulkCopy.Dispose();
}
catch(Exception ex)
{
    Console.WriteLine("failed to write:\t{0}",ex.Message)
    }
}
Follow

Get every new post delivered to your Inbox.