public static API_VistaDB add_Viewer_StoredProcedures_Raw <T>(this API_VistaDB vistaDB, T control, string databaseName)
where T : Control
{
var sqlQuery = "select * from {0}.Information_Schema.Routines".format(databaseName);
return(vistaDB.add_Viewer_QueryResult(control, sqlQuery));
}
public static API_VistaDB add_Viewer_DataBases <T>(this API_VistaDB vistaDB, T control)
where T : Control
{
var sqlQuery = "select * from [database schema] where typeid = 1";
return(vistaDB.add_Viewer_QueryResult(control, sqlQuery));
}
public static API_VistaDB add_Viewer_Tables_Raw <T>(this API_VistaDB vistaDB, T control, string databaseName)
where T : Control
{
var objectId = vistaDB.executeScalar("select objectId from [database schema] where typeid = 1 and name ='{0}'".format(databaseName));
var sqlQuery = "select * from [database schema] where typeid = 3 and foreignReference ='{0}'".format(objectId);
return(vistaDB.add_Viewer_QueryResult(control, sqlQuery));
}
public static API_VistaDB add_GUI_SqlCommandExecute <T>(this API_VistaDB vistaDB, T control)
where T : Control
{
Action <string> executeNonQuery = null;
Action <string> executeReader = null;
var resultsPanel = control.add_GroupBox("Result");
var sqlCommandToExecute = resultsPanel.insert_Above("Sql Command to execute").add_TextArea();
var sampleQueries = sqlCommandToExecute.insert_Left(300, "Sample Queries")
.add_TreeView()
.afterSelect <string>((text) => sqlCommandToExecute.set_Text(text));
sqlCommandToExecute.insert_Right(200)
.add_Button("Execute Non Query")
.fill()
.onClick(() => {
"Executing Non Query".info();
executeNonQuery(sqlCommandToExecute.get_Text());
})
.insert_Above()
.add_Button("Execute Reader")
.fill()
.onClick(() => {
"Executing Reader".info();
executeReader(sqlCommandToExecute.get_Text());
});;
executeReader = (sqlQuery) => {
vistaDB.add_Viewer_QueryResult(resultsPanel, sqlQuery);
"done".info();
};
executeNonQuery = (sqlText) => {
var log = resultsPanel.control <TextBox>();
if (log.isNull())
{
log = resultsPanel.clear().add_TextArea();
}
if (sqlText.contains("GO".line()))
{
var sqlTexts = sqlText.line().split("GO".line());
log.append_Line("[{0}]Found a GO, so breaking it into {1} queries".format(DateTime.Now, sqlTexts.size()));
var sqlConnection = vistaDB.getOpenConnection();
foreach (var text in sqlTexts)
{
vistaDB.executeNonQuery(sqlConnection, text);
if (vistaDB.LastError.valid())
{
log.append_Line("SQL ERROR: {0}".lineBeforeAndAfter().format(vistaDB.LastError));
log.append_Line("ERROR: stoping execution since there was an error which executing the query: {0}".format(text).lineBeforeAndAfter());
break;
}
}
vistaDB.closeConnection(sqlConnection);
}
else
{
log.append_Line("Executing as Non Query: {0}".format(sqlText));
vistaDB.LastError = "";
vistaDB.executeNonQuery(sqlText);
if (vistaDB.LastError.valid())
{
log.append_Line("SQL ERROR: {0}".lineBeforeAndAfter().format(vistaDB.LastError));
}
}
"done".info();
};
sampleQueries.add_Nodes(new string[] {
"select * from master..sysDatabases",
"select * from master.Information_Schema.Tables",
"select * from master.Information_Schema.Routines"
});
sampleQueries.selectFirst();
return(vistaDB);
}