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