public void AddValuesByColumnName(string columnName, object value)
{
if (String.IsNullOrEmpty(columnName))
{
throw new ArgumentException("columnName está nulo ou vazio", nameof(columnName));
}
columnName = columnName.ToUpper();
if (!ColumnsName.Exists(x => x.ColumnName.Equals(columnName)))
{
throw new Exception(
$"Não foi encontrado a coluna {columnName} na configuração de colunas da tabela {TableName}");
}
if (!ColumnsValues.Keys.Contains(columnName))
{
ColumnsValues[columnName] = new List <object>();
}
if (value != null && string.IsNullOrEmpty(value.ToString()))
{
value = null;
}
ColumnsValues[columnName].Add(value);
}
public InsertQuery ColumnsToInsert(IEnumerable <string> columnsName)
{
foreach (string columnName in columnsName)
{
ColumnsName.Add(Query.ColumnSyntax(columnName));
}
return(this);
}
public void AddColumnName(string columnName, Type typeColumn)
{
if (String.IsNullOrEmpty(columnName))
{
throw new ArgumentException("columnName está nulo ou vazio", nameof(columnName));
}
columnName = columnName.ToUpper();
if (!ColumnsName.Exists(x => x.ColumnName.Equals(columnName)))
{
Log($"Adicionado ao Oracle ArrayBind coluna: {columnName}, tipo de dados: {typeColumn.ToString()}.");
ColumnsName.Add(new DbColumn(columnName, typeColumn));
}
}
private string getName(string table, string field)
{
string query = @"SELECT column_comment
FROM information_schema.columns
WHERE table_schema = 'sectors_status_map' and
table_name = '" + table +
"' and COLUMN_NAME='" + field + "'";
//string text = new MySqlCommand(query, myConnection).ExecuteScalar().ToString();
string text = ((string)Base.Select(query).Tables[0].Rows[0][0]);
TablesColumns.Add(new List <string>());
TablesColumns[TablesColumns.Count - 1].Add(table);
TablesColumns[TablesColumns.Count - 1].Add(field);
ColumnsName.Add(text);
return(text);
}
public void ExecuteOracleArrayBind(int amountBlocks)
{
Log($"Iniciando execução de Insert com Blocos de {amountBlocks} registros.");
double countAllBlocks = (double)ColumnsValues.First().Value.Count;
int interations = Convert.ToInt32(Math.Ceiling(countAllBlocks / (double)amountBlocks));
Log($"Serão {interations} iterações, com blocos de {amountBlocks} registros.");
for (int blockIndex = 0; blockIndex < interations; blockIndex++)
{
List <OracleParameter> columnsOracleParameter = new List <OracleParameter>();
foreach (var item in ColumnsValues)
{
var column = ColumnsName.Single(x => x.ColumnName.Equals(item.Key));
var paramOracle = new OracleParameter();
paramOracle.OracleDbType = GetType(column.DataType);
if (blockIndex == interations - 1)
{
paramOracle.Value = item.Value.Skip(amountBlocks * blockIndex).ToArray();
}
else
{
paramOracle.Value = item.Value.Skip(amountBlocks * blockIndex).Take(amountBlocks).ToArray();
}
columnsOracleParameter.Add(paramOracle);
}
int arrayBindCount = ((columnsOracleParameter.First().Value as IList <object>) ?? throw new InvalidOperationException()).Count();
Log(
$"Iniciando inserção: Iteração {blockIndex + 1} de {interations} com blocos de {arrayBindCount} registros.");
using (OracleConnection con = new OracleConnection(StringConnection))
{
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = GenerateCommandText();
cmd.ArrayBindCount = arrayBindCount;
cmd.Parameters.AddRange(columnsOracleParameter.ToArray());
cmd.ExecuteNonQuery();
}
Log(
$"Término da inserção: Iteração {blockIndex + 1} de {interations} com blocos de {arrayBindCount} registros.");
}
}
public InsertQuery ColumnsToInsert(string columnName, params string[] columnsName)
{
ColumnsName.Add(Query.ColumnSyntax(columnName));
return(ColumnsToInsert(columnsName));
}
public List <string> GetColumnsName()
{
return(ColumnsName.Select(x => x.ColumnName).ToList());
}
/// <summary>
/// Parse sql select query
/// </summary>
/// <param name="words"></param>
private void parse(string[] words)
{
var fromIndex = getIndexWord(words, "from");
if (fromIndex + 1 >= words.Length)
{
throw new SelectCommandParse($"Not found table name into query.");
}
// set table name and database name if format [dbname].[tableName]
var tableAndDatabaseNames = words[fromIndex + 1].Split('.');
if (tableAndDatabaseNames.Length == 2)
{
DatabaseName = tableAndDatabaseNames[0];
TableName = tableAndDatabaseNames[1];
}
else
{
TableName = tableAndDatabaseNames[0];
}
// set flag if all columns
if (words[1] == "*" && fromIndex == 2)
{
AllColumns = true;
}
// if there are columns name by commas
else if (fromIndex >= 2)
{
for (int i = 1; i < fromIndex; i++)
{
ColumnsName.Add(words[i].Replace(",", "").Trim());
}
}
else
{
throw new SelectCommandParse($"Not found columns into query.");
}
var orderIndex = getIndexWord(words, "order");
var whereIndex = getIndexWord(words, "where");
// parse conditions
if (whereIndex != -1)
{
var indexTo = orderIndex == -1 ? words.Length : orderIndex;
var conditionParts = new List <string>();
for (int i = whereIndex + 1; i < indexTo; i++)
{
if (words[i].ToLower() == "and" || words[i].ToLower() == "or")
{
ConditionsOperators.Add(words[i].ToLower());
Conditions.Add(new Condition(conditionParts));
conditionParts = new List <string>();
}
else
{
conditionParts.Add(words[i]);
}
}
Conditions.Add(new Condition(conditionParts));
}
// do not have order by clause into query
if (orderIndex == -1)
{
return;
}
// if ... no continue for order by clause
if (words.Length - orderIndex < 2)
{
throw new SelectCommandParse($"Not found ORDER BY columns into query.");
}
// check BY after ORDER
if (words[orderIndex + 1].ToLower() != "by")
{
throw new SelectCommandParse($"Not found 'BY' after ORDER clause into query.");
}
// parse order by clause
for (int i = orderIndex + 2; i < words.Length; i++)
{
string item = words[i]
.Replace(",", "")
.Trim();
// checking double column
if (OrderBy.ContainsKey(item))
{
throw new SelectCommandParse($"Double column [{item}] for ORDER clause into query.");
}
bool asc = true;
if (i + 1 != words.Length)
{
string type = words[i + 1]
.Replace(",", "")
.ToLower()
.Trim();
if (type == "desc")
{
asc = false;
}
i++;
}
OrderBy.Add(item, asc);
}
}
