Vaibhav Gaikwad
Microsoft .NET developer
Bla Code! Bla Code!

SQL Server: Moving DATA and LOG file to different location

The default location of the "data" and "log" files for SQL Server database is "C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA" for version 2016 unless it is set differently during the installation process.

The core issue in discussion here is the copy the database files from one drive to another in case of a scenario where you current drive is getting exhausted. In my case the C: drive was running of space and I wanted to move the files to F: drive

So start the SQL Management Studio and fire the following query to understand the current file locations for a specific DB.

SELECT name,physical_name FROM <database_name>.sys.database_files

Once you have verified the locations, just copy the .mdf and .ldf files from the location listed in the output of above query and move to the desired drive (location). At this instance you have the database DATA nd LOG files in 2 drives, and you want it at just the new location. So before you are ready to delete the files from the old location first you must change the registry of those file at Server level. You can edit the file locations using the following query command

ALTER DATABASE <database_name>
MODIFY FILE (NAME =
<database_name_file_name_for_mdf>, FILENAME = 'F:\DATA\my_db.mdf');

ALTER DATABASE
<database_name> MODIFY FILE (NAME = <database_name_file_name_for_ldf>_Log, FILENAME = 'F:\DATA\my_db.ldf');

Note: <value> are used as placeholders, do replace them with your respective values

The final part, is the take the DB offline delete the files in the old location and bring the DB online again so that it will start using the files from new location.






MS SQL - bcp to export varbinary to image file

I don't do much SQL in regular day to day basis but when it comes to it then it gets really exciting. Here is one of the odd days where I wanted to push out a image file from SQL which was sorted as varbinary(MAX) in database. As you all know that bcp is a very handy utility when it comes to dumping data out. So, I made that as a first choice but soon realized it was difficult to handle varbinary with the default arguments. Reading the internet here is what I could learn...

You need a format (.fmt) file for such an export. To generate the format file you need first go to the command prompt and perform the following:

D:\>bcp "select top 1 annotation from [DEMO_0]..[MasterCompany_Demographics_files]"  queryout d:\test.png -T

Enter the file storage type of field annotation [varbinary(max)]:{press enter}
Enter prefix-length of field annotation [8]: 0
Enter length of field annotation [0]:{press enter}
Enter field terminator [none]:{press enter}

Do you want to save this format information in a file? [Y/n] y {press enter}
Host filename [bcp.fmt]: annotation.fmt {press enter}

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (1000.00 rows per sec.)


This will help you to generate your format file which then can be used to export out the images easily.

D:\>bcp "select top 1 annotation from [DEMO_0]..[MasterCompany_Demographics_files]"  queryout "d:\test.png" -T -f annotation.fmt

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 15     Average : (66.67 rows per sec.)

I hope this helps someone...


HTTP logging using CustomTraceListener from Enterprise library

This was more like a debugging related topic for me. The overall knowledge is helpful to understand how the class CustomTraceListener can be useful to build you own tracing mechanism.

My use-case was the trace all the HTTP requests in and out of my application. One way to do that was using HttpModule but as I never intended to do any re-routing or change in processing etc. I did not find using HttpModule was needed. I was looking for a more silent way of doing things on the background.

So here it is:

1. You need the have Enterprise library for logging
https://www.nuget.org/packages/EnterpriseLibrary.Logging/

2. For writing to file better use the log4net 

Develop a simple library project with the follwing code inside it

using log4net;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Logging;
using Microsoft.Practices.EnterpriseLibrary.Logging.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners;
using System;
using System.Collections.Generic;
using System.Diagnostics;


namespace TraceLib
{
    //[ConfigurationElementType(typeof(CustomTraceListenerData))]
    public class DebugTraceListener : CustomTraceListener
    {
        private static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        public DebugTraceListener()
            : base()
        { }
        public override void Write(string message)
        {
            log.Debug(message);
        }

        public override void WriteLine(string message)
        {
            log.Debug(message);
        }

        public override void TraceData(TraceEventCache eventCache, string source, TraceEventType eventType, int id, object data)
        {
            if (data is LogEntry && this.Formatter != null)
            {
                this.WriteLine(this.Formatter.Format(data as LogEntry));
            }
            else
            {
                this.WriteLine(data.ToString());
            }
        }

    }
}


This will be your tracing code, build it and reference it your web-project like a normal "add reference" 

The configuration to add within your web.config is as follows:
<configuration>
......
  <system.diagnostics>
    <sharedListeners>
      <add name="MyTrace" type="TraceLib.DebugTraceListener,TraceLib" />      
    </sharedListeners>
    <sources>
      <source name="System.Net" >
        <listeners>
          <add name="MyTrace"/>         
        </listeners>
      </source>
    </sources>    
    <switches>
      <add name="System.Net" value="Information" />
    </switches>
  </system.diagnostics>
</configuration>

Mostly your done, configure you log4net part in the web.config so that you can put the data to some file.

  <log4net debug="true">
    <appender name="RollingLogFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="logs/rolling.log" />
      <appendToFile value="true" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="10" />
      <maximumFileSize value="10MB" />
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%-5p %d %5rms %-22.22c{1} %-18.18M - %m%n" />
      </layout>
    </appender>
    <root>
      <level value="DEBUG" />
      <appender-ref ref="RollingLogFileAppender" />
    </root>
  </log4net>

Run the web-app and fire a web-service or some code that triggers an http call. You should be able to see all the relevant data inside the "logs/rolling.log" file inside of your virtual directory.

Hope this helps to make some more experimenting towards tracing to database etc. Cheers!


Need a class at runtime - .NET 4.5 helps

Ever came across a need to create class definitions at run-time based on some meta definitions coming from some external source. Well, we will not care about where those meta definition about the class comes from, but the problem at hand is the create a class definition at run-time. 

.NET evolved a lot and with 4.5 there are pretty interesting things to work out. Two of them are:
1. Keyword "dynamic"
2. DynamicObject class from System.Dynamic

Lets assume that our meta definitions on the class properties are stored in a Dictionary and we want to use it to create a new class at run-time and bind those properties to it, then this is how it can be done.

/* code for NewClass */
    public sealed class NewClass : DynamicObject
    {
        private readonly Dictionary<string, object> properties;

        public NewClass(Dictionary<string, object> properties)
        {
            this.properties = properties;
        }

        public override IEnumerable<string> GetDynamicMemberNames()
        {
            return this.properties.Keys;
        }

        public override bool TryGetMember(GetMemberBinder binder, out object result)
        {
            if (this.properties.ContainsKey(binder.Name))
            {
                result = this.properties[binder.Name];
                return true;
            }
            else
            {
                result = null;
                return false;
            }
        }
        public override bool TrySetMember(SetMemberBinder binder, object value)
        {
            if (this.properties.ContainsKey(binder.Name))
            {
                this.properties[binder.Name] = value;
                return true;
            }
            else
            {
                return false;
            }
        }
    }
 
/* code end here */

Let see how to utilize this one in the simple program. Lets make a class Employee with properties Name, Code, Telephone


/* Program code starts here */
class Program
    {
        static void Main(string[] args)
        {
            dynamic emp = new NewClass(new Dictionary<string, object>() { {"Code" , 1} , {"Name", "John"} , {Telephone, "3636352"} });
   Console.WriteLine(emp.Code);
   Console.WriteLine(emp.Name);
   Console.WriteLine(emp.Telephone);
           Console.ReadLine();
        }
    }
/* code ends here */

There could be numerous applications to this concept. Some people many think to build the whole middle ware using this because now your entities can modify automatically when the table structure changes.  

Feel free to comment and question.


IIS 6 meta reading in .NET

First I'll talk about what made me write this code. I had been asked to set MIME types on IIS 6 web-site using web.config. I read and read for couple of hours but did not reach any good article on it. During my reading I came of across couple of article which talked about how we can do it programmatically using windows script (vbs) etc...

So finally I decided to make a utility for myself and here's the idea for all...

First, you need some extra references to:



This will come from COM



Most of the things you can straight away do with System.DirectoryServices namespace, the above mentioned COM is needed for adding any MIME definition (MimeMapClass from IISOle)

The IIS 6 metabase can be explored via "IIS://localhost/W3SVC" path.
DirectoryEntry iis6Entry = new DirectoryEntry(path);

Now you can just iterate through all the child entries of the above defined entry.

foreach (DirectoryEntry child in iis6Entry.Children)
{
          if (child.SchemaClassName.Equals("IIsWebServer"))
          {
                    // this child is a web-site entry on your IIS 6 : call it as "siteEntry"
           }
 }

On IIS 6 all the web-site entries have a unique number assigned, and you virtual directory name is actually an attribute called as "ServerComment". So if you have a web-site called "homesite" it will have path some what like this "IIS://localhost/W3SVC/1164071614"

you can check that using DirectoryEntry.Path property. Now once you have access to the the web-site's directory entry, you need to get acess to "root" just by making a new DirectoryEntry.

DirectoryEntry siteRootEntry = new DirectoryEntry(siteEntry.Path + "/root");

siteRootEntry will have all the details about the web-site MIME definitions inside a Property element called as "MimeMap".

PropertyValueCollection mimeProps = siteRootEntry.Properties["MimeMap"];

Now to add you MIME definitions:

 if (mimeProps != null)
{
                //This example requires a reference to the Active DS IIS Namespace Provider in Visual Studio .NET. This reference enables you to use the IISOle namespace to access the IISMimeType class.
                IISOle.MimeMapClass newObj = new IISOle.MimeMapClass();
                newObj.Extension = ".webm";
                newObj.MimeType = "webm/video";
                mimeProps.Add(newObj);
                siteRootEntry.CommitChanges(); // this call with actually save the changes to the web-site
}

That's it for this article. Things which I missed out to test are:
        1. Does the app-pool recycles after I edit the MIME entries
        2. What if I push garbage values in the MIME entries
        3. Exception scnenarios

I hope this becomes useful to someone :)




Remove full path of PDB file from C# dll or exe. (/pdbaltpath in c#)

I came across a challenge last week, wherein I was asked to remove the path information from the dll files which were build inside my project.

For those who don’t have an idea about what I am talking about, here is a quick introduction. The dll/exe files which are build by Visual Studio contains the Win32 PE header information, and this is true for all the dll and exe on Win32 platforms. Read about it at (http://en.wikipedia.org/wiki/Portable_Executable)

To check the headers in your dll/exe files you can use the dumpbin tool.

dempbin


So you can see the full path of the PDB file is embedded inside the DLL file, which is the point of concern as you might not want to expose private information in your releases. Fortunately this can be avoided by using the linker argument “/PDBALTPATH:%_PDB%” which just embeds the filename of PDB instead of full path. Unfortunately, this is true only for VC/C++ and there is no way to use this method when you are building C# assemblies.

So I started reading the internet to find a quick hack for it. Some suggested rebase.exe and some suggested “binplace.exe”. Binplace comes with Microsoft DDK. Nothing worked for me, and hence you are reading this post Smile

I decided to strip out the file path information myself, and so started reading on PE header format. I saw some interesting article already written on the same problem.

http://www.debuginfo.com/examples/debugdir.html

I used the code provided in above article and tweaked it to make it more user friendly. My code is variant of DebugDir.cpp written by  Oleg Starodumov.

I renamed the build to have a output called “DebugInfo.exe” and which has 2 arguments.

Usage of DebugInfo.exe:

1. Just to list the debug directories

c:\DebugInfo {filename.(exe|pdb)}

debuginfo-0

2. To remove complete PDB file information

c:\DebugInfo {filename.(exe|pdb)} clean

debuginfo-1

3. To remove just the path information of PDB and retain the filename

c:\DebugInfo {filename.(exe|pdb)} clean-path

debuginfo-2

You can verify the headers again using dumpbin.exe. Find the source code from the SourceForge site listed below. It is compiled and build using Visual Studio 2010 (VC++ Win32 Console application), add imagehlp.lib to your linker.

 

SourceForge: https://sourceforge.net/projects/debugdir/

Have fun!!!

-Vaibhav Gaikwad



User32's SetForegroundWindow() API in C#

If there occurs a need to check if some process is already running and then bringing that process' main window in front then .NET does not helps 100%.

Using the System.Diagnositics.Process class to find out a already running process is a easy task but to bring that process' main window in front is a difficult goal.

Here's the code to get the already running process (e.g. Notepad) and then bringing it to front, using the native API from User32.dll >> SetForegroundWindow(int WindowHandle)

[System.Runtime.InteropServices.

class Program
{
static void Main(string[] args)
{
 System.Diagnostics.Process[] p = System.Diagnostics.Process.GetProcessesByName("notepad");
   if (p.Length > 0)
   {
     SetForegroundWindow(p[0].MainWindowHandle);
   }
 }
}

DllImport("user32.dll")]
public static extern bool SetForegroundWindow(IntPtr hWnd);

-Vaibhav Gaikwad



Flashing your window, getting attention.

This is a simple example of using Win32 api in .NET. I'll use the FlashWindow() API from user32.dll so as to make the window go flashing and get attention.

Take a simple Windows form and add a button to it. Add a reference to System.Runtime.InteropServices namespace. And heres the code.

// --------- code -----------
[DllImport("user32")]
public static extern int FlashWindow(int hWnd, int bInvert);

public partial class Form1 : Form
{
   public Form1()
   {
      InitializeComponent();
   }

   private void button1_Click(object sender, EventArgs e)
   {
      FlashWindow(this.Handle.ToInt32(), 1);
   }
} // -------- code ends

This was nice and simple for the start.

-Vaibhav Gaikwad



Where to start from ?

Thinking of where to start from with millions of topics in mind. I will dedicate this blog to unmanaged code. As a .NET programmer, I will be illustrating use of Win32 native code in managed .NET code.

Right form the start, I was really interested in exploring the Interop framework of .NET.

So this blog will be a mix of both, unmanged in managed world.

-Vaibhav Gaikwad