public int ExecuteUpdate(QueryBuilder query)
{
SetCommand(query);
int rows = currentCommand.ExecuteNonQuery();
currentConnection.Close();
return rows;
}
public object ExecuteInsert(QueryBuilder query)
{
object id = null;
query.AddText(";SELECT @@IDENTITY;");
SetCommand(query);
id = currentCommand.ExecuteScalar();
currentConnection.Close();
return id;
}
private void SetCommand(QueryBuilder query)
{
currentConnection = GetConnection();
currentConnection.Open();
currentCommand = GetCommand();
currentCommand.Connection = currentConnection;
currentCommand.CommandText = query.ToString();
foreach (DatabaseParameter parmeter in query.Parameters)
{
currentCommand.Parameters.Add(GetParameter(parmeter));
}
}
public void Insert(int tranUserId)
{
DatabaseAccessLayer dataAccessLayer = GetDatabaseAccessLayer();
QueryBuilder query = new QueryBuilder();
query.AddText("INSERT INTO ");
query.AddTableName(this.GetTableName());
query.AddText("(");
Field[] fields = null;
if (this.GetPrimaryKey().IsAutoIncreement == false)
fields = GetAllFields();
else
fields = GetFields();
bool addComma = false;
for (int i = 0; i < fields.Length; i++)
{
if (fields[i].Value != null)
{
if (addComma == true)
query.AddText(",");
query.AddColumn(fields[i].ColumnName);
addComma = true;
}
}
if (this.HasAccessColumns() == true)
{
query.AddText(",");
query.AddAccessColumns();
}
query.AddText(") VALUES (");
addComma = false;
for (int i = 0; i < fields.Length; i++)
{
if (fields[i].Value != null)
{
if (addComma == true)
query.AddText(",");
query.AddParam(fields[i]);
addComma = true;
}
}
if (this.HasAccessColumns())
{
query.AddText(",");
query.AddAccessParams(tranUserId);
}
query.AddText(")");
if (this.GetPrimaryKey().IsAutoIncreement == false)
dataAccessLayer.ExecuteUpdate(query);
else
this.GetPrimaryKey().Value = dataAccessLayer.ExecuteInsert(query);
}
public QueryBuilder GetSelectQuery()
{
Console.WriteLine("GetSelectQuery()");
QueryBuilder query = new QueryBuilder();
query.AddText("SELECT ");
Field[] fields = this.GetAllFields();
for (int i = 0; i < fields.Length; i++)
{
if (i > 0)
query.AddText(",");
query.AddColumn(fields[i].ColumnName);
}
query.AddText(" FROM ");
query.AddTableName(this.GetTableName());
return query;
}
public DbDataReader ExecuteQuery(QueryBuilder query)
{
SetCommand(query);
return currentCommand.ExecuteReader();
}
public void Update(int tranUserId)
{
DatabaseAccessLayer dataAccessLayer = GetDatabaseAccessLayer();
QueryBuilder query = new QueryBuilder();
query.AddText("UPDATE ");
query.AddTableName(this.GetTableName());
query.AddText("SET ");
Field[] fields = GetFields();
for (int i = 0; i < fields.Length; i++)
{
if (i > 0) query.AddText(",");
query.AddColumn(fields[i].ColumnName);
query.AddText("=");
query.AddParam(fields[i]);
}
if (this.HasAccessColumns() == true)
{
query.AddText(",");
query.AddLastUpdate(tranUserId);
}
query.AddEqualsFilter(this.GetPrimaryKey());
int rows = dataAccessLayer.ExecuteUpdate(query);
}
public void Remove(int tranUserId)
{
DatabaseAccessLayer dataAccessLayer = GetDatabaseAccessLayer();
QueryBuilder query = new QueryBuilder();
query.AddText("DELETE FROM");
query.AddTableName(this.GetTableName());
query.AddEqualsFilter(this.GetPrimaryKey());
dataAccessLayer.ExecuteDelete(query);
}