public void Rename(string newName)
{
string tmpName = this.Name;
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
SqlTransaction tran = null;
try
{
tran = conn.BeginTransaction();
DbCmd.ExecuteCommand(GetDropScript(), conn, tran);
this.Name = newName;
DbCmd.ExecuteCommand(GetCreateScript(), conn, tran);
tran.Commit();
}
catch (Exception ex)
{
this.Name = tmpName;
if (tran != null)
{
tran.Rollback();
}
throw ex;
}
}
LoadBasicPropertiesByName();
}
public static void Drop(ConnectionParams cp, string hostTableName, string FkName)
{
DbCmd.ExecuteCommand(
"ALTER TABLE " + hostTableName
+ " DROP CONSTRAINT " + FkName
, cp);
}
public string GetColumns(string columnName)
{
string strColName = "";
string cmdText = "";
cmdText += " SELECT COL_NAME(fkeyid,fkey) as columnName";
cmdText += " FROM sysforeignkeys";
cmdText += " WHERE constid = " + ID.ToString() + " AND COL_NAME(rkeyid,rkey) = '" + columnName + "' ORDER BY keyno";
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
SqlDataReader readerKey = DbCmd.ExecuteReader(cmdText, conn);
try
{
while (readerKey.Read())
{
strColName = readerKey["columnName"].ToString();
}
}
finally
{
readerKey.Close();
readerKey.Dispose();
}
}
return(strColName);
}
public void LoadProperties( )
{
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);
this.HostTable.ID = Convert.ToInt64(reader.GetInt32(1));
this.HostTable.LoadProperties();
this.HostTable.LoadColumns();
this.RefTable.ID = Convert.ToInt64(reader.GetInt32(2));
this.RefTable.LoadProperties();
this.RefTable.LoadColumns();
}
}
finally
{
reader.Close();
}
}
}
public bool DropSelectedUsers(bool confirm)
{
if (grd.SelectedRows.Count == 0)
{
return(false);
}
if (confirm && !MessageService.AskQuestion("Drop selected users?"))
{
return(false);
}
//delete login from every all associated databases
foreach (DataGridViewRow row in grd.SelectedRows)
{
if (row.Cells[1].Value == null || row.Cells[1].GetType() == typeof(DBNull))
{
continue;
}
DbCmd.DropUser(_cp, row.Cells[1].Value.ToString(), _cp.Database);
}
RefreshUsers();
return(true);
}
public void SetTableName( )
{
string cmdText = "";
cmdText += "SELECT OBJECT_NAME(I.id) as tableName, I.id as tableID FROM sysindexes I ";
cmdText += " WHERE I.name = '" + this.NormalizedName + "' AND (((I.status & 0x800)=0x800) OR ((I.status & 0x1000)=0x1000)) ";
cmdText += " ORDER BY I.indid";
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
SqlDataReader reader = DbCmd.ExecuteReader(cmdText, conn);
try
{
while (reader.Read())
{
_hostTable.ID = Convert.ToInt64(reader[1].ToString());
_hostTable.LoadProperties();
_hostTable.LoadColumns();
}
}
finally
{
reader.Close();
reader.Dispose();
}
}
}
private void DropLogin( )
{
//delete login from every all associated databases
foreach (DataGridViewRow row in grd.Rows)
{
if (checkBox1.Checked && !row.Selected)
{
continue;
}
if (row.Cells[1].Value == null || row.Cells[1].GetType() == typeof(DBNull))
{
continue;
}
if (row.Cells[0].Value == null || row.Cells[0].GetType() == typeof(DBNull))
{
continue;
}
DbCmd.DropUser(_cp, row.Cells[1].Value.ToString(), row.Cells[0].Value.ToString());
}
if (!checkBox1.Checked)
{
DbCmd.DeleteLogin(_cp, _isWindowsLogin, _loginName);
}
}
private void RenameCheck( )
{
string newName = CheckName;
if (InputDialog.ShowDialog("Rename Check Constraint", "New Name", ref newName) != DialogResult.OK)
{
return;
}
if (CheckName.ToLowerInvariant() == newName.ToLowerInvariant())
{
return;
}
DbCmd.RenameCheck(_cp, _owner, _checkName, newName);
CheckName = newName;
txtName.Text = CheckName;
if (_afterCheckRenamed != null)
{
_afterCheckRenamed(this, EventArgs.Empty);
}
}
private void PopulateDatabaseOptions( )
{
bsOptions.DataSource = null;
_tblOptions = DbCmd.PrepareDatabaseOptionsTable(_cp);
bsOptions.DataSource = _tblOptions;
}
public void RefreshPrimaryKeys(bool loadProps, TableWrapper initialTable)
{
string keyName = String.Empty;
try
{
_initializing = true;
keyName = SelectedKey != null?SelectedKey.ToString() : String.Empty;
DbCmd.PopulatePrimaryKeysCombo(cmbPk, _cp, loadProps, (initialTable != null ? initialTable.ID : -1));
_initialTable = initialTable;
DbCmd.PopulateUserDefinedTablesCombo(cmbTables, _cp, (initialTable != null ? initialTable.ID : -1));
}
finally
{
_initializing = false;
}
if (cmbPk.Items.Count > 0)
{
cmbPk.SelectedIndex = cmbPk.FindStringExact(keyName);
if (cmbPk.SelectedIndex < 0)
{
cmbPk.SelectedIndex = 0;
}
}
else
{
CreateNewKey();
}
}
private void PopulateChecks( )
{
bsChecks.DataSource = null;
DataTable tbl = DbCmd.GetCheckConstraints(_cp);
bsChecks.DataSource = tbl;
}
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 PopulateCheckDefinition( )
{
string ruleDef = DbCmd.GetCheckDefinition(_cp, _owner, _checkName);
Content = ruleDef;
_originalDefinition = ruleDef;
}
private void UpdateCheckDefinition( )
{
try
{
//Create temporary check
string tmpCheck = "tempCheck_" + Guid.NewGuid().ToString().Replace('-', '_');
DbCmd.CreateCheck(_cp, Owner, TableName, tmpCheck, Content, chkNotForRep.Checked);
//Create backup of the original
if (chkBackup.Checked)
{
DbCmd.CreateCheck(_cp, Owner, TableName, CheckName + "_bak_" + Utils.GetFormattedNow(), _originalDefinition, _originalNoRep);
}
//Drop original
DbCmd.DropCheck(_cp, Owner, TableName, CheckName);
//Rename temp to original
DbCmd.RenameCheck(_cp, Owner, tmpCheck, CheckName);
_originalDefinition = Content;
//CreateCheck(false);
SetModified(false);
if (_afterDefinitionUpdated != null)
{
_afterDefinitionUpdated(this, EventArgs.Empty);
}
}
catch (Exception ex)
{
MessageService.ShowError("Can not update check constraint definitions!\nError Message:" + ex.Message);
}
}
private void PopulateUsers()
{
bsUsers.DataSource = null;
DataTable tbl = DbCmd.GetUsers(_cp.Database, _cp);
bsUsers.DataSource = tbl;
}
private void PopulateIndexes( )
{
bsIndexes.DataSource = null;
_tblIndexes = null;
_tblIndexes = DbCmd.GetIndexes(_cp, _objectId);
bsIndexes.DataSource = _tblIndexes;
}
private void CheckUserOnline( )
{
string cmdText = "SELECT sid FROM master.dbo.sysprocesses WHERE sid=SUSER_SID('" + Utils.ReplaceQuatations(_loginName) + "')";
DataTable tbl = DbCmd.ExecuteDataTable(cmdText, _cp);
checkBox1.Checked = !(tbl == null || tbl.Rows.Count == 0);
}
private void LoadDatabases( )
{
bs.DataSource = null;
DataTable tbl = DbCmd.GetDatabasesAsDataTable(_cp);
if (tbl.Rows.Count == 0)
{
return;
}
string cmdText = "declare @r table(";
cmdText += " databasename text null,";
cmdText += " username text null) ";
foreach (DataRow row in tbl.Rows)
{
cmdText += " insert into @r ";
cmdText += "SELECT '" + row["name"].ToString().Replace("'", "''") + "' as databasename, name as username FROM " + row["name"].ToString().Replace("'", "''") + ".dbo.sysusers WHERE SUSER_SNAME(sid)='" + _loginName.Replace("'", "''") + "'";
}
cmdText += " select * from @r ";
bs.DataSource = DbCmd.ExecuteDataTable(cmdText, _cp);
}
private void PopulateHostColumnsCombo( )
{
_tblCols.Clear();
colHostColumns.DataSource = null;
_tblHostCols.Clear();
TableWrapper hostTable = cmbHostTables.SelectedItem as TableWrapper;
if (hostTable == null)
{
return;
}
DataRow row = null;
DataTable tbl = DbCmd.GetColumnsSimple(_cp, hostTable.NormalizedFullName);
foreach (DataRow colRow in tbl.Rows)
{
row = _tblHostCols.NewRow();
row["colName"] = colRow["colName"];
_tblHostCols.Rows.Add(row);
}
colHostColumns.DataSource = _tblHostCols;
colHostColumns.DisplayMember = "colName";
colHostColumns.ValueMember = "colName";
}
private void PopulateColumns( )
{
_tblCols.Clear();
TableWrapper hostTable = cmbRefTables.SelectedItem as TableWrapper;
NameIdPair pkConstraint = cmbRefPk.SelectedItem as NameIdPair;
ForeignKeyWrapper fk = cmbFk.SelectedItem as ForeignKeyWrapper;
if (hostTable == null || pkConstraint == null || fk == null)
{
return;
}
DataTable tbl = DbCmd.GetColumnsByForeignKey(_cp, hostTable.ID, pkConstraint.Id);
DataRow newRow = null;
foreach (DataRow row in tbl.Rows)
{
newRow = _tblCols.NewRow();
newRow["refCol"] = row["colName"];
newRow["hostCol"] = fk.GetColumns((string)row["colName"]);
_tblCols.Rows.Add(newRow);
}
bsCols.DataSource = _tblCols;
}
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 void InitializePrimaryKeys(TableKeyEditorMode mode, bool loadProps, TableWrapper initialTable)
{
try
{
_initializing = true;
Mode = mode;
DbCmd.PopulatePrimaryKeysCombo(cmbPk, _cp, loadProps, (initialTable != null ? initialTable.ID : -1));
_initialTable = initialTable;
DbCmd.PopulateUserDefinedTablesCombo(cmbTables, _cp, (initialTable != null ? initialTable.ID : -1));
}
finally
{
_initializing = false;
}
if (cmbPk.Items.Count > 0)
{
cmbPk.SelectedIndex = 0;
}
else
{
CreateNewKey();
}
}
private void PopulateRoles()
{
bsRoles.DataSource = null;
DataTable tbl = DbCmd.GetRoles(_cp, _cp.Database);
bsRoles.DataSource = tbl;
}
/// <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();
}
}
}
}
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 PopulateAllIndexes( )
{
bsIndexes.DataSource = null;
_tblIndexes = null;
_tblIndexes = DbCmd.GetAllIndexes(_cp);
bsIndexes.DataSource = _tblIndexes;
}
private void PopulateRoles(string dbName, short dbId)
{
string roleName = String.Empty;
SqlDataReader reader = null;
using (SqlConnection conn = _cp.CreateSqlConnection(true))
{
reader = DbCmd.GetRolesAsDataReader(dbName, conn);
IList <ListViewItem> roles = new List <ListViewItem>();
try
{
while (reader.Read())
{
roleName = (string)reader["name"];
if (roleName.ToLowerInvariant() == "public")
{
continue;
}
ListViewItem item = new ListViewItem(roleName);
roles.Add(item);
}
_roleMap.Add(dbId, roles);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
}
private void CreateCheck(bool fireEvent)
{
string err = String.Empty;
if (!ValidateRuleDefinition(ref err))
{
MessageService.ShowError(err);
return;
}
CheckTableSpec tblSpec = cmbTable.SelectedItem as CheckTableSpec;
DbCmd.CreateCheck(_cp, tblSpec.Owner, tblSpec.Name, txtName.Text, Content, chkNotForRep.Checked);
Mode = EditMode.Modify;
CheckName = tblSpec.Name;
Owner = tblSpec.Owner;
TableName = cmbTable.Text;
CheckId = DbCmd.GetCheckConstraintId(_cp, CheckName);
CheckEnabled = true;
_originalDefinition = Content;
_originalNoRep = chkNotForRep.Checked;
SetModified(false);
if (fireEvent && _afterCheckCreated != null)
{
_afterCheckCreated(this, EventArgs.Empty);
}
}
private void PopulateDbsAndLanguages( )
{
cmbDb.Items.Clear();
DataTable tbl = DbCmd.GetDatabasesAsDataTable(_cp);
DataRow row = tbl.NewRow();
row["description"] = "<Default>";
row["name"] = String.Empty;
tbl.Rows.Add(row);
cmbDb.DataSource = tbl;
cmbDb.DisplayMember = "description";
cmbDb.ValueMember = "name";
cmbLanguage.Items.Clear();
tbl = DbCmd.GetLanguages(_cp);
row = tbl.NewRow();
row["description"] = "<Default>";
row["alias"] = String.Empty;
tbl.Rows.Add(row);
cmbLanguage.DataSource = tbl;
cmbLanguage.DisplayMember = "description";
cmbLanguage.ValueMember = "alias";
}
private void PopulateRuleDepends( )
{
bsDepends.DataSource = null;
_tblDepends = null;
_tblDepends = DbCmd.GetRuleDepends(_cp, _ruleId);
bsDepends.DataSource = _tblDepends;
}
/// <summary>
///
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public ProcessResult ExecuteCommand(DbCmd command)
{
try
{
if (command == null)
{
throw new ArgumentNullException("command");
}
if (command.Ontology == null)
{
throw new ArgumentNullException("ontology");
}
if (command.Client == null)
{
throw new ArgumentNullException("client");
}
var db = this.GetEntityDb(command.Ontology);
Sql sqlObj = new Sql(command.Ontology, command.Client.Id.ToString(), command.CommandId, command.ActionType, command.LocalEntityId, command.InfoValue, db.CreateParameter);
if (!sqlObj.IsValid)
{
return new ProcessResult(false, Status.ExecuteFail, sqlObj.Description);
}
else
{
if (!command.IsDumb)
{
int n = db.ExecuteNonQuery(sqlObj.Text, sqlObj.Parameters);
if (n == 0)
{
if (command.ActionType == DbActionType.Insert)
{
return new ProcessResult(false, Status.AlreadyExist, "已经创建");
}
else
{
return new ProcessResult(false, Status.ExecuteFail, "目标记录不存在");
}
}
else if (n > 1)
{
return new ProcessResult(new AnycmdException("Id:" + command.CommandId + ",意外的影响行数" + n.ToString()));
}
}
return new ProcessResult(true, Status.ExecuteOk, "执行成功");
}
}
catch (Exception ex)
{
command.Ontology.Host.LoggingService.Error(ex);
return new ProcessResult(ex);
}
}
