Microsoft .NET framework provides a common runtime functionality for Windows applications. This allows a consistent and comprehensive programming model for Windows developers. FairCom DB SQL now extends .NET support for our stored procedure programming framework which is a more suitable and native environment for Windows developers than our existing cross-platform Java framework.
Key features of this environment include:
- SP, UDF, and triggers can be written in any language that compiles to a CLI.
- SQL classes, similar to ADO, are exposed.
- SP, UDF, and triggers can be debugged by attaching to the server.
- Visual Studio 2012 and later are supported.
With this .NET support, you can use Visual Studio to create, edit, and debug your SP, UDF, and triggers. An API is provided along with a Visual Studio extension for complete .NET development. You can compile on the client and deploy binaries to the server.
Tutorials, which follow the format of the standard FairCom tutorials, are provided. See .NET Tutorials (in this book) and see the ReadMe titled FairCom C# Stored Procedures.
Visual Studio .NET Development
To aid in developing stored procedures (SP), user-defined functions (UDF), and triggers, FairCom provides a "c-tree Stored Procedure" extension to the Visual Studio IDE.
Note: C# .NET stored procedures are available on Microsoft Windows platforms only. If you wish to use stored procedures on other platforms, Java can be used for the stored procedures. See Java Stored Procedures.
Note that the C# stored procedure tutorials that FairCom provides are not executables which can be launched from the command line, like most of the c-tree tutorials are. Instead, these tutorials must be installed into the database from within the Visual Studio IDE and then can be tested from within the Visual Studio IDE.
The .NET Framework 4.0, available from Microsoft, is required for writing SP, UDF, and triggers.
Prerequisite - Configure Microsoft Visual Studio properly
For Visual Studio 2017 to be able to build the tutorials, the ".NET desktop development" workload needs to be installed as part of Visual Studio. In Visual Studio 2017, click Tools > Get Tools and Features, which runs the Visual Studio installer. If the installer requires an update before it will run, allow that update. Find the ".NET desktop development" workload under the “Windows” group and make sure it is checked, as shown here:
If it isn’t checked, give it a check mark and then press the Modify button to install that workload. This ensures that all of the tools needed to build and run these tutorials are installed.
c-tree Stored Procedure Extension Installation
FairCom distributes VSIX package installers, which install the c-tree Stored Procedure extension into Microsoft Visual Studio. These installers have been updated to support Visual Studio: 2017. Users with prior versions should uninstall their existing plug-in (following the instructions below) and install the new version for the latest compatibility. To install this extension, navigate to the <faircom>\drivers\csharp.sql.storedprocs folder with Windows Explorer, and then double-click the .vsix file that matches your version of Visual Studio. This will launch the VSIX Installer. Give it permission to run and make changes, and then click the Install button when you see a window like the following appear:
The install process might download and install one or more .NET Frameworks, so it’s important to be connected to the internet while running this installer. Note that the install process can take several minutes. When the installation is complete, you should see a window like this:
This window is asking you to close Visual Studio (if it’s open) and then restart it, in order for the changes to take effect. In other words, it is asking you to “reboot” Visual Studio.
Note that this extension must be installed into Visual Studio before you load the solution file that contains the c-tree Stored Procedure tutorials.
Note also that this extension should be installed when Visual Studio is not running.
Extension Removal
Should you need to remove this extension, launch Visual Studio and do the following:
- VS 2017 - Go to the Visual Studio Tools menu and select Extensions and Updates.
- Locate the “c-tree Stored Procedure” item template, and click Uninstall, as shown below. Note that the search tool is useful for finding the c-tree item.
- Clicking the Uninstall button will schedule the extension for un-installation. To make the un-install actually happen, exit Visual Studio. This will cause the VSIX Installer to run. Give it permission to run and then click the Modify button when asked.
Note that the un-install process can take several minutes.
.NET Tutorials
Two C# tutorials are included to guide you through the .NET Stored Procedure support. These tutorials are located in the following directory by default:
<faircom>\drivers\csharp.sql.storedprocs\tutorials\
To get started, simply load the Microsoft Visual Solution File labeled Tutorials.sln. The code behind the two tutorials is located in the Stored_Procedures folder:
<faircom>\drivers\csharp.sql.storedprocs\tutorials\Stored_Procedures
To learn more about these tutorials, see the ReadMe titled FairCom C# Stored Procedures.
The image below shows the first tutorial, tutorial_1.cs, loaded in the Solution Explorer:
To execute tutorial_1.cs, right-click tutorial_1.cs under Stored-Procedures and choose Deploy to Server. You should see a screen similar to the following:
Next, right-click tutorial_1.cs under Stored-Procedures again, choose Test Procedure, and click Execute.
After you execute tutorial_1, you should see the following window:
The window shows the resultset obtained from running the tutorial.
Similar procedures can be used to run the other C# tutorial, tutorial_2.cs.
Learn More...
To try these tutorials, see the ReadMe titled FairCom C# Stored Procedures.
Mapping Between SQL Types and .NET Types
| SQL TYPE | .NET Type |
| CHAR | String |
| VARCHAR | String |
| LVC | String |
| CLOB | String |
| NCHAR | String |
| NVARCHAR | String |
| CHAR_CS | String |
| VARCHAR_CS | String |
| NCHAR_CS | String |
| NVARCHAR_CS | String |
| NUMERIC | Nullable<Decimal> |
| MONEY | Nullable<Decimal> |
| SMALLINT | Nullable<Int16> |
| INTEGER | Nullable<Int32> |
| REAL | Nullable<Single> |
| FLOAT | Nullable<Double> |
| DOUBLE PRECISION | Nullable<Double> |
| DATE | Nullable<DateTime> |
| TIME | Nullable<TimeSpan> |
| TIMESTAMP | Nullable<DateTime> |
| TINYINT | Nullable<Int16> |
| BINARY | Byte[] |
| VARBINARY | Byte[] |
| LVB | Byte[] |
| BLOB | Byte[] |
| BIT | Nullable<Boolean> |
| BIGINT | Nullable<Int64> |
The .NET Ctree.SqlSP Assembly
.NET stored procedures rely on an assembly named ctree.SqlSP.dll. This assembly includes a single namespace.
The Ctree.SqlSP namespace exports classes that are specific to .NET Stored Procedures, UDFs, and Triggers:
SqlSpBase
SqlSpBase is the base class for SqlStoredProc, SqlUdf, and SqlTrigger classes.
This class must not be used by itself.
Constructor
SqlSpBase()
Public Methods
Object GetParameterValue(String name)
Return the value of the parameter with the specified name.
Object GetParameterValue(int index)
Return the value of the parameter at the specified index.
SqlSpCommand NewSqlSpCommand()
Return a new object implementing the SqlSpCommand interface to be used to execute SQL commands.
void SetParameterValue(String name, Object value)
Set the value of the parameter with the specified name.
void SetParameterValue(int index, Object value)
Set the value of the parameter at the specified index.
void Err(String message)
Write a character-string message to the SQL log file, sql_server.log. You may include values of variables or return values of methods in the string using the standard concatenation operator (+).
void Log(String message)
Write a character-string message to the SQL log file, sql_server.log. Log includes internal details intended for development use and should be disabled in production. You can include the values of variables or return values of methods in the string using the standard concatenation operator (+).
SqlStoredProc
SqlStoredProc is the class that implements a .NET stored procedure. To create a stored procedure, a new class derived from the SqlStoredProc class must be created. This class must implement the Execute() method, which is the method called by the server. The Visual Studio Extension automatically performs the following:
- It generates the class.
- It generates the Execute() method and adds proper code to exchange the stored procedure parameters with the server.
- It creates an ExecuteSP method, which is the method to be implemented.
SqlStoredProc Class Description
This class inherits from SqlSpBase. It adds the following additional methods.
Constructor
SqlStoredProc()
Public Methods
void NewResultSetRow()
Return an SqlSpRow object having columns defined as per the stored procedure result set definition. See the SqlSpRow description to learn how to obtain access to column definition and values.
void ResultSetAddRow(SqlSpRow row)
Add a row to stored procedure result set. The row content is copied into an internal structure such that the row object can be reused.
SqlUdf
SqlUdf is the class that implements a User Defined Function (UDF). To create a UDF, a new class derived from SqlUdf class must be created. This class must implement the Execute() method, which is the method called by the server. The Visual Studio Extension automatically performs the following:
- It generates the class.
- It generates the Execute() method and adds proper code to exchange the UDF parameters and return code.
- It creates an ExecuteSP method, which is the method to be implemented.
SqlUdf Class Description
This class inherits from SqlSpBase. It adds the following additional methods.
Constructor
SqlUdf()
Public Methods
void SetReturnValue(Object obj)
Set the UDF return value.
SqlTrigger
SqlTrigger is the class that implements a Trigger. To create a Trigger, a new class derived from the SqlTrigger class must be created. This class must implement the Execute() method, which is the method called by the server. The Visual Studio Extension automatically performs the following:
- It generates the class.
- It generates the Execute() method and adds proper code to exchange the Trigger old and new rows (when referenced) and return code.
- It creates an ExecuteSP method for you complete the implementation.
SqlTrigger Class Description
This class inherits from SqlSpBase. It adds the following additional methods.
Constructor
SqlTrigger()
Public
SqlSpRow GetNewRow()
The method above returns an SqlSpRow object containing “newrow” columns’ values. See the SqlSpRow description to learn how to access the columns’ definitions and values.
SqlSpRow GetOldRow()
The method above returns a SqlSpRow object containing “oldrow” columns’ values. See the SqlSpRow description to learn how to access the columns’ definitions and values.
SqlSpRow
SqlSpRow contains the columns of rows.
SqlSpRow Class Description
The enumerator returns an object that allows you to iterate in sequence through the SqlSpColumn objects in the row.
Public Methods
IEnumerator GetEnumerator()
Return an enumerator for the entire row.
Public Properties
SqlSpColumn this[int column]
Return the column number “column”.
int ColumnCount
Return the number of columns defined for the row.
SqlSpColumn
SqlSpColumn contains a column definition and value.
SqlSpColumn Interface Description
Public Read-Only Properties
Nullability IsNullable
Get the column nullability.
String Name
Get the column name.
int Precision
Get the column precision.
int Scale
Get the column scale.
SqlDataType SqlType
Get the column SQL TYPE.
Boolean IsNull
Return if the SQL value is null or not.
Public Read/Write Properties
Object Value
Get/Set the column value. The “object” must be of a type compatible with the column SQL TYPE.
Byte[] BinaryValue
Get/Set a Binary column value.
DateTime? DateValue
Get/Set a Date value.
Double? FloatValue
Get/Set a Float/Double (SQL) column value.
Int32? IntegerValue
Get/Set an Integer column value.
Decimal? MoneyValue
Get/Set a Money column value.
Decimal? NumericValue
Get/Set a Numeric column value.
Boolean? BitValue
Get/Set a Bit column value.
Int64? BigIntValue
Get/Set a BigInt column value.
Single? RealValue
Get/Set a Real column value.
Int16? SmallIntValue
Get/Set a SmallInt column value.
TimeSpan? TimeValue
Get/Set the column value.
DateTime? TimeStampValue
Get/Set a TimeStamp column value.
Int16? TinyIntValue
Get/Set a TinyInt column value.
String CharValue
Get/Set a [Var]Char column value.
SqlSpCommand
SqlSpCommand is the class used to execute SQL commands within Stored Procedures, UDFs, and triggers.
Public Methods
int AffectedRows()
Return the number of rows affected by a command.
int Execute()
Execute a FairCom DB SQL command after it has been prepared with Prepare().
int ExecuteNonQuery(string SqlCommand)
Execute a FairCom DB SQL command directly without the need for a previous preparation of the statement. Should the command generate a result set, it will be automatically closed and there is no possibility to fetch it. In such case use the Prepare() followed by the Execute() methods.
bool IsProcedureCall()
Return an indication if the statement is a SP call or not.
bool IsSelect()
Return an indication if the statement is a “SELECT ...” or not.
bool MoveNext()
Retrieve the next row from cursor.
int Prepare(string SqlCommand)
Prepare a SQL statement for execution. To execute the statement, use Execute().
Public Read-Only Properties
SqlSpRow CurrentRow
Get the current row in a recordset returned by the Execute method.
SqlSpRow Parameter
Get the parameters (organized as a row) for the command (to be set before Execute, and get after Execute).
string ErrorMessage
Return the last error’s message text.
SqlException
SqlException is a class representing fatal errors that occur during execution.
Constructor
SqlException(int errorCode, String ErrorMessage)
Creates a new instance of Ctree.SqlSP.SqlException.
Public Properties
int ErrorCode
The numeric value of the error.