| public static CreateCommand ( IDbConnection conn, IDbTransaction tx, string sql, object args = null ) : IDbCommand | ||
| conn | IDbConnection | The connection |
| tx | IDbTransaction | |
| sql | string | The sql statement |
| args | object | The parameters |
| return | IDbCommand | |
/// <summary>
/// Creates a database command to update the database with the given record.
/// </summary>
/// <param name="conn">The current database connection</param>
/// <param name="record">The record to update</param>
/// <param name="tx">Optional transaction to run the update in</param>
/// <returns>The command</returns>
private IDbCommand CreateUpdateCommand(IDbConnection conn, IDbTransaction tx = null)
{
List <object> args = new List <object>();
// Build set statement
string values = "";
foreach (string key in Columns.Keys)
{
// Exclude joined & read only members
if (!Attributes[key].ReadOnly && String.IsNullOrEmpty(Attributes[key].Table))
{
values += (values != "" ? "," : "") + key + " = @" + args.Count.ToString();
// Check if the ActiveField is marked with the OnSave property.
if (!String.IsNullOrEmpty(Attributes[key].OnSave))
{
MethodInfo m = this.GetType().GetMethod(Attributes[key].OnSave,
BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
args.Add(m.Invoke(this, new object[] { Columns[key].GetValue(this, null) }));
}
else
{
if (Columns[key].PropertyType == typeof(HtmlString) && Columns[key].GetValue(this, null) != null)
{
args.Add(((HtmlString)Columns[key].GetValue(this, null)).ToHtmlString());
}
else if (typeof(Enum).IsAssignableFrom(Columns[key].PropertyType))
{
args.Add(Columns[key].GetValue(this, null).ToString());
}
else if (Attributes[key].Json)
{
JavaScriptSerializer json = new JavaScriptSerializer();
args.Add(json.Serialize(Columns[key].GetValue(this, null)));
}
else
{
args.Add(Columns[key].GetValue(this, null));
}
}
}
}
// Build where clause
string where = ""; //PrimaryKey + "=@" + args.Count.ToString() ;
foreach (string pk in PrimaryKeys)
{
where += (where != "" ? " AND " : "") +
pk + "=@" + args.Count.ToString();
args.Add(Columns[pk].GetValue(this, null));
}
//args.Add(Columns[PrimaryKey].GetValue(this, null)) ;
// Create command
IDbCommand cmd = Database.CreateCommand(conn, /*tx*/ null, String.Format(SqlUpdate,
TableName, values, where), args.ToArray());
if (tx != null)
{
cmd.Transaction = tx;
}
return(cmd);
}
/// <summary>
/// Executes the given query and returns the matching records.
/// </summary>
/// <param name="query">The query to run</param>
/// <param name="args">Optional query parameters</param>
/// <returns>A list of records</returns>
public static List <T> Query(string query, params object[] args)
{
List <T> result = new List <T>();
var tx = args.Count() > 0 && args[0] is IDbTransaction ? (IDbTransaction)args[0] : null;
using (IDbConnection conn = tx != null ? null : Database.OpenConnection()) {
using (IDbCommand cmd = Database.CreateCommand(tx != null ? tx.Connection : conn, tx, query, args)) {
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.Default)) {
while (rdr.Read())
{
// Create and fill object
object o = Activator.CreateInstance <T>();
for (int n = 0; n < rdr.FieldCount; n++)
{
if (Columns.ContainsKey(rdr.GetName(n)))
{
object val = rdr[n] != DBNull.Value ? rdr[n] : null;
string name = rdr.GetName(n);
// If this is JSON, deserialize before possible OnLoad method
if (Attributes[name].Json)
{
try {
JavaScriptSerializer json = new JavaScriptSerializer();
val = json.Deserialize(Convert.ToString(val), Columns[name].PropertyType);
} catch {
val = null;
}
if (val == null)
{
val = Activator.CreateInstance(Columns[name].PropertyType);
}
}
// Check if the property is marked with the "OnLoad" property
if (!String.IsNullOrEmpty(Attributes[name].OnLoad))
{
MethodInfo m = o.GetType().GetMethod(Attributes[name].OnLoad,
BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
val = m.Invoke(o, new object[] { val });
}
else
{
// Automatically convert strings to HtmlString if needed.
if (Columns[name].PropertyType == typeof(HtmlString))
{
val = new HtmlString(Convert.ToString(val));
}
else if (typeof(Enum).IsAssignableFrom(Columns[name].PropertyType))
{
val = Enum.Parse(Columns[name].PropertyType, (string)val);
}
// Extra type conversions for MySql
if (Database.IsMySql)
{
if (Columns[name].PropertyType == typeof(Guid) && val == null)
{
val = Guid.Empty;
}
else if (Columns[name].PropertyType == typeof(Guid) && val.GetType() == typeof(string))
{
val = new Guid((string)val);
}
else if (Columns[name].PropertyType == typeof(bool) && val.GetType() == typeof(ulong))
{
val = (ulong)val == 1;
}
}
}
Columns[name].SetValue(o, val, null);
}
}
// Set correct object state
if (o is ActiveRecord <T> )
{
((ActiveRecord <T>)o).IsNew = false;
}
result.Add((T)o);
}
}
}
}
return(result);
}
