Posts Tagged 'C#'



Partial Methods

We all know about Partial Classes (in case you dont know read here) but I was intriqued when I look at LINQ to SQL generated designer.cs. I saw methods like

partial void OnLoaded();

partial  void OnCreated();

After a bit of researching I came to know that these are called as Partial Methods and their implementation is pretty neat. Check out this blog to get an overview about partial method implementation Or check out this blog for a detailed explanation about Partial methods.

Happy Programming!!!

Cheers,

Raja

Advertisements

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

Server side safe JSON

We are working on a project which involves a lot of JQuery and JSON. We wanted to make sure that the JSON does not fail to evaluate so I got the function given below (after a bit of searching) to make the string safe so that it successfully evaluates. There you go…..

// Make a string safe for JSON evaluation
private static string SafeJSON(string sIn)
{
    StringBuilder sbOut = new StringBuilder(sIn.Length); //using the StringBuilder Class for performance
    foreach (char ch in sIn) //Looping through the string (dont u think a counter is better???)
    {
        if (Char.IsControl(ch) || ch == '\'')
        {
            int ich = (int)ch;
            sbOut.Append(@"\u" + ich.ToString("x4"));
            continue;
        }
        else if (ch == '\"' || ch == '\\' || ch == '/')
        {
            sbOut.Append('\\');
        }
        sbOut.Append(ch);
    }
    return sbOut.ToString();
}

Hope this helps.

Happy Programming!!!

Cheers,

Raja