/// <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")); } }