OWASP O2 Platform Blog

Setting useLegacyV2RuntimeActivationPolicy on O2

I just set the useLegacyV2RuntimeActivationPolicy flag on the O2′s O2 Platform.exe.config file (app.config) so that it possible to load up the CefSharp dlls (which are managed C++ assemblies built targeted to 2.0) and be able to Run Chrome inside O2

Not entirely sure what is the long term effects of this, but so far so good.

Here are some references:

Rant: Come on WordPress.com sort your site out, it took me 20m to post this ! (see Losing my mojo with WordPress.com (they’re not getting the basics right)  (originally I was not able add hyperlinks to this post  ext since wordpress’s link button in the editor was not working (another ‘back to basics’ example))

May 25, 2012 Posted by | O2 Internals | Leave a Comment

Using O2 Platform and HacmeBank

Michael from the OWASP Costa Rica chapter wrote a great article about O2 and his first use of using it to automate HacmeBank’s login sequence: Starting with OWASP O2 Platform : a short step in a long journey

In reply I wrote Using O2 with HacmeBank which gives an overview of what you can do today with O2+HacmeBank  (and ideas for where to go next)

May 6, 2012 Posted by | .NET, HacmeBank | Leave a Comment

PoC of IronPython REPL Scripting Environment

Based on http://www.ironpython.net/2.7 , here is an O2 script that creates a simple Python REPL scripting environment

Gui Created

Source Code

var topPanel = "PoC - IronPython REPL - Simple".popupWindow(800,400);
topPanel.insert_LogViewer();
//var topPanel = panel.clear().add_Panel();

var code = "print 2+2;";

Action<string> executeScript = null;
new API_ConsoleOut().show_ConsoleOut(topPanel.title("Console Out"));
var codeEditor = topPanel.insert_Left("Python Code").add_SourceCodeViewer().set_Text(code,".cs");

var cmdLine = new PythonCommandLine();
var engine = Python.CreateEngine();
var runtime = engine.Runtime;
executeScript = (script) =>	{
								try
								{
									"Execucuring code".info();
									var source = engine.CreateScriptSourceFromString(script, SourceCodeKind.AutoDetect);
									var mod = engine.CreateScope();
									source.Execute(mod);
								}
								catch(Exception ex)
								{
									ex.log();
								}
							};

codeEditor.onTextChange((text)=> code = text);
codeEditor.parent().insert_Above(20).add_Link("Execute Pyhton code" , ()=> executeScript(code)).click();

//using Microsoft.Scripting
//using IronPython
//using IronPython.Hosting
//O2Ref:IronPython.dll
//O2Ref:Microsoft.Dynamic.dll
//O2Ref:Microsoft.Scripting.dll
//O2File:API_ConsoleOut.cs

April 23, 2012 Posted by | Python | Leave a Comment

39 O2 Platform videos with 12k YouTube views

After uploading the O2 Installer video, I took at look at the O2 related videos stats and was amazed to see that there were 20 subscribers with about 12,000 views of O2 related videos :)

Looking at the list of those videos, there are some really good gems in there, so for reference here they are:

If you want to see how I created this list, take a look at the first video or at O2 Script to get YouTube videos list

April 21, 2012 Posted by | videos | Leave a Comment

O2 Script to get YouTube videos list

Based on the Accessing YouTube via its C# API script , here are two more uses of the YouTube C# API:

Video

Scripts

Create HTML list of videos (used to create this blog post entry)

panel.clear();
var googleDevKey = "AI39si4VZRef7hm9N-RBZ5u-5x5N2lIgyEvv45dGgDxCJjltduhlnF9tTmfWV55tFMUWbnThHybbipiXiEufJueJPpF65lkyjg ";
var settings = new YouTubeRequestSettings("API_YouTube",googleDevKey);
settings.PageSize = 50;
var youTubeRequest = new YouTubeRequest(settings);

Feed<Video> feed = youTubeRequest.GetVideoFeed("DinisCruz");

var webBrowser = panel.add_WebBrowser_Control();

var htmlCode = "<ul>";
var index = 1;
foreach(var entry in feed.Entries)
{
    htmlCode += "<li><a href='{1}'>{0}</a>  - {2:MMM yy}</li>"
                    .format(entry.str(),
                            entry.WatchPage.str(),
                            entry.AtomEntry.Published,
                            index++);
}
htmlCode += "</ul>";
webBrowser.open(htmlCode.saveWithExtension(".html"));

return "done";

//using Google.YouTube
//using Google.GData.Client
//using Google.GData.YouTube
//O2Ref:Google.GData.Client.dll
//O2Ref:Google.GData.YouTube.dll
//O2Ref:Google.GData.Extensions.dll

Mini Gui to View Blog Posts

panel.clear();
var googleDevKey = "AI39si4VZRef7hm9N-RBZ5u-5x5N2lIgyEvv45dGgDxCJjltduhlnF9tTmfWV55tFMUWbnThHybbipiXiEufJueJPpF65lkyjg ";
var settings = new YouTubeRequestSettings("API_YouTube",googleDevKey);

var youTubeRequest = new YouTubeRequest(settings);
Feed<Video> feed = youTubeRequest.GetVideoFeed("DinisCruz");
var treeView = panel.clear().add_Panel().add_TreeView();
var webBrowser = treeView.insert_Right().add_WebBrowser_Control();
treeView.afterSelect<Video>(
	(video)=>{
				webBrowser.open(video.WatchPage.str());
			 });
foreach(var entry in feed.Entries)
	treeView.add_Node(entry);
treeView.selectFirst();
//feed.details();
//panel.add_TableList().show(feed.Entries);

return "done";

//using Google.YouTube
//using Google.GData.Client
//using Google.GData.YouTube
//O2Ref:Google.GData.Client.dll
//O2Ref:Google.GData.YouTube.dll
//O2Ref:Google.GData.Extensions.dll

April 21, 2012 Posted by | Interoperability | 1 Comment

Set .NET WebBrowser Control to use latest version of IE

O2 Script to change the IE Script environment to use the latest version of IE

//note you will need to run this wihout UAC
"O2 log Viewer".popupWindow().add_LogViewer();

//for more details:    http://www.west-wind.com/weblog/posts/2011/May/21/Web-Browser-Control-Specifying-the-IE-Version
//                     http://msdn.microsoft.com/en-us/library/ee330730%28VS.85%29.aspx#browser_emulation
try
{
    var IEVAlue =  9000; // can be: 9999 , 9000, 8888, 8000, 7000
    var targetApplication = Processes.getCurrentProcessName() + ".exe";

    var localMachine = Registry.LocalMachine;
    var parentKeyLocation = @"SOFTWARE\Microsoft\Internet Explorer\MAIN\FeatureControl";
    var keyName = "FEATURE_BROWSER_EMULATION";
    "opening up Key: {0} at {1}".info(keyName, parentKeyLocation);
    var subKey = localMachine.getOrCreateSubKey(parentKeyLocation,keyName,true);
    subKey.SetValue(targetApplication, IEVAlue,RegistryValueKind.DWord);
    return "all done, now try it on a new process".info();
}
catch(Exception ex)
{
    ex.log();
    "NOTE: you need to run this under no UAC".info();
}

//using Microsoft.Win32
//O2File:_Extra_methods_Windows.cs

April 17, 2012 Posted by | IE Automation | Leave a Comment

Exporting Checkmarx SAST Database into XML files

Once I added VistaDB support to O2, I was able to export the CWE content from CheckMarx database into TeamMentor  (see videos at PoC of integrating TeamMentor with Checkmarx),

This post covers the part where we export the entire Checkmark database into flat XML files (for the script that consumed the CWE.xml see Case Study – Creating a CWE Library from CheckMarx data )

Viewing table data: Configurations

var vistaDB = new API_VistaDB();
vistaDB.ConnectionString = @"data source='C:\Program Files\Checkmarx\Checkmarx Application Server\CxDB.vdb3'";

var dataTable = vistaDB.table("Configurations").dataTable();
panel.clear().add_DataGridView().dataSource(dataTable);

//O2File:API_VistaDB.cs

Viewing table data: CWE

var vistaDB = new API_VistaDB();
var dataTable = vistaDB.table("CWE").dataTable();
panel.clear().add_DataGridView().dataSource(dataTable);

//O2File:API_VistaDB.cs

Get table as XML

var vistaDB = new API_VistaDB();
var xml = vistaDB.table("Configurations").xml();
return xml;

//O2File:API_VistaDB.cs
//O2Tag_DontAddExtraO2Files.

View table in DataGridView

var vistaDB = new API_VistaDB();

var dataTable = vistaDB.table("Configurations").dataTable();
panel.clear().add_DataGridView().dataSource(dataTable);

//O2File:API_VistaDB.cs
//O2Tag_DontAddExtraO2Files

Saving one table as XML file

var vistaDB = new API_VistaDB();
var checkMark_XmlDumps = "_CheckMark_XmlDumps".tempDir(false);
var tables = vistaDB.tables();

var table = tables[0];
var targetFile = checkMark_XmlDumps.pathCombine(table.Name + ".xml");

return table.xml().saveAs(targetFile);

//O2File:API_VistaDB.cs
//O2Tag_DontAddExtraO2Files

Saving all tables

var vistaDB = new API_VistaDB();
var checkMark_XmlDumps = "_CheckMark_XmlDumps".tempDir(false);
foreach(var table in vistaDB.tables())
{
 "saving table: {0}".info(table);
 var targetFile = checkMark_XmlDumps.pathCombine(table.Name + ".xml");
 table.xml().saveAs(targetFile);
}
return "done";

//O2File:API_VistaDB.cs
//O2Tag_DontAddExtraO2Files

Consuming  Saved CWE.xml

var checkMark_XmlDumps = "_CheckMark_XmlDumps".tempDir(false);
var cwe = checkMark_XmlDumps.pathCombine("CWE.xml").xRoot();

return cwe.elements().size();

Consuming saved CWE.Xml via cache: (faster)

var checkMark_XmlDumps = "_CheckMark_XmlDumps".tempDir(false);var cwe = "cweData".o2Cache<XElement>(()=> checkMark_XmlDumps.pathCombine("CWE.xml").xRoot());
return cwe.elements().size();
Tool to visualize the CWE data as HTML pages
var vistaDB = new API_VistaDB();
var checkMark_XmlDumps = "_CheckMark_XmlDumps".tempDir(false);
var xRoot = "cweData".o2Cache<XElement>(()=> checkMark_XmlDumps.pathCombine("CWE.xml").xRoot());

var topPanel = panel.clear().add_Panel();
var webBrowser = topPanel.add_WebBrowser_Control();
var treeView= webBrowser.insert_Left(200).add_TreeView();
var codeViewer = webBrowser.insert_Below().add_SourceCodeViewer();

treeView.afterSelect<string>(
 (text) =>
 {
 webBrowser.open(text.saveWithExtension(".html") );
 codeViewer.set_Text(text.htmlDecode().tidyHtml() , ".html");
 });

foreach(var cweEntry in xRoot.elements().remove(0))
{
 var entryData = cweEntry.elements();
 treeView.add_Node(entryData[0].value(),entryData[1].value());
}

treeView.selectFirst();
//O2File:API_VistaDB.cs
//O2File:HtmlAgilityPack_ExtensionMethods.cs

//O2Ref:O2_Misc_Microsoft_MPL_Libs.dll

April 13, 2012 Posted by | CheckMarx | Leave a Comment

VistaDB API and GUI (as used by Checkmarx SAST engine)

O2 now supports the VistaDB, which is a pure .NET embeded database used by applications like Checkmarx (who use it to support their SAST Web Application).

After using it a bit, I have to say that VistaDB seams like a really nice solution, since it is really a xcopy-based-relational-database with full SQL support.

To add support to VistaDB in O2, all that was needed was to grab the API_SqlServer.cs script and changed the Sql classes to their equivalent VistaDB classes (for example SqlConnection became VistaDBConnection).

Since VistaDB is not an open source project, you will need to have access to their eval download, or to an application that uses it (note how  used the VistaDB.NET20.dll from the Checkmarx install folder)

Util – VistaDB Browser.h2

//var topPanel = panel.clear().add_Panel();
var topPanel = O2Gui.open<Panel>("Sql Info",900,500);
topPanel.insert_Below(100).add_LogViewer();
var vistaDb = new API_VistaDB();
var show= false;
show = true;
TabControl tabControl = null;
Action loadDataFromCurrentConnection = 
    ()=>{
            tabControl.remove_Tab("Database details viewer");
            tabControl.remove_Tab("Table's Schema");
            tabControl.remove_Tab("Table's Data");
            tabControl.remove_Tab("Stored Procedures");
            vistaDb.add_Viewer_DataBases(tabControl.add_Tab("Database details viewer"));
            vistaDb.add_Viewer_Tables(tabControl.add_Tab("Table's Schema"));
            vistaDb.add_Viewer_TablesData(tabControl.add_Tab("Table's Data"));

        };



if(show)
{
    tabControl = topPanel.add_TabControl();    
    vistaDb.add_ConnectionStringTester(tabControl.add_Tab("Test/Set SqlConnection string"), loadDataFromCurrentConnection);
    vistaDb.add_GUI_SqlCommandExecute(tabControl.add_Tab("Execute SQL Commands"));                        
}



//O2Ref:System.Data.dll
//O2File:API_VistaDB.cs
//O2Tag_DontAddExtraO2Files

 
API_VistaDB.cs

using System;
using System.Data;
using System.Xml.Serialization;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;
using O2.Interfaces.O2Core;
using O2.Kernel;
using O2.Kernel.ExtensionMethods;
using O2.DotNetWrappers.DotNet;
using O2.DotNetWrappers.ExtensionMethods;
using O2.External.SharpDevelop.ExtensionMethods;
using O2.Views.ASCX.ExtensionMethods;
using O2.XRules.Database.Utils;
using VistaDB.Provider;
//O2Ref:C:\Program Files\Checkmarx\Checkmarx Engine Server\VistaDB.NET20.dll</pre>
namespace O2.XRules.Database.APIs
{
    public class API_VistaDB_Test
    {
        public void launchTestGui()
        {
            "Util - VistaDB Browser.h2".local().executeH2Script();
        }
    }

    public class API_VistaDB
    {  
        public string ConnectionString { get;set; }
        public string LastError { get; set; }
       
        public API_VistaDB()
        {
            ConnectionString = @"data source='C:\Program Files\Checkmarx\Checkmarx Application Server\CxDB.vdb3'";     //default to this one           
        }       
       
        public API_VistaDB(string connectionString)
        {
            ConnectionString = connectionString;
        }
    }
   
    public class Database
    {
        public API_VistaDB VistaDB { get; set; }
        public string Name { get; set; }       
        public List<Table> Tables { get; set; }
        public List<StoredProcedure> StoredProcedures { get; set; }
       
        public Database(string name)
        {
            Name = name.trim();
            Tables = new List<Table>();
            StoredProcedures = new List<StoredProcedure>();
        }
       
        public Database(API_VistaDB vistaDB, string name) : this (name)
        {
            VistaDB = vistaDB;
       
        }
    }
   
    public class Table
    {
        [XmlIgnore] public API_VistaDB VistaDB { get; set; }               
//        public string Catalog {get;set;}
//        public string Schema {get;set;}
        public string Name {get;set;}
//        public string Type {get;set;}
        public List<Table_Column> Columns {get;set;}
       
        public DataTable TableData { get; set; }
       
        public Table()
        {
            Columns = new List<Table_Column>();
        }
       
        public override string ToString()
        {
            /*return (Schema.valid())
                        ? "{0}.{1}".format(Schema, Name)
                        : Name;*/
            return Name;           
        }
    }
   
    public class Table_Column
    {
        //name, typeId, objectId,options , scriptValue
        public string Name { get; set; }       
        public string TypeId { get; set; }       
        public string ObjectId { get; set; }       
        public string Options { get; set; }       
        public string ScriptValue     { get; set; }       
       
        public override string ToString()
        {
            //return "{0} ({1})".format(Name, DataType);
            return "{0} ({1})".format(Name);
        }
    }
   
    public class StoredProcedure
    {
        public string Schema {get;set;}
        public string Name {get;set;}
        public string Value {get;set;}
       
        public StoredProcedure(string schema, string name, string value)
        {
            Schema = schema;
            Name = name;
            Value = value;
        }
       
        public StoredProcedure(string name, string value) : this("",name, value)
        {
           
        }
       
        public override string ToString()
        {
            return (Schema.valid())
                        ? "{0}.{1}".format(Schema, Name)
                        : Name;
        }
    }
   
    //add these queries should be done using Linq
    public static class API_VistaDB_Helps
    {
        public static Database database(this API_VistaDB vistaDB, string name)
        {
            return new Database(vistaDB, name);
        }
    }
    public static class API_VistaDB_getData
    {
        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 List<string> column_Names(this Table table)
        {
            return (from column in table.columns()
                       select column.Name).toList();
        }
       
        public static List<Table> tables(this API_VistaDB vistaDb)
        {           
            return vistaDb.database("").tables();
        }
       
        public static List<Table> tables(this Database database)
        {           
            if (database.Tables.size() ==0)
                database.map_Tables();
            return database.Tables;
        }
       
        public static Table table(this API_VistaDB vistaDb, string name)
        {
            return vistaDb.database("").table(name);
        }
       
        public static Table table(this Database database, string name)
        {           
            return (from table in database.tables()
                    where table.Name.trim() == name
                    select table).first();
        }
       
       
        public static List<Table_Column> columns(this Table table)
        {
            return table.Columns;
        }
    }
   
    public static class API_VistaDB_PopulateData
    {
        public static Database map_StoredProcedures(this Database database)
        {       
            var sqlQuery = "select Specific_Schema, Specific_Name, Routine_Definition  from {0}.Information_Schema.Routines".format(database.Name);
            var storedProceduresData = database.VistaDB.executeReader(sqlQuery);           
            foreach(DataRow row in storedProceduresData.Rows)
                database.StoredProcedures.Add(new StoredProcedure(row.ItemArray[0].str(),row.ItemArray[1].str(),row.ItemArray[2].str()));
            return database;
        }   
       
        public static Database map_Tables(this API_VistaDB vistaDB)
        {
            return vistaDB.database("").map_Tables();
        }
       
        public static Database map_Tables(this Database database)
        {       
            var sqlQuery = "select * from [database schema] where typeid = 1".format();
            var tables = database.VistaDB.executeReader(sqlQuery);           
            foreach(DataRow row in tables.Rows)
                database.Tables.Add(new Table(){
                                                    VistaDB = database.VistaDB,
//                                                    Catalog = row.ItemArray[0].str(),
//                                                    Schema = row.ItemArray[1].str(),
                                                    Name = row["name"].str().trim()
//                                                    Type = row.ItemArray[3].str()
                                                    });
            return database;
        }   
       
        public static Database map_Table_Columns(this Database database)
        {       
            foreach(var table in database.tables())
            {           
                //var sqlQuery = "select Column_Name, Column_Default, Is_Nullable, Data_Type, Character_Maximum_Length from {0}.Information_Schema.Columns where table_Schema='{1}' and table_name='{2}'"
                //                    .format(table.Catalog, table.Schema,table.Name);
                               
                var objectId = database.VistaDB.executeScalar("select objectId from [database schema] where typeid = 1 and name ='{0}' ".format(table.Name));
                var sqlQuery = "select name, typeId, objectId,options , scriptValue from [database schema] where foreignReference = '{0}' ".format(objectId);
                
                var columns = database.VistaDB.executeReader(sqlQuery);           
               
                foreach(DataRow row in columns.Rows)
                    table.Columns.Add(new Table_Column(){
                                                        Name =  row.ItemArray[0].str().trim(),
                                                        TypeId = row.ItemArray[1].str().trim(),
                                                        ObjectId = row.ItemArray[2].str().trim(),
                                                        Options = row.ItemArray[3].str().trim(),
                                                        ScriptValue = row.ItemArray[4].str().trim()
                                                        });
            }
            return database;
        }
       
        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 Database map_Table_Data(this Database database, Table table)
        {
            database.VistaDB.map_Table_Data(table);
            return database;
        }
        public static Database map_Table_Data(this Database database)
        {
            "Mapping table data".info();
            var timer = new O2Timer("Mapped tabled data").start();
            foreach(var table in database.tables())                   
                database.map_Table_Data(table);
            timer.stop();
            return database;               
        }
       
        public static DataTable dataTable(this Table table)
        {
            if (table.isNull())
                return null;
            table.VistaDB.map_Table_Data(table);
            return table.TableData;
        }       
       
        public static string xml(this Table table)
        {           
            var dataSet = new DataSet();
            dataSet.Tables.Add(table.dataTable());
            return dataSet.GetXml();
        }
    }
   
    public static class API_VistaDB_Queries
    {
        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 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 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 executeNonQuery(this API_VistaDB vistaDB, string command)
        {       
            "[API_VistaDB] Executing Non Query: {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;
                sqlCommand.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }
            finally
            {
                if (sqlConnection.notNull())
                    sqlConnection.Close();
            }
            return vistaDB;
        }
       
        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 class API_VistaDB_GUI_Controls
    {
        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 <a href="http://www.connectionstrings.com/sql-server-2005">http://www.connectionstrings.com/sql-server-2005</a>
            sampleConnectionStrings.add(@"data source='C:\Program Files\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 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 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_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_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 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 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().fixCRLF();
                            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 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;
        }
    }       
}

 

April 13, 2012 Posted by | .NET, CheckMarx | 1 Comment

New version of O2, GitHub based, running in OSX and in VisualStudio

I just wrote a number of blog posts about the new version of O2:

March 24, 2012 Posted by | O2 Scripting, OSX, VisualStudio | , | Leave a Comment

Consuming NGit from O2 (first pass)

One of the key requirements that I had (when moving O2 from SVN to Git) was to be able to have the same auto-update workflow using Git that I had using SVN.

Until I found NGIT , I was considering that my only option to have O2 use Git for its Scripts library was to ask the users to install Git in their systems, which would be a pain since it is quite a heavy install.

But NGit changes the whole game, since NGit gives me a C# native implementation of the GIT version control system (even more interresting is the fact that NGIT is a semi-automatic port of JGit (http://eclipse.org/jgit ))

My first steps were to see if I could use this library , and using O2′s Scripting Environment, here are the initial tests I performed:

Open Repository


var testRepository = "_gitTest".tempDir(false);

var git = Git.Open(testRepository);
var repository = (Repository)git.field("repo");

return repository.Directory;

//using NGit
//using  NGit.Api;
//O2Ref:E:\Dev Tests\mono-ngit-17c0d7f\bin\NGit.dll
//O2Ref:E:\Dev Tests\mono-ngit-17c0d7f\bin\Mono.Security.dll
//O2Ref:E:\Dev Tests\mono-ngit-17c0d7f\bin\NSch.dll
//O2Ref:E:\Dev Tests\mono-ngit-17c0d7f\bin\Sharpen.dll

another option of getting the repository object


var testRepository = @"C:\_WorkDir\O2\O2_Install\GitHub.Repositories\O2.Platform.Exe";

var git = Git.Open(testRepository);
var repository = git.GetRepository();

return repository.Directory;

//using NGit
//using  NGit.Api;
//O2Ref:C:\_WorkDir\Git_O2OPlatform\_O2_Platform_Source_Code\O2.Platform.Exe\O2_Reference_Dlls\NGit.dll
//O2Ref:C:\_WorkDir\Git_O2OPlatform\_O2_Platform_Source_Code\O2.Platform.Exe\O2_Reference_Dlls\Mono.Security.dll
//O2Ref:C:\_WorkDir\Git_O2OPlatform\_O2_Platform_Source_Code\O2.Platform.Exe\O2_Reference_Dlls\NSch.dll
//O2Ref:C:\_WorkDir\Git_O2OPlatform\_O2_Platform_Source_Code\O2.Platform.Exe\O2_Reference_Dlls\Sharpen.dll

adding a file


var testRepository = "_gitTest".tempDir(false);
var git = Git.Open(testRepository);

"some text".saveAs(testRepository.pathCombine("a file.txt"));

git.Add().AddFilepattern(".").Call();
return git.Status().Call().GetAdded();

Commit a File


var testRepository = "_gitTest".tempDir(false);
var git = Git.Open(testRepository);

"some text".saveAs(testRepository.pathCombine("a file.txt"));

git.Add().AddFilepattern(".").Call();

git.Commit().SetMessage("test Commit").Call();
return git.Status().Call().GetAdded();



<strong>clone a repository</strong>
var tempDir = "_cloneTest".tempDir().info();

var command = Git.CloneRepository();
command.SetDirectory(tempDir);
command.SetURI("git://github.com/o2platform/Scripts-by-O2-Users.git");
var git = command.Call();
var repository = git.GetRepository();
return repository;

cloning O2 Scripts GitHub repository  (in 13 seconds)


var tempDir = "_O2_Scripts".tempDir(false).info();

var command = Git.CloneRepository();
command.SetDirectory(tempDir);
command.SetURI("git://github.com/o2platform/O2-Platform-Scripts.git");
var o2Timer = new O2Timer("Repository clone").start();
var git = command.Call();
o2Timer.stop();
files in checked out repository
var git = Git.Open(@"E:\O2_Tests\tmp_Folder\_cloneTest_tmpD053");

var repository = git.GetRepository();

return repository.WorkTree.List();

pull updates

var git = Git.Open(@"E:\O2_Tests\tmp_Folder\_cloneTest_tmpD053");

return git.Pull().Call().GetFetchResult().GetTrackingRefUpdates();
push into remote

var result = Git.Open(@"E:\O2_Tests\tmp_Folder\_O2_Scripts")
.Push().Call();

Viewing Push Status

var pushCommand = Git.Open(@"E:\O2_Tests\tmp_Folder\_O2_Scripts")
.Push();
var pushResults = pushCommand.Call();

foreach(var pushResult in pushResults)
{
var remoteUpdates = pushResult.GetRemoteUpdates();
foreach(var remoteUpdate in remoteUpdates)
{
"status:{0}".debug(remoteUpdate.GetStatus());
}

}

pull with progress text

var git = Git.Open(testRepository);
var repository = git.GetRepository();
var stringWriter = new StringWriter();
var textMonitor = new  TextProgressMonitor(stringWriter);

var pullCommand= git.Pull();
pullCommand.SetProgressMonitor(textMonitor);
var pullResponse = pullCommand.Call();
return stringWriter.str() + " \n\n.........\n\n " + pullResponse.str();

March 18, 2012 Posted by | GitHub | Leave a Comment

« Previous Entries    

Follow

Get every new post delivered to your Inbox.