/// <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(); }