Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

April 05, 2013

TSQL: Count / SUM with 'Where' condition

My problem

Doing some reporting services I found myself in need to have group aggregates. I try to solve most hurdles in the SQL queries so I don't need to much trickery in the report definitions. I've done these queries many times and usually found myself doing sub queries from the main one, or using temporary tables etc.

Imagine this question based on the tables below: How many herbivores with what total value are in my zoo?

Example tables :)

Problem solved!

I've found the answer on Stack Overflow. The trick is using a 'join' and a 'case when' in your count/sum syntax. So the query to answer the question above is like this:

 SELECT dbo.Food.[Description] as Food  
     ,COUNT(CASE dbo.Species.FoodID WHEN dbo.Food.Id THEN 1 ELSE null END) AS [Count]  
     ,SUM((CASE dbo.Species.FoodID WHEN dbo.Food.Id THEN dbo.Species.Value ELSE null END) AS Value  
  FROM dbo.Species  
  LEFT JOIN dbo.Food ON dbo.Species.FoodID = dbo.Food.Id 
  GROUP BY dbo.Food.[Description]  

And the result:
The expected result of the query

March 13, 2013

VB.Net: SQL Server Image column to PicturBox Bitmap

There's always a debate at the office whether images should be stored in the database or in the file structure. I prefer the database, using the SQL Server "image" column data type.
This way there is no need to set authentication on the file server folders who can and cannot access the location of the images.

Usually the 'location' is still saved in a database column. When accessing the image at the stored location you have to catch exceptions if someone deleted the image from the server without updating the 'location' in the database.

When reading and writing one uses a byte array to 'communicate' the data. Consider the table above where the image column is "FactuurHeader".

Check the example "read" code:

 Public Shared Function GetHeader(ByVal cnStr As String) As Bitmap  
     'default (empty) logo  
     Dim logo As New Bitmap(100, 108)  
     Using cn As New SqlConnection(cnStr)  
       Using cmd As New SqlCommand()  
         cmd.CommandText = "SELECT TOP 1 FactuurHeader FROM dbo.tblPraktijk WHERE Actief=1"  
         cmd.Connection = cn  
         Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)  
         If dr.HasRows Then  
           If Not dr.Item("FactuurHeader").Equals(DBNull.Value) Then  
             'Here's the voodoo: from image column data to bitmap... 
             Dim imgData() As Byte = DirectCast(dr.Item("FactuurHeader"), Byte())  
             If imgData.Length > 0 Then  
               Dim stream As New MemoryStream(imgData)  
               logo = New Bitmap(stream)  
             End If  
           End If  
         End If  
       End Using  
     End Using  
     Return logo  
   End Function  

The example code takes the first record from the database table (tblPraktijk), checks if the "FactuurHeader" isn't null and returns a bitmap object. When the record does not exist (empty table) or it's image column is empty, a default (empty) bitmap is returned (first line in the method)

When writing to the column the process is reversed by creating a byte array from your bitmap. I use two static methods to do the conversion:

   ''' <summary>  
   ''' Convert Image to byte array 
   ''' (From picturebox to database data)  
   ''' </summary>  
   ''' <param name="img">Image to be converted</param>  
   ''' <returns>A byte array representing the image</returns>  
   ''' <remarks></remarks>  
   Public Shared Function ImageToBytes(img As Image) As Byte()  
     If img Is Nothing Then Return Nothing  
     Dim ms As New MemoryStream()  
     img.Save(ms, System.Drawing.Imaging.ImageFormat.Png)  '<- Use correct image format!
     Return ms.ToArray()  
   End Function 
   ''' <summary>  
   ''' Convert byte atray to Image  
   ''' (From database data to picturebox bitmap) 
   ''' PicturBox1.Image = new Bitmap(myImage) 
   ''' </summary>  
   ''' <param name="bytes">Byte arry to be converted</param>  
   ''' <returns>Image from the byte array</returns>  
   ''' <remarks></remarks>  
   Public Shared Function BytesToImage(bytes As Byte()) As Image  
     If bytes Is Nothing Then Return Nothing  
     Dim ms As New MemoryStream(bytes)  
     Dim img As Image = Image.FromStream(ms)  
     Return img  
   End Function  

Note: my default image format is PNG. Tweak the ImageToBytes function if you want a little flexibility in using different image formats...

July 12, 2012

Linked tables in SQL Server 2008 (R2)

Usually Google is my friend, but looking for SQL Linked tables gave me some frustration. Admittedly, I searched for the wrong thing...

My problem

Consider 2 identical databases on 1 (SQL 2008) server. The data is -off course- different, however some tables need to be 'in sync'. My initial thought was to make database one the lead database, storing the data. In database two I delete that particular table and create a linked table to database one. Just like one would do in MS Access...

Problem solved?

Hold that thought: "Just like in access"... Short version of the story: there are no linked tables in SQL Server!

Problem solved!

It turns out in SQL Server it's not "linked tables", it's Synonyms!!! So when I did a search on Synonyms Google showed a wealth of information.

Some things I stumbled into when replacing the tables with synonyms:
  • First remove all foreign keys related to tables which are being replaced by synonyms
  • Then remove the primary keys of tables to be replaced
  • Synonyms can not have FK constraints!
So  linking tables in SQL turns out to be as easy as:
 DROP TABLE [dbo].[myTable]  
 CREATE SYNONYM [dbo].[myTable] FOR [TheOtherDatabase].[dbo].[myTable]