public string BuildUpdateStatement(Common.Classes.TableInfo tableInfo)
{
var sb = new StringBuilder();
sb.AppendFormat("UPDATE {0} SET", tableInfo.TableName.Trim().ToUpper());
var cols = tableInfo.Columns.ToList();
foreach (var col in cols)
{
// do not allow the keys to be updated through this function
if ((!col.IsIdentity) && (!col.IsKey))
{
sb.AppendFormat("{0} = @{0}", col.ColumnName);
if (cols.IndexOf(col) != cols.Count() - 1)
{
sb.Append(", ");
}
}
sb.Append(" WHERE ");
var keys = tableInfo.Keys.ToList();
foreach (var s in keys)
{
sb.AppendFormat("{0} = @Original{0}", s.ColumnName);
if (keys.IndexOf(s) != keys.Count - 1)
{
sb.Append(" AND ");
}
}
}
return(sb.ToString());
}
public string BuildSelectStatement(Common.Classes.TableInfo tableInfo)
{
var sb = new StringBuilder("SELECT");
var columns = tableInfo.Columns.ToList();
foreach (var col in columns)
{
sb.Append(col.ColumnName);
if (columns.IndexOf(col) < columns.Count)
{
sb.Append(", ");
}
}
sb.AppendFormat(@" FROM {0}", tableInfo.TableName);
return(sb.ToString());
}
public string BuildInsertStatement(Common.Classes.TableInfo tableInfo)
{
var sb = new StringBuilder(256);
var sb2 = new StringBuilder(256);
var bHasSeed = false;
sb.AppendFormat("INSERT INTO {0}", tableInfo.TableName.Trim().ToUpper());
sb2.Append(" VALUES(");
var cols = tableInfo.Columns.ToList();
foreach (var col in cols)
{
if (!col.IsIdentity)
{
sb.Append(col.ColumnName);
sb2.AppendFormat("@{0}", col.ColumnName);
if (cols.IndexOf(col) != cols.Count() - 1)
{
sb.Append(", ");
sb2.Append(", ");
}
else
{
bHasSeed = true;
}
}
}
sb.Append(")");
sb2.Append(")");
//Only return scope identity if the key has an identity seed
if (bHasSeed)
{
sb2.AppendFormat("RETURNING {0} into @NewId", cols.First(c => c.IsIdentity).ColumnName);
}
return(String.Format("{0}{1}", sb.ToString(), sb2));
}
public string BuildDeleteStatement(Common.Classes.TableInfo tableInfo)
{
var sb = new StringBuilder(256);
var colKeys = tableInfo.Keys.ToList();
sb.AppendFormat("DELETE FROM {0}", tableInfo.TableName.Trim().ToUpper());
if (colKeys.Count > 0)
{
sb.Append(" WHERE ");
foreach (var s in colKeys)
{
sb.AppendFormat("{0} = @Original{0}", s);
if (colKeys.IndexOf(s) != colKeys.Count - 1)
{
sb.Append(" AND ");
}
}
}
return(sb.ToString());
}
