private void btnAttach_Click(object sender, EventArgs e)
{
string err = String.Empty;
if (!String.IsNullOrEmpty(fsLog.Path))
{
if (!ValidateFiles(ValidationType.Both, ref err))
{
MessageService.ShowError(err);
return;
}
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
DbCmd.Attach(conn, txtName.TextBoxText, fsData.Path, fsLog.Path);
DialogResult = DialogResult.OK;
}
}
else
{
if (!ValidateFiles(ValidationType.Data, ref err))
{
MessageService.ShowError(err);
return;
}
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
DbCmd.AttachSingle(conn, txtName.TextBoxText, fsData.Path);
DialogResult = DialogResult.OK;
}
}
}
private void PopulateDatabasesAndRoles( )
{
bsDbs.DataSource = null;
_tblDbs.Clear();
_roleMap.Clear();
SqlDataReader reader = null;
using (SqlConnection conn = _cp.CreateSqlConnection(true))
{
reader = DbCmd.GetDatabasesAsDataReader(conn);
DataRow row = null;
try
{
while (reader.Read())
{
row = _tblDbs.NewRow();
row["Database"] = reader["name"];
row["Id"] = reader["dbid"];
row["Map"] = false;
_tblDbs.Rows.Add(row);
PopulateRoles((string)row["Database"], (short)row["Id"]);
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
bsDbs.DataSource = _tblDbs;
}
private void PopulateRoles( )
{
lvRoles.Items.Clear();
string roleName = String.Empty;
SqlDataReader reader = null;
using (SqlConnection conn = _cp.CreateSqlConnection(true))
{
reader = DbCmd.GetRolesAsDataReader(conn.Database, conn);
try
{
while (reader.Read())
{
roleName = (string)reader["name"];
if (roleName.ToLowerInvariant() == "public")
{
continue;
}
ListViewItem item = new ListViewItem(roleName);
lvRoles.Items.Add(item);
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
}
public void LoadData( )
{
_bs.DataSource = null;
_tbl.Clear();
string script = String.Empty;
switch (_refDetail)
{
case RefDetail.Any:
script = ResManager.GetDBScript("Script_ReferencesAny");
break;
case RefDetail.Objects:
script = ResManager.GetDBScript("Script_ReferencesAny");
break;
default:
throw new Exception("RefDetail value specified is not supported!");
}
using (SqlConnection conn = _connParams.CreateSqlConnection(true, false))
{
script = String.Format(script, _objName);
SqlCommand cmd = new SqlCommand(script, conn);
cmd.CommandTimeout = 0;
_adapter.SelectCommand = cmd;
_adapter.Fill(_tbl);
}
RefreshBindings();
}
public void LoadProperties( )
{
string cmdText = "declare @cmplevel int select @cmplevel = cmptlevel from master..sysdatabases where name = DB_NAME() ";
cmdText += "SELECT name, CASE WHEN @cmplevel < 90 THEN USER_NAME(uid) ELSE SCHEMA_NAME(uid) END as owner FROM systypes S "
+ " WHERE xusertype > 256 AND usertype = " + ID.ToString();
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
SqlDataReader reader = DbCmd.ExecuteReader(cmdText, conn);
try
{
while (reader.Read())
{
this.Name = reader.GetString(0);
this.Owner = reader.GetString(1);
}
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
}
}
/// <summary>
/// Load index identity properties
/// </summary>
public void LoadProperties( )
{
string cmdText = String.Format(ResManager.GetDBScript("Script_GetIndexProperties"), ID, OwnerObjectId);
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
SqlDataReader reader = DbCmd.ExecuteReader(cmdText, conn);
try
{
while (reader.Read())
{
this.Name = reader.GetString(0);
this.Owner = reader.GetString(1);
this.OwnerObjectId = reader.GetInt32(2);
this.OwnerObjectName = reader.GetString(3);
}
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
}
}
public void LoadServerLocks()
{
try
{
lblProgress.Text = "Refreshing locks...";
Application.DoEvents();
tblInfo.Clear();
string cmdText = "";
cmdText += "SELECT master.dbo.sysprocesses.spid, rtrim(master.dbo.sysprocesses.status) as status, master.dbo.syslockinfo.rsc_type, master.dbo.spt_values.name, SUSER_SNAME(master.dbo.sysprocesses.sid) AS UserName, DB_NAME(master.dbo.syslockinfo.rsc_dbid) AS DB, OBJECT_NAME(master.dbo.syslockinfo.rsc_objid) AS Object, RTRIM(master.dbo.sysprocesses.hostname) AS HostName, RTRIM(master.dbo.sysprocesses.program_name) AS Application, master.dbo.sysprocesses.cmd AS Command, master.dbo.sysprocesses.cpu AS CPU, master.dbo.sysprocesses.physical_io AS IO, master.dbo.sysprocesses.memusage AS MemUsage";
cmdText += " FROM master.dbo.syslockinfo INNER JOIN master.dbo.sysprocesses ON master.dbo.syslockinfo.req_spid = master.dbo.sysprocesses.spid INNER JOIN master.dbo.spt_values ON master.dbo.syslockinfo.rsc_type = master.dbo.spt_values.number";
cmdText += " WHERE (master.dbo.spt_values.type = 'L')";
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, conn);
adapter.Fill(tblInfo);
}
bs.DataSource = tblInfo;
grd.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
}
finally
{
lblProgress.Text = String.Empty;
}
}
public void LoadServerProcesses()
{
try
{
lblProgress.Text = "Refreshing processes...";
Application.DoEvents();
tblInfo.Clear();
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
SqlDataAdapter adapter = new SqlDataAdapter("SELECT spid, rtrim(status) as Status, SUSER_SNAME(sid) as UserName, rtrim(hostname) as hostname"
+ ", rtrim(program_name) as program_name, memusage, cpu, blocked"
+ ", CASE WHEN dbid=0 THEN '' ELSE DB_NAME(dbid) END as DB"
+ ", cmd, rtrim(nt_domain) as nt_domain, rtrim(nt_username) as nt_username"
+ ", net_address, net_library FROM master.dbo.sysprocesses (nolock) ORDER BY spid"
, conn);
adapter.Fill(tblInfo);
}
bs.DataSource = tblInfo;
grd.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
}
finally
{
lblProgress.Text = String.Empty;
}
}
public void PopulateSystemPrivileges( )
{
SqlDataReader reader = null;
DataRow row = null;
bsPrivileges.DataSource = null;
InitializeSystemPrivilegesTable();
using (SqlConnection conn = _cp.CreateSqlConnection(true))
{
try
{
reader = DbCmd.GetSystemPrivilegesAsDataReader(conn, Principal);
string action = String.Empty;
string protectType = String.Empty;
while (reader.Read())
{
if (!Utils.IsReaderItemValid(reader, "ProtectType"))
{
continue;
}
protectType = (string)reader["ProtectType"];
if (protectType.ToLowerInvariant().Trim() != "grant")
{
continue;
}
action = (string)reader["Action"];
row = _tblPrivileges.Rows.Find(action.Trim());
if (row == null)
{
continue;
}
row[0] = true;
row[1] = true;
}
}
catch (Exception ex)
{
string errMsg = "System privileges not available! Principal: {0}, Error message: {1}";
errMsg = String.Format(errMsg, _principal, ex.Message);
HostServicesSingleton.HostServices.MsgService.ErrorMsg(errMsg, (MethodInfo)MethodInfo.GetCurrentMethod());
}
finally
{
if (reader != null)
{
reader.Close();
}
}
bsPrivileges.DataSource = _tblPrivileges;
SetModified(false);
}
}
private void LoadServerVersion()
{
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
SqlDataAdapter adapter = new SqlDataAdapter("EXEC master.dbo.xp_msver", conn);
adapter.Fill(tblInfo);
}
bs.DataSource = tblInfo;
}
public void LoadPropsUsingId( )
{
string cmdText = String.Empty;
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
cmdText = "declare @cmplevel int select @cmplevel = cmptlevel from master..sysdatabases where name = DB_NAME()\r\n";
cmdText += "SELECT so.name, CASE WHEN @cmplevel < 90 THEN USER_NAME(so.uid) ELSE SCHEMA_NAME(so.uid) END 'owner' FROM sysobjects so WHERE so.id = " + ID.ToString();
SqlDataReader reader = null;
try
{
reader = TableWrapper.ExecuteReader(cmdText, conn);
while (reader.Read())
{
this.Name = reader.GetString(0);
this.Owner = reader.GetString(1);
}
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
try
{
cmdText = @" select distinct fg.groupname from sysindexes i
join sysobjects so on i.id = so.id
join sysfilegroups fg on i.groupid = fg.groupid
where so.id = " + ID.ToString();
reader = TableWrapper.ExecuteReader(cmdText, conn);
if (reader.HasRows)
{
reader.Read();
this.FileGroup = reader.GetString(0);
}
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
}
}
public void PopulateUsers( )
{
bsUsers.Sort = String.Empty;
bsUsers.Filter = String.Empty;
bsUsers.DataSource = null;
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
_tblUsers = DbCmd.GetUsersInRole(conn, conn.Database, _roleName);
_tblUsers.ColumnChanged += new DataColumnChangeEventHandler(_tblRoles_ColumnChanged);
bsUsers.DataSource = _tblUsers;
bsUsers.Sort = "isin DESC";
SetModified(false);
}
}
private void PopulateServerRoles( )
{
_tblRoles.Clear();
DataRow row = null;
DataTable isInTbl = null;
string isInRoleCmd = "SELECT IS_SRVROLEMEMBER('{0}','{1}') as isin";
string cmdText = "EXEC sp_helpsrvrole";
SqlDataReader reader = DbCmd.ExecuteReader(cmdText, _cp.CreateSqlConnection(true));
try
{
while (reader.Read())
{
row = _tblRoles.NewRow();
row["ServerRole"] = reader["ServerRole"];
row["isin"] = false;
isInTbl = DbCmd.ExecuteDataTable(String.Format(isInRoleCmd, (string)reader["ServerRole"], Utils.ReplaceQuatations(_loginName)), _cp);
if (isInTbl != null && isInTbl.Rows.Count != 0)
{
if (isInTbl.Rows[0].ItemArray[0] != null && isInTbl.Rows[0].ItemArray[0].GetType() != typeof(DBNull))
{
switch (isInTbl.Rows[0].ItemArray[0].ToString())
{
case "1":
row["isin"] = true;
break;
default:
break;
}
}
}
isInTbl = null;
_tblRoles.Rows.Add(row);
}
bsRoles.DataSource = _tblRoles;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public void Execute(ConnectionParams cp, string commandText)
{
ClearAll();
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
try
{
using (SqlConnection conn = cp.CreateSqlConnection(true, false))
{
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandText = commandText;
adapter.SelectCommand = cmd;
adapter.Fill(dataSet);
}
}
finally
{
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (adapter != null)
{
adapter.Dispose();
adapter = null;
}
}
RenderDataSet(dataSet);
}
private void PopulateDatabases(string defaultDatabaseName)
{
cmbDatabases.Items.Clear();
using (SqlConnection conn = _connParams.CreateSqlConnection(true, false))
{
DataTable dbs = conn.GetSchema("Databases");
dbs.DefaultView.Sort = "database_name";
dbs = dbs.DefaultView.ToTable();
int dbIndex = -1;
foreach (DataRow row in dbs.Rows)
{
string dbName = (string)row["database_name"];
cmbDatabases.Items.Add(dbName);
if (defaultDatabaseName.ToLowerInvariant() == dbName.ToLowerInvariant())
{
cmbDatabases.SelectedIndex = cmbDatabases.Items.Count - 1;
dbIndex = cmbDatabases.SelectedIndex;
}
}
if (dbIndex == -1)
{
cmbDatabases.Items.Add(defaultDatabaseName);
cmbDatabases.SelectedIndex = cmbDatabases.Items.Count - 1;
}
}
}
public static Version GetServerVersion(ConnectionParams cp)
{
Version result = null;
using (SqlConnection conn = cp.CreateSqlConnection(false, false))
{
ServerConnection sqlConn = null;
try
{
sqlConn = new ServerConnection(conn);
Server srvr = new Server(sqlConn);
result = srvr.Information.Version;
}
finally
{
if (sqlConn != null)
{
if (sqlConn.InUse)
{
sqlConn.Cancel();
}
if (sqlConn.IsOpen)
{
sqlConn.Disconnect();
}
}
}
}
return(result);
}
public bool DropSelectedChecks(bool confirm)
{
if (grd.SelectedRows.Count == 0)
{
return(false);
}
if (confirm && !MessageService.AskQuestion("Are you sure you want to drop selected check constraints?"))
{
return(false);
}
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
foreach (DataGridViewRow row in grd.SelectedRows)
{
if (!Utils.IsGridRowItemValid(row, 1) || !Utils.IsGridRowItemValid(row, 2) || !Utils.IsGridRowItemValid(row, 3))
{
continue;
}
DbCmd.DropCheck(conn, (string)row.Cells[1].Value, (string)row.Cells[2].Value, (string)row.Cells[3].Value);
}
}
RefreshChecks();
return(true);
}
public static void ExecuteCommand(string cmdText, ConnectionParams cp)
{
if (cp == null)
{
throw new Exception("Connection parameters not specified!");
}
ExecuteCommand(cmdText, cp.CreateSqlConnection(true, false));
}
public static DataTable ExecuteDataTable(string cmdText, ConnectionParams cp)
{
if (cp == null)
{
throw new Exception("Connection parameters not specified!");
}
return(ExecuteDataTable(cmdText, cp.CreateSqlConnection(true, false)));
}
private void UpdateRoles( )
{
string errorText = "Roles can not be changed!\n";
string action = String.Empty;
bool hasErrors = false;
using (SqlConnection conn = _cp.CreateSqlConnection(true))
{
foreach (DataRow row in _tblRoles.Rows)
{
if (!Utils.IsRowItemValid(row, 0) || !Utils.IsRowItemValid(row, 1))
{
continue;
}
try
{
if ((bool)row.ItemArray[0])
{
if (DbCmd.IsUserInRole(conn, _userName, (string)row.ItemArray[1]))
{
continue;
}
action = "ADD";
DbCmd.AddUserToRole(conn, _userName, (string)row.ItemArray[1]);
}
else
{
if (!DbCmd.IsUserInRole(conn, _userName, (string)row.ItemArray[1]))
{
continue;
}
action = "DROP";
DbCmd.DropUserFromRole(conn, _userName, (string)row.ItemArray[1]);
}
}
catch (Exception ex)
{
errorText += "Action: " + action + " , Role: " + (string)row.ItemArray[1] + ", Error:" + ex.Message + "\n";
hasErrors = true;
}
}
}
if (hasErrors)
{
MessageService.ShowError(errorText);
}
SetModified(false);
}
public static bool TruncateLogs(ConnectionParams cp)
{
if (!MessageService.AskQuestion("Are you sure you want to truncate logs for the selected database?\nDatabase name: " + cp.Database))
{
return(false);
}
ConnectionParams tmp = cp.CreateCopy();
tmp.Database = "master";
using (SqlConnection conn = tmp.CreateSqlConnection(true, false))
{
DbCmd.TruncLog(conn, cp.Database);
}
return(true);
}
public static bool DropDatabase(ConnectionParams cp, long dbid)
{
if (!MessageService.AskQuestion("Are you sure you want to drop the selected database?\nDatabase name: " + cp.Database))
{
return(false);
}
ConnectionParams tmp = cp.CreateCopy();
tmp.Database = "master";
using (SqlConnection conn = tmp.CreateSqlConnection(true, false))
{
DbCmd.DropDb(conn, cp.Database, dbid);
}
return(true);
}
public DependencyExplorer(ConnectionParams cp)
{
if (cp == null)
{
throw new ArgumentNullException("cp", "Connection parameters object is null!");
}
ConnParams = cp;
using (SqlConnection conn = _connParams.CreateSqlConnection(false, false))
{
sqlConn = new ServerConnection(conn);
srvr = new Server(sqlConn);
db = srvr.Databases[_connParams.Database];
}
}
private void btnShrink_Click(object sender, EventArgs e)
{
string err = String.Empty;
if (!ValidateOptions(ref err))
{
MessageService.ShowError(err);
return;
}
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
DbCmd.ShrinkDB(conn, conn.Database, txtPrecent.Text, cmbOption.Text);
DialogResult = DialogResult.OK;
}
}
private void PopulateLogins( )
{
string cmdText = "USE [master]; ";
cmdText += " SELECT sid,loginname, isntname, isntuser, hasaccess, dbname, createdate, master.dbo.syslanguages.alias as language"
+ " FROM master.dbo.syslogins , master.dbo.syslanguages"
+ " WHERE ((master.dbo.syslogins.language like master.dbo.syslanguages.alias or master.dbo.syslogins.language like master.dbo.syslanguages.name) )"
+ " ORDER BY loginname";
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, conn);
_tbl.Clear();
adapter.Fill(_tbl);
}
bs.DataSource = _tbl;
}
public BatchScripter(ConnectionParams cp)
{
if (cp == null)
{
throw new ArgumentNullException("cp", "Connection parameters object is null!");
}
ConnParams = cp;
using (SqlConnection conn = _connParams.CreateSqlConnection(false, false))
{
sqlConn = new ServerConnection(conn);
srvr = new Server(sqlConn);
db = srvr.Databases[_connParams.Database];
scrp = new Scripter(srvr);
scrp.ScriptingProgress += new ProgressReportEventHandler(scrp_ScriptingProgress);
}
}
public bool CreateRole( )
{
string err = String.Empty;
if (!ValidateRoleDefinition(ref err))
{
MessageService.ShowError(err);
return(false);
}
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
string serverVersion = DbCmd.QueryServerVersion(conn);
DbCmd.CreateRole(serverVersion, conn, _cp.Database, txtName.Text, cmbOwner.Text, rbStandart.Checked, txtPwd.Text);
}
return(true);
}
public static void PopulateForeignKeysCombo(ComboBox cmb, ConnectionParams cp, string tableName)
{
if (cmb == null)
{
return;
}
cmb.Items.Clear();
using (SqlConnection conn = cp.CreateSqlConnection(true, false))
{
ServerConnection srvConn = new ServerConnection(conn);
Server srv = new Server(srvConn);
Database db = srv.Databases[srvConn.DatabaseName];
Table hostTbl = db.Tables[tableName];
ForeignKeyWrapper key = null;
foreach (ForeignKey fk in hostTbl.ForeignKeys)
{
key = new ForeignKeyWrapper(cp);
//Load properties
key.ID = fk.ID;
key.Name = fk.Name;
key.HostTable.ID = fk.Parent.ID;
key.HostTable.Name = fk.Parent.Name;
key.HostTable.Owner = fk.Parent.Schema;
key.HostTable.FileGroup = fk.Parent.FileGroup;
key.HostTable.LoadColumns(fk.Parent);
Table refTbl = db.Tables[fk.ReferencedTable];
key.RefTable.ID = refTbl.ID;
key.RefTable.Name = refTbl.Name;
key.RefTable.Owner = refTbl.Schema;
key.RefTable.FileGroup = refTbl.FileGroup;
key.RefTable.LoadColumns(refTbl);
cmb.Items.Add(key);
}
}
}
public void LoadBasicProperties( )
{
string cmdText = " SELECT dbo.sysobjects.name, dbo.sysforeignkeys.fkeyid, dbo.sysforeignkeys.rkeyid, dbo.sysobjects.id ";
cmdText += " FROM dbo.sysobjects INNER JOIN dbo.sysforeignkeys ON dbo.sysobjects.id = dbo.sysforeignkeys.constid";
cmdText += " WHERE (dbo.sysobjects.id = " + ID.ToString() + ")";
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
SqlDataReader reader = DbCmd.ExecuteReader(cmdText, conn);
try
{
while (reader.Read())
{
this.Name = reader.GetString(0);
}
}
finally
{
reader.Close();
}
}
}
public bool DropSelectedRoles(bool confirm)
{
if (grd.SelectedRows.Count == 0)
{
return(false);
}
bool removeUsers = false;
if (confirm && DropRoleConfirmation.ShowConfirmation(ref removeUsers) != DialogResult.Yes)
{
return(false);
}
string roletype = String.Empty;
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
foreach (DataGridViewRow row in grd.SelectedRows)
{
if (row.Cells[2].Value == null || row.Cells[2].GetType() == typeof(DBNull))
{
continue;
}
//Remove users
if (removeUsers)
{
DbCmd.RemoveUsersFromRole(conn, row.Cells[2].Value.ToString());
}
roletype = (string)row.Cells[3].Value;
DbCmd.DropRole(conn, _cp.Database, row.Cells[2].Value.ToString(), roletype.ToLower() == "standard");
}
}
RefreshRoles();
return(true);
}
