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

Author: fransiscuss Page 12 of 19

Send message in Twitter using .NET

This code snippet is used to wrap Twitter API to send message. It’s not a complete Twitter wrapper but It can send message using Twitter easily. There are two overloaded constructors where both of them requires user name and password to be passed and proxy server is used as the overloaded parameter.

using System;
using System.IO;
using System.Net;
using System.Xml;
using System.Web;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Helpers
{
    public class TwitterHelpers
    {
        private string _twitterJsonUrl = "http://twitter.com/statuses/update.json";

        public string TwitterJsonUrl
        {
            get { return _twitterJsonUrl; }
            set { _twitterJsonUrl = value; }
        }

        private string _twitterUser = string.Empty;

        public string TwitterUser
        {
            get { return _twitterUser; }
            set { _twitterUser = value; }
        }

        private string _twitterPass = string.Empty;

        public string TwitterPass
        {
            get { return _twitterPass; }
            set { _twitterPass = value; }
        }

        private string _proxyServer = string.Empty;

        public string ProxyServer
        {
            get { return _proxyServer; }
            set { _proxyServer = value; }
        }

        public string SendTwitterMessage(string message)
        {
            try
            {
                HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(TwitterJsonUrl);
                System.Net.ServicePointManager.Expect100Continue = false;

                string post = string.Empty;
                using (TextWriter writer = new StringWriter())
                {
                    writer.Write("status={0}", HttpUtility.UrlEncode(message.Substring(0,140)));
                    post = writer.ToString();
                }

                SetRequestParams(request);

                request.Credentials = new NetworkCredential(TwitterUser, TwitterPass);

                using (Stream requestStream = request.GetRequestStream())
                {
                    using (StreamWriter writer = new StreamWriter(requestStream))
                    {
                        writer.Write(post);
                    }
                }

                WebResponse response = request.GetResponse();
                string content;

                using (Stream responseStream = response.GetResponseStream())
                {
                    using (StreamReader reader = new StreamReader(responseStream))
                    {
                        content = reader.ReadToEnd();
                    }
                }

                return content;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void SetRequestParams(HttpWebRequest request)
        {
            request.Timeout = 500000;
            request.Method = "POST";
            request.ContentType = "application/x-www-form-urlencoded";
            request.UserAgent = "My Twitter Application";

            if (!string.IsNullOrEmpty(_proxyServer))
            {
                request.Proxy = new WebProxy(_proxyServer, false);
            }

        }

        public TwitterHelpers(string userName, string userPassword, string proxyServer)
        {
            _twitterUser = userName;
            _twitterPass = userPassword;
            _proxyServer = proxyServer;
        }

        public TwitterHelpers(string userName, string userPassword)
        {
            _twitterUser = userName;
            _twitterPass = userPassword;
        }

    }
}

Usage:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleAppTest
{
    class Program
    {
        private static TwitterHelpers _twitterHelpers = null;

        private static TwitterHelpers TwitterHelpers
        {
            get
            {
                if (_twitterHelpers == null)
                {
                    _twitterHelpers = new TwitterHelpers("twitteruser", "twitterpassword");
                }

                return _twitterHelpers;
            }
        }

        static void Main(string[] args)
        {
            TwitterHelpers.SendTwitterMessage("Hello World!!");
            Console.WriteLine("Press any key to continue...");
            Console.ReadKey();
        }
    }
}

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.

TRY….CATCH Rollback Transaction In SQL Server 2005

This feature has been sometime in SQL Server 2005 in SQL Server 2000 you normally use @@TRANCOUNT to detect any exception but now in SQL Server 2005 you can use try catch block.

In this snippet, there are 2 INSERT statement and 1 UPDATE statement. I’m expecting when there is any failure (e.g the UPDATE statement fails) then all the preceding INSERT/UPDATE/DELETE within the TRANSACTION block will be canceled

e.g

BEGIN TRY
BEGIN TRANSACTION transABC

INSERT INTO TABLEA (col1,col2,col3) VALUES ('a','b','c')
INSERT INTO TABLEB (col1,col2,col3) VALUES ('a','b','c')

UPDATE TABLEA SET col2='abcde' WHERE col1 = 'a'

COMMIT TRANSACTION transABC
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION transABC --RollBack in case of Error

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

Index in Table Variable

One biggest advantage of table variable for me is you can’t put index on the columns. I was having a problem where doing self join in table variable (it has 1000+ records) is taking really really long time. One way I found to optimize it is to add primary key assuming every single record is unique on the table variable. The performance improvement is really significant by adding the primary key on table variable. And by changing table variable to temp table, it improves the performance more than by twice

CREATE TABLE #dtblJobsBillDates
(
		chProcessID			CHAR(03)		COLLATE database_default NOT NULL,
		intAssgnmtID		INTEGER									 NOT NULL,
		chBillOfficeCode	CHAR(03)		COLLATE database_default NOT NULL,
		chBillEntityCode	CHAR(03)		COLLATE database_default NOT NULL,
		chBillNo			CHAR(08)		COLLATE database_default NOT NULL,
		PRIMARY KEY (intAssgnmtID, chBillNo, chProcessID, chBillOfficeCode, chBillEntityCode)
)

Disabling Time Synchronization of Virtual PC image

I was having the problem where I tried to change the date and time on my VPC environment, it keeps resetting it back. To resolve the issue, you need to edit the *.VMC file and add the highlighted section below, resave the file and launch your VPC.

<integration>
    <microsoft>
        <mouse>
            <allow type=”boolean”>true</allow>
        </mouse>
        <components>
            <host_time_sync>
                <enabled type=”boolean”>false</enabled>
            </host_time_sync>
        </components>

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 

Self Inner Join with Group By in SQL Server

This is the case:
I have a table and basically this table has many redundancy. The solution is to create a link table and this table will contains record association. So what I need to do is to get all the ID’s Populated to the link table and link it to a single unique record on the original table. The logic that I used is to get the value from the column based on the lowest ID.

INSERT INTO tblExpReceiptFileAssociation(intExpenseDtlId, intFileID)
	SELECT te.intExpenseDtlID, ta.LinkFileID
	FROM tblExpReceiptFile te
	INNER JOIN
		(
			SELECT vcFileName, bintFileSize, chCreateStaffCode,
				   CONVERT(VARCHAR,sdCreateDate,101) as DateCreated,
				   MIN(intFileID) as LinkFileID
			FROM
				tblExpReceiptFile
			GROUP BY
				vcFileName, bintFileSize,
				chCreateStaffCode, CONVERT(VARCHAR,sdCreateDate,101)
		) ta
	ON
		te.vcFileName = ta.vcFileName
	AND
		te.bintFileSize = ta.bintFileSize
	AND
		te.chCreateStaffCode = ta.chCreateStaffCode
	AND
		CONVERT(VARCHAR,te.sdCreateDate,101) = ta.DateCreated

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;
        }

Check Existence of temp table in memory

How to check whether the temp table is exists on the memory or not?The reason why you need this is because your stored procedure can throw the error when you try to drop a temp table which is not exists anymore on the memory. The best practice to drop a temp table is normally to check the existence of the table on the memory then we drop the table if it is exists

--Drop the table after usage
IF object_id('tempdb..#tmpStandardCostRate') IS NOT NULL
BEGIN
   DROP TABLE #tmpStandardCostRate
END

Page 12 of 19

Powered by WordPress & Theme by Anders Norén