By: Jeffrey Yao | Updated: 2016-11-18 | Comments (5) | Related: More > Database Administration
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.
We can type #help to see the basic keyboard shortcuts and commands.
Two important commands are #r and #load, which will be demonstrated later.
- #r is to load assembly files, such as DLL files
- #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).
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.
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.
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:
While the original c:\temp\comment.sql is like this:
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.
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.
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:
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-11-18
