JLH/JLHHJSvr/DBA/DBVersionSql/DBVersionSql.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace LJLib.DBOption
{
    public abstract class DBVersionSql
    {
        protected sealed class Script
        {
            public Script(string version, string script, string tablename)
            {
                this.version = version;
                this.script = script;
                this.tablename = tablename;
            }
            public readonly string version;
            public readonly string script;
            public readonly string tablename;
        }

        protected abstract string currentVersion { get; }
        protected abstract string dbname { get; }
        protected abstract Script[] scripts { get; }


        public void CreateTables(string connectionString)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            using (SqlCommand cmd = con.CreateCommand())
            {
                con.Open();
                CheckDbVersionTable(cmd);
                AutoUpdate(cmd);
            }
        }

        private void AutoUpdate(SqlCommand cmd)
        {
            cmd.CommandText = "SELECT dbversion FROM sys_dbversion WHERE dbname = @dbname";
            cmd.Parameters.Add("@dbname", SqlDbType.VarChar).Value = dbname;
            object rslt = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            string dbversion = (rslt == null || rslt == DBNull.Value ? "1.0.00000000" : Convert.ToString(rslt));
            if (string.Compare(currentVersion, dbversion) > 0)
            {
                foreach (Script script in scripts)
                {
                    if (string.Compare(script.version, dbversion) > 0)
                    {
                        cmd.CommandText = script.script;
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            Trace.Write(ex.ToString());
                            if (cmd.Connection.State != ConnectionState.Open)
                            {
                                throw;
                            }
                        }
                    }
                }

                cmd.CommandText = "UPDATE sys_dbversion SET dbversion = @dbversion WHERE dbname = @dbname";
                cmd.Parameters.Add("@dbname", SqlDbType.VarChar).Value = dbname;
                cmd.Parameters.Add("@dbversion", SqlDbType.VarChar).Value = currentVersion;
                int nrows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                if (nrows <= 0)
                {
                    cmd.CommandText = "INSERT INTO sys_dbversion(dbname, dbversion) VALUES(@dbname, @dbversion)";
                    cmd.Parameters.Add("@dbname", SqlDbType.VarChar).Value = dbname;
                    cmd.Parameters.Add("@dbversion", SqlDbType.VarChar).Value = currentVersion;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private void CheckDbVersionTable(SqlCommand cmd)
        {
            cmd.CommandText = "SELECT COUNT(0) FROM dbo.sysobjects WHERE id = OBJECT_ID(N'sys_dbversion') AND OBJECTPROPERTY(id, N'IsUserTable') = 1";
            int cnt = Convert.ToInt32(cmd.ExecuteScalar());
            if (cnt <= 0)
            {
                cmd.CommandText = "CREATE TABLE sys_dbversion(dbname varchar(255) not null CONSTRAINT DF_sys_dbversion_dbname default(''),dbversion varchar(255) not null CONSTRAINT DF_sys_dbversion_dbversion default(''),CONSTRAINT PK_sys_dbversion Primary key(dbname asc))";
                cmd.ExecuteNonQuery();
            }
        }
    }
}