Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Introduction to C# Scripting for SQL Server DBAs


By:   |   Read Comments (5)   |   Related Tips: More > Database Administration

Attend a SQL Server Conference for FREE >> click to learn more


Problem

Since the Visual Studio 2015 Update 1, Microsoft has introduced an interactive C# scripting technology inside the Visual Studio 2015 environment by a dedicated interactive window. In it we can write C# code and run the code on the fly with all C# language features and do something that otherwise would be very difficult to do in other languages. As a SQL Server DBA, is there anything I need to know about this new C# scripting technology?

Solution

C# scripting is an interesting technology that can take advantage of native advanced features of the C# language, such as LINQ, Parallel threads, Lambda expressions, etc. These features can make the life of a SQL Server DBA much easier by creating some innovative solutions to some tough tasks.

We will provide some introduction and examples on how to use C# interactively in Visual Studio 2015.

Environment Setup

First install Visual Studio 2015 Community (or above) version, note the Community version is freeC.

After installation, it is highly recommended to apply the latest Visual Studio 2015 Update 3.

Using C# Interactively

After starting VS 2015, go to menu [View] > [Other Windows] > [C# Interactive], and we will see a new window opened at the bottom, as shown below.

C# interactive window

We can type #help to see the basic keyboard shortcuts and commands.

Two important commands are #r and #load, which will be demonstrated later.

  1. #r is to load assembly files, such as DLL files
  2. #load is to load csx script files, which are text files with C# scripts.

Example 1 - Some Basic DBA Work

In this example, we will explore some DBA tasks, such as finding the largest database in an instance, finding the SQL Server service account, doing a backup, etc.

In the code, we first load the SQL Server SMO DLL, using #r and then using a LINQ query to find the three largest databases on my local SQL Server instance.

#r "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
using Microsoft.SqlServer.Management.Smo;
Server svr = new Server("localhost"); 

var dbs = (from d in svr.Databases.Cast<Database>()
            where d.Status.ToString() == "Normal"
            orderby d.Size descending
            select d).Take(3);

foreach (var d in dbs) { Console.WriteLine($"{d.Name} = {d.Size}MB"); }

If you want to find the service account, just do this.

svr.ServiceAccount

Here is the snapshot running the script, which you can type in one line after another (just as I did).

C# interactive code 2

Now let's do a database full backup. In this example I am doing a backup of database [AdventureWorks2012] on my local default instance.

// do a database backup
#r "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll"
using Microsoft.SqlServer.Management.Common;

BackupDeviceItem deviceitem = new BackupDeviceItem(@"c:\temp\advwks2012_bkup.bak", DeviceType.File);
Backup bkup = new Backup { Database = "AdventureWorks2012", Action = BackupActionType.Database, Incremental = false, Initialize = true, CompressionOption = BackupCompressionOptions.On };
bkup.Devices.Add(deviceitem);

bkup.SqlBackup(svr);

After this you will see a backup file is created as c:\temp\advwks2012_bkup.bak.

C# interactive code 3

Example 2 - Parallel Tasks

Many times we know we can do something in parallel, such as doing database backups, or running multiple scripts. In this example, there are 8 files (named f_n.sql, where n=1 to 8) in a specific folder c:\MyTest, each file has two lines of code like this.

insert into dbo.t (a) values (1); -- 1 for f_1.sql, 2 for f_2.sql and so on so forth until 8 for f_8.sql
waitfor delay '00:00:10';

I also create a table in my [MSSQLTips] database as the following.

use mssqltips
if object_id('dbo.t', 'U') is not null 
 drop table dbo.t;
go
create table dbo.t (a int, d datetime default getdate());
go

When I run each file against this table, it will create a record with a unique value column [a] in this table. Now if I run the 8 files in sequence, it will take at least 80 seconds to finish because in each file, there is a WAITFOR 10 seconds statement.

But if I can execute the 8 files in parallel, assuming I can only start 7 threads at a time, meaning I can execute 7 files simultaneously, then it will take me around 20 seconds to finish because the first 7 simultaneous executions will take 10 seconds, and then the remaining one will take another 10 seconds.

In table dbo.t, I should see 7 rows with almost the same value for [d] column and a value about 10 seconds later for the other rows for the [d] column.

Let's see whether this works as expected. So here is the C# script.

using System.IO;
using System.Data;
using System.Data.SqlClient; 
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
string[] files = Directory.GetFiles(@"c:\Mytest\");

Parallel.ForEach(files, new ParallelOptions { MaxDegreeOfParallelism = 7 }, (cf) =>
{
      string sql = System.IO.File.ReadAllText(cf);
      using (SqlCommand cmd = new SqlCommand(sql))
      {
          using (SqlConnection conn = new SqlConnection("server=.;database=mssqltips;trusted_connection=true"))
          {
              conn.Open();
              cmd.Connection = conn;
              cmd.ExecuteNonQuery();
          }
      }
} );

After running the script, I go to SSMS and do a SELECT * FROM dbo.t, and I can see the following 7 rows with almost the same [d] value around 23:29:28, and one row with [d] value around 23:29:38, i.e. exactly 10 seconds later.

This means the parallel execution indeed works as expected.

C# interactive code 4

Example 3 - SQL Parser

Microsoft has a very powerful SQL parser function library provided via a DLL called Microsoft.SqlServer.TransactSql.ScriptDom.dll. The functions in this DLL are much easier to use with C# than PowerShell, because lots of functions use C# features such as delegates or interface like IList<T> etc.

In the first example, we will look at how to do a re-formatting for a SQL file, such as upper-casing all keywords and adding semi-columns after each statement, etc.

#r "C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
using System.IO;
using Microsoft.SqlServer.TransactSql.ScriptDom;

var sqlparser = new TSql110Parser(false);
TSqlFragment frag;
IList<ParseError> ilError;

using (var sr = new StreamReader(@"c:\temp\test.sql"))
{ frag = sqlparser.Parse(sr, out ilError); }

if (ilError.Count > 0) { Console.WriteLine("Parse failed, cannot continue"); return; }

var gen = new Sql110ScriptGenerator();

using (var tw = new StreamWriter(@"c:\temp\test_fmt.sql"))
{ gen.GenerateScript(frag, tw); }

The original c:\temp\test.sql is like this.

alter database jydb set offline with rollback immediate;

select col_1, col_2, col_3 from dbo.t with (nolock)

insert into dbo.t (a) values (1)

The re-formatted c:\temp\test_fmt.sql is like this.

ALTER DATABASE jydb
    SET OFFLINE 
    WITH ROLLBACK IMMEDIATE;

SELECT col_1,
       col_2,
       col_3
FROM   dbo.t WITH (NOLOCK);

INSERT  INTO dbo.t (a)
VALUES            (1);

In the 2nd example, we will try to remove all comments.

Assume we have a file c:\temp\comment.sql with the following code.

-- the single line comment here
select * from dbo.t;

select col_1, col_2 /*block comment here*/ from dbo.t2;

/*
Multiple line comments 
line 1 comment
-- line 2 comment
line 3 comment
 /* embedded commen here */
 -- *** -- trouble maker

*/
update dbo.t2
set col = 'hello world'
from dbo.t2

After processing with the following C# code.

#r "C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
using System.IO;
using Microsoft.SqlServer.TransactSql.ScriptDom;

var sqlparser = new TSql110Parser(false);
TSqlFragment frag;
IList<ParseError> ilError;

using (var sr = new StreamReader(@"c:\temp\comment.sql"))
{ frag = sqlparser.Parse(sr, out ilError); }

if (ilError.Count > 0) { Console.WriteLine("Parse failed, cannot continue"); return; }

string sql_no_comment = "";
sql_no_comment = string.Join("", (
    from s in frag.ScriptTokenStream
    where s.TokenType != TSqlTokenType.SingleLineComment && s.TokenType != TSqlTokenType.MultilineComment
    select s.Text));
 
using (var tw = new StreamWriter(@"c:\temp\no_comment.sql"))
{ tw.Write(sql_no_comment); }

Now if we open c:\temp\no_comment.sql in SSMS window, we will see the following:

no comment script

While the original c:\temp\comment.sql is like this:

comment script

We can see that all of the comments are gone, no matter how complex the comments are.

There are many other interesting things we can do with ScriptDom DLL, such as counting the number of TRUNCATE/DELETE/UPDATE/INSERT statements or finding tables used in a stored procedure. These are good topics for advanced code analysis.

Batch Mode with CSX Files

We often want to repeat what we have done before. It would be very inconvenient if we had to type the same C# script every time for the same work, so it is better if we can save the script in the interactive window to a file and then run the file directly. Fortunately this is doable, all we need to do is to copy & paste the script into Notepad (or any other text editor) and save it as a .csx file and then start a command window as follows.

In Windows 7, [All programs] > [Visual Studio 2015] > [Visual Studio Tools] > [Developer Command Prompt for VS2015], a new window will open like the following.

C# command window

Let's write a simple csx file in Notepad and save it as c:\temp\HelloWorld.csx.

using System;
string name = "John Dole";
Console.WriteLine($"Hello World ! \r\n {name}");

In the command window run the following.

csi c:\temp\HelloWorld.csx

We will get the following.

CSX file execution

Summary

In this tip, I mainly demonstrated what we can do with a C# script and I hope this tip will intrigue you to dive deeper into the C# language. We DBAs by nature enjoy interactive work (i.e. after I type SELECT * FROM MyTable I expect to see something) while traditionally C# code needs to be programmed, compiled and then run. This process simply does not align well with a DBA's native work habit. but with MS developing (and open-sourcing) this interactive C# technology, we are suddenly able to write C# in a familiar way.

No doubt, C# is a great language, it is arguably more advanced and feature rich than PowerShell. PowerShell has already opened a new window for DBAs to do their work efficiently, and I'd say with C# scripting gaining more attention and investment from Microsoft this may bean even bigger window of opportunity for us DBAs to do our work innovatively.

Next Steps

You can explore the following articles to learn more about C# scripting technology:



Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, December 19, 2016 - 6:41:33 AM - DarkoMartinovic Back To Top

 Thanky your for this tip,

Do you know perhaps what will happen with debugging support in the future? Currently SSMS has better debugging support then c# interactive. And why I need VS in order to be interactive? In my opinion better solution will be if c# interactive will be part of OS and SSMS.

I posted these questions to https://github.com/dotnet/roslyn/wiki/Interactive-Window, but there is no answer.

Thank you in advance

D.

 

 


Friday, December 16, 2016 - 2:00:14 PM - jeff_yao Back To Top

 @Larry, it is only in Visual Studio, for example, you can download the free Visual Studio 2015 Community edition and use it (that what I installed on my computers).

 

Thanks for reading the tip.

 


Friday, December 16, 2016 - 12:53:06 PM - Larry Back To Top

I don't see this option in "Other Windows" In SSMS 2016.  Is it just in regular Visual Studio?  Do you know if there are plans for SSMS?


Tuesday, November 22, 2016 - 12:16:35 PM - jeff_yao Back To Top

 @Timothy, I agree with you. I would say C# script vs PowerShell is like VB vs C++. Both can do lots of things, and in some cases VB beats C++ in terms of convenience and learning curve, but C++ beats VB in many other challenging areas.

C# script at this stage is far from "mature" when doing editing / debuging compared with PowerShell. I just hope MS spends more resources on this area to enrich users' experience when using C# script.


Monday, November 21, 2016 - 5:03:57 PM - Timothy A Wiseman Back To Top

 Thank you for this tip. This may be a strong alternative to powershell for at least some problem sets since C# is more fully featured for certain things, especially analytic work.

 


Learn more about SQL Server tools