Tuesday, September 27, 2011

Avoid Connection Leak from SqlDataReader

Many developer are using SqlDataReader object to return SQL Data to client. Since SqlDataReader is connection based data class, the SQL connection actually is open under the hood.
Below is the typical example of using SqlDataReader. In DBClass, GetData() method returns SqlDataReader, and the caller (client) uses the DataReader object.

class Program
{
   static void Main(string[] args)
   {
      Program p = new Program();
      p.Run();                  
      Console.ReadLine(); //wait
   }

   void Run()
   {
      DBClass db = new DBClass();
      SqlDataReader rdr = db.GetData("SELECT * FROM Tab1");
      rdr.Read();         
      db.PrintConnectionState();

      rdr.Close();
      db.PrintConnectionState();
   }
}

class DBClass
{
   private string connStr = "Data Source=.;Initial Catalog=TestDB;Integrated Security=SSPI;";
   private SqlConnection conn;

   public SqlDataReader GetData(string sql)
   {
      conn = new SqlConnection(connStr);
      conn.Open();
      SqlCommand cmd = new SqlCommand(sql, conn);
      //SqlDataReader rdr = cmd.ExecuteReader(); // <== #1
      SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);  // <== #2
         
      return rdr;
   }

   public void PrintConnectionState()
   {
      Console.WriteLine(conn.State.ToString());
   }
}

If we use cmd.ExecuteReader() as in #1 above, SQL connection is open regardless of closing SqlDataReader object. The console output of using #1 method is "Open Open." So the connection will be open until your application is closed, which is typical example of connection leak.

If we use CommandBehavior.CloseConnection in ExecuteReader() method as in #2, the output will be "Open Close." That means the connection will be closed when SqlDataReader is closed. This is a good way to avoid connection leak when using SqlDataReader & when you do not have any way of accessing connection object.

Friday, September 16, 2011

How to check database connection in Excel file

Micorsoft Excel supports external data sources such as SQL Server. In terms of database connection, Excel allows both OLEDB connnection and ODBC connection. The following code snippet shows a way of how to detect the DB connection programmatically. The code opens Excel file in the background and check any connection is related to DB connection.

using System;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace FindConnectionInDB
{
   class Program
   {
      static void Main(string[] args)
      {
         string xlsfile = @"c:\temp\book1.xlsx";

         Program p = new Program();
         bool ret = p.CheckDbConnectionInExcel(xlsfile);
         Console.WriteLine("Has DB Connection : {0}", ret);
      }

      public bool CheckDbConnectionInExcel(string excelFile)
      {
         bool hasDBConnection = false;
         Excel.Application excelApp = null;
         Excel.Workbook wb = null;

         try
         {
            excelApp = new Excel.Application();
            wb = excelApp.Workbooks.Open(excelFile);

            for (int i = 1; i <= wb.Connections.Count; i++)
            {
               if (wb.Connections[i].Type == Excel.XlConnectionType.xlConnectionTypeOLEDB ||
                   wb.Connections[i].Type == Excel.XlConnectionType.xlConnectionTypeODBC)
               {
                  hasDBConnection = true;
                  break;
               }
            }

            wb.Close(true);
            excelApp.Quit();
         }
         finally
         {                        
            ReleaseExcelObject(wb);
            ReleaseExcelObject(excelApp);
         }

         return hasDBConnection;
      }

      private void ReleaseExcelObject(object obj)
      {
         try
         {
            if (obj != null)
            {
               Marshal.ReleaseComObject(obj);
               obj = null;
            }
         }
         catch (Exception ex)
         {
            obj = null;
            throw ex;
         }
         finally
         {
            GC.Collect();
         }
      }
   }
}

One thing to note is that you need to release Excel object by using Marshal.ReleaseComObject() so that in any case your Excel process can be disposed.

Tuesday, September 13, 2011

Import Excel file to SQL table using TSQL

There are many tools to import Excel file to SQL database, a few examples are Import/Export Wizard in SSMS, SSIS, etc. This post summarizes a way of importing Excel to Table by using TSQL statement. Exporting can be done by using the similar (but opposite direction) way.
First, to import Excel file, one can use the following ad hoc query. If the Excel file is frequently used for importing, it is recommended to add linked server. Otherwise, ad hoc query is enough.


OPENDATASOURCE allows to open data source by using OLEDB provider. So if you don't have OLEDB provider on your machine, it's not gonna work. The query below specifies Excel file (book1.xls) as a Data Source and its sheet name 'Sheet1' in this case. Think of Sheet1 as a kind of table. The '...' between OpenDataSource and Sheet name is needed since the format follows TSQL 4 part name (server.db.user.dbOjbect).

SELECT * INTO Table1
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Temp\book1.xls;Extended Properties=EXCEL 5.0')...[Sheet1$];

If you run the query above, you might get the following error:


Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.


This is because the SQL Server option for ad hoc distributed query is disabled. This can be enabled as follows.

sp_configure 'show advanced options', 1
go
reconfigure 
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure 
go

Now if you rerun the SELECT INTO statement above, it should work. One problem you might notice is that the column type or column size is not exactly what you want. For example, for number column in Excel can be imported into 'float' column type in SQL table.

To cope with this kind of problem, you can create SQL table first on your SQL Server and then import the data from Excel. So, you create the following table.

CREATE TABLE [dbo].[ExcelTest2](
 [Id] [int] NOT NULL PRIMARY KEY,
 [Name] [nvarchar](50) NULL,
 [Tel] [nvarchar](20) NULL
) 

And then import Excel file data into the existing SQL table. This time you have to use INSERT INTO instead of SELECT INTO since table alreasy exists.

INSERT INTO Table2
SELECT * 
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Temp\book1.xls;Extended Properties=EXCEL 5.0')...[Sheet1$];

Now, let's take a example of exporting data from SQL table to Excel.
If you want to attach SQL table data to existing Excel file, you can use the following TSQL.
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\temp\book1.xls;', 
'SELECT * FROM [Sheet1$]') 
SELECT Id,Name,Tel FROM Table1

By the way, if you open the Excel file while you run TSQL statement trying to open the same Excel file, you will get the following error. Just close your Excel.exe.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Note:
If you want to export SQL table/view or any SQL query result to Excel file without any coding, here is a handy tool : Convert SQL Table or Query Results to Excel file

Sunday, September 11, 2011

Import binary data to FILESTREAM column / Export binary data from FILESTREAM column

My previous post (How to use FILESTREAM) explained how to setup and use FILESTREAM. In this post, I will be little more specific about binary data in FILESTREAM.

The following code shows how to import binary file to FILESTREAM column by using ADO.NET and how to export binary data from the FILESTREAM column. Let's assume we're using the same sample table as in the previous post.

using System.IO;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        string connStr = "Data Source=.;Integrated Security=true;Initial Catalog=FSDB";        

        static void Main(string[] args)
        {
            Program p = new Program();
            p.SavePictureFileToDB(@"c:\test\input.jpg");
            p.ReadPictureDataAndCreaetPictureFile(@"c:\test\output.jpg");
        }

        void SavePictureFileToDB(string picFile)
        {
            byte[] pic = File.ReadAllBytes(picFile);            

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                //@data is FileStream column / id=100
                string sql = "INSERT FSTable VALUES (NEWID(), 100, @data)";  
                SqlParameter param1 = new SqlParameter("@data", System.Data.SqlDbType.VarBinary);
                param1.Size = -1;
                param1.Value = pic;

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(param1);
                cmd.ExecuteNonQuery();                
            }
        }

        void ReadPictureDataAndCreaetPictureFile(string outputFilename)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                string sql = "SELECT Data FROM FSTable WHERE Id=100";
                SqlCommand cmd = new SqlCommand(sql, conn);
                object objResult = cmd.ExecuteScalar();
                byte[] bytes = (byte[])objResult;

                File.WriteAllBytes(outputFilename, bytes);
            }
        }
    }
}

SavePictureFileToDB() reads picture file and put the binary data into FILESTREAM column by using INSERT statement. ReadPictureDataAndCreaetPictureFile() reads binary data from the FILESTREAM column and create a new picture file with those bytes data. As you can see, the code is pretty much the same as handling varbinary data type. This approach will be good if the binary data is not that big and there is no performance issue on it. However, if the binary data is big and so performance is slow, you can consider using SqlFileStream, which is faster access to the data.