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
-
Database
- Inherited Members
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
IDbCommandThe IDbCommand to add the parameter to
name
stringThe name of the System.Data.IDataParameter.
value
boolThe 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
IDbCommandThe IDbCommand to add the parameter to
name
stringThe name of the System.Data.IDataParameter.
value
DateTimeThe 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
IDbCommandThe IDbCommand to add the parameter to
name
stringThe name of the System.Data.IDataParameter.
value
doubleThe 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
IDbCommandThe IDbCommand to add the parameter to
name
stringThe name of the System.Data.IDataParameter.
value
shortThe 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
IDbCommandThe IDbCommand to add the parameter to
name
stringThe name of the System.Data.IDataParameter.
value
intThe 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
IDbCommandThe IDbCommand to add the parameter to
name
stringThe name of the System.Data.IDataParameter.
value
stringThe 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
IDataReaderThe datareader.
columnName
stringName of the column.
Returns
- bool
true
if the column exists; otherwisefalse
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
IDbCommandThe 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
stringThe 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
CommandBuilderThe CommandBuilder to create the IDbDataAdapter from.
Returns
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
IDbCommandThe IDbCommand to create the IDbDataAdapter from.
Returns
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
IDbDataAdapterAn 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
CommandBuilderThe 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
CommandBuilderThe CommandBuilder specifying the data to return in the DataReader
connection
IDbConnectionExisting 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
CommandBuilderThe CommandBuilder specifying the data to return in the DataReader
connection
IDbConnectionExisting database connection. The connection will remain open upon the completion.
transaction
IDbTransactionThe 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
CommandBuilderThe CommandBuilder specifying the data to return in the DataReader
connection
IDbConnectionExisting database connection. The connection will remain open upon the completion.
commandTimeout
intThe 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
CommandBuilderThe CommandBuilder specifying the data to return in the DataReader
commandTimeout
intThe 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
IDbCommandThe Command specifying the data to return in the DataReader
behavior
CommandBehaviorThe 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
stringThe 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
stringThe 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
stringThe SQL specifiyng the data to return in the DataReader
connection
IDbConnectionExisting 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
stringThe SQL specifiyng the data to return in the DataReader
connection
IDbConnectionExisting database connection.
transaction
IDbTransactionExisting 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
stringThe SQL specifiyng the data to return in the DataReader
connection
IDbConnectionExisting database connection.
commandTimeout
intThe 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
stringThe SQL specifiyng the data to return in the DataReader
commandTimeout
intThe 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
CommandBuilderThe CommandBuilder specifying the query to execute and data to return in the DataSet.
Returns
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
CommandBuilderThe CommandBuilder specifying the query to execute and data to return in the DataSet.
withSchema
boolSet to true if the returned dataset should contain schema information on table names, column names and types etc.
Returns
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, 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
CommandBuilderThe CommandBuilder specifying the query to execute and data to return in the DataSet.
connection
IDbConnectionExisting database connection. The connection will remain open upon the completion.
Returns
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, 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
CommandBuilderThe CommandBuilder specifying the query to execute and data to return in the DataSet.
connection
IDbConnectionExisting database connection. The connection will remain open upon the completion.
withSchema
boolSet to true if the returned dataset should contain schema information on table names, column names and types etc.
Returns
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, 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
CommandBuilderThe CommandBuilder specifying the query to execute and data to return in the DataSet.
connection
IDbConnectionExisting database connection. The connection will remain open upon the completion.
transaction
IDbTransactionThe transaction.
Returns
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, 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
CommandBuilderThe CommandBuilder specifying the query to execute and data to return in the DataSet.
connection
IDbConnectionExisting database connection. The connection will remain open upon the completion.
transaction
IDbTransactionThe transaction.
withSchema
boolSet to true if the returned dataset should contain schema information on table names, column names and types etc.
Returns
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(string)
Creates a DataSet with data returned by the passed SQL statement from the specfied database.
public static DataSet CreateDataSet(string sql)
Parameters
sql
stringThe SQL specifiyng the data to return in dataset
Returns
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
stringThe 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
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
stringThe SQL specifiyng the data to return in dataset
connection
IDbConnectionExisting database connection.
transaction
IDbTransactionThe transaction.
Returns
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
Returns
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
stringThe SQL specifiyng the data to return in DataTable
connection
IDbConnectionExisting database connection.
Returns
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
stringThe SQL specifiyng the data to return in DataTable
connection
IDbConnectionExisting database connection.
transaction
IDbTransactionExisting transaction on the given connection.
Returns
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
CommandBuilderThe CommandBuilder containing the SELECT-query.
connection
IDbConnectionThe 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
CommandBuilderThe CommandBuilder containing the SELECT-query.
connection
IDbConnectionThe IDbConnection to the relevant database.
tryFillIdentity
boolBoolean 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
CommandBuilderThe CommandBuilder containing the SELECT-query.
connection
IDbConnectionThe IDbConnection to the relevant database.
transaction
IDbTransactionThe 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
CommandBuilderThe CommandBuilder containing the SELECT-query.
connection
IDbConnectionThe IDbConnection to the relevant database.
transaction
IDbTransactionThe IDbTransaction to use when executing queries
tryFillIdentity
boolBoolean 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
IDbCommandThe 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
IDbCommandThe IDbCommand containing the SELECT-query.
tryFillIdentity
boolBoolean 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
stringThe select SQL.
connection
IDbConnectionThe IDbConnection to the relevant database.
Returns
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
stringThe select SQL.
connection
IDbConnectionThe IDbConnection to the relevant database.
tryFillIdentity
boolBoolean value indicating whether the Identity column should be filled automatically or not. Default is
false
.
Returns
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
stringThe select SQL.
connection
IDbConnectionThe IDbConnection to the relevant database.
transaction
IDbTransactionThe IDbTransaction to use when executing queries
Returns
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
stringThe select SQL.
connection
IDbConnectionThe IDbConnection to the relevant database.
transaction
IDbTransactionThe IDbTransaction to use when executing queries
tryFillIdentity
boolBoolean value indicating whether the Identity column should be filled automatically or not. Default is
false
.
Returns
EscapeForLikeStatement(string, bool)
Wrapes wildcard characters in to [] symbols.
public static string EscapeForLikeStatement(string value, bool escapeApostrophes)
Parameters
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
CommandBuilderThe 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
CommandBuilderThe CommandBuilder containing the query to execute.
useTransaction
boolIf 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
CommandBuilderThe CommandBuilder containing the query to execute.
useTransaction
boolIf set to
true
the query is executed in a transaction.commandTimeout
intThe 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
CommandBuilderThe CommandBuilder containing the query to execute.
connection
IDbConnectionExisting 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
CommandBuilderThe CommandBuilder containing the query to execute.
connection
IDbConnectionExisting database connection to be reused. The connection will not be closed upon completion.
useTransaction
boolIf 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
CommandBuilderThe CommandBuilder containing the query to execute.
connection
IDbConnectionExisting database connection to be reused. The connection will not be closed upon completion.
useTransaction
boolIf set to
true
the query is executed in a transaction.commandTimeout
intThe 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
CommandBuilderThe CommandBuilder containing the query to execute.
connection
IDbConnectionExisting database connection to be reused. The connection will not be closed upon completion.
transaction
IDbTransactionThe 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
CommandBuilderThe CommandBuilder containing the query to execute.
connection
IDbConnectionExisting database connection to be reused. The connection will not be closed upon completion.
transaction
IDbTransactionThe transaction associated with the specified
connection
.commandTimeout
intThe 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
CommandBuilderThe CommandBuilder containing the query to execute.
connection
IDbConnectionExisting database connection to be reused. The connection will not be closed upon completion.
commandTimeout
intThe 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
CommandBuilderThe CommandBuilder containing the query to execute.
commandTimeout
intThe 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
IDbCommandThe 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
stringThe 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
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
stringThe SQL to execute against the database
transaction
boolif set to
true
[transaction].connection
IDbConnectionExisting 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
stringThe 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
stringThe SQL to execute against the database
connection
IDbConnectionExisting 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
stringThe SQL to execute against the database
connection
IDbConnectionExisting database connection.
transaction
IDbTransactionThe 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
stringThe SQL to execute against the database
connection
IDbConnectionExisting database connection.
transaction
IDbTransactionThe transaction.
commandTimeout
intThe 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
stringThe SQL to execute against the database
commandTimeout
intThe 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
stringThe SQL to execute against the database
commandTimeout
intThe wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.
useTransaction
boolIf 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
stringThe SQL to execute against the database
commandTimeout
intThe wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.
useTransaction
boolIf set to
true
the query is executed in a transaction.connection
IDbConnectionExisting 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
stringThe SQL to execute against the database
commandTimeout
intThe wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.
connection
IDbConnectionExisting 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
CommandBuilderThe 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
CommandBuilderThe CommandBuilder containing the query to execute.
connection
IDbConnectionExisting 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
CommandBuilderThe CommandBuilder containing the query to execute.
connection
IDbConnectionExisting database connection. The connection will remain open upon the completion.
transaction
IDbTransactionThe 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
IDbCommandThe 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
stringThe 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
stringThe SQL to execute against the database
connection
IDbConnectionExisting 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
stringThe SQL to execute against the database
connection
IDbConnectionExisting database connection.
transaction
IDbTransactionThe 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
DataRowThe datarow to update.
connection
IDbConnectionThe connection used to update the datarow.
index
intIndex 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
IDbConnectionThe 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
IDbConnectionThe connection used for the update
transaction
IDbTransactionThe 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
GetParam(string, DbType, int, string)
Gets current parameter from database.
public static IDbDataParameter GetParam(string paramName, DbType paramType, int paramSize, string paramSourceColumn)
Parameters
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
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
DataTableData table specifying the data to insert.
Exceptions
- ArgumentNullException
data
is null.- ArgumentException
data
has a TableName property which is either null or empty string ordata
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
DataSetThe dataset to be sorted
dataSetPrimaryId
stringName of the column with the primary id in the dataset.
dataSetSort
stringName of the column, the dataset should be sorted by.
direction
stringThe direction the selected row should be moved ("up" or "down").
inputPrimaryId
intThe primary ID of the datarow that needs to be moved up or down.
Returns
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
booltrue
orfalse
.
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
intInteger 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
DateTimeDate 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
DateTimeDate as DateTime
skipTime
boolIndicates 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
stringThe 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
stringThe text being checked
escapeApostrophes
boolValue indicating whether to escape single apostrophes by replacing them with double apostrohpes.
Returns
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
stringThe string to quote
Returns
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
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
stringName of the table.
Returns
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
Returns
- bool
true
if update was made, otherwsiefalse
Remarks
The .NET has a DateTime resolution to 100th of a nanosecond while SQL Server datatime only has one to 100th of a millisecond.