Class SqlUtil

A Wrapper utity class for a SqlDataReader. Supports getting data from a column by specifying the columnname.

Inheritance
System.Object
SqlUtil
Implements
System.IDisposable
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: ImageVault.Common.Lib.Data
Assembly: ImageVault.Common.dll
Syntax
public class SqlUtil : IDisposable

Constructors

SqlUtil(IDataReader)

Creates the SqlUtil wrapper around the supplied _reader

Declaration
public SqlUtil(IDataReader reader)
Parameters
Type Name Description
System.Data.IDataReader reader

SqlUtil(IDbCommand)

Creates a cached SqlUtil, the connection is closed after creating the util and the data is contained within the SqlUtil class.

Declaration
public SqlUtil(IDbCommand cmd)
Parameters
Type Name Description
System.Data.IDbCommand cmd

SqlUtil(OleDbCommand, Boolean)

Creates a cached SqlUtil, the connection is closed after creating the util and the data is contained within the SqlUtil class.

Declaration
public SqlUtil(OleDbCommand cmd, bool cached)
Parameters
Type Name Description
System.Data.OleDb.OleDbCommand cmd
System.Boolean cached

SqlUtil(SqlCommand, Boolean)

Creates a cached SqlUtil, the connection is closed after creating the util and the data is contained within the SqlUtil class.

Declaration
public SqlUtil(SqlCommand cmd, bool cached)
Parameters
Type Name Description
System.Data.SqlClient.SqlCommand cmd
System.Boolean cached

SqlUtil(String, OleDbConnection, Boolean)

Creates the SqlUtil wrapper around the supplied query and connection

Declaration
public SqlUtil(string query, OleDbConnection con, bool cached)
Parameters
Type Name Description
System.String query
System.Data.OleDb.OleDbConnection con
System.Boolean cached

SqlUtil(String, SqlConnection, Boolean)

Creates the SqlUtil Wrapper around the supplied query and connection.

Declaration
public SqlUtil(string query, SqlConnection con, bool cached)
Parameters
Type Name Description
System.String query
System.Data.SqlClient.SqlConnection con
System.Boolean cached

Properties

Count

Returns the number of rows in a cached SqlUtil. If the Util isn't cached -1 is returned

Declaration
public int Count { get; }
Property Value
Type Description
System.Int32

ProgressReportingAction

Action for reporting progress

Declaration
public static Action<int, int> ProgressReportingAction { get; set; }
Property Value
Type Description
System.Action<System.Int32, System.Int32>

Methods

Close()

Closes the _reader

Declaration
public void Close()

Dispose()

Releases the resources used by the SqlUtil.

Declaration
public void Dispose()

Dispose(Boolean)

Releases the resources used by the SqlUtil.

Declaration
protected virtual void Dispose(bool disposing)
Parameters
Type Name Description
System.Boolean disposing

If true we are called from the Dispose method, if false we are called from the destructor

ExecuteNonQuery(SqlCommand)

Executes a command and returns the number of rows affected.

Declaration
public static int ExecuteNonQuery(SqlCommand cmd)
Parameters
Type Name Description
System.Data.SqlClient.SqlCommand cmd
Returns
Type Description
System.Int32

ExecuteQuery(String, String)

Returns a cached SqlUtil from based on the query

Declaration
public static SqlUtil ExecuteQuery(string query, string connectionString)
Parameters
Type Name Description
System.String query
System.String connectionString
Returns
Type Description
SqlUtil

Finalize()

Releases unmanaged resources and performs other cleanup operations before the SqlUtil is reclaimed by garbage collection.

Declaration
protected void Finalize()

GenerateDeclareParameterStatement(SqlParameter)

Generates an SQL statement for declaring a supplied parameter

Declaration
public static string GenerateDeclareParameterStatement(SqlParameter p)
Parameters
Type Name Description
System.Data.SqlClient.SqlParameter p
Returns
Type Description
System.String

GenerateSetParameterStatement(SqlParameter)

Generates an SQL statement for setting the supplied Sql parameter to it's value

Declaration
public static string GenerateSetParameterStatement(SqlParameter p)
Parameters
Type Name Description
System.Data.SqlClient.SqlParameter p
Returns
Type Description
System.String

GetBit(Int32)

Gets a bit value from the specified column

Declaration
public bool GetBit(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Boolean

GetBit(Int32, Boolean)

Declaration
public bool GetBit(int i, bool valueIfNull)
Parameters
Type Name Description
System.Int32 i
System.Boolean valueIfNull
Returns
Type Description
System.Boolean

GetBit(String)

Gets a bit value from the specified column

Declaration
public bool GetBit(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Boolean

GetBit(String, Boolean)

Gets a bit (bool) from the specified column

Declaration
public bool GetBit(string columnName, bool valueIfNull)
Parameters
Type Name Description
System.String columnName

Name of the column
System.Boolean valueIfNull

Value to return if column-value is null
Returns
Type Description
System.Boolean

GetByte(Int32)

Gets a byte value from the specified column

Declaration
public byte GetByte(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Byte

GetByte(Int32, Byte)

Gets a byte value from the specified column

Declaration
public byte GetByte(int i, byte valueIfNull)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
System.Byte valueIfNull
Returns
Type Description
System.Byte

GetByte(String)

Gets a byte value from the specified column

Declaration
public byte GetByte(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Byte

GetByte(String, Byte)

Gets a byte value from the specified column

Declaration
public byte GetByte(string columnName, byte valueIfNull)
Parameters
Type Name Description
System.String columnName
System.Byte valueIfNull

If the value is null, this value will be returned instead
Returns
Type Description
System.Byte

GetDateTime(Int32)

Gets a DateTime value from the specified column

Declaration
public DateTime GetDateTime(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.DateTime

GetDateTime(Int32, DateTime)

Declaration
public DateTime GetDateTime(int i, DateTime valueIfNull)
Parameters
Type Name Description
System.Int32 i
System.DateTime valueIfNull
Returns
Type Description
System.DateTime

GetDateTime(String)

Gets a DateTime value from the specified column

Declaration
public DateTime GetDateTime(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.DateTime

GetDateTime(String, DateTime)

Declaration
public DateTime GetDateTime(string columnName, DateTime valueIfNull)
Parameters
Type Name Description
System.String columnName
System.DateTime valueIfNull
Returns
Type Description
System.DateTime

GetDecimal(Int32)

Gets a decimal from the specified column

Declaration
public decimal GetDecimal(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Decimal

GetDecimal(String)

Gets a decimal from the specified column

Declaration
public decimal GetDecimal(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Decimal

GetDouble(Int32)

Gets a double from the specified column.

Declaration
public double GetDouble(int i)
Parameters
Type Name Description
System.Int32 i

The column
Returns
Type Description
System.Double

The value from the specified column. If the value is null, an exception is thrown

GetDouble(Int32, Double)

Gets a double from the specified column.

Declaration
public double GetDouble(int i, double valueIfNull)
Parameters
Type Name Description
System.Int32 i

The column
System.Double valueIfNull

The value to return if the column contains null
Returns
Type Description
System.Double

The value from the specified column if it is not null, valueIfNull otherwise

GetDouble(String)

Gets a double from the column with the specified name

Declaration
public double GetDouble(string columnName)
Parameters
Type Name Description
System.String columnName

The name of the column
Returns
Type Description
System.Double

The value from the specified column

GetDouble(String, Double)

Gets a double from the column with the specified name

Declaration
public double GetDouble(string columnName, double valueIfNull)
Parameters
Type Name Description
System.String columnName

The name of the column
System.Double valueIfNull

The value to return if the specified column contains null
Returns
Type Description
System.Double

The value from the specified column if it is not null, valueIfNull otherwise

GetInstallationScripts(IFolder, String)

Gets installation scripts needed for the system

Declaration
public static List<ScriptPart> GetInstallationScripts(IFolder baseFolder, string filepattern)
Parameters
Type Name Description
IFolder baseFolder
System.String filepattern
Returns
Type Description
System.Collections.Generic.List<ScriptPart>

GetInt(Int32)

Gets an integer value from the specified column

Declaration
public int GetInt(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Int32

GetInt(Int32, Int32)

Gets an integer value from the specified column

Declaration
public int GetInt(int i, int valueIfNull)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
System.Int32 valueIfNull

if the value is null, return this value instead
Returns
Type Description
System.Int32

GetInt(String)

Gets an integer value from the specified column

Declaration
public int GetInt(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Int32

GetInt(String, Int32)

Gets an integer value from the specified column

Declaration
public int GetInt(string columnName, int valueIfNull)
Parameters
Type Name Description
System.String columnName
System.Int32 valueIfNull

if the value is null, return this value instead
Returns
Type Description
System.Int32

GetInt16(Int32)

Gets an integer value from the specified column

Declaration
public short GetInt16(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Int16

GetInt16(Int32, Int16)

Gets an integer value from the specified column

Declaration
public short GetInt16(int i, short valueIfNull)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
System.Int16 valueIfNull

if the value is null, return this value instead
Returns
Type Description
System.Int16

GetInt16(String)

Gets an integer value from the specified column

Declaration
public short GetInt16(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Int16

GetInt16(String, Int16)

Gets an integer value from the specified column

Declaration
public short GetInt16(string columnName, short valueIfNull)
Parameters
Type Name Description
System.String columnName

The name of the column
System.Int16 valueIfNull

if the value is null, return this value instead
Returns
Type Description
System.Int16

GetInt32(Int32)

Gets an integer value from the specified column

Declaration
public int GetInt32(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Int32

GetInt32(Int32, Int32)

Gets an integer value from the specified column

Declaration
public int GetInt32(int i, int valueIfNull)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
System.Int32 valueIfNull

if the value is null, return this value instead
Returns
Type Description
System.Int32

GetInt32(String)

Gets an integer value from the specified column

Declaration
public int GetInt32(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Int32

GetInt32(String, Int32)

Gets an integer value from the specified column

Declaration
public int GetInt32(string columnName, int valueIfNull)
Parameters
Type Name Description
System.String columnName

The name of the column
System.Int32 valueIfNull

if the value is null, return this value instead
Returns
Type Description
System.Int32

GetInt64(Int32)

Gets an Int64 (long) value from the specified column

Declaration
public long GetInt64(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Int64

GetInt64(String)

Gets an Int64 (long) value from the specified column

Declaration
public long GetInt64(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Int64

GetLong(Int32)

Gets a Long value from the specified column

Declaration
public long GetLong(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Int64

GetLong(Int32, Int64)

Gets a Long value from the specified column

Declaration
public long GetLong(int i, long valueIfNull)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
System.Int64 valueIfNull
Returns
Type Description
System.Int64

GetLong(String)

Gets a Long value from the specified column

Declaration
public long GetLong(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Int64

GetLong(String, Int64)

Gets an integer value from the specified column

Declaration
public long GetLong(string columnName, long valueIfNull)
Parameters
Type Name Description
System.String columnName
System.Int64 valueIfNull
Returns
Type Description
System.Int64

GetNullableDateTime(Int32)

Gets a NullableDateTime value from the specified column

Declaration
public DateTime? GetNullableDateTime(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Nullable<System.DateTime>

GetNullableDateTime(String)

Gets a NullableDateTime value from the specified column

Declaration
public DateTime? GetNullableDateTime(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Nullable<System.DateTime>

GetOrdinal(String)

Gets the index of the column with the specified name

Declaration
public int GetOrdinal(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Int32

GetScalarIntValue(SqlCommand)

Returns the first value returned (first recordset, first field) as an Int32.

Declaration
public static int GetScalarIntValue(SqlCommand cmd)
Parameters
Type Name Description
System.Data.SqlClient.SqlCommand cmd

The System.Data.SqlClient.SqlCommand to execute
Returns
Type Description
System.Int32

The first column of the first row in the result set, converted to an System.Int32.

GetScalarIntValue(String, SqlConnection)

Returns the first value returned (first recordset, first field) as an Int32.

Declaration
public static int GetScalarIntValue(string sql, SqlConnection con)
Parameters
Type Name Description
System.String sql

The sql query to execute
System.Data.SqlClient.SqlConnection con

The System.Data.SqlClient.SqlConnection to execute the query on.
Returns
Type Description
System.Int32

The first column of the first row in the result set, converted to an System.Int32.

GetScalarStringValue(SqlCommand)

Returns the first value returned (first recordset, first field) as an string

Declaration
public static string GetScalarStringValue(SqlCommand cmd)
Parameters
Type Name Description
System.Data.SqlClient.SqlCommand cmd
Returns
Type Description
System.String

GetShort(Int32)

Gets an integer value from the specified column

Declaration
public short GetShort(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Int16

GetShort(Int32, Int16)

Gets an integer value from the specified column

Declaration
public short GetShort(int i, short valueIfNull)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
System.Int16 valueIfNull

if the value is null, return this value instead
Returns
Type Description
System.Int16

GetShort(String)

Gets an integer value from the specified column

Declaration
public short GetShort(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Int16

GetShort(String, Int16)

Gets an integer value from the specified column

Declaration
public short GetShort(string columnName, short valueIfNull)
Parameters
Type Name Description
System.String columnName
System.Int16 valueIfNull

if the value is null, return this value instead
Returns
Type Description
System.Int16

GetString(Int32)

Gets a string value from the specified column. If the column is null an exception is thrown.

Declaration
public string GetString(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.String

GetString(Int32, String)

Gets a string value from the specified column

Declaration
public string GetString(int i, string valueIfNull)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
System.String valueIfNull
Returns
Type Description
System.String

GetString(String)

Gets a string value from the specified column

Declaration
public string GetString(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.String

GetTinyInt(Int32)

Gets a byte value from the specified column

Declaration
public byte GetTinyInt(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Byte

GetTinyInt(Int32, Byte)

Gets a byte value from the specified column

Declaration
public byte GetTinyInt(int i, byte valueIfNull)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
System.Byte valueIfNull

If the value is null, this value will be returned instead
Returns
Type Description
System.Byte

GetTinyInt(String)

Gets a byte value from the specified column

Declaration
public byte GetTinyInt(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Byte

GetTinyInt(String, Byte)

Gets a byte value from the specified column

Declaration
public byte GetTinyInt(string columnName, byte valueIfNull)
Parameters
Type Name Description
System.String columnName

The name of the column
System.Byte valueIfNull

If the value is null, this value will be returned instead
Returns
Type Description
System.Byte

IsDBNull(Int32)

Checks if ths value in the column is null

Declaration
public bool IsDBNull(int i)
Parameters
Type Name Description
System.Int32 i

The zero-based column ordinal.
Returns
Type Description
System.Boolean

IsDBNull(String)

Checks if ths value in the column is null

Declaration
public bool IsDBNull(string columnName)
Parameters
Type Name Description
System.String columnName
Returns
Type Description
System.Boolean

NextResult()

Advances the datareader to the next result

Declaration
public bool NextResult()
Returns
Type Description
System.Boolean

True if there was a next result, false otherwize.

Read()

Reads next post from the _reader

Declaration
public bool Read()
Returns
Type Description
System.Boolean

Reset()

Resets a cached SqlUtil to point at the beginning of the result

Declaration
public void Reset()
Remarks

Has no affect on not cached SqlUtils

RunInstallationScript(IDbConnection, IFolder, String, Int32, IScriptLog)

Runs the installation scripts found in the supplied base folder. The method will search for a subfolder called procedures and one called tables and execute all scripts in these folders. Scripts can be devided into 3 parts Pre, main and post installation.

The execution order is as follows. pre general pre procedure pre table main table main general main procedure post procedure post table post general

Declaration
public static bool RunInstallationScript(IDbConnection con, IFolder baseFolder, string filepattern = "*.sql", int commandTimeout = -1, IScriptLog log = null)
Parameters
Type Name Description
System.Data.IDbConnection con
IFolder baseFolder
System.String filepattern
System.Int32 commandTimeout

The time (in seconds) to wait for the command to execute. The default value is 30 seconds.
IScriptLog log
Returns
Type Description
System.Boolean

True if installation was run successfully.

SqlEncode(String)

Encodes a string and replaces all single quotes (') with two single quotes('')

Declaration
public static string SqlEncode(string text)
Parameters
Type Name Description
System.String text

The string to encode
Returns
Type Description
System.String

The encoded result.

SqlEncodeDateTime(DateTime, Boolean)

Returns a Sql server suitable value representation for a datetime. Requires that the SqlServer supports the Convert function and supports date format 112 (ISO) and 121 (ODBC) (includeTime)

Declaration
public static string SqlEncodeDateTime(DateTime date, bool includeTime)
Parameters
Type Name Description
System.DateTime date

The date value to convert
System.Boolean includeTime

If the time should be parsed, if false only the day part is converted.
Returns
Type Description
System.String

SqlQuote(String)

Quotes and encodes a literal string in sql

Declaration
public static string SqlQuote(string text)
Parameters
Type Name Description
System.String text

The text to quote
Returns
Type Description
System.String

The quoted System.String

TableExists(SqlConnection, String, String)

Checks if a table exists

Declaration
public static bool TableExists(SqlConnection con, string schema, string table)
Parameters
Type Name Description
System.Data.SqlClient.SqlConnection con
System.String schema
System.String table
Returns
Type Description
System.Boolean
Exceptions
Type Condition
System.ApplicationException

Implements

System.IDisposable

Extension Methods