List all tables in the database along with their row counts

There is a catalog view that will give us this information. The benefit of this approach is that it uses figures that have already been calculated by SQL Server so it should run very quickly. Here is the script :

SELECT T.name TableName,i.Rows NumberOfRows
FROM sys.tables T
JOIN sys.sysindexes I ON T.OBJECT_ID = I.ID
WHERE indid IN (0,1)
ORDER BY i.Rows DESC,T.name

The script uses the clustered index to source the information (where indid=1). Tables that don’t have a clustered index are stored as a heap but still have a row in sysindexes (where indid=0) containing the rowcount. There is one important caveat, though, the figures may not be entirely accurate ! This is because SQL Server doesn’t always keep this bang up to date. Fortunately there is a DBCC command which updates these figures. Just run this command before the script above to make sure the figures are accurate :
DBCC UPDATEUSAGE(0)

How to determine Index fragmentation and then defragment a database

Index fragmentation is always an issue with big data. I have written some articles before:
How to rebuild all indexes
How to script all indexes
How to examine index fragmentation in SQL Server 2012

The easy way:

SELECT
DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
, OBJECT_NAME(DPS.OBJECT_ID) AS TableName
, SI.NAME AS IndexName
, DPS.INDEX_TYPE_DESC AS IndexType
, DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
, DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID
AND DPS.INDEX_ID = SI.INDID
ORDER BY AVG_FRAGMENTATION_IN_PERCENT DESC

As a rule, all indexes with a fragmentation state over 30% should be defragmented:

— Index neu erstellen
EXEC sp_MSforeachtable @command1=”print ‘Erstelle Index für ?’ ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)”
GO

— Index reorganisieren
EXEC sp_MSforeachtable @command1=”print ‘Reorgansiere Index für ?’ ALTER INDEX ALL ON ? REORGANIZE”
GO

Impossible WPF Part 2: Binding Expressions

The result is the JScriptConverter, which for simplicity of use is both an IMultiValueConverter and an IValueConverter. There are numerous benefits to using JScript here:

  1. It’s part of the framework, so we don’t have to go and write our own scanner/parser/interpreter for a new mini language.
  2. It’s a dynamic language, so it nicely complements the dynamically typed binding system of WPF.
  3. Evaluations in JScript are interpreted not compiled, so we don’t leak memory on every evaluation.

The code for the conveter is once again quite trivial. Because it’s quite difficult to set up a JScript environment for evaluation I instead compile a tiny JScript assembly to wrap the evaluations. This is done once statically and then reused by all instances of the class. Note the TrapExceptions property, if this is set to true any exceptions raised during the conversion will result in null.

Using it is easy. Just instantitate the converter as a resource:

<utils:JScriptConverter x:Key="JScript" TrapExceptions="False" />

And wire it up as a single Binding:

<TextBlock Text="{Binding ElementName=tb1, Path=Text,
    Converter={StaticResource JScript}, 
    ConverterParameter=Int32.Parse(values[0])/100.0"/>

Or as a MultiBinding:

<MultiBinding Converter="{StaticResource JScript}" 
        ConverterParameter=
            "new System.Windows.Thickness(values[0]*0.1/2,values[1]*0.1/2,0,0)">
    <Binding RelativeSource="{RelativeSource TemplatedParent}" 
        Path="ActualWidth" />
    <Binding RelativeSource="{RelativeSource TemplatedParent}" 
        Path="ActualHeight" />
</MultiBinding>

I’m not so happy with the code that enumerates and adds references to all the Assemblies in the current AppDomain, but for the time being it serves its purpose.

Anyway, without further ado (of course this requires a reference to Microsoft.JScript). Cut and paste as you please:

using System;
using System.Windows.Data;
using System.CodeDom.Compiler;
using System.Reflection;

namespace Utils.Avalon
{
    public sealed class JScriptConverter : IMultiValueConverter, IValueConverter
    {
        private delegate object Evaluator(string code, object[] values);
        private static Evaluator evaluator;

        static JScriptConverter()
        {
            string source = 
                @"import System; 

                class Eval
                {
                    public function Evaluate(code : String, values : Object[]) : Object
                    {
                        return eval(code);
                    }
                }";

            CompilerParameters cp = new CompilerParameters();
            cp.GenerateInMemory = true;
            foreach (Assembly assembly in AppDomain.CurrentDomain.GetAssemblies())
                if (System.IO.File.Exists(assembly.Location))
                    cp.ReferencedAssemblies.Add(assembly.Location);

            CompilerResults results = (new Microsoft.JScript.JScriptCodeProvider())
                .CompileAssemblyFromSource(cp, source);

            Assembly result = results.CompiledAssembly;

            Type eval = result.GetType("Eval");

            evaluator = (Delegate.CreateDelegate(
                typeof(Evaluator),
                Activator.CreateInstance(eval),
                "Evaluate") as Evaluator);
        }

        private bool trap = false;
        public bool TrapExceptions
        {
            get { return this.trap; }
            set { this.trap = true; }
        }

        public object Convert(object[] values, System.Type targetType,
            object parameter, System.Globalization.CultureInfo culture)
        {
            try
            {
                return evaluator(parameter.ToString(), values);
            }
            catch
            {
                if (trap)
                    return null;
                else
                    throw;
            }
        }

        public object Convert(object value, Type targetType, 
            object parameter, System.Globalization.CultureInfo culture)
        {
            return Convert(new object[] { value }, targetType, parameter, culture);
        }


        public object[] ConvertBack(object value, System.Type[] targetTypes,
            object parameter, System.Globalization.CultureInfo culture)
        {
            throw new System.NotSupportedException();
        }

        public object ConvertBack(object value, Type targetType,
            object parameter, System.Globalization.CultureInfo culture)
        {
            throw new System.NotSupportedException();
        }
    }
}

How to post multipart data in C# via WebRequest

This is a small code sample showing how you can post an image for example to a http server REST API endpoint.

 /// 
    /// Class to show how to do a MultiPartRequest
    /// For more information about MultiPart requests, look for this link : https://code.msdn.microsoft.com/windowsapps/WP8-Post-Multipart-Data-62fbbf72 
    /// 
    class MultipartRequest
    {
        #region Constants

        /// 
        /// The encoding in UTF8
        /// 
        private static readonly Encoding Encoding = Encoding.UTF8;

        /// 
        /// Guid of the cardholder to set the picture to.
        /// 
        private readonly Guid m_cardholderGuid;

        /// 
        /// The image to send.
        /// 
        private readonly Image m_image;

        /// 
        /// The Image Name, extracted from the Path to the image.
        /// 
        private readonly string m_imageName;

        /// 
        /// The server uri. Does not contain any query.
        /// 
        private readonly string m_uriServer;

        /// 
        /// The webSdkStudioWindow
        /// 
        private readonly WebSdkStudioWindow m_webSdkStudioWindow;

        #endregion

        #region Nested Classes and Structures

        public class FileParameter
        {
            #region Properties

            public string ContentType { get; set; }

            public byte[] File { get; set; }

            public string FileName { get; set; }

            #endregion

            #region Constructors

            public FileParameter(byte[] file) : this(file, null) { }

            public FileParameter(byte[] file, string filename) : this(file, filename, null) { }

            public FileParameter(byte[] file, string filename, string contenttype)
            {
                File = file;
                FileName = filename;
                ContentType = contenttype;
            }

            #endregion
        }

        #endregion

        #region Constructors

        public MultipartRequest(string image, string uriServer, Guid cardholderGuid, WebSdkStudioWindow webSdkStudioWindow)
        {
            m_image = Image.FromFile(image);
            m_uriServer = uriServer;
            string[] imageSplit = image.Split('\\');
            m_imageName = imageSplit.Last();
            m_cardholderGuid = cardholderGuid;
            m_webSdkStudioWindow = webSdkStudioWindow;
        }

        #endregion

        #region Public Methods

        public void UploadData()
        {
            // Generate post objects
            Dictionary postParameters = new Dictionary();

            //String parameters
            string myPicture = ImageToBase64String(m_image);
            byte[] myPictureArray = Encoding.GetBytes(myPicture);

            postParameters.Add("$myPicture", new FileParameter(myPictureArray, m_imageName, "image/png"));

            MultipartFormDataPost(
                m_uriServer + "entity?q=entity=" + m_cardholderGuid + ",Picture=$myPicture",
                postParameters);


        }

        #endregion

        #region Private Methods

        private static byte[] GetMultipartFormData(Dictionary postParameters, string boundary)
        {

            Stream formDataStream = new MemoryStream();
            bool needsClrf = false;
            try
            {
                foreach (var param in postParameters)
                {
                    // Thanks to feedback from commenters, add a CRLF to allow multiple parameters to be added.
                    // Skip it on the first parameter, add it to subsequent parameters.
                    if (needsClrf)
                        formDataStream.Write(Encoding.GetBytes(Environment.NewLine), 0, Encoding.GetByteCount(Environment.NewLine));

                    needsClrf = true;

                    FileParameter value = param.Value as FileParameter;
                    if (value != null)
                    {
                        FileParameter fileToUpload = value;

                        // Add just the first part of this param, since we will write the file data directly to the Stream
                        string header = string.Format(
                            "--{0}Content-Disposition: form-data; name=\"{1}\"; filename=\"{2}\"{3}Content-Type: {4}",
                            new StringBuilder(boundary).Append(Environment.NewLine),
                            param.Key,
                            fileToUpload.FileName ?? param.Key,
                            Environment.NewLine,
                            new StringBuilder(fileToUpload.ContentType ?? "application/octet-stream").Append(Environment.NewLine).Append(Environment.NewLine));

                        formDataStream.Write(Encoding.GetBytes(header), 0, Encoding.GetByteCount(header));

                        // Write the file data directly to the Stream, rather than serializing it to a string.
                        formDataStream.Write(fileToUpload.File, 0, fileToUpload.File.Length);
                    }
                    else
                    {
                        string postData = string.Format("--{0}Content-Disposition: form-data; name=\"{1}\"{2}",
                            new StringBuilder(boundary).Append(Environment.NewLine),
                            param.Key,
                            new StringBuilder(Environment.NewLine).Append(Environment.NewLine).Append(param.Value));
                        formDataStream.Write(Encoding.GetBytes(postData), 0, Encoding.GetByteCount(postData));
                    }
                }

                // Add the end of the request.  Start with a newline
                string footer = new StringBuilder(Environment.NewLine).Append("--").Append(boundary).Append("--").Append(Environment.NewLine).ToString();
                formDataStream.Write(Encoding.GetBytes(footer), 0, Encoding.GetByteCount(footer));
            }
            catch (Exception ex)
            {
                throw new Exception("Network Issue : ", ex);
            }
            // Dump the Stream into a byte[]
            formDataStream.Position = 0;
            byte[] formData = new byte[formDataStream.Length];
            formDataStream.Read(formData, 0, formData.Length);
            formDataStream.Close();
            return formData;
        }

        private static string ImageToBase64String(Image image)
        {
            byte[] imageBytes = ImageToByteArray(image);
            return Convert.ToBase64String(imageBytes);
        }

        private static byte[] ImageToByteArray(Image image)
        {
            var ms = new MemoryStream();
            image.Save(ms, ImageFormat.Png);
            return ms.ToArray();
        }

        private void MultipartFormDataPost(string url, Dictionary postParameters)
        {
            string formDataBoundary = string.Format("----------{0:N}", Guid.NewGuid());
            string contentType = "multipart/form-data; boundary=" + formDataBoundary;

            byte[] formData = GetMultipartFormData(postParameters, formDataBoundary);

            PostForm(url, contentType, formData);
        }

        private void PostForm(string url, string contentType, byte[] formData)
        {
            Request request = new Request("POST", url);
            HttpWebRequest httpWebRequest = WebRequest.Create(request.Url) as HttpWebRequest;

            if (httpWebRequest == null)
            {
                throw new NullReferenceException("request is not a http request");
            }

            // Set up the request properties.
            httpWebRequest.Method = request.HttpMethod;
            httpWebRequest.ContentType = contentType;
            httpWebRequest.CookieContainer = new CookieContainer();
            httpWebRequest.ContentLength = formData.Length;
            httpWebRequest.Credentials = request.WebRequestCredentials;

            httpWebRequest.BeginGetRequestStream(result =>
            {
                try
                {
                    HttpWebRequest webRequest = (HttpWebRequest)result.AsyncState;
                    using (Stream requestStream = webRequest.EndGetRequestStream(result))
                    {
                        requestStream.Write(formData, 0, formData.Length);
                        requestStream.Close();
                    }
                    webRequest.BeginGetResponse(ar =>
                    {
                        try
                        {
                            WebResponse response = webRequest.EndGetResponse(ar);
                            Stream responseStream = response.GetResponseStream();
                            if (responseStream != null)
                            {
                                using (StreamReader sr = new StreamReader(responseStream))
                                {
                                    request.Response = sr.ReadToEnd();
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            request.Response = ex.Message;
                        }
                        m_webSdkStudioWindow.OnMultipartResponse(request);
                    }, null);
                }
                catch (Exception ex)
                {
                    request.Response = ex.Message;
                    m_webSdkStudioWindow.OnMultipartResponse(request);
                }
            }, httpWebRequest);

        }

        #endregion
    }

    #endregion
}

How to find the SSL Certificate in Chrome

If you ever wanted to know how to find the SSL certificate information in a browser like Chrome, look no further!
CertificateBefore and up to around Google Chrome version 55, I could view the details of the certificate a website was using by clicking on the green lock icon in the address bar.
Now, you can find this information by going to the Three Dots Menu on the Right of your Chrome Window -> More Tools -> Developer Tools, then click on the Security Tab. This will give you a Security Overview with a “View Certificate Button”.
View Cert
CertificateInfo

The Beauty of Closures

What are closures?

To put it very simply, closures allow you to encapsulate some behaviour, pass it around like any other object, and still have access to the context in which they were first declared. This allows you to separate out control structures, logical operators etc from the details of how they’re going to be used. The ability to access the original context is what separates closures from normal objects, although closure implementations typically achieve this using normal objects and compiler trickery. Continue reading “The Beauty of Closures”

Failed to listen on prefix http://localhost:portnumber because it conflicts with an existing registration on the machine

When creating a TCP/IP server connection on a Windows based platform you can specify a port number ranging from 1000 to 65535. It would seem unlikely that two applications executing at the same time will both attempt to open the same port number, in reality it happens quite often. It is advisable to first determine if a port is already in use before attempting to start a server connection listening on that port.

 

Active Tcp Listeners

The System.Net.NetworkInformation namespace defines an IPGlobalProperties class. Using IPGlobalProperties we can determine the IPEndPoint every server connection listens on for incoming connections. Listed below is a code snippet detailing the PortInUse method.

 public static bool PortInUse(int  port)
 {
     bool inUse = false;
             
     IPGlobalProperties ipProperties = IPGlobalProperties.GetIPGlobalProperties();
     IPEndPoint [] ipEndPoints = ipProperties.GetActiveTcpListeners();

 
     foreach(IPEndPoint endPoint in ipEndPoints)
     {
         if  (endPoint.Port == port)
         {
             inUse = true;
             break;
         }
     }

 
     return  inUse;
 }

or if you want to use Linq expressions:

private bool PortInUse(ushort servicePort)
        {
            IPGlobalProperties ipProperties = IPGlobalProperties.GetIPGlobalProperties();
            IPEndPoint[] ipEndPoints = ipProperties.GetActiveTcpListeners();

            return ipEndPoints.Any(endpoint => endpoint.Port == servicePort);
        }

The PortInUse method determines all active server connections, then proceeds to iterate an Array of IPEndPoint objects comparing port numbers to the method’s only parameter.

The Implementation
The PortInUse method is implemented in a Console based application. First the sample source starts up an instance of the HttpListner class on port 8080. The HttpListner definition is followed by determining if port 8080 is in fact being used.

 static  void  Main(string [] args)
 {
     HttpListener  httpListner = new  HttpListener ();
     httpListner.Prefixes.Add("http://*:8080/" );
     httpListner.Start();

 
     Console .WriteLine("Port: 8080 status: "  + (PortInUse(8080) ? "in use" : "not in use" ));

 
     Console .ReadKey();

 
     httpListner.Close();
 }