public Dictionary <string, object> NextInsertRecord()
{
var nextInsertRecord = new Dictionary <string, object>();
InsertValues.Add(nextInsertRecord);
return(nextInsertRecord);
}
public string BuildInsert(string targetTableName, InsertValues insertValues)
{
if (_type != QueryType.Insert)
{
return(null);
}
StringBuilder sb = new StringBuilder();
sb.Append(QueryType.Insert.ToString().ToUpper() + " INTO " + targetTableName.ToUpper() + " (");
Type type = typeof(InsertValues);
PropertyInfo[] info = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var item in info)
{
sb.Append(item.Name + ", ");
}
sb.Remove(sb.Length - 2, 2);
sb.Append(@") VALUES (");
foreach (var item in info)
{
if (item.PropertyType == typeof(string))
{
sb.Append("'" + item.GetValue(insertValues).ToString() + "', ");
}
else
{
sb.Append(item.GetValue(insertValues).ToString() + ", ");
}
}
sb.Remove(sb.Length - 2, 2);
sb.Append(@")");
return(sb.ToString());
}
//string Translit(string str)
//{
// string[] lat_up = { "A", "B", "V", "G", "D", "E", "Yo", "Zh", "Z", "I", "Y", "K", "L", "M", "N", "O", "P", "R", "S", "T", "U", "F", "Kh", "Ts", "Ch", "Sh", "Shch", "\"", "Y", "'", "E", "Yu", "Ya" };
// string[] lat_low = { "a", "b", "v", "g", "d", "e", "yo", "zh", "z", "i", "y", "k", "l", "m", "n", "o", "p", "r", "s", "t", "u", "f", "kh", "ts", "ch", "sh", "shch", "\"", "y", "'", "e", "yu", "ya" };
// string[] rus_up = { "А", "Б", "В", "Г", "Д", "Е", "Ё", "Ж", "З", "И", "Й", "К", "Л", "М", "Н", "О", "П", "Р", "С", "Т", "У", "Ф", "Х", "Ц", "Ч", "Ш", "Щ", "Ъ", "Ы", "Ь", "Э", "Ю", "Я" };
// string[] rus_low = { "а", "б", "в", "г", "д", "е", "ё", "ж", "з", "и", "й", "к", "л", "м", "н", "о", "п", "р", "с", "т", "у", "ф", "х", "ц", "ч", "ш", "щ", "ъ", "ы", "ь", "э", "ю", "я"};
// for (int i = 0; i <= 32; i++)
// {
// str = str.Replace(rus_up[i], lat_up[i]);
// str = str.Replace(rus_low[i], lat_low[i]);
// str = str.Replace('і', '_');
// str = str.Replace('І', '_');
// str = str.Replace('ї', '_');
// str = str.Replace('Ї', '_');
// str = str.Replace('є', '_');
// str = str.Replace('Є', '_');
// str = str.Replace(' ', '_');
// str = str.Replace(',', '_');
// str = str.Replace('\'', '_');
// }
// return str;
//}
//void ReplaceUkrSymbols()
//{
// DirectoryInfo di = new DirectoryInfo(_restructedPath);
// foreach (DirectoryInfo dirInfo in di.GetDirectories())
// {
// foreach (FileInfo file in dirInfo.GetFiles())
// {
// string translitStr = Translit(file.Name);
// File.Move(file.FullName, dirInfo.FullName + '\\' + translitStr);
// }
// }
//}
void InitDataForDB()
{
int counter = 0;
DirectoryInfo di = new DirectoryInfo(_restructedPath);
int counterAsFileName = 0;
string ext = "";
foreach (DirectoryInfo dirInfo in di.GetDirectories())
{
foreach (FileInfo file in dirInfo.GetFiles())
{
ext = file.Extension;
InsertValues insertValue = new InsertValues();
insertValue.Dogovor_ID = dirInfo.Name;
insertValue.comment1 = file.Name;
insertValue.comment2 = textBox1.Text + "/" + dirInfo.Name + "/" + (++counterAsFileName) + ext;
insertValue.comment3 = counterAsFileName + ext;
insertValue.comment4 = file.Length;
_listValuesToDB.Add(insertValue);
counter++;
string newName = counterAsFileName + ext;
File.Move(file.FullName, dirInfo.FullName + '\\' + newName);
}
}
int stop = counter;
}
public void AssignInsertFieldFromSource(string fieldName, string sourceTableName, string sourceFieldName, string op)
{
var lastInsertRecord = InsertValues.LastOrDefault() ?? NextInsertRecord();
var updateValue = $"{Adapter.Field(sourceTableName, sourceFieldName)}";
lastInsertRecord.Add(Adapter.Field(fieldName), updateValue);
}
public void insertToExcelString(string deviceID, string VIN, string errorCode, string description)
{
InsertValues insertString = new InsertValues();
insertString.VIN = VIN;
insertString.deviceID = deviceID;
insertString.Date = DateTime.Today.ToString();
insertString.felkod = errorCode;
insertString.Comments = description;
insertValues.Add(insertString);
}
/// <summary>
/// 克隆
/// </summary>
/// <returns>Insert插入语句</returns>
public override object Clone()
{
var sql = base.Clone() as InsertSqlStaMySQL;
if (InsertFields != null)
{
sql.InsertFields = InsertFields.Clone() as InsertFieldList;
}
if (InsertValues != null)
{
sql.InsertValues = InsertValues.Clone() as InsertValueList;
}
return(sql);
}
/// <summary>
/// Updates specified <see cref="fieldName"/> with assigning <see cref="value"/>
/// </summary>
/// <param name="fieldName"></param>
/// <param name="value"></param>
public void AssignInsertField(string fieldName, object value)
{
var updateValue = "";
switch (Operation)
{
case SqlOperations.Insert:
case SqlOperations.InsertFrom:
var paramId = NextParamId();
AddParameter(paramId, value);
updateValue = $"{Adapter.Parameter(paramId)}";
break;
default:
throw new NotSupportedException();
}
var lastInsertRecord = InsertValues.LastOrDefault() ?? NextInsertRecord();
lastInsertRecord.Add(Adapter.Field(fieldName), updateValue);
}
/// <summary>
/// Constructor
/// </summary>
public ConvertUpgradeFilesClass()
{
currentEdition = "";
nextEdition = "";
insertV = new InsertValues();
insertV.sectionNumber = 0;
insertV.executionOrder = 1000;
insertV .finalIndication= "'N'";
insertV.retryType = "1";
insertV.upgradId = "1";
startInsert = new List<string>();
startInsert.Add("create or replace and compile java source");
startInsert.Add("create or replace trigger");
startInsert.Add("create or replace package");
startInsert.Add("create or replace procedure");
startInsert.Add("create or replace function ");
startInsert.Add("declare");
startInsert.Add("begin");
retryCount = new Dictionary<string, string>();
retryCount.Add("drop", "2");
retryCount.Add("revoke", "2");
retryCount.Add("commit", "3");
retryCount.Add("grant", "3");
retryCount.Add("purge recyclebin", "3");
dbConnections = new Dictionary<string, string>();
dbConnections.Add("lynx", "lynx/dang3r");
dbConnections.Add("lynx_ne", "lynx_ne/f3line");
dbConnections.Add("lynx_dev_ne", "lynx_dev_ne/f3line");
dbConnections.Add("lynx_dev", "lynx_dev/f3line");
dbConnections.Add("lynx_olap", "lynx_olap/dang3r");
dbConnections.Add("lynx_analysis", "lynx_analysis/dang3r");
}
internal string ToParameterizedSqlInternal()
{
if (RawQueryParts.Count > 0)
{
AppendQueryParts(RawQueryParts);
}
if (InsertTable is not null)
{
Builder.Append("insert into ").Append(InsertTable);
if (InsertColumns.Count > 0)
{
Builder.Append(" (");
AppendQueryParts(InsertColumns);
Builder.Append(')');
}
bool isFirst = true;
foreach (var chunk in InsertValues.Chunk(InsertColumns.Count))
{
if (chunk.Count() != InsertColumns.Count && InsertColumns.Count != 0)
{
throw new InvalidOperationException("Wrong number of insert values.");
}
Builder.Append(isFirst ? " values (" : ", (");
AppendQueryParts(chunk);
Builder.Append(')');
isFirst = false;
}
}
if (UpdateTable is not null)
{
Builder.Append("update ").Append(UpdateTable).Append(" set ");
AppendQueryParts(UpdateValues);
}
if (DeleteTable is not null)
{
Builder.Append("delete from ").Append(DeleteTable);
}
if (SelectColumns.Count > 0 || SelectFrom.Count > 0)
{
Builder.Append("select ");
if (Distinct)
{
Builder.Append("distinct ");
}
AppendQueryParts(SelectColumns);
Builder.Append(" from ").AppendJoin(", ", SelectFrom);
}
if (Joins.Count > 0)
{
AppendQueryParts(Joins);
}
if (WhereForest.Count > 0)
{
AppendQueryParts(WhereForest);
}
if (GroupByColumns.Count > 0)
{
Builder.Append(" group by ");
AppendQueryParts(GroupByColumns);
}
if (HavingForest.Count > 0)
{
AppendQueryParts(HavingForest);
}
if (OrderByList.Count > 0)
{
Builder.Append(" order by ");
AppendQueryParts(OrderByList);
}
if (Limits.Count > 0)
{
AppendQueryParts(Limits);
}
return(Builder.ToString());
}
/// <summary>
/// Tries to parse a script node.
/// </summary>
/// <param name="Parser">Custom parser.</param>
/// <param name="Result">Parsed Script Node.</param>
/// <returns>If successful in parsing a script node.</returns>
public bool TryParse(ScriptParser Parser, out ScriptNode Result)
{
Result = null;
try
{
string s = Parser.NextToken().ToUpper();
if (s != "INTO")
{
return(false);
}
if (!SelectParser.TryParseSources(Parser, out SourceDefinition Source))
{
return(false);
}
switch (Parser.PeekNextToken().ToUpper())
{
case "(":
Parser.NextToken();
ScriptNode Node = Parser.ParseList();
if (!(Node is ElementList Columns))
{
Columns = new ElementList(new ScriptNode[] { Node }, Node.Start, Node.Length, Node.Expression);
}
if (Parser.NextToken() != ")")
{
return(false);
}
if (Parser.NextToken().ToUpper() != "VALUES")
{
return(false);
}
if (Parser.NextToken() != "(")
{
return(false);
}
Node = Parser.ParseList();
if (!(Node is ElementList Values))
{
Values = new ElementList(new ScriptNode[] { Node }, Node.Start, Node.Length, Node.Expression);
}
if (Values.Elements.Length != Columns.Elements.Length)
{
return(false);
}
if (Parser.NextToken() != ")")
{
return(false);
}
Result = new InsertValues(Source, Columns, Values, Parser.Start, Parser.Length, Parser.Expression);
return(true);
case "SELECT":
Node = Parser.ParseStatement();
if (!(Node is Select Select))
{
return(false);
}
Result = new InsertSelect(Source, Select, Parser.Start, Parser.Position, Parser.Expression);
return(true);
case "OBJECT":
case "OBJECTS":
Parser.NextToken();
Node = Parser.ParseList();
if (!(Node is ElementList Objects))
{
Objects = new ElementList(new ScriptNode[] { Node }, Node.Start, Node.Length, Node.Expression);
}
Result = new InsertObjects(Source, Objects, Parser.Start, Parser.Length, Parser.Expression);
return(true);
default:
return(false);
}
}
catch (Exception)
{
return(false);
}
}
public SQLiteCommand Build()
{
var sqlStatement = "";
var columnNamesString =
ColumnNames is not null && ColumnNames.Count > 0 ? string.Join(", ", ColumnNames) : "*";
switch (Type)
{
case StatementType.Insert:
{
var insertValuesString = $@"""{InsertValues[0]}""";
foreach (var value in InsertValues.Skip(1))
{
insertValuesString += $@", ""{value}""";
}
var insert = $"INSERT INTO {TableName} ({columnNamesString})";
var values = $"VALUES ({insertValuesString})";
sqlStatement = $"{insert} {values}";
break;
}
case StatementType.Select:
{
var select = $"SELECT {columnNamesString}";
var join = JoinParameters.Item1 is not null ? $" INNER JOIN {JoinParameters.Item1} ON {JoinParameters.Item2}={JoinParameters.Item3}" : null;
var from = $" FROM {TableName}{@join}";
var where = WhereCondition is not null ? $" WHERE {WhereCondition}" : null;
sqlStatement = $"{@select}{@from}{@where}";
break;
}
case StatementType.Update:
if (ColumnNames is null || UpdateValues is null || ColumnNames.Count != UpdateValues.Count)
{
return(null);
}
var pairs = new List <string>();
for (var index = 0; index < ColumnNames.Count; index++)
{
if (!string.IsNullOrWhiteSpace(UpdateValues[index]) &&
!string.IsNullOrWhiteSpace(ColumnNames[index]))
{
pairs.Add($@"{ColumnNames[index]} = ""{UpdateValues[index]}""");
}
}
var set = $" SET {string.Join(", ", pairs)}";
var updateCondition = $" WHERE {WhereCondition}"; //will crash if no condition is given as this is too dangerous to be done without it
sqlStatement = $"UPDATE {TableName}{set}{updateCondition}";
break;
case StatementType.Delete:
sqlStatement = $"DELETE FROM {TableName} WHERE {WhereCondition}";
break;
case StatementType.Create:
if (ColumnNames == null || ColumnNames.Count != ColumnTypes.Count)
{
return(null);
}
pairs = new List <string>();
for (var index = 0; index < ColumnNames.Count; index++)
{
pairs.Add($"{ColumnNames[index]} {ColumnTypes[index]}");
}
pairs.AddRange(Constraints);
var columns = string.Join(", ", pairs);
sqlStatement = $"CREATE TABLE {TableName} ({columns})";
break;
}
var sqlCommand = new SQLiteCommand(sqlStatement);
AddParameters(sqlCommand);
ResetFields();
return(sqlCommand);
}
public ISqlBuilder Insert(List <object> values)
{
Type = StatementType.Insert;
values.ForEach(v => InsertValues.Add(StoreAndGetReference(v)));
return(this);
}