private string IsNullableString(APGenColumn column) { return column.IsNullable ? "null" : "not null"; }
private string DF_name(APGenTable table, APGenColumn column) { return "DF_" + table.TableName + "_" + column.ColumnName; }
// private void _createCurrentIdProc(APDatabase db) // { // string sql = //@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ap_Query_NewId]') AND type in (N'P', N'PC')) //BEGIN //EXEC dbo.sp_executesql @statement = N' //CREATE PROCEDURE [dbo].[ap_Query_NewId] //( // @name nvarchar(512) //) //AS //BEGIN // // UPDATE ap_query_mapid // SET value = value + 1 // WHERE name = @name // // SELECT value // FROM ap_query_mapid // WHERE name = @name // //END //' //END //"; // DbCommand cmd = db.CreateSqlCommand(sql); // cmd.ExecuteNonQuery(); // } #endregion #region [ Helper ] private string DBTypeName(APGenColumn column) { if (column.DBType == DbType.Object) { // auto detect dbtype Type dataType = column.ParsedType; if (column.IsEnum || dataType == null || dataType.IsEnum) { return "int"; } else { if (dataType.IsGenericType && dataType.IsValueType) dataType = Nullable.GetUnderlyingType(dataType); if (dataType == typeof(bool)) return "bit"; else if (dataType == typeof(byte)) return "tinyint"; else if (dataType == typeof(byte[])) { if (column.DataLength == 0 || column.DataLength > 8000) return "image"; else return "varbinary(" + column.DataLength.ToString() + ")"; } else if (dataType == typeof(DateTime)) return "datetime"; else if (dataType == typeof(decimal)) { if (column.Precision >= 1 && column.Precision <= 38 && column.Scale >= 0 && column.Scale <= column.Precision) return String.Format("decimal({0}, {1})", column.Precision, column.Scale); else ThrowColumnParsedTypeNotImplemented(dataType); return null; } else if (dataType == typeof(double)) return "float"; else if (dataType == typeof(float)) return "real"; else if (dataType == typeof(Guid)) return "uniqueidentifier"; else if (dataType == typeof(short)) return "smallint"; else if (dataType == typeof(int)) return "int"; else if (dataType == typeof(long)) return "bigint"; else if (dataType == typeof(string)) { if (column.DataLength == 0 || column.DataLength > 4000) return "ntext"; else return "nvarchar(" + column.DataLength.ToString() + ")"; } else { ThrowColumnParsedTypeNotImplemented(dataType); return null; } } } else { switch (column.DBType) { case DbType.Boolean: return "bit"; case DbType.Byte: return "tinyint"; case DbType.Binary: { if (column.DataLength == 0 || column.DataLength > 8000) return "image"; else return "varbinary(" + column.DataLength.ToString() + ")"; } case DbType.DateTime: return "datetime"; case DbType.Decimal: { if (column.Precision >= 1 && column.Precision <= 38 && column.Scale >= 0 && column.Scale <= column.Precision) return String.Format("decimal({0}, {1})", column.Precision, column.Scale); else ThrowColumnParsedTypeNotImplemented(column.DBType); return null; } case DbType.Double: return "float"; case DbType.Single: return "real"; case DbType.Guid: return "uniqueidentifier"; case DbType.Int16: return "smallint"; case DbType.Int32: return "int"; case DbType.Int64: return "bigint"; case DbType.String: if (column.DataLength == 0 || column.DataLength > 4000) return "ntext"; else return "nvarchar(" + column.DataLength.ToString() + ")"; case DbType.AnsiString: if (column.DataLength == 0 || column.DataLength > 8000) return "text"; else return "varchar(" + column.DataLength.ToString() + ")"; case DbType.AnsiStringFixedLength: if (column.DataLength == 0) return "char(10)"; else return "char(" + column.DataLength.ToString() + ")"; case DbType.Currency: return "money"; case DbType.StringFixedLength: if (column.DataLength == 0) return "nchar(10)"; else return "nchar(" + column.DataLength.ToString() + ")"; default: ThrowColumnParsedTypeNotImplemented(column.DBType); return null; } } }
private string DF_constraint(APGenTable table, APGenColumn column) { if (column.DBDefaultValue != "") return String.Format("constraint {0} default {1}", DF_name(table, column), column.DBDefaultValue); return ""; }
private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info) { if (info.dfname != "") _dropConstraint(db, table, info.dfname); DbCommand dbCmd; // no safe mode to change identity, so ingone this, throw sql exception. if (info.isnullable && !column.IsNullable) { dbCmd = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName); if ((int)dbCmd.ExecuteScalar() > 0) { // when column nullable change to not nullable and has data, // set default value. dbCmd = db.CreateSqlCommand("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, info.GetDefaultValue()); dbCmd.ExecuteNonQuery(); } } dbCmd = db.CreateSqlCommand("alter table {0} alter column {1} {2} {3}", table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column)); dbCmd.ExecuteNonQuery(); if (column.DBDefaultValue != "") { dbCmd = db.CreateSqlCommand("alter table {0} add {1} for {2}", table.TableName, DF_constraint(table, column), column.ColumnName); dbCmd.ExecuteNonQuery(); } // alter table table_nae alter column column_name type_name [not] null // go // alter table add constraint DF_tablename_columnname default dfvalue for column_name // go }
private void _createColumn(APDatabase db, APGenTable table, APGenColumn column, bool isTableEmpty) { bool changeNullable = (!isTableEmpty && !column.IsNullable); if (changeNullable) { column.IsNullable = true; } string sql; if (column.IdentityType == APColumnIdentityType.Database) sql = string.Format("alter table {0} add {1} {2} IDENTITY {3} {4}", table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column), DF_constraint(table, column)); else sql = string.Format("alter table {0} add {1} {2} {3} {4}", table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column), DF_constraint(table, column)); DbCommand dbCmd = db.CreateSqlCommand(sql); dbCmd.ExecuteNonQuery(); if (changeNullable) { column.IsNullable = false; string defaultValue = column.DBDefaultValue == "" ? "''" : column.DBDefaultValue; dbCmd = db.CreateSqlCommand("update {0} set {1} = {2}", table.TableName, column.ColumnName, defaultValue); dbCmd.ExecuteNonQuery(); dbCmd = db.CreateSqlCommand("alter table {0} alter column {1} {2} {3}", table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column)); dbCmd.ExecuteNonQuery(); } }
private string DF_constraint(APGenTable table, APGenColumn column) { if (column.DBDefaultValue != "") return String.Format("default {0}", column.DBDefaultValue); return "default null"; }
private void _createColumn(APDatabase db, APGenTable table, APGenColumn column, bool isTableEmpty) { string sql = ""; if (column.IdentityType == APColumnIdentityType.Database) ThrowDBUnsupport("identityType=\"Database\""); else sql = string.Format("alter table {0} add {1} {2} {3} {4}", table.TableName, column.ColumnName, DBTypeName(column), DF_constraint(table, column), IsNullableString(column)); DbCommand dbCmd = db.CreateSqlCommand(sql); dbCmd.ExecuteNonQuery(); }
private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info) { DbCommand dbCmd; int totalCount, nullCount = 0; dbCmd = db.CreateSqlCommand("select count(*) from {0}", table.TableName, column.ColumnName); totalCount = Convert.ToInt32(dbCmd.ExecuteScalar()); if (totalCount > 0) { dbCmd = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName); nullCount = Convert.ToInt32(dbCmd.ExecuteScalar()); } if (totalCount > 0) { if (!_sameType(DBTypeName(column), info.GetTypeFullName()) && nullCount != totalCount) { // change db type, the column value must be null if (!info.isnullable) { dbCmd = db.CreateSqlCommand("alter table {0} modify {1} null", table.TableName, column.ColumnName); dbCmd.ExecuteNonQuery(); info.isnullable = true; } _tempSql(String.Format("update {0} set {1} = null where {1} is not null", table.TableName, column.ColumnName)); nullCount = totalCount; } } string sqlPadding = ""; if (DBTypeName(column) != info.GetTypeFullName()) sqlPadding += " " + DBTypeName(column); if (column.DBDefaultValue != info.dfvalue) sqlPadding += " " + DF_constraint(table, column); bool lazyNotNullable = false; if (column.IsNullable != info.isnullable) { if (column.IsNullable || nullCount == 0) sqlPadding += " " + IsNullableString(column); else lazyNotNullable = true; } if (sqlPadding != "") { dbCmd = db.CreateSqlCommand("alter table {0} modify {1}", table.TableName, column.ColumnName); dbCmd.CommandText += sqlPadding; dbCmd.ExecuteNonQuery(); } if (lazyNotNullable) { string dfv = column.DBDefaultValue; if (dfv == "") dfv = "0"; _tempSql(String.Format("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, dfv)); dbCmd = db.CreateSqlCommand("alter table {0} modify {1} not null", table.TableName, column.ColumnName); dbCmd.ExecuteNonQuery(); } // alter table table_nae modify column_name type_name default dfvalue [not] null // go }
/// <summary> /// Check whether the column can be a identity column rely on Database. /// </summary> /// <param name="column">Column definition.</param> /// <returns>true means the column can be a identity column, otherwise false.</returns> public bool CanIdentityRelyOnDatabase(APGenColumn column) { return column.IsEnum || column.ParsedType == typeof(sbyte) || column.ParsedType == typeof(short) || column.ParsedType == typeof(int) || column.ParsedType == typeof(long) || column.ParsedType == typeof(byte) || column.ParsedType == typeof(ushort) || column.ParsedType == typeof(uint) || column.ParsedType == typeof(ulong) || column.ParsedType == typeof(decimal); }
/// <summary> /// Check whether the column can be a identity column rely on Provider. /// </summary> /// <param name="column">Column definition.</param> /// <returns>true means the column can be a identity column, otherwise false.</returns> public bool CanIdentityRelyOnProvider(APGenColumn column) { return column.IsEnum || column.ParsedType == typeof(sbyte) || column.ParsedType == typeof(short) || column.ParsedType == typeof(int) || column.ParsedType == typeof(long) || column.ParsedType == typeof(byte) || column.ParsedType == typeof(ushort) || column.ParsedType == typeof(uint) || column.ParsedType == typeof(ulong) || column.ParsedType == typeof(decimal) || column.ParsedType == typeof(float) || column.ParsedType == typeof(double) || column.ParsedType == typeof(Guid); }