public static object executeScalar(this API_VistaDB vistaDB, string command)
{
"[API_VistaDB] Executing Scalar: {0}".info(command);
VistaDBConnection sqlConnection = null;
try
{
sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
sqlConnection.Open();
var sqlCommand = new VistaDBCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlCommand.CommandType = CommandType.Text;
return(sqlCommand.ExecuteScalar());
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
finally
{
sqlConnection.Close();
}
return(null);
}
public static DataTable executeReader(this API_VistaDB vistaDB, string command)
{
var sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
sqlConnection.Open();
try
{
var sqlCommand = new VistaDBCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlCommand.CommandType = CommandType.Text;
var reader = sqlCommand.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(reader);
return(dataTable);
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
finally
{
if (sqlConnection.notNull())
{
sqlConnection.Close();
}
}
return(null);
}
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 List <string> database_Names(this API_VistaDB vistaDB)
{
var sqlQuery = "select * from [database schema] where typeid = 1";
return((from DataRow row in vistaDB.executeReader(sqlQuery).Rows
select row["name"].str()).toList());
}
public static API_VistaDB map_Table_Data(this API_VistaDB vistaDB, Table table)
{
// var sqlQuery = "select * from [{0}].[{1}].[{2}]".format(table.Catalog,table.Schema, table.Name);
var sqlQuery = "select * from {0}".format(table.Name);
table.TableData = vistaDB.executeReader(sqlQuery);
return(vistaDB);
}
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_Viewer_QueryResult <T>(this API_VistaDB vistaDB, T control, string sqlQuery)
where T : Control
{
control.clear();
var dataTable = vistaDB.executeReader(sqlQuery);
var dataGridView = control.add_DataGridView();
dataGridView.DataError += (sender, e) => { // " dataGridView error: {0}".error(e.Context);
};
dataGridView.invokeOnThread(() => dataGridView.DataSource = dataTable);
return(vistaDB);
}
public static T add_ConnectionStringTester <T>(this API_VistaDB vistaDB, T control, Action afterConnect)
where T : Control
{
control.clear();
var connectionString = control.add_GroupBox("Connection String").add_TextArea();
var connectionStringSamples = connectionString.parent().insert_Left <Panel>(200).add_GroupBox("Sample Connection Strings")
.add_TreeView()
.afterSelect <string>((text) => connectionString.set_Text(text));
var connectPanel = connectionString.insert_Below <Panel>(200);
var button = connectPanel.insert_Above <Panel>(25).add_Button("Connect").fill();
var response = connectPanel.add_GroupBox("Response").add_TextArea();
button.onClick(() => {
try
{
var text = connectionString.get_Text();
vistaDB.ConnectionString = text;
response.set_Text("Connecting using: {0}".format(text));
var sqlConnection = new VistaDBConnection(text);
sqlConnection.Open();
response.set_Text("Connected ok");
afterConnect();
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
response.set_Text("Error: {0}".format(ex.Message));
}
});
//connectionString.set_Text(@"Data Source=.\SQLExpress;Trusted_Connection=True");
var sampleConnectionStrings = new List <string>();
//from http://www.connectionstrings.com/sql-server-2005
sampleConnectionStrings.add(@"data source='C:\Program Files (x86)\Checkmarx\Checkmarx Application Server\CxDB.vdb3'")
.add(@"Data Source=.\SQLExpress;Trusted_Connection=True")
.add(@"Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI")
.add(@"Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;")
.add(@"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;")
.add(@"Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;")
.add(@"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;")
.add(@"Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;");
connectionStringSamples.add_Nodes(sampleConnectionStrings).selectFirst();
button.click();
return(control);
}
public static VistaDBConnection closeConnection(this API_VistaDB vistaDB, VistaDBConnection sqlConnection)
{
"[API_VistaDB] Closing Connection".info();
try
{
sqlConnection.Close();
return(sqlConnection);
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
return(null);
}
public static VistaDBConnection getOpenConnection(this API_VistaDB vistaDB)
{
"[API_VistaDB] Opening Connection".info();
try
{
var sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
sqlConnection.Open();
return(sqlConnection);
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
return(null);
}
public static API_VistaDB add_Viewer_Tables <T>(this API_VistaDB vistaDB, T control)
where T : Control
{
control.clear();
var value = control.add_TableList();
var tables_Names = value.insert_Left <Panel>(200).add_TreeView().sort();
// var database_Names = tables_Names.insert_Above<Panel>(100).add_TreeView().sort();
/* database_Names.afterSelect<string>(
* (database_Name)=>{
* tables_Names.backColor(Color.Salmon);
* O2Thread.mtaThread(
* ()=>{
* value.set_Text("");
* var database = new Database(vistaDB, database_Name);
* database.map_Tables()
* .map_Table_Columns();
* tables_Names.clear();
* tables_Names.add_Nodes(database.Tables);
* tables_Names.selectFirst();
* tables_Names.backColor(Color.White);
* });
* });
*/
tables_Names.afterSelect <Table>(
(table) => value.show(table.Columns));
// database_Names.add_Nodes(vistaDB.database_Names());
// database_Names.selectFirst();
var database = new Database(vistaDB, "");
database.map_Tables()
.map_Table_Columns();
tables_Names.clear();
tables_Names.add_Nodes(database.Tables);
tables_Names.selectFirst();
tables_Names.backColor(Color.White);
return(vistaDB);
}
public static API_VistaDB executeNonQuery(this API_VistaDB vistaDB, VistaDBConnection sqlConnection, string command)
{
"[API_VistaDB] Executing Non Query: {0}".info(command);
try
{
var sqlCommand = new VistaDBCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
return(vistaDB);
}
public static API_VistaDB add_Viewer_StoredProcedures <T>(this API_VistaDB vistaDB, T control)
where T : Control
{
control.clear();
Database currentDatabase = null;
var value = control.add_TextArea();
var storedProcedure_Names = value.insert_Left <Panel>(200).add_TreeView().sort();
var database_Names = storedProcedure_Names.insert_Above <Panel>(100).add_TreeView().sort();
var filter = storedProcedure_Names.insert_Above(20)
.add_TextBox("Filter:", "")
.onTextChange((text) => {
storedProcedure_Names.clear();
var result = (from storedProcedure in currentDatabase.StoredProcedures
where storedProcedure.Name.regEx(text)
select storedProcedure);
storedProcedure_Names.add_Nodes(result);
});
database_Names.afterSelect <string>(
(database_Name) => {
value.set_Text("");
currentDatabase = new Database(vistaDB, database_Name);
currentDatabase.map_StoredProcedures();
storedProcedure_Names.clear();
storedProcedure_Names.add_Nodes(currentDatabase.StoredProcedures);
storedProcedure_Names.selectFirst();
});
storedProcedure_Names.afterSelect <StoredProcedure>(
(storedProcedure) => value.set_Text(storedProcedure.Value));
database_Names.add_Nodes(vistaDB.database_Names());
database_Names.selectFirst();
return(vistaDB);
}
public static Table table(this API_VistaDB vistaDb, string name)
{
return(vistaDb.database("").table(name));
}
public Database(API_VistaDB vistaDB, string name) : this(name)
{
VistaDB = vistaDB;
}
public static Database map_Tables(this API_VistaDB vistaDB)
{
return(vistaDB.database("").map_Tables());
}
public static API_VistaDB add_Viewer_TablesData <T>(this API_VistaDB vistaDB, T control)
where T : Control
{
control.clear();
var dataGridView = control.add_DataGridView();
dataGridView.DataError += (sender, e) => {}; //" dataGridView error: {0}".error(e.Context);};
var tables_Names = dataGridView.insert_Left <Panel>(200).add_TreeView().sort();
var database_Names = tables_Names.insert_Above <Panel>(100).add_TreeView().sort();
var preloadAllData = false;
tables_Names.insert_Below(20).add_CheckBox("Preload all data from database", 0, 0, (value) => preloadAllData = value).autoSize(); //.check();
var rowData = dataGridView.insert_Below <Panel>(100).add_SourceCodeViewer();
var rowDataField = rowData.insert_Left <Panel>(100).add_TreeView();
var selectedField = "";
rowDataField.afterSelect <DataGridViewCell>(
(cell) => {
selectedField = rowDataField.selected().get_Text();
var fieldContent = cell.Value.str().fix_CRLF();
if (fieldContent.starts("<?xml"))
{
"mapping xml".info();
fieldContent = fieldContent.xmlFormat();
rowData.set_Text(fieldContent, "a.xml");
}
else
{
rowData.set_Text(fieldContent);
}
});
dataGridView.afterSelect(
(row) => {
rowDataField.clear();
//rowData.set_Text("");
foreach (DataGridViewCell cell in row.Cells)
{
var fieldName = dataGridView.Columns[cell.ColumnIndex].Name;
var node = rowDataField.add_Node(fieldName, cell);
if (fieldName == selectedField)
{
node.selected();
}
}
if (rowDataField.selected().isNull())
{
rowDataField.selectFirst();
}
});
database_Names.afterSelect <string>(
(database_Name) => {
tables_Names.backColor(Color.Salmon);
O2Thread.mtaThread(
() => {
var database = new Database(vistaDB, database_Name);
database.map_Tables();
if (preloadAllData)
{
database.map_Table_Data();
}
tables_Names.clear();
tables_Names.add_Nodes(database.Tables);
tables_Names.selectFirst();
tables_Names.backColor(Color.White);
database_Names.splitContainer().panel1Collapsed(true);
});
});
Action <Table> loadTableData =
(table) => {
tables_Names.backColor(Color.Salmon);
O2Thread.mtaThread(
() => {
rowDataField.clear();
rowData.set_Text("");
dataGridView.remove_Columns();
if (table.TableData.isNull())
{
vistaDB.map_Table_Data(table);
}
dataGridView.invokeOnThread(() => dataGridView.DataSource = table.TableData);
tables_Names.backColor(Color.White);
});
};
tables_Names.afterSelect <Table>(
(table) => {
loadTableData(table);
});
database_Names.add_Nodes(vistaDB.database_Names());
database_Names.selectFirst();
tables_Names.add_ContextMenu().add_MenuItem("reload data",
() => {
var selectedNode = tables_Names.selected();
if (selectedNode.notNull())
{
var table = (Table)tables_Names.selected().get_Tag();
table.TableData = null;
loadTableData(table);
}
});
return(vistaDB);
}
public static List <Table> tables(this API_VistaDB vistaDb)
{
return(vistaDb.database("").tables());
}
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);
}
public static Database database(this API_VistaDB vistaDB, string name)
{
return(new Database(vistaDB, name));
}
