Saturday, August 29, 2009

A Date with SQL Server 2008

Almost everybody who has worked a fair bit in SQL Server knows that the datetime data type in SQL Server is one of the most primitive. Infact, in a lot of applications I have seen, they don’t even see it worthy of being used, opting to use varchar instead. Their argument: If I am going to convert it into varchar anyways, then I might as well store it in the same format.

Hopefully, that will change with SQL Server 2008. DateTime datatype has been given special attention in the latest edition of SQL Server; so much so that I will stick my neck out and say that it is the only data type that has been enhanced!!! (FILESTREAM is not a data type, remember?). This post is an attempt to give you a preview of what the enhancements are in the DateTime datatypes.

In the previous versions of SQL Server, there were no separate datatypes for date and time. That has changed now. In SQL Server 2008, you have date and time datatypes that let you store only date or time. Apart from this, there is also the datetime2 and datetimeoffset. The datetime2 lets you store a wider range of dates. If you use datetime your minimum value for the year is 1/1/1753. The datetime2 datatype lets you store a wider range and is based on the ANSI standard. The datetimeoffset as you would have already guessed, helps you store dates for different regions. Some of the key points that you have to remember when using the above datatypes are

  • You cannot use mathematical functions or expressions with the new datetime2 datatype. Use DateAdd instead.
  • Try avoiding the format ‘dmy’. This is the only string format that produces different results with the old datetime and the new datetime2 format.
  • Most of the old t-sql functions work consistently with both the datetime and the datetime2 datatypes.
  • If you are using datetime2, use the SYSDATETIME function for more precision.
  • Though you can use the Datetimeoffset datatype, SQL Server 2008 does not support automatic support for daylight savings.
  • It does not also provide support for timezones, so the most effective way to use the offset is to store the timezone at which the date was entered.
  • Use the SWITCHOFFSET function of SQL Server to convert the offsets to the timezones.

Sunday, August 23, 2009

Madras Quiz 2009

I am just back from the annual Madras quiz that is conducted as a part of the Madras Week celebrations. The quiz this time was conducted by the Indian Quizzing League.  The quiz master for the day was Sylvian Patrick.

The event started off with a 30 question prelims. There were some thought-provoking questions from various aspects of Chennai, ranging from the Chennai Metro to the Ergo. On the whole, the prelims were not too tough. A lot of the questions were guessable. Couple of questions from the top of my head

What did Gandhiji establish in Chennai in 1918 recognising the importance of the whole nation speaking one language?

Who is the most famous grandson of Rajagopala Bhagavadar who excelled in Hari Katha kathakalakshebam?

Six teams made it to the finals, which comprised of 5 rounds of Madras Intensive questions. Jayakanthan and his teammate managed to surge ahead wresting the rolling trophy from V.V. Ramanan and Ramshankar who were lacklustre today. There were some quality answers from all the teams. I don’t remember the name of the team who finished second.

Overall the quality of the questions in the finals was passable. I personally believe the Indian Quizzing League need to find themselves some quizmasters. I attended their India quiz and there again, the quizmasters were found wanting.

 

Saturday, August 22, 2009

SQL Server 2008 FILESTREAM – Part – II

Having done the necessary set up for configuring FILESTREAM on a SQL Server instance in Part - I, we will now look at how to query a FILESTREAM column from .NET code and insert new files.

For this purpose, I have created one more table called UserMaster. This has the usual details of a user like the first name, last name and a column PhotoId which is a foreignkey to the ProductPhotoId that we created in the previous part.

Querying files or data from the FILESTREAM is not too different from how you would query and display data from an ordinary table. I first create a stored procedure to get the user details and the photo associated with each user. The stored procedure is as below

create proc dbo.GetUserDetails
as
begin
Select UM.UserId, UM.UserName, UM.FirstName, UM.LastName, UM.EmailAddress, PP.ProductPhotoId, PP.LargePhoto
from
UserMaster UM INNER JOIN ProductPhotoWithFileStream PP
On
PP.ProductPhotoId=UM.PhotoId
end

As you can see, there is nothing special with the above stored procedure. The next step is to display these photos in a Windows form. I have a DataGridView which is going to display the pics. The code for Form.cs is

   1: var connString = ConfigurationManager.ConnectionStrings["FileStreamConn"].ConnectionString;
   2: var query = "GetUserDetails";
   3: var daProducts = new SqlDataAdapter(query,connString);
   4:  
   5: productsGridView.DataSource = null;
   6: productsGridView.Refresh();
   7: var binding = new BindingSource()
   8: productsGridView.DataSource = binding;
   9:  
  10: // Populate a new data table and bind it to the BindingSource.
  11: var table = new DataTable();
  12: daProducts.Fill(table);
  13: binding.DataSource = table;
  14:  
  15: // Resize the DataGridView columns to fit the newly loaded content.
  16: productsGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
  17: productsGridView.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders);

Again, nothing fancy. Plain old-fashioned code. But good enough to query the files in the database and display it as shown below.


ImageDisplay


To insert a user, I create a form where the user will enter the details and select the picture associated with the user.

InsertForm

We will ignore the rest of the code in the form and concentrate on the Insert Button click. Below is the code for the complete button click event



   1: //1. Call the procedure to insert an empty value as photo and get transaction context
   2: var connString = ConfigurationManager.ConnectionStrings["FileStreamConn"].ConnectionString;
   3: var query = "GetUserDetails";
   4: string filePath = String.Empty;
   5: string selPhotoPath = txtFilePath.Text;
   6: SqlCommand daInsertCommand=null;
   7:  
   8: try
   9: {
  10:  
  11:     //1.1open a new connection
  12:     SqlConnection conn = new SqlConnection(connString);
  13:     conn.Open();
  14:  
  15:  
  16:     SqlTransaction tranPhoto = conn.BeginTransaction("photoInsert");
  17:  
  18:     //1.2 Execute the command to insert the photo and get the photo id
  19:     daInsertCommand = new SqlCommand(query,conn);
  20:     daInsertCommand.Transaction = tranPhoto;
  21:     var PhotoId = daInsertCommand.ExecuteScalar();
  22:  
  23:     //1.3 Get the Path Name for the newly created file.
  24:     query = "Select LargePhoto.PathName() from ProductPhotoWithFilestream where ProductPhotoId = " + PhotoId;
  25:  
  26:     daInsertCommand.CommandText = query;
  27:     var PhotoPath = daInsertCommand.ExecuteScalar();
  28:     if (!PhotoPath.Equals(DBNull.Value))
  29:     {
  30:         filePath = (string)PhotoPath;
  31:     }
  32:  
  33:     //1.4 Get the transaction context for writing to the file.
  34:     daInsertCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
  35:     var txContext = daInsertCommand.ExecuteScalar();
  36:     if (!txContext.Equals(DBNull.Value))
  37:     {
  38:         //convert the transaction into a bytearray
  39:         byte[] transContext = (byte[])txContext;
  40:         //create a photostream object to write
  41:         SqlFileStream photoStream = new SqlFileStream(filePath, transContext, System.IO.FileAccess.Write);
  42:  
  43:         //read the selected image as a byte array
  44:         byte[] imgByte = File.ReadAllBytes(selPhotoPath);
  45:  
  46:         //write to the sql filestream
  47:         photoStream.Write(imgByte, 0, imgByte.Length);
  48:  
  49:         //close the filestream and commit transaction
  50:         photoStream.Close();
  51:  
  52:  
  53:         //2. Insert the User record with the PhotoId
  54:         daInsertCommand.CommandText = "InsertUser";
  55:         daInsertCommand.CommandType = CommandType.StoredProcedure;
  56:         daInsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 10);
  57:         daInsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 10);
  58:         daInsertCommand.Parameters.Add("@UserName", SqlDbType.VarChar, 20);
  59:         daInsertCommand.Parameters.Add("@emailAdd", SqlDbType.VarChar, 100);
  60:         daInsertCommand.Parameters.Add("@PhotoId", SqlDbType.Int, 32);
  61:  
  62:         daInsertCommand.Parameters[0].Value = txtFirstName.Text;
  63:         daInsertCommand.Parameters[1].Value = txtLastName.Text;
  64:         daInsertCommand.Parameters[2].Value = txtUserName.Text;
  65:         daInsertCommand.Parameters[3].Value = "test@add.com";
  66:         daInsertCommand.Parameters[4].Value = PhotoId;
  67:  
  68:         daInsertCommand.ExecuteNonQuery();
  69:  
  70:         daInsertCommand.Transaction.Commit();
  71:  
  72:     }
  73: }
  74: catch(Exception ex)
  75: {
  76:     daInsertCommand.Transaction.Rollback();
  77:     MessageBox.Show(ex.Message);
  78: }



I have tried and added comments so that the snippet makes sense but I will re-cap the steps to insert a new file. The first thing to do is to insert an empty string value into the FILESTREAM column. This is done so that the SQL Server creates a new file on the file system. Note that a null value does not create a file.

The next thing is to get the path of the new file created and wrap it around the same transaction. This is done between lines 24 and 35. Once we get the transaction context, then writing the file is like writing to any other file on the file system.

Also note the use of the SQLFileStream data type which is tightly integrated with the FILESTREAM attribute. The downside of the approach I have shown above is the number of round trips to the database. There are atleast three, one to insert the empty value into the FILESTREAM column, another one to get the path and the final trip to get the transaction context. Though I did read somewhere that this can now be reduced with the output clause I have not tried this yet. I will get around to that in a while and post an update to this post.

That concludes our two part post on the FILESTREAM attribute. Though it is easy to get excited by the ability of thee FILESTREAM attribute, there are as with a lot of other things, considerations that need to be taken into account when using this. I found the below links useful for further reading

Using FILESTREAM with Other SQL Server Features

FILESTREAM Storage in SQL Server 2008

To FILESTREAM or not to FILESTREAM... That is the question

Sunday, August 16, 2009

Landmark Quiz ‘09

Technorati Tags: ,

It was August 15th and India’s 63rd Independence Day. As with every year, the evening was consumed by the 18th edition of India’s largest open quiz: The Landmark Quiz Competition. The prelims were set to start at 2.30 P.M. The turnout was huge once more. Almost every seat in Music Academy was taken. It is not for nothing that Chennai is the quizzing capital of India and quiz enthusiasts turned out in large numbers to confirm that. If that was not enough proof, the quality of quizzing settled the matter beyond doubt.

The prelims had an interesting set of 40 questions, starting with the National Anthem rendering by the grandma granddaughter pair of Nithyashree and Late. D.k. Pattamal and closing with a question on Vikram crooning for Kanthasaamy. Try these questions

What is a bunch of bananas called? or What does the M stand for in M.R. Radha and M.L. Vasanthakumari.

You should be able to get the full list of questions on www.quizzing.in site in a short while.

The top 8 teams qualified for the Chennai finals. The team calling themselves No Enthu da won the Regional round and were crowned Chennai champions. They qualified for the National finals along with the team finishing runners up The Dhukkan Chargers.

The National Finals was another mouth-watering fare with the quiz master Dr. Navin Jayakumar raising the bar on the diffficulty of the questions and teams doing brilliantly to keep up with it. Though the teams for the national finals had qualified from three other cities, Pune, Mumbai and Bangalore, the teams comprised mostly of quizzers from Chennai. The team comprising of V.V. Ramana, Swamy and Samanth called Q.E.D walked home with the top laurels of the evening but not before they were given a run for their money by the team from Bangalore “We are like that only.” It was so close that the winner had to be decided by a tie-breaker.

Overall it was a well spent evening of quizzing and we all returned home having acquired a bit more power because “Knowledge is Power”.

Thursday, August 13, 2009

SQL Server 2008 FILESTREAM – Part - I

There have been many a times when designing my database, I have faced this question. Should I store my files\images as BLOB or should I store it on the file system and keep a reference in the database. There has never been an easy answer to this question. Till now that is; With the FILESTREAM feature of SQL Server, the answer is “It does not matter”.

By using the FILESTREAM feature SQL Server 2008, you can store your files in the database and configure SQL Server to internally store it on the file system. This means you can make use of the large storage capacity of your file system without the overhead of having to store the path of the file in the database. In the next couple of blogs, I will try and show you how to work with the FILESTREAM feature.

To use this feature, you have to enable FILESTREAM on the SQL Server and the individual database. You can use SQL Server Configuration Services to enable FILESTREAM on your SQL Server. Go to the properties of the SQL Server instance you want to enable the feature on and go to the FILESTREAM tab. The rest of it should be self-explanatory. Enable the feature as shown below.

SQLConfig

With that done, we will now go on to configure FILESTREAM on the database. We do that with the following script

EXEC sp_configure 'filestream access level', '2'
RECONFIGURE
GO

The script configures FILESTREAM access level to 2 which means you can access files with both T-SQL or streaming I/O. If you are sure you will access FILESTREAM only with T-SQL, you can set the access level to 1. Setting it to 0 disables the FILESTREAM. There is also a level ‘3’ that you use if you need Remote Access.

The next step is to indicate to the database, which folder we want SQL Server to use to store the files. To do this, you first create a SQL Server Filegroup with the following script

ALTER DATABASE TestDatabase ADD FILEGROUP TestFileGroup CONTAINS FILESTREAM
GO

What is remaining is to configure the path in which the files for the above filegroup will be stored. You can use the below script to do the same.

ALTER DATABASE TestDatabase
    ADD FILE
      (
       NAME = FileFolder,
       FILENAME = 'C:\FileStream\FileFolder'
      )
   TO FILEGROUP TestFileGroup
GO

where C:\FileStream should be an existing folder.

If you navigate to C:\FileStream you will see that FileFolder has now been created. With the above steps, your database is now ready to use the FileStream.

With all the configuration now done, the only remaining step is to create the table to store the data for the files. I have created the table with the script below

CREATE TABLE ProductPhotoWithFilestream(
  [ProductPhotoID] [int] IDENTITY(1,1) NOT NULL,
  [LargePhoto] varbinary(max) FILESTREAM NULL,
  [RowGuid] UNIQUEIDENTIFIER NOT NULL  ROWGUIDCOL UNIQUE DEFAULT NEWID(),
CONSTRAINT [PK_ProductPhotoWithFilestream_ProductPhotoID] PRIMARY KEY CLUSTERED
(
  [ProductPhotoID] 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

Notice how, in the third line of the script you indicate a FILESTREAM column. You declare a FILESTREAM attribute on a varbinary(max) column. It is important to understand that FILESTREAM is not entirely a SQL Datatype. It is just an attribute that can be applied on the varbinary(max) column.

The other thing worth mentioning in the script above is the use of the RowGUID. This is a pre-requisite for using FILESTREAM. The table on which you enable FILESTREAM should have a GUID column.

Since, this post is now getting a bit lengthy, I will break now. In Part-II, I will show you how to use this feature from a .NET application.