Technical Insights: Azure, .NET, Dynamics 365 & EV Charging Architecture

Category: ASP.NET Page 3 of 7

Category for ASP.NET

Compression in Silverlight Isolated Storage

Isolated storage in silverlight is used to store information or object therefore we don’t need to go to database to get all the information over and over again but again to use isolated storage or not should be based on case by case. Based on the implementation of the code below, I’ve found that it can compress the isolated storage file from 0.8Mb becoming 0.1 MB which is great enough for me since the quota limit is 1 mb and we try not to exceed that limit.

I’ve got this compression method from Peter Bromberg blog.

This compression method is a wrapper to SharpZip Library for Silverlight, you can download it from here, you need this library before using the code below. You can download the code below from here.

I’ve also created a Isolated Storage File with the assembly version as the file name to make sure that we have clean isolated storage file assuming the uncompressed version of isolated storage is already in production and we need to clean it up. “AssemblyVersion” properties will return the property of the current version no of the running assembly. CheckIsolatedStorageFileVersion will make sure that we always have the clean isolated storage for the new assembly.

Sample of usage
1. How to read/deserialize object from isolated storage

 Dim objMessageCodes As MessageCodes = GetIsolatedStorage(Of MessageCodes)("MyFile.txt")

2. How to write/serialize object to isolated storage

      Dim _messageCodes As New MessageCodes(mListMessage)
      WriteIsolatedStorage(_messageCodes, "myfile.txt")

These are the definition of the above function to serialize/deserialize object from your silverlight, this function is created based on Generic so it’s flexible enough to accept anything

Imports System.Collections.Generic
Imports System.ServiceModel
Imports System.Threading
Imports System.IO
Imports System.IO.IsolatedStorage
Imports System.Xml.Serialization
Imports System.Runtime.Serialization
Imports System.Reflection

Public Class MySL
 Private mAppStorage As IsolatedStorageFile

    ''' 
    ''' This is used to write the compressed object to Isolated Storage
    ''' 
    ''' 
    ''' 
    Private Sub WriteIsolatedStorage(Of T)(ByVal obj As T, ByVal filename As String)
        Try
            Dim xmlByte As Byte() = Compression.SerializeAndCompress(obj)

            Using _stream As IsolatedStorageFileStream = mAppStorage.CreateFile(filename)
                _stream.Write(xmlByte, 0, xmlByte.Length)
            End Using
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    ''' 
    ''' this is used get the compressed object from Isolated storage
    ''' 
    ''' 
    ''' 
    ''' 
    ''' 
    Private Function GetIsolatedStorage(Of T)(ByVal fileName As String) As T
        Try
            Using _stream As IsolatedStorageFileStream = mAppStorage.OpenFile(fileName, FileMode.Open)
                Using _reader As BinaryReader = New BinaryReader(_stream)
                    Dim tmpBytes As Byte()
                    ReDim tmpBytes(1024)
                    Dim fullBytes As Byte() = Nothing
                    Dim xmlStream As MemoryStream = New MemoryStream()

                    While True
                        Dim read As Integer = _reader.Read(tmpBytes, 0, tmpBytes.Length)

                        If (read <= 0) Then
                            fullBytes = xmlStream.ToArray()
                            Exit While
                        End If

                        xmlStream.Write(tmpBytes, 0, read)
                    End While

                    Dim xmlTempbyte As Byte() = xmlStream.ToArray()
                     Return Compression.DecompressAndDeserialize(Of T)(xmlTempbyte)
                End Using

            End Using

            Return Nothing

        Catch ex As Exception
            Throw ex
        End Try

    End Function

Private _compression As Compression = Nothing

    Private ReadOnly Property Compression() As Compression
        Get
            If (_compression Is Nothing) Then
                _compression = New Compression()
            End If

            Return _compression
        End Get
    End Property

    ''' 
    ''' get the assembly version
    ''' 
    ''' 
    ''' 
    ''' 
    Private ReadOnly Property AssemblyVersion() As String
        Get
            Dim name As String = Assembly.GetExecutingAssembly().FullName
            Dim asmName As AssemblyName = New AssemblyName(name)
            Return asmName.Version.ToString().Replace(".", "")
        End Get
    End Property

''' 
    ''' try to clear isolated storage
    ''' 
    ''' 
    Private Sub CheckIsolatedStorageFileVersion()
        Try
            Dim isoStorage As IsolatedStorageFile = IsolatedStorageFile.GetUserStoreForSite

            If Not mAppStorage.FileExists(AssemblyVersion + ".txt") Then
                'clear all the isolated storage
                isoStorage.Remove()
                mAppStorage.Remove()

                mAppStorage = IsolatedStorageFile.GetUserStoreForApplication()

                Using _stream As IsolatedStorageFileStream = mAppStorage.CreateFile(AssemblyVersion + ".txt")
                    Using sw As StreamWriter = New StreamWriter(_stream)
                        sw.Write(AssemblyVersion)
                    End Using
                End Using
                'Throw New Exception("Clearing!!!")
            End If

        Catch ex As Exception
            Throw ex
        End Try
    End Sub

End Class

Wrapper Class to SL SharpZipLib

Imports System
Imports System.Text
Imports System.IO
Imports System.Collections
Imports System.Diagnostics
Imports System.Collections.Generic
Imports System.Runtime.Serialization
Imports ICSharpCode.SharpZipLib.Zip.Compression
Imports System.Xml.Serialization
Imports System.Text.RegularExpressions

Public Class Compression

    Public Sub New()
    End Sub

    Public Function Serialize(Of T)(ByVal inst As T) As Byte()
        Dim dcs As New DataContractSerializer(GetType(T))

        Using ms As New MemoryStream
            dcs.WriteObject(ms, inst)
            Return ms.ToArray()
        End Using

    End Function

    Public Function Deserialize(Of T)(ByVal objectData As Byte()) As T
        Dim dcs As New DataContractSerializer(GetType(T))

        Using ms As New MemoryStream(objectData)
            Return CType(dcs.ReadObject(ms), T)
        End Using

    End Function

    Public Function SerializeAndCompress(Of T)(ByVal inst As T) As Byte()
        Dim b As Byte() = Serialize(Of T)(inst)
        Return Compress(b)
    End Function

    Public Function DecompressAndDeserialize(Of T)(ByVal bytData As Byte()) As T
        Dim bytes As Byte() = Decompress(bytData)
        Return Deserialize(Of T)(bytes)
    End Function

    Public Function Compress(ByVal strInput As String) As Byte()
        Try
            Dim bytData As Byte() = System.Text.Encoding.UTF8.GetBytes(strInput)
            Dim ms As New MemoryStream()
            Dim defl As New Deflater(9, False)

            Using s As Stream = New Streams.DeflaterOutputStream(ms, defl)
                s.Write(bytData, 0, bytData.Length)
                s.Close()
            End Using

            Return DirectCast(ms.ToArray(), Byte())
        Catch
            Throw

        End Try
    End Function

    Public Function Compress(ByVal bytData As Byte()) As Byte()
        Try

            Dim ms As New MemoryStream()
            Dim defl As New Deflater(9, False)

            Using s As Stream = New Streams.DeflaterOutputStream(ms, defl)
                s.Write(bytData, 0, bytData.Length)
                s.Close()
            End Using

            Return DirectCast(ms.ToArray(), Byte())
        Catch
            Throw

        End Try
    End Function

    Public Function Compress(ByVal bytData As Byte(), ByVal ParamArray ratio As Integer()) As Byte()

        Dim compRatio As Integer = 9
        Try
            If ratio(0) > 0 Then
                compRatio = ratio(0)
            End If
        Catch

        End Try

        Try
            Dim ms As New MemoryStream()
            Dim defl As New Deflater(compRatio, False)

            Using s As Stream = New Streams.DeflaterOutputStream(ms, defl)
                s.Write(bytData, 0, bytData.Length)
                s.Close()
            End Using

            Return DirectCast(ms.ToArray(), Byte())
        Catch
            Throw
        End Try
    End Function

    Public Function Decompress(ByVal bytInput As Byte()) As Byte()
        Try

            Dim ms As New MemoryStream(bytInput, 0, bytInput.Length)
            Dim bytResult As Byte() = Nothing
            Dim strResult As String = [String].Empty
            Dim writeData As Byte() = New Byte(4095) {}

            Using s2 As Stream = New Streams.InflaterInputStream(ms)
                bytResult = ReadFullStream(s2)
                s2.Close()
            End Using

            Return bytResult
        Catch
            Throw
        End Try
    End Function

    Public Function ReadFullStream(ByVal stream As Stream) As Byte()
        Dim buffer As Byte() = New Byte(32767) {}
        Using ms As New MemoryStream()
            While True
                Dim read As Integer = stream.Read(buffer, 0, buffer.Length)
                If read <= 0 Then
                    Return ms.ToArray()
                End If
                ms.Write(buffer, 0, read)
            End While
        End Using

        Return buffer
    End Function

End Class

FormsAuthentication.GetRedirectUrl only get the first parameter of querystring

I found the issue with FormsAuthentication.GetRedirectUrl when it redirects then it redirects with the first querystring that you have while in fact you might have more than one querystring

e.g http://localhost/myweb/login.aspx?returnurl=myview.aspx?viewID=123&viewname=abc&viewall=false then the standard FormsAuthentication will redirect to http://localhost/myweb/myview.aspx?viewID=123

but where’s the remaining viewname querystring and viewall querystring??to fix this, just use the code below to pick the remaining querystring

Methods:

  'this is used to fix the issue with FormsAuthentication.GetRedirectUrl only pick the first query string
            'get the original Redirect URL
            Dim redirectUrl As StringBuilder = New StringBuilder(FormsAuthentication.GetRedirectUrl(" ", True))
            Dim coll As NameValueCollection = objRequest.QueryString

            'iterate through every key in query string
            'add the missing query string
            For Each key As String In coll.AllKeys
                If (String.Compare(key, "returnurl", True)  0) Then
                    Dim values As String() = coll.GetValues(key)

                    If (values.Length > 0) Then
                        Dim pair As String = String.Format("{0}={1}", key, values(0))

                        If (redirectUrl.ToString().IndexOf(pair) < 0) Then
                            redirectUrl.Append("&" + pair)
                        End If
                    End If
                End If
            Next

            'this is to retain the original URL as in the query string
            objResponse.Redirect(redirectUrl.ToString())

an activex control on this page might be unsafe to interact with other parts of the page. do you want to allow this interaction?

I keep getting this message “an activex control on this page might be unsafe to interact with other parts of the page. do you want to allow this interaction” when I try to access a page that use Javascript to access ActiveX Code. I’ve spent quite sometime to get rid of this error message and I found out that

  1. The code need implement IObjectSafety Interface
  2. You need to digitally sign the code with valid certificate

Step 1 : Detail on how to implement IObjectSafety
-Create an interface file called as IObjectSafety.vb/IObjectSafety.cs

CSharp (IObjectSafety.cs) :

[ComImport()]
[Guid("CB5BDC81-93C1-11CF-8F20-00805F2CD064")]
[InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
interface IObjectSafety {
   [PreserveSig()]
   int GetInterfaceSafetyOptions(ref Guid riid, out int pdwSupportedOptions, out int pdwEnabledOptions);

   [PreserveSig()]
   int SetInterfaceSafetyOptions(ref Guid riid, int dwOptionSetMask, int dwEnabledOptions);
}
Imports System.Runtime.InteropServices

 _
 _
 _
Interface IObjectSafety
     _
    Function GetInterfaceSafetyOptions(ByRef riid As Guid, ByRef pdwSupportedOptions As Integer, ByRef pdwEnabledOptions As Integer) As Integer

     _
    Function SetInterfaceSafetyOptions(ByRef riid As Guid, ByVal dwOptionSetMask As Integer, ByVal dwEnabledOptions As Integer) As Integer
End Interface

in your ActiveX code you need to implement IObjectSafety

CSharp:


VB.NET:

 _
 _
 _
 _
Public NotInheritable Class ClientUtility
    Implements IObjectSafety


#Region "IObjectSafety Constants"

    Private Const INTERFACESAFE_FOR_UNTRUSTED_CALLER As Integer = &H1
    Private Const INTERFACESAFE_FOR_UNTRUSTED_DATA As Integer = &H2
    Private Const S_OK As Integer = 0

#End Region

#Region "IObjectSafety Methods"

    Public Function GetInterfaceSafetyOptions(ByRef riid As System.Guid, ByRef pdwSupportedOptions As Integer, ByRef pdwEnabledOptions As Integer) As Integer Implements IObjectSafety.GetInterfaceSafetyOptions
        pdwSupportedOptions = INTERFACESAFE_FOR_UNTRUSTED_CALLER Or INTERFACESAFE_FOR_UNTRUSTED_DATA
        pdwEnabledOptions = INTERFACESAFE_FOR_UNTRUSTED_CALLER Or INTERFACESAFE_FOR_UNTRUSTED_DATA
        Return S_OK
    End Function

    Public Function SetInterfaceSafetyOptions(ByRef riid As System.Guid, ByVal dwOptionSetMask As Integer, ByVal dwEnabledOptions As Integer) As Integer Implements IObjectSafety.SetInterfaceSafetyOptions
        Return S_OK
    End Function

#End Region

CSharp:

[ProgId("Fransiscus.Authentication.ClientUtility")]
[ClassInterface(ClassInterfaceType.AutoDual), ComSourceInterfaces("ControlEvents")]
[Guid("0577147B-6941-4f15-9EFB-2551FEB3D6CC")]
[ComVisible(true)]
public sealed class ClientUtility : IObjectSafety
{


    #region "IObjectSafety Constants"

    private const int INTERFACESAFE_FOR_UNTRUSTED_CALLER = 0x1;
    private const int INTERFACESAFE_FOR_UNTRUSTED_DATA = 0x2;
    private const int S_OK = 0;

    #endregion

    #region "IObjectSafety Methods"

    public int GetInterfaceSafetyOptions(ref System.Guid riid, ref int pdwSupportedOptions, ref int pdwEnabledOptions)
    {
        pdwSupportedOptions = INTERFACESAFE_FOR_UNTRUSTED_CALLER | INTERFACESAFE_FOR_UNTRUSTED_DATA;
        pdwEnabledOptions = INTERFACESAFE_FOR_UNTRUSTED_CALLER | INTERFACESAFE_FOR_UNTRUSTED_DATA;
        return S_OK;
    }

    public int SetInterfaceSafetyOptions(ref System.Guid riid, int dwOptionSetMask, int dwEnabledOptions)
    {
        return S_OK;
    }
#endregion

}

Step 2:
You need to obtain certificate and sign it, You also need Windows Server 2008 SDK to sign your code using SignTool.exe (type Signtool SignWizard in command prompt to follow the wizard and sign your DLL)
For more detail please open verisign website (http://www.verisign.com/support/code-signing-support/code-signing/identity-authentication.html) or click here

Resolve URL functions

This snippet code is credited to Scott’s Hanselman, This Resolve URL function is used where you want to implement it on your business layer.

Methods:


        #region "Image URL helpers"

        public static string ResolveUrl(string originalUrl)
        {
            if (originalUrl == null)
                return null;
            // *** Absolute path - just return
            if (originalUrl.IndexOf("://") != -1)
                return originalUrl;
            // *** Fix up image path for ~ root app dir directory
            if (originalUrl.StartsWith("~"))
            {
                string newUrl = "";
                if (System.Web.HttpContext.Current != null)
                    newUrl = System.Web.HttpContext.Current.Request.ApplicationPath + originalUrl.Substring(1).Replace("//", "/");
                else  // *** Not context: assume current directory is the base directory
                    throw new ArgumentException("Invalid URL: Relative URL not allowed.");
                // *** Just to be sure fix up any double slashes
                return newUrl;
            }
            return originalUrl;
        }

        /// Works like Control.ResolveUrl including support for ~ syntax
        /// but returns an absolute URL.
        /// 
        /// Any Url, either App relative or fully qualified
        /// if true forces the url to use https
        /// 
        public static string ResolveServerUrl(string serverUrl, bool forceHttps)
        {    // *** Is it already an absolute Url?
            if (serverUrl.IndexOf("://") > -1)
                return serverUrl;
            // *** Start by fixing up the Url an Application relative Url
            string newUrl = ResolveUrl(serverUrl);
            Uri originalUri = System.Web.HttpContext.Current.Request.Url;
            newUrl = (forceHttps ? "https" : originalUri.Scheme) + "://" + originalUri.Authority + newUrl;
            return newUrl;
        }

        /// 
        /// This method returns a fully qualified absolute server Url which includes
        /// the protocol, server, port in addition to the server relative Url.
        ///
        /// It work like Page.ResolveUrl, but adds these to the beginning.
        /// This method is useful for generating Urls for AJAX methods
        /// 
        /// Any Url, either App relative or fully qualified
        /// 
        public static string ResolveServerUrl(string serverUrl)
        {
            return ResolveServerUrl(serverUrl, false);
        }

        #endregion

Usage:

 sb.AppendFormat("

", row.WidgetItemClass); sb.AppendFormat("More Info", ProductsService.GetProductUrl(row.ProductID), ResolveServerUrl("~/GetWhiteLabelFile.aspx?whiteLabelFileID=" + row.WidgetItemLinkImageID.ToString())); sb.AppendFormat("

");

Upload Multiple Files to FTP in .NET

I’ve played around with FTP couple of weeks back. My application is required to feed FTP folder with 1000+ files daily. I’ve been googling around and I found one of the methods is to use WebApplication so I decided to put this WebApplication method to upload while iterating every single files in the directory. After 5 files, it keeps giving me error The remote server returned an error: (503) Bad sequence of commands. Strangely the only the first 4 files always uploaded successfully, my thought was the FTP server forcely terminated the connection which is right. After googling I found that we should set KeepAlive property to false which means new connection is always created instead of maintained but unfortunately WebClient class doesn’t have KeepAlive property. Finally, I’ve come up with this code which solves my issue in uploading 1000+ files one after the other by doing Asynchronous Upload

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.Threading;
using System.IO;

namespace Helpers
{
    public class FtpHelpers
    {
        public class FtpState
        {
            private ManualResetEvent wait;
            private FtpWebRequest request;
            private string fileName;
            private Exception operationException = null;
            string status;

            public FtpState()
            {
                wait = new ManualResetEvent(false);
            }

            public ManualResetEvent OperationComplete
            {
                get { return wait; }
            }

            public FtpWebRequest Request
            {
                get { return request; }
                set { request = value; }
            }

            public string FileName
            {
                get { return fileName; }
                set { fileName = value; }
            }
            public Exception OperationException
            {
                get { return operationException; }
                set { operationException = value; }
            }
            public string StatusDescription
            {
                get { return status; }
                set { status = value; }
            }
        }

        public void AsynchronousUpload(string destinationPath, string sourcePath, string userName, string password)
        {
            // Create a Uri instance with the specified URI string.
            // If the URI is not correctly formed, the Uri constructor
            // will throw an exception.
            ManualResetEvent waitObject;

            Uri target = new Uri(destinationPath);
            string fileName = sourcePath;
            FtpState state = new FtpState();
            FtpWebRequest request = (FtpWebRequest)WebRequest.Create(target);
            request.Method = WebRequestMethods.Ftp.UploadFile;
            request.KeepAlive = false;
            request.Proxy = null;

            // This example uses anonymous logon.
            // The request is anonymous by default; the credential does not have to be specified.
            // The example specifies the credential only to
            // control how actions are logged on the server.

            if (!(string.IsNullOrEmpty(userName) && string.IsNullOrEmpty(password)))
            {
                request.Credentials = new NetworkCredential(userName, password);
            }

            // Store the request in the object that we pass into the
            // asynchronous operations.
            state.Request = request;
            state.FileName = fileName;

            // Get the event to wait on.
            waitObject = state.OperationComplete;

            // Asynchronously get the stream for the file contents.
            request.BeginGetRequestStream(
                new AsyncCallback(EndGetStreamCallback),
                state
            );

            // Block the current thread until all operations are complete.
            waitObject.WaitOne();

            // The operations either completed or threw an exception.
            if (state.OperationException != null)
            {
                throw state.OperationException;
            }

        }

        private static void EndGetStreamCallback(IAsyncResult ar)
        {
            FtpState state = (FtpState)ar.AsyncState;

            Stream requestStream = null;
            // End the asynchronous call to get the request stream.
            try
            {
                requestStream = state.Request.EndGetRequestStream(ar);
                // Copy the file contents to the request stream.
                const int bufferLength = 2048;
                byte[] buffer = new byte[bufferLength];
                int count = 0;
                int readBytes = 0;
                FileStream stream = File.OpenRead(state.FileName);
                do
                {
                    readBytes = stream.Read(buffer, 0, bufferLength);
                    requestStream.Write(buffer, 0, readBytes);
                    count += readBytes;
                }
                while (readBytes != 0);
                // IMPORTANT: Close the request stream before sending the request.
                requestStream.Close();
                // Asynchronously get the response to the upload request.
                state.Request.BeginGetResponse(
                    new AsyncCallback(EndGetResponseCallback),
                    state
                );
            }
            // Return exceptions to the main application thread.
            catch (Exception e)
            {
                state.OperationException = e;
                state.OperationComplete.Set();
                return;
            }

        }

        // The EndGetResponseCallback method
        // completes a call to BeginGetResponse.
        private static void EndGetResponseCallback(IAsyncResult ar)
        {
            FtpState state = (FtpState)ar.AsyncState;
            FtpWebResponse response = null;
            try
            {
                response = (FtpWebResponse)state.Request.EndGetResponse(ar);
                response.Close();
                state.StatusDescription = response.StatusDescription;
                // Signal the main application thread that
                // the operation is complete.
                state.OperationComplete.Set();
            }
            // Return exceptions to the main application thread.
            catch (Exception e)
            {
                state.OperationException = e;
                state.OperationComplete.Set();
            }
        }

    }
}

Usage:

#Region "Properties"

    Private _ftpUploader As FtpHelpers = Nothing

    Private ReadOnly Property FtpUploader() As FtpHelpers
        Get
            If (_ftpUploader Is Nothing) Then
                _ftpUploader = New FtpHelpers()
            End If

            Return _ftpUploader
        End Get
    End Property

#End Region

Private Sub ProcessFilesToFTP()

        ' make a reference to a directory
        Dim di As New IO.DirectoryInfo(ConfigurationManager.AppSettings("OutputZIPDirectory"))
        Dim jobFiles As IO.FileInfo() = di.GetFiles()

        Console.WriteLine(String.Format("FTP Location: {0}", ConfigurationManager.AppSettings("ftplocation")))

        'list the names of all files in the specified directory
        For Each jobFile As IO.FileInfo In jobFiles

            'process only zip file
            If (jobFile.FullName.Contains("zip")) Then
                Console.WriteLine(String.Format("Upload file {0}", jobFile.Name))

                FtpUploader.AsynchronousUpload(ConfigurationManager.AppSettings("ftplocation") + "/" + jobFile.Name, jobFile.FullName, _
                                   ConfigurationManager.AppSettings("ftpuser"), _
                                   ConfigurationManager.AppSettings("ftppassword"))

            End If

        Next

        Console.WriteLine(String.Format("XML files has been uploaded to {0}", ConfigurationManager.AppSettings("ftplocation")))
    End Sub

The drawback with this code is in the FTP connection where the connection always reinitialized for every single file.

SQL Server Function using CLR

UPDATED: I’ve added one function to write from BLOB in SQL Server table to the disk straight away

I thought this article might be useful for anyone that wants to implement .NET code to SQL server level. In this case I really need CLR because I want to do compression of images and I believe it’s not possible to do that using pure SQL server stored procedure and I’m trying to avoid creating a .NET application just for compression of images through row by row processing.

Here we start:

This is your C#/.NET code, you need to declare it as SQL function or SQL stored procedure

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Zip = ICSharpCode.SharpZipLib.Zip.Compression;

namespace CLRCompressionFunctions
{
    public partial class CompressionCore
    {
        [SqlFunction()]
        public static SqlBytes fn_Compress(SqlBytes uncompressedBytes)
        {
            if (uncompressedBytes.IsNull)
                return uncompressedBytes;

            MemoryStream memory = new MemoryStream();

            ICSharpCode.SharpZipLib.Zip.Compression.Streams.DeflaterOutputStream stream =
                new ICSharpCode.SharpZipLib.Zip.Compression.Streams.DeflaterOutputStream(memory, new Zip.Deflater(Zip.Deflater.BEST_COMPRESSION), 131072);

            stream.Write(uncompressedBytes.Buffer, 0, Convert.ToInt32(uncompressedBytes.Length));
            stream.Flush();
            stream.Close();
            stream = null;

            return new SqlBytes(memory.ToArray());
        }

        [SqlFunction()]
        public static SqlBytes fn_Decompress(SqlBytes compressedBytes)
        {
            if (compressedBytes.IsNull)
                return compressedBytes;

            ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputStream stream =
                new ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputStream(new MemoryStream(compressedBytes.Buffer));
            MemoryStream memory = new MemoryStream();
            byte[] writeData = new byte[4096];
            int size;

            while (true)
            {
                size = stream.Read(writeData, 0, writeData.Length);
                if (size > 0)
                {
                    memory.Write(writeData, 0, size);
                }
                else break;
            }
            stream.Flush();
            stream.Close();
            stream = null;

            return new SqlBytes(memory.ToArray());
        }

[SqlFunction()]
        public static SqlString fn_WriteFile(SqlString path, SqlBytes bytesFile, SqlBoolean isCompressed)
        {
            string returnString = string.Empty;

            try
            {
                //check if the file exists or not
                FileStream myFStream = new FileStream(path.ToString(), FileMode.OpenOrCreate, FileAccess.ReadWrite);

                SqlBytes fileBytes = bytesFile;

                if (isCompressed)
                {
                    fileBytes = fn_Decompress(bytesFile);
                }

                int Length = 256;
                Byte[] buffer = new Byte[Length];

                Stream readStream = fileBytes.Stream;

                int bytesRead = readStream.Read(buffer, 0, Length);

                // write the required bytes
                while (bytesRead > 0)
                {
                    myFStream.Write(buffer, 0, bytesRead);
                    bytesRead = readStream.Read(buffer, 0, Length);
                }

                readStream.Close();
                myFStream.Close();
                returnString = "File is written successfully";
            }
            catch (Exception ex)
            {
                returnString = ex.ToString();
            }

            return new SqlString(returnString);
        }
    }
}

Installation time to your SQL Server, You need to register your assembly(.dll) as well as referenced Assembly to SQL Server

ALTER DATABASE TestAssembly SET TRUSTWORTHY ON
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
CREATE ASSEMBLY [ICSharpCode.SharpZipLib.dll]
                  FROM 'D:\Applications\FileCompressorApp\CLRCompressionFunctions\Deployment\ICSharpCode.SharpZipLib.dll'
                  WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY [CLRCompressionFunctions]
                  FROM 'D:\Applications\FileCompressorApp\CLRCompressionFunctions\Deployment\CLRCompressionFunctions.dll'
                  WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

PERMISSION_SET = SAFE only if you don’t want the assembly accessing external resources such as writing to disk, but in this case the function is used to write into the disk
e.g How about if you want to use/register System.Drawing to your assembly? Yes you can do it by using

CREATE ASSEMBLY [System.Drawing.dll]
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll'
WITH PERMISSION_SET = UNSAFE

You need to enable the CLR on SQL server in order to use your function

Now you need to create your function based on your assembly

CREATE FUNCTION [fn_Compress]           (
                 @uncompressedBytes varbinary(MAX))
            RETURNS varbinary(MAX)
            AS    EXTERNAL NAME CLRCompressionFunctions.[CLRCompressionFunctions.CompressionCore].fn_Compress
GO
CREATE FUNCTION [fn_Decompress]           (
                 @uncompressedBytes varbinary(MAX))
            RETURNS varbinary(MAX)
            AS    EXTERNAL NAME CLRCompressionFunctions.[CLRCompressionFunctions.CompressionCore].fn_Decompress
GO
CREATE FUNCTION [fn_WriteFile]           (
                 @path nvarchar(4000),
				 @bytesFile varbinary(MAX),
				 @bitCompressed bit)
			RETURNS nvarchar(4000)
            AS    EXTERNAL NAME CLRCompressionFunctions.[CLRCompressionFunctions.CompressionCore].fn_WriteFile
GO

Usage, It’s the same as you call a function in SQL Server

SELECT dbo.[fn_Compress](testimage) FROM tblImages
SELECT dbo.[fn_Decompress](imgFileContent) FROM TABLE_NAME
GO
SELECT dbo.[fn_WriteFile]('c:\test.pdf',imgFileContent, 0) FROM TABLE_NAME
GO 

Linq.Binary(SQL Image Data Type) Type to File Stream

I got a column with Data Type of Image in SQL Server. What I would like to do is to omit this data to a FileStream or to a file. I tried to read the data using LINQ and I found the data type detected for that particular column is System.Data.Linq.Binary. I was expecting it to be Byte Data type. So I need to convert the Binary to byte then to File Stream. But I found a simpler way by using “ToArray” properties and cast it back to byte solves my problem.

    foreach (tblExpReceiptFile file in ExpReceiptFactory.tblExpReceiptFileSelect())
            {
                string fileName = file.vcFileName.Replace(" ","_");
                FileStream fileStream = File.Create(DirectoryPath + @"\" + fileName + ".pdf");
                fileStream.Write((byte[])file.imgFileContent.ToArray(), 0, ((byte[])file.imgFileContent.ToArray()).Length);
                fileStream.Close();
            }

SQL Methods in LINQ

I’m trying to implement DateDiff in LINQ. I would like to get all records with the same date and ignoring the timestamp.

Here is my code snippet which doesn’t work

 public int tblExpReceiptFileUniqueID(string chCreateStaffCode, string vcFileName,
                                                long intFileSize, DateTime? sdCreateDate)
        {
            var expReceiptFile = from ef in DataContext.tblExpReceiptFiles orderby ef.intFileID
                                 where ef.chCreateStaffCode == chCreateStaffCode
                                 && ef.vcFileName == vcFileName
                                 && ef.bintFileSize == intFileSize
                                 && ((DateTime)ef.sdCreateDate).Date == sdCreateDate.Value.Date
                                 select ef;

            tblExpReceiptFile expReceiptFileRec = expReceiptFile.First();

            return expReceiptFileRec.intFileID;
        }

I’m trying to cast the column to “DateTime” and use the “Date” property but I got this error
“The Member ‘System.DateTime.Date’ has no supported translation to SQL”

I thought of “DATEDIFF” function in SQL server and I’m trying to get my head around in implementing this using LINQ and I found that we can use a library called “SQLClient”

using System.Data.Linq.SqlClient;

Code Snippet which is working

    public int tblExpReceiptFileUniqueID(string chCreateStaffCode, string vcFileName,
                                                long intFileSize, DateTime? sdCreateDate)
        {
            var expReceiptFile = from ef in DataContext.tblExpReceiptFiles orderby ef.intFileID
                                 where ef.chCreateStaffCode == chCreateStaffCode
                                 && ef.vcFileName == vcFileName
                                 && ef.bintFileSize == intFileSize
                                 && SqlMethods.DateDiffDay(ef.sdCreateDate, sdCreateDate.Value.Date) == 0
                                 select ef;

            tblExpReceiptFile expReceiptFileRec = expReceiptFile.First();

            return expReceiptFileRec.intFileID;
        }

Convert DataTable to CSV Function

This is the class that you can use to create a CSV file from DataTable. Basically what it does is to iterate each column and row in datatable and separate them with comma.

Class/Function:

using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;

namespace BusinessLayer
{
    public class CSVBuilder
    {
        public static string BuildCSVDocument(DataTable table)
        {
            StringBuilder   builder = new StringBuilder();
            DataColumn      col;

            // append header
            for (int index = 0; index < table.Columns.Count; index++)
            {
                col = table.Columns[index];
                builder.AppendFormat("{0},", col.ColumnName);
            }

            builder.AppendLine();

            // append rows
            foreach (DataRow row in table.Rows)
            {
                object[] values = row.ItemArray;

                for (int index = 0; index < values.Length; index++)
                {
                    object value = row[index];
                    string valueString;

                    if (value is DateTime)
                    {
                        valueString = Convert.ToDateTime(value).ToString("dd/MM/yyyy hh:mm");
                    }
                    else if (value is string)
                    {
                        valueString = value.ToString().Replace("'", "`").Replace(",", "");
                    }
                    else
                    {
                        valueString = value.ToString();
                    }

                    builder.AppendFormat("{0},", valueString);
                }

                builder.AppendLine();
            }

            return builder.ToString();
        }

        public static void StreamCSV(DataTable table, HttpResponse response, string fileName)
        {
            // convert the extract to CSV
            string csv = BuildCSVDocument(table);

            // Send it to browser
            response.ClearContent();
            response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
            response.ContentType = "text/csv";

            // Write to the stream
            StreamWriter sw = new StreamWriter(response.OutputStream);
            sw.Write(csv);
            sw.Close();

            response.Flush();
            response.Close();
        }
    }
}

Usage

 private void GenerateMyCsv()
    {
        lblErrorOrderID.Visible = false;

        try
        {
            tdsReports.MissingBatchNumberSelectDataTable dtBatch =
                ReportFactory.GetMissingBatch(txtOrderID.Text);

            //display no results returned message
            if (dtBatch.Rows.Count != 0)
            {
               CSVBuilder.StreamCSV(dtBatch , Response, "MyCSV.csv");
            }

        }
        catch (ApplicationException ex)
        {
            lblErrorOrderID.Text = ex.Message;
            lblErrorOrderID.Visible = true;
        }
        catch (Exception ex)
        {
            lblErrorOrderID.Text = ex.Message;
            lblErrorOrderID.Visible = true;
        }
    }

overriding maximum file upload size in ASP.NET

To override maximum file size that can be uploaded in you website, you need to put this in your web.config

      
        
      

Page 3 of 7

Powered by WordPress & Theme by Anders Norén