private string BuildCreateSQL()
{
Dictionary <string, ColumnLayout> Columns = new Dictionary <string, ColumnLayout>();
foreach (DataGridViewRow dr in dgvTableDef.Rows)
{
// Assign to local variables for readability
// ColName and ColType have already been tested to insure values are not null
ColumnRow cr = MoveGridRow(dr);
if (string.IsNullOrEmpty(cr.ColName))
{
break;
}
ColumnLayout column = new ColumnLayout();
column.Check = cr.ColCheck;
column.Collation = cr.ColCollation;
column.ColumnType = cr.ColType;
column.DefaultValue = cr.ColDefault;
column.ForeignKey = new ForeignKeyLayout();
column.ForeignKey.Table = cr.FK_Table;
column.ForeignKey.To = cr.FK_Column;
column.ForeignKey.OnUpdate = cr.FK_OnUpdate;
column.ForeignKey.OnDelete = cr.FK_OnDelete;
column.NullType = cr.ColAllowNulls ? 0 : 1;
column.PrimaryKey = cr.ColPrimaryKey;
column.Unique = cr.ColUnique;
Columns.Add(cr.ColName, column);
}
return(SqlFactory.CreateSQL(txtTableName.Text, Columns));
}
public static void CreateCliffRenderer(
IClientSceneObject sceneObject,
ColumnRow textureRegion,
TextureAtlasResource cliffAtlas)
{
RenderingService.CreateSpriteRenderer(
sceneObject,
cliffAtlas.Chunk(textureRegion.Column, textureRegion.Row),
drawOrder: DrawOrder.GroundCliffs,
positionOffset: (0, 0));
}
private ColumnRow MoveGridRow(DataGridViewRow dr)
{
ColumnRow cr = new ColumnRow();
// Assign to local variables for readability
cr.ColName = dr.Cells["ColName"].Value == null ? string.Empty : dr.Cells["ColName"].Value.ToString().Trim();
cr.ColType = dr.Cells["ColType"].Value == null ? string.Empty : dr.Cells["ColType"].Value.ToString();
cr.ColAllowNulls = dr.Cells["ColAllowNulls"].Value == null ? false : (bool)dr.Cells["ColAllowNulls"].Value;
cr.ColUnique = dr.Cells["ColUnique"].Value == null ? false : (bool)dr.Cells["ColUnique"].Value;
cr.ColDefault = dr.Cells["ColDefault"].Value == null ? string.Empty : dr.Cells["ColDefault"].Value.ToString();
cr.ColPrimaryKey = 0;
if (dr.Cells["ColPrimaryKey"].Value != null)
{
Int32.TryParse(dr.Cells["ColPrimaryKey"].Value.ToString(), out cr.ColPrimaryKey);
}
cr.ColCollation = dr.Cells["ColCollation"].Value == null ? null : dr.Cells["ColCollation"].Value.ToString();
cr.ColCheck = dr.Cells["ColCheck"].Value == null ? null : dr.Cells["ColCheck"].Value.ToString();
cr.FK_Table = dr.Cells["FKey_Table"].Value == null ? null : dr.Cells["FKey_Table"].Value.ToString();
cr.FK_Column = dr.Cells["FKey_Column"].Value == null ? null : dr.Cells["FKey_Column"].Value.ToString();
cr.FK_OnUpdate = dr.Cells["FKey_OnUpdate"].Value == null ? null : dr.Cells["FKey_OnUpdate"].Value.ToString();
cr.FK_OnDelete = dr.Cells["FKey_OnDelete"].Value == null ? null : dr.Cells["FKey_OnDelete"].Value.ToString();
return(cr);
}
public override int GetHashCode()
{
return(ColumnRow.GetHashCode());
}
public override bool Equals(object obj)
{
var squareBeingCompared = obj as Square;
return(ColumnRow.Equals(squareBeingCompared.ColumnRow, StringComparison.OrdinalIgnoreCase));
}
public void AddColumn(PKColumn column)
{
ColumnRow.Add(column);
}
/// <summary>
/// Create a column description used in a Create Table command
/// </summary>
/// <param name="dr">A DataGridViewRow with column information</param>
/// <param name="i">Relative location of the DataGridViewRow</param>
/// <returns></returns>
protected string CreateColumnEntry(DataGridViewRow dr, int i)
{
StringBuilder sb = new StringBuilder();
// Assign to local variables for readability
// ColName and ColType have already been tested to insure values are not null
ColumnRow cr = MoveGridRow(dr);
sb.Append("\"").Append(cr.ColName).Append("\" ");
sb.Append(cr.ColType);
sb.Append(cr.ColAllowNulls ? " Null" : " Not Null");
if (cr.ColUnique)
{
sb.Append(" Unique");
}
if (!string.IsNullOrEmpty(cr.ColDefault))
{
sb.Append(" Default ");
// If Column Type is a Text Type, wrap the default value in Quotes.
// Note that Default values that are functions must be preceeded by a "("
if (!cr.ColDefault.Trim().StartsWith("(") || Common.IsText(cr.ColType))
{
sb.Append("\"").Append(cr.ColDefault).Append("\"");
}
else
{
sb.Append(cr.ColDefault);
}
}
if (!string.IsNullOrEmpty(cr.ColCheck))
{
sb.Append(" Check").Append(cr.ColCheck);
}
if (!string.IsNullOrEmpty(cr.ColCollation))
{
sb.Append(" Collate ").Append(cr.ColCollation);
}
if (cr.ColPrimaryKey > 0)
{
PrimaryKeys[cr.ColPrimaryKey - 1] = cr.ColName;
}
//Start constructing the Foriegn Key clause
if (!string.IsNullOrEmpty(cr.FK_Table))
{
if (sbFKeyClause.Length != 0)
{
sbFKeyClause.Append(",\r\n\t");
}
sbFKeyClause.Append("Foreign Key (\"").Append(cr.ColName).Append("\") References \"").Append(cr.FK_Table).Append("\"(\"").Append(cr.FK_Column).Append("\")");
if (!string.IsNullOrEmpty(cr.FK_OnDelete))
{
sbFKeyClause.Append(" On Delete ").Append(cr.FK_OnDelete);
}
if (!string.IsNullOrEmpty(cr.FK_OnUpdate))
{
sbFKeyClause.Append(" On Update ").Append(cr.FK_OnUpdate);
}
}
return(sb.ToString());
}
/// <summary>
/// Validate all input and flag errors one at a time.
/// </summary>
/// <returns>False if input errors are found, otherwise true</returns>
private bool ValidateInput()
{
// Table Name is required
if (string.IsNullOrEmpty(txtTableName.Text))
{
errorMsg = "Please enter a Table Name.";
txtTableName.Focus();
return(false);
}
// Loop through all rows to insure fields are populated correctly
for (int i = 0; i < dgvTableDef.RowCount - 1; i++)
{
DataGridViewRow dr = dgvTableDef.Rows[i];
ColumnRow cr = MoveGridRow(dr);
//Column name is required
if (string.IsNullOrEmpty(cr.ColName))
{
errorMsg = "Column Name is required.";
dgvTableDef.CurrentCell = dr.Cells["ColName"];
dgvTableDef.BeginEdit(true);
return(false);
}
// Let's make sure column names are not duplicated
for (int j = 0; j < dgvTableDef.RowCount - 1; j++)
{
if (j == i)
{
continue;
}
string jColName = dgvTableDef.Rows[j].Cells["ColName"].Value == null ? string.Empty : dgvTableDef.Rows[j].Cells["ColName"].Value.ToString().Trim();
if (jColName == cr.ColName)
{
errorMsg = "Column Names must be unique.";
dgvTableDef.CurrentCell = dgvTableDef.Rows[j].Cells["ColName"];
dgvTableDef.BeginEdit(true);
return(false);
}
}
//Column type is required
if (string.IsNullOrEmpty(cr.ColType))
{
errorMsg = "Column Type is required.";
dgvTableDef.CurrentCell = dr.Cells["ColType"];
dgvTableDef.BeginEdit(true);
return(false);
}
//Default Value must be consistent with datatype
if (!string.IsNullOrEmpty(cr.ColDefault))
{
if (!ValidateDefault(cr.ColType, cr.ColDefault))
{
dgvTableDef.CurrentCell = dr.Cells["ColDefault"];
dgvTableDef.BeginEdit(true);
return(false);
}
}
//Foreign Key Validation
// If all FK Columns are empty just return with no error
if (String.IsNullOrEmpty(cr.FK_Table) && string.IsNullOrEmpty(cr.FK_Column) && string.IsNullOrEmpty(cr.FK_OnUpdate) && string.IsNullOrEmpty(cr.FK_OnDelete))
{
return(true);
}
// If th parent column is empty, find the column that's not empty and return an error.
if (String.IsNullOrEmpty(cr.FK_Table))
{
if (!string.IsNullOrEmpty(cr.FK_Column))
{
errorMsg = "You cannot enter a Foreign Key Column without a Foreign Key Parent Table.";
propertyGridTable.Focus(); // Best I can do for now
return(false);
}
if (!string.IsNullOrEmpty(cr.FK_OnUpdate))
{
errorMsg = "You cannot enter an Update Action without a Foreign Key Parent Table and Foreign Key Column.";
propertyGridTable.Focus(); // Best I can do for now
return(false);
}
if (!string.IsNullOrEmpty(cr.FK_OnDelete))
{
errorMsg = "You cannot enter an Update Action without a Foreign Key Parent Table and Foreign Key Column.";
propertyGridTable.Focus(); // Best I can do for now
return(false);
}
}
//Lastly, make sure a Foriegn Key Column is selected
if (string.IsNullOrEmpty(cr.FK_Column))
{
errorMsg = "You must select a Foreigh Key Column.";
propertyGridTable.Focus(); // Best I can do for now
return(false);
}
}
// Validate the Primary Key sequence, if any exists
// Primary Key values must be sequential - starting with '1' - and unique. Additionally, there
// can be no gaps in the sequence.
bool[] Keys = new bool[dgvTableDef.RowCount];
int MaxKey = 0;
for (int i = 0; i < dgvTableDef.RowCount - 1; i++)
{
int ColPrimaryKey = 0;
if (dgvTableDef.Rows[i].Cells["ColPrimaryKey"].Value != null)
{
Int32.TryParse(dgvTableDef.Rows[i].Cells["ColPrimaryKey"].Value.ToString(), out ColPrimaryKey);
}
if (ColPrimaryKey != 0)
{
Keys[ColPrimaryKey] = true; MaxKey = ColPrimaryKey;
}
}
if (MaxKey > 0)
{
for (int i = 1; i <= MaxKey; i++)
{
if (!Keys[i])
{
errorMsg = "Primary Key continuity error." + Environment.NewLine;
errorMsg += "Please insure Primary Key values start a 1 and increment sequentially by 1 for each column included in the Primary Key.";
return(false);
}
}
}
return(true);
}
