Archive for March, 2009

Did You Know?

Each day I learn something new, as do all of us here on Ctrl+Shift+B. Our love of learning and technology is what prompted us to start this blog. I have always said that you either keep moving or wither away and die. I believe this to be true in all areas of life, from relationships to careers. But I believe this to be especially true in Information Technologies. What is new today will be the standard five years from now.

Happy Learning!

~Ron

How to Upload and Download Image/BLOB Data in SQL Server

There was a very interesting post over on SQL Authority on how to upload image data to SQL Server. The question was posed, “How do you then retrieve that data from SQL Server.”

You could build an application and retrieve the data through a GUI, but that would be a rather cumbersome process if you had multiple images to download.

Within SQL Server itself, this requires a multi-step process of created a BCP format file and then using BCP QueryOut to create the actual image files. Fast and efficient, but it could be better.

My preferred method would be to use CLR. Once you have registered the assembly, making sure to mark it for external access, this task can be accomplished in a single step.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void usp_RetrieveBlobData(string query, string outpath)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand(query, connection);
            SqlDataReader reader = command.ExecuteReader();

            using (reader)
            {
                while (reader.Read())
                {
                    FileStream fs = File.Create(outpath);
                    BinaryWriter bw = new BinaryWriter(fs);

                    byte[] file = (byte[])reader.GetSqlBinary(0);

                    bw.Write(file);

                    bw.Close();
                    fs.Close();
                }
            }
        }
    }
};

NOTE: You will also need to enable CLR on the server.

~Ron

Get Job Details from Sql Server 2005

We are doing a database audit and we had to get all the jobs running on a particular server and the time at which it is run. So wrote a quick script as given below to get it:

SELECT
 [Name],
 'Scheduled at ' + LEFT(RIGHT('000000' + CAST(next_run_time AS VARCHAR(10)),6),2) + ':' +
 SUBSTRING(RIGHT('000000' + CAST(next_run_time AS VARCHAR(10)),6),3,2) + ':' +
 RIGHT(RIGHT('000000' + CAST(next_run_time AS VARCHAR(10)),6),2)
FROM
 dbo.sysjobs j LEFT OUTER JOIN dbo.sysJobSchedules s ON
 j.job_id=s.job_id

Hope it helps.

Happy Programming!!!

Cheers,

Raja

Database Versioning

We all know the importance of incrementing version numbers with software releases. But what about the database? Database objects are just as important to a web application as any front end page, code behind, JavaScript or web service.

Most companies I have worked for have very strict control over source code versions. Many have stricter policies for database access. But where are the controls for database design?

There are some very excellent pieces written on database versioning at OdeToCode.com.

  1. Three rules for database work
  2. The Baseline
  3. Change Scripts
  4. Views, Stored Procedures and the Like
  5. Branching and Merging

After reading through these articles I can definitely see the merit of database source control and versioning, and I hope you can as well. But how do you implement these features? No one likes change.

First decide the scope of control that will be implements and the access needed for each role on your team(s). (WARNING: Please don’t read one article and take that as gospel.) Once that is decided, create a plan of action and a timeline to implement the new policies. Please keep operational tempo in mind as no once wants to change processes right before an impending release.

Do your research and come to a consensus on what will work well in your environment. What works well in one environment may not work well in another.

 

~Ron

Disappointed with Linq to Entity :-(

We have a scenario where in we have to check for the existence of User Information in a database (residing in production SQL Server) other than the application database. We are using LINQ to Entities as our data cum business layer (Isn’t that what it should be ???). Since I have to get the information from another database (or server rather) I didnt want to create another entity set so I just created a stored proc in my application database to check for that (we have linked server set up so just took 2 mins to write the SP). Now I cannot use the written stored procedure in my L 2 E since it is not returning any entity as such :-(. I searched online and could not find any direct methods to do so. We have to work around to make this happen. I hope they fix this in the forth coming releases. Do you guys know of any direct method by which I can make use of a function import which does not return a value or a scalar value?

Happy Programming!!!

Cheers,

Raja

Checkbox + JQuery Pattern

We were working on a project which includes multiple selection entries under multiple categories. All the checkboxes are dynamically generated (Database lookup) since those were master items maintained by the admins of the system. We were able to easily generate the checkboxes by bringing in data using JSON and dynamically appending checkboxes to a DIV using JQuery. Now the problem was to get all the selected entries per category. That is when we brought in the following pattern by which a good naming convention followed by the amazing power of Jquery came into play. We made sure that all our check boxes followed a particular pattern like chk+category+id(the primary id). And then wrote the function given below to get the selected items in a particular category.

function GetCheckedItems(item)
{
    var strIDList="";   
    //this is the pattern: the class is going to be the checkboxtype followed by checkbox ex. contactcheckbox
    //so to get the generalized checkbox typ replace the checkbox with ""
    //this would give us just .contact. so taking the substring from 1 which would give us contact
    var strSearch=item.toString().replace(('checkbox'),'').substring(1);  
    //now prefixing chk to it so that we get the checkbox name
    strSearch="chk"+strSearch;
    //alert (strSearch);
    $(item).each(
        function()
        {
            if ($(this).is(":checked"))
                //replacing the chk followed by the checkbox type to get the IDs
                strIDList+=$(this).attr("id").replace((strSearch),"") + ",";
        }
    )
    //getting the length to negate the last ,
    var strLength=strIDList.length - 1;
    return strIDList.substring(0,strLength);
}

Hope this helps.

Happy Programming!!!

Cheers,

Raja

Little hack to have localized images – ASP .NET

In my current project we are implementing localization to support Spanish. We thought it would be good if we have our header image change as per the language specification selected by the user. Obviously we thought of adding those images under their specific resource files but the problem was how to display them. Although we found the code to display those images dynamically (using Response.ContentType as image and Response.BinaryWrite) we were in search of a better solution. That is when I did this little hack to set the path for the src of the image from the resource file as given below:


<img id="imgTest" runat="server" alt="test image" src="<%$ Resources:ImageFile %>" meta:resourcekey="imgTestResource1"/>

And specified the path of the image to point to specific image files in the respective resource files. Works like a charm. I dont know if this is a good solution but it works fine. I would love to hear your feedback or better solutions if any.

Happy Programming!!!

Cheers,

Raja