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