/// <summary> /// Execute the specified SQL command /// </summary> /// <param name="command">SQL command to execute</param> /// <returns>Value returned from the <c>ExecuteNonQuery</c> method</returns> public int Execute(CommandWithParameters command) { using (var sqlCommand = conx.CreateCommand()) try { sqlCommand.CommandText = command.Text; sqlCommand.Parameters.Clear(); if (command.Parameters != null) { foreach (var key in command.Parameters.Keys) { sqlCommand.Parameters.Add(new SqliteParameter { ParameterName = key, Value = command.Parameters[key], }); } } Debug.Log($"<color=#{ColorUtility.ToHtmlStringRGBA(Color.green)}>SQL command</color>:\n\n" + sqlCommand.CommandText); return(sqlCommand.ExecuteNonQuery()); } catch (Exception ex) { Debug.LogError(ex); Debug.LogError("SqliteException - failed command:\n\n" + sqlCommand.CommandText); return(FailedScriptErrorCode); } }
public override void Execute() { // Create the sub-tasks. if (FindParametersInCommandText == null) { FindParametersInCommandText = new FindParametersInCommandText(); } FindParametersInCommandText.CommandText = CommandWithParameters.CommandText; FindParametersInCommandText.Execute(); foreach (var parameterNameX in FindParametersInCommandText.ParameterNames) { var objectType = typeof(T); object objectContainingPropertyValue = ObjectWithValues; // Strip off the first character of the parameter name to find a matching property (e.g. make @Name => Name). var nameOfPropertyContainingValue = parameterNameX.Substring(1); // If the parameter contains a dot then the property must be a complex object, and therefore we must look inside the object to find the value. PropertyInfo property; while (nameOfPropertyContainingValue.Contains(".")) { // Look for a property using the string that comes before the dot. var indexOfDot = nameOfPropertyContainingValue.IndexOf("."); property = objectType.GetProperty(nameOfPropertyContainingValue.Substring(0, indexOfDot)); // Apparently there isn't a property that is a complex object that matches the parameter name. if (property == null) { break; } // Reset variables using the property that was found that matched the string that came before the dot. objectType = property.PropertyType; objectContainingPropertyValue = property.GetValue(objectContainingPropertyValue, null); nameOfPropertyContainingValue = nameOfPropertyContainingValue.Substring(indexOfDot + 1); } // Use the property to build the parameter. property = objectType.GetProperty(nameOfPropertyContainingValue); if (property != null) { IDbDataParameter dbDataParameter = CommandWithParameters.CreateParameter(); // If the property came from a complex object then it contains a dot, and dots aren't allowed in parameter names. dbDataParameter.ParameterName = parameterNameX.Replace(".", "_"); CommandWithParameters.CommandText = CommandWithParameters.CommandText.Replace(parameterNameX, parameterNameX.Replace(".", "_")); dbDataParameter.Value = property.GetValue(objectContainingPropertyValue, null) ?? DBNull.Value; CommandWithParameters.Parameters.Add(dbDataParameter); } } }
/// <summary> /// Get the raw results from the specified SQL query /// </summary> /// <param name="conx">Opened Database connection</param> /// <param name="query">SQL query to execute</param> /// <param name="getFieldNames">Value indicating if the first result set of the query result should contain the field names</param> /// <returns>Raw results</returns> public object[][] GetRawCollection(CommandWithParameters query, bool getFieldNames = false) { string[] fieldNameCollection = null; var resultSet = new List <object[]>(); using (var sqlQuery = conx.CreateCommand()) { sqlQuery.CommandText = query.Text; if (query.Parameters != null) { foreach (var key in query.Parameters.Keys) { sqlQuery.Parameters.Add(new SqliteParameter { ParameterName = key, Value = query.Parameters[key], }); } } Debug.Log($"<color=#{ColorUtility.ToHtmlStringRGBA(Color.green)}>SQL query</color>:\n\n" + sqlQuery.CommandText); var reader = sqlQuery.ExecuteReader(); fieldNameCollection = new string[reader.FieldCount] .Select((_entry, inx) => reader.GetName(inx)) .ToArray(); while (reader.Read()) { resultSet.Add( new object[reader.FieldCount] .Select((_entry, inx) => reader.GetValue(inx)) .ToArray() ); } } if (getFieldNames) { resultSet.Insert(0, fieldNameCollection); } return(resultSet.ToArray()); }