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.