public int Delete(GenericDatabase db, String conditions)
{
string sql = "DELETE FROM " + TableName + " WHERE " + conditions;
DbCommand dbCommand = db.GetOpenedConnection().CreateCommand();
dbCommand.CommandText = sql;
return dbCommand.ExecuteNonQuery();
}
public DataTable GetFields(GenericDatabase db, string columns, string condetions = null)
{
DbCommand dbCommand = db.GetOpenedConnection().CreateCommand();
dbCommand.CommandText = "SELECT " + columns + " FROM " + TableName;
if (condetions != null)
{
dbCommand.CommandText += " WHERE " + condetions;
}
return GetDataTableFromCommand(dbCommand);
}
public bool LoadWithPrimaryKey(GenericDatabase db, object primaryKeyValue)
{
DbCommand dbCommand = db.GetOpenedConnection().CreateCommand();
dbCommand.CommandText = "SELECT * FROM " + TableName + " WHERE " + PrimaryKey.Name + " = " + db.GetAsParameterName(PrimaryKey.Name);
dbCommand.Parameters.Add(db.GetDBCellAsDBParameter(dbCommand, new DBCell(PrimaryKey.Name, PrimaryKey.Type, primaryKeyValue)));
return FillObjectWithDataReader(dbCommand);
}
public bool Load(GenericDatabase db, string conditions)
{
DbCommand dbCommand = db.GetOpenedConnection().CreateCommand();
dbCommand.CommandText = "SELECT * FROM " + TableName + " WHERE " + conditions;
return FillObjectWithDataReader(dbCommand);
}
public int Insert(GenericDatabase db)
{
DbCommand dbCommand = GetInsertCommand(db, db.GetOpenedConnection(), TableName, Values);
return dbCommand.ExecuteNonQuery();
}
private bool isValueExistsWithColumns(GenericDatabase db, String[] keyNames)
{
bool exists = false;
if (keyNames == null)
{
throw new NullReferenceException();
}
else
{
if ((keyNames.Length <= 0) || (keyNames.Length > Values.Length))
{
throw new ArgumentException();
}
else
{
String sql = "Select TOP 1 * from " + TableName + " where ";
DbCommand cmd = db.GetOpenedConnection().CreateCommand();
for (int i = 0; i < keyNames.Length; i++)
{
DBCell cell = this[keyNames[i]];
sql += " " + keyNames[i] + " = " + db.GetAsParameterName(keyNames[i]) + " and ";
cmd.Parameters.Add(db.GetDBCellAsDBParameter(cmd, cell));
}
sql += " 1=1";
cmd.CommandText = sql;
exists = (GetDataTableFromCommand(cmd).Rows.Count > 0);
}
}
return exists;
}
private DbCommand GetUpdateCommand(GenericDatabase db, DbConnection conn, string tableName, DBCell[] cells, DBCell[] condetions)
{
DbCommand cmd = GetUpdateCommand(db, conn, tableName, cells);
cmd.CommandText += " WHERE ";
foreach (DBCell condetion in condetions)
{
cmd.CommandText += condetion.Name + " = " + db.GetAsParameterName(condetion.Name) + " AND ";
cmd.Parameters.Add(db.GetDBCellAsDBParameter(cmd, condetion));
}
cmd.CommandText += " 1=1";
return cmd;
}
private DbCommand GetUpdateCommand(GenericDatabase db, DbConnection conn, string tableName, DBCell[] cells)
{
if (Validate())
{
string sql = "UPDATE " + tableName + " SET ";
DbCommand dbCommand = conn.CreateCommand();
for (int i = 0; i < cells.Length; i++)
{
if (cells[i].IsUpdatable)
{
sql += cells[i].Name + " = " + db.GetAsParameterName(cells[i].Name);
sql += ", ";
dbCommand.Parameters.Add(db.GetDBCellAsDBParameter(dbCommand, cells[i]));
}
}
sql = Utils.RemoveTrailingSymbol(sql, ",");
dbCommand.CommandText = sql;
return dbCommand;
}
return null;
}
private DbCommand GetInsertCommand(GenericDatabase db, DbConnection conn, string tableName, DBCell[] cells)
{
if(Validate())
{
DbCommand dbCommand = conn.CreateCommand();
dbCommand.CommandText = "INSERT INTO " + tableName + "(";
String vals = "";
for (int i = 0; i < cells.Length; i++)
{
if (cells[i].IsUpdatable)
{
dbCommand.CommandText += cells[i].Name;
dbCommand.CommandText += ", ";
vals += db.GetAsParameterName(cells[i].Name);
vals += ", ";
dbCommand.Parameters.Add(db.GetDBCellAsDBParameter(dbCommand, cells[i]));
}
}
dbCommand.CommandText = Utils.RemoveTrailingSymbol(dbCommand.CommandText, ",");
vals = Utils.RemoveTrailingSymbol(vals, ",");
dbCommand.CommandText += ") VALUES (" + vals + ")";
return dbCommand;
}
return null;
}
public int Update(GenericDatabase db, String conditions)
{
DbCommand dbCommand = GetUpdateCommand(db, db.GetOpenedConnection(), TableName, Values);
if (conditions != null)
{
dbCommand.CommandText += " WHERE " + conditions;
}
return dbCommand.ExecuteNonQuery();
}
public int Save(GenericDatabase db)
{
if (isValueExistsWithColumns(db, new string[] { PrimaryKey.Name }))
{
return Update(db, new DBCell[] { PrimaryKey });
}
else
{
return Insert(db);
}
}
