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