Sunday, February 24, 2013

Enabling SQL Server Service Broker

Below is the common script we used to enable the Service Broker in SQL Server 2008.

ALTER DATABASE <DatabaseName> SET ENABLE_BROKER;

Example:


ALTER DATABASE Northwind SET ENABLE_BROKER;

If you feel your database has an outstanding open connection, you have to clear it first before running the script. For you to terminate all connection, you have to set the current database to be single-user and call the rollback keyword to terminate it all. After executing the script, set back the database to be a multi-user database.

Example script:

ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE Northwind SET ENABLE_BROKER;
GO
ALTER DATABASE Northwind SET MULTI_USER;
GO

Microsoft Documentation: Please visit this link for more information about SQL Server Service Broker.

Sunday, February 10, 2013

Querying Database Objects in SQL Server

The SQL Server contains a built-in schema called [sys] which can be used to query all the server objects created on the current database. This schema provides all the information used by each object of the database such as indexes, columns/fields, tables and views.

This is commonly used by other developer to actually developed an auto generator tool for their architecture.

For Microsoft documentation, please visit this link.

See below how to query the SQL Server Objects from the SQL Server.

Querying Objects

Inside the [sys] schema, if your SQL Server has intellisense, you can see all the schema definition objects if you put a dot after [sys] keyword. See below the screenshot.



You can select what schema object you may query for. We can use a default SELECT query to do a query.

Let say for example, we can query the tables, views, stored procedures and functions with the use of sys.objects catalog.

Querying Tables

We can use the
sys.objects catalog to query the list of tables from the database. See sample code below.

SELECT object_id
      , name
      , type
      , type_desc
      , create_date
      , modify_date
FROM sys.objects
WHERE type = 'u'
ORDER BY name;

Type 'U' defined the object as the USER_TABLE. If we want to query the custom stored procedures, then we can filter the type 'P'.

Querying Fields

We can use the sys.columns catalog to query all the columns from the database. After querying the columns, we can use the object_id field to determine what table it is belong to. See sample below.

SELECT object_id
      , name
      , column_id
      , max_length
      , user_type_id
      , system_type_id
FROM sys.columns;

Joining the table and only querying the custom table columns.

SELECT o.object_id

      , o.name as tablename

      , c.name as columnname
      , c.column_id
      , c.max_length
      , c.user_type_id
      , c.system_type_id
FROM sys.columns c
INNER JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.type = 'U'
ORDER BY o.name, c.name;

Querying Indexes


Same with the columns, we can use the sys.indexes to query the list of indexes under one table.  See our sample below.

SELECT i.object_id
      , o.name as tablename
      , i.name
      , i.index_id
      , i.type
      , i.type_desc
      , i.is_unique
      , i.is_primary_key
FROM sys.indexes i
INNER JOIN sys.objects o ON o.object_id = i.object_id
WHERE o.type = 'u'
ORDER BY o.name;

Querying the indexed columns by table can be filtered using the sys.index_columns. See below.

SELECT i.object_id
      , o.name as tablename
      , c.name as columnname
      , i.index_id
      , i.index_column_id
      , i.column_id
FROM sys.index_columns i
INNER JOIN sys.objects o ON o.object_id = i.object_id
INNER JOIN sys.columns c ON c.object_id = o.object_id AND c.column_id = i.column_id
WHERE o.type = 'u'
ORDER BY o.name;

Base in your requirements, you can expand and filter more specific objects inside [sys] schema.

Saturday, February 2, 2013

Execute SQL Server Scripts in C#

With this tutorial we will guide you how to execute SQL Server scripts from C#.Net. This topic is more about SMO or SQL Server Management Objects.

For your reference regarding SMO, please visit Microsoft documentation.

What is SQL Server Management Object?

The SQL Server Management Object is a set of API developed by Microsoft so that any object manipulations in SQL Server can also be done in the client. This allow other developer to develop more a dynamic Query or Class generator architecture.

Let us start with the set of procedure below.

First, in your C# project, add a reference to the list of DLL below.
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoExtended
  • Microsoft.SqlServer.SqlEnum
Folder Location: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\

We will most likely only use 2 binary there (just add other for your future development).

For you to make your SQL scripts runnable in the client, you should test it first in the SQL Management Studio whether there are no syntax error existed. If you feel that the script is right then you're ready to go with the client manipulation.

Stored Procedure

Suppose you have a database named Northwind and you have a table named User (userid, name, email, createddate); then we will create a sample stored procedure for that table.

With our sample table User, we will create a script to get the current user based on the UserID parameter. See below our sample script.

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetUser_sp]
(
      @UserID BIGINT
)
AS
BEGIN
      SELECT [UserID]
      , [Name]
      , [Email]
      , [CreatedDate]
      FROM [dbo].[User]
      WHERE ([UserID] = @UserID);
END

Ensure that the script above is running successfully in the SQL Management Studio. If you found any problem or error (script problem or syntax error) then fix it before executing it in the client.

.NET SMO Execution

We will now guide you how to execute it in the client. Now, go back to our C# project solution and do some code snippet.

First add a reference to the namespaces Microsoft.SqlServer.Management.Smo and Microsoft.SqlServer.Management.Common to your class above. See below.

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

Then, create a SqlConnection object that connects to your database. See below.

using (var connection = new SqlConnection(this.ConnectionString))
{
   
}

Inside the using block, create a Server and Database object. See below our codes.

using (var connection = new SqlConnection(this.ConnectionString))
{
    var server = new Server(new ServerConnection(connection));
    var database = server.Databases[connection.Database];
}

In the Server object ConnectionContext property, we need to set the property AutoDisconnectMode to NoAutoDisconnect for it to not disconnect when there are existing pooling operation in the database.

After that, call the Connect method to connect on the server and then call the ExecuteNonQuery method passing the string of our SQL Scripts. Please make sure to disconnect the connection once executed.

Now, our new code is below.

using (var connection = new SqlConnection(this.ConnectionString))
{
    var server = new Server(new ServerConnection(connection));
    var database = server.Databases[connection.Database];
    server.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect;
    server.ConnectionContext.Connect();
    server.ConnectionContext.ExecuteNonQuery("SQL SCRIPTS HERE");
    server.ConnectionContext.Disconnect();
}

Note: If you are running the 4.0 version of .NET and if you however encountered an exception regarding version compatibility. You need to support the 2.0 version of .NET during start up. To do this, you have to modify some settings from your config file. See below.

<?xml version="1.0"?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
</configuration>

The useLegacyV2RuntimeActivationPolicy will do the trick.

That's all. Have a happy coding.

Wednesday, January 30, 2013

Creating an Attribute in C#

In C#, attribute is one of the common declarative technique that most of us is using into our application. Usually, we use the attribute to tag or flag a class, method or property in the form of object base recognition. It also simplify our determination in every object.

Furthermore, in C# there are lots of pre-defined attribute that we can embed into our application. One common sample is SerializableAttribute which flag/tag our class to participate in serialization/deserialization process. If however you would like to extend the attribute functionality and define your own, you can extend/inherit the attribute class from System namespace.

Please visit C# Flags Attribute blog before you continue. The reason is to let you explore the enumeration named Privilege.

Now, we are expecting that you already explore the Privilege enumeration form mentioned  blog above.

Let say for example you would like to define a scenario that gives your code-level to determine whether a class (or a stub object) can be deleted from database. See below our sample custom attribute.

public class SecurityAccess : Attribute
{
    public SecurityAccess()
    {
    }

    public SecurityAccess(Privilege privilege)
        : this()
    {
        this.Privilege = privilege;
    }

    public Privilege Privilege
    {
        get;
        set;
    }
}

What the class SecurityAccess is doing above is just simply handling the value for the Privilege enumeration. This property defines whether the class that declare this attribute has an enough level of privilege before doing necessary action in the database.

How to use custom attribute?

Now, in this section, we will going to guide you how to use the attribute we created above.

Suppose we have two class that inherits from one object. Let's call them Animal, and the two other class in Cat and Dog. See below implementation.

public class Animal
{
    public bool IsDeletable
    {
        get;
        protected set;
    }
}

public class Cat : Animal
{
    public Cat()
    {
    }
}

public class Dog : Animal
{
    public Dog()
    {
    }
}

The Dog and Cat is an Animal base on our implementation. With the use of our custom attribute named SecurityPrivilege, we can declare each privilege in every class. Suppose we would like different breed of Cat cannot be deleted from our database, then we can declare our custom attribute like below.

[SecurityAccess(Privilege = Privilege.Read | Privilege.Write | Privilege.Create)]
public class Cat : Animal
{
    public Cat()
    {

    }
}

And the Dog breed is deletable. See below.

[SecurityAccess(Privilege = Privilege.Read | Privilege.Write | Privilege.Create | Privilege.Delete)]
public class Dog : Animal
{
    public Dog()
    {

    }
}

How to access an attribute value declared in the class/method and other object?

Now, in this section, we will going to guide you how to access the value of the attribute per class level.

In C#, the only way to get the attribute value is to use Reflection. First, we need to get the current type of the object, from there check if there are declared custom attributes and determine the attribute type. If its match the attribute we're looking for, then that is the custom attribute we had created. See below the process.

protected T GetCustomAttribute<T>(object @object)
{
    if (!object.ReferenceEquals(@object, null))
    {
        var type = @object.GetType();
        var attrs = type.GetCustomAttributes(false);
        if (attrs.Length > 0)
        {
            foreach (var attr in attrs)
            {
                if (attr is T)
                {
                    return (T)(object)attr;
                }
            }
        }
    }
    return default(T);
}

What the code is doing above is to simply return the embedded attribute in the object you passed in the parameter named @object. If there is no attribute found, then it will return null. Best to place this code in the Animal base class so both derive class can use it.

And now, in the construction of Dog and Cat class, you should call the method directly from there.

public Dog()
{
    var attr = this.GetCustomAttribute<SecurityAccess>(this);
    base.IsDeletable = (attr.Privilege & Privilege.Delete) == Privilege.Delete;
}

public Cat()
{
    var attr = this.GetCustomAttribute<SecurityAccess>(this);
    base.IsDeletable = (attr.Privilege & Privilege.Delete) == Privilege.Delete;
}

Now, in the base class IsDeletable property, we then set it depends on the privilege level we declared on the class.

Please note that we can override the attribute value declared in the base class into derive class. So if you want that the Siamese Cat breed be deletable, you can declare your own SecurityAccess attribute in Siamese class.

That's all about this blog. Please follow us so you will get more interesting topics soon.

Please visit Microsoft documentation for further details.

Sunday, January 27, 2013

WPF Binding to a Static Instance

This tutorial will guide you how to bind in a static property of a class in a two directional way in WPF.

Two way Binding in a Static Property

In order for our class StaticBinder to participates in the two directional binding, we should implement the IPropertyNotifyChanged event. In our case, we are requiring you to read our pre-requisite blog so you will fully understand the implementation.

List of pre-requisite blogs.

And here we are expecting that you're finish reading the pre-requisite blogs above.

Now let's start with our explanation.

We need to modify our existing implementation of StaticBinder class and force inherit from the BaseObject class. With this, the StaticBinder will also inherit the implementation of the super class PropertyNotifier that implements the IPropertyNotifier interface.

In each property of the StaticBinder class, we should use the BaseObject GetValue/SetValue accessor so it will notify the listener on every property value changed. Please note that you need not to implement each method in a static way. In our case, you must remove the static keyword in each property.

Now, last thing to do within StaticBinder class is to declare an additional static property named Instance. This approach is a single-ton approach for the StaticBinder class. This new property named Instance will participate in WPF binding. See below our new class implementation.

public class StaticBinder : BaseObject
{
    private static StaticBinder __instance = null;

    static StaticBinder()
    {
        __instance = new StaticBinder();
    }

    public StaticBinder()
    {
        MSG_Cancel = "Welcome";
        MSG_OK = "OK";
        MSG_Welcome = string.Format("Welcome {0}!", "WPF Binding");
    }

    public string MSG_Cancel
    {
        get { return base.GetValue<string>("MSG_Cancel"); }
        set { base.SetValue("MSG_Cancel", value); }
    }

    public string MSG_OK
    {
        get { return base.GetValue<string>("MSG_OK"); }
        set { base.SetValue("MSG_OK", value); }
    }

    public string MSG_Welcome
    {
        get { return base.GetValue<string>("MSG_Welcome"); }
        set { base.SetValue("MSG_Welcome", value); }
    }

    public static StaticBinder Instance
    {
        get { return __instance; }
    }
}

Binding in XAML

Now, if we are binding a static property in XAML, we need to use the namespace referencing and direct call the static property of that instance. Additionally, you can bind directly to the property of that shared instance which is in our case it is the StaticBinder class.

With the help of the Binding (x:Static) keyword, the trick will be addressed. See below our codes in the XAML in yellow background.

<Window x:Class="CodesDirectory.WIN_StaticBinding"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="WIN Static Binding" Height="300" Width="300"
        xmlns:classes="clr-namespace:CodesDirectory.Classes"
        WindowStartupLocation="CenterScreen" ResizeMode="NoResize">
    <Grid Margin="5">
        <Grid.RowDefinitions>
            <RowDefinition Height="*"></RowDefinition>
            <RowDefinition Height="Auto"></RowDefinition>
        </Grid.RowDefinitions>
        <Label Grid.Row="0"
               Content="{Binding Source={x:Static classes:StaticBinder.Instance}, Path=MSG_Welcome}"></Label>
        <StackPanel Grid.Row="1" HorizontalAlignment="Right" Orientation="Horizontal">
            <Button Name="okayButton"
                    Click="okayButton_Click" Content="{Binding Source={x:Static classes:StaticBinder.Instance}, Path=MSG_OK}"
                    Width="100"
                    Margin="5"></Button>
            <Button Name="cancelButton"
                    Content="{Binding Source={x:Static classes:StaticBinder.Instance}, Path=MSG_Cancel}"
                    Width="100"
                    Margin="5"></Button>
        </StackPanel>
    </Grid>
</Window>

For you to check the actual two-dimensional binding, please follow the code behind implementation below.

public partial class WIN_StaticBinding : Window
{
    public WIN_StaticBinding()
    {
        InitializeComponent();
    }

    private void okayButton_Click(object sender, RoutedEventArgs e)
    {
        Classes.StaticBinder.Instance.MSG_OK = "Okay";
    }
}

You will notice that upon click of the OK button, the changes will then reflect to the UI.

And that's all about it. You just finished reading this blog.