/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataTable">The data table to return containing the data.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="getSchemaTable">Get the table schema from the database and then load the data. Used when
/// returning data from the database for a particular table.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public OdbcCommand ExecuteClientQuery(ref DataTable dataTable, string queryText, CommandType commandType,
string connectionString, bool getSchemaTable, ConnectionContext.ConnectionDataType connectionDataType,
params OdbcParameter[] values)
{
// Initial connection objects.
OdbcCommand sqlCommand = null;
OdbcConnection connection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (connection = new OdbcConnection(connectionString))
{
// Open the connection.
connection.Open();
// Create the command and assign any parameters.
sqlCommand = new OdbcCommand(queryText, connection);
sqlCommand.CommandType = commandType;
if (values != null)
{
foreach (OdbcParameter sqlParameter in values)
{
sqlCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = sqlCommand.ExecuteReader())
{
// Get the schema from the data because the
// table has not predefined schema
if (getSchemaTable)
{
// Load the table after the schema is
// returned.
dataTable = dataReader.GetSchemaTable();
dataTable = new DataTable("TableData");
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
}
else
{
// Load the data into a table schema.
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
}
dataReader.Close();
}
// Close the database connection.
connection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(sqlCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// Get the data type of a .NET Framework data provider.
/// </summary>
/// <param name="sqlDataType">The sql data type.</param>
/// <param name="type">The database connection type.</param>
/// <returns>The data type of a field, a property, or a Parameter object of
/// a .NET Framework data provider.</returns>
public static System.Data.DbType GetDbType(string sqlDataType,
ConnectionContext.ConnectionDataType type)
{
switch (sqlDataType.ToLower())
{
case "image":
return(System.Data.DbType.Binary);
case "text":
return(System.Data.DbType.String);
case "tinyint":
return(System.Data.DbType.Byte);
case "smallint":
return(System.Data.DbType.Int16);
case "int":
return(System.Data.DbType.Int32);
case "smalldatetime":
return(System.Data.DbType.DateTime);
case "real":
return(System.Data.DbType.Double);
case "binarydouble":
return(System.Data.DbType.Binary);
case "binary double":
return(System.Data.DbType.Binary);
case "binary_double":
return(System.Data.DbType.Binary);
case "binaryfloat":
return(System.Data.DbType.Binary);
case "binary float":
return(System.Data.DbType.Binary);
case "binary_float":
return(System.Data.DbType.Binary);
case "money":
return(System.Data.DbType.Currency);
case "datetime":
return(System.Data.DbType.DateTime);
case "date":
return(System.Data.DbType.Date);
case "float":
return(System.Data.DbType.Single);
case "single":
return(System.Data.DbType.Single);
case "ntext":
return(System.Data.DbType.String);
case "bit":
return(System.Data.DbType.Boolean);
case "decimal":
return(System.Data.DbType.Decimal);
case "dec":
return(System.Data.DbType.Decimal);
case "smallmoney":
return(System.Data.DbType.Currency);
case "bigint":
return(System.Data.DbType.Int64);
case "varbinary":
return(System.Data.DbType.Binary);
case "varchar":
return(System.Data.DbType.String);
case "varchar1":
return(System.Data.DbType.String);
case "varchar2":
return(System.Data.DbType.String);
case "binary":
return(System.Data.DbType.Binary);
case "char":
return(System.Data.DbType.String);
case "nvarchar":
return(System.Data.DbType.String);
case "nvarchar1":
return(System.Data.DbType.String);
case "nvarchar2":
return(System.Data.DbType.String);
case "nchar":
return(System.Data.DbType.String);
case "time":
return(System.Data.DbType.Time);
case "uniqueidentifier":
return(System.Data.DbType.Guid);
case "rowversion":
return(System.Data.DbType.Binary);
case "xml":
return(System.Data.DbType.Xml);
case "numeric":
return(System.Data.DbType.Decimal);
case "sql_variant":
return(System.Data.DbType.Object);
case "bfile":
return(System.Data.DbType.Binary);
case "blob":
return(System.Data.DbType.Binary);
case "byte":
return(System.Data.DbType.Byte);
case "clob":
return(System.Data.DbType.String);
case "cursor":
return(System.Data.DbType.Object);
case "ref cursor":
return(System.Data.DbType.Object);
case "refcursor":
return(System.Data.DbType.Object);
case "double":
return(System.Data.DbType.Double);
case "int16":
return(System.Data.DbType.Int16);
case "int32":
return(System.Data.DbType.Int32);
case "int64":
return(System.Data.DbType.Int64);
case "interval day to second":
return(System.Data.DbType.Object);
case "interval year to month":
return(System.Data.DbType.Int32);
case "intervalds":
return(System.Data.DbType.Object);
case "intervalym":
return(System.Data.DbType.Int32);
case "long raw":
return(System.Data.DbType.Binary);
case "longraw":
return(System.Data.DbType.Binary);
case "long":
return(System.Data.DbType.String);
case "long varchar":
return(System.Data.DbType.String);
case "longvarchar":
return(System.Data.DbType.String);
case "nclob":
return(System.Data.DbType.String);
case "number":
return(System.Data.DbType.Decimal);
case "integer":
return(System.Data.DbType.Int32);
case "raw":
return(System.Data.DbType.Binary);
case "rowid":
return(System.Data.DbType.String);
case "urowid":
return(System.Data.DbType.String);
case "sbyte":
return(System.Data.DbType.SByte);
case "timestamp with local time zone":
return(System.Data.DbType.DateTime);
case "timestamp with time zone":
return(System.Data.DbType.DateTime);
case "timestampltz":
return(System.Data.DbType.DateTime);
case "timestamptz":
return(System.Data.DbType.DateTime);
case "uint16":
return(System.Data.DbType.UInt16);
case "uint32":
return(System.Data.DbType.UInt32);
case "boolean":
return(System.Data.DbType.Boolean);
case "bstr":
return(System.Data.DbType.String);
case "currency":
return(System.Data.DbType.Currency);
case "dbdate":
return(System.Data.DbType.Date);
case "dbtime":
return(System.Data.DbType.Time);
case "dbtimestamp":
return(System.Data.DbType.DateTime);
case "filetime":
return(System.Data.DbType.DateTime);
case "guid":
return(System.Data.DbType.Guid);
case "idispatch":
return(System.Data.DbType.Object);
case "iunknown":
return(System.Data.DbType.Object);
case "longvarbinary":
return(System.Data.DbType.Binary);
case "longvarwchar":
return(System.Data.DbType.String);
case "propvariant":
return(System.Data.DbType.Object);
case "unsignedbigint":
return(System.Data.DbType.UInt64);
case "unsignedint":
return(System.Data.DbType.UInt32);
case "unsignedsmallint":
return(System.Data.DbType.UInt16);
case "unsignedtinyint":
return(System.Data.DbType.Byte);
case "variant":
return(System.Data.DbType.Object);
case "varnumeric":
return(System.Data.DbType.Decimal);
case "varwchar":
return(System.Data.DbType.String);
case "wchar":
return(System.Data.DbType.String);
case "xmltype":
return(System.Data.DbType.Xml);
case "box":
return(System.Data.DbType.Object);
case "circle":
return(System.Data.DbType.Object);
case "line":
return(System.Data.DbType.Object);
case "lseg":
return(System.Data.DbType.Object);
case "oidvector":
return(System.Data.DbType.Object);
case "path":
return(System.Data.DbType.Object);
case "point":
return(System.Data.DbType.Object);
case "polygon":
return(System.Data.DbType.Object);
case "bytea":
return(System.Data.DbType.Binary);
case "interval":
return(System.Data.DbType.DateTime);
case "array":
return(System.Data.DbType.Binary);
case "inet":
return(System.Data.DbType.String);
case "name":
return(System.Data.DbType.String);
case "uuid":
return(System.Data.DbType.Guid);
case "timetz":
return(System.Data.DbType.DateTime);
case "int8":
return(System.Data.DbType.Int64);
case "float8":
return(System.Data.DbType.Double);
case "int4":
return(System.Data.DbType.Int32);
case "float4":
return(System.Data.DbType.Single);
case "int2":
return(System.Data.DbType.Int16);
case "timestamp":
switch (type)
{
case ConnectionContext.ConnectionDataType.SqlDataType:
case ConnectionContext.ConnectionDataType.AccessDataType:
case ConnectionContext.ConnectionDataType.ScxDataType:
return(System.Data.DbType.Binary);
default:
return(System.Data.DbType.DateTime);
}
case "enum":
return(System.Data.DbType.Object);
case "geometry":
return(System.Data.DbType.Object);
case "int24":
return(System.Data.DbType.Int32);
case "longblob":
return(System.Data.DbType.Binary);
case "longtext":
return(System.Data.DbType.String);
case "mediumblob":
return(System.Data.DbType.Binary);
case "mediumtext":
return(System.Data.DbType.String);
case "newdecimal":
return(System.Data.DbType.Decimal);
case "set":
return(System.Data.DbType.String);
case "tinyblob":
return(System.Data.DbType.Binary);
case "tinytext":
return(System.Data.DbType.String);
case "ubyte":
return(System.Data.DbType.SByte);
case "uint24":
return(System.Data.DbType.UInt32);
case "uint64":
return(System.Data.DbType.UInt64);
case "varstring":
return(System.Data.DbType.String);
case "year":
return(System.Data.DbType.UInt16);
case "null":
return(System.Data.DbType.Object);
case "uninitialized":
return(System.Data.DbType.Object);
default:
return(System.Data.DbType.Object);
}
}
public string[] GetList(string prefixText, int count)
{
List <string> list = null;
try
{
ConnectionStringExtensionElement[] items = ConnectionStringExtensionConfigurationManager.ConnectionStringExtensionElements();
if (items != null)
{
if (items.Count() > 0)
{
// For each service host configuration find
// the corresponding service type.
foreach (ConnectionStringExtensionElement item in items)
{
// Get the current type name
// and create a instance of the type.
Type typeName = Type.GetType(item.TypeName, true, true);
object typeNameInstance = Activator.CreateInstance(typeName);
if (WebServiceType == null)
{
WebServiceType = this;
}
if (WebServiceType != null)
{
if (WebServiceType.GetType().FullName.ToLower() == typeNameInstance.GetType().FullName.ToLower())
{
Type dataAccessProviderType = Type.GetType(item.DataAccessProvider, true, true);
ConnectionContext.ConnectionType connectionType = ConnectionContext.ConnectionTypeConverter.GetConnectionType(item.ConnectionType);
ConnectionContext.ConnectionDataType connectionDataType = ConnectionContext.ConnectionTypeConverter.GetConnectionDataType(item.ConnectionDataType);
// Build the current data object type and
// the select data model generic type.
Type dataType = Type.GetType(item.DataObjectTypeName, true, true);
Type listGenericType = typeof(SelectDataGenericBase <>);
// Create the generic type parameters
// and create the genric type.
Type[] typeArgs = { dataType };
Type listGenericTypeConstructor = listGenericType.MakeGenericType(typeArgs);
// Create an instance of the data access provider
Nequeo.Data.DataType.IDataAccess dataAccess = ((Nequeo.Data.DataType.IDataAccess)Activator.CreateInstance(dataAccessProviderType));
// Add the genric type contructor parameters
// and create the generic type instance.
object[] parameters = new object[] { item.ConnectionName, connectionType, connectionDataType, dataAccess };
object listGeneric = Activator.CreateInstance(listGenericTypeConstructor, parameters);
PropertyInfo propertyInfo = dataType.GetProperty(item.DataObjectPropertyName);
string name = propertyInfo.Name;
// Get the current object.
Object[] args = new Object[]
{
0,
count,
name + " ASC",
"(SqlQueryMethods.Like(" + name + ", \"" + prefixText + "%\"))"
};
// Add the current data row to the
// business object collection.
object ret = listGeneric.GetType().InvokeMember("SelectData",
BindingFlags.DeclaredOnly | BindingFlags.Public |
BindingFlags.Instance | BindingFlags.InvokeMethod,
null, listGeneric, args);
// Cast the data object type as an enumerable object,
// get the enumerator.
System.Collections.IEnumerable itemsRet = (System.Collections.IEnumerable)ret;
System.Collections.IEnumerator dataObjects = itemsRet.GetEnumerator();
// Iterate through the collection.
while (dataObjects.MoveNext())
{
// If the current index equals the
// selected index then return
// the data object type.
// Get the property.
PropertyInfo property = dataObjects.Current.GetType().GetProperty(name);
// Get the current value of the property
string v = property.GetValue(dataObjects.Current, null).ToString();
list.Add(v);
}
dataObjects.Reset();
break;
}
}
}
}
}
}
catch (Exception ex)
{
string errorMessage = ex.Message;
LogHandler.WriteTypeMessage(errorMessage, typeof(AutoComplete).GetMethod("GetList"));
}
return(list != null ? list.ToArray() : new string[0]);
}
/// <summary>
/// Validates a user to a SQL or integrated account.
/// </summary>
/// <param name="username">The user name.</param>
/// <param name="password">The user password.</param>
private void ValidateUser(string username, string password)
{
try
{
bool authResult = false;
ConnectionStringExtensionElement[] items = ConnectionStringExtensionConfigurationManager.ConnectionStringExtensionElements();
if (items.Count() > 0)
{
// For each service host configuration find
// the corresponding service type.
foreach (ConnectionStringExtensionElement item in items)
{
// Get the current type name
// and create a instance of the type.
Type typeName = Type.GetType(item.TypeName, true, true);
object typeNameInstance = Activator.CreateInstance(typeName);
if (UserNamePasswordValidatorType == null)
{
UserNamePasswordValidatorType = this;
}
if (UserNamePasswordValidatorType != null)
{
if (UserNamePasswordValidatorType.GetType().FullName.ToLower() == typeNameInstance.GetType().FullName.ToLower())
{
Type dataAccessProviderType = Type.GetType(item.DataAccessProvider, true, true);
ConnectionContext.ConnectionType connectionType = ConnectionContext.ConnectionTypeConverter.GetConnectionType(item.ConnectionType);
ConnectionContext.ConnectionDataType connectionDataType = ConnectionContext.ConnectionTypeConverter.GetConnectionDataType(item.ConnectionDataType);
// Data table containing the data.
DataTable dataTable = null;
string sql =
"SELECT [" + item.IndicatorColumnName + "] " +
"FROM [" + (String.IsNullOrEmpty(item.DatabaseOwner) ? "" : item.DatabaseOwner + "].[") + item.TableName.Replace(".", "].[") + "] " +
"WHERE ([" + item.ComparerColumnName + "] = '" + username + "')";
sql = Nequeo.Data.DataType.DataTypeConversion.
GetSqlConversionDataTypeNoContainer(connectionDataType, sql);
string providerName = null;
string connection = string.Empty;
string connectionString = string.Empty;
// Get the current database connection string
// from the configuration file through the
// specified configuration key.
using (DatabaseConnections databaseConnection = new DatabaseConnections())
connection = databaseConnection.DatabaseConnection(item.ConnectionName, out providerName);
// If empty string is returned then
// value should be the connection string.
if (String.IsNullOrEmpty(connection))
{
connectionString = item.ConnectionName;
}
else
{
connectionString = connection;
}
// Create an instance of the data access provider
Nequeo.Data.DataType.IDataAccess dataAccess = ((Nequeo.Data.DataType.IDataAccess)Activator.CreateInstance(dataAccessProviderType));
// Get the connection type
switch (connectionType)
{
// Get the permission data from the
// database through the sql provider.
case ConnectionContext.ConnectionType.SqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.PostgreSqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OracleClientConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OleDbConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OdbcConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.MySqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
default:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
}
// Permission data exists.
if (dataTable != null)
{
if (dataTable.Rows.Count > 0)
{
string permissionValueItem = dataTable.Rows[0][item.IndicatorColumnName].ToString();
if (!String.IsNullOrEmpty(permissionValueItem))
{
if (password.ToLower() == permissionValueItem.ToLower())
{
authResult = true;
break;
}
}
}
}
}
}
}
}
// If the user has been validated
// and autharised then allow connection.
if (!authResult)
{
throw new FaultException("Unknown Username or Incorrect Password");
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Directly applied nullable types.
/// </summary>
/// <param name="sqlDataType">The database type.</param>
/// <param name="type">The database connection type.</param>
/// <returns>True if the type is nullable else false.</returns>
public static bool GetApplyNullableType(string sqlDataType,
ConnectionContext.ConnectionDataType type)
{
return(!GetNullableType(sqlDataType, type));
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public int ExecuteCommand(ref DbCommand dbCommand, string commandText, CommandType commandType,
string connectionString, ConnectionContext.ConnectionDataType connectionDataType, params DbParameter[] values)
{
return(ExecuteCommand(ref dbCommand, commandText, commandType, connectionString, values));
}
public string[] UserListService()
{
string[] userList = null;
try
{
ConnectionStringExtensionElement[] items = ConnectionStringExtensionConfigurationManager.ConnectionStringExtensionElements();
if (items != null)
{
if (items.Count() > 0)
{
// For each service host configuration find
// the corresponding service type.
foreach (ConnectionStringExtensionElement item in items)
{
if (item.ServiceMethodName.ToLower() == "userlistservice")
{
// Get the current type name
// and create a instance of the type.
Type typeName = Type.GetType(item.TypeName, true, true);
object typeNameInstance = Activator.CreateInstance(typeName);
if (DynamicDataType == null)
{
DynamicDataType = this;
}
if (DynamicDataType != null)
{
if (DynamicDataType.GetType().FullName.ToLower() == typeNameInstance.GetType().FullName.ToLower())
{
Type dataAccessProviderType = Type.GetType(item.DataAccessProvider, true, true);
ConnectionContext.ConnectionType connectionType = ConnectionContext.ConnectionTypeConverter.GetConnectionType(item.ConnectionType);
ConnectionContext.ConnectionDataType connectionDataType = ConnectionContext.ConnectionTypeConverter.GetConnectionDataType(item.ConnectionDataType);
// Data table containing the data.
DataTable dataTable = null;
string sql =
"SELECT [" + item.IndicatorColumnName + "], [" + item.DataObjectPropertyName + "] " +
"FROM [" + (String.IsNullOrEmpty(item.DatabaseOwner) ? "" : item.DatabaseOwner + "].[") + item.TableName.Replace(".", "].[") + "] ";
if ((!String.IsNullOrEmpty(item.ComparerColumnName)) && (!String.IsNullOrEmpty(item.ComparerValue)))
{
sql += "WHERE ([" + item.ComparerColumnName + "] = '" + item.ComparerValue + "')";
}
sql = Nequeo.Data.DataType.DataTypeConversion.
GetSqlConversionDataTypeNoContainer(connectionDataType, sql);
string providerName = null;
string connection = string.Empty;
string connectionString = string.Empty;
// Get the current database connection string
// from the configuration file through the
// specified configuration key.
using (DatabaseConnections databaseConnection = new DatabaseConnections())
connection = databaseConnection.DatabaseConnection(item.ConnectionName, out providerName);
// If empty string is returned then
// value should be the connection string.
if (String.IsNullOrEmpty(connection))
{
connectionString = item.ConnectionName;
}
else
{
connectionString = connection;
}
// Create an instance of the data access provider
Nequeo.Data.DataType.IDataAccess dataAccess = ((Nequeo.Data.DataType.IDataAccess)Activator.CreateInstance(dataAccessProviderType));
// Get the connection type
switch (connectionType)
{
// Get the permission data from the
// database through the sql provider.
case ConnectionContext.ConnectionType.SqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.PostgreSqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OracleClientConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OleDbConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OdbcConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.MySqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
default:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
}
// Permission data exists.
if (dataTable != null)
{
if (dataTable.Rows.Count > 0)
{
List <string> cols = new List <string>();
foreach (DataRow row in dataTable.Rows)
{
cols.Add("<a href=\"" + item.ServiceMethodRedirectionUrl + "?" + item.DataObjectPropertyName + "=" +
row[item.DataObjectPropertyName].ToString() + "\">" + row[item.IndicatorColumnName].ToString() + "</a>");
}
// Assign the collection.
userList = cols.ToArray();
break;
}
}
}
}
}
}
}
}
}
catch (Exception ex)
{
string errorMessage = ex.Message;
LogHandler.WriteTypeMessage(errorMessage, typeof(DynamicData).GetMethod("UserListService"));
}
// Return the list of users.
return(userList == null ? new string[0] : userList);
}
/// <summary>
/// Executes the query.
/// </summary>
/// <typeparam name="TypeDataTable">The current data table type.</typeparam>
/// <param name="dataTable">The data table to return containing the data.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery <TypeDataTable>(ref TypeDataTable dataTable, string queryText,
CommandType commandType, string connectionString, ConnectionContext.ConnectionDataType connectionDataType,
params DbParameter[] values)
where TypeDataTable : System.Data.DataTable, new()
{
dataTable = new TypeDataTable();
// Initial connection objects.
DbCommand dbCommand = null;
OleDbConnection oleDbConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (oleDbConnection = new OleDbConnection(connectionString))
{
// Open the connection.
oleDbConnection.Open();
// Create the command and assign any parameters.
dbCommand = new OleDbCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
connectionDataType, queryText), oleDbConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (OleDbParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
dataReader.Close();
}
// Close the database connection.
oleDbConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (oleDbConnection != null)
{
oleDbConnection.Close();
}
}
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText,
CommandType commandType, string connectionString, ConnectionContext.ConnectionDataType connectionDataType,
params DbParameter[] values)
{
// Initial connection objects.
dbCommand = null;
Int32 returnValue = -1;
OleDbConnection oleDbConnection = null;
OleDbTransaction oleDbTransaction = null;
try
{
// Create a new connection.
using (oleDbConnection = new OleDbConnection(connectionString))
{
// Open the connection.
oleDbConnection.Open();
// Start a new transaction.
oleDbTransaction = oleDbConnection.BeginTransaction();
// Create the command and assign any parameters.
dbCommand = new OleDbCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
connectionDataType, commandText), oleDbConnection);
dbCommand.CommandType = commandType;
dbCommand.Transaction = oleDbTransaction;
if (values != null)
{
foreach (OleDbParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = dbCommand.ExecuteNonQuery();
// Commit the transaction.
oleDbTransaction.Commit();
// Close the database connection.
oleDbConnection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (oleDbTransaction != null)
{
oleDbTransaction.Rollback();
}
}
catch { }
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (oleDbConnection != null)
{
oleDbConnection.Close();
}
}
}
/// <summary>
/// Default constructor
/// </summary>
/// <param name="connectionDataType">The connection data type</param>
public DataTypeConversion(ConnectionContext.ConnectionDataType connectionDataType)
{
_connectionDataType = connectionDataType;
}
/// <summary>
/// Gets the list of oledb parameters.
/// </summary>
/// <param name="functionParameters">The function parameters created.</param>
/// <param name="functionValues">The function values created.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="methodInfo">The current method information.</param>
/// <param name="parameters">The parameter collection.</param>
/// <returns>The list of parameters.</returns>
public DbParameter[] GetParameters(ref string functionParameters, ref string functionValues,
ConnectionContext.ConnectionDataType connectionDataType, MethodInfo methodInfo, params Object[] parameters)
{
int i = -1;
long length = -1;
string dbType = null;
bool isNullable = true;
string parameterName = null;
System.Data.ParameterDirection parameterDirection = ParameterDirection.Input;
// Create a new instance of the oracle parameter collection.
string functionParameterNames = string.Empty;
string functionValueNames = string.Empty;
List <System.Data.OleDb.OleDbParameter> oledbParameters = new List <OleDbParameter>();
DataTypeConversion dataTypeConversion = new DataTypeConversion(connectionDataType);
// For each parameter within the method.
foreach (ParameterInfo parameter in methodInfo.GetParameters())
{
// For each attribute for the parameter.
foreach (object attribute in parameter.GetCustomAttributes(true))
{
// If the attribute is the
// function parameter column attribute.
if (attribute is Nequeo.Data.Custom.FunctionParameterAttribute)
{
// Increment the parameter count.
i++;
// Cast the current attribute.
Nequeo.Data.Custom.FunctionParameterAttribute att =
(Nequeo.Data.Custom.FunctionParameterAttribute)attribute;
dbType = att.DbType;
length = att.Length;
parameterName = att.Name;
isNullable = att.IsNullable;
parameterDirection = att.ParameterDirection;
// Add each parameter to the collection.
oledbParameters.Add(new System.Data.OleDb.OleDbParameter(
parameterName, Nequeo.Data.OleDb.ClientDataType.GetOleDbDbType(dbType), Convert.ToInt32(length),
parameterDirection, isNullable, ((Byte)(0)), ((Byte)(0)), "", System.Data.DataRowVersion.Current, parameters[i]));
// If the parameter is an input type
// then add the parameter to the list.
if (parameterDirection == ParameterDirection.Input || parameterDirection == ParameterDirection.InputOutput)
{
functionParameterNames += parameterName + ", ";
functionValueNames += dataTypeConversion.GetSqlStringValue(parameters[i].GetType(), parameters[i]) + ", ";
}
}
}
}
// Get the parameters for the function
functionParameters = functionParameterNames.TrimEnd(' ', ',');
functionValues = functionValueNames.TrimEnd(' ', ',');
// Return the oledb parameters.
return(oledbParameters.ToArray());
}
/// <summary>
/// Gets the list of postgresql parameters.
/// </summary>
/// <param name="functionParameters">The function parameters created.</param>
/// <param name="functionValues">The function values created.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="methodInfo">The current method information.</param>
/// <param name="parameters">The parameter collection.</param>
/// <returns>The list of parameters.</returns>
public DbParameter[] GetParameters(ref string functionParameters, ref string functionValues,
ConnectionContext.ConnectionDataType connectionDataType, MethodInfo methodInfo, params object[] parameters)
{
return(GetParameters(ref functionParameters, ref functionValues, methodInfo, parameters));
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataTable">The data table to return containing the data.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="getSchemaTable">Get the table schema from the database and then load the data. Used when
/// returning data from the database for a particilar table.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref DataTable dataTable, string queryText, CommandType commandType, string connectionString,
bool getSchemaTable, ConnectionContext.ConnectionDataType connectionDataType, params DbParameter[] values)
{
return(ExecuteQuery(ref dataTable, queryText, commandType, connectionString, getSchemaTable, values));
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataSet">The data set to return containing the data.</param>
/// <param name="tables">The tables names to add.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref DataSet dataSet, string[] tables, string queryText, CommandType commandType,
string connectionString, ConnectionContext.ConnectionDataType connectionDataType, params DbParameter[] values)
{
return(ExecuteQuery(ref dataSet, tables, queryText, commandType, connectionString, values));
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="odbcCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteClientCommand(ref OdbcCommand odbcCommand, string commandText,
CommandType commandType, string connectionString, ConnectionContext.ConnectionDataType connectionDataType,
params OdbcParameter[] values)
{
// Initial connection objects.
odbcCommand = null;
Int32 returnValue = -1;
OdbcConnection connection = null;
OdbcTransaction transaction = null;
try
{
// Create a new connection.
using (connection = new OdbcConnection(connectionString))
{
// Open the connection.
connection.Open();
// Start a new transaction.
transaction = connection.BeginTransaction();
// Create the command and assign any parameters.
odbcCommand = new OdbcCommand(commandText, connection);
odbcCommand.CommandType = commandType;
odbcCommand.Transaction = transaction;
if (values != null)
{
foreach (OdbcParameter sqlParameter in values)
{
odbcCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = odbcCommand.ExecuteNonQuery();
// Commit the transaction.
transaction.Commit();
// Close the database connection.
connection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (transaction != null)
{
transaction.Rollback();
}
}
catch (Exception exp)
{
throw new Exception(exp.Message, exp.InnerException);
}
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public string GetJSonDataTableService(int?iDisplayStart, int iDisplayLength, string sSearch, bool bEscapeRegex, int iColumns,
int iSortingCols, int iSortCol_0, string sSortDir_0, string sEcho, string extensionName)
{
// Create a new json datatable service object.
JSonDataTableService dataTableObject =
new JSonDataTableService()
{
sEcho = Convert.ToInt32(sEcho).ToString()
};
// Default data to send back.
string jSonData = "{ \"sEcho\": " + sEcho + ", \"iTotalRecords\": 0, \"iTotalDisplayRecords\": 0, \"aaData\": [] }";
try
{
// Create a new json datatable service object.
dataTableObject = new JSonDataTableService()
{
sEcho = Convert.ToInt32(sEcho).ToString()
};
// Get the configuration data.
ConnectionStringExtensionElement[] items = ConnectionStringExtensionConfigurationManager.ConnectionStringExtensionElements();
if (items != null)
{
if (items.Count() > 0)
{
// For each service host configuration find
// the corresponding service type.
foreach (ConnectionStringExtensionElement item in items)
{
if (item.Name.ToLower() == extensionName.ToLower())
{
// Get the current type name
// and create a instance of the type.
Type typeName = Type.GetType(item.TypeName, true, true);
object typeNameInstance = Activator.CreateInstance(typeName);
if (DynamicDataType == null)
{
DynamicDataType = this;
}
if (DynamicDataType != null)
{
if (DynamicDataType.GetType().FullName.ToLower() == typeNameInstance.GetType().FullName.ToLower())
{
Type dataAccessProviderType = Type.GetType(item.DataAccessProvider, true, true);
ConnectionContext.ConnectionType connectionType = ConnectionContext.ConnectionTypeConverter.GetConnectionType(item.ConnectionType);
ConnectionContext.ConnectionDataType connectionDataType = ConnectionContext.ConnectionTypeConverter.GetConnectionDataType(item.ConnectionDataType);
// Build the current data object type and
// the select data model generic type.
Type dataType = Type.GetType(item.DataObjectTypeName, true, true);
Type listGenericType = typeof(SelectDataGenericBase <>);
// Create the generic type parameters
// and create the genric type.
Type[] typeArgs = { dataType };
Type listGenericTypeConstructor = listGenericType.MakeGenericType(typeArgs);
// Create an instance of the data access provider
Nequeo.Data.DataType.IDataAccess dataAccess = ((Nequeo.Data.DataType.IDataAccess)Activator.CreateInstance(dataAccessProviderType));
// Add the genric type contructor parameters
// and create the generic type instance.
object[] parameters = new object[] { item.ConnectionName, connectionType, connectionDataType, dataAccess };
object listGeneric = Activator.CreateInstance(listGenericTypeConstructor, parameters);
// Get the current sorting column use this column as the
// searc
string[] columnNames = item.JsonDataTableColumnNames.Split(new char[] { '|' }, StringSplitOptions.None);
PropertyInfo propertyInfo = dataType.GetProperty(columnNames[iSortCol_0]);
string name = propertyInfo.Name;
// Get the current object.
Object[] args = null;
Object[] countArgs = null;
// If a search text exits.
if (!String.IsNullOrEmpty(sSearch))
{
args = new Object[]
{
(iDisplayStart != null ? (int)iDisplayStart : 0),
iDisplayLength,
name + (String.IsNullOrEmpty(sSortDir_0) ? " ASC" : " " + sSortDir_0.ToUpper()),
"(SqlQueryMethods.Like(" + name + ", \"" + sSearch + "%\"))"
};
// Get the current object.
countArgs = new Object[]
{
name + " LIKE '" + sSearch + "%'"
};
}
else
{
args = new Object[]
{
(iDisplayStart != null ? (int)iDisplayStart : 0),
iDisplayLength,
name + (String.IsNullOrEmpty(sSortDir_0) ? " ASC" : " " + sSortDir_0.ToUpper())
};
}
// Add the current data row to the
// business object collection.
object retCount = listGeneric.GetType().InvokeMember("GetRecordCount",
BindingFlags.DeclaredOnly | BindingFlags.Public |
BindingFlags.Instance | BindingFlags.InvokeMethod,
null, listGeneric, countArgs);
// Add the current data row to the
// business object collection.
object ret = listGeneric.GetType().InvokeMember("SelectData",
BindingFlags.DeclaredOnly | BindingFlags.Public |
BindingFlags.Instance | BindingFlags.InvokeMethod,
null, listGeneric, args);
// Cast the data object type as an enumerable object,
// get the enumerator.
System.Collections.IEnumerable itemsRet = (System.Collections.IEnumerable)ret;
System.Collections.IEnumerator dataObjects = itemsRet.GetEnumerator();
// Create the data array.
string[,] aaData =
new string[
(iDisplayLength <= Convert.ToInt32((long)retCount) ? iDisplayLength : Convert.ToInt32((long)retCount)),
columnNames.Length];
int z = 0;
// Iterate through the collection.
while (dataObjects.MoveNext())
{
// If the current index equals the
// selected index then return
// the data object type.
// Get the property.
for (int i = 0; i < columnNames.Length; i++)
{
// Get the property info the current column
string columnValue = string.Empty;
PropertyInfo property = dataObjects.Current.GetType().GetProperty(columnNames[i]);
try
{
// Get the current value of the property
columnValue = property.GetValue(dataObjects.Current, null).ToString();
}
catch { }
// Add the value to the data two dimentinal array.
aaData[z, i] = columnValue.Trim();
}
// Increamnt the row count.
z++;
}
dataObjects.Reset();
dataTableObject.iTotalRecords = Convert.ToInt32((long)retCount);
dataTableObject.iTotalDisplayRecords = Convert.ToInt32((long)retCount);
dataTableObject.aaData = aaData;
// Serialse the data to a JSON format.
jSonData = JavaObjectNotation.JSonCustomSerializer(dataTableObject);
break;
}
}
}
}
}
}
}
catch (Exception ex)
{
string errorMessage = ex.Message;
LogHandler.WriteTypeMessage(errorMessage, typeof(DynamicData).GetMethod("GetJSonDataTableService"));
}
// Return serialised json data.
return(jSonData);
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataSet">The data set to return containing the data.</param>
/// <param name="tables">The data tables names to return.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public OdbcCommand ExecuteClientQuery(ref System.Data.DataSet dataSet, string[] tables, string queryText,
CommandType commandType, string connectionString, ConnectionContext.ConnectionDataType connectionDataType,
params OdbcParameter[] values)
{
// Initial connection objects.
OdbcCommand sqlCommand = null;
OdbcConnection connection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (connection = new OdbcConnection(connectionString))
{
// Open the connection.
connection.Open();
// Create the command and assign any parameters.
sqlCommand = new OdbcCommand(queryText, connection);
sqlCommand.CommandType = commandType;
if (values != null)
{
foreach (OdbcParameter sqlParameter in values)
{
sqlCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = sqlCommand.ExecuteReader())
{
dataSet = new System.Data.DataSet();
dataSet.EnforceConstraints = false;
dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
dataReader.Close();
}
// Close the database connection.
connection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(sqlCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}
public void ProcessRequest(HttpContext context)
{
try
{
// Get the collection of http handler configuration extensions
HttpHandlerDataExtensionElement[] httpCollection = HttpHandlerDataConfigurationManager.HttpHandlerExtensionElements();
if (httpCollection != null)
{
// If http extensions exist
if (httpCollection.Count() > 0)
{
// For each configuration
foreach (HttpHandlerDataExtensionElement item in httpCollection)
{
// Get the current http handler type
// and create a instance of the type.
Type httpHandlerType = BuildManager.GetType(item.HttpHandlerTypeName, true, true);
object httpHandlerTypeInstance = Activator.CreateInstance(httpHandlerType);
if (HttpHandlerType == null)
{
HttpHandlerType = this;
}
if (HttpHandlerType != null)
{
if (HttpHandlerType.GetType().FullName.ToLower() == httpHandlerTypeInstance.GetType().FullName.ToLower())
{
// Get the query string associated with this http handler
string value = context.Request.QueryString[item.UrlQueryTextName];
// If a query string is assosicated with
// this http handler then return the
// data from the database and place this
// data in the current session object.
if (!String.IsNullOrEmpty(value))
{
Type dataAccessProviderType = BuildManager.GetType(item.DataAccessProvider, true, true);
ConnectionContext.ConnectionType connectionType = ConnectionContext.ConnectionTypeConverter.GetConnectionType(item.ConnectionType);
ConnectionContext.ConnectionDataType connectionDataType = ConnectionContext.ConnectionTypeConverter.GetConnectionDataType(item.ConnectionDataType);
// Build the current data object type and
// the select data model generic type.
Type dataType = BuildManager.GetType(item.DataObjectTypeName, true, true);
Type listGenericType = typeof(SelectDataGenericBase <>);
// Create the generic type parameters
// and create the genric type.
Type[] typeArgs = { dataType };
Type listGenericTypeConstructor = listGenericType.MakeGenericType(typeArgs);
// Create an instance of the data access provider
Nequeo.Data.DataType.IDataAccess dataAccess = ((Nequeo.Data.DataType.IDataAccess)Activator.CreateInstance(dataAccessProviderType));
// Add the genric tyoe contructor parameters
// and create the generic type instance.
object[] parameters = new object[] { item.ConnectionName, connectionType, connectionDataType, dataAccess };
object listGeneric = Activator.CreateInstance(listGenericTypeConstructor, parameters);
PropertyInfo propertyInfo = dataType.GetProperty(item.DataObjectPropertyName);
object valueType = Convert.ChangeType(value, propertyInfo.PropertyType);
// Get the current object.
Object[] args = new Object[] {
(item.DataObjectPropertyName + " == @0"),
item.ReferenceLazyLoading,
new object[] { valueType }
};
// Add the current data row to the
// business object collection.
object ret = listGeneric.GetType().InvokeMember("SelectDataEntitiesPredicate",
BindingFlags.DeclaredOnly | BindingFlags.Public |
BindingFlags.Instance | BindingFlags.InvokeMethod,
null, listGeneric, args);
// Assign the generic collection data
// to the current session sate with
// the unquie configuration name as the key.
Nequeo.Caching.RuntimeCache.Set(item.Name, ret, (double)600.0);
}
}
}
else
{
break;
}
}
if (HttpHandlerType != null)
{
// Redirect to the current request.
context.Server.Execute(httpCollection[0].ChildPageGroupExecution);
}
}
}
}
catch (Exception ex)
{
context.AddError(ex);
LogHandler.WriteTypeMessage(ex.Message, typeof(GenericData).GetMethod("ProcessRequest"));
}
}
