/// <summary> /// Initializes a new instance of the <see cref="QueryForm"/> class. /// </summary> /// <param name="target">The target.</param> /// <param name="text">The text.</param> public QueryForm(ConnectionAdapter target, String text) : this() { Query.Connection = new ConnectionAdapter(target); Query.Query.Text = text; Query.Query.Select(0, 0); }
/// <summary> /// Gets the definition. /// </summary> /// <returns></returns> public String getDefinition() { String query = String.Format("exec sp_helptext @objname = '{0}'", Name); SqlConnection myConnection = m_Parent.Connection.connect(); if (myConnection == null) { return(String.Empty); } Table text_table = ConnectionAdapter.executeSelect(myConnection, query); myConnection.Close(); if (text_table.Rows.Count > 0) { String ret = String.Empty; foreach (Row row in text_table.Rows) { ret += row.Values[0].Data; } return(ret); } return(String.Empty); }
/// <summary> /// Gets the definition. /// </summary> /// <returns></returns> public String getDefinition() { String query = String.Format("SELECT * FROM [{0}]", m_Parent.Name); SqlConnection myConnection = m_Parent.Parent.Connection.connect(); if (myConnection == null) { return(String.Empty); } Table text_table = ConnectionAdapter.executeSelect(myConnection, query); myConnection.Close(); if (text_table.Rows.Count > 0) { String ret = String.Empty; foreach (Row row in text_table.Rows) { foreach (Value val in row.Values) { ret += val.Data; } } return(ret); } return(String.Empty); }
private void Button_Exec_Click(object sender, EventArgs e) { SqlConnection conn = m_Connection.connect(); if (conn != null) { try { String query = String.Empty; if (Query.SelectionLength == 0) { query = Query.Text; } else { query = Query.SelectedText; } Table tbl = ConnectionAdapter.executeSelect(conn, query); if (tbl != null) { DataGrid.Rows.Clear(); DataGrid.ColumnHeadersHeight = 40; DataGrid.ColumnCount = tbl.FieldCont.Fields.Count; DataGrid.RowCount = tbl.Rows.Count + 1; // DataGrid.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader; for (int i = 0; i < tbl.FieldCont.Fields.Count; i++) { DataGrid.Columns[i].HeaderCell.Value = tbl.FieldCont.Fields[i].Name; } if (tbl.Rows.Count > 0) { for (int i = 0; i < tbl.Rows.Count; i++) { for (int j = 0; j < tbl.Rows[i].Values.Count; j++) { DataGrid.Rows[i].Cells[j].Value = tbl.Rows[i].Values[j].Data; } } } else { DataGrid.Rows[0].Cells[0].Value = "No result"; } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } else { } }
public QueryControl() { InitializeComponent(); m_Connection = new ConnectionAdapter(); this.Query.KeyDown += new KeyEventHandler(QueryControl_KeyDown); }
/// <summary> /// Initializes a new instance of the <see cref="Base"/> class. /// </summary> public Base() { m_Connection = new ConnectionAdapter(); m_TableCont = new TableContainer(this); m_ViewCont = new ViewContainer(this); m_ProcedureCont = new ProcedureContainer(this); m_FunctionCont = new FunctionContainer(this); }
/// <summary> /// Initializes a new instance of the <see cref="ConnectionAdapter"/> class. /// </summary> /// <param name="target">The target.</param> public ConnectionAdapter(ConnectionAdapter target) : this() { m_Host = target.m_Host; m_User = target.m_User; m_Pass = target.m_Pass; m_Name = target.m_Name; m_WindowsAuth = target.m_WindowsAuth; }
/// <summary> /// Initializes a new instance of the <see cref="ConnectionForm"/> class. /// </summary> /// <param name="Connection">The connection.</param> public ConnectionForm(ConnectionAdapter Connection) : this() { m_Connection = Connection; ConnectionCont.TB_HostName.Text = m_Connection.Host; ConnectionCont.TB_UserName.Text = m_Connection.User; ConnectionCont.TB_UserPass.Text = m_Connection.Pass; ConnectionCont.TB_Database.Text = m_Connection.Name; ConnectionCont.CHK_WindowsAuth.Checked = m_Connection.WindowsAuth; }
private void GetDatabases() { if (m_Connection == null) { return; } SqlConnection connection = m_Connection.connectWithoutDataBaseName(); if (connection != null) { try { Table tbl = ConnectionAdapter.executeSelect(connection, "SELECT NAME FROM sys.databases"); if (tbl != null) { DatabaseList.Items.Clear(); foreach (Row row in tbl.Rows) { DatabaseList.Items.Add(row.Values[0].Data); } if (DatabaseList.Items.Contains(m_Connection.Name)) { DatabaseList.SelectedItem = m_Connection.Name; } else { if (DatabaseList.SelectedItem != null) { m_Connection.Name = DatabaseList.SelectedItem.ToString(); } else if (DatabaseList.Items.Count > 0) { DatabaseList.SelectedItem = DatabaseList.Items[0]; } } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { connection.Close(); } } }
/// <summary> /// Initializes a new instance of the <see cref="Configuration"/> class. /// </summary> public Configuration() { _Version = 1; _Connection1 = new ConnectionAdapter(); _Connection2 = new ConnectionAdapter(); _Connection3 = new ConnectionAdapter(); _ShowReportOnAnalyze = true; _HideUnmodifiedByDefault = false; _AnalyzeTables = true; _AnalyzeViews = true; _AnalyzeProcedures = true; _AnalyzeFunctions = true; _AnalyzeData = false; _UpgradeAllowTableCreate = true; _UpgradeAllowTableDrop = true; _UpgradeAllowTableAlter = true; _UpgradeAllowViewCreate = true; _UpgradeAllowViewDrop = true; _UpgradeAllowViewAlter = true; _UpgradeAllowFieldCreate = true; _UpgradeAllowFieldDrop = true; _UpgradeAllowFieldAlter = true; _UpgradeAllowConstraintCreate = true; _UpgradeAllowConstraintDrop = true; _UpgradeAllowConstraintAlter = true; _UpgradeAllowProcedureCreate = true; _UpgradeAllowProcedureDrop = true; _UpgradeAllowProcedureAlter = true; _UpgradeAllowFunctionCreate = true; _UpgradeAllowFunctionDrop = true; _UpgradeAllowFunctionAlter = true; _BackupPath = String.Empty; }
/// <summary> /// Initializes a new instance of the <see cref="ConnectionForm"/> class. /// </summary> public ConnectionForm() { InitializeComponent(); m_Connection = null; }
/// <summary> /// Gets the view data. /// </summary> /// <param name="Target">The target.</param> /// <param name="config">The config.</param> public void GetViewData(View Target, Configuration config) { SqlConnection myConnection = Connection.connect(); if (myConnection == null) { return; } Target.FieldCont = new ViewFieldContainer(Target); Table TableList = ConnectionAdapter.executeSelect ( myConnection, String.Format ( "SELECT name, object_id, parent_object_id FROM sys.objects WHERE type_desc='VIEW' and name='{0}'", Target.Name ) ); String table_name = TableList.Rows[0].Values[0].Data; String object_id = TableList.Rows[0].Values[1].Data; String parent_object_id = TableList.Rows[0].Values[2].Data; String query = String.Format ( "SELECT sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable, sys.columns.is_identity " + "FROM sys.columns " + // "INNER JOIN sys.types ON sys.columns.system_type_id=sys.types.system_type_id " + "INNER JOIN sys.types " + "ON sys.columns.system_type_id=sys.types.system_type_id " + "AND sys.columns.user_type_id=sys.types.user_type_id " + "WHERE sys.columns.object_id='{0}'", object_id ); Table ColumnList = ConnectionAdapter.executeSelect(myConnection, query); foreach (Row col in ColumnList.Rows) { String type = col.Values[1].Data; String num = col.Values[2].Data; String nullable = col.Values[3].Data; String identity = col.Values[4].Data; if ( IsTextType(type) == true && num != String.Empty && num != "NULL" ) { type += "(" + num + ")"; } ViewField f = new ViewField(col.Values[0].Data, type, nullable.ToLower() == "true" ? true : false, identity.ToLower() == "true" ? true : false); f.Parent = Target; Target.FieldCont.Fields.Add(f); } myConnection.Close(); }
/// <summary> /// Gets the table data. /// </summary> /// <param name="Target">The target.</param> public void GetTableData(Table Target, Configuration config) { SqlConnection myConnection = Connection.connect(); if (myConnection == null) { return; } Target.FieldCont = new TableFieldContainer(Target); Target.DataCont = new DataContainer(Target); Target.ConstraintCont = new ConstraintContainer(Target); Table TableList = ConnectionAdapter.executeSelect ( myConnection, String.Format ( "SELECT name, object_id, parent_object_id FROM sys.objects WHERE type_desc='USER_TABLE' and name='{0}'", Target.Name ) ); String table_name = TableList.Rows[0].Values[0].Data; String object_id = TableList.Rows[0].Values[1].Data; String parent_object_id = TableList.Rows[0].Values[2].Data; String query = String.Format ( "SELECT sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable, sys.columns.is_identity " + "FROM sys.columns " + "INNER JOIN sys.types " + "ON sys.columns.system_type_id=sys.types.system_type_id " + "AND sys.columns.user_type_id=sys.types.user_type_id " + "WHERE sys.columns.object_id='{0}'", object_id ); Table ColumnList = ConnectionAdapter.executeSelect(myConnection, query); foreach (Row col in ColumnList.Rows) { String type = col.Values[1].Data; String num = col.Values[2].Data; String nullable = col.Values[3].Data; String identity = col.Values[4].Data; if (IsTextType(type) == true && num != String.Empty && num != "NULL") { type += "(" + num + ")"; } TableField f = new TableField(col.Values[0].Data, type, nullable.ToLower() == "true" ? true : false, identity.ToLower() == "true" ? true : false); f.Parent = Target; Target.FieldCont.Fields.Add(f); } //---------------------------------------------------------------- // Handle constraints query = String.Format("SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='{0}'", table_name); Table ConstraintList = ConnectionAdapter.executeSelect(myConnection, query); foreach (Row current_row in ConstraintList.Rows) { Constraint NewConstraint = new Constraint(); NewConstraint.Name = current_row.Values[0].Data; NewConstraint.Type = Constraint.SQLConstraintTypeToConstraintType(current_row.Values[1].Data); NewConstraint.Parent = Target; if (NewConstraint.Type == Constraint.ConstraintType.Check) { query = String.Format("SELECT CHECK_CLAUSE FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME='{0}'", NewConstraint.Name); Table ConstraintText = ConnectionAdapter.executeSelect(myConnection, query); if (ConstraintText.Rows.Count > 0) { NewConstraint.Text = ConstraintText.Rows[0].Values[0].Data; } } else if (NewConstraint.Type == Constraint.ConstraintType.ForeignKey) { query = String.Format ( "SELECT " + "CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME, " + "TABLE_NAME = FK.TABLE_NAME, " + "COLUMN_NAME = FK_COLS.COLUMN_NAME, " + "REFERENCED_TABLE_NAME = PK.TABLE_NAME, " + "REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME " + "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST " + "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK " + "ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG " + "AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA " + "AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME " + "AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY' " + "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG " + "AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA " + "AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME " + "AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY' " + "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME " + "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME " + "WHERE REF_CONST.CONSTRAINT_NAME = '{0}'", NewConstraint.Name ); Table ConstraintText = ConnectionAdapter.executeSelect(myConnection, query); if (ConstraintText != null && ConstraintText.Rows.Count > 0) { NewConstraint.ReferencedTable = ConstraintText.Rows[0].Values[3].Data; NewConstraint.ReferencedFieldNames.Add(ConstraintText.Rows[0].Values[4].Data); } } query = String.Format ( "SELECT COLUMN_NAME " + "FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE " + "WHERE TABLE_NAME='{0}' AND CONSTRAINT_NAME='{1}'", table_name, NewConstraint.Name ); Table ConstraintRows = ConnectionAdapter.executeSelect(myConnection, query); foreach (Row rw in ConstraintRows.Rows) { NewConstraint.FieldNames.Add(rw.Values[0].Data); } Target.ConstraintCont.Constraints.Add(NewConstraint); } // Handle data if (config.AnalyzeData) { Target.DataCont = new DataContainer(Target); } myConnection.Close(); }
/// <summary> /// Fetches the data. /// </summary> /// <param name="Analyzer">The analyzer.</param> /// <param name="config">The config.</param> public bool fetchData(BaseAnalyzer Analyzer, Configuration config) { SqlConnection myConnection = Connection.connect(); if (myConnection == null) { return(false); } //---------------------------------------------------------------- // Handle tables if (config.AnalyzeTables) { int NumTables = 0; Table TableCountTable = ConnectionAdapter.executeSelect(myConnection, "SELECT COUNT(name) FROM sys.objects WHERE type_desc='USER_TABLE'"); if (TableCountTable.Rows.Count > 0) { Int32.TryParse(TableCountTable.Rows[0].Values[0].Data, out NumTables); } Analyzer.Progress_Max = NumTables; Analyzer.Progress_Current = 0; Table TableList = ConnectionAdapter.executeSelect(myConnection, "SELECT name FROM sys.objects WHERE type_desc='USER_TABLE'"); foreach (Row table_new_row in TableList.Rows) { Analyzer.Progress_Current++; Table NewTable = new Table(this); NewTable.Name = table_new_row.Values[0].Data; Analyzer.Status = m_Connection.Name + " : " + NewTable.Name + "..."; GetTableData(NewTable, config); TableCont.Tables.Add(NewTable); } } //---------------------------------------------------------------- // Handle views if (config.AnalyzeViews) { int NumViews = 0; Table ViewCountTable = ConnectionAdapter.executeSelect(myConnection, "SELECT COUNT(name) FROM sys.objects WHERE type_desc='VIEW'"); if (ViewCountTable.Rows.Count > 0) { Int32.TryParse(ViewCountTable.Rows[0].Values[0].Data, out NumViews); } Analyzer.Progress_Max = NumViews; Analyzer.Progress_Current = 0; Table TableList = ConnectionAdapter.executeSelect(myConnection, "SELECT name FROM sys.objects WHERE type_desc='VIEW'"); foreach (Row table_new_row in TableList.Rows) { Analyzer.Progress_Current++; View NewView = new View(this); NewView.Name = table_new_row.Values[0].Data; Analyzer.Status = m_Connection.Name + " : " + NewView.Name + "..."; GetViewData(NewView, config); ViewCont.Views.Add(NewView); } } //---------------------------------------------------------------- // Handle procedures if (config.AnalyzeProcedures) { Analyzer.Status = "Analyzing procedures..."; String query = "SELECT name FROM sys.objects WHERE type_desc='SQL_STORED_PROCEDURE'"; Table proc_list = ConnectionAdapter.executeSelect(myConnection, query); foreach (Row proc_new_row in proc_list.Rows) { Procedure proc = new Procedure(); proc.Name = proc_new_row.Values[0].Data; proc.Parent = this; ProcedureCont.Procedures.Add(proc); } } //---------------------------------------------------------------- // Handle functions if (config.AnalyzeFunctions) { Analyzer.Status = "Analyzing functions..."; String query = "SELECT name FROM sys.objects WHERE type_desc='SQL_SCALAR_FUNCTION' OR type_desc='SQL_TABLE_VALUED_FUNCTION'"; Table func_list = ConnectionAdapter.executeSelect(myConnection, query); foreach (Row func_new_row in func_list.Rows) { Function func = new Function(); func.Name = func_new_row.Values[0].Data; func.Parent = this; FunctionCont.Functions.Add(func); } } //---------------------------------------------------------------- Analyzer.Status = "Analyze done..."; Analyzer.Progress_Current = 0; myConnection.Close(); return(true); }