Example #1
0
        private bool PerformSelectDataBase()
        {
            string serverInstance = tbServerInstance.Text.Trim();
            string dbName = tbDBName.Text.Trim();

            UseWaitCursor = true;
            bool disposeConnetion = true;
            var s = new SqlConnection(new SqlConnectionStringBuilder
            {
                DataSource = serverInstance,
                IntegratedSecurity = true,
            }.ToString());
            var Dbs = new List<string>();
            try
            {
                s.Open();
                var c = new SqlCommand("EXEC sp_databases", s);
                using (var r = c.LexExecuteReader())
                {
                    while (r.Read())
                    {
                        Dbs.Add(r.GetString(0));
                    }
                }

                if (Dbs.IndexOf(dbName) < 0)
                {
                    if (MessageBox.Show(this, string.Format("DataBase '{0}' was not found in {1}. Do you want to create it?", dbName, serverInstance), "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        c.CommandText = @"CREATE DATABASE [" + dbName + "]";
                        c.LexExecuteNonQuery();
                    }
                    else
                    {
                        return false;
                    }
                }

                s.Close();
                s.ConnectionString = new SqlConnectionStringBuilder
                {
                    DataSource = serverInstance,
                    IntegratedSecurity = true,
                    InitialCatalog = dbName
                }.ToString();
                s.Open();

                disposeConnetion = false;
                var f = new frmDBStatus(s, this, dbName);
                f.Show(this);
                return true;
            }
            catch (SqlException se)
            {
                MessageBox.Show(this, se.Message, string.Format("Error on openning '{0}'", serverInstance), MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            finally
            {
                UseWaitCursor = false;
                if (disposeConnetion)
                {
                    s.Dispose();
                }
            }
        }
Example #2
0
        public static void ReCreateDataBase(IDBUpdaterUserInteractionContext view)
        {
            try
            {
                var dbname = view.DBName;

                view.Connection.Close();
                SqlConnection.ClearAllPools();

                using (var newConnection = new SqlConnection(new SqlConnectionStringBuilder
                {
                    DataSource = view.Connection.DataSource,
                    IntegratedSecurity = true
                }.ToString()))
                {
                    newConnection.Open();

                    using (var c = new SqlCommand(GetDBUsageSql(dbname), newConnection))
                    {
                        var ok = true;
                        var sb = new StringBuilder("Database is using by following clients:");
                        sb.AppendLine();
                        using (var r = c.ExecuteReader())
                        {
                            while (r.Read())
                            {
                                string status = r.GetString(0).Trim();
                                string host = r.GetString(1).Trim();
                                string prg = r.GetString(2).Trim();

                                if ((host.IsNotNull() || prg.IsNotNull()))
                                {
                                    ok = false;
                                    sb.AppendLine(string.Format("{0} | {1} | {2}", status, host, prg));
                                }
                            }
                        }
                        if (!ok)
                        {
                            sb.AppendLine();
                            sb.Append("Close ALL connection?");
                            if (
                                MessageBox.Show(sb.ToString(), "Close confirmation", MessageBoxButtons.YesNo,
                                                MessageBoxIcon.Exclamation) == DialogResult.Yes)
                            {
                                c.CommandText =
                                    string.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE",
                                                  dbname);
                                c.CommandType = CommandType.Text;
                                c.LexExecuteNonQuery();
                            }
                            else
                                return;
                        }
                    }

                    using (var c = new SqlCommand(string.Format("DROP DATABASE [{0}]", dbname), newConnection))
                    {
                        c.LexExecuteNonQuery();

                        c.CommandText = string.Format("CREATE DATABASE [{0}]", dbname);
                        c.LexExecuteNonQuery();
                    }
                }

                view.Connection.Open();

                CreateSysDbVersionTable(view);
            }
            finally
            {
                if (view.Connection.State != ConnectionState.Open)
                {
                    view.Connection.Open();
                }
                view.AsyncEnQueueOperation(GetScriptsToRun, "Getting scripts to run...");
            }
        }
Example #3
0
        private static void CreateSysDbVersionTable(IDBUpdaterUserInteractionContext f)
        {
            using (var c = new SqlCommand(CreateSysDbVersionTableScript, f.Connection))
            {
                c.LexExecuteNonQuery();

                c.CommandText = CreateSysDbVersionTriggerScript;
                c.LexExecuteNonQuery();

                c.CommandText = GetDBVersionProcSql(0, false);
                c.LexExecuteNonQuery();

                c.CommandText = CreateUpgradeDBProcedure;
                c.LexExecuteNonQuery();
            }
        }
Example #4
0
        public static Action<IDBUpdaterUserInteractionContext> UpgrateToNextVersion(int dbVersion, string sqlscript, string scriptFileName)
        {
            return frmDBStatus =>
            {
                using (var transaction = frmDBStatus.Connection.BeginTransaction(IsolationLevel.ReadUncommitted))
                {
                    using (var cmd = new SqlCommand("select dbo.GetDBVersion()", transaction.Connection)
                    {
                        Transaction = transaction
                    })
                    {
                        try
                        {
                            var v = cmd.LexExecuteScalar<int>();
                            if (v != dbVersion)
                            {
                                throw new InvalidDBVersionException(v);
                            }

                            foreach (var cmdText in SplitSqlCommands(sqlscript))
                            {
                                cmd.CommandText = cmdText;
                                cmd.LexExecuteNonQuery();
                            }

                            cmd.CommandText = GetDBVersionProcSql(dbVersion + 1, true);
                            cmd.ExecuteScalar();

                            cmd.CommandText = "INSERT INTO sysDBVersion (VersionNumber, ScriptFileName, Date) VALUES (@version, @filename, @date)";
                            cmd.Parameters.Assign(new
                            {
                                version = dbVersion,
                                filename = scriptFileName,
                                date = DateTime.Now
                            });
                            cmd.LexExecuteNonQuery();

                            transaction.Commit();
                        }
                        catch
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
            };
        }
Example #5
0
        public static object DropDataBase(IDBUpdaterInteractionContext context)
        {
            var dbname = context.DBName;

            context.Connection.Close();
            SqlConnection.ClearAllPools();

            using (var newConnection = GetNewConnectionWithoutDBRefference(context.Connection))
            {
                newConnection.Open();

                using (var c = new SqlCommand(GetDBUsageSql(dbname), newConnection))
                {
                    var ok = true;
                    var sb = new StringBuilder("Database is using by following clients:");
                    sb.AppendLine();
                    using (var r = c.LexExecuteReader())
                    {
                        while (r.Read())
                        {
                            string status = r.GetString(0).Trim();
                            string host = r.GetString(1).Trim();
                            string prg = r.GetString(2).Trim();

                            if ((host.IsNotNull() || prg.IsNotNull()))
                            {
                                ok = false;
                                sb.AppendLine(string.Format("{0} | {1} | {2}", status, host, prg));
                            }
                        }
                    }
                    if (!ok)
                    {
                        sb.AppendLine();
                        sb.Append("Close ALL connection?");
                        if (
                            MessageBox.Show(sb.ToString(), "Close confirmation", MessageBoxButtons.YesNo,
                                            MessageBoxIcon.Exclamation) == DialogResult.Yes)
                        {
                            c.CommandText =
                                string.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE",
                                              dbname);
                            c.CommandType = CommandType.Text;
                            c.LexExecuteNonQuery();
                        }
                        else
                            return false;
                    }
                }

                using (var c = new SqlCommand(string.Format("DROP DATABASE [{0}]", dbname), newConnection))
                {
                    c.LexExecuteNonQuery();
                }
            }
            return true;
        }
Example #6
0
        public static object CreateDataBase(IDBUpdaterInteractionContext context)
        {
            var dbname = context.DBName;
            using (var newConnection = GetNewConnectionWithoutDBRefference(context.Connection))
            {
                context.Connection.Close();

                newConnection.Open();
                using (var c = new SqlCommand(string.Format("CREATE DATABASE [{0}]", dbname), newConnection))
                {
                    c.LexExecuteNonQuery();
                }
            }

            context.Connection.ConnectionString = new SqlConnectionStringBuilder(context.Connection.ConnectionString) { InitialCatalog = dbname }.ToString();

            context.Connection.Open();

            CreateSysDbVersionTable(context);
            return true;
        }