//command parameters in mysql start with ?
public virtual void Insert(MySqlConnection conn, MySqlTransaction trans)
{
string commStr = string.Format("INSERT INTO {0} (", myType.TableName);
string valStr = "VALUES(";
MySqlCommand comm = new MySqlCommand();
comm.Connection = conn;
comm.Transaction = trans;
bool flag = false;
int i = 0;
foreach (PropertyInfo pi in myType.Columns)
{
object val = pi.GetValue(this, null);
DSShared.DB.DBColumnAttribute attr = myType[pi];
if (!attr.IsAutoNumber && val != null)
{
if (flag)
{
commStr += ",";
valStr += ",";
}
else
{
flag = true;
}
commStr += attr.ColumnName;
string paramID = "?" + paramPrefix + (i++);
valStr += paramID;
if (val is DateTime)
{
comm.Parameters.Add(paramID, val.ToString());
}
else
{
comm.Parameters.Add(paramID, val);
}
//Console.WriteLine("Insert: "+columns[c].Name+":"+columns[c].Value+" type: "+columns[c].Value.GetType());
}
}
comm.CommandText = commStr + ") " + valStr + ")";
comm.ExecuteNonQuery();
if (myType.AutoNumber != null)
{
comm = new MySqlCommand("SELECT @@IDENTITY", conn, trans);
myType.AutoNumber.SetValue(this, Convert.ToInt32(comm.ExecuteScalar()), null);
}
}
public virtual void Update(MySqlConnection conn, MySqlTransaction trans, params string[] whereCols2)
{
//figure out which propertyInfo objects we wont be updating
Hashtable updateHash = new Hashtable();
foreach (string uc in whereCols2)
{
foreach (PropertyInfo pi in myType.Columns)
{
DSShared.DB.DBColumnAttribute attr = myType[pi];
if (uc == attr.ColumnName)
{
updateHash[pi] = true;
break;
}
}
}
string commStr = "UPDATE " + myType.TableName + " SET ";
MySqlCommand comm = new MySqlCommand();
comm.Connection = conn;
comm.Transaction = trans;
bool flag = false;
int i = 0;
//build SET clause
foreach (PropertyInfo pi in myType.Columns)
{
if (updateHash[pi] != null) //if true, this property is part of the WHERE clause
{
continue;
}
object val = pi.GetValue(this, null);
DSShared.DB.DBColumnAttribute attr = myType[pi];
if (!attr.IsAutoNumber && val != null)
{
if (flag)
{
commStr += ",";
}
else
{
flag = true;
}
commStr += attr.ColumnName;
string paramID = "?" + paramPrefix + (i++);
commStr += "=" + paramID;
if (val is DateTime)
{
comm.Parameters.Add(paramID, val.ToString());
}
else
{
comm.Parameters.Add(paramID, val);
}
}
}
commStr += " WHERE ";
flag = false;
foreach (PropertyInfo pi in updateHash.Keys)
{
object val = pi.GetValue(this, null);
DSShared.DB.DBColumnAttribute attr = myType[pi];
if (val != null)
{
if (flag)
{
commStr += ",";
}
else
{
flag = true;
}
commStr += attr.ColumnName;
string paramID = "?" + paramPrefix + (i++);
commStr += "=" + paramID;
if (val is DateTime)
{
comm.Parameters.Add(paramID, val.ToString());
}
else
{
comm.Parameters.Add(paramID, val);
}
}
}
comm.CommandText = commStr;
comm.ExecuteNonQuery();
}
