Exemple #1
0
        public static API_SqlServer add_Viewer_DataBases <T>(this API_SqlServer sqlServer, T control)
            where T : Control
        {
            var sqlQuery = "select * from master..sysDatabases";

            return(sqlServer.add_Viewer_QueryResult(control, sqlQuery));
        }
Exemple #2
0
        public static API_SqlServer add_Viewer_Tables <T>(this API_SqlServer sqlServer, 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(sqlServer, 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(sqlServer.database_Names());

            database_Names.selectFirst();
            return(sqlServer);
        }
Exemple #3
0
        public static API_SqlServer add_Viewer_StoredProcedures_Raw <T>(this API_SqlServer sqlServer, T control, string databaseName)
            where T : Control
        {
            var sqlQuery = "select * from {0}.Information_Schema.Routines".format(databaseName);

            return(sqlServer.add_Viewer_QueryResult(control, sqlQuery));
        }
Exemple #4
0
        public static object executeScalar(this API_SqlServer sqlServer, string command)
        {
            "[API_SqlServer] Executing Scalar: {0}".info(command);
            SqlConnection sqlConnection = null;

            try
            {
                sqlConnection = new SqlConnection(sqlServer.ConnectionString);
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection  = sqlConnection;
                sqlCommand.CommandText = command;
                sqlCommand.CommandType = CommandType.Text;
                return(sqlCommand.ExecuteScalar());
            }
            catch (Exception ex)
            {
                sqlServer.LastError = ex.Message;
                ex.log();
            }
            finally
            {
                sqlConnection.Close();
            }
            return(null);
        }
Exemple #5
0
        public static DataTable executeReader(this API_SqlServer sqlServer, string command)
        {
            SqlConnection sqlConnection = new SqlConnection(sqlServer.ConnectionString);

            sqlConnection.Open();
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                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)
            {
                sqlServer.LastError = ex.Message;
                ex.log();
            }
            finally
            {
                if (sqlConnection.notNull())
                {
                    sqlConnection.Close();
                }
            }
            return(null);
        }
Exemple #6
0
        public static API_SqlServer map_Table_Data(this API_SqlServer SqlServer, Table table)
        {
            var sqlQuery = "select * from [{0}].[{1}].[{2}]".format(table.Catalog, table.Schema, table.Name);

            table.TableData = SqlServer.executeReader(sqlQuery);
            return(SqlServer);
        }
Exemple #7
0
        public static API_SqlServer executeNonQuery(this API_SqlServer sqlServer, string command)
        {
            "[API_SqlServer] Executing Non Query: {0}".info(command);
            SqlConnection sqlConnection = null;

            try
            {
                sqlConnection = new SqlConnection(sqlServer.ConnectionString);
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection  = sqlConnection;
                sqlCommand.CommandText = command;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                sqlServer.LastError = ex.Message;
                ex.log();
            }
            finally
            {
                if (sqlConnection.notNull())
                {
                    sqlConnection.Close();
                }
            }
            return(sqlServer);
        }
Exemple #8
0
        public static List <string> database_Names(this API_SqlServer sqlServer)
        {
            var names    = new List <string>();
            var sqlQuery = "select name from master..sysDatabases";

            foreach (DataRow row in sqlServer.executeReader(sqlQuery).Rows)
            {
                names.add(row.ItemArray[0].str());
            }
            return(names);
        }
Exemple #9
0
        public static API_SqlServer add_Viewer_QueryResult <T>(this API_SqlServer sqlServer, T control, string sqlQuery)
            where T : Control
        {
            control.clear();
            var dataTable    = sqlServer.executeReader(sqlQuery);
            var dataGridView = control.add_DataGridView();

            dataGridView.DataError += (sender, e) => {           // " dataGridView error: {0}".error(e.Context);
            };
            dataGridView.invokeOnThread(() => dataGridView.DataSource = dataTable);
            return(sqlServer);
        }
Exemple #10
0
 public static SqlConnection closeConnection(this API_SqlServer sqlServer, SqlConnection sqlConnection)
 {
     "[API_SqlServer] Closing Connection".info();
     try
     {
         sqlConnection.Close();
         return(sqlConnection);
     }
     catch (Exception ex)
     {
         sqlServer.LastError = ex.Message;
         ex.log();
     }
     return(null);
 }
Exemple #11
0
 public static SqlConnection getOpenConnection(this API_SqlServer sqlServer)
 {
     "[API_SqlServer] Opening Connection".info();
     try
     {
         SqlConnection sqlConnection = new SqlConnection(sqlServer.ConnectionString);
         sqlConnection.Open();
         return(sqlConnection);
     }
     catch (Exception ex)
     {
         sqlServer.LastError = ex.Message;
         ex.log();
     }
     return(null);
 }
Exemple #12
0
        public static T add_ConnectionStringTester <T>(this API_SqlServer sqlServer, 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();
                    sqlServer.ConnectionString = text;
                    response.set_Text("Connecting using: {0}".format(text));
                    var sqlConnection = new SqlConnection(text);
                    sqlConnection.Open();
                    response.set_Text("Connected ok");
                    afterConnect();
                }
                catch (Exception ex)
                {
                    sqlServer.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=.\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);
            return(control);
        }
Exemple #13
0
 public static API_SqlServer executeNonQuery(this API_SqlServer sqlServer, SqlConnection sqlConnection, string command)
 {
     "[API_SqlServer] Executing Non Query: {0}".info(command);
     try
     {
         SqlCommand sqlCommand = new SqlCommand();
         sqlCommand.Connection  = sqlConnection;
         sqlCommand.CommandText = command;
         sqlCommand.CommandType = CommandType.Text;
         sqlCommand.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
         sqlServer.LastError = ex.Message;
         ex.log();
     }
     return(sqlServer);
 }
Exemple #14
0
        public static API_SqlServer add_Viewer_StoredProcedures <T>(this API_SqlServer sqlServer, 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(sqlServer, 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(sqlServer.database_Names());

            database_Names.selectFirst();
            return(sqlServer);
        }
Exemple #15
0
        public static API_SqlServer add_Viewer_TablesData <T>(this API_SqlServer sqlServer, 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(sqlServer, 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);
                });
            });

            Action <Table> loadTableData =
                (table) => {
                tables_Names.backColor(Color.Salmon);
                O2Thread.mtaThread(
                    () => {
                    rowDataField.clear();
                    rowData.set_Text("");
                    dataGridView.remove_Columns();
                    if (table.TableData.isNull())
                    {
                        sqlServer.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(sqlServer.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(sqlServer);
        }
Exemple #16
0
 public Database(API_SqlServer sqlServer, string name) : this(name)
 {
     SqlServer = sqlServer;
 }
Exemple #17
0
        public static API_SqlServer add_GUI_SqlCommandExecute <T>(this API_SqlServer sqlServer, 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) => {
                sqlServer.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 = sqlServer.getOpenConnection();
                    foreach (var text in sqlTexts)
                    {
                        sqlServer.executeNonQuery(sqlConnection, text);

                        if (sqlServer.LastError.valid())
                        {
                            log.append_Line("SQL ERROR: {0}".lineBeforeAndAfter().format(sqlServer.LastError));
                            log.append_Line("ERROR: stoping execution since there was an error which executing the query: {0}".format(text).lineBeforeAndAfter());
                            break;
                        }
                    }
                    sqlServer.closeConnection(sqlConnection);
                }
                else
                {
                    log.append_Line("Executing as Non Query: {0}".format(sqlText));
                    sqlServer.LastError = "";
                    sqlServer.executeNonQuery(sqlText);
                    if (sqlServer.LastError.valid())
                    {
                        log.append_Line("SQL ERROR: {0}".lineBeforeAndAfter().format(sqlServer.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(sqlServer);
        }
Exemple #18
0
 public static Database database(this API_SqlServer sqlServer, string name)
 {
     return(new Database(sqlServer, name));
 }