Class Database

Namespace: Dynamicweb.Data

Assembly: Dynamicweb.Core.dll

This class contains functions to handle database connection and interaction.

Database	Function to call
Create a database connection	CreateConnection()
Create a DataReader	CreateDataReader(CommandBuilder)
Create a DataTable	CreateDataTable(string)
Create a DataSet	CreateDataSet(string)
Execute a non-query	ExecuteNonQuery(string)
Create a CommandBuilder	CreateCommandBuilder(ref IDbDataAdapter)
Create a DataAdapter	CreateAdapter()

public sealed class Database

Inheritance: object

Database

Inherited Members: object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.ReferenceEquals(object, object)

object.ToString()

Examples

using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateConnection
    {

        public void ConnectToDatabase()
        {
            //Create a connection to default database
            using (var myConnection = Database.CreateConnection())
            {
                //Create a command object from the connection 
                using (var myCommand = myConnection.CreateCommand())
                {
                    //Create a DataAdapter
                    var daAdapter = Database.CreateAdapter();

                    //Prepare command object
                    myCommand.CommandText = "SELECT TOP 1 * FROM Page";
                    daAdapter.SelectCommand = myCommand;

                    //Fill a dataset
                    var myDataSet = new DataSet();
                    daAdapter.Fill(myDataSet);
                }
            }

            //Create a connection to another database in /Database folder when running Access
            using (var myConnection = Database.CreateConnection())
            {
                //Do stuff witht the connection
            }

			var con = (System.Data.SqlClient.SqlConnection)Database.CreateConnection();
		}
    }
}

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Properties

ConnectionString

Gets the connection string. ONLY MSSQL!

public static string ConnectionString { get; }

Property Value

string: The connection string.

LocalAddress

Gets or sets the local alternative ip address.

public static string LocalAddress { get; set; }

Property Value

string: The local addr.

Methods

AddBooleanParam(IDbCommand, string, bool)

Adds boolean parameter to SQL statement.

public static void AddBooleanParam(IDbCommand cmd, string name, bool value)

Parameters

cmd IDbCommand: The IDbCommand to add the parameter to
name string: The name of the System.Data.IDataParameter.
value bool: The value of the parameter.

AddDateTimeParam(IDbCommand, string, DateTime)

Adds DateTime parameter to SQL statement.

public static void AddDateTimeParam(IDbCommand cmd, string name, DateTime value)

Parameters

cmd IDbCommand: The IDbCommand to add the parameter to
name string: The name of the System.Data.IDataParameter.
value DateTime: The value of the parameter.

AddDoubleParam(IDbCommand, string, double)

Adds double parameter to SQL statement.

public static void AddDoubleParam(IDbCommand cmd, string name, double value)

Parameters

cmd IDbCommand: The IDbCommand to add the parameter to
name string: The name of the System.Data.IDataParameter.
value double: The value of the parameter.

AddInt16Param(IDbCommand, string, short)

Adds Int16 parameter to SQL statement.

public static void AddInt16Param(IDbCommand cmd, string name, short value)

Parameters

cmd IDbCommand: The IDbCommand to add the parameter to
name string: The name of the System.Data.IDataParameter.
value short: The value of the parameter.

AddInt32Param(IDbCommand, string, int)

Adds Int32 parameter to SQL statement.

public static void AddInt32Param(IDbCommand cmd, string name, int value)

Parameters

cmd IDbCommand: The IDbCommand to add the parameter to
name string: The name of the System.Data.IDataParameter.
value int: The value of the parameter.

AddStringParam(IDbCommand, string, string?)

Adds string parameter to SQL statement.

public static void AddStringParam(IDbCommand cmd, string name, string? value)

Parameters

cmd IDbCommand: The IDbCommand to add the parameter to
name string: The name of the System.Data.IDataParameter.
value string: The value of the parameter.

ColumnExists(IDataReader, string)

Checks if a columns the exists in a given datareader.

public static bool ColumnExists(IDataReader reader, string columnName)

Parameters

reader IDataReader: The datareader.
columnName string: Name of the column.

Returns

bool: true if the column exists; otherwise false

CopyRow(IDbCommand, IDictionary<string, object>)

Copies a row and replaces some values in the new row

public static void CopyRow(IDbCommand command, IDictionary<string, object> newValues)

Parameters

command IDbCommand: The IDbCommand to select the row to copy
newValues IDictionary<string, object>: A dictionary of (column name, new value). These values will be set on the new row

Remarks

If more rows match the query, only the first will be copied. See CopyRow(string, Dictionary<string, Tuple<object, DbType>>, IDictionary<string, object>) for example

CopyRow(string, Dictionary<string, Tuple<object, DbType>>, IDictionary<string, object>)

Copies a row and replaces some values in the new row

public static void CopyRow(string selectSql, Dictionary<string, Tuple<object, DbType>> sqlParams, IDictionary<string, object> newValues)

Parameters

selectSql string: The SQL command text to select the row to copy
sqlParams Dictionary<string, Tuple<object, DbType>>: Command parameters dictionary with key as a param name and value of tuple(param value, param db type)
newValues IDictionary<string, object>: A dictionary of (column name, new value). These values will be set on the new row

Remarks

If more rows match the SQL, only the first will be copied

CreateAdapter()

Creates a DataAdapter based on the database type of the solution.

public static IDbDataAdapter CreateAdapter()

Returns

IDbDataAdapter: A SqlDataAdapter or OleDbDataAdapter depending on the database type.

Examples

using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateConnection
    {

        public void ConnectToDatabase()
        {
            //Create a connection to default database
            using (var myConnection = Database.CreateConnection())
            {
                //Create a command object from the connection 
                using (var myCommand = myConnection.CreateCommand())
                {
                    //Create a DataAdapter
                    var daAdapter = Database.CreateAdapter();

                    //Prepare command object
                    myCommand.CommandText = "SELECT TOP 1 * FROM Page";
                    daAdapter.SelectCommand = myCommand;

                    //Fill a dataset
                    var myDataSet = new DataSet();
                    daAdapter.Fill(myDataSet);
                }
            }

            //Create a connection to another database in /Database folder when running Access
            using (var myConnection = Database.CreateConnection())
            {
                //Do stuff witht the connection
            }

			var con = (System.Data.SqlClient.SqlConnection)Database.CreateConnection();
		}
    }
}

Remarks

The type of the DataAdapter is based on the database type set up for the current solution.

CreateAdapter(CommandBuilder)

Creates a DataAdapter based on the database type of the command.

public static IDbDataAdapter CreateAdapter(CommandBuilder commandBuilder)

Parameters

commandBuilder CommandBuilder: The CommandBuilder to create the IDbDataAdapter from.

Returns

IDbDataAdapter: A SqlDataAdapter

Examples

using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateConnection
    {

        public void ConnectToDatabase()
        {
            //Create a connection to default database
            using (var myConnection = Database.CreateConnection())
            {
                //Create a command object from the connection 
                using (var myCommand = myConnection.CreateCommand())
                {
                    //Create a DataAdapter
                    var daAdapter = Database.CreateAdapter();

                    //Prepare command object
                    myCommand.CommandText = "SELECT TOP 1 * FROM Page";
                    daAdapter.SelectCommand = myCommand;

                    //Fill a dataset
                    var myDataSet = new DataSet();
                    daAdapter.Fill(myDataSet);
                }
            }

            //Create a connection to another database in /Database folder when running Access
            using (var myConnection = Database.CreateConnection())
            {
                //Do stuff witht the connection
            }

			var con = (System.Data.SqlClient.SqlConnection)Database.CreateConnection();
		}
    }
}

CreateAdapter(IDbCommand)

Creates a DataAdapter based on the database type of the command.

public static IDbDataAdapter CreateAdapter(IDbCommand command)

Parameters

command IDbCommand: The IDbCommand to create the IDbDataAdapter from.

Returns

IDbDataAdapter: A SqlDataAdapter

Examples

using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateConnection
    {

        public void ConnectToDatabase()
        {
            //Create a connection to default database
            using (var myConnection = Database.CreateConnection())
            {
                //Create a command object from the connection 
                using (var myCommand = myConnection.CreateCommand())
                {
                    //Create a DataAdapter
                    var daAdapter = Database.CreateAdapter();

                    //Prepare command object
                    myCommand.CommandText = "SELECT TOP 1 * FROM Page";
                    daAdapter.SelectCommand = myCommand;

                    //Fill a dataset
                    var myDataSet = new DataSet();
                    daAdapter.Fill(myDataSet);
                }
            }

            //Create a connection to another database in /Database folder when running Access
            using (var myConnection = Database.CreateConnection())
            {
                //Do stuff witht the connection
            }

			var con = (System.Data.SqlClient.SqlConnection)Database.CreateConnection();
		}
    }
}

Remarks

The type of the DataAdapter is based on the IDbCommand type.

CreateCommandBuilder(ref IDbDataAdapter)

Creates a CommandBuilder based for the specified DataAdapters.

public static DbCommandBuilder CreateCommandBuilder(ref IDbDataAdapter adapter)

Parameters

adapter IDbDataAdapter: An existing DataAdapter.

Returns

DbCommandBuilder: A SqlCommandBuilder.

Remarks

A CommandBuilder automatically generates Insert, Update and Delete commands based on a DataAdapters Select command

CreateConnection()

Creates and opens a database connection to the default database

public static IDbConnection CreateConnection()

Returns

IDbConnection: A Database Connection (a IDbConnection object). The connection is connected to /Database/Dynamic.mdb if the solution runs on MS Access or to the SQL Server database specified in database setup (/Files/GlobalSettings.aspx) if the solution runs on MS SQL-Server.

Examples

using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateConnection
    {

        public void ConnectToDatabase()
        {
            //Create a connection to default database
            using (var myConnection = Database.CreateConnection())
            {
                //Create a command object from the connection 
                using (var myCommand = myConnection.CreateCommand())
                {
                    //Create a DataAdapter
                    var daAdapter = Database.CreateAdapter();

                    //Prepare command object
                    myCommand.CommandText = "SELECT TOP 1 * FROM Page";
                    daAdapter.SelectCommand = myCommand;

                    //Fill a dataset
                    var myDataSet = new DataSet();
                    daAdapter.Fill(myDataSet);
                }
            }

            //Create a connection to another database in /Database folder when running Access
            using (var myConnection = Database.CreateConnection())
            {
                //Do stuff witht the connection
            }

			var con = (System.Data.SqlClient.SqlConnection)Database.CreateConnection();
		}
    }
}

Remarks

The returned IDbConnection instance is either a SqlConnection or a OleDbConnection depending on the database type.

CreateDataReader(CommandBuilder)

Creates a DataReader by creating a IDbCommand with the CommandBuilder and sending it to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(CommandBuilder commandBuilder)

Parameters

commandBuilder CommandBuilder: The CommandBuilder specifying the data to return in the DataReader

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataReader(CommandBuilder, IDbConnection)

Creates a DataReader by creating a IDbCommand with the CommandBuilder and sending it to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(CommandBuilder commandBuilder, IDbConnection connection)

Parameters

commandBuilder CommandBuilder: The CommandBuilder specifying the data to return in the DataReader
connection IDbConnection: Existing database connection. The connection will remain open upon the completion.

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataReader(CommandBuilder, IDbConnection, IDbTransaction?)

Creates a DataReader by creating a IDbCommand with the CommandBuilder and sending it to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(CommandBuilder commandBuilder, IDbConnection connection, IDbTransaction? transaction)

Parameters

commandBuilder CommandBuilder: The CommandBuilder specifying the data to return in the DataReader
connection IDbConnection: Existing database connection. The connection will remain open upon the completion.
transaction IDbTransaction: The transaction.

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataReader(CommandBuilder, IDbConnection, int)

Creates a DataReader by creating a IDbCommand with the CommandBuilder and sending it to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(CommandBuilder commandBuilder, IDbConnection connection, int commandTimeout)

Parameters

commandBuilder CommandBuilder: The CommandBuilder specifying the data to return in the DataReader
connection IDbConnection: Existing database connection. The connection will remain open upon the completion.
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataReader(CommandBuilder, int)

Creates a DataReader by creating a IDbCommand with the CommandBuilder and sending it to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(CommandBuilder commandBuilder, int commandTimeout)

Parameters

commandBuilder CommandBuilder: The CommandBuilder specifying the data to return in the DataReader
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataReader(IDbCommand, CommandBehavior)

Creates a DataReader by sending an IDbCommand to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(IDbCommand command, CommandBehavior behavior)

Parameters

command IDbCommand: The Command specifying the data to return in the DataReader
behavior CommandBehavior: The CommandBehavior to be used when executing the IDbCommand.

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataReader(string)

Creates a DataReader by sending the sql to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(string sql)

Parameters

sql string: The SQL specifiyng the data to return in the DataReader

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataReader(string, Dictionary<string, Tuple<object, DbType>>)

Creates a DataReader by sending the sql to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(string sql, Dictionary<string, Tuple<object, DbType>> sqlParams)

Parameters

sql string: The SQL specifiyng the data to return in the DataReader
sqlParams Dictionary<string, Tuple<object, DbType>>: Command parameters dictionary with key as a param name and value of tuple(param value, param db type)

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataReader(string, IDbConnection)

Creates a DataReader by sending the sql to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(string sql, IDbConnection connection)

Parameters

sql string: The SQL specifiyng the data to return in the DataReader
connection IDbConnection: Existing database connection.

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataReader(string, IDbConnection, IDbTransaction?)

Creates a DataReader by sending the sql to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(string sql, IDbConnection connection, IDbTransaction? transaction)

Parameters

sql string: The SQL specifiyng the data to return in the DataReader
connection IDbConnection: Existing database connection.
transaction IDbTransaction: Existing transaction on the given connection.

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataReader(string, IDbConnection, int)

Creates a DataReader by sending the sql to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(string sql, IDbConnection connection, int commandTimeout)

Parameters

sql string: The SQL specifiyng the data to return in the DataReader
connection IDbConnection: Existing database connection.
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds. 0 Indicates infinite.

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataReader(string, int)

Creates a DataReader by sending the sql to the Connection and build an IDataReader.

public static IDataReader CreateDataReader(string sql, int commandTimeout)

Parameters

sql string: The SQL specifiyng the data to return in the DataReader
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds. 0 indicates infinite.

Returns

IDataReader: Datareader instance - OleDBReader for Access solutions and SqlDbReader for SQl-Server based solutions.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataReader
    {
        public void DataReaderSample()
        {
            //Create a DataReader on the default database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM Page"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["PageID"]);
                }
            }

            //Create a DataReader on alternate database
            using (var myDr = Database.CreateDataReader("SELECT TOP 10 * FROM AccessUser"))
            {
                while (myDr.Read())
                {
                    var pageId = Converter.ToInt32(myDr["AccessUserID"]);
                }
            }
        }
    }
}

Remarks

The underlying connection will close itself after the reader is closed and disposed.

CreateDataSet(CommandBuilder)

Creates a DataSet with data returned by the passed SQL statement from the specfied database.

public static DataSet CreateDataSet(CommandBuilder commandBuilder)

Parameters

commandBuilder CommandBuilder: The CommandBuilder specifying the query to execute and data to return in the DataSet.

Returns

DataSet: A disconnected DataSet instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateDataSet
    {
        public void DataSetSample()
        {
            //Create dataset on the default database
            var myDataSet = Database.CreateDataSet("SELECT TOP 10 * FROM Page");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create dataset on alternate database
            var myDataset2 = Database.CreateDataSet("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

Since this is a disconnected DataSet, no updates are possible. If you want to obtain an updatable DataSet use the DataManager. Adds a DataTable named "Table" to the specified DataSet and configures the schema to match that in the data source.

CreateDataSet(CommandBuilder, bool)

Creates a DataSet with data returned by the passed SQL statement from the specfied database.

public static DataSet CreateDataSet(CommandBuilder commandBuilder, bool withSchema)

Parameters

commandBuilder CommandBuilder: The CommandBuilder specifying the query to execute and data to return in the DataSet.
withSchema bool: Set to true if the returned dataset should contain schema information on table names, column names and types etc.

Returns

DataSet: A disconnected DataSet instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateDataSet
    {
        public void DataSetSample()
        {
            //Create dataset on the default database
            var myDataSet = Database.CreateDataSet("SELECT TOP 10 * FROM Page");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create dataset on alternate database
            var myDataset2 = Database.CreateDataSet("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

CreateDataSet(CommandBuilder, IDbConnection)

Creates a DataSet with data returned by the passed SQL statement from the specfied database.

public static DataSet CreateDataSet(CommandBuilder commandBuilder, IDbConnection connection)

Parameters

commandBuilder CommandBuilder: The CommandBuilder specifying the query to execute and data to return in the DataSet.
connection IDbConnection: Existing database connection. The connection will remain open upon the completion.

Returns

DataSet: A disconnected DataSet instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateDataSet
    {
        public void DataSetSample()
        {
            //Create dataset on the default database
            var myDataSet = Database.CreateDataSet("SELECT TOP 10 * FROM Page");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create dataset on alternate database
            var myDataset2 = Database.CreateDataSet("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

CreateDataSet(CommandBuilder, IDbConnection, bool)

Creates a DataSet with data returned by the passed SQL statement from the specfied database.

public static DataSet CreateDataSet(CommandBuilder commandBuilder, IDbConnection connection, bool withSchema)

Parameters

commandBuilder CommandBuilder: The CommandBuilder specifying the query to execute and data to return in the DataSet.
connection IDbConnection: Existing database connection. The connection will remain open upon the completion.
withSchema bool: Set to true if the returned dataset should contain schema information on table names, column names and types etc.

Returns

DataSet: A disconnected DataSet instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateDataSet
    {
        public void DataSetSample()
        {
            //Create dataset on the default database
            var myDataSet = Database.CreateDataSet("SELECT TOP 10 * FROM Page");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create dataset on alternate database
            var myDataset2 = Database.CreateDataSet("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

CreateDataSet(CommandBuilder, IDbConnection, IDbTransaction?)

Creates a DataSet with data returned by the passed SQL statement from the specfied database.

public static DataSet CreateDataSet(CommandBuilder commandBuilder, IDbConnection connection, IDbTransaction? transaction)

Parameters

commandBuilder CommandBuilder: The CommandBuilder specifying the query to execute and data to return in the DataSet.
connection IDbConnection: Existing database connection. The connection will remain open upon the completion.
transaction IDbTransaction: The transaction.

Returns

DataSet: A disconnected DataSet instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateDataSet
    {
        public void DataSetSample()
        {
            //Create dataset on the default database
            var myDataSet = Database.CreateDataSet("SELECT TOP 10 * FROM Page");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create dataset on alternate database
            var myDataset2 = Database.CreateDataSet("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

CreateDataSet(CommandBuilder, IDbConnection, IDbTransaction?, bool)

Creates a DataSet with data returned by the passed SQL statement from the specfied database.

public static DataSet CreateDataSet(CommandBuilder commandBuilder, IDbConnection connection, IDbTransaction? transaction, bool withSchema)

Parameters

commandBuilder CommandBuilder: The CommandBuilder specifying the query to execute and data to return in the DataSet.
connection IDbConnection: Existing database connection. The connection will remain open upon the completion.
transaction IDbTransaction: The transaction.
withSchema bool: Set to true if the returned dataset should contain schema information on table names, column names and types etc.

Returns

DataSet: A disconnected DataSet instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateDataSet
    {
        public void DataSetSample()
        {
            //Create dataset on the default database
            var myDataSet = Database.CreateDataSet("SELECT TOP 10 * FROM Page");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create dataset on alternate database
            var myDataset2 = Database.CreateDataSet("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

CreateDataSet(string)

Creates a DataSet with data returned by the passed SQL statement from the specfied database.

public static DataSet CreateDataSet(string sql)

Parameters

sql string: The SQL specifiyng the data to return in dataset

Returns

DataSet: A disconnected DataSet instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateDataSet
    {
        public void DataSetSample()
        {
            //Create dataset on the default database
            var myDataSet = Database.CreateDataSet("SELECT TOP 10 * FROM Page");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create dataset on alternate database
            var myDataset2 = Database.CreateDataSet("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

Since this is a disconnected dataset, no updates are possible. If you want to obtain an updatable dataset use the DataManager.

CreateDataSet(string, Dictionary<string, Tuple<object, DbType>>)

Creates a DataSet with data returned by the passed SQL statement from the specfied database.

public static DataSet CreateDataSet(string sql, Dictionary<string, Tuple<object, DbType>> sqlParams)

Parameters

sql string: The SQL specifiyng the data to return in dataset
sqlParams Dictionary<string, Tuple<object, DbType>>: Command parameters dictionary with key as a param name and value of tuple(param value, param db type)

Returns

DataSet: A disconnected DataSet instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateDataSet
    {
        public void DataSetSample()
        {
            //Create dataset on the default database
            var myDataSet = Database.CreateDataSet("SELECT TOP 10 * FROM Page");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create dataset on alternate database
            var myDataset2 = Database.CreateDataSet("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

Since this is a disconnected dataset, no updates are possible. If you want to obtain an updatable dataset use the DataManager.

CreateDataSet(string, IDbConnection, IDbTransaction?)

Creates a DataSet with data returned by the passed SQL statement from the specfied database.

public static DataSet CreateDataSet(string sql, IDbConnection connection, IDbTransaction? transaction)

Parameters

sql string: The SQL specifiyng the data to return in dataset
connection IDbConnection: Existing database connection.
transaction IDbTransaction: The transaction.

Returns

DataSet: A disconnected DataSet instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    public class DatabaseCreateDataSet
    {
        public void DataSetSample()
        {
            //Create dataset on the default database
            var myDataSet = Database.CreateDataSet("SELECT TOP 10 * FROM Page");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create dataset on alternate database
            var myDataset2 = Database.CreateDataSet("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in dataset
            foreach (DataRow row in myDataSet.Tables[0].Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

Since this is a disconnected dataset, no updates are possible. If you want to obtain an updatable dataset use the DataManager.

CreateDataTable(string)

Creates a DataTable with data returned by the passed SQL statement.

public static DataTable CreateDataTable(string sql)

Parameters

sql string: The SQL specifiyng the data to return in DataTable

Returns

DataTable: A disconnected DataTable instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataTable
    {
        public void DataTableSample()
        {
            //Create DataTable on the default database
            var myDataTable = Database.CreateDataTable("SELECT TOP 10 * FROM Page");

            //Loop rows in DataTable
            foreach (DataRow row in myDataTable.Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create DataTable on alternate database
            DataTable myDataTable2 = Database.CreateDataTable("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in DataTable
            foreach (DataRow row in myDataTable.Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

Connects to the default database. Dynamic.mdb in /Database if the solution is running MS Access, or the SQL-Server database if the solution is running MS SQL-Server. Since this is a disconnected DataTable, no updates are possible.

CreateDataTable(string, IDbConnection)

Creates a DataTable with data returned by the passed SQL statement from the specfied database.

public static DataTable CreateDataTable(string sql, IDbConnection connection)

Parameters

sql string: The SQL specifiyng the data to return in DataTable
connection IDbConnection: Existing database connection.

Returns

DataTable: A disconnected DataTable instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataTable
    {
        public void DataTableSample()
        {
            //Create DataTable on the default database
            var myDataTable = Database.CreateDataTable("SELECT TOP 10 * FROM Page");

            //Loop rows in DataTable
            foreach (DataRow row in myDataTable.Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create DataTable on alternate database
            DataTable myDataTable2 = Database.CreateDataTable("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in DataTable
            foreach (DataRow row in myDataTable.Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

Since this is a disconnected DataTable, no updates are possible.

CreateDataTable(string, IDbConnection, IDbTransaction?)

Creates a DataTable with data returned by the passed SQL statement from the specfied database.

public static DataTable CreateDataTable(string sql, IDbConnection connection, IDbTransaction? transaction)

Parameters

sql string: The SQL specifiyng the data to return in DataTable
connection IDbConnection: Existing database connection.
transaction IDbTransaction: Existing transaction on the given connection.

Returns

DataTable: A disconnected DataTable instance with data.

Examples

using Dynamicweb.Core;
using System.Data;

namespace Dynamicweb.Data.Examples
{
    class DatabaseCreateDataTable
    {
        public void DataTableSample()
        {
            //Create DataTable on the default database
            var myDataTable = Database.CreateDataTable("SELECT TOP 10 * FROM Page");

            //Loop rows in DataTable
            foreach (DataRow row in myDataTable.Rows)
            {
                var pageId = Converter.ToInt32(row["PageID"]);
            }

            //Create DataTable on alternate database
            DataTable myDataTable2 = Database.CreateDataTable("SELECT TOP 10 * FROM AccessUser");

            //Loop rows in DataTable
            foreach (DataRow row in myDataTable.Rows)
            {
                var userId = Converter.ToInt32(row["AccessUserID"]);
            }
        }
    }
}

Remarks

Since this is a disconnected DataTable, no updates are possible.

CreatePreparedAdapter(CommandBuilder, IDbConnection)

Creates a IDbDataAdapter that is prepared based on the commandBuilder. The IDbDataAdapter supports SELECT, INSERT, UPDATE and DELETE.

public static IDbDataAdapter CreatePreparedAdapter(CommandBuilder commandBuilder, IDbConnection connection)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the SELECT-query.
connection IDbConnection: The IDbConnection to the relevant database.

Returns

IDbDataAdapter: A prepared IDataAdapter with Select-, Insert-, Update- and Delete commands.

CreatePreparedAdapter(CommandBuilder, IDbConnection, bool)

Creates a IDbDataAdapter that is prepared based on the commandBuilder. The IDbDataAdapter supports SELECT, INSERT, UPDATE and DELETE.

public static IDbDataAdapter CreatePreparedAdapter(CommandBuilder commandBuilder, IDbConnection connection, bool tryFillIdentity)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the SELECT-query.
connection IDbConnection: The IDbConnection to the relevant database.
tryFillIdentity bool: Boolean value indicating whether the Identity column should be filled automatically or not. Default is false.

Returns

IDbDataAdapter: A prepared IDataAdapter with Select-, Insert-, Update- and Delete commands.

CreatePreparedAdapter(CommandBuilder, IDbConnection, IDbTransaction?)

Creates a IDbDataAdapter that is prepared based on the commandBuilder. The IDbDataAdapter supports SELECT, INSERT, UPDATE and DELETE.

public static IDbDataAdapter CreatePreparedAdapter(CommandBuilder commandBuilder, IDbConnection connection, IDbTransaction? transaction)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the SELECT-query.
connection IDbConnection: The IDbConnection to the relevant database.
transaction IDbTransaction: The IDbTransaction to use when executing queries

Returns

IDbDataAdapter: A prepared IDataAdapter with Select-, Insert-, Update- and Delete commands.

CreatePreparedAdapter(CommandBuilder, IDbConnection, IDbTransaction?, bool)

Creates a IDbDataAdapter that is prepared based on the commandBuilder. The IDbDataAdapter supports SELECT, INSERT, UPDATE and DELETE.

public static IDbDataAdapter CreatePreparedAdapter(CommandBuilder commandBuilder, IDbConnection connection, IDbTransaction? transaction, bool tryFillIdentity)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the SELECT-query.
connection IDbConnection: The IDbConnection to the relevant database.
transaction IDbTransaction: The IDbTransaction to use when executing queries
tryFillIdentity bool: Boolean value indicating whether the Identity column should be filled automatically or not. Default is false.

Returns

IDbDataAdapter: A prepared IDataAdapter with Select-, Insert-, Update- and Delete commands.

CreatePreparedAdapter(IDbCommand)

Creates a IDbDataAdapter that is prepared based on the command. The IDbDataAdapter supports SELECT, INSERT, UPDATE and DELETE.

public static IDbDataAdapter CreatePreparedAdapter(IDbCommand command)

Parameters

command IDbCommand: The IDbCommand containing the SELECT-query.

Returns

IDbDataAdapter: A prepared IDataAdapter with Select-, Insert-, Update- and Delete commands.

CreatePreparedAdapter(IDbCommand, bool)

Creates a IDbDataAdapter that is prepared based on the command. The IDbDataAdapter supports SELECT, INSERT, UPDATE and DELETE.

public static IDbDataAdapter CreatePreparedAdapter(IDbCommand command, bool tryFillIdentity)

Parameters

command IDbCommand: The IDbCommand containing the SELECT-query.
tryFillIdentity bool: Boolean value indicating whether the Identity column should be filled automatically or not. Default is false.

Returns

IDbDataAdapter: A prepared IDataAdapter with Select-, Insert-, Update- and Delete commands.

CreatePreparedAdapter(string, IDbConnection)

Creates a IDbDataAdapter that is prepared based on the sql parameter. The IDbDataAdapter supports SELECT, INSERT, UPDATE and DELETE.

public static IDbDataAdapter CreatePreparedAdapter(string selectSql, IDbConnection connection)

Parameters

selectSql string: The select SQL.
connection IDbConnection: The IDbConnection to the relevant database.

Returns

IDbDataAdapter

CreatePreparedAdapter(string, IDbConnection, bool)

Creates a IDbDataAdapter that is prepared based on the sql parameter. The IDbDataAdapter supports SELECT, INSERT, UPDATE and DELETE.

public static IDbDataAdapter CreatePreparedAdapter(string selectSql, IDbConnection connection, bool tryFillIdentity)

Parameters

selectSql string: The select SQL.
connection IDbConnection: The IDbConnection to the relevant database.
tryFillIdentity bool: Boolean value indicating whether the Identity column should be filled automatically or not. Default is false.

Returns

IDbDataAdapter

CreatePreparedAdapter(string, IDbConnection, IDbTransaction?)

Creates a IDbDataAdapter that is prepared based on the sql parameter. The IDbDataAdapter supports SELECT, INSERT, UPDATE and DELETE.

public static IDbDataAdapter CreatePreparedAdapter(string selectSql, IDbConnection connection, IDbTransaction? transaction)

Parameters

selectSql string: The select SQL.
connection IDbConnection: The IDbConnection to the relevant database.
transaction IDbTransaction: The IDbTransaction to use when executing queries

Returns

IDbDataAdapter

CreatePreparedAdapter(string, IDbConnection, IDbTransaction?, bool)

Creates a IDbDataAdapter that is prepared based on the sql parameter. The IDbDataAdapter supports SELECT, INSERT, UPDATE and DELETE.

public static IDbDataAdapter CreatePreparedAdapter(string selectSql, IDbConnection connection, IDbTransaction? transaction, bool tryFillIdentity)

Parameters

selectSql string: The select SQL.
connection IDbConnection: The IDbConnection to the relevant database.
transaction IDbTransaction: The IDbTransaction to use when executing queries
tryFillIdentity bool: Boolean value indicating whether the Identity column should be filled automatically or not. Default is false.

Returns

IDbDataAdapter

EscapeForLikeStatement(string, bool)

Wrapes wildcard characters in to [] symbols.

public static string EscapeForLikeStatement(string value, bool escapeApostrophes)

Parameters

value string: Input string.
escapeApostrophes bool: shows whether or not to escape apostrophes

Returns

string: String with wraped symbols.

ExecuteNonQuery(CommandBuilder)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(CommandBuilder commandBuilder)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.

Returns

int: The number of rows affected.

ExecuteNonQuery(CommandBuilder, bool)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(CommandBuilder commandBuilder, bool useTransaction)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.
useTransaction bool: If set to true the query is executed in a transaction.

Returns

int: The number of rows affected.

ExecuteNonQuery(CommandBuilder, bool, int)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(CommandBuilder commandBuilder, bool useTransaction, int commandTimeout)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.
useTransaction bool: If set to true the query is executed in a transaction.
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.

Returns

int: The number of rows affected.

ExecuteNonQuery(CommandBuilder, IDbConnection)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(CommandBuilder commandBuilder, IDbConnection connection)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.
connection IDbConnection: Existing database connection to be reused. The connection will not be closed upon completion.

Returns

int: The number of rows affected.

ExecuteNonQuery(CommandBuilder, IDbConnection, bool)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(CommandBuilder commandBuilder, IDbConnection connection, bool useTransaction)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.
connection IDbConnection: Existing database connection to be reused. The connection will not be closed upon completion.
useTransaction bool: If set to true the query is executed in a transaction.

Returns

int: The number of rows affected.

ExecuteNonQuery(CommandBuilder, IDbConnection, bool, int)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(CommandBuilder commandBuilder, IDbConnection connection, bool useTransaction, int commandTimeout)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.
connection IDbConnection: Existing database connection to be reused. The connection will not be closed upon completion.
useTransaction bool: If set to true the query is executed in a transaction.
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.

Returns

int: The number of rows affected.

ExecuteNonQuery(CommandBuilder, IDbConnection, IDbTransaction?)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(CommandBuilder commandBuilder, IDbConnection connection, IDbTransaction? transaction)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.
connection IDbConnection: Existing database connection to be reused. The connection will not be closed upon completion.
transaction IDbTransaction: The transaction associated with the specified connection.

Returns

int: The number of rows affected.

ExecuteNonQuery(CommandBuilder, IDbConnection, IDbTransaction?, int)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(CommandBuilder commandBuilder, IDbConnection connection, IDbTransaction? transaction, int commandTimeout)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.
connection IDbConnection: Existing database connection to be reused. The connection will not be closed upon completion.
transaction IDbTransaction: The transaction associated with the specified connection.
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.

Returns

int: The number of rows affected.

ExecuteNonQuery(CommandBuilder, IDbConnection, int)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(CommandBuilder commandBuilder, IDbConnection connection, int commandTimeout)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.
connection IDbConnection: Existing database connection to be reused. The connection will not be closed upon completion.
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.

Returns

int: The number of rows affected.

ExecuteNonQuery(CommandBuilder, int)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(CommandBuilder commandBuilder, int commandTimeout)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.

Returns

int: The number of rows affected.

ExecuteNonQuery(IDbCommand)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(IDbCommand command)

Parameters

command IDbCommand: The IDbCommand containing the query to execute.

Returns

int: The number of rows affected.

ExecuteNonQuery(string)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(string sql)

Parameters

sql string: The SQL to execute against the database

Returns

int: The number of rows affected.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteNonQuery(string, bool)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(string sql, bool transaction)

Parameters

sql string: The SQL to execute against the database
transaction bool: if set to true [transaction].

Returns

int: The number of rows affected.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteNonQuery(string, bool, IDbConnection)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(string sql, bool transaction, IDbConnection connection)

Parameters

sql string: The SQL to execute against the database
transaction bool: if set to true [transaction].
connection IDbConnection: Existing database connection.

Returns

int: The number of rows affected.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteNonQuery(string, Dictionary<string, Tuple<object, DbType>>)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(string sql, Dictionary<string, Tuple<object, DbType>> sqlParams)

Parameters

sql string: The SQL to execute against the database
sqlParams Dictionary<string, Tuple<object, DbType>>: Command parameters dictionary with key as a param name and value of tuple(param value, param db type)

Returns

int: The number of rows affected.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteNonQuery(string, IDbConnection)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(string sql, IDbConnection connection)

Parameters

sql string: The SQL to execute against the database
connection IDbConnection: Existing database connection.

Returns

int: The number of rows affected.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteNonQuery(string, IDbConnection, IDbTransaction?)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(string sql, IDbConnection connection, IDbTransaction? transaction)

Parameters

sql string: The SQL to execute against the database
connection IDbConnection: Existing database connection.
transaction IDbTransaction: The transaction.

Returns

int: The number of rows affected.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteNonQuery(string, IDbConnection, IDbTransaction?, int)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(string sql, IDbConnection connection, IDbTransaction? transaction, int commandTimeout)

Parameters

sql string: The SQL to execute against the database
connection IDbConnection: Existing database connection.
transaction IDbTransaction: The transaction.
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.

Returns

int: The number of rows affected.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteNonQuery(string, int)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(string sql, int commandTimeout)

Parameters

sql string: The SQL to execute against the database
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.

Returns

int: The number of rows affected.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteNonQuery(string, int, bool)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(string sql, int commandTimeout, bool useTransaction)

Parameters

sql string: The SQL to execute against the database
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.
useTransaction bool: If set to true the query is executed in a transaction.

Returns

int: The number of rows affected.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteNonQuery(string, int, bool, IDbConnection)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(string sql, int commandTimeout, bool useTransaction, IDbConnection connection)

Parameters

sql string: The SQL to execute against the database
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.
useTransaction bool: If set to true the query is executed in a transaction.
connection IDbConnection: Existing database connection.

Returns

int: The number of rows affected.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteNonQuery(string, int, IDbConnection)

Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.

public static int ExecuteNonQuery(string sql, int commandTimeout, IDbConnection connection)

Parameters

sql string: The SQL to execute against the database
commandTimeout int: The wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.
connection IDbConnection: Existing database connection.

Returns

int: The number of rows affected.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteScalar(CommandBuilder)

Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.

public static object? ExecuteScalar(CommandBuilder commandBuilder)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.

Returns

object: The first column of the first row in the resultset.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteScalar(CommandBuilder, IDbConnection?)

Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.

public static object? ExecuteScalar(CommandBuilder commandBuilder, IDbConnection? connection)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.
connection IDbConnection: Existing database connection. The connection will remain open upon the completion.

Returns

object: The first column of the first row in the resultset.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteScalar(CommandBuilder, IDbConnection, IDbTransaction?)

Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.

public static object? ExecuteScalar(CommandBuilder commandBuilder, IDbConnection connection, IDbTransaction? transaction)

Parameters

commandBuilder CommandBuilder: The CommandBuilder containing the query to execute.
connection IDbConnection: Existing database connection. The connection will remain open upon the completion.
transaction IDbTransaction: The transaction associated with specified connection.

Returns

object: The first column of the first row in the resultset.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteScalar(IDbCommand)

Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.

public static object? ExecuteScalar(IDbCommand command)

Parameters

command IDbCommand: The IDbCommand containing the query to execute.

Returns

object: The first column of the first row in the resultset.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteScalar(string)

Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.

public static object? ExecuteScalar(string sql)

Parameters

sql string: The SQL to execute against the database

Returns

object: The first column of the first row in the resultset.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

Remarks

Connects to the default database. Dynamic.mdb in /Database if the solution is running MS Access, or the SQL-Server database if the solution is running MS SQL-Server.

ExecuteScalar(string, IDbConnection)

Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.

public static object? ExecuteScalar(string sql, IDbConnection connection)

Parameters

sql string: The SQL to execute against the database
connection IDbConnection: Existing database connection.

Returns

object: The first column of the first row in the resultset.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

ExecuteScalar(string, IDbConnection, IDbTransaction?)

Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.

public static object? ExecuteScalar(string sql, IDbConnection connection, IDbTransaction? transaction)

Parameters

sql string: The SQL to execute against the database
connection IDbConnection: Existing database connection.
transaction IDbTransaction: The transaction.

Returns

object: The first column of the first row in the resultset.

Examples

using Dynamicweb.Core;

namespace Dynamicweb.Data.Examples
{
    class DatabaseExecute
    {
        public void ExecuteSample()
        {
            //ExecuteScalar
            var pageCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(PageID) FROM Page"));

            //ExecuteScalar on alternate database
            var userCount = Converter.ToInt32(Database.ExecuteScalar("SELECT COUNT(AccessUserID) FROM AccessUser"));

            //ExecuteNonQuery
            var numberOfPagesDeleted = Database.ExecuteNonQuery("DELETE FROM Page WHERE PageID < 101");

            //ExecuteNonQuery on alternate database
            var numberOfUsersDeleted = Database.ExecuteNonQuery("DELETE FROM AccessUser WHERE AccessUserID < 101");
        }
    }
}

GetAddedIdentityKey(ref DataRow, ref IDbConnection, int)

The function returns the identity of the last added row to the database

public static int GetAddedIdentityKey(ref DataRow row, ref IDbConnection connection, int index)

Parameters

row DataRow: The datarow to update.
connection IDbConnection: The connection used to update the datarow.
index int: Index of the column with the primary ID.

Returns

int: The ID of the updated database row is returned as an integer.

GetAddedIdentityKey(ref IDbConnection)

The function returns the identity of the last added row to the database

public static int GetAddedIdentityKey(ref IDbConnection connection)

Parameters

connection IDbConnection: The connection used for the update

Returns

int: The identity of the last added row

Remarks

Returns an integer - this method will only work on tables with a primary key of type identity (SQL-Server), or Auto (MS-Access)

GetAddedIdentityKey(ref IDbConnection, IDbTransaction?)

The function returns the identity of the last added row to the database

public static int GetAddedIdentityKey(ref IDbConnection connection, IDbTransaction? transaction)

Parameters

connection IDbConnection: The connection used for the update
transaction IDbTransaction: The transaction used for the update

Returns

int: The identity of the last added row

Remarks

Returns an integer - this method will only work on tables with a primary key of type identity (SQL-Server), or Auto (MS-Access)

GetCustomDatabaseConnectionProvider()

Gets the custom database connection provider.

public static IDatabaseConnectionProvider? GetCustomDatabaseConnectionProvider()

Returns

IDatabaseConnectionProvider

GetParam(string, DbType, int, string)

Gets current parameter from database.

public static IDbDataParameter GetParam(string paramName, DbType paramType, int paramSize, string paramSourceColumn)

Parameters

paramName string
paramType DbType
paramSize int
paramSourceColumn string

Returns

IDbDataParameter: Current parameter as IDbDataParameter object.

GetParam(string, DbType, int, string, object?)

Gets current parameter from database.

public static IDbDataParameter GetParam(string paramName, DbType paramType, int paramSize, string paramSourceColumn, object? paramValue)

Parameters

paramName string
paramType DbType
paramSize int
paramSourceColumn string
paramValue object

Returns

IDbDataParameter: Current parameter as IDbDataParameter object.

InsertRows(DataTable)

Bulk inserts the given data into the database.

public static void InsertRows(DataTable data)

Parameters

data DataTable: Data table specifying the data to insert.

Exceptions

ArgumentNullException: data is null.
ArgumentException: data has a TableName property which is either null or empty string or data contains no columns.

SortListByDataSet(ref DataSet, string, string, string, int)

Sorts the first table in the specified dataset.

public static string SortListByDataSet(ref DataSet dataSet, string dataSetPrimaryId, string dataSetSort, string direction, int inputPrimaryId)

Parameters

dataSet DataSet: The dataset to be sorted
dataSetPrimaryId string: Name of the column with the primary id in the dataset.
dataSetSort string: Name of the column, the dataset should be sorted by.
direction string: The direction the selected row should be moved ("up" or "down").
inputPrimaryId int: The primary ID of the datarow that needs to be moved up or down.

Returns

string

Remarks

The table is sorted according to the specified column.

SqlBool(bool)

Returns a Boolean in the appropriate database format.

public static string SqlBool(bool value)

Parameters

value bool: true or false.

Returns

string: True or False as strings for Access solutions, or 0 or 1 for SQL solutions

Remarks

The returned value is based on the database type set up for the particular solution. Used only for building sql strings, not for DataRows.

SqlBool(int)

Returns a Boolean in the appropriate database format.

public static string SqlBool(int value)

Parameters

value int: Integer representing a Boolean. 1 = True, 0 = False.

Returns

string: True or False as strings for Access solutions, or 0 or 1 for SQL solutions

Remarks

The returned value is based on the database type set up for the particular solution. Used only for building sql strings, not for DataRows.

SqlDate(DateTime)

Returns a date in the appropriate database format.

public static string SqlDate(DateTime dateTime)

Parameters

dateTime DateTime: Date as DateTime

Returns

string: Returns a string representation of the sqldate.

Remarks

The string format is based on the database type set up for the particular solution.

SqlDate(DateTime, bool)

Returns a date in the appropriate database format.

public static string SqlDate(DateTime dateTime, bool skipTime)

Parameters

dateTime DateTime: Date as DateTime
skipTime bool: Indicates whether to skip the time part of the date

Returns

string: Returns a string representation of the sql date.

Remarks

The string format is based on the database type set up for the particular solution.

SqlDate(string)

Returns a date in the appropriate database format.

public static string SqlDate(string dateTime)

Parameters

dateTime string: The date time as a string.

Returns

string: Returns a string representation of the sql date.

Remarks

The string format is based on the database type set up for the particular solution.

SqlDatePart(string)

Returns a DatePart abbreviation for the appropriate database.

public static string SqlDatePart(string part)

Parameters

part string

Returns

string: Returns a string with the DatePart abbreviation, e.g. "yy" or "yyyy" etc.

Remarks

The abbreviation is based on the database type set up for the particular solution.

SqlEscapeInjection(string, bool)

Strips a string of all SQL-commands used to create SQL-injection.

public static string SqlEscapeInjection(string text, bool escapeApostrophes)

Parameters

text string: The text being checked
escapeApostrophes bool: Value indicating whether to escape single apostrophes by replacing them with double apostrohpes.

Returns

string

Remarks

This function must be used before user input is inserted into SQL-strings.

SqlString(string)

Returns a string quoted with '' for Access and N'' (Unicode) for SQL-Server.

public static string SqlString(string value)

Parameters

value string: The string to quote

Returns

string

Remarks

The string format is based on the database type set up for the particular solution.

SqlWeekday(string)

Returns a string in the appropriate database format.

public static string SqlWeekday(string dateTime)

Parameters

dateTime string

Returns

string

TableExists(string)

Helper method to determine whether a table with the given name exists in the given database.

public static bool TableExists(string tableName)

Parameters

tableName string: Name of the table.

Returns

bool

UpdateDateTimeValue(ref object, DateTime)

Updates the DateTime value if the difference between dates is more than or equal 2 milliseconds.

public static bool UpdateDateTimeValue(ref object dataRowField, DateTime updateValue)

Parameters

dataRowField object: Source value to update
updateValue DateTime: Update value

Returns

bool: true if update was made, otherwsie false

Remarks

The .NET has a DateTime resolution to 100th of a nanosecond while SQL Server datatime only has one to 100th of a millisecond.

Table of Contents

Class Database

Examples

Properties

ConnectionString

Property Value

LocalAddress

Property Value

Methods

AddBooleanParam(IDbCommand, string, bool)

Parameters

AddDateTimeParam(IDbCommand, string, DateTime)

Parameters

AddDoubleParam(IDbCommand, string, double)

Parameters

AddInt16Param(IDbCommand, string, short)

Parameters

AddInt32Param(IDbCommand, string, int)

Parameters

AddStringParam(IDbCommand, string, string?)

Parameters

ColumnExists(IDataReader, string)

Parameters

Returns

CopyRow(IDbCommand, IDictionary<string, object>)

Parameters

Remarks

CopyRow(string, Dictionary<string, Tuple<object, DbType>>, IDictionary<string, object>)

Parameters

Remarks

CreateAdapter()

Returns

Examples

Remarks

CreateAdapter(CommandBuilder)

Parameters

Returns

Examples

CreateAdapter(IDbCommand)

Parameters

Returns

Examples

Remarks

CreateCommandBuilder(ref IDbDataAdapter)

Parameters

Returns

Remarks

CreateConnection()

Returns

Examples

Remarks

CreateDataReader(CommandBuilder)

Parameters

Returns

Examples

Remarks

CreateDataReader(CommandBuilder, IDbConnection)

Parameters

Returns

Examples

Remarks

CreateDataReader(CommandBuilder, IDbConnection, IDbTransaction?)

Parameters

Returns

Examples

Remarks

CreateDataReader(CommandBuilder, IDbConnection, int)

Parameters

Returns

Examples

Remarks

CreateDataReader(CommandBuilder, int)

Parameters

Returns

Examples

Remarks

CreateDataReader(IDbCommand, CommandBehavior)

Parameters

Returns

Examples