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