Native Auditing Features in SQL Server.

How do you find out if there have been some changes made to the database records? You institute an audit on the table. How do you institute an audit? In the early beginnings of RDBMS, you could use a trigger. This is how things were done and sometimes they still are.

CREATE TABLE [dbo].[cp_user] 
  ( 
     [customer_id] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY, 
     [user_id]     [NVARCHAR](50) NOT NULL, 
     [password]    [NVARCHAR](300) NULL, 
     [first_name]  [NVARCHAR](50) NOT NULL, 
     [last_name]   [NVARCHAR](50) NOT NULL 
  ) 

go 

CREATE TABLE [dbo].[cp_user_log] 
  ( 
     [audit_log_id]  [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY, 
     [customer_id]   [INT] NULL, 
     [user_id]       [NVARCHAR](50) NOT NULL, 
     [password]      [NVARCHAR](300) NULL, 
     [first_name]    [NVARCHAR](50) NOT NULL, 
     [last_name]     [NVARCHAR](50) NOT NULL, 
     [log_reason]    [NVARCHAR](1) NOT NULL, 
     [logon_user_id] [NVARCHAR](100) NOT NULL 
  ) 

go 

CREATE TRIGGER [dbo].[tr_cp_user_D] 
ON [dbo].[cp_user] 
FOR DELETE 
AS 
    INSERT INTO dbo.cp_user_log 
                ([customer_id], 
                 [user_id], 
                 [password], 
                 [first_name], 
                 [last_name], 
                 [log_reason], 
                 [logon_user_id]) 
    SELECT [customer_id], 
           [user_id], 
           [password], 
           [first_name], 
           [last_name], 
           'D', 
           User_name() 
    FROM   deleted 

go

Newer versions of database allowed native auditing capabilities, so all this trigger writing could be avoided.
From SQL Server 2000, there was C2 auditing.You can read more on C2 auditing here.

USE  master
GO
EXEC sp_configure 'show advanced option', '1'
Go
RECONFIGURE
GO
--Turn it On
exec sp_configure 'c2 audit mode', 1
go
RECONFIGURE
GO
--Turn it Off
exec sp_configure 'c2 audit mode', 0
go
RECONFIGURE
GO

In order to read this audit log, you would have to run a query on the audit file. The audit files are stored in the Data Folder on MSSQL installation.

SELECT *
 FROM ::fn_trace_gettable(
   'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MIG42DT\MSSQL\DATA\audittrace20140527103256.trc', default
	)
	ORDER BY [StartTime] DESC
GO

Since 2008 Sql Server has added another feature to its arsenal. It allows you to have a database specific audit using native tools. This is how you do it. More information can be found here

USE [master]
GO

/****** Object:  Audit [TestingAudit]    Script Date: 05/27/2014 11:48:46 ******/
CREATE SERVER AUDIT [TestingAudit]
TO FILE 
(	FILEPATH = N'C:\app\sqlserverauditlogs\'
	,MAXSIZE = 300 MB
	,MAX_ROLLOVER_FILES = 100
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = '49c0e7ee-16f7-42da-bffb-57a6f26a6141'
)
GO

USE [abs4]
GO

CREATE DATABASE AUDIT SPECIFICATION [TestingDBChanges]
FOR SERVER AUDIT [TestingAudit]
ADD (DELETE ON DATABASE::[abs4] BY [public]),
ADD (EXECUTE ON DATABASE::[abs4] BY [public]),
ADD (INSERT ON DATABASE::[abs4] BY [public]),
ADD (RECEIVE ON DATABASE::[abs4] BY [public]),
ADD (REFERENCES ON DATABASE::[abs4] BY [public]),
ADD (SELECT ON DATABASE::[abs4] BY [public]),
ADD (UPDATE ON DATABASE::[abs4] BY [public])
WITH (STATE = ON)
GO

You can lookup the logs using the View Audit Logs option

Adding a computed column on an existing table in Sql Server 2008 R2

I have a scenario where a player table containing birth dates exists in the database. I need to add a column called AGE that will display the age of a player in the query. So I added a computed column to the existing table. Here’s my code.

CREATE TABLE [dbo].players 
  (
     player_id    INT IDENTITY(1, 1) PRIMARY KEY, 
     handle       NVARCHAR(20) NOT NULL, 
     first_name   NVARCHAR(100) NOT NULL, 
     last_name    NVARCHAR(100), 
     date_of_birthDATE NOT NULL 
  );

alter table players
ADD[age] AS DATEDIFF(YYYY,date_of_birth,getdate());

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.
Follow

Get every new post delivered to your Inbox.