public bool parse(string queryString)
{
operationType = SqlOperationType.UNDEFINED;
query = queryString.Trim();
parsedQueryString = "";
tableName = "";
lockQueryString = "";
if (query.EndsWith(";"))
query = query.Substring(0, query.Length - 1);
if (selectRegExp.Matches(query).Count > 0)
parseSelect();
else if (insertRegExp.Matches(query).Count > 0)
parseInsert();
else if (updateRegExp.Matches(query).Count > 0)
parseUpdate();
else if (deleteRegExp.Matches(query).Count > 0)
parseDelete();
else if (createRegExp.Matches(query).Count > 0)
parseCreate();
else if (lockRegExp.Matches(query).Count > 0)
parseLock();
else if (unlockRegExp.Matches(query).Count > 0)
parseUnlock();
else
return false;
Logger.getInstance().log("[" + tableName + "] " + parsedQueryString +
"\t" + lockQueryString, LOGGING_NAME, Logger.Level.INFO);
return true;
}
public String getQueryText(DataTable dataTable, string tableName, string tableAlias
, int nColEng, int nColKor, SqlOperationType strQueryKinds, bool checkComment)
{
ConvertGridToIBatisQuery convert = new ConvertGridToIBatisQuery();
return convert.getIBatisQuery(dataTable, strQueryKinds, tableName
, tableAlias, nColEng, nColKor, checkComment);
}
public String getQueryText(DataTable dataTable, string tableName, string tableAlias
, int nColEng, int nColKor, SqlOperationType strQueryKinds, bool checkComment)
{
ConvertGridToIBatisQuery convert = new ConvertGridToIBatisQuery();
return(convert.getIBatisQuery(dataTable, strQueryKinds, tableName
, tableAlias, nColEng, nColKor, checkComment));
}
private SqlGenerator(string tableName, SqlOperationType type, BaseForm form, BaseVM view, int id,
SqlQueryFormatter sqlQuery = null, List <string> condition = null, List <string> orderColumns = null)
{
this.OperationType = type;
this.condition = condition;
this.orderColumns = orderColumns;
this.viewModel = view;
this.rowId = id;
if (type == SqlOperationType.GetById && id <= 0)
{
throw new ArgumentException("Id must provide to get the data");
}
this.sqlQuery = sqlQuery ?? new SqlQueryFormatter();
var sqlParamsList = GetProperties(form);
Parameters = PrepareSqlParams(sqlParamsList);
Statement = PrepareSqlStatement(tableName, type, sqlParamsList);
}
public string getIBatisQuery(DataTable dataTable
, SqlOperationType queryKinds
, string tableName
, string tableAlias
, int nMaxColEngLength
, int nMaxColKorLength
, bool checkedComment)
{
string strResult = null;
MakeQuery msq = null;
CombineQuery csq = null;
if (queryKinds == SqlOperationType.Select)
{
msq = new MakeSelectQuery(tableAlias, nMaxColEngLength, nMaxColKorLength, checkedComment);
csq = new CombineSelectQuery(msq, dataTable, tableName);
}
else if (queryKinds == SqlOperationType.Insert)
{
msq = new MakeInsertQuery(nMaxColEngLength, nMaxColKorLength, checkedComment);
csq = new CombineInsertQuery(msq, dataTable, tableName);
}
else if (queryKinds == SqlOperationType.Update)
{
msq = new MakeUpdateQuery(nMaxColEngLength, nMaxColKorLength, checkedComment);
csq = new CombineUpdateQuery(msq, dataTable, tableName);
}
else if (queryKinds == SqlOperationType.Delete)
{
msq = new MakeDeleteQuery(nMaxColEngLength, nMaxColKorLength, checkedComment);
csq = new CombineDeleteQuery(msq, dataTable, tableName);
}
IBatisQueryFactory factory = new IBatisQueryFactory(msq, csq);
strResult = factory.getQuery();
return(strResult);
}
public string makeQuery(string filedName, string filedComment, SqlOperationType sqlOperationType, bool checkedComment)
{
string strParam1 = null;
string strParam2 = null;
if (sqlOperationType == SqlOperationType.Select)
{
strParam1 = "AS";
strParam2 = "\"";
}
else if (sqlOperationType == SqlOperationType.Where)
{
strParam1 = " =";
strParam2 = "#";
}
if (filedName.Length >= nMaxColEngLength)
{
throw new MyException("컬럼길이(" + filedName.Length + ")보다 입력넓이가 작거나 같습니다.");
}
String strQuery = null;
if (strTableAlias != null && strTableAlias.Length != 0)
{
strQuery += strTableAlias + ".";
}
String str1 = (strQuery + filedName).PadRight(nMaxColEngLength) + strParam1 + " " + strParam2 + filedName + strParam2;
if (checkedComment)
{
return(str1);
}
else
{
return(makeComment(filedComment, str1));
}
}
public string makeQuery(string filedName, string filedComment, SqlOperationType sqlOperationType, bool checkedComment)
{
string strParam1 = null;
string strParam2 = null;
if (sqlOperationType == SqlOperationType.Select)
{
strParam1 = "AS";
strParam2 = "\"";
}
else if (sqlOperationType == SqlOperationType.Where)
{
strParam1 = " =";
strParam2 = "#";
}
if (filedName.Length >= nMaxColEngLength)
{
throw new MyException("컬럼길이(" + filedName.Length + ")보다 입력넓이가 작거나 같습니다.");
}
String strQuery = null;
if (strTableAlias != null && strTableAlias.Length != 0)
{
strQuery += strTableAlias + ".";
}
String str1 = (strQuery + filedName).PadRight(nMaxColEngLength) + strParam1 + " " + strParam2 + filedName + strParam2;
if (checkedComment)
{
return str1;
}
else
{
return makeComment(filedComment, str1);
}
}
public string getIBatisQuery(DataTable dataTable
, SqlOperationType queryKinds
, string tableName
, string tableAlias
, int nMaxColEngLength
, int nMaxColKorLength
, bool checkedComment)
{
string strResult = null;
MakeQuery msq = null;
CombineQuery csq = null;
if(queryKinds == SqlOperationType.Select)
{
msq = new MakeSelectQuery(tableAlias, nMaxColEngLength, nMaxColKorLength, checkedComment);
csq = new CombineSelectQuery(msq, dataTable, tableName);
}
else if(queryKinds == SqlOperationType.Insert)
{
msq = new MakeInsertQuery(nMaxColEngLength, nMaxColKorLength, checkedComment);
csq = new CombineInsertQuery(msq, dataTable, tableName);
}
else if (queryKinds == SqlOperationType.Update)
{
msq = new MakeUpdateQuery(nMaxColEngLength, nMaxColKorLength, checkedComment);
csq = new CombineUpdateQuery(msq, dataTable, tableName);
}
else if (queryKinds == SqlOperationType.Delete)
{
msq = new MakeDeleteQuery(nMaxColEngLength, nMaxColKorLength, checkedComment);
csq = new CombineDeleteQuery(msq, dataTable, tableName);
}
IBatisQueryFactory factory = new IBatisQueryFactory(msq, csq);
strResult = factory.getQuery();
return strResult;
}
private string PrepareSqlStatement(string tableName, SqlOperationType type, List <KeyValuePair <string, object> > sqlParamsList)
{
var sqlStatement = "";
switch (type)
{
case SqlOperationType.Insert:
sqlStatement = PrepareInsertSqlStatement(tableName, sqlParamsList);
break;
case SqlOperationType.Update:
sqlStatement = PrepareUpdateSqlStatement(tableName, sqlParamsList);
break;
case SqlOperationType.Delete:
sqlStatement = PrepareDeleteSqlStatement(tableName, sqlParamsList);
break;
case SqlOperationType.Get:
sqlStatement = PrepareGetSqlStatement(tableName, sqlParamsList);
break;
case SqlOperationType.GetById:
sqlStatement = PrepareGetByIdSqlStatement(tableName, sqlParamsList);
break;
case SqlOperationType.CheckUniqueName:
sqlStatement = PrepareUniqueNameSqlStatement(tableName, sqlParamsList);
break;
case SqlOperationType.StoredProc:
sqlStatement = tableName;
break;
}
return(sqlStatement);
}
public static void AddIfNotExists(this List <TableParsingResult> list, string tableName, SqlOperationType operationType, string alias)
{
if (!list.Any(x => x.TableName == tableName.ToLower() && x.OperationType == operationType))
{
list.Add(new TableParsingResult()
{
TableName = tableName.ToLower(),
Alias = alias,
OperationType = operationType
});
}
}
public static void AddIfNotExists(this ParserResults results, string tableName, SqlOperationType sqlOperationType, string alias)
{
results.TableParsingResults.AddIfNotExists(tableName, sqlOperationType, alias);
}
private string makeQuery(string filedName, string filedComment, SqlOperationType sqlOperationType)
{
MakeCommonQuery comm = new MakeCommonQuery(strTableAlias, nMaxColEngLength, nMaxColKorLength);
return(comm.makeQuery(filedName, filedComment, sqlOperationType, checkedComment));
}
void parseInsert()
{
operationType = SqlOperationType.INSERT;
MatchCollection matchList = insertRegExp.Matches(query);
if (matchList.Count > 0)
{
Match match = matchList[0];
tableName = match.Groups[1].Value.Trim();
String values = match.Groups[2].Value.Trim();
values = values.Remove(0,1);
values = values.Insert(0, "(0,");
parsedQueryString = "INSERT INTO " + tableName+ " VALUES" + values;
}
}
protected virtual TVm ExcecuteWithData(TForm form, SqlOperationType type, List <string> condition = null)
{
var id = Excecute(form, type, condition);
return(GetById(id));
}
void parseSelect()
{
operationType = SqlOperationType.SELECT;
MatchCollection matchList = selectRegExp.Matches(query);
if (matchList.Count > 0)
{
Match match = matchList[0];
String fields = match.Groups[1].Value.Trim();
tableName = match.Groups[2].Value.Trim();
parsedQueryString = "SELECT " + fields + " FROM " + tableName;
}
}
void parseUpdate()
{
operationType = SqlOperationType.UPDATE;
MatchCollection matchList = updateRegExp.Matches(query);
if (matchList.Count > 0)
{
Match match = matchList[0];
tableName = match.Groups[1].Value.Trim();
String set;
String where;
String rest = match.Groups[2].Value.Trim();
Regex withWhereRegExp = new Regex("(.*)WHERE(.*)",
RegexOptions.IgnoreCase);
MatchCollection matchList2 = withWhereRegExp.Matches(rest);
if (matchList2.Count > 0)
{
Match match2 = matchList2[0];
set = match2.Groups[1].Value.Trim();
where = match2.Groups[2].Value.Trim();
where = where.Insert(0, " WHERE (");
where = where.Insert(where.Length, ") AND _lock = 0");
}
else
{
where = " WHERE _lock = 0";
set = rest;
}
parsedQueryString = "UPDATE " + tableName + " SET " + set + where;
where = where.Remove(where.Length - 1);
where = where.Insert(where.Length, "1");
lockQueryString = "SELECT * FROM " + tableName + where;
}
}
//public SqlParameterCollection SqlParams { get; set; }
//tableName or StoredProcName
public SqlGenerator(string tableName, SqlOperationType type, BaseForm form,
SqlQueryFormatter sqlQuery = null, List <string> condition = null)
: this(tableName, type, form, null, 0, sqlQuery, condition)
{
}
/// <summary>
/// 生成SQL语句
/// 说明：
/// SELECT 子句的 WHERE 子句不进行处理（需要自行拼接）
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity"></param>
/// <param name="sqlOperationType"></param>
/// <returns></returns>
protected String GenerateSql <TEntity>(TEntity entity, SqlOperationType sqlOperationType) where TEntity : class
{
char spaceCharacter = '?'; //这里是mysql所以直接使用?
StringBuilder sbSql = new StringBuilder();
String tableName = DbTableMapping.GetTableName(entity);
Dictionary <String, String> columnMappingDict = DbTableColumnMapping.GetColumnMapping(entity.GetType());
Dictionary <String, String> primaryKey = null;
if (null != columnMappingDict && columnMappingDict.Count > 0)
{
switch (sqlOperationType)
{
case SqlOperationType.INSERT:
sbSql.Append($"INSERT INTO {tableName} ");
StringBuilder sbColumns = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
foreach (KeyValuePair <String, String> keyValuePair in columnMappingDict)
{
sbColumns.Append($"`{keyValuePair.Value}`,");
sbValues.Append($"{spaceCharacter}{keyValuePair.Key},");
}
sbSql.Append($"({sbColumns.ToString().Trim(',')})");
sbSql.Append(" VALUES ");
sbSql.Append($"( {sbValues.ToString().Trim(',')} );");
break;
case SqlOperationType.DELETE:
primaryKey = DbTablePrimaryKeyMapping.GetPrimaryKey(entity.GetType());
if (null != primaryKey)
{
foreach (KeyValuePair <String, String> keyValuePair in primaryKey)
{
sbSql.Append($"DELETE FROM {tableName} WHERE `{keyValuePair.Value}` = {spaceCharacter}{keyValuePair.Key};");
break;
}
}
break;
case SqlOperationType.UPDATE:
primaryKey = DbTablePrimaryKeyMapping.GetPrimaryKey(entity.GetType());
if (null != primaryKey)
{
foreach (KeyValuePair <String, String> keyValuePair in primaryKey)
{
StringBuilder sbSet = new StringBuilder();
foreach (KeyValuePair <String, String> columnMap in columnMappingDict)
{
if (keyValuePair.Key.ToLower() != columnMap.Key.ToLower())
{
sbSet.Append($" `{columnMap.Value}` = {spaceCharacter}{columnMap.Key},");
}
}
sbSql.Append($"UPDATE {tableName} " +
$"SET {sbSet.ToString().Trim(',')} " +
$"WHERE {keyValuePair.Value} = {spaceCharacter}{keyValuePair.Key};");
break;
}
}
break;
default: //select
StringBuilder columns = new StringBuilder();
sbSql.Append("SELECT ");
foreach (KeyValuePair <String, String> keyValuePair in columnMappingDict)
{
columns.Append($" `{keyValuePair.Value}` AS '{keyValuePair.Key}', ");
}
sbSql.Append($" FROM {tableName} ");
break;
}
}
return(sbSql.ToString());
}
protected virtual int Excecute(BaseForm form, SqlOperationType type, List <string> condition = null)
{
var sqlGenerator = new SqlGenerator(dbTableName, type, form, sqlQuery, condition);
return(Excecute(form, sqlGenerator));
}
private string makeQuery(string filedName, string filedComment, SqlOperationType sqlOperationType)
{
MakeCommonQuery comm = new MakeCommonQuery(strTableAlias, nMaxColEngLength, nMaxColKorLength);
return comm.makeQuery(filedName, filedComment, sqlOperationType, checkedComment);
}
private void getQuery()
{
string name = cboTable.SelectedItem.ToString();
string sqlId = string.Empty;
string entity = name;
string parameterClass = name + "Query";
string resultClass = name + "Dto";
StringBuilder bulider = new StringBuilder();
string nodeAppend = "";
string sql = listBoxQueryKindCd.SelectedItem.ToString();
SqlOperationType type = SqlOperationType.Select;
if (sql.Equals("select", StringComparison.OrdinalIgnoreCase))
{
sqlId = "q" + name;
string select = "<select id=\"" + sqlId + "\" parameterClass=\"" + parameterClass + "\" resultClass=\"" + resultClass + "\"> ";
bulider.Append(select);
bulider.Append("\r\n");
nodeAppend = @"</select>";
type = SqlOperationType.Select;
}
else if (sql.Equals("insert", StringComparison.OrdinalIgnoreCase))
{
sqlId = "i" + name;
string insert = "<insert id=\"" + sqlId + "\" parameterClass=\"" + entity + "\" " + "> ";
bulider.Append(insert);
bulider.Append("\r\n");
nodeAppend = @"</insert>";
type = SqlOperationType.Insert;
}
else if (sql.Equals("update", StringComparison.OrdinalIgnoreCase))
{
sqlId = "u" + name;
string update = "<update id=\"" + sqlId + "\" parameterClass=\"" + "string" + "\"> ";
bulider.Append(update);
bulider.Append("\r\n");
nodeAppend = @"</update>";
type = SqlOperationType.Update;
}
else if (sql.Equals("delete", StringComparison.OrdinalIgnoreCase))
{
sqlId = "d" + name;
string delete = "<delete id=\"" + sqlId + "\" parameterClass=\"" + "string" + "\"> ";
bulider.Append(delete);
bulider.Append("\r\n");
nodeAppend = @"</delete>";
type = SqlOperationType.Delete;
}
try
{
String result = gridClazz.getQueryText(
(DataTable)dgvTable.DataSource,
cboDbName.SelectedItem.ToString() + ".dbo." + cboTable.SelectedItem.ToString(),
txtTableAlias.Text,
int.Parse(txtNColEng.Text),
int.Parse(txtNColKor.Text),
type,
chkComment.Checked);
bulider.Append(result);
bulider.Append("\r\n");
bulider.Append(nodeAppend);
rtbResultSql.Text = bulider.ToString();
}
catch (MyException my)
{
my.showMessage();
}
}
void parseCreate()
{
operationType = SqlOperationType.CREATE;
MatchCollection matchList = createRegExp.Matches(query);
if (matchList.Count > 0)
{
Match match = matchList[0];
tableName = match.Groups[1].Value.Trim();
String fields = match.Groups[2].Value.Trim();
fields = fields.Remove(0, 1);
fields = fields.Insert(0, "( _lock BOOL, ");
parsedQueryString = "CREATE TABLE " + tableName + fields;
}
}
