Beispiel #1
0
        /// <summary>
        /// Executes the select.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="columns">A list of column names</param>
        /// <returns>Arraylist of arrays. Each array is one row in the dataset.</returns>
        public ArrayList executeSelect(Select query, out List<string> columns)
        {
            columns = new List<string>();
            ArrayList resultSet = new ArrayList();
            lock (this)
            {
                MySqlDataReader dr = this.executeReaderQuery(query.ToString());
                if (dr != null)
                {
                    try
                    {

                        DataTableReader cols = dr.GetSchemaTable().CreateDataReader();
                        while (cols.Read())
                        {
                            columns.Add((string) cols.GetValue(0));
                        }
                        cols.Close();

                        while (dr.Read())
                        {
                            object[] row = new object[dr.FieldCount];
                            dr.GetValues(row);
                            resultSet.Add(row);
                        }
                    }
                    catch (MySqlException ex)
                    {
                        GlobalFunctions.errorLog(ex);
                        throw;
                    }
                    finally
                    {
                        dr.Close();
                    }
                }
            }
            return resultSet;
        }
 public void Select_Expression()
 {
     SubSonic.SqlQuery q = new Select().Expression("dbo.MyFunction(ProductID) as rows")
         .From("Products").WhereExpression("categoryID")
         .IsLessThan(5).And("ProductID")
         .IsGreaterThan(3)
         .CloseExpression()
         .OrExpression("categoryID")
         .IsGreaterThan(8)
         .And("productID")
         .IsLessThan(2)
         .CloseExpression();
     string sql = q.ToString();
     Assert.AreEqual(
         "SELECT [dbo].[Products].[ProductID], [dbo].[Products].[ProductName], [dbo].[Products].[SupplierID], [dbo].[Products].[CategoryID], [dbo].[Products].[QuantityPerUnit], [dbo].[Products].[UnitPrice], [dbo].[Products].[UnitsInStock], [dbo].[Products].[UnitsOnOrder], [dbo].[Products].[ReorderLevel], [dbo].[Products].[Discontinued], [dbo].[Products].[AttributeXML], [dbo].[Products].[DateCreated], [dbo].[Products].[ProductGUID], [dbo].[Products].[CreatedOn], [dbo].[Products].[CreatedBy], [dbo].[Products].[ModifiedOn], [dbo].[Products].[ModifiedBy], [dbo].[Products].[Deleted]\r\n,dbo.MyFunction(ProductID) as rows\r\n FROM [dbo].[Products]\r\n WHERE ([dbo].[Products].[CategoryID] < @CategoryID0\r\n AND [dbo].[Products].[ProductID] > @ProductID1\r\n)\r\n OR ([dbo].[Products].[CategoryID] > @CategoryID3\r\n AND [dbo].[Products].[ProductID] < @ProductID4\r\n)\r\n",
         sql);
 }
 public void Select_Expression()
 {
     SubSonic.SqlQuery q = new Select().Expression("dbo.MyFunction(ProductID) as rows")
         .From("Products").WhereExpression("categoryID")
         .IsLessThan(5).And("ProductID")
         .IsGreaterThan(3)
         .CloseExpression()
         .OrExpression("categoryID")
         .IsGreaterThan(8)
         .And("productID")
         .IsLessThan(2)
         .CloseExpression();
     string sql = q.ToString();
     
     Assert.AreEqual(
         "SELECT `main`.`Products`.`ProductID`, `main`.`Products`.`ProductName`, `main`.`Products`.`SupplierID`, `main`.`Products`.`CategoryID`, `main`.`Products`.`QuantityPerUnit`, `main`.`Products`.`UnitPrice`, `main`.`Products`.`UnitsInStock`, `main`.`Products`.`UnitsOnOrder`, `main`.`Products`.`ReorderLevel`, `main`.`Products`.`Discontinued`, `main`.`Products`.`AttributeXML`, `main`.`Products`.`DateCreated`, `main`.`Products`.`ProductGUID`, `main`.`Products`.`CreatedOn`, `main`.`Products`.`CreatedBy`, `main`.`Products`.`ModifiedOn`, `main`.`Products`.`ModifiedBy`, `main`.`Products`.`Deleted`\r\n,dbo.MyFunction(ProductID) as rows\r\n FROM `main`.`Products`\r\n WHERE (`main`.`Products`.`CategoryID` < @CategoryID0\r\n AND `main`.`Products`.`ProductID` > @ProductID1\r\n)\r\n OR (`main`.`Products`.`CategoryID` > @CategoryID3\r\n AND `main`.`Products`.`ProductID` < @ProductID4\r\n)\r\n",
         sql);
 }