AI OnAI Off

Class SqlHelper

The SqlHelper class is intended to encapsulate high performance, scalable best practices for common uses of SqlClient.

Inheritance
System.Object
SqlHelper
Inherited Members
System.Object.ToString()
System.Object.Equals(System.Object)
System.Object.Equals(System.Object, System.Object)
System.Object.ReferenceEquals(System.Object, System.Object)
System.Object.GetHashCode()
System.Object.GetType()
System.Object.MemberwiseClone()
Namespace: Mediachase.FileUploader
Assembly: Mediachase.FileUploader.dll
Version: 10.8.0
Syntax
public sealed class SqlHelper

Methods

DBNull2Null(Object)

Converts DNNull.Value into a null reference (Nothing in Visual Basic).

Declaration
public static object DBNull2Null(object Value)
Parameters
Type Name Description
System.Object Value

Value from DB
Returns
Type Description
System.Object

Value or null (Nothing in Visual Basic).

DBNull2Null(Object, Object)

Converts DNNull.Value into a null reference (Nothing in Visual Basic) or assign default value.

Declaration
public static object DBNull2Null(object Value, object DefaultValue)
Parameters
Type Name Description
System.Object Value

Value from DB
System.Object DefaultValue

Default value
Returns
Type Description
System.Object

Value or null (Nothing in Visual Basic).

Deserialize(Byte[])

This method deserializes object from binary storage.

Declaration
public static object Deserialize(byte[] Data)
Parameters
Type Name Description
System.Byte[] Data
Returns
Type Description
System.Object

ExecuteDataset(SqlContext, CommandType, String)

Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in the connection string.

Declaration
public static DataSet ExecuteDataset(SqlContext context, CommandType commandType, string commandText)
Parameters
Type Name Description
SqlContext context

the current context.
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Data.DataSet

a dataset containing the resultset generated by the command

ExecuteDataset(SqlContext, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in the connection string.

Declaration
public static DataSet ExecuteDataset(SqlContext context, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
SqlContext context

the current context.
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters
Returns
Type Description
System.Data.DataSet

a dataset containing the resultset generated by the command

ExecuteDataset(SqlConnection, CommandType, String)

Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.

Declaration
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Data.DataSet

a dataset containing the resultset generated by the command
Remarks

e.g.:
DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");

ExecuteDataset(SqlConnection, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a resultset) against the specified SqlConnection using the provided parameters.

Declaration
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Data.DataSet

a dataset containing the resultset generated by the command
Remarks

e.g.:
DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));

ExecuteDataset(SqlConnection, String, Object[])

Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Data.DataSet

a dataset containing the resultset generated by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);

ExecuteDataset(SqlTransaction, CommandType, String)

Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.

Declaration
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Data.DataSet

a dataset containing the resultset generated by the command
Remarks

e.g.:
DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");

ExecuteDataset(SqlTransaction, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction using the provided parameters.

Declaration
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Data.DataSet

a dataset containing the resultset generated by the command
Remarks

e.g.:
DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));

ExecuteDataset(SqlTransaction, String, Object[])

Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Data.DataSet

a dataset containing the resultset generated by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);

ExecuteDataset(String, CommandType, String)

Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in the connection string.

Declaration
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Data.DataSet

a dataset containing the resultset generated by the command
Remarks

e.g.:
DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");

ExecuteDataset(String, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a resultset) against the database specified in the connection string using the provided parameters.

Declaration
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Data.DataSet

a dataset containing the resultset generated by the command
Remarks

e.g.:
DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));

ExecuteDataset(String, String, Object[])

Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in the connection string using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Data.DataSet

a dataset containing the resultset generated by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);

ExecuteNonQuery(SqlContext, CommandType, String)

Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in the connection string.

Declaration
public static int ExecuteNonQuery(SqlContext context, CommandType commandType, string commandText)
Parameters
Type Name Description
SqlContext context

The current context.
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Int32

an int representing the number of rows affected by the command

ExecuteNonQuery(SqlContext, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in the connection string.

Declaration
public static int ExecuteNonQuery(SqlContext context, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
SqlContext context

The current context.
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Int32

an int representing the number of rows affected by the command

ExecuteNonQuery(SqlConnection, CommandType, String)

Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.

Declaration
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Int32

an int representing the number of rows affected by the command
Remarks

e.g.:
int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");

ExecuteNonQuery(SqlConnection, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns no resultset) against the specified SqlConnection using the provided parameters.

Declaration
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Int32

an int representing the number of rows affected by the command
Remarks

e.g.:
int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));

ExecuteNonQuery(SqlConnection, String, Object[])

Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Int32

an int representing the number of rows affected by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);

ExecuteNonQuery(SqlTransaction, CommandType, String)

Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.

Declaration
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Int32

an int representing the number of rows affected by the command
Remarks

e.g.:
int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");

ExecuteNonQuery(SqlTransaction, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction using the provided parameters.

Declaration
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Int32

an int representing the number of rows affected by the command
Remarks

e.g.:
int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));

ExecuteNonQuery(SqlTransaction, String, Object[])

Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Int32

an int representing the number of rows affected by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);

ExecuteNonQuery(String, CommandType, String)

Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in the connection string.

Declaration
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Int32

an int representing the number of rows affected by the command
Remarks

e.g.:
int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");

ExecuteNonQuery(String, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns no resultset) against the database specified in the connection string using the provided parameters.

Declaration
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Int32

an int representing the number of rows affected by the command
Remarks

e.g.:
int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));

ExecuteNonQuery(String, String, Object[])

Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in the connection string using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.String spName

the name of the stored prcedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Int32

an int representing the number of rows affected by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);

ExecuteReader(SqlContext, CommandType, String)

Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.

Declaration
public static SqlDataReader ExecuteReader(SqlContext context, CommandType commandType, string commandText)
Parameters
Type Name Description
SqlContext context

the current context.
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Data.SqlClient.SqlDataReader

SqlDataReader containing the results of the command

ExecuteReader(SqlContext, CommandType, String, SqlParameter[])

Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.

Declaration
public static SqlDataReader ExecuteReader(SqlContext context, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
SqlContext context

the current context.
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParameters to be associated with the command or 'null' if no parameters are required
Returns
Type Description
System.Data.SqlClient.SqlDataReader

SqlDataReader containing the results of the command

ExecuteReader(SqlConnection, CommandType, String)

Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.

Declaration
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Data.SqlClient.SqlDataReader

a SqlDataReader containing the resultset generated by the command
Remarks

e.g.:
SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");

ExecuteReader(SqlConnection, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a resultset) against the specified SqlConnection using the provided parameters.

Declaration
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Data.SqlClient.SqlDataReader

a SqlDataReader containing the resultset generated by the command
Remarks

e.g.:
SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));

ExecuteReader(SqlConnection, String, Object[])

Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Data.SqlClient.SqlDataReader

a SqlDataReader containing the resultset generated by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);

ExecuteReader(SqlTransaction, CommandType, String)

Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.

Declaration
public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Data.SqlClient.SqlDataReader

a SqlDataReader containing the resultset generated by the command
Remarks

e.g.:
SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");

ExecuteReader(SqlTransaction, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction using the provided parameters.

Declaration
public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Data.SqlClient.SqlDataReader

a SqlDataReader containing the resultset generated by the command
Remarks

e.g.:
SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));

ExecuteReader(SqlTransaction, String, Object[])

Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Data.SqlClient.SqlDataReader

a SqlDataReader containing the resultset generated by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);

ExecuteReader(String, CommandType, String)

Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in the connection string.

Declaration
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Data.SqlClient.SqlDataReader

a SqlDataReader containing the resultset generated by the command
Remarks

e.g.:
SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");

ExecuteReader(String, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a resultset) against the database specified in the connection string using the provided parameters.

Declaration
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Data.SqlClient.SqlDataReader

a SqlDataReader containing the resultset generated by the command
Remarks

e.g.:
SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));

ExecuteReader(String, String, Object[])

Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in the connection string using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Data.SqlClient.SqlDataReader

a SqlDataReader containing the resultset generated by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);

ExecuteScalar(SqlContext, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in the connection string.

Declaration
public static object ExecuteScalar(SqlContext context, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
SqlContext context

the current context/
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Object

an object containing the value in the 1x1 resultset generated by the command

ExecuteScalar(SqlConnection, CommandType, String)

Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.

Declaration
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Object

an object containing the value in the 1x1 resultset generated by the command
Remarks

e.g.:
int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");

ExecuteScalar(SqlConnection, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection using the provided parameters.

Declaration
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Object

an object containing the value in the 1x1 resultset generated by the command
Remarks

e.g.:
int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));

ExecuteScalar(SqlConnection, String, Object[])

Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Object

an object containing the value in the 1x1 resultset generated by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);

ExecuteScalar(SqlTransaction, CommandType, String)

Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.

Declaration
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Object

an object containing the value in the 1x1 resultset generated by the command
Remarks

e.g.:
int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");

ExecuteScalar(SqlTransaction, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction using the provided parameters.

Declaration
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Object

an object containing the value in the 1x1 resultset generated by the command
Remarks

e.g.:
int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));

ExecuteScalar(SqlTransaction, String, Object[])

Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Object

an object containing the value in the 1x1 resultset generated by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);

ExecuteScalar(String, CommandType, String)

Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in the connection string.

Declaration
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
Returns
Type Description
System.Object

an object containing the value in the 1x1 resultset generated by the command
Remarks

e.g.:
int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");

ExecuteScalar(String, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string using the provided parameters.

Declaration
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Object

an object containing the value in the 1x1 resultset generated by the command
Remarks

e.g.:
int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));

ExecuteScalar(String, String, Object[])

Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.String connectionString

a valid connection string for a SqlConnection
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Object

an object containing the value in the 1x1 resultset generated by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);

ExecuteScript(SqlContext, String)

Executes a complex sql script.

Declaration
public static void ExecuteScript(SqlContext context, string Script)
Parameters
Type Name Description
SqlContext context

The current context.
System.String Script

A sql script.

ExecuteXmlReader(SqlConnection, CommandType, String)

Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.

Declaration
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command using "FOR XML AUTO"
Returns
Type Description
System.Xml.XmlReader

an XmlReader containing the resultset generated by the command
Remarks

e.g.:
XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");

ExecuteXmlReader(SqlConnection, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a resultset) against the specified SqlConnection using the provided parameters.

Declaration
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command using "FOR XML AUTO"
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Xml.XmlReader

an XmlReader containing the resultset generated by the command
Remarks

e.g.:
XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));

ExecuteXmlReader(SqlConnection, String, Object[])

Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection connection

a valid SqlConnection
System.String spName

the name of the stored procedure using "FOR XML AUTO"
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Xml.XmlReader

an XmlReader containing the resultset generated by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);

ExecuteXmlReader(SqlTransaction, CommandType, String)

Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.

Declaration
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command using "FOR XML AUTO"
Returns
Type Description
System.Xml.XmlReader

an XmlReader containing the resultset generated by the command
Remarks

e.g.:
XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");

ExecuteXmlReader(SqlTransaction, CommandType, String, SqlParameter[])

Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction using the provided parameters.

Declaration
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.Data.CommandType commandType

the CommandType (stored procedure, text, etc.)
System.String commandText

the stored procedure name or T-SQL command using "FOR XML AUTO"
System.Data.SqlClient.SqlParameter[] commandParameters

an array of SqlParamters used to execute the command
Returns
Type Description
System.Xml.XmlReader

an XmlReader containing the resultset generated by the command
Remarks

e.g.:
XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));

ExecuteXmlReader(SqlTransaction, String, Object[])

Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

Declaration
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
Parameters
Type Name Description
System.Data.SqlClient.SqlTransaction transaction

a valid SqlTransaction
System.String spName

the name of the stored procedure
System.Object[] parameterValues

an array of objects to be assigned as the input values of the stored procedure
Returns
Type Description
System.Xml.XmlReader

a dataset containing the resultset generated by the command
Remarks

This method provides no access to output parameters or the stored procedure's return value parameter.

e.g.:
XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);

Null2DBNull(Object)

Convert a null reference (Nothing in Visual Basic) into DBNull.Value.

Declaration
public static object Null2DBNull(object Value)
Parameters
Type Name Description
System.Object Value
Returns
Type Description
System.Object

Serialize(Object)

This method serializes object into binary storage.

Declaration
public static byte[] Serialize(object Value)
Parameters
Type Name Description
System.Object Value
Returns
Type Description
System.Byte[]