Пример #1
0
        public DataTable ViewPending()
        {
            var data = new DataTable();

            using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
            {
                try
                {
                    connection.Open();
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        command.Connection = connection;
                        var query   = command.CommandText = $"SELECT Id,PoNumber,Beneficiary,Amount,(SELECT SchoolName FROM  School s WHERE s.Id=p.SchoolId) AS 'School' FROM dbo.PaymentOrder p where status='Pending'";
                        var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
                        adapter.Fill(data);
                        connection.Close();
                    }
                }
                catch (VistaDBException exception)
                {
                    MessageBox.Show("Something went wrong");
                    Log.Error(exception);
                }
            }
            return(data);
        }
 private void SetConnection(IDbConnection value)
 {
     if (_connection != value)
     {
         _connection = (VistaDBConnection)value;
     }
 }
Пример #3
0
        public double Recieved()
        {
            double Value = 0;

            using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
            {
                try
                {
                    connection.Open();
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        command.Connection  = connection;
                        command.CommandText = $"SELECT dbo.Recieved('{SchoolName}')";
                        var reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            Value = reader.GetDouble(0);
                        }
                        connection.Close();
                    }
                }
                catch (VistaDBException exception)
                {
                    MessageBox.Show(exception.Message);
                }
            }
            recieved = Value;
            return(recieved);
        }
Пример #4
0
        public DataTable ViewTransactions(string schoolname)
        {
            var data = new DataTable();

            using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
            {
                try
                {
                    connection.Open();
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        command.Connection = connection;
                        var query   = command.CommandText = $"SELECT po.Id AS 'Transaction Id',po.Beneficiary,po.Payee,po.Amount,po.Date,po.Status FROM PaymentOrder po WHERE po.SchoolId=(SELECT s.Id FROM School s WHERE s.SchoolName='{schoolname}') and po.RecievedDate BETWEEN GETDATE()-30 AND GETDATE()";
                        var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
                        adapter.Fill(data);
                        connection.Close();
                    }
                }
                catch (VistaDBException exception)
                {
                    MessageBox.Show("Something went wrong");
                    Log.Error(exception);
                }
            }
            return(data);
        }
Пример #5
0
        public double StandingOrder()
        {
            double Value = 0;

            using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
            {
                try
                {
                    connection.Open();
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        command.Connection  = connection;
                        command.CommandText = $"SELECT dbo.StandingOrder('{SchoolName}')";
                        var reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            Value = reader.GetDouble(0);
                        }
                        connection.Close();
                    }
                }
                catch (VistaDBException exception)
                {
                    MessageBox.Show("Something went wrong");
                    Log.Error(exception);
                }
            }

            return(Value);
        }
Пример #6
0
        public static List <string> Names()
        {
            List <string> names = new List <string>();

            using (VistaDBConnection connection = Connection.Connexion)
            {
                try
                {
                    connection.Open();
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        command.Connection  = connection;
                        command.CommandText = $"SELECT Name FROM dbo.Users";
                        var reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            names.Add(reader.GetString(0));
                        }
                        connection.Close();
                        if (names.Count < 1)
                        {
                            names.Add("No Data Available");
                        }
                    }
                }
                catch (VistaDBException exception)
                {
                    MessageBox.Show("Something went wrong");
                    Log.Error(exception);
                }
            }

            return(names);
        }
        public ViewSchema[] GetViews(string connectionString, DatabaseSchema database)
        {
            var views = new List <ViewSchema>();
            var extendedProperties = new List <ExtendedProperty>();

            const string sql = "SELECT VIEW_NAME, DESCRIPTION, IS_UPDATABLE FROM GetViews()";

            using (VistaDBConnection connection = GetConnection(connectionString))
                using (var adapter = new VistaDBDataAdapter(sql, connection))
                    using (var table = new DataTable())
                    {
                        adapter.Fill(table);

                        foreach (DataRow row in table.Rows)
                        {
                            string name        = row[0].ToString();
                            string description = row[1].ToString();
                            bool   isUpdatable = (bool)(row[2] ?? true);

                            extendedProperties.Clear();
                            extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, description));
                            extendedProperties.Add(ExtendedProperty.Readonly("CS_IsUpdatable", isUpdatable));

                            var view = new ViewSchema(database, name, string.Empty, DateTime.MinValue, extendedProperties.ToArray());

                            views.Add(view);
                        }
                    }

            return(views.ToArray());
        }
Пример #8
0
        DataTable IPlugin.GetViews(string database)
        {
            DataTable metaData = new DataTable();

            try
            {
                metaData = context.CreateViewsDataTable();

                using (VistaDBConnection conn = new VistaDBConnection(context.ConnectionString))
                {
                    using (VistaDBCommand cmd = new VistaDBCommand("SELECT * FROM GetViews()", conn))
                    {
                        using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
                        {
                            DataTable views = new DataTable();
                            da.Fill(views);

                            foreach (DataRow vistaRow in views.Rows)
                            {
                                DataRow row = metaData.NewRow();
                                metaData.Rows.Add(row);

                                row["TABLE_NAME"]   = vistaRow["VIEW_NAME"];
                                row["DESCRIPTION"]  = vistaRow["DESCRIPTION"];
                                row["VIEW_TEXT"]    = vistaRow["VIEW_DEFINITION"];
                                row["IS_UPDATABLE"] = vistaRow["IS_UPDATABLE"];
                            }
                        }
                    }
                }
            }
            catch {}

            return(metaData);
        }
        /// <summary>
        /// Call the Sql Function version to get the database version
        /// </summary>
        public static void CallGetDatabaseVersionFunctionSQL()
        {
            Console.WriteLine("Attempting to execute CLR Function GetDatabaseVersionFunction");
            using (VistaDBConnection connection = new VistaDBConnection(SampleRunner.ConnectionString))
            {
                connection.Open();

                try
                {
                    // Straight forward way to call a function is just using SELECT
                    // You cannot EXEC a SqlFunction, and you cannot set the command here to be a stored proc
                    // Setting this command to a stored proc is a common error, the two are not the same
                    // SqlFunction = SELECT to call
                    // SqlProcdure = EXEC or direct call using StoredProcedure command type
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        command.Connection = connection;
                        command.CommandText = "SELECT GetVersionFunction();";
                        // The results are returned as a part of the standard rowset, so we only need to get back the first entry
                        Console.WriteLine(Convert.ToString(command.ExecuteScalar()));
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("Failed to execute CLR Function GetVersionFunction, Reason: " + e.Message);
                }
            }
        }
        /// <summary>
        /// Call the export schema and data sql function to write out the xml file
        /// </summary>
        /// <param name="outputFilename">Name of the file to write to disk</param>
        public static void CallExportSchemaAndDataSQL(string outputFilename)
        {
            Console.WriteLine("Attempting to execute CLR Proc ExportSchemaAndData");
            using (VistaDBConnection connection = new VistaDBConnection())
            {
                connection.ConnectionString = SampleRunner.ConnectionString;
                connection.Open();

                try
                {
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        // Straight forward way to call a function is just using SELECT
                        // You cannot EXEC a SqlFunction, and you cannot set the command here to be a stored proc
                        // Setting this command to a stored proc is a common error, the two are not the same
                        // SqlFunction = SELECT to call
                        // SqlProcdure = EXEC or direct call using StoredProcedure command type
                        command.Connection = connection;
                        command.CommandText = string.Format("SELECT ExportSchemaAndData('{0}');", outputFilename);
                        // This command does not return anything in the rowset, so just execute non query
                        command.ExecuteNonQuery();
                    }
                    Console.WriteLine(string.Format("Schema and Data export to {0}\\{1}.xml", Directory.GetCurrentDirectory(), outputFilename));
                }
                catch (Exception e)
                {
                    Console.WriteLine("Failed to execute CLR-Proc ExportSchemaAndData, Reason: " + e.Message);
                }
            }
        }
Пример #11
0
        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);
        }
Пример #12
0
        public static void Test()
        {
            FileInfo fi = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "\\Cases\\InitialProject.cs");


            VistaDBConnection conn = new VistaDBConnection("Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "\\App_Data\\FmqStore.vdb3");

            conn.Open();

            string sql = "insert into [FileSystem](FileName,FileDir,FileSize,HashCode,BinData,FileVersion,CreateDate,LastChangeDate) values("
                         + "@FileName,@FileDir,@FileSize,@HashCode,@BinData,1, @CreateDate, @LastChangeDate"
                         + ")";
            VistaDBCommand cmd = new VistaDBCommand(sql, conn);

            cmd.Parameters.AddWithValue("@FileName", fi.Name);
            cmd.Parameters.AddWithValue("@FileDir", "/cases");
            cmd.Parameters.AddWithValue("@FileSize", fi.Length);

            byte[] fBin = GetFileBytes(fi.FullName);

            cmd.Parameters.AddWithValue("@HashCode", GetMD5Hash(fBin));
            cmd.Parameters.AddWithValue("@BinData", fBin);
            cmd.Parameters.AddWithValue("@CreateDate", fi.CreationTimeUtc);
            cmd.Parameters.AddWithValue("@LastChangeDate", fi.LastWriteTimeUtc);

            cmd.ExecuteNonQuery();

            conn.Close();
            conn.Dispose();
        }
        public ViewColumnSchema[] GetViewColumns(string connectionString, ViewSchema view)
        {
            var columns = new List <ViewColumnSchema>();

            string sql = string.Format("SELECT * FROM GetViewColumns('{0}')", view.Name);

            using (VistaDBConnection connection = GetConnection(connectionString))
                using (var adapter = new VistaDBDataAdapter(sql, connection))
                    using (var table = new DataTable())
                    {
                        adapter.Fill(table);

                        foreach (DataRow row in table.Rows)
                        {
                            string name       = row["COLUMN_NAME"].ToString();
                            string nativeType = row["DATA_TYPE_NAME"].ToString();
                            var    size       = (int)(row["COLUMN_SIZE"] ?? 0);
                            var    allowNull  = (bool)(row["ALLOW_NULL"] ?? true);

                            var column = new ViewColumnSchema(
                                view, name, GetDbType(nativeType),
                                nativeType, size, 0, 0, allowNull);

                            columns.Add(column);
                        }
                    }

            return(columns.ToArray());
        }
        private VistaDBConnection GetConnection(string connectionString)
        {
            var connection = new VistaDBConnection(connectionString);

            connection.Open();
            return(connection);
        }
Пример #15
0
 public School(string schoolName)
 {
     SchoolName = schoolName;
     using (VistaDBConnection connection = Connection.Connexion)
     {
         try
         {
             connection.Open();
             using (VistaDBCommand command = new VistaDBCommand())
             {
                 command.Connection  = connection;
                 command.CommandText = $"SELECT Id,TypeId FROM dbo.School WHERE SchoolName='{schoolName}'";
                 var reader = command.ExecuteReader();
                 while (reader.Read())
                 {
                     Id     = reader.GetInt32(0);
                     TypeId = reader.GetInt32(1);
                 }
                 connection.Close();
             }
         }
         catch (VistaDBException exception)
         {
             MessageBox.Show(exception.Message);
         }
     }
 }
Пример #16
0
        public RecievePay(List <PaymentOrder> list)
        {
            using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
            {
                try
                {
                    foreach (var pay in list)
                    {
                        connection.Open();
                        using (VistaDBCommand command = new VistaDBCommand())
                        {
                            command.Connection  = connection;
                            command.CommandText = $"UPDATE dbo.PaymentOrder SET Status = 'Recieved',RecievedBy ={LoginUser.UserId},RecievedDate = GETDATE() WHERE Id = {pay.Id}";
                            command.ExecuteNonQuery();
                            connection.Close();
                        }
                    }

                    MessageBox.Show(@"Selected payments confirmed successfully");
                }
                catch (VistaDBException exception)
                {
                    Log.Error(exception);
                }
            }
        }
Пример #17
0
        public DataTable ViewPending(string serch)
        {
            var data = new DataTable();

            using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
            {
                try
                {
                    connection.Open();
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        command.Connection = connection;
                        var query   = command.CommandText = $"SELECT PoNumber as PONumber,UserId,Date FROM dbo.PaymentOrder where status='Pending'";
                        var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
                        adapter.Fill(data);
                        connection.Close();
                    }
                }
                catch (VistaDBException exception)
                {
                    MessageBox.Show("Something went wrong");
                    Log.Error(exception);
                }
            }
            return(data);
        }
Пример #18
0
 public int Role()
 {
     using (VistaDBConnection connection = Connection.Connexion)
     {
         try
         {
             connection.Open();
             using (VistaDBCommand command = new VistaDBCommand())
             {
                 command.Connection  = connection;
                 command.CommandText = $"SELECT RoleId FROM dbo.Users WHERE Id={UserId}";
                 var reader = command.ExecuteReader();
                 while (reader.Read())
                 {
                     RoleId = reader.GetInt32(0);
                 }
                 connection.Close();
             }
         }
         catch (VistaDBException exception)
         {
             MessageBox.Show("Something went wrong");
             Log.Error(exception);
         }
     }
     return(RoleId);
 }
Пример #19
0
        public Table[] GetMetaData(string server, string database, string login, string password, string fn)
        {
            List<Table> tables = new List<Table>();

            IVistaDBDatabase vistaDB = VistaDBEngine.Connections.OpenDDA().OpenDatabase(fn, VistaDBDatabaseOpenMode.NonexclusiveReadWrite, null);

            string[] tableNames = (string[])vistaDB.EnumTables().ToArray(typeof(string));

            vistaDB.Close();

            var conn = new VistaDBConnection("Data Source=" + fn);

            foreach (string tableName in tableNames)
            {
                string className = tableName;

                if (tableName.StartsWith("tbl"))
                    className = tableName.Substring(3);

                tables.Add(GetTable(conn, tableName, className));
            }

            conn.Close();

            return tables.ToArray();
        }
Пример #20
0
        public IEnumerable <long> GetQualified(long qualifier)
        {
            List <long> qualifiedRelations = new List <long>();

            using (VistaDBConnection conn = new VistaDBConnection(this.db))
            {
                VistaDBCommand cmd;

                // get qualified root relations
                cmd = new VistaDBCommand("select id from RootRelations where qualifierId=@qualifierId", conn);
                cmd.Parameters.AddWithValue("@qualifierId", qualifier);
                using (VistaDBDataReader rd = cmd.ExecuteReader())
                {
                    while (rd.Read())
                    {
                        qualifiedRelations.Add(rd.GetInt64(0));
                    }
                }

                // get qualified inner relations
                cmd.CommandText = "select id from InnerRelations where qualifierId=@qualifierId";
                using (VistaDBDataReader rd = cmd.ExecuteReader())
                {
                    while (rd.Read())
                    {
                        qualifiedRelations.Add(rd.GetInt64(0));
                    }
                }
            }

            return(qualifiedRelations);
        }
Пример #21
0
        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);
        }
Пример #22
0
 public double Amount()
 {
     using (VistaDBConnection connection = Connection.Connexion)
     {
         try
         {
             connection.Open();
             using (VistaDBCommand command = new VistaDBCommand())
             {
                 command.Connection  = connection;
                 command.CommandText = $"SELECT Amount FROM dbo.SchoolType WHERE Id={TypeId}";
                 var reader = command.ExecuteReader();
                 while (reader.Read())
                 {
                     amount = reader.GetInt32(0);
                 }
                 connection.Close();
             }
         }
         catch (VistaDBException exception)
         {
             MessageBox.Show(exception.Message);
         }
     }
     return(amount);
 }
        public CommandSchema[] GetCommands(string connectionString, DatabaseSchema database)
        {
            var commands           = new List <CommandSchema>();
            var extendedProperties = new List <ExtendedProperty>();

            const string sql = "SELECT PROC_NAME, PROC_DESCRIPTION FROM sp_stored_procedures()";

            using (VistaDBConnection connection = GetConnection(connectionString))
                using (var adapter = new VistaDBDataAdapter(sql, connection))
                    using (var table = new DataTable())
                    {
                        adapter.Fill(table);

                        foreach (DataRow row in table.Rows)
                        {
                            string name        = row[0].ToString();
                            string description = row[1].ToString();

                            extendedProperties.Clear();
                            extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, description));

                            var command = new CommandSchema(database, name, string.Empty, DateTime.MinValue, extendedProperties.ToArray());

                            commands.Add(command);
                        }
                    }

            return(commands.ToArray());
        }
Пример #24
0
        public int FindId(User user)
        {
            int getId = 0;

            using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
            {
                try
                {
                    connection.Open();
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        command.Connection  = connection;
                        command.CommandText = $"SELECT Id FROM dbo.Users where Name='{user.Name}'";
                        var reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            getId = reader.GetInt32(0);
                        }
                        connection.Close();
                    }
                }
                catch (VistaDBException exception)
                {
                    MessageBox.Show(exception.Message);
                }
            }
            return(getId);
        }
Пример #25
0
        protected override IDbConnection CreateConnection()
        {
            VistaDBConnection conn = new VistaDBConnection(ConnectionString);

            conn.Open();

            // _serverVersion = Convert.ToInt32(conn.ServerVersion.Split('.')[0]);

            return(conn);
        }
Пример #26
0
		protected override IDbConnection CreateConnection()
		{
            VistaDBConnection conn = new VistaDBConnection(ConnectionString);

			conn.Open();

		   // _serverVersion = Convert.ToInt32(conn.ServerVersion.Split('.')[0]);

			return conn;
		}
Пример #27
0
        private string GetPassword(VistaDBConnection conn)
        {
            string password = null;

            if (conn.Password != null && conn.Password.Length > 0)
            {
                password = conn.Password;
            }

            return(password);
        }
        public DataTable GetViewData(string connectionString, ViewSchema view)
        {
            string sql = string.Format("select * from '{0}'", view.Name);

            using (VistaDBConnection connection = GetConnection(connectionString))
                using (var adapter = new VistaDBDataAdapter(sql, connection))
                    using (var dataTable = new DataTable())
                    {
                        adapter.Fill(dataTable);
                        return(dataTable);
                    }
        }
Пример #29
0
        public string GetDatabaseName()
        {
            VistaDBConnection cn = new VistaDBConnection(this.context.ConnectionString);

            string dbName = cn.DataSource;
            int    index  = dbName.LastIndexOfAny(new char[] { '\\' });

            if (index >= 0)
            {
                dbName = dbName.Substring(index + 1);
            }
            return(dbName);
        }
Пример #30
0
        List <string> IPlugin.GetPrimaryKeyColumns(string database, string table)
        {
            List <string>    primaryKeys = new List <string>();
            IVistaDBDatabase db          = null;

            try
            {
                using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
                {
                    db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
                }

                IVistaDBTableSchema tblStructure = db.TableSchema(table);

                string[] pks = null;
                if (tblStructure.Indexes.Contains("PrimaryKey"))
                {
                    pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(';');
                }
                else
                {
                    foreach (IVistaDBIndexInformation pk in tblStructure.Indexes)
                    {
                        if (pk.Primary)
                        {
                            pks = pk.KeyExpression.Split(';');
                            break;
                        }
                    }
                }

                if (pks != null)
                {
                    foreach (string pkColName in pks)
                    {
                        primaryKeys.Add(pkColName);
                    }
                }
            }
            finally
            {
                if (db != null)
                {
                    db.Close();
                }
            }

            return(primaryKeys);
        }
        /// <summary>
        /// Simplify the creation of a VistaDB command object by allowing
        /// a CommandType and Command Text to be provided
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  VistaDBCommand command = CreateCommand(conn, CommandType.Text, "Select * from Customers");
        /// </remarks>
        /// <param name="connection">A valid VistaDBConnection object</param>
        /// <param name="commandType">CommandType (TableDirect, Text)</param>
        /// <param name="commandText">CommandText</param>
        /// <returns>A valid VistaDBCommand object</returns>
        public static VistaDBCommand CreateCommand(VistaDBConnection connection, CommandType commandType, string commandText )
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            if( commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported.");

            // If we receive parameter values, we need to figure out where they go
            if ((commandText == null) && (commandText.Length<= 0)) throw new ArgumentNullException( "Command Text" );

            // Create a VistaDBCommand
            VistaDBCommand cmd = new VistaDBCommand(commandText, connection );
            cmd.CommandType = CommandType.Text ;

            return cmd;
        }
Пример #32
0
        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);
        }
Пример #33
0
        DataTable IPlugin.GetTableIndexes(string database, string table)
        {
            DataTable        metaData = new DataTable();
            IVistaDBDatabase db       = null;

            try
            {
                metaData = context.CreateIndexesDataTable();

                using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
                {
                    db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
                }

                ArrayList tables = db.EnumTables();

                IVistaDBTableSchema tblStructure = db.TableSchema(table);

                foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes)
                {
                    string[] pks = indexInfo.KeyExpression.Split(';');

                    int index = 0;
                    foreach (string colName in pks)
                    {
                        DataRow row = metaData.NewRow();
                        metaData.Rows.Add(row);

                        row["TABLE_CATALOG"] = GetDatabaseName();
                        row["TABLE_NAME"]    = tblStructure.Name;
                        row["INDEX_CATALOG"] = GetDatabaseName();
                        row["INDEX_NAME"]    = indexInfo.Name;
                        row["UNIQUE"]        = indexInfo.Unique;
                        row["COLLATION"]     = indexInfo.KeyStructure[index++].Descending ? 2 : 1;
                        row["COLUMN_NAME"]   = colName;
                    }
                }
            }
            finally
            {
                if (db != null)
                {
                    db.Close();
                }
            }

            return(metaData);
        }
Пример #34
0
 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);
 }
Пример #35
0
 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);
 }
Пример #36
0
 public void CreateStoredProcedures()
 {
     try
     {
         using (var cn = new VistaDBConnection(Properties.Settings.Default.ConnectionString))
         {
             cn.Open();
             using (var cmd = cn.CreateCommand())
             {
                 foreach (
                     var sql in
                         Regex.Split(Properties.Resources.DatabaseReset, @"^\s*GO\s*$", RegexOptions.Multiline))
                 {
                     cmd.CommandText = sql;
                     cmd.ExecuteNonQuery();
                 }
             }
         }
     }
     catch (Exception ex)
     {
         Trace.WriteLine(ex.Message);
     }
 }
 public static int GetDatabaseVersionProcedure(out string versionString )
 {
     try
     {
         //To open a SQL connection to VistaDB from within a CLR Proc you must set the connection string to Context connection=true like this....
         //NOTE: We DO want to dispose of this object because we are the ones allocating it
         using (VistaDBConnection conn = new VistaDBConnection("Context Connection=true"))
         {
             conn.Open();
             using (VistaDBCommand command = new VistaDBCommand())
             {
                 command.Connection = conn;
                 command.CommandText = "SELECT @@Version";
                 versionString = Convert.ToString(command.ExecuteScalar());
                 return 0;
             }
         }
     }
     catch (Exception e)
     {
         throw new ApplicationException("Unable to get the database version due to Application Error", e);
     }
 }
Пример #38
0
		public string GetFullDatabaseName()
		{
			VistaDBConnection cn = new VistaDBConnection(this.context.ConnectionString);
			return cn.DataSource;
		}
 public static string GetDatabaseVersionFunction()
 {
     try
     {
         //To open a SQL connection to VistaDB from within a CLR Proc you must set the connection string to Context connection=true like this....
         //NOTE: We DO want to dispose of this object because we are the ones allocating it
         using (VistaDBConnection conn = new VistaDBConnection("Context Connection=true"))
         {
             conn.Open();
             using (VistaDBCommand command = new VistaDBCommand())
             {
                 command.Connection = conn;
                 command.CommandText = "SELECT @@Version";
                 return Convert.ToString(command.ExecuteScalar());
             }
         }
     }
     catch (Exception e)
     {
         return e.Message;
     }
 }
Пример #40
0
        DataTable IMyMetaPlugin.GetViewColumns(string database, string view)
        {
			DataTable metaData = new DataTable();
			//IVistaDBDatabase db = null;

			try
			{
				metaData = context.CreateColumnsDataTable();

				using (VistaDBConnection conn = new VistaDBConnection())
				{
					conn.ConnectionString = context.ConnectionString;
					conn.Open();

					string sql = "SELECT * FROM GetViewColumns('" + view + "')";

					using (VistaDBCommand cmd = new VistaDBCommand(sql, conn))
					{
						using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
						{
							DataTable views = new DataTable();
							da.Fill(views);

							foreach(DataRow vistaRow in views.Rows)
							{
								DataRow row = metaData.NewRow();
								metaData.Rows.Add(row);

								int width		= Convert.ToInt32(vistaRow["COLUMN_SIZE"]);
								int dec			= 0; 
								int length      = 0;
								int octLength   = width;
								bool timestamp  = false;

								string type = vistaRow["DATA_TYPE_NAME"] as string;

								switch(type)
								{
									case "Char":
									case "NChar":
									case "NText":
									case "NVarchar":
									case "Text":
									case "Varchar":
										length = width;
										width  = 0;
										dec    = 0;
										break;

									case "Currency":
									case "Double":
									case "Decimal":
									case "Single":
										break;

									case "Timestamp":
										timestamp = true;
										break;

									default:
										width = 0;
										dec   = 0;
										break;
								}

								string def = Convert.ToString(vistaRow["DEFAULT_VALUE"]);

								row["TABLE_NAME"] = view;
								row["COLUMN_NAME"] = vistaRow["COLUMN_NAME"];
								row["ORDINAL_POSITION"] = vistaRow["COLUMN_ORDINAL"];
								row["IS_NULLABLE"] = vistaRow["ALLOW_NULL"];
								row["COLUMN_HASDEFAULT"] = def == string.Empty ? false : true;
								row["COLUMN_DEFAULT"] = def;
								row["IS_AUTO_KEY"] = vistaRow["IDENTITY_VALUE"];
								row["AUTO_KEY_SEED"] = vistaRow["IDENTITY_SEED"];
								row["AUTO_KEY_INCREMENT"] = vistaRow["IDENTITY_STEP"];
								row["TYPE_NAME"] = type;
								row["NUMERIC_PRECISION"] = width;
								row["NUMERIC_SCALE"] = dec;
								row["CHARACTER_MAXIMUM_LENGTH"] = length;
								row["CHARACTER_OCTET_LENGTH"] = octLength;
								row["DESCRIPTION"] = vistaRow["COLUMN_DESCRIPTION"];

								if (timestamp)
								{
									row["IS_COMPUTED"] = true;
								}
							}
						}						 
					}
				}
			}
			catch{}

			return metaData;
        }
Пример #41
0
		public string GetDatabaseName()
		{
			VistaDBConnection cn = new VistaDBConnection(this.context.ConnectionString);

			string dbName = cn.DataSource;
			int index = dbName.LastIndexOfAny(new char[]{'\\'});
			if (index >= 0)
			{
				dbName = dbName.Substring(index + 1);
			}
			return dbName;
		}
        DataTable IPlugin.GetForeignKeys(string database, string tableName)
        {
			DataTable metaData = new DataTable();
			IVistaDBDatabase db = null;

			try
			{
				metaData = context.CreateForeignKeysDataTable();

                using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
                {
                    db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
                }

				ArrayList tables = db.EnumTables(); 

				foreach (string table in tables) 
				{
					IVistaDBTableSchema tblStructure = db.TableSchema(table);

                    //==================================================================
                    // This works around a change that was made to the VistaDB provider
                    // It's ugly, we know
                    //==================================================================
                    IEnumerator enumerator = null;

                    if (useOldForeignKeyWay)
                    {
                        enumerator = tblStructure.ForeignKeys.GetEnumerator();
                    }
                    else
                    {
                        try
                        {
                            enumerator = tblStructure.ForeignKeys.Values.GetEnumerator();
                        }
                        catch
                        {
                            enumerator = tblStructure.ForeignKeys.GetEnumerator();
                            useOldForeignKeyWay = true;
                        }
                    }

                    // Okay, now that the version issues are over we just use the 'enumerator'
                    while(enumerator.MoveNext())
                    {
                        IVistaDBRelationshipInformation relInfo = enumerator.Current as IVistaDBRelationshipInformation;

						if(relInfo.ForeignTable != tableName && relInfo.PrimaryTable != tableName)
							continue;

						string fCols = relInfo.ForeignKey; 
						string pCols = String.Empty; 

						string primaryTbl  = relInfo.PrimaryTable; 
						string pkName = "";

						using (IVistaDBTableSchema pkTableStruct = db.TableSchema(primaryTbl)) 
						{ 
							foreach (IVistaDBIndexInformation idxInfo in pkTableStruct.Indexes) 
							{ 
								if (!idxInfo.Primary) 
								continue; 
								        
								pkName = idxInfo.Name;
								pCols = idxInfo.KeyExpression; 
								break; 
							} 
						} 

						string [] fColumns = fCols.Split(';'); 
						string [] pColumns = pCols.Split(';'); 

						for(int i = 0; i < fColumns.GetLength(0); i++)
						{
							DataRow row = metaData.NewRow();
							metaData.Rows.Add(row);

							row["PK_TABLE_CATALOG"] = GetDatabaseName();
							row["PK_TABLE_SCHEMA"]  = DBNull.Value;
							row["FK_TABLE_CATALOG"] = DBNull.Value;
							row["FK_TABLE_SCHEMA"]  = DBNull.Value;
							row["FK_TABLE_NAME"]    = tblStructure.Name;
							row["PK_TABLE_NAME"]    = relInfo.PrimaryTable;
							row["ORDINAL"]          = 0;
							row["FK_NAME"]          = relInfo.Name;
							row["PK_NAME"]          = pkName;
							row["PK_COLUMN_NAME"]   = pColumns[i]; 
							row["FK_COLUMN_NAME"]   = fColumns[i];

							row["UPDATE_RULE"]		= relInfo.UpdateIntegrity;
							row["DELETE_RULE"]		= relInfo.DeleteIntegrity;
						}
					} 
				}
			}
			finally
			{
				if(db != null) db.Close();
			}

			return metaData;
        }
Пример #43
0
		DataTable IMyMetaPlugin.GetViews(string database)
		{
			DataTable metaData = new DataTable();
			//IVistaDBDatabase db = null;

			try
			{
				metaData = context.CreateViewsDataTable();

				using (VistaDBConnection conn = new VistaDBConnection())
				{
					conn.ConnectionString = context.ConnectionString;
					conn.Open();

					using (VistaDBCommand cmd = new VistaDBCommand("SELECT * FROM GetViews()", conn))
					{
						using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
						{
							DataTable views = new DataTable();
							da.Fill(views);

							foreach(DataRow vistaRow in views.Rows)
							{
								DataRow row = metaData.NewRow();
								metaData.Rows.Add(row);

								row["TABLE_NAME"]   = vistaRow["VIEW_NAME"];
								row["DESCRIPTION"]  = vistaRow["DESCRIPTION"];
								row["VIEW_TEXT"]    = vistaRow["VIEW_DEFINITION"];
								row["IS_UPDATABLE"] = vistaRow["IS_UPDATABLE"];
							}
						}						 
					}
				}
			}
			catch{}

			return metaData;
		}
        /// <summary>
        /// Execute a VistaDBCommand (that returns a resultset and takes no parameters) against the provided VistaDBConnection. 
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(conn, CommandType.Text, "Select * from TableTransaction", ds, new string[] {"orders"});
        /// </remarks>
        /// <param name="connection">A valid VistaDBConnection</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)
        /// </param>    
        public static void FillDataset(VistaDBConnection connection, CommandType commandType, 
			string commandText, DataSet dataSet, string[] tableNames)
        {
            FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
        }
        /// <summary>
        /// Execute a VistaDBCommand (that returns a resultset) against the specified VistaDBConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        /// string  r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid VistaDBConnection</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param>
        /// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
        /// <returns>An string containing the resultset generated by the command</returns>
        public static string ExecuteXml(VistaDBConnection connection, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            bool mustCloseConnection = false;
            // Create a command and prepare it for execution
            VistaDBCommand cmd = new VistaDBCommand();
            try
            {
                PrepareCommand(cmd, connection, (VistaDBTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

                // Create the DataAdapter & DataSet
                VistaDBDataAdapter obj_Adapter =new VistaDBDataAdapter (cmd);
                DataSet ds=new DataSet();
                ds.Locale  =CultureInfo.InvariantCulture;
                obj_Adapter.Fill(ds);

                // Detach the VistaDBParameters from the command object, so they can be used again
                cmd.Parameters.Clear();
                string retval= ds.GetXml();
                 ds.Clear();
                 obj_Adapter.Dispose ();
                return retval;

            }
            catch
            {
                if( mustCloseConnection )
                    connection.Close();
                throw;
            }
        }
        DataTable IPlugin.GetTables(string database)
        {
			DataTable metaData = new DataTable();
			IVistaDBDatabase db = null;

            try
            {
                metaData = context.CreateTablesDataTable();

                using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
                {
                    db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
                }

                ArrayList tables = db.EnumTables();

                foreach (string table in tables)
                {
                    IVistaDBTableSchema tblStructure = db.TableSchema(table);

                    DataRow row = metaData.NewRow();
                    metaData.Rows.Add(row);

                    row["TABLE_NAME"] = tblStructure.Name;
                    row["DESCRIPTION"] = tblStructure.Description;
                }
            }
            finally
            {
                if (db != null) db.Close();
            }

			return metaData;
        }
        /// <summary>
        /// Create and prepare a VistaDBCommand, and call ExecuteReader with the appropriate CommandBehavior.
        /// </summary>
        /// <remarks>
        /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
        /// 
        /// If the caller provided the connection, we want to leave it to them to manage.
        /// </remarks>
        /// <param name="connection">A valid VistaDBConnection, on which to execute this command</param>
        /// <param name="transaction">A valid VistaDBTransaction, or 'null'</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="commandParameters">An array of VistaDBParameters to be associated with the command or 'null' if no parameters are required</param>
        /// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by VistaDBHelper</param>
        /// <returns>VistaDBDataReader containing the results of the command</returns>
        private static VistaDBDataReader ExecuteReader(VistaDBConnection connection, VistaDBTransaction transaction, CommandType commandType, string commandText, VistaDBParameter[] commandParameters, VistaDBConnectionOwnership connectionOwnership)
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            bool mustCloseConnection = false;
            // Create a command and prepare it for execution
            VistaDBCommand cmd = new VistaDBCommand();
            try
            {
                PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

                // Create a reader
                VistaDBDataReader dataReader;

                // Call ExecuteReader with the appropriate CommandBehavior
                if (connectionOwnership == VistaDBConnectionOwnership.External)
                {
                    dataReader = cmd.ExecuteReader();
                }
                else
                {
                    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }

                // Detach the VistaDBParameters from the command object, so they can be used again.
                // HACK: There is a problem here, the output parameter values are fletched
                // when the reader is closed, so if the parameters are detached from the command
                // then the VistaDBReader can´t set its values.
                // When this happen, the parameters can´t be used again in other command.
                bool canClear = true;
                foreach(VistaDBParameter commandParameter in cmd.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                        canClear = false;
                }

                if (canClear)
                {
                    cmd.Parameters.Clear();
                }

                return dataReader;
            }
            catch
            {
                if( mustCloseConnection )
                    connection.Close();
                throw;
            }
        }
        DataTable IPlugin.GetTableColumns(string database, string table)
        {
			DataTable metaData = new DataTable();
			IVistaDBDatabase db = null;

			try
			{
				metaData = context.CreateColumnsDataTable();

                using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
                {
                    db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
                }

				ArrayList tables = db.EnumTables();

				IVistaDBTableSchema tblStructure = db.TableSchema(table);

				foreach (IVistaDBColumnAttributes c in tblStructure) 
				{ 
					string colName = c.Name;

					string def = "";
					if(tblStructure.Defaults.Contains(colName))
					{
						def = tblStructure.Defaults[colName].Expression;
					}
					int width		= c.MaxLength; //c.ColumnWidth;
					int dec			= 0; //c.ColumnDecimals;
					int length      = 0;
					int octLength   = width;

					IVistaDBIdentityInformation identity = null;
					if(tblStructure.Identities.Contains(colName))
					{
						identity = tblStructure.Identities[colName];
					}

					string[] pks = null;
					if(tblStructure.Indexes.Contains("PrimaryKey"))
					{
						pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(';');
					}
					else
					{
						foreach(IVistaDBIndexInformation pk in tblStructure.Indexes)
						{
							if(pk.Primary)
							{
								pks = pk.KeyExpression.Split(';');
								break;
							}
						}
					}

					System.Collections.Hashtable pkCols = null;
					if(pks != null)
					{
						pkCols = new Hashtable();
						foreach(string pkColName in pks)
						{
							pkCols[pkColName] = true;
						}
					}

					switch(c.Type)
					{
						case VistaDBType.Char:
						case VistaDBType.NChar:
						case VistaDBType.NText:
						case VistaDBType.NVarChar:
						case VistaDBType.Text:
						case VistaDBType.VarChar:
							length    = width;
							width     = 0;
							dec       = 0;
							break;

						case VistaDBType.Money:
						case VistaDBType.Float:
						case VistaDBType.Decimal:
						case VistaDBType.Real:
							break;

						default:
							width = 0;
							dec   = 0;
							break;
					}

					DataRow row = metaData.NewRow();
					metaData.Rows.Add(row);

					row["TABLE_NAME"] = tblStructure.Name;
					row["COLUMN_NAME"] = c.Name;
					row["ORDINAL_POSITION"] = c.RowIndex;
					row["IS_NULLABLE"] = c.AllowNull;
					row["COLUMN_HASDEFAULT"] = def == string.Empty ? false : true;
					row["COLUMN_DEFAULT"] = def;
					row["IS_AUTO_KEY"] = identity == null ? false : true;
					row["AUTO_KEY_SEED"] = 1;
					row["AUTO_KEY_INCREMENT"] = identity == null ? 0 : Convert.ToInt32(identity.StepExpression);
					row["TYPE_NAME"] = c.Type.ToString();
					row["NUMERIC_PRECISION"] = width;
					row["NUMERIC_SCALE"] = dec;
					row["CHARACTER_MAXIMUM_LENGTH"] = length;
					row["CHARACTER_OCTET_LENGTH"] = octLength;
					row["DESCRIPTION"] = c.Description;

                    string type = (string)row["TYPE_NAME"];
                    row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(type, length, (short)width, (short)dec);

					if (c.Type == VistaDBType.Timestamp)
					{
						row["IS_COMPUTED"] = true;
					}

                    row["IS_CONCURRENCY"] = type == "Timestamp" ? true : false; 
				} 

			}
			finally
			{
				if(db != null) db.Close();
			}

			return metaData;
        }
        List<string> IPlugin.GetPrimaryKeyColumns(string database, string table)
        {
			List<string> primaryKeys = new List<string>();
			IVistaDBDatabase db = null;

			try
			{
                using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
                {
                    db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
                }

				IVistaDBTableSchema tblStructure = db.TableSchema(table);

				string[] pks = null;
				if(tblStructure.Indexes.Contains("PrimaryKey"))
				{
					pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(';');
				}
				else
				{
					foreach(IVistaDBIndexInformation pk in tblStructure.Indexes)
					{
						if(pk.Primary)
						{
							pks = pk.KeyExpression.Split(';');
							break;
						}
					}
				}

				if(pks != null)
				{
					foreach(string pkColName in pks)
					{
						primaryKeys.Add(pkColName);
					}
				}
			}
			finally
			{
				if(db != null) db.Close();
			}

			return primaryKeys;
        }
        DataTable IPlugin.GetTableIndexes(string database, string table)
        {
			DataTable metaData = new DataTable();
			IVistaDBDatabase db = null;

			try
			{
				metaData = context.CreateIndexesDataTable();

                using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
                {
                    db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
                }

				ArrayList tables = db.EnumTables();

				IVistaDBTableSchema tblStructure = db.TableSchema(table);

				foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes) 
				{ 
					string[] pks = indexInfo.KeyExpression.Split(';');

					int index = 0;
					foreach(string colName in pks)
					{
						DataRow row = metaData.NewRow();
						metaData.Rows.Add(row);

						row["TABLE_CATALOG"] = GetDatabaseName();
						row["TABLE_NAME"] = tblStructure.Name;
						row["INDEX_CATALOG"] = GetDatabaseName();
						row["INDEX_NAME"] = indexInfo.Name;
						row["UNIQUE"] = indexInfo.Unique;
						row["COLLATION"] = indexInfo.KeyStructure[index++].Descending ? 2 : 1;
						row["COLUMN_NAME"] = colName;
					}
				} 
			}
			finally
			{
				if(db != null) db.Close();
			}

			return metaData;
        }
        /// <summary>
        /// Call the Stored Proc version to get the database version
        /// </summary>
        public static void CallGetDatabaseVersionProcedureSQL()
        {
            Console.WriteLine("Attempting to execute CLR Procedure GetVersionProcedure");

            using (VistaDBConnection connection = new VistaDBConnection(SampleRunner.ConnectionString))
            {
                connection.Open();

                try
                {

                    // Setup a command against the database like any other command, but then you have to change the command type
                    // to tell it you are calling a stored proc directly
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        // Use our connection from above
                        command.Connection = connection;

                        // Put the name of the stored proc, you don't need to EXEC.  This command will be called directly
                        // Be sure to include all the parameters
                        command.CommandText = "GetVersionProcedure(@versionout);";
                        command.CommandType = System.Data.CommandType.StoredProcedure;  // Normally this is just text that is being executed

                        // Build up the parameter to the clr proc
                        VistaDBParameter outparam = new VistaDBParameter();
                        // This name has to match the entry in the commandtext
                        outparam.ParameterName = "@versionout";
                        // Telling it that this is an OUTPUT parameter
                        // This is how you should always get values back from a stored proc.  The return value in a stored proc is really only
                        // meant to tell you the number of rows affected, not values.
                        outparam.Direction = System.Data.ParameterDirection.Output;

                        // Add it to the command
                        command.Parameters.Add(outparam);

                        // We are not expecting any return values, and the output parameters will still be filled out
                        // using ExecuteNonQuery.  This saves object setup and teardown of a reader when we don't need it.
                        command.ExecuteNonQuery();

                        // Make sure the outparam is not null
                        if (outparam.Value != null)
                        {
                            // Print it to the console
                            Console.WriteLine(Convert.ToString(outparam.Value));
                        }

                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("Failed to execute CLR Function GetVersionProcedure, Reason: " + e.Message);
                }
            }
        }
        /// <summary>
        /// Execute a VistaDBCommand (that returns a 1x1 resultset) against the specified VistaDBConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar(conn, CommandType.Text, "Select count(Order) from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid VistaDBConnection</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalar(VistaDBConnection connection, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            // Create a command and prepare it for execution
            VistaDBCommand cmd = new VistaDBCommand();

            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (VistaDBTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

            // Execute the command & return the results
            object retval = cmd.ExecuteScalar();

            // Detach the VistaDBParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            if( mustCloseConnection )
                connection.Close();

            return retval;
        }
Пример #53
0
        /// <summary>
        /// Logs an error to the database.
        /// </summary>
        /// <remarks>
        /// Use the stored procedure called by this implementation to set a
        /// policy on how long errors are kept in the log. The default
        /// implementation stores all errors for an indefinite time.
        /// </remarks>

        public override string Log(Error error)
        {
            if (error == null)
                throw new ArgumentNullException("error");

            string errorXml = ErrorXml.EncodeString(error);

            using (VistaDBConnection connection = new VistaDBConnection(this.ConnectionString))
            using (VistaDBCommand command = connection.CreateCommand())
            {
                connection.Open();
                command.CommandText = @"INSERT INTO ELMAH_Error
                                            (Application, Host, Type, Source, 
                                            Message, [User], AllXml, StatusCode, TimeUtc)
                                        VALUES
                                            (@Application, @Host, @Type, @Source,
                                            @Message, @User, @AllXml, @StatusCode, @TimeUtc);

                                        SELECT @@IDENTITY";
                command.CommandType = CommandType.Text;

                VistaDBParameterCollection parameters = command.Parameters;
                parameters.Add("@Application", VistaDBType.NVarChar, _maxAppNameLength).Value = ApplicationName;
                parameters.Add("@Host", VistaDBType.NVarChar, 30).Value = error.HostName;
                parameters.Add("@Type", VistaDBType.NVarChar, 100).Value = error.Type;
                parameters.Add("@Source", VistaDBType.NVarChar, 60).Value = error.Source;
                parameters.Add("@Message", VistaDBType.NVarChar, 500).Value = error.Message;
                parameters.Add("@User", VistaDBType.NVarChar, 50).Value = error.User;
                parameters.Add("@AllXml", VistaDBType.NText).Value = errorXml;
                parameters.Add("@StatusCode", VistaDBType.Int).Value = error.StatusCode;
                parameters.Add("@TimeUtc", VistaDBType.DateTime).Value = error.Time.ToUniversalTime();

                return Convert.ToString(command.ExecuteScalar(), CultureInfo.InvariantCulture);
            }
        }
        private string GetPassword(VistaDBConnection conn)
        {
            string password = null;

            if (conn.Password != null && conn.Password.Length > 0)
            {
                password = conn.Password;
            }

            return password;
        }
        /// <summary>
        /// Execute a VistaDBCommand (that returns a resultset) against the specified VistaDBConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new VistaDBParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid VistaDBConnection</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)
        /// </param>
        /// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
        public static void FillDataset(VistaDBConnection connection, CommandType commandType, 
			string commandText, DataSet dataSet, string[] tableNames,
			params VistaDBParameter[] commandParameters)
        {
            FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
        }
Пример #56
0
        /// <summary>
        /// Returns the specified error from the database, or null 
        /// if it does not exist.
        /// </summary>

        public override ErrorLogEntry GetError(string id)
        {
            if (id == null)
                throw new ArgumentNullException("id");

            if (id.Length == 0)
                throw new ArgumentException(null, "id");

            int errorId;
            try
            {
                errorId = int.Parse(id, CultureInfo.InvariantCulture);
            }
            catch (FormatException e)
            {
                throw new ArgumentException(e.Message, "id", e);
            }
            catch (OverflowException e)
            {
                throw new ArgumentException(e.Message, "id", e);
            }

            string errorXml;

            using (VistaDBConnection connection = new VistaDBConnection(this.ConnectionString))
            using (VistaDBCommand command = connection.CreateCommand())
            {
                command.CommandText = @"SELECT  AllXml
                                        FROM    ELMAH_Error
                                        WHERE   ErrorId = @ErrorId";
                command.CommandType = CommandType.Text;

                VistaDBParameterCollection parameters = command.Parameters;
                parameters.Add("@ErrorId", VistaDBType.Int).Value = errorId;

                connection.Open();
                
                // NB this has been deliberately done like this as command.ExecuteScalar 
                // is not exhibiting the expected behaviour in VistaDB at the moment
                using (VistaDBDataReader dr = command.ExecuteReader())
                {
                    if (dr.Read())
                        errorXml = dr[0] as string;
                    else
                        errorXml = null;
                }
            }

            if (errorXml == null)
                return null;

            Error error = ErrorXml.DecodeString(errorXml);
            return new ErrorLogEntry(this, id, error);
        }
        /// <summary>
        /// Execute a VistaDBCommand (that returns a resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(connString, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new VistaDBParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a VistaDBConnection</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)
        /// </param>
        public static void FillDataset(string connectionString, CommandType commandType,
			string commandText, DataSet dataSet, string[] tableNames,
			params VistaDBParameter[] commandParameters)
        {
            if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
            if( dataSet == null ) throw new ArgumentNullException( "dataSet" );
            // Create & open a VistaDBConnection, and dispose of it after we are done
            using (VistaDBConnection connection = new VistaDBConnection(connectionString))
            {
                // Call the overload that takes a connection in place of the connection string
                FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
            }
        }
Пример #58
0
        private void InitializeDatabase()
        {
            string connectionString = ConnectionString;
            Debug.AssertStringNotEmpty(connectionString);

            if (File.Exists(_databasePath))
                return;

            //
            // Make sure that we don't have multiple threads all trying to create the database
            //

            lock (_lock)
            {
                //
                // Just double check that no other thread has created the database while
                // we were waiting for the lock
                //

                if (File.Exists(_databasePath))
                    return;

                VistaDBConnectionStringBuilder builder = new VistaDBConnectionStringBuilder(connectionString);

                using (VistaDBConnection connection = new VistaDBConnection())
                using (VistaDBCommand command = connection.CreateCommand())
                {
                    string passwordClause = string.Empty;
                    if (!string.IsNullOrEmpty(builder.Password))
                        passwordClause = " PASSWORD '" + EscapeApostrophes(builder.Password) + "',";

                    // create the database using the webserver's default locale
                    command.CommandText = "CREATE DATABASE '" + EscapeApostrophes(_databasePath) + "'" + passwordClause + ", PAGE SIZE 1, CASE SENSITIVE FALSE;";
                    command.ExecuteNonQuery();

                    const string ddlScript = @"
                    CREATE TABLE [ELMAH_Error]
                    (
                        [ErrorId] INT NOT NULL,
                        [Application] NVARCHAR (60) NOT NULL,
                        [Host] NVARCHAR (50) NOT NULL,
                        [Type] NVARCHAR (100) NOT NULL,
                        [Source] NVARCHAR (60) NOT NULL,
                        [Message] NVARCHAR (500) NOT NULL,
                        [User] NVARCHAR (50) NOT NULL,
                        [StatusCode] INT NOT NULL,
                        [TimeUtc] DATETIME NOT NULL,
                        [AllXml] NTEXT NOT NULL,
                        CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY ([ErrorId])
                    )

                    GO

                    ALTER TABLE [ELMAH_Error]
                    ALTER COLUMN [ErrorId] INT NOT NULL IDENTITY (1, 1)

                    GO

                    CREATE INDEX [IX_ELMAH_Error_App_Time_Id] ON [ELMAH_Error] ([TimeUtc] DESC, [ErrorId] DESC)";

                    foreach (string batch in ScriptToBatches(ddlScript))
                    {
                        command.CommandText = batch;
                        command.ExecuteNonQuery();
                    }
                }
            }
        }
 /// <summary>
 /// Execute a VistaDBCommand (that returns a 1x1 resultset) against the database specified in the connection string 
 /// using the provided parameters.
 /// </summary>
 /// <remarks>
 /// e.g.:  
 ///  int orderCount = (int)ExecuteScalar(connString, CommandType.Text, "Select count(Order) from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
 /// </remarks>
 /// <param name="connectionString">A valid connection string for a VistaDBConnection</param>
 /// <param name="commandType">The CommandType (TableDirect, Text)</param>
 /// <param name="commandText">The T-SQL command</param>
 /// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
 /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
 {
     if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
     // Create & open a VistaDBConnection, and dispose of it after we are done
     using (VistaDBConnection connection = new VistaDBConnection(connectionString))
     {
         // Call the overload that takes a connection in place of the connection string
         return ExecuteScalar(connection, commandType, commandText, commandParameters);
     }
 }
 /// <summary>
 /// Execute a VistaDBCommand (that returns a resultset and takes no parameters) against the provided VistaDBConnection. 
 /// </summary>
 /// <remarks>
 /// e.g.:  
 ///  string r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction");
 /// </remarks>
 /// <param name="connection">A valid VistaDBConnection</param>
 /// <param name="commandType">The CommandType (TableDirect, Text)</param>
 /// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param>
 /// <returns>An string containing the resultset generated by the command</returns>
 public static string ExecuteXml(VistaDBConnection connection, CommandType commandType, string commandText)
 {
     // Pass through the call providing null for the set of VistaDBParameters
     return ExecuteXml(connection, commandType, commandText, (VistaDBParameter[])null);
 }