private string BuildInsertOrUpdateQuery()
{
for (int i = 0; i < _items.Count; ++i)
{
bool notEmpty = false;
SqlItem item = _items[i];
StringBuilder tableNames = new StringBuilder(1024);
StringBuilder tableValues = new StringBuilder(1024);
StringBuilder tableUpdates = new StringBuilder(1024);
tableNames.AppendFormat("INSERT INTO `{0}` ({1}, ", _tableName, _keyName);
tableValues.AppendFormat(") VALUES ({0}, ", item.Key);
tableUpdates.AppendFormat(") ON DUPLICATE KEY UPDATE ");
for (int j = 0; j < item.Count; ++j)
{
if (!AllowNullValue && string.IsNullOrWhiteSpace(item[j]))
{
continue;
}
tableNames.AppendFormat(NumberFormatInfo.InvariantInfo, "{0}, ", _fields[j]);
tableValues.AppendFormat(NumberFormatInfo.InvariantInfo, "'{0}', ", item[j].Replace("'", "''"));
tableUpdates.AppendFormat(NumberFormatInfo.InvariantInfo, "{0} = VALUES({1}), ", _fields[j], _fields[j]);
notEmpty = true;
}
if (notEmpty)
{
tableNames.Remove(tableNames.Length - 2, 2);
tableValues.Remove(tableValues.Length - 2, 2);
tableUpdates.Remove(tableUpdates.Length - 2, 2);
tableNames.Append(tableValues);
tableNames.Append(tableUpdates);
tableNames.Append(";").AppendLine();
_content.Append(tableNames.ToString());
}
}
return(_content.ToString());
}
private string BuildReplaceInsertQuery()
{
if (QueryType == SqlQueryType.DeleteInsert)
{
List <object> alreadyDoneEntry = new List <object>();
foreach (SqlItem item in _items)
{
if (alreadyDoneEntry.Contains(item.Key))
{
continue;
}
_content.AppendFormat("DELETE FROM `{0}` WHERE `{1}` = '{2}';", _tableName, _keyName, item.Key).AppendLine();
alreadyDoneEntry.Add(item.Key);
}
}
switch (QueryType)
{
case SqlQueryType.Insert:
case SqlQueryType.DeleteInsert:
_content.AppendFormat("INSERT INTO `{0}`", _tableName);
break;
case SqlQueryType.InsertIgnore:
_content.AppendFormat("INSERT IGNORE INTO `{0}`", _tableName);
break;
case SqlQueryType.Replace:
_content.AppendFormat("REPLACE INTO `{0}`", _tableName);
break;
}
if (!WriteWithoutHeader)
{
_content.AppendFormat(" (`{0}`, ", _keyName);
for (int i = 0; i < _fields.Count; ++i)
{
_content.AppendFormat("`{0}`, ", _fields[i]);
}
_content.Remove(_content.Length - 2, 2);
_content.Append(")");
}
_content.AppendLine(" VALUES");
for (int i = 0; i < _items.Count; ++i)
{
SqlItem item = _items[i];
_content.AppendFormat("('{0}', ", item.Key);
for (int j = 0; j < item.Count; ++j)
{
_content.AppendFormat(NumberFormatInfo.InvariantInfo, "'{0}', ", item[j]);
}
_content.Remove(_content.Length - 2, 2);
_content.AppendFormat("){0}", i < _items.Count - 1 ? "," : ";").AppendLine();
}
return(_content + Environment.NewLine);
}
private void BuildReplaceInsertQuery(StringBuilder content)
{
int objectCount = 0;
if (_hasPreparedQueries)
{
foreach (object key in _sortedDictionary.Keys)
{
if (!_querys.ContainsKey(key))
{
continue;
}
foreach (string query in _querys[key])
{
if (!string.IsNullOrEmpty(query))
{
content.AppendLine(query);
}
}
}
content.AppendLine();
}
if (BuilderSettings.AppendDeleteQuery)
{
content.AppendFormat("DELETE FROM `{0}` WHERE `{1}` IN (", _tableName, _keyName);
foreach (object key in _sortedDictionary.Keys)
{
content.AppendFormat("{0}{1}", key, (objectCount++ < _sortedDictionary.Count - 1) ? ", " : ");");
}
content.AppendLine().AppendLine();
}
switch (BuilderSettings.QueryType)
{
case SqlQueryType.Insert:
content.AppendFormat("INSERT INTO `{0}`", _tableName);
break;
case SqlQueryType.InsertIgnore:
content.AppendFormat("INSERT IGNORE INTO `{0}`", _tableName);
break;
case SqlQueryType.Replace:
content.AppendFormat("REPLACE INTO `{0}`", _tableName);
break;
}
if (!BuilderSettings.WriteWithoutHeader)
{
content.AppendFormat(" (`{0}`, ", _keyName);
for (int i = 0; i < _itemCount; ++i)
{
content.AppendFormat("`{0}`{1}", _tableFields[i], (i < _itemCount - 1) ? ", " : ")");
}
}
content.AppendLine(" VALUES");
objectCount = 0;
foreach (KeyValuePair <object, List <SqlItem> > kvp in _sortedDictionary)
{
object key = kvp.Key;
List <SqlItem> items = kvp.Value;
for (int i = 0; i < items.Count; ++i)
{
SqlItem item = items[i];
content.AppendFormat("({0}, ", key);
for (int j = 0; j < _itemCount; ++j)
{
content.AppendFormat(NumberFormatInfo.InvariantInfo, "{0}{1}", item[j], (j < _itemCount - 1) ? ", " : string.Empty);
}
content.AppendFormat("){0}", objectCount++ < _sortedDictionary.Count - 1 ? "," : ";").AppendLine();
}
}
}
/// <summary>
/// Flush <see cref="Sql.SqlBuilder"/>
/// </summary>
public void Flush()
{
_items[_key].Add(_sqlItem);
_sqlItem = new SqlItem(_itemCount);
}
/// <summary>
/// Setup <see cref="Sql.SqlBuilder"/>
/// </summary>
/// <param name="tableName">Table name (like creature_template, creature etc.)</param>
/// <param name="key">Key name from table</param>
/// <param name="fields">Sequance</param>
/// <param name="hasPreparedQueries">Value indicating whether to create a query storage/param>
/// <param name="count">A specified mumber of contiguous elements from the start of a sequance</param>
public void Setup(string tableName, string key, bool hasPreparedQueries, string[] fields, int count)
{
if (string.IsNullOrEmpty(tableName))
throw new ArgumentNullException("tableName");
if (string.IsNullOrEmpty(key))
throw new ArgumentNullException("key");
if (fields == null)
throw new ArgumentNullException("fields");
if (count == -1)
throw new ArgumentOutOfRangeException("count");
_keyName = key;
_tableName = tableName;
_tableFields = fields;
_itemCount = count;
_sqlItem = new SqlItem(_itemCount);
if ((_hasPreparedQueries = hasPreparedQueries))
_querys = new Dictionary<object, List<string>>(1024);
}
/// <summary>
/// Flush <see cref="Sql.SqlBuilder"/>
/// </summary>
public void Flush()
{
_items[_key].Add(_sqlItem);
_sqlItem = new SqlItem(_itemCount);
}