Setting Device Information in MCI

هذه المقالة متوفرة أيضا باللغة العربية، اقرأها هنا.

Interested in MCI multimedia processing? First check this article out if you didn’t:

Creating a Sound Recorder in C and C#

After we received your feedbacks and comments about th.e article, we decided to add a small appendix to the end of the article about setting information (volume, channel, sampling rate, etc.) to a MCI device (a Waveform device of course.)

Like anything else in MCI, you can set device information using a MCI command (string/numeric), and this time it’s the MCI_SET command.

This command is used to set information about a specific device. This command requires an input parameter of the MCI_SET_PARMS structure. However, that input parameter might have extended members for specific devices. Because we are concentrating of Waveform devices, so we are going to use the MCI_WAVE_SET_PARMS structure that contains the extended members for our device and is defined as following:

typedef struct {
    DWORD_PTR dwCallback;
    DWORD     dwTimeFormat;
    DWORD     dwAudio;
    UINT      wInput;
    UINT      wOutput;
    WORD      wFormatTag;
    WORD      wReserved2;
    WORD      nChannels;
    WORD      wReserved3;
    DWORD     nSamplesPerSec;
    DWORD     nAvgBytesPerSec;
    WORD      nBlockAlign;
    WORD      wReserved4;
    WORD      wBitsPerSample;
    WORD      wReserved5;
} MCI_WAVE_SET_PARMS;

This structure contains all and every little piece of information that can be set to a device. I expect that you read the main article and you are already familiar with members like dwCallback (other members are self-explanatory) that we have talked about many times, and you are fine too with function calls and setting up input parameters, so I won’t get into the discussion of the structure or how you are going to use that command. However, if you need more help setting up the input parameters for the structure, you should take a brief look at the MCI_WAVE_SET_PARMS Structure documentation in the MSDN.

As you know, the MCI_WAVE_SET_PARMS unmanaged structure can be marshaled in C# as following:

[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Ansi)]
public struct MCI_WAVE_SET_PARMS
{
    public IntPtr dwCallback;
    public uint     dwTimeFormat;
    public uint     dwAudio;
    public uint      wInput;
    public uint      wOutput;
    public ushort      wFormatTag;
    public ushort      wReserved2;
    public ushort      nChannels;
    public ushort      wReserved3;
    public uint     nSamplesPerSec;
    public uint     nAvgBytesPerSec;
    public ushort      nBlockAlign;
    public ushort      wReserved4;
    public ushort      wBitsPerSample;
    public ushort      wReserved5;
}

Congratulations! You did set the device information! So how to get them back?

This can be done through the MCI_STATUS (discussed earlier) by setting up the MCI_STATUS_ITEM flag and setting the query item to the required information you need to query about (like MCI_DGV_STATUS_VOLUME to query about volume.)

More about the MCI_STATUS command can be found in the MSDN documentation.

Why you receive a System.Security.SecurityException exception!

Have you ever received an exception like this:

System.Security.SecurityException: xxx.

This exception is thrown whenever you try to access a protected resource while your code doesn’t have the required permissions.

.NET Framework helps protecting computer system from malicious code and software by using a nice feature of CLR called Code Access Security (simply, CAS.) This feature manages the permissions gained by .NET applications, allows code from known vendors and code running from secure locations to run seemlessly without interruption of the CLR, while holds other applications from unknown vendors and applications running from insecure locations (like network shares) to run with restrictions and limitation of access to shared system resources.

A great job from the author is done in the following article:

Understanding .NET Code Access Security

Manually performing a garbage collection!

I was asked whether it’s efficient to perform a manual garbage collection using GC.Collect() or not!

Actually, it’s better to just let the CLR handle it. However, there’re few circumstances where manually performing garbage collection is preferred, such as if some non-recurring event has just happened and this event is highly likely to have caused a lot of old objects to die.

More details are available in Rico Mariani’s…

Have a nice weekend!

Serialization vs. Marshaling

هذه المقالة متوفرة أيضا باللغة العربية، اقرأها هنا.

Overview

Are you somewhat confused between Serialization and Marshaling? This writing would break this confusion up, it would give you a basic understanding of the process of Serialization and the process of Marshaling, and how you can get the most out of each.

Serialization

Serialization is the process of converting a data structure or object into a sequence of bits so that it can be stored in a file, a memory buffer, or transmitted across a network connection to be “resurrected” later in the same or another computer environment. And this sequence of bits can be of any format the user chooses; however, they are usually formed as XML or binary.

Serialization comes in many forms in .NET Framework, it can be observed in ADO.NET, Web services, WCF services, Remoting, and others.

For example, calling the WriteXml() function of a DataSet serializes this DataSet into a XML file.

    ds.WriteXml("data.xml");

And if we have such this structure:

    public struct User
    {
        public int id;
        public string name;
    }

we can get the following results if we serialize a collection of that structure into XML:



    
        12
        Mark
    
    
        13
        Charles
    
    
        14
        John
    

Serialization can be observed in Web and WCF services too. The request and parameter information for a function are serialized into XML, and when the function returns the response and the returned data too are serialized into XML. Actually, you don’t have to think about these XML data, CLR handles this for you.

In the same vein, when it comes to Remoting, the sender and recipient must agree to the same form of XML data. That’s, when you send some data CLR serializes this data for you before it sends it to the target process. When the target process receives this XML data, it turns it back (deserializes it) to its original form to be able to handle it.

Thus, the process of converting data structures and objects into series of bits is called Serialization. The reverse of this process, converting these bits back to the original data structures and objects, is called Deserialization.

Therefore, the following ADO.NET line does deserializes the XML file:

    DataSet ds;
    ds.ReadXml("data.xml");

And when your application receives response from the server or from another process, the CLR deserializes that XML data for you.

So why XML is preferred over binary serialization? That’s because XML is text-based. Thus, it’s free to be transmitted from a process to another or via a network connection, and firewalls always allow it.

Marshaling

Marshaling is the process of converting managed data types to unmanaged data types. There’re big differences between the managed and unmanaged environments. One of those differences is that data types of one environment is not available (and not acceptable) in the other.

For example, you can’t call a function like SetWindowText() -that sets the text of a given window- with a System.String because this function accepts LPCTSTR and not System.String. In addition, you can’t interpret (handle) the return type, BOOl, of the same function, that’s because your managed environment (or C# because of the context of this writing) doesn’t have a BOOL, however, it has a System.Boolean.

To be able to interact with the other environment, you will need to not to change the type format, but to change its name.

For example, a System.String is a series of characters, and a LPCTSTR is a series of characters too! Why not just changing the name of the data type and pass it to the other environment?

Consider the following situation. You have a System.String that contains the value €œHello€:

System.String str = "Hello";

The same data can be represented in an array of System.Char too, like the following line:

System.Char[] ch = str.ToCharArray();

So, what is the difference between that System.String variable and that System.Char array? Nothing. Both contain the same data, and that data is laid-out the same way in both variables. That’s what Marshaling means.

So what is the difference between Serialization and Marshaling?

C# has a System.Int32, and Windows API has an INT, and both refer to a 32-bit signed integer (on 32-bit machines.) When you marshal the System.Int32 to INT, you just change its type name, you don’t change its contents, or lay it in another way (usually.) When you serialize a System.Int32, you convert it to another form (XML for instance,) so it’s completely changed.

Summary

Look, after I get back to Wikipedia documentation for Marshaling, I realized that my answer was so specific to C#!

I mean that, Marshaling is a very general term used to describe transformations of memory. Theoretically, it’s more general than Serialization. In Python for instance, the terms Marshaling and Serialization are used interchangeably. There (in Python,) Marshaling = Serialization, and Serialization = Marshaling, there’s no difference. In computer methodology, there’s a silent difference between Marshaling and Serialization (check the Wikipedia definition.)

So what is that System.MarshalByRefObject class? Why that name -specifically- was used? First, System.MarshalByRefObject class allows objects to be passed by reference rather than by value in applications that use Remoting.

Personally, I like to say that Microsoft .NET Framework team’s name was very scientific when they have called that object “MarshalByRefObject” with respect to that silent difference between serialization and marshaling or maybe that name was derived from Python, dunno!

After all, we should keep in mind that in .NET methodology, there’s a big difference between Serialization and Marshaling, Marshaling usually refers to the Interop Marshaling. In .NET Remoting, it refers to that serialization process.

By the way, Marshalling is so named because it was first studied in 1962 by Edward Waite Marshall, then with the General Electric corporation.

That’s all.

Have a nice day!

Microsoft Agent; Providing a Custom Popup Menu

هذه المقالة متوفرة أيضا باللغة العربية، اقرأها هنا.

A second honeymoon with Microsoft Agent. Do you remember our article Programming Microsoft Agent in WinForms and our sample application PartIt? After you have included your desired agent in your application, are you feeling bad with the default popup menu? If so, then you are in the right place (welcome :).)

Enough talking, let’s get to work! First, prepare your code that loads the Microsoft Agent and brings it to the screen.

After that, create your System.Windows.Forms.ContextMenuStrip and add your required items (well, including ‘Hide’ maybe) and finish the item event handlers.

Now, let’s complete it. Get to the code that loads the agent character (e.g. calls the Characters.Load() method of the agent control object, AxAgentObjects.AxAgent) and just disable the AutoPopupMenu flag/property of the character object, AgentObjects.IAgentCtlCharacterEx. This flag/property determines whether to allow the default popup menu or not.

For example, the following code disables this property:

    AxAgentObjects.AxAgent agentCtl;
    AgentObjects.IAgentCtlCharacterEx agentChar;

    // initializing 'agentCtl'
    // . . .

    agentCtl.Characters.Load("Merlin", "merlin.acs");
    agentChar = agentCtl.Characters.Character("Merlin");
    agentChar.AutoPopupMenu = false;

Next comes the interesting point. When the character is clicked, the ClickEvent event of the agent control (AxAgent) fires. So the next step is to handle this event and to provide your code that brings up your custom context menu. Consider the following code:

// agentCtl.ClickEvent += agent_ClickEvent;

public void agentCtl_ClickEvent(object sender, AxAgentObjects._AgentEvents_ClickEvent e)
{
    if (e.characterID == "Merlin")  // check for this if you have many characters
    {
        if (e.button == 2) // 1 = left, 2 = right
        {
            myContextMenu.Show(e.x, e.y);
        }
    }
}

Well done!

Have a nice Sunday!

9 Rules about Constructors, Destructors, and Finalizers

هذه المقالة متوفرة أيضا باللغة العربية، اقرأها هنا.

Overview

First, this writing concentrates of and compares between three programming languages, C#, C++/CLI, and ISO/ANSI C++. It discusses 9 rules that every developer should keep in mind while working with constructors, destructors, and finalizers and class hierarchies:

  • Rule #1: Contrsuctors are called in descending order
  • Rule #2: In C# lexicology, a destructor and a finalizer refer to the same thing
  • Rule #3: Destructors are called in ascending order
  • Rule #4: Finalizers are a feature of GC-managed objects only
  • Rule #5: You cannot determine when finalizers would be called
  • Rule #6: C++/CLI differs between destructors and finalizers
  • Rule #7: In C++/CLI and classic C++, you can determine when destructors are called
  • Rule #8: In C++/CLI, destructors and finalizers are not called together
  • Rule #9: Beware of virtual functions in constructors

Rule #1: Constructors are called in descending order

Rule #1: Constructors are called in descending order; starting from the root class and stepping down through the tree to reach the last leaf object that you need to instantiate. Applies to C#, C++/CLI, and ANSI C++.

Let’s consider a simple class hierarchy like this:

    class BaseClass
    {
        public BaseClass()
        {
            Console.WriteLine("ctor of BaseClass");
        }
    }

    class DerivedClass : BaseClass
    {
        public DerivedClass()
        {
            Console.WriteLine("ctor of DerivedClass");
        }
    }

    class ChildClass : DerivedClass
    {
        public ChildClass()
        {
            Console.WriteLine("ctor of ChildClass");
        }
    }

ChildClass inherits from DerivedClass, and DerivedClass, in turn, inherits from BaseClass.

When we create a new instance of ChildClass using a simple line like this:

    static void Main()
    {
        ChildClass cls = new ChildClass();
    }

the code outputs the following results:

ctor of BaseClass
ctor of DerivedClass
ctor of ChildClass

Rule #2: In C# lexicology, a destructor and a finalizer refer to the same thing

Rule #2: In C# lexicology, a destructor and a finalizer refer to the same thing; the function called before the object is fully-removed from the memory (i.e. GC-collected). Applies to C# only.

Let’s consider the same class hierarchy but with destructors:

    class BaseClass
    {
        public ~BaseClass()
        {
            Console.WriteLine("dtor of BaseClass");
        }
    }

    class DerivedClass : BaseClass
    {
        public ~DerivedClass()
        {
            Console.WriteLine("dtor of DerivedClass");
        }
    }

    class ChildClass : DerivedClass
    {
        public ~ChildClass()
        {
            Console.WriteLine("dtor of ChildClass");
        }
    }

When you define a class destructor with that C++-alike syntax (preceding the function name with a ~) the compiler actually replaces your destructor with an override of the virtual Object.Finalize() function. That is, before the object is removed (i.e. GC-collected) from the memory, the finalizer (i.e. destructor) is called first. This finalizer first executes your code. After that it calls the finalizer of the base type of your object. If we could decompile our assembly, we would see that our destructor in the ChildClass (so other classes) has been replaced with this function:

        protected virtual void Finalize()
        {
            try
            {
                Console.WriteLine("dtor of ChildClass");
            }
            finally
            {
                base.Finalize();
            }
        }

Rule #3: Destructors are called in ascending order

Rule #3: Destructors are called in ascending order, starting from the leaf object that you need to instantiate and moving up through the tree to reach the very first base class of your object. In reverse of constructors calling order. Applies to C#, C++/CLI, and ANSI C++.

Now, instantiate your class:

    static void Main()
    {
        ChildClass cls = new ChildClass();

        // 'cls' is removed from memory here
    }

the code should outputs the following results:

dtor of ChildClass
dtor of DerivedClass
dtor of BaseClass

Rule #4: Finalizers are a feature of GC-managed objects

Rule #4: Finalizers are a feature of GC managed objects (i.e. managed classes). That’s because the finalizer is called only when the GC removes the object from the memory (i.e. frees memory associated with).

Now, try to create a simple structure with a destructor:

    struct MyStruct
    {
        ~MyStruct()
        {
            Console.WriteLine("dtor of MyStruct");
        }
    }

The code won’t compile. That’s because that GC doesn’t handle structures.

Rule #5: You can’t determine when finalizers would be called

That’s because you don’t know when the next garbage collection would occur, even if you performed a manual garbage collection (using System.GC.Collect() function) you won’t know exactly when memory would be released. In addition, GC always delay releasing of finalizable object, it puts them in a special GC queue called freachable (pronounced ef-reachable, F stands for Finalize) queue. Applies to C# and C++/CLI (.NET.)

Rule #6: C++/CLI differs between destructors and finalizers

Rule #6: C++/CLI differs between destructors and finalizers. That is, finalizers are called by GC, and destructors are called when you manually delete the object.

Let’s consider the same example but in C++/CLI:

ref class BaseClass
{
public:
	BaseClass()
	{
		Console::WriteLine("ctor of BaseClass");
	}

	~BaseClass()
	{
		Console::WriteLine("dtor of BaseClass");
		GC::ReRegisterForFinalize(this);
	}
};

ref class DerivedClass : BaseClass
{
public:
	DerivedClass()
	{
		Console::WriteLine("ctor of DerivedClass");
	}

	~DerivedClass()
	{
		Console::WriteLine("dtor of DerivedClass");
		GC::ReRegisterForFinalize(this);
	}
};

ref class ChildClass : DerivedClass
{
public:
	ChildClass()
	{
		Console::WriteLine("ctor of ChildClass");
	}

	~ChildClass()
	{
		Console::WriteLine("dtor of ChildClass");
		GC::ReRegisterForFinalize(this);
	}
};

When we run the code:

int main()
{
	ChildClass^ cls = gcnew ChildClass();
}

it outputs the following results:

ctor of BaseClass
ctor of DerivedClass
ctor of ChildClass

The destructors are not called. Why? Unlike C#, in C++/CLI there is a big difference between destructors and finalizers. As you know, the finalizer is called when the GC removes the object from the memory. Destructors, on the other hand, are called when you destroy the object yourself (e.g. use the delete keyword.)

Now, try to change the test code to the following:

int main()
{
	ChildClass^ cls = gcnew ChildClass();
	delete cls;
}

Run the code. Now, destructors are called.

Next, let’s add finalizers to our objects. The code should be like the following:

ref class BaseClass
{
public:
	BaseClass()
	{
		Console::WriteLine("ctor of BaseClass");
	}

	~BaseClass()
	{
		Console::WriteLine("dtor of BaseClass");
		GC::ReRegisterForFinalize(this);
	}
	!BaseClass()
	{
		Console::WriteLine("finz of BaseClass");
	}
};

ref class DerivedClass : BaseClass
{
public:
	DerivedClass()
	{
		Console::WriteLine("ctor of DerivedClass");
	}

	~DerivedClass()
	{
		Console::WriteLine("dtor of DerivedClass");
		GC::ReRegisterForFinalize(this);
	}
	!DerivedClass()
	{
		Console::WriteLine("finz of DerivedClass");
	}
};

ref class ChildClass : DerivedClass
{
public:
	ChildClass()
	{
		Console::WriteLine("ctor of ChildClass");
	}

	~ChildClass()
	{
		Console::WriteLine("dtor of ChildClass");
		GC::ReRegisterForFinalize(this);
	}
	!ChildClass()
	{
		Console::WriteLine("finz of ChildClass");

	}
};

As you see, the syntax of constructors, destructors, and finalizers are very similar.

Now, let’s try the code:

int main()
{
	ChildClass^ cls = gcnew ChildClass();
}

GC would call finalizers and the code would outputs the following:

ctor of BaseClass
ctor of DerivedClass
ctor of ChildClass
finz of ChildClass
finz of DerivedClass
finz of BaseClass

Rule #7: In C++/CLI and C++, you can determine when destructors are called

Now, try to destroy the object yourself:

int main()
{
	ChildClass^ cls = gcnew ChildClass();
	delete cls;
}

The delete statement calls object destructors and removes the object from memory.

Or else, declare the object with stack-semantics:

int main()
{
	ChildClass cls;
}

Now, destructors are called when the scope of the object ends.

Rule #8: In C++/CLI, destructors and finalizers are not called together

Rule #8: In C++/CLI, destructors and finalizers are not called together. Only destructors or finalizers are called. If you manually delete the object or you declare it with stack-semantics, destructors are called. If you leaved the object for GC to handle, finalizers are called.

Now try to run the code. The code should outputs the following results:

ctor of BaseClass
ctor of DerivedClass
ctor of ChildClass
dtor of ChildClass
dtor of DerivedClass
dtor of BaseClass

Rule #9: Beware of virtual functions in constructors

Rule #9: Beware of virtual (overridable) functions in constructors. In .NET (C# and C++/CLI,) the overload of the most derived object (the object to be instantiated) is called. In traditional C++ (ISO/ANSI C++,) the overload of the current object constructed is called.

Let’s update our C# example:

class BaseClass
{
    public BaseClass()
    {
        Foo();
    }

    public virtual void Foo()
    {
        Console.WriteLine("Foo() of BaseClass");
    }
}

class DerivedClass : BaseClass
{
    public DerivedClass()
    {
    }

    public override void Foo()
    {
        Console.WriteLine("Foo() of DerivedClass");
    }
}

class ChildClass : DerivedClass
{
    public ChildClass()
    {
    }

    public override void Foo()
    {
        Console.WriteLine("Foo() of ChildClass");
    }
}

When you execute the code:

    static void Main()
    {
        ChildClass cls = new ChildClass();
    }

you would get the following results:

Foo() of ChildClass

The same code in C++/CLI:

ref class BaseClass
{
public:
	BaseClass()
	{
		Foo();
	}

	virtual void Foo()
	{
		Console::WriteLine("Foo() of BaseClass");
	}
};

ref class DerivedClass : BaseClass
{
public:
	DerivedClass()
	{
	}

	virtual void Foo() override
	{
		Console::WriteLine("Foo() of DerivedClass");
	}
};

ref class ChildClass : DerivedClass
{
public:
	ChildClass()
	{
	}

	virtual void Foo() override
	{
		Console::WriteLine("Foo() of ChildClass");
	}
};

The code outputs the same results.

But what if you need to call the virtual function of the BaseClass? Just change the code to the following:

ref class BaseClass
{
public:
	BaseClass()
	{
		BaseClass::Foo();
	}

	virtual void Foo()
	{
		Console::WriteLine("Foo() of BaseClass");
	}
};

Now, the code outputs:

Foo() of BaseClass

Let’s consider the same example but in classic ISO/ANSI C++:

class CBaseClass
{
public:
	CBaseClass()
	{
		Foo();
	}
	virtual void Foo()
	{
		cout << "Foo() of CBaseClass" << endl;
	}
};

class CDerivedClass : CBaseClass
{
public:
	CDerivedClass()
	{
	}

	virtual void Foo() override
	{
		cout << "Foo() of CDerivedClass" << endl;
	}
};

class CChildClass : CDerivedClass
{
public:
	CChildClass()
	{
	}

	virtual void Foo() override
	{
		cout << "Foo() of CChildClass" << endl;
	}
};

Now, run the code. It should outputs:

Foo() of BaseClass

In classic C++, the overload of the function of the class being constructed is called unlike C# and C++/CLI (.NET in general.)

Video: What’s New in C# 2/3/4

هذه المقالة متوفرة أيضا باللغة العربية، اقرأها هنا.

Here’s the complete “What’s New in C#” webcast series of Bruce Kyle (ISV Architect Evangelist of Microsoft) from Channel 9 blog:

What’s new in C# 2:

We will try to update this list as soon as new items release.

Bad Practices: Locking on Non-shared Objects in Multi-threaded Applications

Actually, I was having a problem synchronizing threads calling a function. If we could regenerate the bug, we would end up with code like this:

static void Main()
{
    Thread[] arr = new Thread[10];

    for (int i = 0; i < 10; i++)
    {
        arr[i] = new Thread(ThreadProc);
        arr[i].IsBackground = true;
        arr[i].Start(new object());
    }

    foreach (Thread t in arr)
        t.Join();
}

private static void ThreadProc(object obj)
{
    lock (obj)
    {
        int i = 0;
        while (i < 10)
        {
            Thread.Sleep(1);
            Console.WriteLine("Thread #{0},t{1}",
                Thread.CurrentThread.ManagedThreadId, i++);
        }
    }
}

And when we execute this code the results would be like this:

Thread #4,      2
Thread #3,      3
Thread #5,      1
Thread #7,      0
Thread #5,      2
Thread #6,      1
Thread #3,      4
Thread #4,      3
Thread #8,      1
Thread #9,      0

What is the problem with this code? It starts multiple threads and passes them a locking object and the object is locked successfully using the lock statement, so why threads overlap? Why the synchronization doesn’t take effect?

Well, after a long period and after pushing a new thread in the MSDN forums (we are all developers do make silly mistakes, aih? :P), I come up with the solution and discovered the drawback of the code.

The problem was that each time we start off a thread we pass it a new locking object different from the others:

        arr[i].Start(new object());

Therefore, every thread is locking on its own objects, so no thread synchronization take effect.

The solution is very easy, you should lock on a shared object; an object that is shared between all your threads accessing this block of code. Read more about thread synchronization here.

For example, we should change our code to the following:

private static object _lockThis = new object();
static void Main()
{
    Thread[] arr = new Thread[10];

    for (int i = 0; i < 10; i++)
    {
        arr[i] = new Thread(ThreadProc);
        arr[i].IsBackground = true;
        arr[i].Start();
    }

    foreach (Thread t in arr)
        t.Join();
}

private static void ThreadProc(object obj)
{
    lock (_lockThis)
    {
        int i = 0;
        while (i < 10)
        {
            Thread.Sleep(1);
            Console.WriteLine("Thread #{0},t{1}",
                Thread.CurrentThread.ManagedThreadId, i++);
        }
    }
}

Finally, this was one of the bad practices and mistakes me (and many others too) fall in. Honestly, I would like to start my Bad Practices series :”>. I would write about problems I came across and solutions I found for them. In addition, I’m thinking of starting the Questions series. I got a huge number of questions every week, if we could publish them I think it would be great for all.

Have a nice day!

Programmatically Enumerating, Attaching, and Detaching SQL Server Databases

هذه المقالة متوفرة أيضا باللغة العربية، اقرأها هنا.

Contents

Contents of this article:

  • Contents
  • Overview
  • Introduction
  • Enumerating Databases on a Server
    • INFORMATION_SCHEMA.SCHEMATA System View
    • sys.sysdatabases System Table/View
    • sys.databases System View
    • sys.sp_databases Stored Procedure
  • Attaching Databases to the Server
    • CREATE DATABASE Statement
    • sys.sp_attach_db Stored Procedure
    • sys.sp_attach_single_file_db Stored Procedure
  • Detaching Databases from the Server
    • CREATE DATABASE Statement
    • sys.sp_detach_db Stored Procedure
  • Security Considerations

Overview

This writing is like an encyclopedia for the SQL statements and stored procedures used to enumerate, attach, and detach databases on a SQL Server instance. It lists the statements and stored procedures that you can use and discusses them in simple examples.

Introduction

Today we are going to talk about how to programmatically enumerate databases on a SQL Server instance and how you can attach and detach SQL Server databases.

Enumerating Databases on a Server

You can get a list of databases on a server using one of many ways:

  • INFORMATION_SCHEMA.SCHEMATA system view (SQL Server 2000 only)
  • sys.sysdatabases system table (a view in SQL Server 2005 and higher versions)
  • sys.databases system view (SQL Server 2005 and higher versions)
  • sys.sp_databases stored procedure

INFORMATION_SCHEMA.SCHEMATA System View

If you are using SQL Server 2000, you can query the system view INFORMATION_SCHEMA.SCHEMATA to get information about current databases on the server.

The following is the table diagram for INFORMATION_SCHEMA.SCHEMATA system view:

Actually, you don’t need to worry about any of the view columns, just the first column, CATALOG_NAME, that you need to worry about, it is the database (i.e. catalog) name.

The following code simply prints out the databases currently found on the default SQL Server instance in the current machine:

    // C# Code

    SqlConnection conn = new SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI");
    SqlCommand cmd = new SqlCommand("", conn);
    SqlDataReader rdr;

    cmd.CommandText = "SELECT DISTINCT    CATALOG_NAME    FROM    INFORMATION_SCHEMA.SCHEMATA";

    conn.Open();

    rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        Console.WriteLine(rdr.GetString(0));
    }

    rdr.Dispose();
    cmd.Dispose();
    conn.Dispose();
' VB.NET

Dim conn As New SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI")
Dim cmd As New SqlCommand("", conn)
Dim rdr As SqlDataReader

cmd.CommandText = "SELECT DISTINCT    CATALOG_NAME    FROM    INFORMATION_SCHEMA.SCHEMATA"

conn.Open()

rdr = cmd.ExecuteReader()
While (rdr.Read())
    Console.WriteLine(rdr.GetString(0))
End While

rdr.Dispose()
cmd.Dispose()
conn.Dispose()

Again, this is for SQL Server 2000 only.

Check the MSDN documentation for INFORMATION_SCHEMA.SCHEMATA system view here.

sys.sysdatabases System Table/View

This is a system table specific to SQL Server 2000. In SQL Server 2005 it is provided for backward compatibility as a system view. Therefore, do not rely on this system view (or table) because it is expected to be removed in a future version of SQL Server.

The definition of this table/view is as following:

Only the first column, name, is the most important to us, it contains the database name. Other columns of importance (not for the subject of this topic) are:

  • dbid:
    Database ID.
  • sid:
    Security ID for the database.
  • crdate:
    Creation date of the database.
  • filename:
    Database filename.

You can change the line that sets the command text in the previous code to this line:

    // C# Code

    cmd.CommandText = "SELECT [name]    FROM    sys.sysdatabases";
    ' VB.NET Code

    cmd.CommandText = "SELECT [name]    FROM    sys.sysdatabases";

Again, using the sys.sysdatabases system table/view is not recommended because it would be removed in a future version of SQL Server.

Check the MSDN documentation for sys.sysdatabases system view/table here.

Check this MSDN article out: Mapping System Tables to System Views for more information about SQL Server 2000 tables mapped to SQL Server 2005 views or stored procedure.

sys.databases System View

This is the new version included in SQL Server 2005 (and higher versions) replaces the SQL Server 2000 sys.sysdatabases table.

This is a very lengthy system view, it includes tenths of columns, we are interested only on the first column, name, that contains the database name.

You can change the line that sets the command text in the first code to this line:

    // C# Code

cmd.CommandText = "SELECT [name]    FROM    sys. databases";
    ' VB.NET Code

cmd.CommandText = "SELECT [name]    FROM    sys. databases"

Check the MSDN documentation for sys.sysdatabases system view/table here.

sys.sp_databases Stored Procedure

This way is different from all others because it is not a system view or a system table, it is a system stored procedure.

This stored procedure accepts no parameters and returns a result set of three columns:

  • DATABASE_NAME:
    The name of the database.
  • DATABASE_SIZE:
    The size of the database (in kilobytes.)
  • REMARKS:
    Always NULL. For the Database Engine.

The following code demonstrates this stored procedure:

// C# Code

cmd.CommandText = "exec sys.sp_databases";
' VB.NET Code

cmd.CommandText = "exec sys.sp_databases"

Check the MSDN documentation for sys.sysdatabases system view/table here.

Attaching Databases to the Server

You can programmatically attach a database to the server in two ways:

  1. Using the CREATE DATABASE statement
  2. Using the sys.sp_attach_db system stored procedure
  3. Using the sys.sp_attach_single_file_db system stored procedure

CREATE DATABASE Statement

The CREATE DATABASE statement can be used to create databases into the server or to attach existing database files.

If you are going to attach a database, this statement should be formed as the following:

CREATE DATABASE database_name
    ON  [ ,...n ]
    FOR ATTACH [;]

The database_name is the name that you wish to give to the database. In addition, this statement takes filegroups of the database files.

Keep in mind that the database name should not be exist in the server or the function would fail.

The following example shows how you can attach the database database.mdf to the server and give it the name MyDatabase:

    // C# Code

    SqlConnection conn = new SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI");
    SqlCommand cmd = new SqlCommand("", conn);

    cmd.CommandText =
        "CREATE DATABASE 'MyDatabase' ON " +
        "PRIMARY ( FILENAME =  'database.mdf' ) " +
        "FOR ATTACH";

    conn.Open();

    cmd.ExecuteNonQuery();

    cmd.Dispose();
    conn.Dispose();
' VB.NET Code

Dim conn As New SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI")
Dim cmd As New SqlCommand("", conn)

cmd.CommandText = "CREATE DATABASE 'MyDatabase' ON " & _
    "PRIMARY ( FILENAME =  'database.mdf' ) " & _
    "FOR ATTACH"

conn.Open()

cmd.ExecuteNonQuery()

cmd.Dispose()
conn.Dispose()

If no log file (.LDF) can be found, SQL Server creates one for you.

The following code attaches the same database along with its log file. Just change the third line of the previous example that sets the command text with this line:

    // C# Code

    cmd.CommandText = "CREATE DATABASE 'MyDatabase' ON " +
        "PRIMARY ( FILENAME =  'database.mdf' ), " +
        "FILEGROUP MyDatabase_Log ( FILENAME = 'database.ldf')" +
        "FOR ATTACH";
' VB.NET Code

cmd.CommandText = "CREATE DATABASE 'MyDatabase' ON " & _
    "PRIMARY ( FILENAME =  'database.mdf' ), " & _
    "FILEGROUP MyDatabase_Log ( FILENAME = 'database.ldf')" & _
    "FOR ATTACH"

Check the MSDN documentation for the CREATE DATABASE statement here.

More about database files and filegroups can be found in the MSDN documentation here.

sys.sp_attach_db Stored Procedure

Another way that allows to attach a database to the server is the sys.sp_attach_db stored procedure. The definition of this stored procedure is as following:

sp_attach_db [ @dbname= ] 'dbname'
    , [ @filename1= ] 'filename_n' [ ,...16 ]

This function takes the database name as the first argument. In addition it accepts another 16 arguments (only the first is required) represent database files. The following code attaches the same database to the server. Again, just change the third line of the previous code to the following line:

    // C# Code

    cmd.CommandText = "exec sys.sp_attach_db    MyDatabase,    'database.mdf'";
' VB.NET Code

cmd.CommandText = "exec sys.sp_attach_db    MyDatabase,    'database.mdf'"

Check the MSDN documentation for the sys.sp_attach_db statement here.

sys.sp_attach_single_file_db Stored Procedure

This statement is the same as sys.sp_attach_db stored procedure. However, this statement accepts only one file, the database file.

Check the following code out:

    // C# Code

    cmd.CommandText = "exec sys.sp_attach_single_file_db    MyDatabase,    'database.mdf'";
' VB.NET Code

cmd.CommandText = "exec sys.sp_attach_single_file_db    MyDatabase,    'database.mdf'"

Check the MSDN documentation for the sys.sp_attach_single_file_db statement here.

Detaching Databases from the Server

Unlike attaching databases, you can detach a database from a server in only two ways:

  1. DROP DATABASE statement
  2. sys.sp_detach_db system stored procedure

DROP DATABASE Statement

This statement is used to remove one or more databases from SQL Server. It has the following syntax:

DROP DATABASE database_name [ ,...n ] [;]

The following code simply executes this statement against our database MyDatabase:

    // C# Code

    SqlConnection conn = new SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI");
    SqlCommand cmd = new SqlCommand("", conn);

    cmd.CommandText = "DROP DATABASE MyDatabase";

    conn.Open();

    cmd.ExecuteNonQuery();

    cmd.Dispose();
    conn.Dispose();
' VB.NET Code

Dim conn As New SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI")
Dim cmd As New SqlCommand("", conn)

cmd.CommandText = "DROP DATABASE MyDatabase"

conn.Open()

cmd.ExecuteNonQuery()

cmd.Dispose()
conn.Dispose()

Check the MSDN documentation for the sys.sp_attach_single_file_db statement here.

sys.sp_detach_db Stored Procedure

Huh, the last one. This stored procedure is used to detach a database from the server. It has the following syntax:

sp_detach_db [ @dbname= ] 'database_name'

It accepts a single argument, the database name. The following code removes our database, MyDatabase, from the server:

    // C# Code

    cmd.CommandText = "sys.sp_detach_db MyDatabase";
' VB.NET Code

cmd.CommandText = "sys.sp_detach_db MyDatabase"

Check the MSDN documentation for the sys.sp_attach_single_file_db statement here.

Security Considerations

Every statement and stored procedure we discussed in this writing requires specific permissions. Check the MSDN documentation to get more information.

Creating a Simple Sheet Designer in C#

هذه المقالة متوفرة أيضا باللغة العربية، اقرأها هنا.

Code: Geming.Sisc.msi

Contents

Contents of this article:

  • Contents
  • Introduction
  • Problem
  • Requirements
  • Solution
  • Snapshots
  • Component Design
  • Class Diagrams
  • Database Diagram
  • Characteristics
  • Background
  • Code Highlights
  • Download

Introduction

Today, we are going to create a simple application, actually a simple sheet designing tool. This tool gives the user the flexibility to design his sheets, reports, bills, invoices, and receipts (whatever.)

In this writing, we will refer to reports, bills, invoices, receipts, etc. with just the name sheets. For this, we will need to give a sheet a definition.

A sheet is just like an empty page (i.e. template) that contains sheet items. For example, a resume template is a sheet that has many items (name, birth date, address, etc.) A cash receipt is a sheet that has a few items (date, amount, charged to, received by, etc.)

We will go through this tool in a nice way. We will begin by a very simple system analysis and application design. After that, we will get into coding.

Actually, I€™m not an architect, and I think I will never be. So please, DO NOT blame me for this bad analysis. It is just an illustration to get you have a solid understanding of what our application is designed for.

Problem

The user is tied into the sheet designs that the developer has created for him. The user should have the ability to design his own sheets and/or to edit application defined sheets.

Requirements

User Requirements

The following is list of common user requirements:

  • The application should allow the user to create, edit, and delete sheets, and to group those sheets into categories he creates.
  • For the sake of simplicity, those categories would be only one level, no nested categories are allowed.
  • The application should be persistent. The data should be stored in a database and loaded when the user needs it.
  • The sheet should be loaded with right data and printed whenever the user asks.
  • Like Visual Studio, the user should have a Toolbox that has all types of sheet items. The user could insert a sheet item into his sheet by drawing it into the screen, dragging it from the toolbox, or just double-clicking its icon in the toolbox.
  • The user should have a ruler to allow him measuring sheet item dimensions. In addition, he should have a design-time-only grid.
  • The user should be allowed to set attributes of the sheet like the page setup, grid settings, background color, etc.
  • Not all sheet attributes are printed out; some are for design-mode only (like background color, and grid.)
  • Each type of sheet items should have its own attributes and the user should be allowed to change them too.
  • The user should be allowed to drag sheet items around the screen and position them.
  • The user should be allowed to cut, copy, paste, and delete sheet items.
  • Of course, the user should be allowed to print out the sheet, and to preview it too.
  • The application should be generic to be used in any system and in any application.
  • Other pieces of the system should have the possibility to interact with the sheet.

Functional Requirements

Common functional (developer) requirements are:

  • Abstraction. Component/Class abstraction should be considered into the design. The system should consist of several components each of which groups a related features together (e.g. interface objects, business objects, and data management.)
  • Extensibility. The system should be extensible. A good class hierarchy should be created, and derivation (inheritance) should be taken into consideration. In addition, every sheet item should be represented by a class in this hierarchy.
  • Technically, sheet items would be called shapes. Because they are just drawings into the sketch (or sheet.)
  • To allow the user to work with sheet items (shapes) and the application to populate the sheet with data, sheet items should not be drawn directly into the sheet page. Instead every item should represented by a class that has its own drawing routine.
  • The sheet should be a container for the shapes. All shapes are children of the sheet.
  • The sheet and rulers should be Windows controls to allow them to be hosted by a Windows Form.
  • Sheet items should be Windows controls too to allow them to be hosted by the sheet.
  • The sheet items should be owner-drawn controls and they should not be derived from existing Windows controls to help integrating them easily into the sheet.
  • For the sake of simplicity, the sheet and shapes should be serialized into XML and stored in the database.
  • The database should be a SQL Server database to allow faster communication and easier manipulation of XML data.
  • The database should have three tables for the three core system components, the category, the sheet, and the shape.
  • Every sheet item in a given sheet should have a unique name (or a tag) to allow other system components to interact with it (e.g. populate it with the right data.)
  • The developer should have the ability to control the quality of the painting process for each control or item independently.

Solution

After going through project requirements and considering a sophisticated system design, we got a nice plan for our project that would be called Geming SISC (Sheet Infrastructure Components.)

This project would be created using C# and .NET 2.0 (or future versions of course.)

Snapshots

The following are final snapshots of the application. Figure 1 shows a cash receipt sheet designed by labels, boxes, and lines. Figure 2 shows a simple resume template designed by only a few labels and a picture. The left pane of the application lists current sheets grouped by their categories.

Figure 01 - Geming SISC (Cash Receipt)

Figure 01 - Geming SISC (Cash Receipt)

Figure 2 - Geming SISC (Resume Template)

Figure 2 - Geming SISC (Resume Template)

As you know other system components could fill those fields up with correct information.

Component Design

Figure 3 shows the three components of our system.

Figure 3 - Geming SISC Component Design

Figure 3 - Geming SISC Component Design

The three core components of our system, Geming SISC are:

  • Geming.Sisc.Infrastructure:
    Contains the sheet control and shapes controls (label, text box, etc.) The sheet, rulers, and sheet items controls are all derived from System.Windows.Forms.Control.
  • Geming.Sisc.Data:
    Business objects that would be sent to the data among database manager objects. This component references the Geming.Sisc.Infrastructure component.
  • Geming.Sisc.Driver:
    The application interface that would be used to design sheets. It references the other two components.

Class Diagrams

Extensibility is one of the main goals of the system. A flexible class hierarchy should be considered as well as derivation (i.e. inheritance) of controls for other external objects.

The following is the class hierarchy for the sheet and the ruler controls.

Figure 4 - Sheet and Rulers Class Hierarchy

Figure 4 - Sheet and Rulers Class Hierarchy

As we can see, the base class is the System.Windows.Forms.Control class. This allows the sheet and rulers controls to be inserted into a Windows form or a Windows control.

The following figure, figure 5, shows the class hierarchy of sheet items (technically called shapes.)

Figure 5 - Sheet Items (Shapes) Class Hierarchy

Figure 5 - Sheet Items (Shapes) Class Hierarchy

As we can see, the base class for all shapes is the abstract Geming.Sisc.Infrastructure.ShapeBase class which inherits from System.Windows.Forms.Control class. All other shapes are derived from ShapeBase.

Two edit shapes were created, TextBoxShape that has a look like a Windows Text Box, and LabelShape that has a look like a normal Windows Label. Both are derived from the abstract EditableShapeBase.

Other shapes are BoxShape, LineShape, ImageShape, and CheckBoxShape.

All classes are serializable (implement System.Runtime.Serialization.ISerializable interface,) so they can easily converted into XML and pushed to the database.

For simplicity, we have developed just the box, line, image, check box, and two edit shapes (or sheet items.) You can go further and create any other shapes you like. In addition, you can create more specific items like CurrencyField, DateField, etc.

For more detailed class diagrams like those shows class members, check the application code.

Database Diagram

In its simplicity, database is defined as the following diagram illustrates:

Figure 6 - Geming SISC Database Diagram

Figure 6 - Geming SISC Database Diagram

Notice that, all data operations are carried out through stored procedures in the database.

The Shape.Value column is of the type xml to allow easier manipulation of XML data in the future.

Characteristics

Here are some of the characteristics (i.e. attributes) of the sheet (some are represented by properties):

  • Background color:
    The user should be able change the background color. Notice that the background color is not printed.
  • Non-printable grid:
    To help the user position sheet items. The user should be able to display them or not. In addition, the user can change the grid color.
  • Margin:
    The application should set the page margin based on print settings.
  • Title:
    Every sheet has a title, a description, and a category.

In addition, next is a list of some the characteristics of a shape (sheet item):

  • Selected:
    Is the shape currently selected or not. The user could select a shape by the mouse. A selection frame is drawn around the shape when selected.
  • Non-printable size grip:
    Each shape should have a size grip to allow the user to resize the shape.
  • Cloning:
    The shape should be able to be cloned; that is copied, to allow the copy and paste feature.

Background

Here are some refreshers of techniques we use in this system:

  • Painting

We will rely on custom painting in most situations. Therefore, well request help from System.Drawing classes specially the System.Drawing.Graphics class.

In some shapes like those mimic existing Windows controls (like the text box and the check box,) we will get help from classes in System.Windows.Forms.VisualStyles namespace to reflect the visual styles of Windows in our controls. In addition, System.Windows.Forms.ControlPaint class is used for drawing borders and selection rectangles.

  • Painting Quality

Every control in our project (sheet, rulers, and shapes) has a paining quality property that determines the quality of the drawing (low, medium, and high.) For this to work we will make use of some properties of the System.Drawing.Graphics object like those related to smoothing and anti-aliasing feature.

  • Serialization

All serialized objects should be marked with System.SerializableAttribute attribute. With help from System.Runtime.Serialization namespace we could customize the serialization process.

The core interface that would allow us to customize the serialization process is the System.Runtime.Serialization.ISerializable interface (implemented in all serializable classes.) Notice that, we should add the deserialization constructor to get correct deserialization.

  • Database

The database is a SQL Server client database with all operations included as stored procedures.

The system uses two-tier architecture; means that it accesses the database directly using just three objects, a connection, a command, and a data reader.

  • Design-Mode Support

For extending design-mode support we have created custom designer service for the Sheet class that inherits the System.Windows.Forms.Design.ParentControlDesigner class to allow nested controls in the Sheet object in the design-mode.

Code Highlights

In this section we will talk about significant blocks of code that would be of interest.

  • Painting Routines

The following is the code for OnPaint() routine overridden by only the ShapeBase class.

Listing 1 – ShapeBase.OnPaint() Method Code Listing
protected override void OnPaint(PaintEventArgs e)
{
    GraphicsManager.SetQuality(e.Graphics, PaintingQuality);

    ControlPaint.DrawBorder(e.Graphics, this.ClientRectangle, this.ForeColor, ButtonBorderStyle.Dotted);

    PaintShape(e.Graphics);

    if (Selected)
        DrawSelectionFrame(e.Graphics);

    ControlPaint.DrawSizeGrip(e.Graphics, this.BackColor, GetResizeGripRect());
}

This function first calls the GraphicsManager.SetQuality() function that sets the quality attributes of the Graphics object. We will get back to this function in about a moment.

After that the function paints the control border using the System.Windows.Forms.ControlPaint class.

Next comes the interesting point. The function calls the virtual function PaintShape() that a derived class overrides to provide its own drawing routines.

For example, the TextBoxShape class has this PaintShape() override:

Listing 2 – TextBoxShape.PaintShape() Method Code Listing
public override void PaintShape(Graphics dc)
{
    base.PaintShape(dc);

    using (Brush b = new SolidBrush(this.BackColor))
    dc.FillRectangle(b, this.ClientRectangle);

    using (Pen p = new Pen(SystemColors.ActiveCaption, 1))
    {
        p.Alignment = System.Drawing.Drawing2D.PenAlignment.Inset;
        Rectangle r = this.ClientRectangle;
        dc.DrawRectangle(p, r);
    }

    Rectangle rect = this.ClientRectangle;
    rect.Offset(2, 2);
    rect.Width -= 4; rect.Height -= 4;

    StringFormat format = new StringFormat();
    format.LineAlignment = this.Alignment;
    if (this.RightToLeft == RightToLeft.Yes)
        format.FormatFlags = StringFormatFlags.DirectionRightToLeft;

    using (Brush b = new SolidBrush(this.ForeColor))
        dc.DrawString(this.Text, this.Font, b, rect, format);
}

After that the OnPaint() function draws selection frame if the shape is currently selected.

Just before the function closes, it calls the ControlPaint.DrawSizeGrip() function to draw the sizing grip that the user would resize the shape from.

  • Painting Quality

In our library Geming.Sisc.Infrastructure, we have created a helper class, GraphicsManager, which contains only one function, SetQuality().

This function accepts two input parameters, the graphics object and the quality you wish to set to that object. The following is the code for the SetQuality() function.

Listing 3 – GraphicsManager.SetQuality() Method Code Listing
public static void SetQuality(System.Drawing.Graphics g, PaintingQuality quality)
{
    if (g == null)
        throw new ArgumentNullException("g");

    if (quality == PaintingQuality.High)
    {
        g.CompositingQuality = CompositingQuality.AssumeLinear;
        g.InterpolationMode = InterpolationMode.HighQualityBicubic;
        g.PixelOffsetMode = PixelOffsetMode.Half;
        g.SmoothingMode = SmoothingMode.AntiAlias;
        g.TextRenderingHint =
        System.Drawing.Text.TextRenderingHint.ClearTypeGridFit;
    }
    else if (quality == PaintingQuality.Medium)
    {
        g.CompositingQuality = CompositingQuality.HighQuality;
        g.InterpolationMode = InterpolationMode.Bilinear;
        g.PixelOffsetMode = PixelOffsetMode.HighQuality;
        g.SmoothingMode = SmoothingMode.HighQuality;
        g.TextRenderingHint =
        System.Drawing.Text.TextRenderingHint.AntiAliasGridFit;
    }
    else
    {
        g.CompositingQuality = CompositingQuality.Default;
        g.InterpolationMode = InterpolationMode.Default;
        g.PixelOffsetMode = PixelOffsetMode.Default;
        g.SmoothingMode = SmoothingMode.Default;
        g.TextRenderingHint =
        System.Drawing.Text.TextRenderingHint.SystemDefault;
    }
}

Download

The application Geming.Sisc along with its code is available for download here.

Download Geming.Sisc