SqlToEfStruct CreateDelete(string tableName, string constrLine, SqlToEfStruct deleteStruct)
{
string EFBlock = "using (ApplicationContext db = new ApplicationContext())\n{\n\t";
EFBlock += "db." + tableName + ".RemoveRange(db." + tableName;
if (constrLine.Length > 0)
{
EFBlock += ".Where(x => ";
string[] constrns = constrLine.Split(new[] { " AND ", " OR " }, StringSplitOptions.None);
foreach (var i in constrns)
{
string pattern = @"^[^=<>]+";
Regex regex = new Regex(pattern);
foreach (Match match in Regex.Matches(i, pattern))
{
string newColumn = "x." + match.Value.Replace(" ", "") + " ";
constrLine = constrLine.Replace(match.Value, newColumn);
}
}
constrLine = constrLine.Replace("=", "==").Replace("AND", "&&").Replace("OR", "||");
EFBlock += constrLine + ")";
}
EFBlock += ");\n\tdb.SaveChanges();\n}";
deleteStruct.EfBlock = EFBlock;
return(deleteStruct);
}
//Разделяем на элементы запрос DELETE
public SqlToEfStruct SplitDelete(string sqlQuery)
{
SqlToEfStruct deleteStruct = new SqlToEfStruct();
deleteStruct.replaceLine = sqlQuery;
string tableName = "";
//убираем лишние проблемы
string pattern = @"\s+";
string target = " ";
Regex regex = new Regex(pattern);
sqlQuery = regex.Replace(sqlQuery, target);
pattern = @"^(.)+(?ixn)DELETE[ ]+((FROM[ ])|())";
target = " ";
regex = new Regex(pattern);
sqlQuery = regex.Replace(sqlQuery, target);
deleteStruct.sqlQuery = "DELETE " + sqlQuery.Trim();
//получаем имя таблицы
pattern = @"(?ixn)^[ ]*[^ ]+";
regex = new Regex(pattern);
foreach (Match match in Regex.Matches(sqlQuery, pattern))
{
tableName = match.Value.Replace(" ", "");
}
sqlQuery = regex.Replace(sqlQuery, target);
pattern = @"^[ ]*(?ixn)WHERE";
regex = new Regex(pattern);
string constrLine = "";
if (regex.IsMatch(sqlQuery))
{
sqlQuery = regex.Replace(sqlQuery, target);
constrLine = sqlQuery;
}
deleteStruct = CreateDelete(tableName, constrLine, deleteStruct);
return(deleteStruct);
}
SqlToEfStruct CreateSelect(string columnsLines, string tablesLines, string constrainsLine, string orderByLine, SqlToEfStruct selectStruct)
{
string EFBlock = "using (ApplicationContext db = new ApplicationContext())\n{\n\tvar result = ";
//колонки
string columnCode = "select new\n\t{\n\t\t";
if (columnsLines.Trim() == "*")
{
columnCode += "Specify all columns included in the table\n}";
}
else
{
string[] columns = columnsLines.Split(',');
Dictionary <string, string> columnsPsevdo = new Dictionary <string, string>();
for (int i = 0; i < columns.Length; i++)
{
string[] a = columns[i].ToLower().Replace("as", "").Replace(" ", " ").Trim().Split(' ');
if (a.Length == 2)
{
columnsPsevdo.Add(a[0], a[1]);
}
else
{
columnsPsevdo.Add(a[0], a[0]);
}
}
foreach (var i in columnsPsevdo)
{
if (i.Key.Contains('.'))
{
columnCode += i.Value + " = " + i.Key + "\n\t\t";
}
else
{
columnCode += i.Value + " = t." + i.Key + "\n\t\t";
}
}
columnCode = columnCode.Trim('\t') + "\t}";
}
//условия
string whereCode = "";
if (constrainsLine.Length > 0)
{
whereCode += "where ";
string[] constrns = constrainsLine.Split(new[] { " AND ", " OR " }, StringSplitOptions.None);
foreach (var i in constrns)
{
string pattern = @"^[^=<>]+";
Regex regex = new Regex(pattern);
foreach (Match match in Regex.Matches(i, pattern))
{
if (!match.Value.Contains("."))
{
string newColumn = "t." + match.Value.Replace(" ", "") + " ";
constrainsLine = constrainsLine.Replace(match.Value, newColumn);
}
}
}
constrainsLine = constrainsLine.Replace("=", "==").Replace("AND", "&&").Replace("OR", "||");
whereCode += constrainsLine;
}
//сортировка
string orderByCode = "";
if (orderByLine.Length > 0)
{
orderByCode += "orderby ";
string[] orderBys = orderByLine.Split(',');
for (int i = 0; i < orderBys.Length; i++)
{
if (i > 0)
{
orderByCode += ", ";
}
string[] a = orderBys[i].Trim().Split(' ');
if (!a[0].Contains("."))
{
orderByCode += "t.";
}
orderByCode += a[0];
if (a.Length > 1)
{
if (a[1].ToUpper() == "DESC")
{
orderByCode += " descending";
}
}
}
}
//таблицы
string fromCode = "from ";
string[] tableInfo = tablesLines.Split(new[] { " JOIN ", ",", " INNER JOIN " }, StringSplitOptions.None);
for (int i = 0; i < tableInfo.Length; i++)
{
tableInfo[i] = tableInfo[i].Trim();
string pattern = @"^[^ ]+";
Regex regex = new Regex(pattern);
string tableName = "";
foreach (Match match in Regex.Matches(tableInfo[i], pattern))
{
tableName = match.Value;
}
tableInfo[i] = regex.Replace(tableInfo[i], "").Trim();
string psevd = "";
if (tableInfo[i].ToUpper().Contains("ON "))
{
pattern = @"^.*(?ixn)[ ]ON[ ]";
regex = new Regex(pattern);
foreach (Match match in Regex.Matches(tableInfo[i], pattern))
{
psevd = match.Value.ToLower().Replace("as ", "").Replace(" on ", "");
}
tableInfo[i] = regex.Replace(tableInfo[i], "").Trim();
}
else
{
psevd = tableInfo[i];
}
if (psevd.Trim().Length == 0)
{
psevd = tableName;
}
if (i == 0)
{
fromCode += psevd + " in db." + tableName;
}
else
{
fromCode += " join " + psevd + " in db." + tableName + " on " + tableInfo[i].Replace("=", "equals");
}
}
EFBlock += fromCode + "\n\t" + whereCode + "\n\t" + orderByCode + "\n\t" + columnCode + ";\n}";
selectStruct.EfBlock = EFBlock;
return(selectStruct);
}
//Разделяем на элементы запрос INSERT INTO
public SqlToEfStruct SplitInto(string sqlQuery)
{
SqlToEfStruct intoStruct = new SqlToEfStruct();
intoStruct.replaceLine = sqlQuery;
string tableName = "";
string[] columns = new string[0];
string[] values = new string[0];
//убираем лишние проблемы
string pattern = @"\s+";
string target = " ";
Regex regex = new Regex(pattern);
sqlQuery = regex.Replace(sqlQuery, target);
pattern = @"^(.)+(?ixn)INSERT[ ]INTO[ ]";
target = " ";
regex = new Regex(pattern);
sqlQuery = regex.Replace(sqlQuery, target);
intoStruct.sqlQuery = "INSERT INTO " + sqlQuery.Trim();
//получаем имя таблицы
pattern = @"(?ixn)^[ ]*[^ (]+([ ]|[(])";
regex = new Regex(pattern);
foreach (Match match in Regex.Matches(sqlQuery, pattern))
{
tableName = match.Value.Replace(" ", "").Replace("(", "");
}
sqlQuery = regex.Replace(sqlQuery, target);
//получаем имена столбцов
pattern = @"(?ixn)^[ ]*VALUES[ ]*[(]";
regex = new Regex(pattern);
if (!regex.IsMatch(sqlQuery))
{
pattern = @"^[ ]*[^)]+[)]";
regex = new Regex(pattern);
foreach (Match match in Regex.Matches(sqlQuery, pattern))
{
columns = match.Value.Replace(" ", "").Replace(")", "").Replace("(", "").Split(',');
}
sqlQuery = regex.Replace(sqlQuery, target);
}
//получаем значения
pattern = @"(?ixn)^[ ]*[^(]+[(]";
regex = new Regex(pattern);
sqlQuery = regex.Replace(sqlQuery, target);
pattern = @"^[ ]*[^)]+[)]";
regex = new Regex(pattern);
foreach (Match match in Regex.Matches(sqlQuery, pattern))
{
values = match.Value.Replace(" ", "").Replace(")", "").Split(',');
}
sqlQuery = regex.Replace(sqlQuery, target);
intoStruct = CreateInto(tableName, columns, values, intoStruct);
return(intoStruct);
}
public SqlToEfStruct SplitSelect(string sqlQuery)
{
SqlToEfStruct selectStruct = new SqlToEfStruct();
selectStruct.replaceLine = sqlQuery;
bool whereBool = false;
bool orderByBool = false;
if (sqlQuery.ToUpper().Contains(" WHERE "))
{
whereBool = true;
}
if (sqlQuery.ToUpper().Contains(" ORDER BY "))
{
orderByBool = true;
}
//убираем лишние проблемы
string pattern = @"\s+";
string target = " ";
Regex regex = new Regex(pattern);
sqlQuery = regex.Replace(sqlQuery, target);
pattern = @"^(.)+(?ixn)SELECT[ ]*";
target = " ";
regex = new Regex(pattern);
sqlQuery = regex.Replace(sqlQuery, target);
selectStruct.sqlQuery = "SELECT " + sqlQuery.Trim();
//получаем название столбцов
pattern = @"^.*(?ixn)FROM";
string columnsLine = "";
regex = new Regex(pattern);
foreach (Match match in Regex.Matches(sqlQuery, pattern))
{
columnsLine = match.Value;
}
sqlQuery = regex.Replace(sqlQuery, target);
pattern = @"(?ixn)FROM$";
regex = new Regex(pattern);
columnsLine = regex.Replace(columnsLine, target);
//получаем название таблиц
if (whereBool)
{
pattern = @"^.*(?ixn)((WHERE))";
}
else
{
if (orderByBool)
{
pattern = @"^.*(?ixn)((ORDER[ ]BY))";
}
else
{
pattern = @"^.*($)";
}
}
string tablesLine = "";
regex = new Regex(pattern);
foreach (Match match in Regex.Matches(sqlQuery, pattern))
{
tablesLine = match.Value;
}
sqlQuery = regex.Replace(sqlQuery, target);
pattern = @"(?ixn)((WHERE)|(ORDER[ ]BY))$";
regex = new Regex(pattern);
tablesLine = regex.Replace(tablesLine, target);
//получаем условия
string constrainsLine = "";
if (whereBool)
{
if (orderByBool)
{
pattern = @"^.*(?ixn)((ORDER[ ]BY))";
}
else
{
pattern = @"^.*($)";
}
regex = new Regex(pattern);
foreach (Match match in Regex.Matches(sqlQuery, pattern))
{
constrainsLine = match.Value;
}
sqlQuery = regex.Replace(sqlQuery, target);
pattern = @"(?ixn)ORDER[ ]BY$";
regex = new Regex(pattern);
constrainsLine = regex.Replace(constrainsLine, target);
}
string orderByLine = sqlQuery.Trim();
selectStruct = CreateSelect(columnsLine, tablesLine, constrainsLine, orderByLine, selectStruct);
return(selectStruct);
}
SqlToEfStruct CreateUpdate(string tableName, List <string> columns, List <string> values, string constrLine, SqlToEfStruct updateStruct)
{
string EFBlock = "using (ApplicationContext db = new ApplicationContext())\n{\n\t";
EFBlock += "db." + tableName;
if (constrLine.Length > 0)
{
EFBlock += ".Where(x => ";
Dictionary <string, string> replaceStr = new Dictionary <string, string>();
string[] constrns = constrLine.Split(new[] { " AND ", " OR " }, StringSplitOptions.None);
foreach (var i in constrns)
{
string pattern = @"^[^=<>]+";
Regex regex = new Regex(pattern);
foreach (Match match in Regex.Matches(i, pattern))
{
string newColumn = "x." + match.Value.Replace(" ", "") + " ";
if (!replaceStr.ContainsKey(match.Value.Replace(" ", "")))
{
replaceStr.Add(match.Value.Replace(" ", ""), newColumn);
}
}
}
foreach (var i in replaceStr)
{
constrLine = constrLine.Replace(i.Key, i.Value);
}
constrLine = constrLine.Replace("=", "==").Replace("AND", "&&").Replace("OR", "||");
EFBlock += constrLine + ")";
}
EFBlock += ".ToList().ForEach(a =>\n\t{\n";
for (int i = 0; i < values.Count; i++)
{
EFBlock += "\t\ta." + columns[i].Replace(" ", "") + " = " + values[i].Replace(@"'", @"""") + ";\n";
}
EFBlock += "\t});\n\tdb.SaveChanges();\n}";
updateStruct.EfBlock = EFBlock;
return(updateStruct);
}
//Разделяем на элементы запрос UPDATE
public SqlToEfStruct SplitUpdate(string sqlQuery)
{
SqlToEfStruct updateStruct = new SqlToEfStruct();
updateStruct.replaceLine = sqlQuery;
string tableName = "";
List <string> columns = new List <string>();
List <string> values = new List <string>();
//убираем лишние проблемы
string pattern = @"\s+";
string target = " ";
Regex regex = new Regex(pattern);
sqlQuery = regex.Replace(sqlQuery, target);
pattern = @"^(.)+(?ixn)UPDATE[ ]";
target = " ";
regex = new Regex(pattern);
sqlQuery = regex.Replace(sqlQuery, target);
updateStruct.sqlQuery = "UPDATE " + sqlQuery.Trim();
//получаем имя таблицы
pattern = @"(?ixn)^[ ]*[^ ]+";
regex = new Regex(pattern);
foreach (Match match in Regex.Matches(sqlQuery, pattern))
{
tableName = match.Value.Replace(" ", "");
}
sqlQuery = regex.Replace(sqlQuery, target);
pattern = @"(?ixn)^[ ]*SET";
target = " ";
regex = new Regex(pattern);
sqlQuery = regex.Replace(sqlQuery, target);
bool flag = true;
bool flagWhere = false;
if (sqlQuery.ToUpper().Contains(" WHERE "))
{
pattern = @"^[^=]+[=](([^,]*[,])|(.*(?ixn)((['][ ]*WHERE)|([ ]+WHERE))))";
flagWhere = true;
}
else
{
pattern = @"^[^=]+[=](([^,]*[,])|(.*$)|(.* ""))";
}
regex = new Regex(pattern);
while (flag)
{
flag = false;
foreach (Match match in Regex.Matches(sqlQuery, pattern))
{
flag = true;
string line = match.Value;
string pat = @"[ ]*(((?ixn)WHERE$)|([,]$))";
string tar = "";
Regex reg = new Regex(pat);
line = reg.Replace(line, tar);
string[] colVal = line.Split('=');
columns.Add(colVal[0]);
values.Add(colVal[1]);
}
sqlQuery = regex.Replace(sqlQuery, target);
}
string constrLine = "";
if (flagWhere)
{
constrLine = sqlQuery;
}
updateStruct = CreateUpdate(tableName, columns, values, constrLine, updateStruct);
return(updateStruct);
}
//Создание аналога запроса INSERT INTO, с помощью EF
SqlToEfStruct CreateInto(string tableName, string[] columns, string[] values, SqlToEfStruct intoStruct)
{
string EFBlock = "using (ApplicationContext db = new ApplicationContext())\n{\n\t";
EFBlock += tableName + " str = new " + tableName + @"{ ";
if (columns.Length == 0)//генерируем имена столбцов если их не было в запросе
{
columns = new string[values.Length];
for (int i = 0; i < values.Length; i++)
{
columns[i] = "<column" + i.ToString() + ">";
}
}
for (int i = 0; i < values.Length; i++)
{
if (i < values.Length - 1)
{
EFBlock += columns[i] + " = " + values[i].Replace(@"'", @"""") + ", ";
}
else
{
EFBlock += columns[i] + " = " + values[i].Replace(@"'", @"""") + "}\n\t";
}
}
EFBlock += "db.Test.Add(t1);\n\tdb.SaveChanges();\n}";
intoStruct.EfBlock = EFBlock;
return(intoStruct);
}
