Example #1
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 #2
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 #3
0
 private static bool IsDBVersionExists(IDBUpdaterInteractionContext f)
 {
     using (var c = new SqlCommand("select * from sys.tables where name = 'sysDBVersion'", f.Connection))
     {
         using (var r = c.LexExecuteReader())
         {
             return r.Read();
         }
     }
 }
Example #4
0
        public static object GetScriptsToRun(IDBUpdaterInteractionContext context)
        {
            var runned = new List<string>();
            var version = 0;
            var files = new List<string>(Directory.GetFiles(context.DBScriptsPath, "*.sql"));
            using (var c = new SqlCommand("select * from sysDBVersion", context.Connection))
            {
                using (var r = c.LexExecuteReader())
                {
                    while (r.Read())
                    {
                        version = Math.Max(r.GetInt32(0), version);
                        runned.Add(r.GetString(1));
                    }
                }
            }

            for (int i = 0; i < files.Count; i++)
            {
                files[i] = Path.GetFileNameWithoutExtension(files[i]);
            }

            foreach (var r in runned)
            {
                var ind = files.FindIndex(sc => sc.EndsWith(r));
                if (ind >= 0)
                {
                    files.RemoveAt(ind);
                }
            }
            return new KeyValuePair<IList<string>, IList<string>>(runned, files);
        }