Getting Started



Connection Setup



Execution Functions



Query Functions



Extra Features



Getting Started


To get started download DotNetSDB either from GitHub or Nuget and reference the namespaces below, then you are setup to be able to start using DotNetSDB.

// Generic required namespace
using DotNetSDB;

// If you want to use the error logging features
using DotNetSDB.output;

// If you want to use the DatabaseConnector feature
using DotNetSDB.Connector;
    

Examples


This section helps to give some useful examples of how you could use DotNetSDB.

Connection Examples


SQL Server SQL User Connection Example


This example demonstrates how to setup a generic connection to a SQL Server database using an SQL user account.

// Sets up the error logger settings
OutputManagementSettings settings = new OutputManagementSettings("./", "database-log", TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time"), true);

// Sets up the database connection
SQLServerUserConnection con = new SQLServerUserConnection("server here", "username here", "password here", "database name here", 30, settings);

// Initialises the database connection
using (SQLServer2016 db = new SQLServer2016(con))
{
    db.add_select("tableName", "*");
    DataTable results = db.run_return_datatable();
}

SQL Server Windows Connection Example


This example demonstrates how to setup a generic connection to a SQL Server database using Windows authentication.

// Sets up the error logger settings
OutputManagementSettings settings = new OutputManagementSettings("./", "database-log", TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time"), true);

// Sets up the database connection
SQLServerWindowsConnection con = new SQLServerWindowsConnection("host server here", "database name here", 30, settings);

// Initialises the database connection
using (SQLServer2016 db = new SQLServer2016(con))
{
    db.add_select("tableName", "*");
    DataTable results = db.run_return_datatable();
}

MySQL SQL User Connection Example


This example demonstrates how to setup a generic connection to a MySQL database using an SQL user account.

// Sets up the error logger settings
OutputManagementSettings settings = new OutputManagementSettings("./", "database-log", TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time"), true);

// Sets up the database connection
MySQLUserConnection con = new MySQLUserConnection("server here", "username here", "password here", "database name here", 30, settings);

// Initialises the database connection
using (MySQLCoredb = new MySLQCore(con))
{
    db.add_select("tableName", "*");
    DataTable results = db.run_return_datatable();
}

Model Examples


SQL Server Information Table Model Example


This example demonstrates how you can create a database table model for SQL Server with the flexibility to do whatever you require, this demonstrates how to create, delete, insert, update, truncate and get data from a database table using DotNetSDB, please note though that this is a very simple example and DotNetSDB can be used for much more complex requirements if needed.

using DotNetSDB;
using System;
using System.Data;
namespace Models.InformationDBModel
{
    public partial class InformationDBModel
    {
        // Stores the table name
        string table = "information";

        // Stores the local connection variables
        SQLServerUserConnection userCon = null;
        SQLServerWindowsConnection windowsCon = null;

        // Initialisations a database connection using whichever connection we have supplied
        SQLServer2016 DBInit
        {
            get
            {
                if(userCon == null)
                {
                    return new SQLServer2016(windowsCon);
                }
                else
                {
                    return new SQLServer2016(userCon);
                }
            }
        }

        // Constructors that deal with getting the connection into one of our local variables ready for the database initialisation
        public InformationDBModel(SQLServerUserConnection con)
        {
            if(con != null)
            {
                userCon = con;
            }
            else
            {
                throw new System.Exception("Must be a valid connection object.");
            }
        }
        public InformationDBModel(SQLServerWindowsConnection con)
        {
            if (con != null)
            {
                windowsCon = con;
            }
            else
            {
                throw new System.Exception("Must be a valid connection object.");
            }
        }

        // This function deals with creating the database table
        public bool CreateTables(bool dropTableIfExist = false)
        {
            try
            {
                using (SQLServer2016 db = DBInit)
                {
                    if (db.table_exist(table) && dropTableIfExist)
                    {
                        db.add_drop_table(table);
                        db.run();
                    }
                    else if (db.table_exist(table))
                    {
                        // Cannot create new table as it exists and the user does not want us to drop it
                        throw new Exception(string.Format("Cannot create the {0} table as it already exists and the drop flag is false.", table));
                    }

                    db.add_create_table(table, new string[] { "information_id", "value" },
                                               new string[] { "int Identity(1,1) PRIMARY KEY not null", "varchar(max)" });
                    db.run();
                    return true;
                }
            }
            catch { }
            return false;
        }

        // This function deals with reseting the database table
        public bool ResetAllTable()
        {
            try
            {
                using (SQLServer2016 db = DBInit)
                {
                    db.truncate_table(table);
                    return true;
                }
            }
            catch { }
            return false;
        }

        // This function deals with updating the database record
        public bool Update(string id, string text)
        {
            try
            {
                // Checks if it already exist before trying to update the record                
                if (!string.IsNullOrWhiteSpace(text) && Exists(id))
                {
                    using (SQLServer2016 db = DBInit)
                    {
                        // Runs the update
                        db.add_update(table, "value", text);
                        db.add_where_normal(table, "information_id", id);
                        db.run();

                        // If no failures have been thrown then return true
                        return true;
                    }
                }
            }
            catch { }
            return false;
        }

        // This gets all the records in the table
        public DataTable GetAll()
        {
            try
            {
                using (SQLServer2016 db = DBInit)
                {
                    db.add_select(table, "*");
                    DataTable results = db.run_return_datatable();
                    results.TableName = table;
                    return results;
                }
            }
            catch { }
            return null;
        }

        // This gets a specific value from a row ID
        public string GetValue(string id)
        {
            try
            {
                using (SQLServer2016 db = DBInit)
                {
                    db.add_select(table, "value");
                    db.add_where_normal(table, "information_id", id);
                    return db.run_return_string();
                }
            }
            catch { }
            return null;
        }

        // This function checks if the record id exist in the database
        public bool Exists(string id)
        {
            try
            {
                using (SQLServer2016 db = DBInit)
                {
                    db.add_select(table, "information_id");
                    db.add_where_normal(table, "information_id", id);
                    string temp = db.run_return_string();
                    if (!string.IsNullOrWhiteSpace(temp))
                    {
                        return true;
                    }
                }
            }
            catch { }
            return false;
        }

        // This function deletes the specific record in the database
        public bool Delete(string id)
        {
            try
            {
                // Checks the id is not empty and that we have a record for the specified ID before continuing
                if (!string.IsNullOrWhiteSpace(id) && Exists(id))
                {
                    using (SQLServer2016 db = DBInit)
                    {
                        db.add_delete(table);
                        db.add_where_normal(table, "information_id", id);
                        db.run();

                        return true;
                    }
                }
            }
            catch { }
            return false;
        }

        // This function adds another row in the database
        public bool Add(string text)
        {
            try
            {
                if (!string.IsNullOrWhiteSpace(text))
                {
                    using (SQLServer2016 db = DBInit)
                    {
                        db.add_insert(table, "value", text);
                        db.run();

                        return true;
                    }
                }
            }
            catch { }
            return false;
        }
    }
}

MySQL Information Table Model Example


This example helps to demonstrates how you can create a database table model for MySQL with the flexibility to do whatever you require, this demonstrates how to create, delete, insert, update, truncate and get data from a database table using DotNetSDB, please note though that this is a very simple example and DotNetSDB can be used for much more complex requirements if needed.

using DotNetSDB;
using System;
using System.Data;
namespace Models.InformationDBModel
{
    public partial class InformationDBModel
    {
        // Stores the table name
        string table = "information";

        // Stores the local connection variables
        MySQLUserConnection userCon = null;

        // Initialisations a database connection using whichever connection we have supplied
        MySQLCoreDBInit
        {
            get
            {
                return new MySLQCore(userCon);
                
            }
        }

        // Constructor that deal with getting the connection into one of our local variables ready for the database initialisation
        public InformationDBModel(MySQLUserConnection con)
        {
            if(con != null)
            {
                userCon = con;
            }
            else
            {
                throw new Exception("Must be a valid connection object.");
            }
        }        

        // This function deals with creating the database table
        public bool CreateTables(bool dropTableIfExist = false)
        {
            try
            {
                using (MySQLCoredb = DBInit)
                {
                    if (db.table_exist(table) && dropTableIfExist)
                    {
                        db.add_drop_table(table);
                        db.run();
                    }
                    else if (db.table_exist(table))
                    {
                        // Cannot create new table as it exists and the user does not want us to drop it
                        throw new Exception(string.Format("Cannot create the {0} table as it already exists and the drop flag is false.", table));
                    }

                    db.add_create_table(table, new string[] { "information_id", "value" },
                                               new string[] { "NOT NULL AUTO_INCREMENT PRIMARY KEY", "varchar(max)" });
                    db.run();

                    return true;
                }
            }
            catch { }
            return false;
        }

        // This function deals with reseting the database table
        public bool ResetAllTable()
        {
            try
            {
                using (MySQLCoredb = DBInit)
                {
                    db.truncate_table(table);
                    return true;
                }
            }
            catch { }
            return false;
        }

        // This function deals with updating the database record
        public bool Update(string id, string text)
        {
            try
            {
                // Checks if it already exist before trying to update the record                
                if (!string.IsNullOrWhiteSpace(text) && Exists(id))
                {
                    using (MySQLCoredb = DBInit)
                    {
                        // Runs the update
                        db.add_update(table, "value", text);
                        db.add_where_normal(table, "information_id", id);
                        db.run();

                        // If no failures have been thrown then return true
                        return true;
                    }
                }
            }
            catch { }
            return false;
        }

        // This gets all the records in the table
        public DataTable GetAll()
        {
            try
            {
                using (MySQLCoredb = DBInit)
                {
                    db.add_select(table, "*");
                    DataTable results = db.run_return_datatable();
                    results.TableName = table;
                    return results;
                }
            }
            catch { }
            return null;
        }

        // This gets a specific value from a row ID
        public string GetValue(string id)
        {
            try
            {
                using (MySQLCoredb = DBInit)
                {
                    db.add_select(table, "value");
                    db.add_where_normal(table, "information_id", id);
                    return db.run_return_string();
                }
            }
            catch { }
            return null;
        }

        // This function checks if the record id exist in the database
        public bool Exists(string id)
        {
            try
            {
                using (MySQLCoredb = DBInit)
                {
                    db.add_select(table, "information_id");
                    db.add_where_normal(table, "information_id", id);
                    string temp = db.run_return_string();
                    if (!string.IsNullOrWhiteSpace(temp))
                    {
                        return true;
                    }
                }
            }
            catch { }
            return false;
        }

        // This function deletes the specific record in the database
        public bool Delete(string id)
        {
            try
            {
                // Checks the id is not empty and that we have a record for the specified ID before continuing
                if (!string.IsNullOrWhiteSpace(id) && Exists(id))
                {
                    using (MySQLCoredb = DBInit)
                    {
                        db.add_delete(table);
                        db.add_where_normal(table, "information_id", id);
                        db.run();

                        return true;
                    }
                }
            }
            catch { }
            return false;
        }

        // This function adds another row in the database
        public bool Add(string text)
        {
            try
            {
                if (!string.IsNullOrWhiteSpace(text))
                {
                    using (MySQLCoredb = DBInit)
                    {
                        db.add_insert(table, "value", text);
                        db.run();

                        return true;
                    }
                }
            }
            catch { }
            return false;
        }
    }
}

OutputManagement


OutputManagement is a simple error logging object, its settings object can be passed to any DotNetSDB database connection to add error logging features to that particular connection. This can be quite useful as SQL statements which fail due to errors will be physically logged with the exception which was thrown at the time of its failure so we have a record of why it failed.

Namespace

using DotNetSDB.output;

Parameter Syntax

directoryPath: The directory path the logs will be saved to.

theLogName: The name that will be added to the start of the log file.

timezone: The TimeZoneInfo you would like to use for the DateTime outputs on the logs.

multiLineLogging: The flag for if an error that is logged should be on one line or on multiple lines for readability and size.

cleanUpDaysAmount: If the variable is set to 0 it means no clean up is required, otherwise everytime an entry is added to the error logger it checks all the previous log file dates in the specified directory and removes the ones that are older than the specified amount of days from the current datetime that has been passed.

OutputManagementSettings(string directoryPath, string theLogName, TimeZoneInfo timezone, bool multiLineLogging = false, int cleanUpDaysAmount = 0)

Initialising Examples

Basic Example

OutputManagementSettings settings = new OutputManagementSettings("local full folder path", "the name of the log file", TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time"));

Multiline Example

OutputManagementSettings settings = new OutputManagementSettings("local folder path", "the name of the log file", TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time"), true);

Log Cleaning Example

Everytime an error entry is logged it checks the cleaning proccedure to see if the current logs already in the directory are not within our accepted date range and if so removes them.

// Multiline 30 day log cleaning settings
OutputManagementSettings settings = new OutputManagementSettings("local folder path", "the name of the log file", TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time"), true, 30);

// single line 30 day log cleaning settings
OutputManagementSettings settings = new OutputManagementSettings("local folder path", "the name of the log file", TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time"), false, 30);

MySQL Connections


There is only one method to connect to a MySQL database using DotNetSDB which is by using a SQL User connection.

OutputManagementSettings: This parameter is optional and is not required to create a database connection in DotNetSDB but if supplied does allow physical error logging for SQL failures.

SQL User


This connection is only to be used for trying to connect with a MySQL instance as a SQL user.

Default Syntax

Note: This uses the default MySQL port.

MySQLUserConnection(string serverName, string username, string password, string databaseName, int connectionTimeout = 30, OutputManagementSettings errorLogger = null, string , string additionalConnectionString = null)

Port Version Syntax

MySQLUserConnection(string serverName, string username, string password, string databaseName, string thePort, int connectionTimeout = 30, OutputManagementSettings errorLogger = null, string , string additionalConnectionString = null)

Generic Example

// Connection String: Server=myDBServer;Database=myDatabaseName;UId=sqlUsername;Pwd=sqlPassword;Connection Timeout=30;AllowZeroDateTime=true;ConvertZeroDatetime=True;

MySQLUserConnection con = new MySQLUserConnection("myDBServer", "sqlUsername", "sqlPassword", "myDatabaseName");
using(MySQLCore db = new MySQLCore(con))
{
}

Port Example

// Connection String: Server=myDBServer,9999;Database=myDatabaseName;UId=sqlUsername;Pwd=sqlPassword;Connection Timeout=30;AllowZeroDateTime=true;ConvertZeroDatetime=True;

MySQLUserConnection con = new MySQLUserConnection("myDBServer", "sqlUsername", "sqlPassword", "myDatabaseName", "9999");
using(MySQLCore db = new MySQLCore(con))
{
}

Error Logging Example

This is similar to the methods above except it adds internal error logging with the use of the OutputManagement object.

// Connection String: Server=myDBServer,9999;Database=myDatabaseName;UId=sqlUsername;Pwd=sqlPassword;Connection Timeout=60;AllowZeroDateTime=true;ConvertZeroDatetime=True;

MySQLUserConnection con = new MySQLUserConnection("myDBServer", "sqlUsername", "sqlPassword", "myDatabaseName", "9999", 60, errorLogger);
using(MySQLCore db = new MySQLCore(con))
{
}

Additional Connection String Example

This is similar to the methods above except it adds additional connection string information.

// Connection String: Server=myDBServer,9999;Database=myDatabaseName;UId=sqlUsername;Pwd=sqlPassword;Connection Timeout=60;AllowZeroDateTime=true;ConvertZeroDatetime=True;extraConnectionString=abc;

MySQLUserConnection con = new MySQLUserConnection("myDBServer", "sqlUsername", "sqlPassword", "myDatabaseName", "9999", 60, errorLogger, "extraConnectionString=abc");
using(MySQLCore db = new MySQLCore(con))
{
}

MySQL Instances


At current there is only 1 main MySQL database instance version in DotNetSDB, all generic SQL functions are inherited from the base SQL object but additional features such as Limit are apart of the MySQL instance.

Initialising The MySQL Instance

To use the supported MySQL instance in DotNetSDB simply use the following example below with a SQL User connection.

MySQL

using(MySQLCore db = new MySQLCore(con))
{
}
    

SQL Server Connections


There are two methods of connecting to a SQL Server database using DotNetSDB. One is using a SQL User connection type and the other is using the windows type.

OutputManagementSettings: This parameter is optional and is not required to create a database connection in DotNetSDB but if supplied does allow physical error logging for SQL failures.

SQL User


This connection is only to be used for trying to connect with a SQL Server instance as a SQL user.

Default Syntax

Note: This uses the default SQL Server port.

SQLServerUserConnection(string serverName, string username, string password, string databaseName, int connectionTimeout = 30, OutputManagementSettings errorLogger = null, string , string additionalConnectionString = null)

Port Version Syntax

SQLServerUserConnection(string serverName, string username, string password, string databaseName, string thePort, int connectionTimeout = 30, OutputManagementSettings errorLogger = null, string , string additionalConnectionString = null)

Generic Example

// Connection String: Server=myDBServer;Database=myDatabaseName;User Id=sqlUsername;Password=sqlPassword;connection timeout=30;

SQLServerUserConnection con = new SQLServerUserConnection("myDBServer", "sqlUsername", "sqlPassword", "myDatabaseName");
using(SQLServer2016 db = new SQLServer2016(con))
{
}

Port Example

// Connection String: Server=myDBServer,9999;Database=myDatabaseName;User Id=sqlUsername;Password=sqlPassword;connection timeout=30;

SQLServerUserConnection con = new SQLServerUserConnection("myDBServer", "sqlUsername", "sqlPassword", "myDatabaseName", "9999");
using(SQLServer2016 db = new SQLServer2016(con))
{
}

Error Logging Example

This is similar to the methods above except it adds internal error logging with the use of the OutputManagement object.

// Connection String: Server=myDBServer,9999;Database=myDatabaseName;User Id=sqlUsername;Password=sqlPassword;connection timeout=60;

SQLServerUserConnection con = new SQLServerUserConnection("myDBServer", "sqlUsername", "sqlPassword", "myDatabaseName", "9999", 60, errorLogger);
using(SQLServer2016 db = new SQLServer2016(con))
{
}

Additional Connection String Example

This is similar to the methods above except it adds additional connection string information.

// Connection String: Server=myDBServer,9999;Database=myDatabaseName;User Id=sqlUsername;Password=sqlPassword;connection timeout=60;extraConnectionString=abc;

SQLServerUserConnection con = new SQLServerUserConnection("myDBServer", "sqlUsername", "sqlPassword", "myDatabaseName", "9999", 60, errorLogger, "extraConnectionString=abc");
using(SQLServer2016 db = new SQLServer2016(con))
{
}

Windows User


This connection is only to be used for trying to connect with a SQL Server instance as a Windows user.

Default Syntax

Note: This uses the default SQL Server port.

SQLServerWindowsConnection(string serverName, string database, int connectionTimeout = 30, OutputManagementSettings errorLogger = null, string , string additionalConnectionString = null)

Port Version Syntax

SQLServerWindowsConnection(string serverName, string database, string thePort, int connectionTimeout = 30, OutputManagementSettings errorLogger = null, string , string additionalConnectionString = null)

Generic Example

// Connection String: Server=myDBServer;Database=myDatabaseName;Integrated Security=SSPI;connection timeout=30;

SQLServerWindowsConnection con = new SQLServerWindowsConnection("myDBServer", "myDatabaseName");
using(SQLServer2016 db = new SQLServer2016(con))
{
}

Port Example

// Connection String: Server=myDBServer,9999;Database=myDatabaseName;Integrated Security=SSPI;connection timeout=30;

SQLServerWindowsConnection con = new SQLServerWindowsConnection("myDBServer", "myDatabaseName", "9999");
using(SQLServer2016 db = new SQLServer2016(con))
{
}

Error Logging Example

This is similar to the methods above except it adds internal error logging with the use of the OutputManagement object.

// Connection String: Server=myDBServer,9999;Database=myDatabaseName;Integrated Security=SSPI;connection timeout=60;

SQLServerWindowsConnection con = new SQLServerWindowsConnection("myDBServer", "myDatabaseName", "9999", 60, errorLogger);
using(SQLServer2016 db = new SQLServer2016(con))
{
}

Additional Connection String Example

This is similar to the methods above except it adds additional connection string information.

// Connection String: Server=myDBServer,9999;Database=myDatabaseName;Integrated Security=SSPI;connection timeout=60;extraConnectionString=abc;

SQLServerWindowsConnection con = new SQLServerWindowsConnection("myDBServer", "myDatabaseName", "9999", 60, errorLogger, "extraConnectionString=abc");
using(SQLServer2016 db = new SQLServer2016(con))
{
}

SQL Server Instances


At current there are 4 supported database instance versions in DotNetSDB, all generic SQL functions are inherited from the base SQL object but different instances might have additional features such as Offset in 2012+.

Initialising Different Instances

To use any of the supported instances of SQL Server in DotNetSDB simply use any of the following with either a SQL User connection or a Windows User connection.

2008

using(SQLServer2008 db = new SQLServer2008(con))
{
}
    

2012

using(SQLServer2012 db = new SQLServer2012(con))
{
}
    

2014

using(SQLServer2014 db = new SQLServer2014(con))
{
}
    

2016

using(SQLServer2016 db = new SQLServer2016(con))
{
}
    

Execution Methods


DotNetSDB has many different execution functions to help give flexibility in returning data in various different formats.

run


This function is the same in all MySQL and SQL Server instances

This function executes the sql that has been built and does not return any data back.

Parameter Syntax

void run();

General Example

db.add_delete("myTableName");
db.add_where_normal("myTableName", "columnOne", 1);
db.run();

run_return_datatable


This function is the same in all MySQL and SQL Server instances

This function executes the sql that has been built and returns all the results in a DataTable format.

Parameter Syntax

DataTable run_return_datatable()

General Example

db.add_select("myTableName", "columnOne");
DataTable results = db.run_return_datatable();

run_return_dataset


This function is the same in all MySQL and SQL Server instances

This function executes the sql that has been built and returns all the results in a DataSet format.

Parameter Syntax

Note: The enforceConstraints parameter simply sets the "DataSet.EnforceConstraints" option before populating and returning the data.

DataSet run_return_dataset(bool enforceConstraints = true)

General Example

db.add_select("myTableName", "*");
db.start_new_query();
db.add_select("myTableName", "columnOne");

// This returns a DataSet with two DataTables inside it for the two different queries compiled in DotNetSDb
DataSet results = db.run_return_dataset();

run_return_string


This function is the same in all MySQL and SQL Server instances

This function executes the sql that has been built and returns the first value in a string format.

Parameter Syntax

string run_return_string()

General Example

db.add_select("myTableName", "columnOne");
db.add_where_normal("myTableName", "columnTwo", 1);
string result = db.run_return_string();

run_return_string_array


This function is the same in all MySQL and SQL Server instances

This function executes the sql that has been built and returns all the results for the first column in a string array format.

Parameter Syntax

string[] run_return_string_array()

General Example

db.add_select("myTableName", "columnOne");
string[] results = db.run_return_string_array();

run_return_json


This function is the same in all MySQL and SQL Server instances

This function executes the sql that has been built and returns all the results in a json string format.

Parameter Syntax

string run_return_json()

General Example

db.add_select("myTableName", "columnOne");
string jsonString = db.run_return_json();

run_return_dynamic


This function is the same in all MySQL and SQL Server instances

This function executes the sql that has been built and returns all the results in a List of dynamic object formats.

Parameter Syntax

List<dynamic> run_return_dynamic()

General Example

db.add_select("users", "*");
var dObjects = db.run_return_dynamic();

// Checks we have results back
if (dObjects != null && dObjects.Count > 0)
{
    // Pulls the first value of the columnOne on the first row
    var tempValue = dObjects[0].columnOne;
}

run_to_csv


This function is the same in all MySQL and SQL Server instances

This function executes the sql that has been built and writes the results to the specified file path in a CSV format.

Parameter Syntax

bool run_to_csv(string fullFilePath, string delimiter = ",")

General Example

db.add_select("myTableName", "*");
db.run_to_csv(@"C:\1-example.csv");

run_append_to_csv


This function is the same in all MySQL and SQL Server instances

This function executes the sql that has been built and appends the results to the specified file in a CSV format.

Parameter Syntax

bool run_to_csv(string fullFilePath, string delimiter = ",")

General Example

db.add_select("myTableName", "*");
db.run_to_csv(@"C:\1-example.csv");

// Adds more results to the same file
db.add_select("myTableName", "*");
db.run_append_to_csv(@"C:\1-example.csv");

run_bulk_copy


This function is only supported in Sql Server instances

This function bulk copies the data that is in a specified DataTable.

Parameter Syntax

sourceData: The DataTable that holds all the data to be inserted "the real column and table names need to match whats in the DataTable".

batchSize: The max records to insert at a time.

timeoutSeconds: The maximum timeout per batch insert.

void run_bulk_copy(DataTable sourceData, int batchSize = 500, int timeoutSeconds = 30)

General Example

// Creates the normal table
db.add_create_table("myTableName", "columnOne", "int not null");
db.run();

// Creates the backup table
db.add_create_table("myTableName_backup", "columnOne", "int not null");
db.run();

// Adds various different values into the table
db.add_insert("myTableName", "columnOne");
db.add_insert_values(1);
db.add_insert_values(2);
db.add_insert_values(3);
db.add_insert_values(4);
db.run();

// Pulls all the results from the table
db.add_select("myTableName", "*");
DataTable table = db.run_return_datatable();

// Sets the name to the table we will be uploading to "so the table name and all the columns match the new table"
table.TableName = "myTableName_backup";

// Does a bulk copy of the table contents to the new table
db.run_bulk_copy(table);

run_procedure


This function is the same in all MySQL and SQL Server instances

This function executes a stored procedure with the specified name without returning any results.

Parameter Syntax

Note: The parameters are the values you want to pass to run the stored procedure.

void run_procedure(string procedureName, Dictionary theParameters = null)

General Example

// Runs the stored procedure and gets the results back
db.run_procedure("myTableName_procedure");

Parameter Example

// Sets up the parameter
var parameters = new Dictionary() { { "columnOne", 2 } };

// Runs the stored procedure
db.run_procedure("myTableName_procedure", parameters);

run_procedure_return_datatable


This function is the same in all MySQL and SQL Server instances

This function executes a stored procedure with the specified name and returns the results in a DataTable format.

Parameter Syntax

Note: The parameters are the values you want to pass to run the stored procedure.

DataTable run_procedure_return_datatable(string procedureName, Dictionary theParameters = null)

General Example

// Runs the stored procedure and gets the results back
db.run_procedure_return_datatable("myTableName_procedure");

Parameter Example

// Sets up the parameter
var parameters = new Dictionary() { { "columnOne", 2 } };

// Runs the stored procedure and gets the results back
var table = db.run_procedure_return_datatable("myTableName_procedure", parameters);

run_raw_output


This function is the same in all MySQL and SQL Server instances

This function executes the sql that has been built and returns all the data into a concatenated string format seperated by a delimiter.

Parameter Syntax

string run_raw_output(string delimiter = ",")

General Example

db.add_select("myTableName", "*");
string raw = db.run_raw_output();

start_new_query


This function is the same in all MySQL and SQL Server instances

This function is used to seperate between multiple queries that are all being called in a single execution.

Parameter Syntax

void start_new_query()

General Example

/*SQL:
    select
        myTableName.*
    from myTableName;

    select
        myTableName.columnOne
    from myTableName;
*/
db.add_select("myTableName", "*");

// Ends the current query and starts the next one
// NOTE: this does not execute the query, it simply seperates the queries by a ";" in the same query.
db.start_new_query();

db.add_select("myTableName", "columnOne");

is_distinct


This function is the same in all MySQL and SQL Server instances

Determines if the current query should use a distinct in the query, if not used the default is false so no distinct will appear.

Parameter Syntax

void is_distinct(bool distinct)

General Example

/*SQL: 
    select distinct 
        myTableName.columnOne 
    from myTableName
*/
db.is_distinct(true);
db.add_select("myTableName", "columnOne");

add_select


This function is the same in all MySQL and SQL Server instances

Parameter Syntaxs

Note: The startField and endField are both optional and are not required.

// Single version
void add_select(string tableName, string selectField, string startField = null, string endField = null)

// Multiple field version
void add_select(string tableName, string[] selectFields, string[] startFields = null, string[] endFields = null)

Single Column Example

/*SQL: 
    select 
        myTableName.* 
    from myTableName
*/
db.add_select("myTableName", "*");

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
*/
db.add_select("myTableName", "columnOne");

Multiple Column Example

/*SQL: 
    select 
        myTableName.columnOne, 
        myTableName.columnTwo 
    from myTableName
*/
db.add_select("myTableName", new string[] {"columnOne", "columnTwo"});

Start and End Example

Single Example

/*SQL: 
    select 
        (CASE myTableName.columnOne WHEN 1 THEN 0 ELSE 1 END) as calculatedColumn 
    from myTableName
*/
db.add_select("myTableName", "columnOne", "(CASE ", " WHEN 1 THEN 0 ELSE 1 END) as calculatedColumn");

Multiple Column Example

/*SQL: 
        select 
            (CASE myTableName.columnOne WHEN 1 THEN 0 ELSE 1 END) as calculatedColumnOne,  
            (CASE myTableName.columnTwo WHEN 1 THEN 0 ELSE 1 END) as calculatedColumnTwo
        from myTableName
*/
db.add_select("myTableName", new string[] {"columnOne", "columnTwo"},
                             new string[] { "(CASE ", "(CASE "},
                             new string[] { " WHEN 1 THEN 0 ELSE 1 END) as calculatedColumnOne", 
                                            " WHEN 1 THEN 0 ELSE 1 END) as calculatedColumnTwo" 
                                            });

add_select_fields


This function is the same in all MySQL and SQL Server instances

This function does not use the order structure but it does require that the add_select has already been used prior to its use. This is because the add_select_fields is simply extending the original select fields definition for the query.

Parameter Syntaxs

Note: The startField and endField are both optional and are not required.

// Single version
void add_select_fields(string tableName, string selectField, string startField = null, string endField = null)

// Multiple field version
void add_select_fields(string tableName, string[] selectFields, string[] startFields = null, string[] endFields = null)

Single Example

/*SQL: 
    select 
        myTableName.columnOne, 
        myTableName.columnTwo --Added by from the "add_select_fields"
    from myTableName
*/
db.add_select("myTableName", "columnOne");
db.add_select_fields("myTableName", "columnTwo");

Multiple Column Example

/*SQL: 
    select 
        myTableName.columnOne, 
        myTableName.columnTwo, 
        myTableName.columnThree, --Added by from the "add_select_fields"
        myTableName.columnFour   --Added by from the "add_select_fields"
    from myTableName
*/
db.add_select("myTableName", new string[] {"columnOne", "columnTwo"});
db.add_select_fields("myTableName", new string[] {"columnThree", "columnFour"});

Start and End Example

Single Example

/*SQL:
    select 
        myTableName.columnOne,
        (CASE myTableName.columnTwo WHEN 1 THEN 0 ELSE 1 END) as calculatedColumn --Added by from the "add_select_fields"
    from myTableName
*/
db.add_select("myTableName", "columnOne");
db.add_select_fields("myTableName", "columnTwo", "(CASE ", " WHEN 1 THEN 0 ELSE 1 END) as calculatedColumn");

Multiple Column Example

/*SQL: 
        select 
            myTableName.columnOne,
            (CASE myTableName.columnTwo WHEN 1 THEN 0 ELSE 1 END) as calculatedColumnOne,   --Added by from the "add_select_fields"
            (CASE myTableName.columnThree WHEN 1 THEN 0 ELSE 1 END) as calculatedColumnTwo  --Added by from the "add_select_fields"
        from myTableName
*/
db.add_select("myTableName", "columnOne");
db.add_select_fields("myTableName", new string[] {"columnTwo", "columnThree"},
                                    new string[] { "(CASE ", "(CASE "},
                                    new string[] { " WHEN 1 THEN 0 ELSE 1 END) as calculatedColumnOne", 
                                                   " WHEN 1 THEN 0 ELSE 1 END) as calculatedColumnTwo" 
                                            });

Join Additional Column Select Example

/*SQL:
        select
            myTableName.columnOne,
            joinedTable.joinColumnOne --Added by from the "add_select_fields"
        from myTableName
                    
        inner join joinedTable on
        myTableName.columnOne = joinedTable.joinedColumnOne
*/
db.add_select("myTableName", "columnOne");
db.add_join("inner join", "joinedTable", "myTableName", "joinColumnOne", "columnOne");
db.add_select_fields("joinedTable", "joinColumnOne");

add_select_top


This function is only supported in SQL Server instances

This function adds a top value to the select query.

Parameter Syntax

void add_select_top(int topValue)

General Example

/*SQL: 
    select top 10 
        myTableName.columnOne 
    from myTableName;
*/
db.add_select("myTableName", "columnOne");
db.add_select_top(10);

add_join


This function is the same in all MySQL and SQL Server instances

Parameter Syntaxs

// Single version
void add_join(string joinType, string joinTableName, string currentTableName, string joinTableField, string currentTableField)

// Multiple field version
void add_join(string joinType, string joinTableName, string currentTableName, string[] joinTableFields, string[] currentTableFields)

Single Join Column Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName

    inner join joinedTable on
    myTableName.columnOne = joinedTable.joinedColumnOne
*/
db.add_select("myTableName", "columnOne");
db.add_join("inner join", "joinedTable", "myTableName", "joinColumnOne", "columnOne");

Multiple Join Column Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName

    inner join joinedTable on
    myTableName.columnOne = joinedTable.joinedColumnOne 
    and myTableName.columnTwo = joinedTable.joinedColumnTwo 
*/
db.add_select("myTableName", "columnOne");
db.add_join("inner join", "joinedTable", "myTableName", new string[] {"joinColumnOne", "joinColumnTwo"}, 
                                                        new string[] {"columnOne", "columnTwo"});

Multiple Joins Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName

    inner join joinedTable on
    myTableName.columnOne = joinedTable.joinedColumnOne 

    left join joinedTable2 on
    myTableName.columnOne = joinedTable2.joined2ColumnOne 
*/
db.add_select("myTableName", "columnOne");
db.add_join("inner join", "joinedTable", "myTableName", "joinColumnOne", "columnOne");
db.add_join("left join", "joinedTable2", "myTableName", "join2ColumnOne", "columnOne");

add_join_parameters


This function is the same in all MySQL and SQL Server instances

This functions adds additional join parameters to the last add_join function which was called, it must be used directly after the add_join function.

Parameter Syntaxs

// Single version
void add_join_parameters(string joinTableName, string currentTableName, string joinTableField, string currentTableField)

// Multiple field version
void add_join_parameters(string joinTableName, string currentTableName, string[] joinTableFields, string[] currentTableFields)

Single Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName

    inner join joinedTable on
    myTableName.columnOne = joinedTable.joinedColumnOne
    and myTableName.columnTwo = joinedTable.joinedColumnTwo --Added by from the "add_join_parameters" 
*/
db.add_select("myTableName", "columnOne");
db.add_join("inner join", "joinedTable", "myTableName", "joinColumnOne", "columnOne");
db.add_join_parameters("joinedTable", "myTableName", "joinColumnTwo", "columnTwo");

Multiple Column Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName

    inner join joinedTable on
    myTableName.columnOne = joinedTable.joinedColumnOne 
    and myTableName.columnTwo = joinedTable.joinedColumnTwo --Added by from the "add_join_parameters" 
    and myTableName.columnThree = joinedTable.joinedColumnThree --Added by from the "add_join_parameters" 

*/
db.add_select("myTableName", "columnOne");
db.add_join("inner join", "joinedTable", "myTableName", "joinColumnOne", "columnOne");
db.add_join_parameters("joinedTable", "myTableName", new string[] {"joinColumnTwo", "joinColumnThree"}, 
                                                     new string[] {"columnTwo", "columnThree"});

add_where_normal


This function is the same in all MySQL and SQL Server instances

Parameter Syntax

Note: The value parameter can be supplied nearly any generic type of variable from int, string, datetimes etc.

void add_where_normal(string tableName, string field, object value, string theOperator = null, string type = null, string startWrapper = null, string endWrapper = null)

Generic String Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne = 'valueHere'
*/
db.add_select("myTableName", "columnOne");
db.add_where_normal("myTableName", "columnOne", "valueHere");

Generic Int Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne = 1
*/
db.add_select("myTableName", "columnOne");
db.add_where_normal("myTableName", "columnOne", 1);

Generic DateTime Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne = '03/03/2017 13:43:28'
*/
db.add_select("myTableName", "columnOne");
db.add_where_normal("myTableName", "columnOne", DateTime.Now);

Generic Multiple Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne = 'valueHere'
    and myTableName.columnTwo = 'valueHere'
*/
db.add_select("myTableName", "columnOne");
db.add_where_normal("myTableName", "columnOne", "valueHere");
db.add_where_normal("myTableName", "columnTwo", "valueHere");

Where Operator Example

The default operator if not supplied is "=".

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne != 'valueHere'
    
*/
db.add_select("myTableName", "columnOne");
db.add_where_normal("myTableName", "columnOne", "valueHere", "!=");

Where Type Example

The where type can only be supplied in any of the where clauses if one of the where clauses has already been used prior to it.

Note: The default where type if not supplied is "and".

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne = 'valueHere'
    or myTableName.columnTwo = 'valueHere'
    
*/
db.add_select("myTableName", "columnOne");
db.add_where_normal("myTableName", "columnOne", "valueHere");
db.add_where_normal("myTableName", "columnTwo", "valueHere", "=", "or");

Where Wrapper Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne = 'valueHere'
    and (
            myTableName.columnTwo = 'valueHere'
            or myTableName.columnThree = 'valueHere'
        )
*/
db.add_select("myTableName", "columnOne");
db.add_where_normal("myTableName", "columnOne", "valueHere");
db.add_where_normal("myTableName", "columnTwo", "valueHere", "=", "and", "(");
db.add_where_normal("myTableName", "columnThree", "valueHere", "=", "or" null, ")");

add_where_like


This function is the same in all MySQL and SQL Server instances

Parameter Syntax

Note: The value parameter can be supplied nearly any generic type of variable from int, string, datetimes etc but we do recommend a string type for like queries.

void add_where_like(string tableName, string field, object value, string theOperator = null, string type = null, string startWrapper = null, string endWrapper = null)

Generic Examples

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne like '%valueHere%'
*/
db.add_select("myTableName", "columnOne");
db.add_where_like("myTableName", "columnOne", "%valueHere%");
/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne like '%valueHere'
*/
db.add_select("myTableName", "columnOne");
db.add_where_like("myTableName", "columnOne", "%valueHere");
/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne like 'valueHere%'
*/
db.add_select("myTableName", "columnOne");
db.add_where_like("myTableName", "columnOne", "valueHere%");

Generic Multiple Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne like '%valueHere%'
    and myTableName.columnTwo like '%valueHere%'
*/
db.add_select("myTableName", "columnOne");
db.add_where_like("myTableName", "columnOne", "%valueHere%");
db.add_where_like("myTableName", "columnTwo", "%valueHere%");

Where Operator Example

The default operator if not supplied is nothing otherwise you can use "not".

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne not like '%valueHere%'
    
*/
db.add_select("myTableName", "columnOne");
db.add_where_like("myTableName", "columnOne", "%valueHere%", "not");

Where Type Example

The where type can only be supplied in any of the where clauses if one of the where clauses has already been used prior to it.

Note: The default where type if not supplied is "and".

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne like '%valueHere%'
    or myTableName.columnTwo not like '%valueHere%'
    
*/
db.add_select("myTableName", "columnOne");
db.add_where_like("myTableName", "columnOne", "%valueHere%");
db.add_where_like("myTableName", "columnTwo", "%valueHere%", "not", "or");

Where Wrapper Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne like '%valueHere%'
    and (
            myTableName.columnTwo not like '%valueHere%'
            or myTableName.columnThree not like '%valueHere%'
        )
*/
db.add_select("myTableName", "columnOne");
db.add_where_like("myTableName", "columnOne", "%valueHere%");
db.add_where_like("myTableName", "columnTwo", "%valueHere%", "not", "and", "(");
db.add_where_like("myTableName", "columnThree", "%valueHere%", "not", "or" null, ")");

add_where_is


This function is the same in all MySQL and SQL Server instances

Parameter Syntax

void add_where_is(string tableName, string field, string theOperator = null, string type = null, string startWrapper = null, string endWrapper = null)

Generic Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne is null
*/
db.add_select("myTableName", "columnOne");
db.add_where_is("myTableName", "columnOne");

Generic Multiple Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne is null
    and myTableName.columnTwo is null
*/
db.add_select("myTableName", "columnOne");
db.add_where_is("myTableName", "columnOne");
db.add_where_is("myTableName", "columnTwo");

Where Operator Example

The default operator if not supplied is nothing otherwise you can use "not".

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne is not null
    
*/
db.add_select("myTableName", "columnOne");
db.add_where_is("myTableName", "columnOne", "not");

Where Type Example

The where type can only be supplied in any of the where clauses if one of the where clauses has already been used prior to it.

Note: The default where type if not supplied is "and".

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne is null
    or myTableName.columnTwo is not null
    
*/
db.add_select("myTableName", "columnOne");
db.add_where_is("myTableName", "columnOne");
db.add_where_is("myTableName", "columnTwo", "not", "or");

Where Wrapper Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne is null
    and (
            myTableName.columnTwo is not null
            or myTableName.columnThree is not null
        )
*/
db.add_select("myTableName", "columnOne");
db.add_where_is("myTableName", "columnOne");
db.add_where_is("myTableName", "columnTwo", "not", "and", "(");
db.add_where_is("myTableName", "columnThree", "not", "or" null, ")");

add_where_in


This function is the same in all MySQL and SQL Server instances

Parameter Syntax

Note: The value parameter must be wrapped in a object[] format but can be supplied nearly any generic type of variable from int, string, datetimes etc.

void add_where_in(string tableName, string field, object values, string theOperator = null, string type = null, string startWrapper = null, string endWrapper = null)

Generic String Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne in ('valueHere1', 'valueHere2', 'valueHere3')
*/
db.add_select("myTableName", "columnOne");
db.add_where_in("myTableName", "columnOne", new object[] { "valueHere1", "valueHere2", "valueHere3" });

Generic Int Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne in (1, 2, 3)
*/
db.add_select("myTableName", "columnOne");
db.add_where_in("myTableName", "columnOne", new object[] { 1, 2, 3 });

Generic DateTime Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne in ('04/03/2017 14:37:22','05/03/2017 14:37:22','06/03/2017 14:37:22')
*/
db.add_select("myTableName", "columnOne");
db.add_where_in("myTableName", "columnOne", new object[] { DateTime.Now.AddDays(1), DateTime.Now.AddDays(2), DateTime.Now.AddDays(3) });

Generic Multiple Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne in ('valueHere1', 'valueHere2')
    and myTableName.columnTwo in ('valueHere3', 'valueHere4')
*/
db.add_select("myTableName", "columnOne");
db.add_where_in("myTableName", "columnOne", new object[] { "valueHere1", "valueHere2" });
db.add_where_in("myTableName", "columnTwo", new object[] { "valueHere3", "valueHere4" });

Where Operator Example

The default operator if not supplied is nothing otherwise you can use "not".

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne not in ('valueHere1', 'valueHere2')
    
*/
db.add_select("myTableName", "columnOne");
db.add_where_in("myTableName", "columnOne", new object[] { "valueHere1", "valueHere2" }, "not");

Where Type Example

The where type can only be supplied in any of the where clauses if one of the where clauses has already been used prior to it.

Note: The default where type if not supplied is "and".

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne in ('valueHere1', 'valueHere2')
    or myTableName.columnTwo not in ('valueHere3', 'valueHere4')
    
*/
db.add_select("myTableName", "columnOne");
db.add_where_in("myTableName", "columnOne", new object[] { "valueHere1", "valueHere2" });
db.add_where_in("myTableName", "columnTwo", new object[] { "valueHere3", "valueHere4" }, "not", "or");

Where Wrapper Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne in ('valueHere1', 'valueHere2')
    and (
            myTableName.columnTwo not in ('valueHere3', 'valueHere4')
            or myTableName.columnThree not in ('valueHere5', 'valueHere6')
        )
*/
db.add_select("myTableName", "columnOne");
db.add_where_in("myTableName", "columnOne", new object[] { "valueHere1", "valueHere2" });
db.add_where_in("myTableName", "columnTwo", new object[] { "valueHere3", "valueHere4" }, "not", "and", "(");
db.add_where_in("myTableName", "columnThree", new object[] { "valueHere5", "valueHere6" }, "not", "or", null, ")");

add_where_between


This function is the same in all MySQL and SQL Server instances

Parameter Syntax

Note: The value parameter must be wrapped in a object[] and contain only 2 elements of the same format but can be supplied nearly any generic type of variable from int, string, datetimes etc.

void add_where_between(string tableName, string field, object values, string theOperator = null, string type = null, string startWrapper = null, string endWrapper = null)

Generic String Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne between 'a' and 'c'
*/
db.add_select("myTableName", "columnOne");
db.add_where_between("myTableName", "columnOne", new object[] { "a", "c" });

Generic Int Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne between 1 and 3
*/
db.add_select("myTableName", "columnOne");
db.add_where_between("myTableName", "columnOne", new object[] { 1, 3 });

Generic DateTime Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne between '04/03/2017 14:37:22' and '06/03/2017 14:37:22'
*/
db.add_select("myTableName", "columnOne");
db.add_where_between("myTableName", "columnOne", new object[] { DateTime.Now.AddDays(1), DateTime.Now.AddDays(3) });

Generic Multiple Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne between 1 and 3
    and myTableName.columnTwo between 6 and 9
*/
db.add_select("myTableName", "columnOne");
db.add_where_between("myTableName", "columnOne", new object[] { 1, 3 });
db.add_where_between("myTableName", "columnTwo", new object[] { 6, 9 });

Where Operator Example

The default operator if not supplied is nothing otherwise you can use "not".

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne not between 1 and 3
    
*/
db.add_select("myTableName", "columnOne");
db.add_where_between("myTableName", "columnOne", new object[] { 1, 3 }, "not");

Where Type Example

The where type can only be supplied in any of the where clauses if one of the where clauses has already been used prior to it.

Note: The default where type if not supplied is "and".

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne between 1 and 3
    or myTableName.columnTwo not between 6 and 9
*/
db.add_select("myTableName", "columnOne");
db.add_where_between("myTableName", "columnOne", new object[] { 1, 3 });
db.add_where_between("myTableName", "columnTwo", new object[] { 6, 9 }, "not", "or");

Where Wrapper Example

/*SQL: 
    select 
        myTableName.columnOne 
    from myTableName
    
    where myTableName.columnOne between 1 and 3
    and (
            myTableName.columnTwo not between 6 and 9
            or myTableName.columnTwo not between 11 and 15
        )
*/
db.add_select("myTableName", "columnOne");
db.add_where_between("myTableName", "columnOne", new object[] { 1, 3 });
db.add_where_between("myTableName", "columnTwo", new object[] { 6, 9 }, "not", "and", "(");
db.add_where_between("myTableName", "columnTwo", new object[] {11, 15 }, "not", "or", null ")");

add_groupby


This function is the same in all MySQL and SQL Server instances

Parameter Syntaxs

// Single version
void add_groupby(string tableName, string field)

// Multiple version
void add_groupby(string tableName, string[] fields)

Single Column Example

/*SQL: 
    select  
        sum( myTableName.columnOne ) as count 
    from myTableName 

    group by 
        myTableName.columnTwo
*/
db.add_select("myTableName", "columnOne", "Sum(", ") as count");
db.add_groupby("myTableName", "columnTwo");

Multiple Column Example

/*SQL: 
    select  
        sum( myTableName.columnOne ) as count 
    from myTableName 
    
    group by 
        myTableName.columnTwo,
        myTableName.columnThree,
*/
db.add_select("myTableName", "columnOne", "Sum(", ") as count");
db.add_groupby("myTableName", new string[] {"columnTwo", "columnThree"});

add_groupby_fields


This function is the same in all MySQL and SQL Server instances

This function does not use the order structure but it does require that the add_groupby has already been used prior to its use. This is because the add_groupby_fields is simply extending the original group by fields definition for the query.

Parameter Syntaxs

// Single version
void add_groupby_fields(string tableName, string field)

// Multiple version
void add_groupby_fields(string tableName, string[] fields)

Single Column Example

/*SQL: 
    select  
        sum( myTableName.columnOne ) as count 
    from myTableName 

    group by 
        myTableName.columnTwo,
        myTableName.columnThree --added from the add_groupby_fields
*/
db.add_select("myTableName", "columnOne", "Sum(", ") as count");
db.add_groupby("myTableName", "columnTwo");
db.add_groupby_fields("myTableName", "columnThree");

Multiple Column Example

/*SQL: 
    select  
        sum( myTableName.columnOne ) as count 
    from myTableName 
    
    group by 
        myTableName.columnTwo,
        myTableName.columnThree,
        myTableName.columnFour, --added from the add_groupby_fields
        myTableName.columnFive  --added from the add_groupby_fields
*/
db.add_select("myTableName", "columnOne", "Sum(", ") as count");
db.add_groupby("myTableName", new string[] {"columnTwo", "columnThree"});
db.add_groupby_fields("myTableName", new string[] {"columnFour", "columnFive"});

add_orderby


This function is the same in all MySQL and SQL Server instances

Parameter Syntaxs

// Single version
void add_orderby(string tableName, string field, string type = null)

// Multiple version
void add_orderby(string tableName, string[] fields, string[] types = null)

Single Column Example

Note:Uses the sql generic ordering.

/*SQL: 
    select 
        myTableName.columnOne
    from myTableName

    order by 
        myTableName.columnOne
*/
db.add_select("myTableName", "columnOne");
db.add_orderby("myTableName", "columnOne");
/*SQL: 
    select 
        myTableName.columnOne
    from myTableName

    order by 
        myTableName.columnOne asc
*/
db.add_select("myTableName", "columnOne");
db.add_orderby("myTableName", "columnOne", "asc");
/*SQL: 
    select 
        myTableName.columnOne
    from myTableName

    order by 
        myTableName.columnOne desc
*/
db.add_select("myTableName", "columnOne");
db.add_orderby("myTableName", "columnOne", "desc");

Multiple Column Example

Note:Uses the sql generic ordering.

/*SQL: 
    select 
        myTableName.columnOne
    from myTableName

    order by 
        myTableName.columnOne, 
        myTableName.columnTwo
*/
db.add_select("myTableName", "columnOne");
db.add_orderby("myTableName", new string[] {"columnOne", "columnTwo"});

Note:Uses the order types supplied.

/*SQL: 
    select 
        myTableName.columnOne
    from myTableName

    order by 
        myTableName.columnOne asc, 
        myTableName.columnTwo desc
*/
db.add_select("myTableName", "columnOne");
db.add_orderby("myTableName", new string[] {"columnOne", "columnTwo"}, 
                              new string[] {"asc", "desc"});

add_orderby_fields


This function is the same in all MySQL and SQL Server instances

This function does not use the order structure but it does require that the add_orderby has already been used prior to its use. This is because the add_orderby_fields is simply extending the original order by fields definition for the query.

Parameter Syntaxs

// Single version
void add_orderby_fields(string tableName, string field, string type)

// Multiple field version
void add_orderby_fields(string tableName, string[] fields, string[] types)

Single Column Example

Note:Uses the sql generic ordering.

/*SQL: 
    select 
        myTableName.columnOne
    from myTableName

    order by 
        myTableName.columnOne, 
        myTableName.columnTwo --added from the add_orderby_fields
*/
db.add_select("myTableName", "columnOne");
db.add_orderby("myTableName", "columnOne");
db.add_orderby_fields("myTableName", "columnTwo");
/*SQL: 
    select 
        myTableName.columnOne
    from myTableName
    
    order by 
        myTableName.columnOne asc,
        myTableName.columnTwo asc --added from the add_orderby_fields
*/
db.add_select("myTableName", "columnOne");
db.add_orderby("myTableName", "columnOne", "asc");
db.add_orderby_fields("myTableName", "columnTwo", "asc");    
/*SQL: 
    select 
        myTableName.columnOne
    from myTableName

    order by 
        myTableName.columnOne desc,         
        myTableName.columnTwo desc --added from the add_orderby_fields
*/
db.add_select("myTableName", "columnOne");
db.add_orderby("myTableName", "columnOne", "desc");
db.add_orderby_fields("myTableName", "columnTwo", "desc");    

Multiple Column Example

Note:Uses the sql generic ordering.

/*SQL: 
    select 
        myTableName.columnOne
    from myTableName

    order by 
        myTableName.columnOne, 
        myTableName.columnTwo, 
        myTableName.columnThree, --added from the add_orderby_fields 
        myTableName.columnFour   --added from the add_orderby_fields
*/
db.add_select("myTableName", "columnOne");
db.add_orderby("myTableName", new string[] {"columnOne", "columnTwo"});
db.add_orderby_fields("myTableName", new string[] {"columnthree", "columnFour"});    

Note:Uses the order types supplied.

/*SQL: 
    select 
        myTableName.columnOne
    from myTableName

    order by 
        myTableName.columnOne asc, 
        myTableName.columnTwo desc, 
        myTableName.columnThree desc, --added from the add_orderby_fields
        myTableName.columnFour desc   --added from the add_orderby_fields
*/
db.add_select("myTableName", "columnOne");
db.add_orderby("myTableName", new string[] {"columnOne", "columnTwo"}, 
                              new string[] {"asc", "desc"});
db.add_orderby_fields("myTableName", new string[] {"columnThree", "columnFour"}, 
                                     new string[] {"desc", "desc"});

add_limit


This function is supported in all MySQL and SQLServer2008 instances only

Parameter Syntaxs

Note: The SQL Server 2008 version has a built in wrapper support to obtain the same functionality as MySQL.

// MySQL version
void add_limit(int maxRows)
void add_limit(int startLocation, int numberOfRows)

// SQL Server version
void add_limit(int minValue, int maxValue)

MySQL maxRows Example

Pulls the first 5 rows start at the first row.

/*SQL: 
    select
        myTableName.columnOne
    from myTableName
    limit 5
*/
db.add_select("myTableName", "columnOne");
db.add_limit(5);

MySQL Start and Row Number Example

Pulls 10 rows starting at the 5th row.

/*SQL: 
    select
        myTableName.columnOne
    from myTableName
    limit 5, 10
*/
db.add_select("myTableName", "columnOne");
db.add_limit(5, 10);

SQL Server 2008 Example

Note: This function uses a wrapper as limits did not exist in 2008 but in 2012+ we had offsets introduced for SQL Server.

Note: This function also supports order by usage and modifies the wrapper to cater for them.

Starts at row 1 and pulls up till row 10 (inlcuding row 10).

/*SQL:
    --if the hash table already exists for some reason, it removes it
	if OBJECT_ID('tempdb..#database2008limitwrapper') is not null
	begin
		drop table #database2008limitwrapper
	end

    select * into #database2008limitwrapper from 
    ( 
        select 
            myTableName.columOne, 
            ROW_NUMBER() over (order by (select 0)) as theLimitRow 
        from myTableName 
    ) as a 
    where a.theLimitRow >= 1 and a.theLimitRow <= 10
    
    --drops the extra column used to calculate the correct rows
    alter table #database2008limitwrapper
    drop column theLimitRow
    
    --displays the results without the extra column
    select * from #database2008limitwrapper
   
    --removes the hash table as we do not need it anymore
    drop table #database2008limitwrapper
*/
db.add_select("myTableName", "columnOne");
db.add_limit(1, 10);

add_offset


This functionality is only supported in instances of SQL Server 2012 and above

Note: This functions requires an order by prior to being used.

Parameter Syntax

void add_offset(int offsetRows, int numberOfRows)

General Example

Starts at row 1 and pulls 10 rows.

/*SQL: 
    select 
        myTableName.columOne 
    from myTableName 
    order by myTableName.columOne desc
    offset 1 rows fetch next 10 rows only;
*/
db.add_select("myTableName", "columOne");
db.add_orderby("myTableName", "columOne", "desc");
db.add_offset(1, 10);

add_create_table


This function is the same in all MySQL and SQL Server instances

Parameter Syntaxs

// Single version
void add_create_table(string newTableName, string newFieldsName, string newFieldType)

// Multiple version
void add_create_table(string newTableName, string[] newFieldsNames, string[] newFieldTypes)

Single Column Example

/*SQL: 
        create table myTableName 
        (
            table_id int Identity(1,1) PRIMARY KEY not null
        )
*/
db.add_create_table("myTableName", "table_id", "int Identity(1,1) PRIMARY KEY not null");

Multiple Column Example

/*SQL: 
        create table myTableName 
        (
            table_id int Identity(1,1) PRIMARY KEY not null,
            name varchar(10) not null
        )
*/
db.add_create_table("myTableName", new string[] { "table_id", "name" },
                                   new string[] { "int Identity(1,1) PRIMARY KEY not null", "varchar(10) not null" });

add_create_fields


This function is the same in all MySQL and SQL Server instances

This function does not use the order structure but it does require that the add_create_table has already been used prior to its use. This is because the add_create_fields is simply extending the original table creation fields definition for the query.

Parameter Syntaxs

// Single version
void add_create_fields(string newFieldsName, string newFieldType)

// Multiple version
void add_create_fields(string[] newFieldsNames, string[] newFieldTypes)

Single Column Example

/*SQL: 
        create table myTableName 
        (
            table_id int Identity(1,1) PRIMARY KEY not null
            title varchar(10) not null // added from the add_create_fields
        )
*/
db.add_create_table("myTableName", "table_id", "int Identity(1,1) PRIMARY KEY not null");
db.add_create_fields("title", "varchar(10) not null");

Multiple Column Example

/*SQL: 
        create table myTableName 
        (
            table_id int Identity(1,1) PRIMARY KEY not null,
            name varchar(10) not null,
            title varchar(10) not null, // added from the add_create_fields
            age int not null // added from the add_create_fields
        )
*/
db.add_create_table("myTableName", new string[] { "table_id", "name" },
                                   new string[] { "int Identity(1,1) PRIMARY KEY not null", "varchar(10) not null" });
db.add_create_fields(new string[] { "title", "age" },
                     new string[] { "varchar(10) not null", "int not null" });

add_drop_table


This function is the same in all MySQL and SQL Server instances

Parameter Syntax

void add_drop_table(string tableName)

General Example

/*SQL: 
    drop table myTableName
*/
db.add_drop_table("myTableName");

add_delete


This function is the same in all MySQL and SQL Server instances

Parameter Syntax

void add_delete(string tableName)

General Example

/*SQL: 
    delete myTableName from myTableName
*/
db.add_delete("myTableName");

Where Examples

/*SQL: 
    delete myTableName from myTableName
    where myTableName.columnOne = 1
*/
db.add_delete("myTableName");
db.add_where_normal("myTableName", "columnOne", 1);

add_delete_return


This function is only supported in SQL Server instances

Parameter Syntax

void add_delete_return(string tableName)

General Example

/*SQL: 
    delete myTableName output deleted.* from myTableName
*/
db.add_delete_return("myTableName");

Where Examples

/*SQL: 
    delete myTableName output deleted.* from myTableName
    where myTableName.columnOne = 1
*/
db.add_delete_return("myTableName");
db.add_where_normal("myTableName", "columnOne", 1);

add_insert


This function is the same in all MySQL and SQL Server instances

Parameter Syntaxs

Note: The value and values parameters can be supplied nearly any generic type of variable from int, string, datetimes, byte[] etc.

void add_insert(string tableName)

void add_insert(string tableName, string field)

void add_insert(string tableName, string[] fields)

// This values parameter can be either singular or multiple, if its multipe it must be wrapped in a object[] format
void add_insert(string tableName, object values)

void add_insert(string tableName, string field, object value)

// This values parameter must be wrapped in a object[] format
void add_insert(string tableName, string[] fields, object values)

Table Example

/*SQL: 
    insert into myTableName
*/
db.add_insert("myTableName");

Table & Field Example

/*SQL: 
    insert into myTableName
    (columnOne)
*/
db.add_insert("myTableName", "columnOne");

Table & Fields Example

/*SQL: 
    insert into myTableName
    (columnOne, columnTwo)
*/
db.add_insert("myTableName", new string[] { "columnOne", "columnTwo" });

Table & Values Example

Single Value Version

/*SQL: 
    insert into myTableName 
    values (1)
*/
db.add_insert("myTableName", 1);

Multiple Value Version

/*SQL: 
    insert into myTableName 
    values (1, 2)
*/
db.add_insert("myTableName", new object[] { 1, 2 });

Table, Field & Value Example

/*SQL: 
    insert into myTableName 
    (columnOne) 
    values (1)
*/
db.add_insert("myTableName", "columnOne", 1);

Table, Fields & Values Example

/*SQL: 
    insert into myTableName 
    (columnOne,columnTwo) 
    values (1, 2) ;
*/
db.add_insert("myTableName", new string[] { "columnOne", "columnTwo" },
                             new object[] { 1, 2 });

add_insert_fields


This function is the same in all MySQL and SQL Server instances

This function does not use the order structure but it does require that the add_insert has already been used prior to its use. This is because the add_insert_fields is simply extending the original insert fields definition for the query.

Parameter Syntaxs

// Single version
void add_insert_fields(string field)

// Multiple version
void add_insert_fields(string[] fields)

Single Example

/*SQL: 
    insert into myTableName
    (columnOne)
*/
db.add_insert("myTableName");
db.add_insert_fields("columnOne");

Multiple Example

/*SQL: 
    insert into myTableName
    (columnOne, columnTwo)
*/
db.add_insert("myTableName");
db.add_insert_fields(new string[] {"columnOne", "columnTwo"});

add_insert_values


This function is the same in all MySQL and SQL Server instances

This function requires that the add_insert was the last used prior to its use. This is because the add_insert_values adds a new values row to the query.

This function can be used multiple times to build a large insert values query but it is restricted to whatever the insert values limit is on the SQL instance your running.

Parameter Syntax

Note: The values parameter can be either singular or multiple, if its multipe it must be wrapped in a object[] format, it can be supplied nearly any generic type of variable from int, string, datetimes, byte[] etc.

add_insert_values(object values)

Singular Example

/*SQL: 
    insert into myTableName 
    values (1)
*/
db.add_insert("myTableName");
db.add_insert_values(1);

Multiple Example

/*SQL: 
    insert into myTableName 
    values (1, 2, 3)
*/
db.add_insert("myTableName");
db.add_insert_values(new object[] {1, 2, 3});

Multiple Usage Example

/*SQL: 
    insert into myTableName 
    values (1, 2, 3),
           (4, 5, 6),
           (7, 8, 9)
*/
db.add_insert("myTableName");
db.add_insert_values(new object[] { 1, 2, 3 });
db.add_insert_values(new object[] { 4, 5, 6 });
db.add_insert_values(new object[] { 7, 8, 9 });

add_insert_return


This function is only supported in SQL Server instances

Parameter Syntaxs

Note: The value and values parameters can be supplied nearly any generic type of variable from int, string, datetimes, byte[] etc.

void add_insert_return(string tableName)

void add_insert_return(string tableName, string field)

void add_insert_return(string tableName, string[] fields)

// This values parameter can be either singular or multiple, if its multipe it must be wrapped in a object[] format
void add_insert_return(string tableName, object values)

void add_insert_return(string tableName, string field, object value)

// This value parameter must be wrapped in a object[] format
void add_insert_return(string tableName, string[] fields, object values)

Table Example

/*SQL: 
    insert into myTableName output inserted.*
*/
db.add_insert_return("myTableName");

Table & Field Example

/*SQL: 
    insert into myTableName
    (columnOne)
    output inserted.*
*/
db.add_insert_return("myTableName", "columnOne");

Table & Fields Example

/*SQL: 
    insert into myTableName
    (columnOne, columnTwo)
    output inserted.*
*/
db.add_insert_return("myTableName", new string[] { "columnOne", "columnTwo" });

Table & Values Example

Single Value Version

/*SQL: 
    insert into myTableName 
    output inserted.*
    values (1)
*/
db.add_insert_return("myTableName", 1);

Multiple Value Version

/*SQL: 
    insert into myTableName 
    output inserted.*
    values (1, 2)
*/
db.add_insert_return("myTableName", new object[] { 1, 2 });

Table, Field & Value Example

/*SQL: 
    insert into myTableName 
    (columnOne) 
    output inserted.*
    values (1)
*/
db.add_insert_return("myTableName", "columnOne", 1);

Table, Fields & Values Example

/*SQL: 
    insert into myTableName 
    (columnOne,columnTwo) 
    output inserted.*
    values (1, 2) ;
*/
db.add_insert_return("myTableName", new string[] { "columnOne", "columnTwo" },
                                    new object[] { 1, 2 });

add_update


This function is the same in all MySQL and SQL Server instances

Parameter Syntaxs

Note: The value and values parameters can be supplied nearly any generic type of variable from int, string, datetimes, byte[] etc.

// Single Version
void add_update(string tableName, string field, object value)

// Multiple Version
// This values parameter must be wrapped in a object[] format
void add_update(string tableName, string[] fields, object values)

Single Example

/*SQL: 
    update myTableName 
    set myTableName.columnOne = 10  
    from myTableName
*/
db.add_update("myTableName", "columnOne", 10);

Multiple Example

/*SQL: 
    update myTableName 
        set myTableName.columnOne = 10, 
            myTableName.columnTwo = 12  
    from myTableName
*/
db.add_update("myTableName", new string[] { "columnOne", "columnTwo"},
                             new object[] {10, 12});

Where Example

/*SQL: 
    update myTableName 
        set myTableName.columnOne = 10, 
            myTableName.columnTwo = 12  
    from myTableName;
    where myTableName.columnOne = 1
*/
db.add_update("myTableName", new string[] { "columnOne", "columnTwo"},
                             new object[] {10, 12});
db.add_where_normal("myTableName", "columnOne", 1);

add_update_additions


This function is the same in all MySQL and SQL Server instances

This function does not use the order structure but it does require that the add_update has already been used prior to its use. This is because the add_update_additions is simply extending the original update fields and values definition for the query.

Parameter Syntaxs

Note: The value and values parameters can be supplied nearly any generic type of variable from int, string, datetimes, byte[] etc.

// Single Version
void add_update_additions(string tableName, string field, object value)

// Multiple Version
// This values parameter must be wrapped in a object[] format
void add_update_additions(string tableName, string[] fields, object values)

Single Example

/*SQL: 
    update myTableName 
        set myTableName.columnOne = 10, 
            myTableName.columnTwo = 20 --Added by from the "add_update_additions"   
    from myTableName
*/
db.add_update("myTableName", "columnOne", 10);
db.add_update_additions("myTableName", "columnTwo", 20);

Multiple Example

/*SQL: 
    update myTableName 
        set myTableName.columnOne = 10, 
            myTableName.columnTwo = 11, --Added by from the "add_update_additions"  
            myTableName.columnTwo = 12  --Added by from the "add_update_additions"
    from myTableName
*/
db.add_update("myTableName", "columnOne", 10);
db.add_update_additions("myTableName", new string[] { "columnTwo", "columnThree"},
                                       new object[] {11, 12});

Where Example

/*SQL: 
    update myTableName 
        set myTableName.columnOne = 10, 
            myTableName.columnTwo = 11, --Added by from the "add_update_additions"  
            myTableName.columnTwo = 12  --Added by from the "add_update_additions"
    from myTableName;
    where myTableName.columnOne = 1
*/
db.add_update("myTableName", "columnOne", 10);
db.add_update_additions("myTableName", new string[] { "columnTwo", "columnThree"},
                                       new object[] {11, 12});
db.add_where_normal("myTableName", "columnOne", 1);

table_exists


This function is the same in all MySQL and SQL Server instances

This function uses the internal database name from the database connection supplied, it runs the query and checks for any results, if any it returns true else it returns false.

Parameter Syntax

bool table_exist(string tableName)

General Example

Note: The SQL output is different deppending on if your using MySQL or SQL Server.

// SQL Server output
/*SQL: 
    select 
        1 
    from connectionDatabaseName.sys.tables 
    where name = 'myTableName'
*/

// MySQL output
/*SQL: 
    select 
        * 
    from INFORMATION_SCHEMA.TABLES 
    where table_name like 'myTableName%' 
    and TABLE_SCHEMA = 'connectionDatabaseName'
*/
bool tableExistStatus = db.table_exist("myTableName");

table_fields


This function is the same in all MySQL and SQL Server instances

This function runs the query to return a string array of all the columns for a specified table.

Parameter Syntax

string[] table_fields(string tableName)

General Example

/*SQL: 
    select 
        column_name 
    from INFORMATION_SCHEMA.COLUMNS 
    where TABLE_NAME = 'myTableName' 
    order by ordinal_position;
*/
string[] tableFields = db.table_fields("myTableName");

truncate_table


This function is the same in all MySQL and SQL Server instances

This function runs the query to truncate a specified table.

Parameter Syntax

void truncate_table(string tableName)

General Example

/*SQL: 
    truncate table myTableName
*/
db.truncate_table("myTableName");

add_pure_sql


This function is the same in all MySQL and SQL Server instances

This function allows a pure sql query string to be passed which will then be executed. If you still wish to have parameter binding with this type of query building please see the different add_pure_sql_bind functions.

Parameter Syntax

void add_pure_sql(string sqlQuery)

General Example

/*SQL: 
    select * from myTableName
*/
db.add_pure_sql("select * from myTableName");
/*SQL: 
    select * from myTableName
    where columnOne = 2
*/
db.add_pure_sql("select * from myTableName where columnOne = 2");

add_pure_sql_bind


This function is the same in all MySQL and SQL Server instances

This function is designed to be used with the "add_pure_sql" function to allow parameter binding to be done.

Parameter Syntax

Note: The return value will be a parameter binding definition which is to be used in the query instead of the real values.

Note: The value parameter can be supplied nearly any generic type of variable from int, string, datetimes, byte[] etc.

string add_pure_sql_bind(object values)

General Example

/*SQL: 
    select * from myTableName
    where columnOne = 2
*/
// Gets the binding definition
string binding = db.add_pure_sql_bind(2);

// Adds the query with the binding definition in place of the real value
db.add_pure_sql(string.Format(@"select * from myTableName
                                where columnOne = {0}
                            ", binding));

Note: One binding can be used multiple times across the query if its the same value required.

/*SQL: 
    select * from myTableName
    where columnOne = 2
    and columnTwo = 2
*/
// Gets the binding definitions
string binding = db.add_pure_sql_bind(2);

// Adds the query with the binding definition in place of the real value
db.add_pure_sql(string.Format(@"select * from myTableName
                                where columnOne = {0}
                                and columnTwo = {0}
                            ", db.add_pure_sql_bind(2)));

add_pure_sql_bind_array


This function is the same in all MySQL and SQL Server instances

This function is designed to be used with the "add_pure_sql" function to allow parameter binding to be done.

Parameter Syntax

Note: The return values will be parameter binding definitions which are to be used in the query instead of the real values.

Note: The values parameter must be in object[] format, it can be supplied nearly any generic type of variable from int, string, datetimes, byte[] etc.

string[] add_pure_sql_bind_array(object values)

General Example

/*SQL: 
    select * from myTableName
    where columnOne = 2
    and columnTwo = 3
    and columnThree = 4
*/

// Gets the binding definitions
string[] bindings = db.add_pure_sql_bind_array(new object[] {2, 3, 4});

// Adds the query with the binding definitions in place of the real values
db.add_pure_sql(string.Format(@"select * from myTableName
                                where columnOne = {0}
                                and columnTwo = {1}
                                and columnThree = {2}
                            ", bindings[0], bindings[1], bindings[2]));

Join Bindings Example

/*SQL: 
    select * from myTableName
    where columnOne = 2
    and columnTwo = 3
    and columnThree = 4
*/
// Gets the binding definitions
string[] bindings = db.add_pure_sql_bind_array(new object[] {2, 3, 4});

// Joins the bindings together for one string
string totalBinding = string.Join(",", bindings);

// Adds the query with the binding definitions in place of the real values
db.add_pure_sql(string.Format(@"select * from myTableName
                                where columnOne in ({0})
                            ", totalBinding));

add_start_sql_wrapper


This function is the same in all MySQL and SQL Server instances

This function adds a start wrapper to the query and is designed to be used with the add_end_sql_wrapper function.

Parameter Syntax

void add_start_sql_wrapper(string sqlQuery)

General Example

/*SQL:
    select * from (
                    select myTableName.columnOne from myTableName
                  ) 
                  as example
*/
db.add_start_sql_wrapper("select * from (");
db.add_select("myTableName", "columnOne");
db.add_end_sql_wrapper(") as example");

add_end_sql_wrapper


This function is the same in all MySQL and SQL Server instances

This function adds an end wrapper to the query and is designed to be used with the add_start_sql_wrapper function.

Parameter Syntax

void add_end_sql_wrapper(string sqlQuery)

General Example

/*SQL:
    select * from (
                    select myTableName.columnOne from myTableName
                  ) 
                  as example2
*/
db.add_start_sql_wrapper("select * from (");
db.add_select("myTableName", "columnOne");
db.add_end_sql_wrapper(") as example2");

return_compiled_sql_string


This function is the same in all MySQL and SQL Server instances

This function returns the compiled SQL without executing it in a string format.

Parameter Syntax

string return_compiled_sql_string()

General Example

db.add_select("myTableName", "columnOne");
db.add_where_normal("myTableName", "columnTwo", 1);

// Gets the SQL that the above query generates
// Note: this does not affect your ability to still run the query.
string compiledSQL = db.return_compiled_sql_string();

// Executes the statement
string result = db.run_return_string();

DatabaseConnector


The DatabaseConnector is a dynamic approach to using DotNetSDB, it allows every kind of connection to be made in a single object rather than having to have different models for different connection types.

Note: in the DatabaseConnector object you can access each real instance of the database connections i.e. MySQLCore, SQLServer2016 etc, this can help with the intellisense when writing the code for the queries due to the db variable being a dynamic variable type.

Initialisation


This demonstrates how to setup and use the DatabaseConnector.

Parameter Syntax

public DatabaseConnector(object dbObject)

General Example


/*
    "db" can be any of the following:-
        SQLServer2016
        SQLServer2014
        SQLServer2012
        SQLServer2008
        mysqlCore
*/
using(DatabaseConnector connector = new DatabaseConnector(db))
{   
    // Now you can simply use the connector.db like you would with any other DotNetSDB database connection
}

Example

using DotNetSDB.Connector;
using System;
namespace Models.InformationDBModel
{
    public partial class InformationDBModel
    {
        // Stores the table name
        string table = "information";
        // Holds the database connection
        object databaseConnection;
        // Constructor that accept any of the SQL Server and MySQL connections
        public InformationDBModel(object db)
        {
            /*
                db object MUST be any of the following:-
                    SQLServer2016
                    SQLServer2014
                    SQLServer2012
                    SQLServer2008
                    mysqlCore
                Note: anything else being passed thats not within our accepted connection range will make the DatabaseConnector return null into its db variable "connector.db"
            */
            if (db != null)
            {
                databaseConnection = db;
            }
            else
            {
                throw new Exception("Must be a valid connection connection.");
            }
        }
        // This function deals with creating the database table in either SQL Server or MySQL
        public bool CreateTables(bool dropTableIfExist = false)
        {
            try
            {
                // Initilises the database connector with whatever connection type was passed
                using (DatabaseConnector connector = new DatabaseConnector(databaseConnection))
                {   
                    if (connector.db.table_exist(table) && dropTableIfExist)
                    {
                        connector.db.add_drop_table(table);
                        connector.db.run();
                    }
                    else if (connector.db.table_exist(table))
                    {
                        // Cannot create new table as it exists and the user does not want us to drop it
                        throw new Exception(string.Format("Cannot create the {0} table as it already exists and the drop flag is false.", table));
                    }
                    if (connector.isDbMysql)
                    {
                        // Creates the MySQL version of the table
                        connector.db.add_create_table(table, new string[] { "information_id", "value" },
                                               new string[] { "NOT NULL AUTO_INCREMENT PRIMARY KEY", "varchar(max)" });
                        connector.db.run();
                    }
                    else
                    {
                        // Creates the SQL Server version of the table
                        connector.db.add_create_table(table, new string[] { "information_id", "value" },
                                                   new string[] { "int Identity(1,1) PRIMARY KEY not null", "varchar(max)" });
                        connector.db.run();
                    }
                    return true;
                }
            }
            catch { }
            return false;
        }
    }
}

db


This holds the connection type that is being used by DotNetSDB, it can be any of the supported database instances in DotNetSDB.

Syntax

public dynamic db

General Example

using(DatabaseConnector connector = new DatabaseConnector(con))
{
    connector.db.add_select("tableName", "*");
    connector.db.run_return_datatable();
}

dbVersion


This outputs the type of database connection being used in DotNetSDB.

Syntax

public string dbVersion

isDbMysql


This returns a boolean flag that determines if the connection type is a MySQL version.

Syntax

public bool isDbMysql

isDbSqlServer


This returns a boolean flag that determines if the connection type is a SQL Server version.

Syntax

public bool isDbSqlServer

dbType


This returns the literal object type in .Net for the database variable.

Syntax

public Type dbType

General Example

using(DatabaseConnector connector = new DatabaseConnector(con))
{
    Type dbType = connector.dbType;
}

FileTable Extension


This functionality is only supported in instances of SQL Server 2012 and above

Note: For the FileTable functions to fully work you need to manually enable the FILESTREAM option on the SQL Server instance via using the SQL Server Configuration Manager, this is the only place you need to enable it as the enable permission functions in DotNetSDB will take care of the rest for you.


In DotNetSDB the FileTable Extension object is already initialised and ready to use once a connection has been made in all versions of SQL Server 2012+.


The FileTable Extension Location

db.filetable

The FileTable Permission Function Locations

db.filetable.permissions

The FileTable Task Function Locations

db.filetable.tasks

Quick Start


If your just wanting to enable FileTables so you can use it generically, ensure the FILESTREAM option is enabled at the SQL Server instance level and simply run the following function, you wil then be set to run any of the task functions at any time.

db.filetable.permissions.enable_all();

FileTable Permissions


This section deals with various different permission handling functions from enabling FileTables on a database to disabling them.

enabled_all


This function enables FileTable permissions at both the SQL Server base level and the database level.

Parameter Syntax

Note: The directoryName parameter is the real directory name that the filetable instances will be stored under for this particular database instance, if you don't mind what it is then simply use the default setting to make life easier.

void enable_all(string directoryName = "DocumentTable")

General Example

db.filetable.permissions.enable_all();

enable_base_instance


This function enables FileTable permissions at the SQL Server base instance "not at the database level"

Parameter Syntax

void enable_base_instance()

General Example

db.filetable.permissions.enable_base_instance();

disable_all


This function disables FileTable permissions at both the SQL Server base level and the database level.

Parameter Syntax

void disable_all()

General Example

db.filetable.permissions.disable_all();

disable_base_instance


This function disables FileTable permissions at the SQL Server base instance "not at the database level"

Parameter Syntax

void disable_base_instance()

General Example

db.filetable.permissions.disable_base_instance();

get_all_permissions


This function allows you to see all the permission results for both the SQL Server base level and the database level that are required for using FileTables.

Parameter Syntax

DataTable get_all_permissions()

General Example

DataTable allPermissions = db.filetable.permissions.get_all_permissions();

get_base_instance_permissions


This function gets the current base SQL Server instance FileTable permissions in DataTable format.

Parameter Syntax

DataTable get_base_instance_permissions()

General Example

DataTable basePermissions = db.filetable.permissions.get_base_instance_permissions();

get_locked_files


This function returns all the currently locked files for the database in a DataTable format.

Parameter Syntax

DataTable get_locked_files()

General Example

DataTable lockedFiles = db.filetable.permissions.get_locked_files();

has_all_required_permissions


This function returns a boolean that determines if the database has all the permissions required to be able to use FileTables

Parameter Syntax

bool has_all_required_permissions()

General Example

bool status = db.filetable.permissions.has_all_required_permissions();

FileTable Tasks


create_table


This function creates a new FileTable.

Parameter Syntax

tableName: is the name that you want the new FileTable to be called.

directoryName: is the SQL Server file directory name that you want the new FileTable structure to be under.

void create_table(string tableName, string directoryName)

General Example

db.filetable.tasks.create_table("filetableName", "filetableDirectoryName");   

delete_file


This function deletes a file using a stream ID in the FileTable.

Parameter Syntax

void delete_file(string tableName, string streamID)

General Example

// Gets the file id at the root level of the FileTable
string fileStreamID = db.filetable.tasks.get_root_file_id("filetableName", "example.txt");

// Deletes the file using the file id to identify it
db.filetable.tasks.delete_file("filetableName", fileStreamID);

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the file id using its parent folder id to identify it
string fileStreamID = db.filetable.tasks.get_folder_id("filetableName", parentFolderStreamID, "example.txt");

// Deletes the file using the file id to identify it
db.filetable.tasks.delete_file("filetableName", fileStreamID);

delete_folder


This function deletes a folder using a stream ID in the FileTable.

Parameter Syntax

void delete_folder(string tableName, string streamID)

General Example

// Gets the parent folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Deletes the folder using the folder id to identify it
db.filetable.tasks.delete_folder("filetableName", folderStreamID);

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the folder id using its parent folder id to identify it
string folderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentStreamID, "testingChildFolder");

// Deletes the folder using the folder id to identify it
db.filetable.tasks.delete_folder("filetableName", folderStreamID);

delete_folder_contents


This function deletes all the content in a folder using a stream ID in the FileTable.

Parameter Syntax

void delete_folder_contents(string tableName, string streamID)

General Example

// Gets the parent folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Deletes all the content in the folder using the folder id to identify it
db.filetable.tasks.delete_folder_contents("filetableName", folderStreamID);

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the folder id using its parent folder id to identify it
string folderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentStreamID, "testingChildFolder");

// Deletes all the content in the folder using the folder id to identify it
db.filetable.tasks.delete_folder_contents("filetableName", folderStreamID);

file_exists


This function checks if a file exists by using a parent folder stream ID in the FileTable.

Parameter Syntax

bool file_exists(string tableName, string parentFolderID, string fileName)

General Example

// Gets the folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingFolder");

// Checks if the file exists
bool exists = db.filetable.tasks.file_exists("filetableName", folderStreamID, "example.txt");

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the folder id using its parent folder id to identify it
string folderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentStreamID, "testingChildFolder");

// Checks if the file exists
bool exists = db.filetable.tasks.file_exists("filetableName", folderStreamID, "example.txt");

folder_exists


This function checks if a folder exists by using a parent folder stream ID in the FileTable.

Parameter Syntax

bool folder_exists(string tableName, string parentFolderID, string folderName)

General Example

// Gets the folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingFolder");

// Checks if the folder exists
bool exists = db.filetable.tasks.folder_exists("filetableName", folderStreamID, "exampleFolder");

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the folder id using its parent folder id to identify it
string folderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentStreamID, "testingChildFolder");

// Checks if the folder exists
bool exists = db.filetable.tasks.folder_exists("filetableName", folderStreamID, "exampleFolder");

id_exists


This function checks if a passed stream ID exists in the FileTable.

Parameter Syntax

bool id_exists(string tableName, string streamID)

General Example

// Gets a file stream ID
string fileStreamID = db.filetable.tasks.get_root_file_id("filetableName", "example.txt");

// Uses the file stream ID to check if it exists
bool exists = db.filetable.tasks.id_exists("filetableName", fileStreamID);

root_file_exists


This function checks if a file exists at the root level in the FileTable.

Parameter Syntax

bool root_file_exists(string tableName, string fileName)

General Example

bool exists = db.filetable.tasks.root_file_exists("filetableName", "example.txt");

root_folder_exists


This function checks if a folder exists at the root level in the FileTable.

Parameter Syntax

bool root_folder_exists(string tableName, string folderName)

General Example

bool exists = db.filetable.tasks.root_folder_exists("filetableName", "testingFolder");

get_all_file_names_from_folder


This function gets all the filenames from a folder stream ID in the FileTable.

Parameter Syntax

string[] get_all_file_names_from_folder(string tableName, string folderID)

General Example

// Gets the folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets all the filenames in the folder using its folder id to identify them
string[] filenames = db.filetable.tasks.get_folder_id("filetableName", folderStreamID);

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the child folder id using its parent folder id to identify it
string childFolderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentFolderStreamID, "testingChildFolder");

// Gets all the filenames in the folder using its folder id to identify them
string[] filenames = db.filetable.tasks.get_folder_id("filetableName", childFolderStreamID);

get_all_file_names_from_root


This function gets all the filenames that are in the root level of the FileTable.

Parameter Syntax

string[] get_all_file_names_from_root(string tableName)

General Example

string[] filenames = db.filetable.tasks.get_all_file_names_from_root("filetableName");

get_all_files_from_folder


This function gets all the filenames and file data from a folder stream ID in the FileTable.

Parameter Syntax

Dictionary<string, byte[]> get_all_files_from_folder(string tableName, string folderID)

General Example

// Gets the parent folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Uses the folder ID to identify the folder we want all the files for
var directoryFiles = db.filetable.tasks.get_all_files_from_folder("filetableName", folderStreamID);

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the folder id using its parent folder id to identify it
string folderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentStreamID, "testingChildFolder");

// Uses the folder ID to identify the folder we want all the files for
var directoryFiles = db.filetable.tasks.get_all_files_from_folder("filetableName", folderStreamID);

get_all_files_from_folder_dt


This function gets all the file data from a folder stream ID in the FileTable.

Parameter Syntax

DataTable get_all_files_from_folder_dt(string tableName, string folderID)

General Example

// Gets the parent folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Uses the folder ID to identify the folder we want all the files for
var directoryFiles = db.filetable.tasks.get_all_files_from_folder_dt("filetableName", folderStreamID);

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the folder id using its parent folder id to identify it
string folderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentStreamID, "testingChildFolder");

// Uses the folder ID to identify the folder we want all the files for
var directoryFiles = db.filetable.tasks.get_all_files_from_folder_dt("filetableName", folderStreamID);

get_all_files_from_root


This function gets all the filenames and file data from the root directory in the FileTable.

Parameter Syntax

Dictionary<string, byte[]> get_all_files_from_root(string tableName)

General Example

var rootFiles = db.filetable.tasks.get_all_files_from_root("filetableName");

get_all_files_from_root_dt


This function gets all the file data from the root directory in the FileTable.

Parameter Syntax

DataTable get_all_files_from_root_dt(string tableName)

General Example

var rootFileData = db.filetable.tasks.get_all_files_from_root_dt("filetableName");

get_all_folder_names_from_folder


This function gets all the folder names within another folder from a folder stream ID in the FileTable.

Parameter Syntax

string[] get_all_folder_names_from_folder(string tableName, string folderID)

General Example

// Gets the folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingFolder");

// Gets all the folder names in the folder using its folder id to identify them
string[] folderNames = db.filetable.tasks.get_all_folder_names_from_folder("filetableName", folderStreamID);

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the child folder id using its parent folder id to identify it
string childFolderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentFolderStreamID, "testingChildFolder");

// Gets all the folder names in the folder using its folder id to identify them
string[] folderNames = db.filetable.tasks.get_folder_id("filetableName", childFolderStreamID);

get_all_folder_names_from_root


This function gets all the folder names that are in the root level of the FileTable.

Parameter Syntax

string[] get_all_folder_names_from_root(string tableName)

General Example

string[] folderNames = db.filetable.tasks.get_all_folder_names_from_root("filetableName");

get_all_folders_from_folder


This function gets all the folder data within another folder from a folder stream ID in the FileTable.

Parameter Syntax

DataTable get_all_folders_from_folder(string tableName, string folderID)

General Example

// Gets the folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets all the folder data from the parent folder using its folder id to identify it
DataTable folderData = db.filetable.tasks.get_all_folders_from_folder("filetableName", folderStreamID);

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the child folder id using its parent folder id to identify it
string childFolderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentFolderStreamID, "testingChildFolder");

// Gets all the folder data from the parent folder using its folder id to identify it
DataTable folderData = db.filetable.tasks.get_all_folders_from_folder("filetableName", childFolderStreamID);

get_all_folders_from_root_dt


This function gets all the folder data from the root directory in the FileTable.

Parameter Syntax

DataTable get_all_folders_from_root_dt(string tableName)

General Example

DataTable rootFolderData = db.filetable.tasks.get_all_folders_from_root_dt("filetableName");

get_all_from_folder


This function gets all the file and folder data from a folder stream ID in the FileTable.

Parameter Syntax

DataTable get_all_from_folder(string tableName, string folderID)

General Example

// Gets the parent folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets all the file and folder data using the folder id to identify it
DataTable data = db.filetable.tasks.get_all_from_folder("filetableName", folderStreamID);

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the folder id using its parent folder id to identify it
string folderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentStreamID, "testingChildFolder");

// Gets all the file and folder data using the folder id to identify it
DataTable data = db.filetable.tasks.get_all_from_folder("filetableName", folderStreamID);

get_all_from_root


This function gets all the file and folder data from the root directory in the FileTable.

Parameter Syntax

DataTable get_all_from_root(string tableName)

General Example

DataTable rootData = db.filetable.tasks.get_all_from_root("filetableName");

get_all_root_folders


This function gets the folder data the root level of the FileTable.

Parameter Syntax

DataTable get_all_root_folders(string tableName)

General Example

DataTable rootFolderData = db.filetable.tasks.get_all_root_folders("filetableName");

get_file


This function gets the filename and file data for a file by using its stream ID in the FileTable.

Parameter Syntax

Dictionary<string, byte[]> get_file(string tableName, string fileID)

Root Example

// Gets the root file stream ID
string fileStreamID = db.filetable.tasks.get_root_file_id("filetableName", "example.txt");

// Gets the file data by using its stream ID to identify it
var fileData = db.filetable.tasks.get_file("filetableName", fileStreamID);

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the file id using its parent folder id to identify it
string fileStreamID = db.filetable.tasks.get_folder_id("filetableName", parentFolderStreamID, "example.txt");

// Gets the file data by using its stream ID to identify it
var fileData = db.filetable.tasks.get_file("filetableName", fileStreamID);

get_file_id


This function gets the stream ID for a file thats within a folder in the FileTable.

Parameter Syntax

string get_file_id(string tableName, string parentFolderID, string fileName)

General Example

// Gets the parent folder id at the root level of the FileTable
string parentFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the file id using its parent folder id to identify it
string fileStreamID = db.filetable.tasks.get_folder_id("filetableName", parentFolderStreamID, "example.txt");

get_folder_id


This function gets the stream ID for a folder thats within another folder in the FileTable.

Parameter Syntax

string get_folder_id(string tableName, string parentFolderID, string folderName)

General Example

// Gets the parent folder id at the root level of the FileTable
string parentFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the child folder id using its parent folder id to identify it
string childFolderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentFolderStreamID, "testingChildFolder");

get_path_locator


This function gets the path locator for a specified stream ID in the FileTable.

Parameter Syntax

string get_path_locator(string tableName, string streamID)

General Example

// Gets the parent folder id at the root level of the FileTable
string parentFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the child folders id using its parent folder id to identify it
string folderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentFolderStreamID, "testingChildFolder");

// Gets the folder path locator from its stream ID
string pathLocator = db.filetable.tasks.get_path_locator("filetableName", folderStreamID);

get_root_file_id


This function gets the stream ID for a file at the root level of the FileTable.

Parameter Syntax

string get_root_file_id(string tableName, string fileName)

General Example

string fileStreamID = db.filetable.tasks.get_root_file_id("filetableName", "example.txt");

get_root_folder_id


This function gets the stream ID for a folder at the root level of the FileTable.

Parameter Syntax

string get_root_folder_id(string tableName, string folderName)

General Example

string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingFolder");

create_file_at_root


This function creates a file at the root level in the FileTable.

Parameter Syntax

void create_file_at_root(string tableName, string fileName, byte[] fileData, bool incrementNameIfExists = true)

General Example

// Reads the file into a byte array
byte[] fileData = System.IO.File.ReadAllBytes(@"C:\example.txt");

// Creates the file in the filetable using the byte array
db.filetable.tasks.create_file_at_root("filetableName", "example.txt", fileData);

create_file_in_folder


This function creates a file in a folder by using a folder stream ID in the FileTable.

Parameter Syntax

void create_file_in_folder(string tableName, string folderID, string fileName, byte[] fileData, bool incrementNameIfExists = true)

General Example

// Reads the file into a byte array
byte[] fileData = System.IO.File.ReadAllBytes(@"C:\example.txt");

// Gets the folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingFolder")

// Creates the file in the filetable using the byte array
db.filetable.tasks.create_file_in_folder("filetableName", folderStreamID,  "example.txt", fileData);

Multi Folder Layer Example

// Reads the file into a byte array
byte[] fileData = System.IO.File.ReadAllBytes(@"C:\example.txt");

// Gets the parent folder id at the root level of the FileTable
string parentFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the child folder id using its parent folder id to identify it
string childFolderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentFolderStreamID, "testingChildFolder");

// Creates the file in the filetable using the byte array
db.filetable.tasks.create_file_in_folder("filetableName", childFolderStreamID,  "example.txt", fileData);

create_folder


This function creates a folder in another folder by using a folder stream ID in the FileTable.

Parameter Syntax

void create_folder(string tableName, string folderID, string folderName, bool incrementNameIfExists = true)

General Example

// Gets the folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingFolder")

// Creates the folder in the filetable using the folder id to identify the parent folder
db.filetable.tasks.create_folder("filetableName", folderStreamID,  "childTestingFolder");

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the child folder id using its parent folder id to identify it
string childFolderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentFolderStreamID, "testingChildFolder");

// Creates the folder in the filetable using the folder id to identify the parent folder
db.filetable.tasks.create_folder("filetableName", childFolderStreamID,  "childTestingFolder");

create_root_folder


This function creates a folder at the root level in the FileTable.

Parameter Syntax

void create_root_folder(string tableName, string folderName, bool incrementNameIfExists = true)

General Example

db.filetable.tasks.create_root_folder("filetableName", "testingFolder");

update_name


This function updates a file or folder name for a specified stream ID in the FileTable.

Parameter Syntax

void update_name(string tableName, string newName, string streamID)

File Example

// Gets the root file stream ID
string fileStreamID = db.filetable.tasks.get_root_file_id("filetableName", "example.txt");

// Updates the filename in the FileTable
db.filetable.tasks.update_name("filetableName", "newName.txt", fileStreamID);

Folder Example

// Gets the parent folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingFolder");

// Updates the folder name in the FileTable
db.filetable.tasks.update_name("filetableName", "newFolderName", folderStreamID);

update_file_location_to_root


Note: This function will not run if the file already exists at the root.

This function updates a files location from a directory to the root for a specified stream ID in the FileTable.

Parameter Syntax

void update_file_location_to_root(string tableName, string streamID)

General Example

// Gets the folders stream ID
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "folderNameExample");

// Gets the files stream ID
string fileStreamID = db.filetable.tasks.get_file_id("filetableName", folderStreamID, "example.txt");

// Updates the files location 
db.filetable.tasks.update_file_location_to_root("filetableName", fileStreamID);

update_folder_location_to_root


Note: This function will not run if the folder already exists at the root.

This function updates a folders location and all its content to the root for a specified stream ID in the FileTable.

Parameter Syntax

void update_folder_location_to_root(string tableName, string streamID)

General Example

// Gets the folders stream ID
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "folderNameExample");

// Gets the sub folders stream ID
string subFolderStreamID = db.filetable.tasks.get_folder_id("filetableName", folderStreamID, "folderNameExample2");

// Updates the folders location 
db.filetable.tasks.update_folder_location_to_root("filetableName", subFolderStreamID);

update_file_location


Note: This function will not run if the file already exists at the new location.

This function updates a files location from one directory to another using the Stream ID of the new folder and files in the FileTable.

Parameter Syntax

void update_file_location(string tableName, string newFolderID, string streamID)

General Example

// Gets the new folders stream ID
string newFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "folderNameExample");

// Gets the file stream ID
string fileStreamID = db.filetable.tasks.get_root_file_id("filetableName", "example.txt");

// Updates the files location 
db.filetable.tasks.update_file_location("filetableName", newFolderStreamID, fileStreamID);

update_folder_location


Note: This function will not run if the folder already exists at the new location.

This function updates a folders location from one directory to another using the Stream ID of the new and old folder in the FileTable.

Parameter Syntax

void update_folder_location(string tableName, string newFolderID, string streamID)

General Example

// Gets the new folders stream ID
string newFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "folderNameExample");

// Gets the old folders stream ID
string oldFolderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "folderNameExample2");

// Updates the folders location 
db.filetable.tasks.update_folder_location("filetableName", newFolderStreamID, oldFolderStreamID);

search_all_file_context


This function converts all the files in the FileTable to a text string and then returns the data for those found that match the search criteria.

Parameter Syntax

DataTable search_all_file_context(string tableName, string searchContext)

General Example

DataTable foundFiles = db.filetable.tasks.search_all_file_context("filetableName", "search string here");

search_all_file_context_in_folder


This function converts all the files in a specified folder in the FileTable to a text string and then returns the data for those found that match the search criteria.

Parameter Syntax

DataTable search_all_file_context_in_folder(string tableName, string parentDirectoryID, string searchContext)

General Example

// Gets the folder id at the root level of the FileTable
string folderStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingFolder")

// Gets all the files which contain the search criteria that are within the specified folder
DataTable foundFiles = db.filetable.tasks.search_all_file_context_in_folder("filetableName", folderStreamID, "search string here");

Multi Folder Layer Example

// Gets the parent folder id at the root level of the FileTable
string parentStreamID = db.filetable.tasks.get_root_folder_id("filetableName", "testingParentFolder");

// Gets the folder id using its parent folder id to identify it
string folderStreamID = db.filetable.tasks.get_folder_id("filetableName", parentStreamID, "testingChildFolder");

// Gets all the files which contain the search criteria that are within the specified folder
DataTable foundFiles = db.filetable.tasks.search_all_file_context_in_folder("filetableName", folderStreamID, "search string here");

search_all_file_context_in_root


This function converts all the files at the root level only in the FileTable to a text string and then returns the data for those found that match the search criteria.

Parameter Syntax

DataTable search_all_file_context_in_root(string tableName, string searchContext)

General Example

DataTable foundFiles = db.filetable.tasks.search_all_file_context_in_root("filetableName", "search string here");