Using Scripting for Working with SQL Server in C#

In this article, I'm going to talk about using Microsoft SQL Server in C# projects through scripting. This can be used on both Windows and macOS. Because Windows is obviously more common for using C# and SQL Server, I'll put more emphasis on the macOS setup. In the end, the code is the same on both platforms.

There are many advantages to using scripting to access a SQL Server instance. Once the scripting framework is set up and running, some of these advantages are:

• Much less code when using scripting to access the SQL Server. Most of the common functionality can be factored out. One statement in a scripting language can execute many C# statements behind the scenes - the scripting language effectively becomes a functional language.
• Any SQL statement that you can use with SQL Server directly can also be used through scripting. The main advantage here is that when the Scripting Engine is integrated into your project, you can run any SQL Server statement at runtime.
• Not only can the syntax in a scripting language be shorter, simpler, and more precise, but it's also completely up to you how you want to define this syntax. This is especially true if you use an open-source scripting language.

Simplicity does not precede complexity but follows it. – Alan Perlis

There is also a disadvantage of using scripting, namely a hit for parsing the actual expression. You're going to see in this article how to decrease this hit using the insertion of multiple rows into a database as an example.

As a scripting language, I'm going to use CSCS (Customized Scripting in C#). I've talked about this language in a few previous CODE Magazine articles (see links in the sidebar). CSCS is an open-source scripting language that's very easy to integrate into your C# project. Being open-source gives you the advantage of modifying it on the fly.

Among others, I'm going to discuss these topics in this article:

• Using SQL Server on macOS, where you run all of your examples (the CSCS code is actually identical on macOS and on Windows).
• How you can avoid SQL Injection in CSCS.
• As an example, you'll see how you can have simple SQL Server cursor functionality in CSCS. It permits a more intuitive syntax than you would have otherwise when using C# directly.
• How to call SQL Server stored procedures from CSCS and how to use custom SQL data types.

Installing Microsoft SQL Server on macOS

Because it's a common task to install and run SQL Server on Windows, here, you're going to see how to install and run it on macOS. Perform the following steps to get SQL Server up and running on your Mac:

1. Install Docker for Mac from here: https://hub.docker.com/editions/community/docker-ce-desktop-mac
2. Run the newly installed Docker Desktop app (it should now be available in your Applications folder). It's usually installed in this location: /usr/local/bin/docker.
3. Download and install SQL Server for the Linux Docker image. This is the command to run from your shell:

docker pull
mcr.microsoft.com/mssql/server:2019-latest

If this doesn't work because of permission issues, try running same command with sudo (you will have to know the admin password for that):

sudo docker pull
mcr.microsoft.com/mssql/server:2019-latest

4. Next run the newly installed Docker image as follows:

docker run -d --name sql_server_demo
-e 'ACCEPT_EULA=Y'
-e 'SA_PASSWORD=mySronggPwd123' -p 1433:1433
mcr.microsoft.com/mssql/server:2019-latest

5. You can also install a command-line SQL Server client from by running this command in your shell:

npm install -g sql-cli

Precede the command above with sudo if you encounter some permission issues. This step is optional, but highly recommended. Install Microsoft Azure Data Studio from: https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver15.

Obviously, there are other free SQL Server GUI Wrappers available, but I found the Azure Data Studio on macOS to be quite powerful and a good competition to the Microsoft SQL Server Management Studio for Windows.

6. Now you can check that everything is up and running as expected. To connect to your server from the command line, use this command:

mssql -u sa -p myStronggPwd123

To check the installed version of Microsoft SQL Server just run:

SELECT @@version

The result of running this command on my system is shown in Figure 1.

Figure 1: Determining the Version of the SQL Server

Alternatively, using Azure Data Studio, you can check the connection by clicking on “New Connection” and then following the connection wizard, as shown in Figure 2. Type the same password that you used when setting up the Microsoft SQL Server Docker image in Step 4 above.

Figure 2: Azure Data Studio Connection Wizard

Setting Up CSCS Scripting Environment with a C# Project

To set up CSCS scripting in your project, download its source code from https://github.com/vassilych/cscs and include it in your project. This is a plain vanilla project that will work for all command-line or background projects (on both Windows and macOS).

One example of including CSCS Scripting Engine in a Windows GUI project is a WPF (Windows Presentation Foundation) project is available here: https://github.com/vassilych/cscs_wpf. Another example is a Xamarin iOS - Android mobile project that can be downloaded from here: https://github.com/vassilych/mobile. The links in the sidebar describe how to use both of these projects.

Once everything is set up, the Xamarin project should look like Figure 3. The Scripting Engine source code is under the scripting.Shared project section. In case of a WPF project, the scripting will be under the CSCS section.

Figure 3: A Xamarin Project with CSCS Scripting in Visual Studio on macOS

The entry point of the CSCS Scripting Engine depends on the project context. For a plain vanilla command-line project, the entry point is the Main.ProcessScript(string script, string filename = "") static method in Main.cs file. For a WPF project, the entry point is the CSCS_GUI.RunScript(string filename) static method in CSCS_GUI.cs file. And for the iOS and Android Xamarin projects, the entry point is CustomInit.InitAndRunScript(string fileName = "start.cscs") static method in the CustomInit.cs file. If you're using sample projects from the GitHub locations mentioned above, the methods that start the CSCS Scripting Engine will be triggered automatically right after starting everything up.

I also recommend that you install a CSCS Debugger and REPL extension for Visual Studio Code. It permits running any CSCS script at runtime (see the download and usage links in the sidebar). To be able to connect to your CSCS project from Visual Studio Code, the following line must be included in the CSCS starting script (by default it will be start.cscs):

StartDebugger();

Setting Up SQL Server DB and Tables

Once the SQL Server is installed and the CSCS Scripting Engine is integrated into your project, you can set up the SQL Server database that you're going to use in the following examples.

You'll do this set up in CSCS itself. All of the SQL Server functions are described in Table 1. First, you create a new Database:

SQLConnectionString("Data Source=localhost,1433;
    Initial Catalog=master;Database=master;
    User Id=sa;Password=myStronggPwd123");

SQLCreateDB("CSCS");

The first statement initializes the SQL Server connection string (using master DB) and the second string creates a new database. You can also run any SQL statement using the SQLNonQuery() function, so an alternative way of creating a database is the following:

SQLNonQuery("CREATE DATABASE CSCS");

Next, you create a table of users. Note that you use the newly created CSCS Database in the SQL Server connection string in order for the new table to be created there:

SQLConnectionString(
    "Data Source=localhost,1433;
    Initial Catalog=CSCS;Database=CSCS;
    User Id=sa; Password=myStronggPwd123");

SQLNonQuery("CREATE TABLE Users (
    ID int NOT NULL IDENTITY PRIMARY KEY,
    FirstName varchar(255),
    LastName varchar(255),
    Email varchar(255),
    Salary real,
    Timestamp datetime not null default current_timestamp)");

Note the way you created the table Users: There's no need to provide user ID nor the Timestamp: for the former; the next consequent integer will be called user I and for the latter, the current time stamp will be used.

Let's populate the newly created table Users with some sample data:

columns = "FirstName,LastName,Email,Salary";
SQLInsert("Users", columns, ["John", "Johnson", "john@gmail.com", 45000]);
SQLInsert("Users", columns, ["Juan", "Perez", "juan@gmail.mx", 75000]);
SQLInsert("Users", columns, ["Johannes", "Dei", "dei@my.ch", 65000]);
SQLInsert("Users", columns, ["Jean", "Cap", "jean@my.fr", 49000]);
SQLInsert("Users", columns, ["Giovanni", "Vasco", "gv@my.it", 34000]);
SQLInsert("Users", columns, ["Joan", "Lee", "joan@my.es", 74000]);

After that, the table looks like Figure 4:

Figure 4: After populating the Users table with sample data

The list of currently implemented CSCS functions related to the SQL Server is shown in Table 1.

Now you're ready to start playing around with SQL using CSCS scripting.

Basic SQL Server Functions

The easiest way to use scripting at runtime after the main program has been started is by using Visual Studio Code CSCS REPL extension. Then you can run a new script after the program has started. It's to use the REPL functionality: Select the code that you want to run and then press Ctrl + 8 on Windows or Command key + 8 on macOS.

Figure 5 shows how you can query the column information of the Users table that you created.

Figure 5: Running CSCS from Visual Studio Code using the CSCS REPL Extension

Running SQLTableColumn(tableName) command returns a list of column names together with the column types. If you only want the column names, supply an additional second argument (“onlyNames” set to true). See Table 1 for details.

The SQLQuery() function is used to run any Select SQL statement and get a list of results. Consider the following SQLQuery() statement:

results = SQLQuery("SELECT FirstName,Salary
    FROM Users WHERE Salary > 50000
    ORDER BY Salary");

After running the statement above, the variable result will be a list containing four elements:

[[FirstName, Salary], [Johannes, 65000], [Joan, 74000] [Juan, 75000]]

Each element of the returned list is also a list itself, each containing two elements for each of the columns in the Select statement. The first element of the returned list always contains the column names and the rest contain the actual rows. So, effectively, the returned list is a two-dimensional array, such as results[0][0] = “FirstName” ; results[1][1] = 65000, and so on.

The SQLNonQuery() function lets you execute all other SQL Server statements. It can be updating a table, deleting rows, creating a new table or a stored procedure, and so on. This function returns the number of rows affected in case of an UPDATE, DELETE, or INSERT statement. Otherwise, it always returns -1.

Here's an example of this function:

updated = SQLNonQuery("UPDATE Users SET Salary = 48500 WHERE ID = 4"); // returns 1

After running the statement above, the variable updated will be equal to 1 (one row affected). You'll see more examples of SQLNonQuery() functions below.

Scripting and SQL Injection

Any of the examples above can potentially be used for SQL injection if one of the fields used in the SQL query or in any other statement has a value taken from a user input field.

SQL Injection is a code injection attack, where attackers can execute malicious SQL statements to either damage the server or to extract data they're not supposed to see.

For instance, in a code injection attack, you might get a user's email from an input field and then extract the user's info as follows:

SQLQuery("SELECT * FROM Users WHERE Email='" + email + "'");

Then a malicious user can enter this text in the email entry:

"x@x.com' or 1=1 --";

Effectively, the SQLQuery gets this argument if the email is used as above:

SQLQuery("SELECT * FROM Users WHERE Email='x@x.com' or 1=1 --'");

This query returns all users, because the condition 1=1 is always true! To avoid this kind of hacker attack, the following remedies can be used:

• Use stored procedures (see the next section).
• Use parametrized parameters. This is what you're going to see next.

To repair the Select statement above, a parametrized call can be used, where you additionally supply a list of parameters to the query. This list consists of lists of two elements, where the first element is the parametrized parameter name (the same as used in the query) and the second element is the actual value. This is how a “safe” SQL query call will look:

email = "juan@gmail.mx";
SQLQuery("SELECT * FROM Users WHERE Email=@0", [["@0", email]]);

Similarly, you can use parameters with any other SQL Server statements. This is how a parametrized call for an Update statement from the previous section looks, now with two parameters:

updated = SQLNonQuery("UPDATE Users SET Salary = @0 WHERE ID = @1", [["@0", 48500], ["@1", 4]]);

Also, when using stored procedures, you can safely pass arguments to them. This is what you're going to see in the next section.

SQL Server Stored Procedures

You can execute any SQL Server stored procedure using the following CSCS function:

SQLProcedure(spName, spArguments);

The second parameter is optional to use when the stored procedure has some arguments. Let's create a SQL Server stored procedure using this CSCS function:

SQLNonQuery("
    CREATE PROCEDURE UpdateUser @id int,
        @Salary real AS
        UPDATE Users SET Salary = @salary
        WHERE ID = @Id");

This updates the salary for a given user in the database. To run this stored procedure, the following CSCS function can be used:

SQLProcedure("UpdateUser", 6, 74550);

That's it! After running this statement, six will be converted to an integer and 74550 to a real number, and the stored procedure will be executed - but all of these parameter-binding details are hidden from the CSCS scripting user (unlike binding them if using C# directly).

Parameter types will be deduced at runtime and bound to SQL by the Scripting Engine.

Let's see a bit more complex example. More complex because of the data structures involved. First, create a custom user table data type:

SQLNonQuery("
    CREATE TYPE [dbo].[UserType] AS TABLE(
        [FirstName] [varchar](255) NULL,
        [LastName] [varchar](255) NULL,
        [Email] [varchar](255) NULL,
        [Salary] [real] NULL )");

Now let's create a stored procedure using this type:

SQLNonQuery("
    CREATE PROCEDURE InsertUsers @tableusers UserType READONLY AS
    INSERT INTO Users (FirstName,LastName,Email,Salary)
    SELECT [FirstName],[LastName],[Email],[Salary] FROM @tableusers ");

The argument to this procedure is of a specific user type, which is a shorter version of the Users table (without the ID and the Timestamp parameters that will be added to the Users table automatically).

To bind this SQL Server User table with a CSCS data, use a list of lists (or a two-dimensional array). This is how you fill out such a list and execute this stored procedure from the CSCS code:

data = [];
data.Add(["Juan Pablo", "Uno", "new1@one.com", 49000]);
data.Add(["Ivan Drago", "Dos", "new2@one.com", 48000]);
data.Add(["Johhny", "Tres", "new3@one.com", 47000]);
SQLProcedure("InsertUsers", data);

As you can see, the CSCS lists can have entries of different types. The CSCS Engine will then bind the appropriate type as defined in the UserType.

As a result, three new entries will be inserted to the Users table in one SQL statement.

SQL Server Cursor Example

Let's see another example: the implementation of the SQL Server Cursor in CSCS.

The first step is the initialization. It consists of initializing the SQL Connection string. The same string will be used for all consecutive SQL calls (see how to initialize it above) and of initializing the SQL cursor with the table name or a specific select statement. If a table name is used, all table rows are returned. Here are examples of the cursor initialization:

id = SQLCursorInit("Users");
id = SQLCursorInit("SELECT Email,Salary FROM Users WHERE Salary > 50000");

The SQLCursorInit() method returns the underlying cursor ID, which will be used for all consequent cursor calls.

To access each consequent record, call SQLCursorNext() function. Here is an example of a SQL Server Cursor session using CSCS:

id = SQLCursorInit("
    SELECT Email,Salary FROM Users WHERE Salary > 50000 order by Salary");
count = SQLCursorTotal(id); // returns 3
current = SQLCursorCurrentRow(id); // returns 0
next = SQLCursorNext(id); // returns [dei@my.ch, 65000]
next = SQLCursorNext(id); // returns [joan@my.es, 74000]
next = SQLCursorNext(id); // returns [juan@gmail.mx, 75000]
next = SQLCursorNext(id); // returns empty string
current = SQLCursorCurrentRow(id); // returns 3
SQLCursorClose(id);

SQLCursorCurrentRow returns a pointer to the current record. Right after initializing the cursor, it's equal to 0. It increments its value with each call to SQLCursorNext(). When it reaches the total number of records (returned by the SQLCursorTotal() function), there are no more records left. After that, the cursor can be closed with the SQLCursorClose() function call.

Note that because CSCS is an open-source language, you can easily change all the CSCS function names (the functions that I use in this article are defined in the Functions.SQL.cs file). This way, you can have your scripting interface be as intuitive as you wish.

A GUI (Graphical User Interface) is always intuitive to those who design it. – Maya Elhalal

How CSCS Works

The CSCS scripting is based on the Split-and-Merge algorithm, described in CODE Magazine here: https://www.codemag.com/Article/1607081/. I'll very briefly describe it.

As input, the algorithm gets a string where all the comments, consecutive spaces, new lines, etc., are removed (unless they are inside of quotes). Then the algorithm performs two steps.

In the first step, a list of tokens is created. Each token consists of a string or a number and an action that will be applied to it. The token separators are all the mathematical operators ('+', '-', '*', '/', '=', etc.), parentheses, and some other special characters. The extracted token can be any number, a string, or a function. If the extracted token is a function or an expression in parentheses, the whole algorithm is applied to evaluate this function or a value in parentheses recursively (and to all function arguments).

All of the functions must be registered with the Parsing Engine. If the extracted token is a function, its value is evaluated right away by invoking the corresponding Evaluate() method of the implementing function object. In the next section, you'll see how it's done and an example of a function implementation.

In the second step, the resulting list of tokens will be merged. Note that the last item of this list always has a null action. In cases when the script consists of just one function, no merging is needed: the resulting last item is returned as the final answer (it can be nothing if a function invoked doesn't return any value). Otherwise, you continuously merge the two leftmost elements into one, applying the action of the element on the left, if its priority isn't lower than the priority of the action of the element on the right. If this isn't the case, you merge the element on the right with its right neighbor (and so on, recursively) and then retry merging the left element and the newly obtained right one. The element resulting from the merging of two elements will have the priority of the element on the right. Eventually, you get a list with just one element that will be the final result.

Let's see an example of evaluating expression 10 - 3 * 2. The first step gives this list of tokens together with their actions: (10, -), (3, *), and (2, null). The last token has always null action that has the lowest priority.

Because the priority of - is lower than the priority of *, you can't merge the first and second elements of the list right away, so you need first to merge the next tokens on the right. The priority of * is greater than the priority of the null action, so you can merge cells (3, *) and (2, null). The resulting cell will be (3 * 2, null) = (6, null). Now you get back and merge cells (10, -) with the new cell (6, null). The resulting cell will be (10 - 6, null) = (4, null), and the final answer will be 4.

Extending and Modifying CSCS

It's easy to extend or modify CSCS scripting functionality. There are just two steps needed to implement a new function in CSCS:

• Implement the Evaluate() method of a new class extending the ParserFunction class.
• Register this new class with the CSCS Parser Engine.

Let's see how to do this with a relatively short SQLNonQuery() function implementation. First, here's the class implementing it:

class SQLNonQueryFunction : ParserFunction
{
    protected override Variable Evaluate(ParsingScript script) 
    {
        var args = script.GetFunctionArgs();
        var stmt = Utils.GetSafeString(args,0);
        int result = 0;
        var sp = SQLQueryFunction.GetParameters(args);
        using (SqlConnection con = new SqlConnection(CSCS_SQL.ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand(stmt, con)) 
            {
                if (sp != null)
                cmd.Parameters.AddRange(sp.ToArray());
                con.Open();
                result = cmd.ExecuteNonQuery();
            }
        }
        return new Variable(result);
    }
}

Second, register this class with the Parsing Engine as follows:

ParserFunction.RegisterFunction("SQLNonQuery", new SQLNonQueryFunction());

This call is usually done in an Init() method during the system start up (e.g., in Interpreter.Init() in the Interpreter.cs file).

Wrapping Up

Using CSCS scripting, you can greatly decrease the number of lines of code needed to perform different SQL operations. But there's also a slight performance hit that must be taken into account: parsing the actual CSCS expression.

The most amazing achievement of the computer software industry is its continuing cancellation of the steady and staggering gains made by the computer hardware industry. – Henry Petroski

You saw one way that this hit can be avoided in this article. In the case of multiple Insert statements, you created a SQL stored procedure that inserts multiple records at once. Similarly, you can factor out other time-consuming operations into a SQL stored procedure code.

Another way of improving scripting performance is by pre-compiling CSCS scripts at the initialization phase. You can read details on how you can do that in this CODE Magazine article: https://www.codemag.com/Article/2001071/Compiling-Scripts-to-Get-Compiled-Language-Performance .

I'm looking forward to your feedback, especially how you're using CSCS scripting in your projects and what performance tricks you're using.