/// <summary>
/// Performs an insert operation. It is up to the user to ensure that this operation will not fail
/// (ie: calling insert twice on the same object)
/// </summary>
/// <param name="conn"></param>
/// <param name="trans"></param>
public virtual void Insert(OleDbConnection conn, OleDbTransaction trans)
{
string commStr = string.Format("INSERT INTO {0} (", myType.TableName);
string valStr = "VALUES(";
OleDbCommand comm = new OleDbCommand();
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.AddWithValue(paramID, val.ToString());
}
// comm.Parameters.Add(paramID, val.ToString());
else
{
comm.Parameters.AddWithValue(paramID, val);
}
// 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 OleDbCommand("SELECT @@IDENTITY", conn, trans);
myType.AutoNumber.SetValue(this, Convert.ToInt32(comm.ExecuteScalar()), null);
}
}
/// <summary>
/// Generic update method. Will update a row based on the column strings passed in whereCols2
/// </summary>
/// <param name="conn">Connection to use</param>
/// <param name="trans">Transaction to use</param>
/// <param name="whereCols">Columns to limit the updates to</param>
public virtual void Update(
OleDbConnection conn,
OleDbTransaction trans,
params string[] whereCols)
{
// figure out which propertyInfo objects we wont be updating
Hashtable updateHash = new Hashtable();
foreach (string uc in whereCols)
{
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 ";
OleDbCommand comm = new OleDbCommand();
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 exists, this property is part of the WHERE clause
{
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.AddWithValue(paramID, val.ToString());
}
// comm.Parameters.Add(paramID, val.ToString());
else
{
comm.Parameters.AddWithValue(paramID, val);
}
// 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.AddWithValue(paramID, val.ToString());
}
// comm.Parameters.Add(paramID,val.ToString());
else
{
comm.Parameters.AddWithValue(paramID, val);
}
// comm.Parameters.Add(paramID,val);
}
}
comm.CommandText = commStr;
comm.ExecuteNonQuery();
}