public void ConfigDefaultValue()
{
var configStatement = new ConfigStatement();
Assert.AreEqual("true", configStatement.Argument);
}
private ConfigStatement getConfigInsert(object registry)
{
ConfigStatement c;
List <string> fields;
TableAttribute table_att;
FieldAttribute att;
string aux;
string aux2;
bool autoincrement_detected;
object value;
autoincrement_detected = false;
c = new ConfigStatement()
{
SQL = "", Params = new List <StatementParameter>()
};
fields = new List <string>();
//Get table attribute from class
table_att = registry.GetType().GetTypeInfo().GetCustomAttribute <TableAttribute>();
//Get fields attributes from properties
foreach (PropertyInfo item in registry.GetType().GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance))
{
//Create SQL parameters
att = item.GetCustomAttribute <FieldAttribute>();
if (att != null)
{
value = item.GetValue(registry);
if (value == null)
{
value = att.DefaultValue;
}
if (!att.IsAutoincrement)
{
//Check if value por int represent NULL value
if (att.isInteger)
{
if (att.NullValueForInt == (int)value) //always will be NOT NULL because is a integer variable
{
value = DBNull.Value;
}
}
else
{
if (value == null)
{
value = DBNull.Value;
}
}
//Set value
c.Params.Add(new StatementParameter("@arg" + fields.Count, att.Type, value));
fields.Add(att.FieldName);
}
else
{
if (!autoincrement_detected)
{
if (att.isInteger)
{
c.isFirstPKAutoIncrementInt = true;
autoincrement_detected = true;
}
}
}
if (!att.AllowNull)
{
if (value == null || value == DBNull.Value)
{
throw new Exception("Field '" + att.FieldName + "' not support NULL value");
}
}
}
}
//Build a INSERT statement
c.SQL = "INSERT INTO {0}({1}) VALUES ({2})";
aux = "";
for (int i = 0; i < fields.Count; i++)
{
aux += fields[i];
if (i < fields.Count - 1)
{
aux += ", ";
}
}
aux2 = "";
for (int i = 0; i < c.Params.Count; i++)
{
aux2 += c.Params[i].Nombre;
if (i < c.Params.Count - 1)
{
aux2 += ", ";
}
}
c.SQL = string.Format(c.SQL, table_att.Name, aux, aux2);
return(c);
}
private ConfigStatement getConfigSelect(object registry, bool withoutwhere = false)
{
ConfigStatement c;
List <string> fields;
List <string> fields_pk;
TableAttribute table_att;
FieldAttribute att;
object value;
string aux;
string aux2;
c = new ConfigStatement()
{
SQL = "", Params = new List <StatementParameter>()
};
fields = new List <string>();
fields_pk = new List <string>();
//Get table attribute from class
table_att = registry.GetType().GetTypeInfo().GetCustomAttribute <TableAttribute>();
//Get fields attributes from properties
foreach (PropertyInfo item in registry.GetType().GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance))
{
//Create SQL parameters
att = item.GetCustomAttribute <FieldAttribute>();
if (att != null)
{
if (!withoutwhere)
{
if (att.IsPrimaryKey)
{
value = item.GetValue(registry);
if (att.isInteger)
{
if (att.NullValueForInt == (int)value) //always will be NOT NULL because is a integer variable
{
value = DBNull.Value;
}
}
else
{
if (value == null)
{
value = DBNull.Value;
}
}
//c.Params.Add(new StatementParameter("@pk" + fields.Count, att.Type, item.GetValue(registry)));
c.Params.Add(new StatementParameter("@pk" + fields.Count, att.Type, value));
fields_pk.Add(table_att.Name + "." + att.FieldName);
if (!att.AllowNull)
{
if (value == null || value == DBNull.Value)
{
throw new Exception("Field '" + att.FieldName + "' not support NULL value");
}
}
}
}
fields.Add(table_att.Name + "." + att.FieldName);
}
}
//Build a INSERT statement
c.SQL = "SELECT {1} FROM {0} WHERE 1 = 1 {2}";
aux = "";
for (int i = 0; i < fields.Count; i++)
{
aux += fields[i];
if (i < fields.Count - 1)
{
aux += ", ";
}
}
aux2 = "";
for (int i = 0; i < fields_pk.Count; i++)
{
aux2 += " AND " + fields_pk[i] + " = " + c.Params[i].Nombre;
}
if (!withoutwhere)
{
if (string.IsNullOrEmpty(aux2))
{
throw new Exception("This query can not be performed without primary keys");
}
}
c.SELECT = string.Format(c.SQL, table_att.Name, aux, "");
c.SQL = string.Format(c.SQL, table_att.Name, aux, aux2);
return(c);
}
private ConfigStatement getConfigUpdate(object registry)
{
ConfigStatement c;
List <StatementParameter> parameters;
List <StatementParameter> parameters_pk;
List <string> fields;
List <string> fields_pk;
TableAttribute table_att;
FieldAttribute att;
string aux;
string aux2;
object value;
c = new ConfigStatement()
{
SQL = "", Params = new List <StatementParameter>()
};
fields = new List <string>();
fields_pk = new List <string>();
parameters = new List <StatementParameter>();
parameters_pk = new List <StatementParameter>();
//Get table attribute from class
table_att = registry.GetType().GetTypeInfo().GetCustomAttribute <TableAttribute>();
//Get fields in SET section from properties
foreach (PropertyInfo item in registry.GetType().GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance))
{
//Create SQL parameters
att = item.GetCustomAttribute <FieldAttribute>();
if (att != null)
{
value = item.GetValue(registry);
//Check if value por int represent NULL value
if (att.AllowNull)
{
if (att.isInteger)
{
if (att.NullValueForInt == (int)value) //always will be NOT NULL because is a integer variable
{
value = DBNull.Value;
}
}
else
{
if (value == DBNull.Value)
{
value = DBNull.Value;
}
}
}
if (!att.IsPrimaryKey)
{
parameters.Add(new StatementParameter("@arg" + fields.Count, att.Type, value));
if (this.Type == DBMSType.SQLite)
{
fields.Add(att.FieldName);
}
else
{
fields.Add(table_att.Name + "." + att.FieldName);
}
}
if (!att.AllowNull)
{
if (value == null || value == DBNull.Value)
{
throw new Exception("Field '" + att.FieldName + "' not support NULL value");
}
}
}
}
//Get fields in WHERE section from properties
foreach (PropertyInfo item in registry.GetType().GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance))
{
//Create SQL parameters
att = item.GetCustomAttribute <FieldAttribute>();
if (att != null)
{
if (att.IsPrimaryKey)
{
value = item.GetValue(registry);
//Check if value por int represent NULL value
if (att.AllowNull)
{
if (att.isInteger)
{
if (att.NullValueForInt == (int)value) //always will be NOT NULL because is a integer variable
{
value = null;
}
}
}
parameters_pk.Add(new StatementParameter("@pk" + fields_pk.Count, att.Type, value));
fields_pk.Add(table_att.Name + "." + att.FieldName);
if (!att.AllowNull)
{
if (value == null)
{
throw new Exception("Field '" + att.FieldName + "' not support NULL value");
}
}
}
}
}
c.SQL = "UPDATE {0} SET {1} WHERE 1 = 1 {2}";
//Build SET section in UPDATE statement
aux = "";
for (int i = 0; i < fields.Count; i++)
{
aux += fields[i] + " = " + parameters[i].Nombre;
if (i < fields.Count - 1)
{
aux += ", ";
}
c.Params.Add(parameters[i]);
}
//Build WHERE section in UPDATE statement
aux2 = "";
for (int i = 0; i < fields_pk.Count; i++)
{
aux2 += " AND " + fields_pk[i] + " = " + parameters_pk[i].Nombre;
c.Params.Add(parameters_pk[i]);
}
if (string.IsNullOrEmpty(aux2))
{
throw new Exception("This query can not be performed without primary keys");
}
c.SQL = string.Format(c.SQL, table_att.Name, aux, aux2);
return(c);
}
