/// <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);
}
