/// <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);
}