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.