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));
}
internal static Dictionary <string, ColumnLayout> GetColumns(string TableName, SQLiteCommand cmd)
{
Dictionary <string, ColumnLayout> ColumnLayouts = new Dictionary <string, ColumnLayout>();
cmd.CommandText = string.Format(QRY_COLUMNS, TableName);
DataTable dt = ExecuteDataTable(cmd, out SQLiteErrorCode returnCode);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
ColumnLayout cl = new ColumnLayout
{
Cid = Convert.ToInt64(dr["cid"]),
ColumnType = dr["type"].ToString(),
NullType = Convert.ToInt64(dr["notnull"]),
DefaultValue = dr["dflt_value"].ToString(),
PrimaryKey = Convert.ToInt64(dr["pk"])
};
ColumnLayouts.Add(dr["name"].ToString(), cl);
}
}
return(ColumnLayouts);
}
private void dgMain_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
int i = e.RowIndex;
int j = e.ColumnIndex;
string datacolumn = dgMain.Columns[e.ColumnIndex].Name;
ColumnLayout cl = Common.FindColumnLayout(TableName, datacolumn);
if (!Common.ValidateData(cl.ColumnType, dgMain.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString(), out string szValue))
{
Common.ShowMsg("Invalid value");
dgMain.Focus();
dgMain.CurrentCell = dgMain.Rows[e.RowIndex].Cells[e.ColumnIndex];
dgMain.BeginEdit(true);
}
}
private void InitProps()
{
txtColumn.Text = TargetNode.Tag.ToString();
txtColumn.ReadOnly = true;
ColumnLayout cl = Common.FindColumnLayout(tablename, txtColumn.Text);
props.Type = GetColumnType(cl.ColumnType);
props.Size = GetColumnSize(cl.ColumnType);
props.DefaultValue = cl.DefaultValue;
props.AllowNull = cl.NullType == 0 ? true : false;
props.CollatingSequence = cl.Collation;
props.ForeignKeyColumn = cl.ForeignKey.From;
props.ForeignKeyParent = cl.ForeignKey.Table;
props.ForeignKeyOnDelete = cl.ForeignKey.OnDelete;
props.ForeignKeyOnUpdate = cl.ForeignKey.OnUpdate;
}
internal ColumnLayout BuildColumnLayout()
{
ColumnLayout col = new ColumnLayout();
col.Check = string.Empty;
col.Collation = props.CollatingSequence;
col.ColumnType = BuildColumnType();
col.DefaultValue = props.DefaultValue;
col.ForeignKey = new ForeignKeyLayout();
col.ForeignKey.Table = props.ForeignKeyParent;
col.ForeignKey.To = props.ForeignKeyColumn;
col.ForeignKey.OnUpdate = props.ForeignKeyOnUpdate;
col.ForeignKey.OnDelete = props.ForeignKeyOnDelete;
col.NullType = props.AllowNull ? 0 : 1;
col.PrimaryKey = 0;
col.Unique = false;
return(col);
}
protected string BuildCreateSql(string TableName, Dictionary <string, DBColumn> columns)
{
// Remap foreign column layout to internal SQLite Column Layout
Dictionary <string, ColumnLayout> SQColumns = new Dictionary <string, ColumnLayout>();
foreach (var col in columns)
{
if (col.Value.IncludeInImport)
{
ColumnLayout SQCol = new ColumnLayout();
SQCol.Check = string.Empty;
SQCol.Collation = string.Empty;
SQCol.ColumnType = col.Value.Type;
SQCol.DefaultValue = col.Value.HasDefault ? col.Value.DefaultValue : string.Empty;
// Foreign key will not be used during Import
SQCol.ForeignKey = new ForeignKeyLayout();
SQCol.NullType = col.Value.IsNullable ? 0 : 1;
SQCol.PrimaryKey = col.Value.PrimaryKey;
SQCol.Unique = col.Value.IsUnique;
SQColumns.Add(col.Value.Name, SQCol);
}
}
return(SqlFactory.CreateSQL(TableName, SQColumns));
}
/// <summary>
/// Create a column description used in a Create Table command
/// </summary>
/// <param name="ColumnName">The Name of the column.</param>
/// <param name="column">A ColumnLayout structure defining the characteristics of the column.</param>
/// <returns></returns>
protected static string CreateColumnEntry(string ColumnName, ColumnLayout column)
{
StringBuilder sb = new StringBuilder();
sb.Append("\"").Append(ColumnName).Append("\" ");
sb.Append(column.ColumnType == "autoincrement" ? "integer primary key autoincrement" : column.ColumnType);
sb.Append(column.NullType == 0 ? " Null" : " Not Null");
if (column.Unique)
{
sb.Append(" Unique");
}
if (!string.IsNullOrEmpty(column.DefaultValue))
{
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 (!column.DefaultValue.Trim().StartsWith("(") || Common.IsText(column.ColumnType))
{
sb.Append("\"").Append(column.DefaultValue).Append("\"");
}
else
{
sb.Append(column.DefaultValue);
}
}
if (!string.IsNullOrEmpty(column.Check))
{
sb.Append(" Check").Append(column.Check);
}
if (!string.IsNullOrEmpty(column.Collation))
{
sb.Append(" Collate ").Append(column.Collation);
}
return(sb.ToString());
}
/// <summary>
/// Generate SQL needed to add a column to a table.
/// </summary>
/// <param name="TableName">Name of the table being modified.</param>
/// <param name="ColumnName">Name of the column being added.</param>
/// <param name="column">Column descriptor containing attributes of the new column.</param>
/// <returns>Sql to add a new column.</returns>
internal static string BuildAddColumnSQL(string TableName, string ColumnName, ColumnLayout column)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("Alter Table \"{0}\" \r\n Add Column {1}", TableName, CreateColumnEntry(ColumnName, column));
return(sb.ToString());
}
protected bool UpdateRecord(DataRow dr)
{
StringBuilder sb = new StringBuilder();
StringBuilder sbWhere = new StringBuilder();
SQLiteErrorCode returnCode;
sb.Append("Update \"").Append(TableName).Append("\" Set ");
ArrayList parms = new ArrayList();
ArrayList wparms = new ArrayList();
int ColumnsToUpdate = 0;
for (int i = 0; i < dr.Table.Columns.Count; i++)
{
if (dgMain.Columns[i].Visible)
{
if (!dr[i, DataRowVersion.Proposed].Equals(dr[i, DataRowVersion.Current]))
{
if (ColumnsToUpdate > 0)
{
sb.Append(",");
}
sb.Append("\"").Append(dr.Table.Columns[i].ColumnName).Append("\" = ?");
ColumnsToUpdate++;
ColumnLayout cl = Common.FindColumnLayout(TableName, dr.Table.Columns[i].ColumnName);
if (!Common.ValidateData(cl.ColumnType, dr[i, DataRowVersion.Proposed].ToString(), out string szValue))
{
Common.ShowMsg(string.Format("Invalid value entered for Column [{0}]", dr.Table.Columns[i].ColumnName));
return(false);
}
parms.Add(dr[i, DataRowVersion.Proposed]);
}
}
}
if (ColumnsToUpdate == 0)
{
return(true);
}
//sb.Append(")");
//Create where clause
sbWhere.Clear();
if (tableHasRowID)
{
sbWhere.AppendFormat("Where {0} = ", RowIDColName).Append(dr[0].ToString());
}
else if (!string.IsNullOrEmpty(PrimaryKeyName))
{
sbWhere.AppendFormat("Where {0} = ", PrimaryKeyName).Append(dr[PrimaryKeyName].ToString());
}
else
{
sbWhere.Append("Where ");
for (int i = 0; i < dr.Table.Columns.Count - 1; i++)
{
sbWhere.Append("\"").Append(dr.Table.Columns[i].ColumnName).Append("\" = ?");
wparms.Add(dr[i, DataRowVersion.Current]);
sbWhere.Append(" And ");
}
sbWhere.Append("\"").Append(dr.Table.Columns[dr.Table.Columns.Count - 1].ColumnName).Append("\" = ?");
wparms.Add(dr[dr.Table.Columns.Count - 1, DataRowVersion.Current]);
var count = DataAccess.ExecuteScalar(DatabaseName, string.Format("Select Count(*) From \"{0}\" {1}", TableName, sbWhere.ToString()), wparms, out returnCode);
if (Convert.ToInt32(count) != 1)
{
Common.ShowMsg(Common.ERR_MULTIUPDATE);
return(false);
}
}
parms.AddRange(wparms);
var result = DataAccess.ExecuteNonQuery(DatabaseName, string.Format("{0} {1}", sb.ToString(), sbWhere.ToString()), parms, out returnCode);
if (returnCode != SQLiteErrorCode.Ok)
{
MessageBox.Show(string.Format("Update failed: {0}{1}Error Code: {2}", DataAccess.LastError, Environment.NewLine, returnCode.ToString()), Common.APPNAME, MessageBoxButtons.OK, MessageBoxIcon.Error);
toolStripStatusLabelMsg.Text = "0 Records Updated";
return(false);
}
toolStripStatusLabelMsg.Text = string.Format("{0} Record(s) Updated", result.ToString());
return(true);
}
