private void InsertSQL(DatabaseCore accessor, out string sql, out List <DbParameter> parameters)
{
sql = null; parameters = null;
var pattern = "INSERT INTO [{0}] ({1}) VALUES ({2})";
var list = new List <DbParameter>();
var fields = new List <string>();
var values = new List <string>();
var columns = new List <DBColumn>(Columns.Values);
columns.ForEach(col =>
{
if (col.KeyType != KeyType.IncrementPrimary)
{
var parameter = accessor.CreateParameter(col);
list.Add(parameter);
fields.Add(col.ID);
values.Add(parameter.ParameterName);
}
});
sql = string.Format(pattern, this.TableName, string.Join(",", fields), string.Join(",", values));
parameters = list;
}
private void PrimaryWhereSQL(DatabaseCore accessor, out string sql, out List <DbParameter> parameters)
{
sql = null; parameters = null;
if (accessor == null)
{
return;
}
if (PrimaryList == null || PrimaryList.Count <= 0 || PrimaryList.Any(p => p.Value == null))
{
return;
}
var where = new StringBuilder();
var list = new List <DbParameter>();
PrimaryList.ForEach(p =>
{
if (where.Length > 0)
{
where.AppendLine(" AND ");
}
var parameter = accessor.CreateParameter(p);
where.Append(string.Format("{0} = {1}", p.ID, parameter.ParameterName));
list.Add(parameter);
});
sql = where.ToString();
parameters = list;
}
private void UpdateSQL(DatabaseCore accessor, out string sql, out List <DbParameter> parameters)
{
sql = null; parameters = null;
var pattern = "UPDATE [{0}] SET {1} WHERE {2}";
var pattern1 = "{0} = {1}";
var list = new List <DbParameter>();
var items = new List <string>();
var columns = new List <DBColumn>(Columns.Values).FindAll(col => col.State == ColumnState.CHANGED);
var lst_items = new List <DbParameter>();
columns.ForEach(col =>
{
var parameter = accessor.CreateParameter(col);
lst_items.Add(parameter);
items.Add(string.Format(pattern1, col.ID, parameter.ParameterName));
});
if (items.Count <= 0)
{
return;
}
list.AddRange(lst_items);
string where = null;
List <DbParameter> lst_where = null;
PrimaryWhereSQL(accessor, out where, out lst_where);
if (string.IsNullOrWhiteSpace(where))
{
return;
}
list.AddRange(lst_where);
sql = string.Format(pattern, this.TableName, string.Join(",", items), where);
parameters = list;
}