public static void UpdateTableStructure(this SQLiteConnection @this, string targetTable, SQLiteTable newStructure) { newStructure.Name = targetTable + "_temp"; @this.CreateTable(newStructure); @this.CopyAllData(targetTable, newStructure.Name); @this.DropTable(targetTable); @this.RenameTable(newStructure.Name, targetTable); }
public void CreateTable(SQLiteTable table) { StringBuilder sb = new System.Text.StringBuilder(); sb.Append("create table if not exists `"); sb.Append(table.TableName); sb.AppendLine("`("); bool firstRecord = true; foreach (SQLiteColumn col in table.Columns) { if (col.ColumnName.Trim().Length == 0) { throw new Exception("Column name cannot be blank."); } if (firstRecord) { firstRecord = false; } else { sb.AppendLine(","); } sb.Append(col.ColumnName); sb.Append(" "); if (col.AutoIncrement) { sb.Append("integer primary key autoincrement"); continue; } switch (col.ColDataType) { case ColType.Text: sb.Append("text"); break; case ColType.Integer: sb.Append("integer"); break; case ColType.Decimal: sb.Append("decimal"); break; case ColType.DateTime: sb.Append("datetime"); break; case ColType.BLOB: sb.Append("blob"); break; } if (col.PrimaryKey) { sb.Append(" primary key"); } else if (col.NotNull) { sb.Append(" not null"); } else if (col.DefaultValue.Length > 0) { sb.Append(" default "); if (col.DefaultValue.Contains(" ") || col.ColDataType == ColType.Text || col.ColDataType == ColType.DateTime) { sb.Append("'"); sb.Append(col.DefaultValue); sb.Append("'"); } else { sb.Append(col.DefaultValue); } } } sb.AppendLine(");"); _cmd.CommandText = sb.ToString(); _cmd.ExecuteNonQuery(); }
public static void CreateTable(this SQLiteConnection @this, SQLiteTable table) { var sb = new StringBuilder(); sb.Append("create table if not exists `"); sb.Append(table.Name); sb.AppendLine("`("); bool firstRecord = true; foreach (var col in table.Columns) { if (string.IsNullOrWhiteSpace(col.Name)) { throw new Exception("Column name cannot be blank."); } if (firstRecord) { firstRecord = false; } else { sb.AppendLine(","); } sb.Append(col.Name); sb.Append(" "); if (col.AutoIncrement) { sb.Append("integer primary key autoincrement"); continue; } switch (col.Type) { case ColumnType.Text: sb.Append("text"); break; case ColumnType.Integer: sb.Append("integer"); break; case ColumnType.Decimal: sb.Append("decimal"); break; case ColumnType.DateTime: sb.Append("datetime"); break; case ColumnType.Blob: sb.Append("blob"); break; } if (col.PrimaryKey) { sb.Append(" primary key"); } else if (col.NotNull) { sb.Append(" not null"); } else if (!string.IsNullOrEmpty(col.DefaultValue)) { sb.Append(" default "); if (col.DefaultValue.Contains(" ") || col.Type == ColumnType.Text || col.Type == ColumnType.DateTime) { sb.Append("'"); sb.Append(col.DefaultValue); sb.Append("'"); } else { sb.Append(col.DefaultValue); } } } sb.AppendLine(");"); @this.ExecuteNonQuery(sb.ToString()); }