/// <summary>
///
/// </summary>
/// <param name="tablename"></param>
/// <param name="colName"></param>
/// <param name="newDataType"></param>
/// <param name="length"></param>
/// <returns></returns>
public bool AddColumn(string tablename, string colName, TemplateColumnDataType newDataType, int length)
{
StringBuilder builder = new StringBuilder();
if (_providerConfig.DbProvider == DbProvider.MySql)
{
builder.AppendFormat("ALTER TABLE `{0}`", tablename);
builder.AppendFormat(" ADD COLUMN `{0}` ", colName);
switch (newDataType)
{
case TemplateColumnDataType.TemplateInt:
builder.Append(" int NULL default 0");
break;
case TemplateColumnDataType.TemplateDatetime:
builder.Append(" datetime NULL");
break;
case TemplateColumnDataType.TemplateDecimal:
builder.AppendFormat(" decimal({0},4) NULL", 19);
break;
case TemplateColumnDataType.TemplateText:
builder.Append(" text ");
break;
default:
if (length < 1)
{
length = 255;
}
builder.AppendFormat(" varchar({0}) NULL DEFAULT ''", length);
break;
}
DbHelperMySql.ExecuteSql(_providerConfig.DbConnectionString, builder.ToString());
}
else
{
builder.AppendFormat("ALTER TABLE {0}", tablename);
builder.AppendFormat(" ADD {0} ", colName);
switch (newDataType)
{
case TemplateColumnDataType.TemplateInt:
builder.Append(" INT NULL default 0");
break;
case TemplateColumnDataType.TemplateDatetime:
builder.Append(" datetime NULL");
break;
case TemplateColumnDataType.TemplateDecimal:
builder.AppendFormat(" decimal({0},4) NULL", 19);
break;
case TemplateColumnDataType.TemplateText:
builder.Append(" ntext ");
break;
default:
if (length < 1)
{
length = 255;
}
builder.AppendFormat(" nvarchar({0}) NULL DEFAULT ''", length);
break;
}
DbHelperSql.ExecuteSql(_providerConfig.DbConnectionString, builder.ToString());
}
return(true);
}
/// <summary>
/// 创建一个只有一个Id主键的空表
/// </summary>
/// <returns></returns>
public bool AddOrAlterColumn(string tablename, string colName, string newName, TemplateColumnDataType newDataType, int length)
{
if (!ColumHasExists(tablename, colName))
{
//如果旧字段不存，那么创建
if (string.IsNullOrEmpty(newName))
{
newName = colName;
}
return(AddColumn(tablename, newName, newDataType, length));
}
StringBuilder builder = new StringBuilder();
if (_providerConfig.DbProvider == DbProvider.MySql)
{
builder.AppendFormat("ALTER TABLE `{0}`", tablename);
if (!string.IsNullOrEmpty(newName) &&
!string.Equals(colName, newName, StringComparison.OrdinalIgnoreCase))
{
builder.AppendFormat(" CHANGE COLUMN `{0}` `{1}`", colName, newName);
}
else
{
builder.AppendFormat(" MODIFY COLUMN `{0}`", colName);
}
switch (newDataType)
{
case TemplateColumnDataType.TemplateInt:
builder.Append(" int NULL default 0");
break;
case TemplateColumnDataType.TemplateDatetime:
builder.Append(" datetime NULL");
break;
case TemplateColumnDataType.TemplateDecimal:
builder.AppendFormat(" decimal({0},4) NULL", length);
break;
default:
if (length < 1)
{
length = 255;
}
builder.AppendFormat(" varchar({0}) NULL DEFAULT ''", length);
break;
}
DbHelperMySql.ExecuteSql(_providerConfig.DbConnectionString, builder.ToString());
}
else
{
if (!string.IsNullOrEmpty(newName) &&
string.Equals(colName, newName, StringComparison.OrdinalIgnoreCase))
{
var sqlClause =
string.Format(" EXECUTE sp_rename N'{0}.{1}', @newname = N'{2}', @objtype = N'COLUMN'", tablename, colName, newName);
DbHelperSql.ExecuteSql(_providerConfig.DbConnectionString, sqlClause);
colName = newName;
}
builder.AppendFormat("ALTER TABLE {0}", tablename);
builder.AppendFormat(" ALTER COLUMN {0} ", colName);
switch (newDataType)
{
case TemplateColumnDataType.TemplateInt:
builder.Append(" INT NULL ");
break;
case TemplateColumnDataType.TemplateDatetime:
builder.Append(" datetime NULL ");
break;
case TemplateColumnDataType.TemplateDecimal:
builder.AppendFormat(" decimal({0},4) NULL ", length);
break;
case TemplateColumnDataType.TemplateText:
builder.Append(" ntext ");
break;
default:
if (length < 1)
{
length = 255;
}
builder.AppendFormat(" nvarchar({0}) ", length);
break;
}
DbHelperSql.ExecuteSql(_providerConfig.DbConnectionString, builder.ToString());
}
return(true);
}
