public async t.Task RemoveIfAsync(SQLType type, string condition)
{
//if (type.IsRecyclable) throw new Exception("Can't remove a IRecyclable type, please use SQL.MoveToGarbage method");
string cmd = String.Format("DELETE FROM {0} WHERE '{1}';", type.TableName, condition);
await ExecuteQueryAsync(cmd);
}
public async t.Task UpdateAsync(SQLType type, string id, string property, DateTime newValue)
{
string cmd = String.Format("UPDATE {0} SET {2} = '{3}' WHERE ID = '{1}';",
type.TableName, id, property, newValue.ToString("yyyy-MM-dd HH:mm:ss"));
await ExecuteQueryAsync(cmd);
}
public SQLParameter(string name, SQLType type, object tempValue, SQLDirection direction) : base()
{
_name = name;
_type = type;
_value = tempValue;
_direction = direction;
}
public Field(string _name, int _initialPosition, int _len)
{
name = _name;
initialPosition = _initialPosition;
len = _len;
typ = SQLType.Varchar;
}
protected void TranslateScalarParameter(CatalogDevicePlan devicePlan, CatalogDevicePlanNode devicePlanNode, PlanNode planNode)
{
SQLType type = null;
switch (planNode.DataType.Name)
{
case "System.String":
case "System.Name":
case "System.UserID":
type = new SQLStringType(200);
break;
case "System.Integer":
type = new SQLIntegerType(4);
break;
case "System.Boolean":
type = new SQLIntegerType(1);
break;
}
if (type != null)
{
string parameterName = String.Format("P{0}", devicePlanNode.PlanParameters.Count + 1);
devicePlanNode.PlanParameters.Add(new CatalogPlanParameter(new SQLParameter(parameterName, type, null), planNode));
devicePlanNode.WhereCondition.AppendFormat("@{0}", parameterName);
}
else
{
devicePlan.IsSupported = false;
}
}
/// <summary>
/// Parses the specified input.
/// </summary>
/// <param name="input">The input.</param>
/// <param name="listener">The listener.</param>
/// <param name="sqlType">Type of the SQL.</param>
public static void Parse(string input, IParseTreeListener listener, SQLType sqlType)
{
if (sqlType == SQLType.TSql)
{
ParseTSQL(input, listener);
}
}
public SQLType GetType(string sql)
{
SQLType type = SQLType.NONE;
sql = sql.Trim();
if (sql.ToLower().IndexOf("select") == 0)
{
type = SQLType.SELECT;
}
else if (sql.ToLower().IndexOf("insert") == 0)
{
type = SQLType.INSERT;
}
else if (sql.ToLower().IndexOf("update") == 0)
{
type = SQLType.UPDATE;
}
else if (sql.ToLower().IndexOf("delete") == 0)
{
type = SQLType.DELETE;
}
return(type);
}
internal static string FormatSQLValue(object someValue, SQLType type = SQLType.NON_SQL)
{
string FormattedValue = "";
if (someValue == null)
{
FormattedValue = "NULL";
}
if (type == SQLType.SQL)
{
FormattedValue = someValue.ToString();
return(FormattedValue);
}
else
{
switch (someValue.GetType().Name)
{
case "String": FormattedValue = "'" + ((string)someValue).Replace("'", "''") + "'"; break;
// case "DateTime": FormattedValue = "'" + ((DateTime)someValue).ToString("yyyy/MM/dd hh:mm:ss") + "'"; break;
case "DBNull": FormattedValue = "NULL"; break;
case "Boolean": FormattedValue = (bool)someValue ? "1" : "0"; break;
//case "SqlLiteral": FormattedValue = ((SqlLiteral)someValue).Value; break;
default: FormattedValue = someValue.ToString(); break;
}
}
return(FormattedValue);
}
public SQLParameter(string AName, SQLType AType, object AValue, SQLDirection ADirection, string AMarker) : base()
{
FName = AName;
FType = AType;
FValue = AValue;
FDirection = ADirection;
FMarker = AMarker;
}
public async t.Task MoveToGarbageAsync(SQLType type, string id)
{
//if (!type.IsRecyclable) throw new Exception("Can't move it to garbage, please use SQL.Remove method");
string cmd = String.Format("INSERT INTO {0}Garbage SELECT * FROM {0} WHERE ID = '{1}';DELETE FROM {0} WHERE ID = '{1}';",
type.TableName, id);
await ExecuteQueryAsync(cmd);
}
public string QueryBuilder(SQLType command, string condition, string values)
{
switch (command)
{
case SQLType.Custom:
return(condition);
case SQLType.GetSingle:
if (condition == "n")
{
return("Error");
}
return($"SELECT * FROM [{_name}] WHERE {condition}");
case SQLType.Create:
if (values == "n")
{
return("Error");
}
//extrapolates the values from the SQL Model data "RowName = value," => value,
return($"INSERT INTO [{_name}] VALUES ({GetValues(values)})");
case SQLType.Update:
if (condition == "n")
{
return("Error");
}
if (values == "n")
{
return("Error");
}
return($"UPDATE [{_name}] SET {values} WHERE {condition} ");
case SQLType.Delete:
if (condition == "n")
{
return("Error");
}
return($"DELETE FROM [{_name}] WHERE {condition}");
case SQLType.JoinOn:
string[] join = condition.Split('.');
if (values == "n")
{
values = "";
}
else
{
values = $"WHERE {_name}.{join[0]}id = {values}";
}
return($"SELECT * FROM [{_name}] join [{join[1]}] on {join[1]}.{join[0]}id = {_name}.{join[0]}id {values}");
case SQLType.GetAll:
default:
return($"SELECT * FROM [{_name}]");
}
}
public SQLParameter(string name, SQLType type, object tempValue, SQLDirection direction, string marker, string literal) : base()
{
_name = name;
_type = type;
_value = tempValue;
_direction = direction;
_marker = marker;
_literal = literal;
}
public async t.Task RemoveFromGarbageAsync(SQLType type, string id)
{
//if (!type.IsRecyclable) throw new Exception("SQL.RemoveFromGarbage doesn't support unrecyclable type");
string cmd = String.Format(@"DELETE FROM ExtendedNotes WHERE ID = '{1}';
DELETE FROM Notes WHERE ID = '{1}';
DELETE FROM {0}Garbage WHERE ID = '{1}';", type.TableName, id);
await ExecuteQueryAsync(cmd);
}
private void btnParse_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(txtFunctionName.Text))
{
MessageBox.Show("Please fill in the function name");
return;
}
if (string.IsNullOrEmpty(txtSQL.Text))
{
MessageBox.Show("Please fill in the SQL statement");
return;
}
_Signature = new SPSignature();
_Signature.Name = txtFunctionName.Text;
List <string> listParamStr = _Signature.GetAllParam(txtSQL.Text);
if (listParamStr.Count == 0)
{
MessageBox.Show("No @ parameters found");
return;
}
_ListParam = new List <Parameter>();
foreach (string str in listParamStr)
{
_ListParam.Add(new Parameter {
Name = str, SQLType = "NVarChar", TextLength = "50"
});
}
SQLType type = _Signature.GetType(txtSQL.Text);
if (type == SQLType.INSERT)
{
chkReturnPrimaryKey.IsEnabled = true;
}
else
{
chkReturnPrimaryKey.IsEnabled = false;
}
if (type == SQLType.SELECT)
{
chkUseSQLReader.IsEnabled = true;
}
else
{
chkUseSQLReader.IsEnabled = false;
}
listBoxSQPParameterType.ItemsSource = _ListParam;
btnGenerateCode.IsEnabled = true;
}
public List <T> ReadSQLBaseTypes <T>(MySqlDataReader Reader, SQLTypeHelper Helper, int limit = -1)
{
SQLType Type = Helper.GetSQLTypeIndexed(typeof(T));
if (Type == null)
{
throw new SQLIncompatableTypeException();
}
return(ReadSQLBaseTypesUnsafe <T>(Reader, limit));
}
internal void Update(SQLType type, string id, string property, string newValue)
{
string cmd = String.Format("UPDATE {0} SET {2} = '{3}' WHERE ID = '{1}';", type.TableName, id, property, newValue);
ExecuteQuery(cmd).ContinueWith(t => { if (t.IsFaulted)
{
throw t.Exception;
}
});
}
public AppConfig()
{
this.ConnectList = new List <Connection>();
this.ShowForm_RecentItem = true;
this.ShowPrintDialog_AtTagPrint = true;
this.configPath = "";
this.BackupDirectory = @".\backup";
this.DefaultSQLType = SQLType.MySQL;
this.RecentSQLiteFile = new List <SQLiteFile>();
}
internal void RemoveFromGarbage(SQLType type, string id)
{
string cmd = String.Format(@"DELETE FROM {0}Garbage WHERE ID = '{1}';", type.TableName, id);
ExecuteQuery(cmd).ContinueWith(t => { if (t.IsFaulted)
{
throw t.Exception;
}
});
}
public async t.Task RemoveAllFromGarbageAsync(SQLType type)
{
//if (!type.IsRecyclable) throw new Exception("SQL.RemoveAllFromGarbageAsync doesn't support unrecyclable type");
string cmd = String.Format(@"
DELETE FROM ExtendedNotes WHERE ID IN (SELECT {0}.ID FROM {0} INNER JOIN ExtendedNotes ON {0}.ID = ExtendedNotes.ID);
DELETE FROM Notes WHERE ID IN (SELECT {0}.ID FROM {0} INNER JOIN Notes ON {0}.ID = Notes.ID);
DELETE FROM {0}Garbage;", type.TableName);
await ExecuteQueryAsync(cmd);
}
public async Task <bool> SQLCommand(SQLType command, string condition = "n", string values = "n")
{
string test = QueryBuilder(command, condition, values);
if (test == "Error")
{
return(false);
}
OpenDB(test);
try
{
await _command.Connection.OpenAsync();
switch (command)
{
case SQLType.GetSingle:
case SQLType.GetAll:
case SQLType.Custom:
case SQLType.JoinOn:
_reader = await _command.ExecuteReaderAsync();
onRead(currentType);
if (Items.Count > 0)
{
Item = Items[0];
}
break;
case SQLType.Create:
case SQLType.Update:
case SQLType.Delete:
RowsAltered = await _command.ExecuteNonQueryAsync();
break;
default:
break;
}
/*_command.Connection.Close();
* await _command.Connection.OpenAsync();
* _command.CommandText = "SELECT SCOPE_IDENTITY()";
* LastId = await _command.ExecuteNonQueryAsync();*/
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
Task.WaitAll();
CloseDB();
return(true);
}
/// <summary>
/// this is the QueryBase constructor
/// Takes name and type as parameters and
/// sets RetuenIdentity to true.
/// </summary>
/// <param name="name">parameter name</param>
/// <param name="type"></param>
/// <param name="isReturnIdentityRequired"> isReturnIdentityRequired as boolean value</param>
protected QueryBase(string name, SQLType type, bool isReturnIdentityRequired)
{
this.name = name;
fieldNames = new List <string>();
queryBuilder = new QueryBuilder(type, name);
if (isReturnIdentityRequired)
{
queryBuilder.ReturnIdentity = true;
}
}
internal void Update(SQLType type, string id, string property, DateTime newValue)
{
string cmd = String.Format("UPDATE {0} SET {2} = DATE('{3}') WHERE ID = '{1}';",
type.TableName, id, property, newValue.ToString("yyyy-MM-dd HH:mm:ss"));
ExecuteQuery(cmd).ContinueWith(t => { if (t.IsFaulted)
{
throw t.Exception;
}
});
}
public SQLTool(SQLType sqlType, string ip, string db, string user, string pwd)
: this(sqlType, ip, db)
{
if (!string.IsNullOrEmpty(user))
{
m_User = user;
}
if (!string.IsNullOrEmpty(pwd))
{
m_Pwd = pwd;
}
}
public CRegField(WebControl pageelement, String fieldname, FieldType fieldtype,
SQLType sqltype, String desc, Boolean editable, Boolean notnull, Boolean primary)
{
this._pageelement = pageelement;
this._fieldname = fieldname;
this._fieldtype = fieldtype;
this._sqltype = sqltype;
this._desc = desc;
this._editable = editable;
this._notnull = notnull;
this._primary = primary;
}
public async t.Task RestoreFromGarbageAsync(SQLType type, string id)
{
//if (!type.IsRecyclable) throw new Exception("SQL.RestoreFromGarbage doesn't support unrecyclable type");
string cmd = String.Format(@"INSERT INTO {0} SELECT * FROM {0}Garbage WHERE ID = '{1}';DELETE FROM {0}Garbage WHERE ID = '{1}';
PRAGMA foreign_keys = 0;
CREATE TABLE _{0} AS SELECT * FROM {0} ORDER BY LENGTH(ID),ID;
DROP TABLE {0};
CREATE TABLE {0} AS SELECT * FROM _{0};
DROP TABLE _{0};
PRAGMA foreign_keys = 1;",
type.TableName, id);
await ExecuteQueryAsync(cmd);
}
internal void Remove(SQLType type, string id)
{
if (type.IsRecyclable)
{
throw new Exception("Can't remove a IRecyclable type, please use SQL.MoveToGarbage method");
}
string cmd = String.Format("DELETE FROM {0} WHERE ID = '{1}';", type.TableName, id);
ExecuteQuery(cmd).ContinueWith(t => { if (t.IsFaulted)
{
throw t.Exception;
}
});
}
public SQLTypeHelper()
{
foreach (Type Type in Assembly.GetExecutingAssembly().GetTypes().Where(x => typeof(SQLType).IsAssignableFrom(x.BaseType)))
{
if (Attribute.IsDefined(Type, typeof(SQLNetType)))
{
Type NetType = ((SQLNetType)Attribute.GetCustomAttribute(Type, typeof(SQLNetType))).Type;
if (NetType != null && !NetTypeIndex.ContainsKey(NetType))
{
SQLType SQLType = (SQLType)Activator.CreateInstance(Type);
NetTypeIndex.Add(NetType, SQLType);
}
}
}
}
/// <summary>
/// Локальный тип БД.
/// </summary>
/// <param name="DataBaseType"></param>
/// <returns></returns>
public static bool IsLocalDataBaseType(SQLType DataBaseType)
{
if (DataBaseType == SQLType.Access ||
DataBaseType == SQLType.Access2007 ||
DataBaseType == SQLType.MSSqlCE ||
DataBaseType == SQLType.SQLite ||
DataBaseType == SQLType.VistaDB)
{
return(true);
}
else
{
return(false);
}
}
internal void MoveToGarbage(SQLType type, string id)
{
if (!type.IsRecyclable)
{
throw new Exception("Can't move it to garbage, please use SQL.Remove method");
}
string cmd = String.Format("INSERT INTO {0}Garbage SELECT * FROM {0} WHERE ID = '{1}';DELETE FROM {0} WHERE ID = '{1}';",
type.TableName, id);
ExecuteQuery(cmd).ContinueWith(t => { if (t.IsFaulted)
{
throw t.Exception;
}
});
}
public IEnumerable <T> GetItems <T>(SQLType type, params object[] args)
{
string sql = string.Empty;
try
{
sql = string.Format(getSQL(type), args);
return(base.GetItems <T>(sql));
}
catch (Exception ex)
{
this.sm(ex.Message);
throw ex;
}
}
public bool Update(SQLType type, Int32 pkID, Object[] args)
{
string sql = string.Empty;
try
{
sql = String.Format(getSQL(type), args);
return(base.Update(sql));
}
catch (Exception ex)
{
this.sm(ex.Message);
return(false);
}
}
private void DetermineType()
{
if (Statement.StartsWith("SELECT", StringComparison.InvariantCultureIgnoreCase))
{
this.Type = SQLType.SELECT;
}
if (Statement.StartsWith("UPDATE", StringComparison.InvariantCultureIgnoreCase))
{
this.Type = SQLType.UPDATE;
}
if (Statement.StartsWith("DELETE", StringComparison.InvariantCultureIgnoreCase))
{
this.Type = SQLType.DELETE;
}
if (Statement.StartsWith("INSERT", StringComparison.InvariantCultureIgnoreCase))
{
this.Type = SQLType.INSERT;
}
}
