C#通过代码更新MS SQLServer数据结构

大致思路是：将sqlServer的表结构在代码里存起来，根据存起来的和已有的数据表结构对比，进行更新。

• 我们假设已有的数据结构是一个圆，新的结构是另一个圆。
• 为了得到新的结构，需要对两个圆进行分析。绿色的左侧需要删除，交集需要更新，红色右侧需要添加。

 

 

代码如下：

首先定义表的每个列的结构（每个列有各自的名字，类型等属性）：

public class DataBaseTableColumn
    {

        public DataBaseTableColumn(string columnName, string dataType, string isNullable, int maxLength, int numericPrecision, int numericScale)
        {
            ColumnName = columnName;
            Type = dataType;
            IsNullable = isNullable;
            MaxLength = maxLength;
            NumericPrecision = numericPrecision;
            NumericScale = numericScale;
        }

        /// <summary>
        /// 列名
        /// </summary>
        public string ColumnName { get; set; }

        /// <summary>
        /// 类型 int/varchar/char/decimal/datetime/float/nchar
        /// </summary>
        public string Type { get; set; }

        /// <summary>
        /// 是否为空  YES/NO
        /// </summary>
        public string IsNullable { get; set; }

        /// <summary>
        /// 最大长度
        /// </summary>
        public int MaxLength { get; set; }

        /// <summary>
        /// decimal里的precision，位数
        /// </summary>
        public int NumericPrecision { get; set; }


        /// <summary>
        /// decimal 里的scale，小数点后的位数
        /// </summary>
        public int NumericScale { get; set; }

    }

 

定义数据表结构（表有各个字段列表组成）：

   public class DataBaseTableSchema
    {
        public string TableName { get; set; }

        public List<DataBaseTableColumn> Columns { get; set; }
    }

 

定义常用sql语句：

public class ConstDefine
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        public const string CONNECT_STRING =
            "server=.;database=mytest20210106;uid=sa;pwd=123456;MultipleActiveResultSets=true;Persist Security Info=True;";


        /// <summary>
        /// 版本号对应的表结构
        /// </summary>
        public const string VERSION1 = "{\"TableName\":\"WebUsers2\",\"Columns\":[{\"ColumnName\":\"usrID\",\"Type\":\"int\",\"IsNullable\":\"NO\",\"MaxLength\":0,\"NumericPrecision\":10,\"NumericScale\":0},{\"ColumnName\":\"usrFirstName\",\"Type\":\"varchar\",\"IsNullable\":\"NO\",\"MaxLength\":20,\"NumericPrecision\":0,\"NumericScale\":0},{\"ColumnName\":\"usrLastName\",\"Type\":\"varchar\",\"IsNullable\":\"NO\",\"MaxLength\":20,\"NumericPrecision\":0,\"NumericScale\":0},{\"ColumnName\":\"usrMiddleName\",\"Type\":\"char\",\"IsNullable\":\"YES\",\"MaxLength\":1,\"NumericPrecision\":0,\"NumericScale\":0},{\"ColumnName\":\"usrPhonNum\",\"Type\":\"varchar\",\"IsNullable\":\"NO\",\"MaxLength\":20,\"NumericPrecision\":0,\"NumericScale\":0},{\"ColumnName\":\"usrWebAddr\",\"Type\":\"varchar\",\"IsNullable\":\"NO\",\"MaxLength\":100,\"NumericPrecision\":0,\"NumericScale\":0},{\"ColumnName\":\"TEST\",\"Type\":\"nchar\",\"IsNullable\":\"YES\",\"MaxLength\":10,\"NumericPrecision\":0,\"NumericScale\":0}]}";

        /// <summary>
        /// 删除Table的Column，参数0=表名，参数1=列名
        /// </summary>
        public const string DROP_TABLE_COLUMN_SQL = "ALTER TABLE {0} DROP COLUMN {1};\r\n "; //ALTER TABLE table_name DROP COLUMN column_name;

        /// <summary>
        /// 添加Table的Column，参数0=表名，参数1=列名，参数2=列结构，varchar（255）
        /// </summary>
        public const string ADD_TABLE_COLUMN_SQL = "ALTER TABLE {0} ADD {1} {2};\r\n";//ALTER TABLE table_name ADD column varchar(255) NOT NULL;


        /// <summary>
        /// 修改table的column，参数0=表名，参数1=列名，参数2=列结构
        /// </summary>
        public const string ALTER_TABLE_COLUMN_SQL = "ALTER TABLE {0}  ALTER COLUMN {1} {2};\r\n";//ALTER TABLE table_name  ALTER COLUMN column_name datatype; 


        /// <summary>
        /// 查询数据表结构sql语句，参数0=数据库表名称
        /// </summary>
        public const string QUERY_DATA_TABLE_STRUCTURE_SQL = "SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='{0}'";


        /// <summary>
        /// 创建表,参数0=表名
        /// </summary>
        public const string CREATE_TABLE_SQL = "CREATE TABLE [dbo].[{0}] ([ID] nvarchar(36) NOT NULL) ";


        /// <summary>
        /// 获取数据库中所有表
        /// </summary>
        public const string GET_ALL_TABLES = "SELECT [name] FROM SysObjects Where XType='U'";
    }

 

判断两个表结构（旧的和新的）的差别：

 public static string GetOldDatatableSchemaToNewSchemaSql(DataBaseTableSchema oldSchema,
            DataBaseTableSchema newSchema)
        {
            var allSql = new StringBuilder();


            // oldSchema没有，newSchema有，增加new列
            foreach(var newColumn in newSchema.Columns)
            {
                if(!oldSchema.Columns.Exists(oldColumn => oldColumn.ColumnName == newColumn.ColumnName))
                {
                    var addColumnSql = string.Format(ConstDefine.ADD_TABLE_COLUMN_SQL, oldSchema.TableName,
                        newColumn.ColumnName,
                        GetColumnTypeSqlString(newColumn));
                    allSql.Append(addColumnSql);
                }
            }

            //oldSchema有，newSchema没有，删除old列
            foreach(var oldColumn in oldSchema.Columns)
            {
                if(!newSchema.Columns.Exists(newColumn => newColumn.ColumnName == oldColumn.ColumnName))
                {
                    var deleteColumnSql = string.Format(ConstDefine.DROP_TABLE_COLUMN_SQL, oldSchema.TableName,
                        oldColumn.ColumnName);
                    allSql.Append(deleteColumnSql);
                }
            }

            //oldSchema有，newSchema有，但是需要修改的列
            foreach(var newColumn in newSchema.Columns)
            {
                foreach(var oldColumn in oldSchema.Columns)
                {
                    if(newColumn.ColumnName == oldColumn.ColumnName)
                    {
                        var isSame = GetIsSameColumn(newColumn, oldColumn);
                        if(!isSame)
                        {
                            var alterColumnSql = string.Format(ConstDefine.ALTER_TABLE_COLUMN_SQL, oldSchema.TableName,
                                newColumn.ColumnName, GetColumnTypeSqlString(newColumn));
                            allSql.Append(alterColumnSql);
                        }
                    }
                }
            }

            return allSql.ToString();
        }

 

结果（对比后生成sql语句来更新数据库的表结构）：

 

 

源码下载：https://files.cnblogs.com/files/lizhijian/2021-01-11-%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9B%B4%E6%96%B0.rar