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); }