public void CanGroupBySingleColumn()
{
MySqlDataAdapter adapter = new MySqlDataAdapter(
"SELECT Name, COUNT(Id) as Count FROM Companies GROUP BY Name", conn);
DataTable table = new DataTable();
adapter.Fill(table);
using (testEntities context = new testEntities())
{
var companies = from c in context.Companies
group c by c.Name into cgroup
select new
{
Name = cgroup.Key,
Count = cgroup.Count()
};
string sql = companies.ToTraceString();
CheckSql(sql, SQLSyntax.CanGroupBySingleColumn);
int i = 0;
foreach (var company in companies)
{
Assert.AreEqual(table.Rows[i][0], company.Name);
Assert.AreEqual(table.Rows[i][1], company.Count);
i++;
}
}
}
public void AverageWithGrouping()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT AVG(Freight) FROM Orders GROUP BY StoreId", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = "SELECT AVG(o.Freight) FROM Orders AS o GROUP BY o.Store.Id";
ObjectQuery<DbDataRecord> q = context.CreateQuery<DbDataRecord>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.AverageWithGrouping);
foreach (object x in q)
{
string s = x.GetType().ToString();
}
int i = 0;
foreach (var freight in q)
{
// Assert.AreEqual(Convert.ToInt32(dt.Rows[i++][0]), Convert.ToInt32(freight));
}
}
}
public void Delete()
{
using (testEntities context = new testEntities())
{
foreach (Book b in context.Books)
context.DeleteObject(b);
context.SaveChanges();
}
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Books", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(0, dt.Rows.Count);
}
public void SimpleDeleteAllRows()
{
using (testEntities context = new testEntities())
{
foreach (Toy t in context.Toys)
context.DeleteObject(t);
context.SaveChanges();
EntityConnection ec = context.Connection as EntityConnection;
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM toys",
(MySqlConnection)ec.StoreConnection);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(0, dt.Rows.Count);
}
}
public void Distinct()
{
using (testEntities context = new testEntities())
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Companies LIMIT 2", conn);
DataTable dt = new DataTable();
da.Fill(dt);
int i = 0;
var query = context.Companies.Top("2");
foreach (Company c in query)
{
Assert.AreEqual(dt.Rows[i++]["id"], c.Id);
}
}
}
public void SimpleDeleteRowByParameter()
{
using (testEntities context = new testEntities())
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM toys WHERE minage=3", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.IsTrue(dt.Rows.Count > 0);
ObjectQuery<Toy> toys = context.Toys.Where("it.MinAge = @age", new ObjectParameter("age", 3));
foreach (Toy t in toys)
context.DeleteObject(t);
context.SaveChanges();
dt.Clear();
da.Fill(dt);
Assert.AreEqual(0, dt.Rows.Count);
}
}
public void Skip()
{
using (testEntities context = new testEntities())
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Companies LIMIT 3,20", conn);
DataTable dt = new DataTable();
da.Fill(dt);
int i = 0;
var query = context.Companies.Skip("it.Id", "3");
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.Skip);
foreach (Company c in query)
{
Assert.AreEqual(dt.Rows[i++]["id"], c.Id);
}
}
}
public void SelectWithComplexType()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT c.LastName FROM Employees AS c WHERE c.Age > 20", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = @"SELECT c.LastName FROM Employees AS c WHERE c.Age > 20";
ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.SelectWithComplexType);
int i = 0;
foreach (DbDataRecord s in query)
Assert.AreEqual(dt.Rows[i++][0], s.GetString(0));
}
}
public void Any()
{
MySqlDataAdapter da = new MySqlDataAdapter(
@"SELECT a.id FROM authors a WHERE NOT EXISTS(SELECT * FROM books b WHERE b.author_id=a.id)", conn);
DataTable dt = new DataTable();
da.Fill(dt);
int i = 0;
// find all authors that are in our db with no books
using (testEntities context = new testEntities())
{
var authors = from a in context.Authors where !a.Books.Any() select a;
string sql = authors.ToTraceString();
CheckSql(sql, SQLSyntax.Any);
foreach (Author a in authors)
Assert.AreEqual(dt.Rows[i++]["id"], a.Id);
}
}
public void InsertSingleRow()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM companies", conn);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow lastRow = dt.Rows[dt.Rows.Count - 1];
int lastId = (int)lastRow["id"];
DateTime dateBegan = DateTime.Now;
using (testEntities context = new testEntities())
{
Company c = new Company();
c.Id = 23;
c.Name = "Yoyo";
c.NumEmployees = 486;
c.DateBegan = dateBegan;
c.Address.Address = "212 My Street.";
c.Address.City = "Helena";
c.Address.State = "MT";
c.Address.ZipCode = "44558";
context.AddToCompanies(c);
int result = context.SaveChanges();
DataTable afterInsert = new DataTable();
da.Fill(afterInsert);
lastRow = afterInsert.Rows[afterInsert.Rows.Count - 1];
Assert.AreEqual(dt.Rows.Count + 1, afterInsert.Rows.Count);
Assert.AreEqual(lastId+1, lastRow["id"]);
Assert.AreEqual("Yoyo", lastRow["name"]);
Assert.AreEqual(486, lastRow["numemployees"]);
DateTime insertedDT = (DateTime)lastRow["dateBegan"];
Assert.AreEqual(dateBegan.Date, insertedDT.Date);
Assert.AreEqual("212 My Street.", lastRow["address"]);
Assert.AreEqual("Helena", lastRow["city"]);
Assert.AreEqual("MT", lastRow["state"]);
Assert.AreEqual("44558", lastRow["zipcode"]);
}
}
public void Exists()
{
MySqlDataAdapter da = new MySqlDataAdapter(
@"SELECT c.* FROM Companies c WHERE EXISTS
(SELECT * FROM Toys t WHERE t.SupplierId=c.Id && t.MinAge < 4)", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = @"SELECT VALUE c FROM Companies AS c WHERE EXISTS(
SELECT p FROM c.Toys AS p WHERE p.MinAge < 4)";
ObjectQuery<Company> query = context.CreateQuery<Company>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.Exists);
int i = 0;
foreach(Company c in query)
Assert.AreEqual(dt.Rows[i++]["id"], c.Id);
}
}
public void UnionAll()
{
using (testEntities context = new testEntities())
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT t.Id FROM Toys t UNION ALL SELECT c.Id FROM Companies c", conn);
DataTable dt = new DataTable();
da.Fill(dt);
string entitySQL = @"(SELECT t.Id, t.Name FROM Toys AS t)
UNION ALL (SELECT c.Id, c.Name FROM Companies AS c)";
ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(entitySQL);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.UnionAll);
int i = 0;
foreach (DbDataRecord r in query)
{
i++;
}
Assert.AreEqual(dt.Rows.Count, i);
}
}
public void WhereWithRelatedEntities2()
{
MySqlDataAdapter da = new MySqlDataAdapter(
@"SELECT c.* FROM Toys t LEFT JOIN Companies c ON c.Id=t.SupplierId
WHERE c.State<>'TX' AND c.State<>'AZ'", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = @"SELECT VALUE t FROM Toys AS t
WHERE t.Supplier.Address.State<>'TX' AND t.Supplier.Address.State <> 'AZ'";
ObjectQuery<Toy> query = context.CreateQuery<Toy>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.WhereWithRelatedEntities2);
int i = 0;
foreach (Toy t in query)
{
Assert.AreEqual(dt.Rows[i++]["id"], t.Id);
}
}
}
public void WhereLiteralOnRelation()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT id FROM Companies WHERE city = 'Dallas'", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE c FROM Companies AS c WHERE c.Address.City = 'Dallas'";
ObjectQuery<Company> query = context.CreateQuery<Company>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.WhereLiteralOnRelation);
int i = 0;
foreach (Company c in query)
Assert.AreEqual(dt.Rows[i++]["id"], c.Id);
}
}
public void SimpleSelectWithParam()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Toys WHERE minage>3", conn);
DataTable toys = new DataTable();
da.Fill(toys);
int i = 0;
using (testEntities context = new testEntities())
{
var query = context.CreateQuery<Toy>("SELECT VALUE t FROM Toys AS t WHERE t.MinAge>@age");
query.Parameters.Add(new ObjectParameter("age", 3));
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.SimpleSelectWithParam);
foreach (Toy t in query)
{
Assert.AreEqual(toys.Rows[i++]["name"], t.Name);
}
}
}
/// <include file='docs/MySqlCommandBuilder.xml' path='docs/Ctor2/*'/>
public MySqlCommandBuilder(MySqlDataAdapter adapter)
: this()
{
DataAdapter = adapter;
}
/// <summary>
/// Returns the collection of settings property values for the specified application instance and settings property group.
/// </summary>
/// <param name="context">A <see cref="T:System.Configuration.SettingsContext"/> describing the current application use.</param>
/// <param name="collection">A <see cref="T:System.Configuration.SettingsPropertyCollection"/> containing the settings property group whose values are to be retrieved.</param>
/// <returns>
/// A <see cref="T:System.Configuration.SettingsPropertyValueCollection"/> containing the values for the specified settings property group.
/// </returns>
public override SettingsPropertyValueCollection GetPropertyValues(
SettingsContext context, SettingsPropertyCollection collection)
{
SettingsPropertyValueCollection values = new SettingsPropertyValueCollection();
if (collection.Count < 1) return values;
string username = (string)context["UserName"];
foreach (SettingsProperty property in collection)
{
if (property.PropertyType.IsPrimitive || property.PropertyType == typeof(string))
property.SerializeAs = SettingsSerializeAs.String;
else
property.SerializeAs = SettingsSerializeAs.Xml;
values.Add(new SettingsPropertyValue(property));
}
if (String.IsNullOrEmpty(username))
return values;
// retrieve encoded profile data from the database
try
{
using (MySqlConnection c = new MySqlConnection(connectionString))
{
c.Open();
MySqlCommand cmd = new MySqlCommand(
@"SELECT * FROM my_aspnet_Profiles p
JOIN my_aspnet_Users u ON u.id = p.userId
WHERE u.applicationId = @appId AND u.name = @name", c);
cmd.Parameters.AddWithValue("@appId", app.FetchId(c));
cmd.Parameters.AddWithValue("@name", username);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
DecodeProfileData(dt.Rows[0], values);
return values;
}
}
catch (Exception ex)
{
throw new ProviderException(Resources.UnableToRetrieveProfileData, ex);
}
}
public void MaxInSubQuery2()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT s.* FROM Stores AS s WHERE s.id=(SELECT MAX(o.storeId) FROM Orders AS o)", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = @"SELECT VALUE s FROM Stores AS s WHERE s.Id =
ANYELEMENT(SELECT VALUE MAX(o.Store.Id) FROM Orders As o)";
ObjectQuery<Store> q = context.CreateQuery<Store>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.MaxInSubQuery2);
int i = 0;
foreach (Store s in q)
Assert.AreEqual(dt.Rows[i++]["id"], s.Id);
}
}
public void SimpleSelect()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Toys", conn);
DataTable toys = new DataTable();
da.Fill(toys);
int i = 0;
using (testEntities context = new testEntities())
{
var query = context.CreateQuery<Toy>("SELECT VALUE c FROM Toys AS c");
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.SimpleSelect);
foreach (Toy t in query)
{
Assert.AreEqual(toys.Rows[i++]["name"], t.Name);
}
}
}
public void OrderByWithPredicate()
{
using (testEntities context = new testEntities())
{
using (EntityConnection ec = context.Connection as EntityConnection)
{
ec.Open();
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT id FROM Companies c WHERE c.NumEmployees > 100 ORDER BY c.Name", conn);
DataTable dt = new DataTable();
da.Fill(dt);
string eSql = "SELECT VALUE c FROM Companies AS c WHERE c.NumEmployees > 100 ORDER BY c.Name";
ObjectQuery<Company> query = context.CreateQuery<Company>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.OrderByWithPredicate);
int i = 0;
foreach (Company c in query)
Assert.AreEqual(dt.Rows[i++][0], c.Id);
}
}
}
private DataTable GetParametersFromIS(string[] restrictions, DataTable routines)
{
DataTable parms = new DataTable();
if (routines == null || routines.Rows.Count == 0)
{
if (restrictions == null)
{
// first fill our table with the proper structure
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE 1=2", connection);
da.Fill(parms);
}
else
GetParametersForRoutineFromIS(parms, restrictions);
}
else
foreach (DataRow routine in routines.Rows)
{
if (restrictions != null && restrictions.Length >= 3)
restrictions[2] = routine["ROUTINE_NAME"].ToString();
GetParametersForRoutineFromIS(parms, restrictions);
}
parms.TableName = "Procedure Parameters";
return parms;
}
public void TimeType()
{
using (testEntities context = new testEntities())
{
TimeSpan birth = new TimeSpan(11,3,2);
Child c = new Child();
c.Id = 20;
c.EmployeeID = 1;
c.FirstName = "first";
c.LastName = "last";
c.BirthTime = birth;
c.Modified = DateTime.Now;
context.AddToChildren(c);
context.SaveChanges();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM EmployeeChildren WHERE id=20", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(birth, dt.Rows[0]["birthtime"]);
}
}
public void SumWithGrouping()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT SUM(Freight) FROM Orders GROUP BY StoreId", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE SUM(o.Freight) FROM Orders AS o GROUP BY o.Store.Id";
ObjectQuery<Double> q = context.CreateQuery<Double>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.SumWithGrouping);
int i = 0;
foreach (double freight in q)
Assert.AreEqual(Convert.ToInt32(dt.Rows[i++][0]), Convert.ToInt32(freight));
}
}
public virtual DataTable GetUsers(string[] restrictions)
{
StringBuilder sb = new StringBuilder("SELECT Host, User FROM mysql.user");
if (restrictions != null && restrictions.Length > 0)
sb.AppendFormat(CultureInfo.InvariantCulture, " WHERE User LIKE '{0}'", restrictions[0]);
MySqlDataAdapter da = new MySqlDataAdapter(sb.ToString(), connection);
DataTable dt = new DataTable();
da.Fill(dt);
dt.TableName = "Users";
dt.Columns[0].ColumnName = "HOST";
dt.Columns[1].ColumnName = "USERNAME";
return dt;
}
public void OrderBySimple()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT id FROM Companies c ORDER BY c.Name", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE c FROM Companies AS c ORDER BY c.Name";
ObjectQuery<Company> query = context.CreateQuery<Company>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.OrderBySimple);
int i = 0;
foreach (Company c in query)
Assert.AreEqual(dt.Rows[i++][0], c.Id);
}
}
public virtual DataTable GetDatabases(string[] restrictions)
{
Regex regex = null;
int caseSetting = Int32.Parse(connection.driver.Property("lower_case_table_names"));
string sql = "SHOW DATABASES";
// if lower_case_table_names is zero, then case lookup should be sensitive
// so we can use LIKE to do the matching.
if (caseSetting == 0)
{
if (restrictions != null && restrictions.Length >= 1)
sql = sql + " LIKE '" + restrictions[0] + "'";
}
else if (restrictions != null && restrictions.Length >= 1 && restrictions[0] != null)
regex = new Regex(restrictions[0], RegexOptions.IgnoreCase);
MySqlDataAdapter da = new MySqlDataAdapter(sql, connection);
DataTable dt = new DataTable();
da.Fill(dt);
DataTable table = new DataTable("Databases");
table.Columns.Add("CATALOG_NAME", typeof(string));
table.Columns.Add("SCHEMA_NAME", typeof(string));
foreach (DataRow row in dt.Rows)
{
if (caseSetting != 0 && regex != null &&
!regex.Match(row[0].ToString()).Success)
continue;
DataRow newRow = table.NewRow();
newRow[1] = row[0];
table.Rows.Add(newRow);
}
return table;
}
private void GetParametersForRoutineFromIS(DataTable dt, string[] restrictions)
{
Debug.Assert(dt != null);
string[] keys = new string[5];
keys[0] = "SPECIFIC_CATALOG";
keys[1] = "SPECIFIC_SCHEMA";
keys[2] = "SPECIFIC_NAME";
keys[3] = "ROUTINE_TYPE";
keys[4] = "PARAMETER_NAME";
StringBuilder sql = new StringBuilder(@"SELECT * FROM INFORMATION_SCHEMA.PARAMETERS");
// now get our where clause and append it if there is one
string where = GetWhereClause(null, keys, restrictions);
if (!String.IsNullOrEmpty(where))
sql.AppendFormat(CultureInfo.InvariantCulture, " WHERE {0}", where);
MySqlDataAdapter da = new MySqlDataAdapter(sql.ToString(), connection);
da.Fill(dt);
}
public virtual DataTable GetIndexes(string[] restrictions)
{
DataTable dt = new DataTable("Indexes");
dt.Columns.Add("INDEX_CATALOG", typeof(string));
dt.Columns.Add("INDEX_SCHEMA", typeof(string));
dt.Columns.Add("INDEX_NAME", typeof(string));
dt.Columns.Add("TABLE_NAME", typeof(string));
dt.Columns.Add("UNIQUE", typeof(bool));
dt.Columns.Add("PRIMARY", typeof(bool));
dt.Columns.Add("TYPE", typeof(string));
dt.Columns.Add("COMMENT", typeof(string));
// Get the list of tables first
int max = restrictions == null ? 4 : restrictions.Length;
string[] tableRestrictions = new string[Math.Max(max, 4)];
if (restrictions != null)
restrictions.CopyTo(tableRestrictions, 0);
tableRestrictions[3] = "BASE TABLE";
DataTable tables = GetTables(tableRestrictions);
foreach (DataRow table in tables.Rows)
{
string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`",
MySqlHelper.DoubleQuoteString((string)table["TABLE_SCHEMA"]),
MySqlHelper.DoubleQuoteString((string)table["TABLE_NAME"]));
MySqlDataAdapter da = new MySqlDataAdapter(sql, connection);
DataTable indexes = new DataTable();
da.Fill(indexes);
foreach (DataRow index in indexes.Rows)
{
long seq_index = (long)index["SEQ_IN_INDEX"];
if (seq_index != 1) continue;
if (restrictions != null && restrictions.Length == 4 &&
restrictions[3] != null &&
!index["KEY_NAME"].Equals(restrictions[3]))
continue;
DataRow row = dt.NewRow();
row["INDEX_CATALOG"] = null;
row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"];
row["INDEX_NAME"] = index["KEY_NAME"];
row["TABLE_NAME"] = index["TABLE"];
row["UNIQUE"] = (long)index["NON_UNIQUE"] == 0;
row["PRIMARY"] = index["KEY_NAME"].Equals("PRIMARY");
row["TYPE"] = index["INDEX_TYPE"];
row["COMMENT"] = index["COMMENT"];
dt.Rows.Add(row);
}
}
return dt;
}
private DataTable GetTable(string sql)
{
DataTable table = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, connection);
da.Fill(table);
return table;
}
public void SkipAndTakeWithOrdering()
{
using (testEntities context = new testEntities())
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Companies ORDER BY Name DESC LIMIT 2,2", conn);
DataTable dt = new DataTable();
da.Fill(dt);
int i = 0;
var query = context.Companies.OrderByDescending(q => q.Name).Skip(2).Take(2);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.SkipAndTakeWithOrdering);
foreach (Company c in query)
Assert.AreEqual(dt.Rows[i++]["Name"], c.Name);
}
}
