public void UpdateAllRows() { MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM toys", conn); object count = cmd.ExecuteScalar(); using (testEntities context = new testEntities()) { foreach (Toy t in context.Toys) t.Name = "Top"; context.SaveChanges(); } cmd.CommandText = "SELECT COUNT(*) FROM Toys WHERE name='Top'"; object newCount = cmd.ExecuteScalar(); Assert.AreEqual(count, newCount); }
public void FirstPredicate() { MySqlCommand cmd = new MySqlCommand("SELECT id FROM orders WHERE freight > 100", conn); int id = (int)cmd.ExecuteScalar(); using (testEntities context = new testEntities()) { var q = from o in context.Orders where o.Freight > 100 select o; Order order = q.First() as Order; Assert.AreEqual(id, order.Id); } }
public void AverageSimple() { MySqlCommand trueCmd = new MySqlCommand("SELECT AVG(minAge) FROM Toys", conn); object avgAge = trueCmd.ExecuteScalar(); using (testEntities context = new testEntities()) { string eSql = "SELECT VALUE Avg(t.MinAge) FROM Toys AS t"; ObjectQuery<Decimal> q = context.CreateQuery<Decimal>(eSql); string sql = q.ToTraceString(); CheckSql(sql, SQLSyntax.AverageSimple); foreach (Decimal r in q) Assert.AreEqual(avgAge, r); } }
internal string GetCommandTextForBatching() { if (batchableCommandText == null) { // if the command starts with insert and is "simple" enough, then // we can use the multi-value form of insert if (String.Compare(CommandText.Substring(0, 6), "INSERT", true) == 0) { MySqlCommand cmd = new MySqlCommand("SELECT @@sql_mode", Connection); string sql_mode = cmd.ExecuteScalar().ToString().ToUpper(CultureInfo.InvariantCulture); MySqlTokenizer tokenizer = new MySqlTokenizer(CommandText); tokenizer.AnsiQuotes = sql_mode.IndexOf("ANSI_QUOTES") != -1; tokenizer.BackslashEscapes = sql_mode.IndexOf("NO_BACKSLASH_ESCAPES") == -1; string token = tokenizer.NextToken().ToLower(CultureInfo.InvariantCulture); while (token != null) { if (token.ToUpper(CultureInfo.InvariantCulture) == "VALUES" && !tokenizer.Quoted) { token = tokenizer.NextToken(); Debug.Assert(token == "("); // find matching right parameter, and ensure that pares // are balanced. int openParenCount = 1; while (token != null) { batchableCommandText += token; token = tokenizer.NextToken(); if (token == "(") openParenCount++; else if (token == ")") openParenCount--; if (openParenCount == 0) break; } if (token != null) batchableCommandText += token; token = tokenizer.NextToken(); if (token != null && (token == "," || token.ToUpper(CultureInfo.InvariantCulture) == "ON")) { batchableCommandText = null; break; } } token = tokenizer.NextToken(); } } // Otherwise use the command verbatim else batchableCommandText = CommandText; } return batchableCommandText; }
public void SumWithPredicate() { MySqlCommand trueCmd = new MySqlCommand("SELECT SUM(Freight) FROM Orders WHERE storeId=2", conn); object freight = trueCmd.ExecuteScalar(); using (testEntities context = new testEntities()) { string eSql = "SELECT VALUE SUM(o.Freight) FROM Orders AS o WHERE o.Store.Id = 2"; ObjectQuery<Double> q = context.CreateQuery<Double>(eSql); string sql = q.ToTraceString(); CheckSql(sql, SQLSyntax.SumWithPredicate); foreach (Double r in q) Assert.AreEqual(freight, r); } }
public int FetchId(MySqlConnection connection) { if (Id == -1) { MySqlCommand cmd = new MySqlCommand( @"SELECT id FROM my_aspnet_Applications WHERE name=@name", connection); cmd.Parameters.AddWithValue("@name", Name); object id = cmd.ExecuteScalar(); Id = id == null ? -1 : Convert.ToInt32(id); } return Id; }
/// <summary> /// Gets the user name associated with the specified e-mail address. /// </summary> /// <param name="email">The e-mail address to search for.</param> /// <returns> /// The user name associated with the specified e-mail address. If no match is found, return null. /// </returns> public override string GetUserNameByEmail(string email) { try { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); string sql = @"SELECT u.name FROM my_aspnet_Users u JOIN my_aspnet_Membership m ON m.userid=u.id WHERE m.Email = @email AND u.applicationId=@appId"; MySqlCommand cmd = new MySqlCommand(sql, conn); cmd.Parameters.AddWithValue("@email", email); cmd.Parameters.AddWithValue("@appId", app.FetchId(conn)); return (string)cmd.ExecuteScalar(); } } catch (MySqlException e) { if (WriteExceptionsToEventLog) WriteToEventLog(e, "GetUserNameByEmail"); throw new ProviderException(exceptionMessage); } }
/// <summary> /// Determines whether [is user in role] [the specified username]. /// </summary> /// <param name="username">The username.</param> /// <param name="rolename">The rolename.</param> /// <returns> /// <c>true</c> if [is user in role] [the specified username]; otherwise, <c>false</c>. /// </returns> public override bool IsUserInRole(string username, string rolename) { try { // this will refresh the app id if necessary if (!RoleExists(rolename)) return false; using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); string sql = @"SELECT COUNT(*) FROM my_aspnet_UsersInRoles uir JOIN my_aspnet_Users u ON uir.userId=u.id JOIN my_aspnet_Roles r ON uir.roleId=r.id WHERE u.applicationId=@appId AND u.name LIKE @userName AND r.name LIKE @roleName"; MySqlCommand cmd = new MySqlCommand(sql, connection); cmd.Parameters.AddWithValue("@appId", app.FetchId(connection)); cmd.Parameters.AddWithValue("@userName", username); cmd.Parameters.AddWithValue("@roleName", rolename); int count = Convert.ToInt32(cmd.ExecuteScalar()); return count > 0; } } catch (Exception ex) { if (WriteExceptionsToEventLog) WriteToEventLog(ex, "IsUserInRole"); throw; } }
private int GetRoleId(MySqlConnection connection, string rolename) { MySqlCommand cmd = new MySqlCommand( "SELECT id FROM my_aspnet_Roles WHERE name=@name AND applicationId=@appId", connection); cmd.Parameters.AddWithValue("@name", rolename); cmd.Parameters.AddWithValue("@appId", app.FetchId(connection)); return (int)cmd.ExecuteScalar(); }
/// <summary> /// When overridden in a derived class, returns the number of profiles /// in which the last activity date occurred on or before the specified /// date. /// </summary> /// <param name="authenticationOption">One of the /// <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, /// specifying whether anonymous, authenticated, or both types of profiles /// are returned.</param> /// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/> /// that identifies which user profiles are considered inactive. If the /// <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/> of /// a user profile occurs on or before this date and time, the profile /// is considered inactive.</param> /// <returns> /// The number of profiles in which the last activity date occurred on /// or before the specified date. /// </returns> public override int GetNumberOfInactiveProfiles( ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate) { using (MySqlConnection c = new MySqlConnection(connectionString)) { c.Open(); MySqlCommand queryCmd = new MySqlCommand( @"SELECT COUNT(*) FROM my_aspnet_Users WHERE applicationId = @appId AND lastActivityDate < @lastActivityDate", c); queryCmd.Parameters.AddWithValue("@appId", app.FetchId(c)); queryCmd.Parameters.AddWithValue("@lastActivityDate", userInactiveSinceDate); if (authenticationOption == ProfileAuthenticationOption.Anonymous) queryCmd.CommandText += " AND isAnonymous = 1"; else if (authenticationOption == ProfileAuthenticationOption.Authenticated) queryCmd.CommandText += " AND isAnonymous = 0"; return (int)queryCmd.ExecuteScalar(); } }
private static int GetSchemaVersion(string connectionString) { // retrieve the current schema version using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); MySqlCommand cmd = new MySqlCommand("SELECT * FROM my_aspnet_SchemaVersion", conn); try { object ver = cmd.ExecuteScalar(); if (ver != null) return (int)ver; } catch (MySqlException ex) { if (ex.Number != (int)MySqlErrorCode.NoSuchTable) throw; string[] restrictions = new string[4]; restrictions[2] = "mysql_Membership"; DataTable dt = conn.GetSchema("Tables", restrictions); if (dt.Rows.Count == 1) return Convert.ToInt32(dt.Rows[0]["TABLE_COMMENT"]); } return 0; } }
public void SumSimple() { MySqlCommand trueCmd = new MySqlCommand("SELECT SUM(minage) FROM Toys", conn); object sumAge = trueCmd.ExecuteScalar(); using (testEntities context = new testEntities()) { string eSql = "SELECT VALUE Sum(t.MinAge) FROM Toys AS t"; ObjectQuery<Int32> q = context.CreateQuery<Int32>(eSql); string sql = q.ToTraceString(); CheckSql(sql, SQLSyntax.SumSimple); foreach (int r in q) Assert.AreEqual(sumAge, r); } }
public void FirstSimple() { MySqlCommand cmd = new MySqlCommand("SELECT id FROM orders", conn); int id = (int)cmd.ExecuteScalar(); using (testEntities context = new testEntities()) { var q = from o in context.Orders select o; Order order = q.First() as Order; Assert.AreEqual(id, order.Id); } }
public void MinWithPredicate() { MySqlCommand trueCmd = new MySqlCommand("SELECT MIN(Freight) FROM Orders WHERE storeId=2", conn); object freight = trueCmd.ExecuteScalar(); using (testEntities context = new testEntities()) { string eSql = "SELECT Min(o.Freight) FROM Orders AS o WHERE o.Store.Id = 2"; ObjectQuery<DbDataRecord> q = context.CreateQuery<DbDataRecord>(eSql); string sql = q.ToTraceString(); CheckSql(sql, SQLSyntax.MinWithPredicate); foreach (DbDataRecord r in q) { Assert.AreEqual(freight, r.GetDouble(0)); } } }
public void MinSimple() { MySqlCommand trueCmd = new MySqlCommand("SELECT MIN(minage) FROM Toys", conn); int trueMin = (int)trueCmd.ExecuteScalar(); using (testEntities context = new testEntities()) { string eSql = "SELECT VALUE MIN(t.MinAge) FROM Toys AS t"; ObjectQuery<Int32> q = context.CreateQuery<Int32>(eSql); string sql = q.ToTraceString(); CheckSql(sql, SQLSyntax.MinSimple); foreach (int age in q) Assert.AreEqual(trueMin, age); } }
public void CountWithPredicate() { MySqlCommand trueCmd = new MySqlCommand("SELECT COUNT(*) FROM Toys AS t WHERE t.MinAge > 3", conn); object trueCount = trueCmd.ExecuteScalar(); using (testEntities context = new testEntities()) { string eSql = "SELECT VALUE Count(t.Id) FROM Toys AS t WHERE t.MinAge > 3"; ObjectQuery<Int32> q = context.CreateQuery<Int32>(eSql); string sql = q.ToTraceString(); CheckSql(sql, SQLSyntax.CountWithPredicate); foreach (int count in q) Assert.AreEqual(trueCount, count); } }
private string GetSqlMode() { MySqlCommand cmd = new MySqlCommand("SELECT @@SQL_MODE", connection); return cmd.ExecuteScalar().ToString(); }
internal string GetCommandTextForBatching() { if (batchableCommandText == null) { // if the command starts with insert and is "simple" enough, then // we can use the multi-value form of insert if (String.Compare(CommandText.Substring(0, 6), "INSERT", true) == 0) { MySqlCommand cmd = new MySqlCommand("SELECT @@sql_mode", Connection); string sql_mode = cmd.ExecuteScalar().ToString().ToUpper(); MySqlTokenizer tokenizer = new MySqlTokenizer(CommandText); tokenizer.AnsiQuotes = sql_mode.IndexOf("ANSI_QUOTES") != -1; tokenizer.BackslashEscapes = sql_mode.IndexOf("NO_BACKSLASH_ESCAPES") == -1; string token = tokenizer.NextToken().ToLower(); while (token != null) { if (token.ToUpper() == "VALUES" && !tokenizer.Quoted) { token = tokenizer.NextToken(); Debug.Assert(token == "("); // find matching right parameter, and ensure that pares // are balanced. int openParenCount = 1; while (token != null) { batchableCommandText += token; token = tokenizer.NextToken(); if (token == "(") { openParenCount++; } else if (token == ")") { openParenCount--; } if (openParenCount == 0) { break; } } if (token != null) { batchableCommandText += token; } token = tokenizer.NextToken(); if (token != null && (token == "," || token.ToUpper() == "ON")) { batchableCommandText = null; break; } } token = tokenizer.NextToken(); } } // Otherwise use the command verbatim else { batchableCommandText = CommandText; } } return(batchableCommandText); }
/// <summary> /// When overridden in a derived class, deletes profile properties /// and information for profiles that match the supplied list of user names. /// </summary> /// <param name="usernames">A string array of user names for /// profiles to be deleted.</param> /// <returns> /// The number of profiles deleted from the data source. /// </returns> public override int DeleteProfiles(string[] usernames) { using (MySqlConnection c = new MySqlConnection(connectionString)) { c.Open(); MySqlCommand queryCmd = new MySqlCommand( @"SELECT * FROM my_aspnet_Users WHERE applicationId=@appId AND name = @name", c); queryCmd.Parameters.AddWithValue("@appId", app.FetchId(c)); queryCmd.Parameters.Add("@name", MySqlDbType.VarChar); MySqlCommand deleteCmd = new MySqlCommand( "DELETE FROM my_aspnet_Profiles WHERE userId = @userId", c); deleteCmd.Parameters.Add("@userId", MySqlDbType.UInt64); int count = 0; foreach (string name in usernames) { queryCmd.Parameters[1].Value = name; ulong uid = (ulong)queryCmd.ExecuteScalar(); deleteCmd.Parameters[0].Value = uid; count += deleteCmd.ExecuteNonQuery(); } return count; } }
public void AverageWithPredicate() { MySqlCommand trueCmd = new MySqlCommand("SELECT AVG(Freight) FROM Orders WHERE storeId=3", conn); Double freight = (Double)trueCmd.ExecuteScalar(); using (testEntities context = new testEntities()) { string eSql = "SELECT VALUE AVG(o.Freight) FROM Orders AS o WHERE o.Store.Id = 3"; ObjectQuery<Double> q = context.CreateQuery<Double>(eSql); string sql = q.ToTraceString(); CheckSql(sql, SQLSyntax.AverageWithPredicate); foreach (Double r in q) Assert.AreEqual(Convert.ToInt32(freight), Convert.ToInt32(r)); } }
private ProfileInfoCollection GetProfiles( ProfileAuthenticationOption authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords) { List<string> whereClauses = new List<string>(); using (MySqlConnection c = new MySqlConnection(connectionString)) { c.Open(); MySqlCommand cmd = new MySqlCommand( @"SELECT p.*, u.name, u.isAnonymous, u.lastActivityDate, LENGTH(p.stringdata) + LENGTH(p.binarydata) AS profilesize FROM my_aspnet_Profiles p JOIN my_aspnet_Users u ON u.id = p.userId WHERE u.applicationId = @appId", c); cmd.Parameters.AddWithValue("@appId", app.FetchId(c)); if (usernameToMatch != null) { cmd.CommandText += " AND u.name LIKE @userName"; cmd.Parameters.AddWithValue("@userName", usernameToMatch); } if (userInactiveSinceDate != DateTime.MinValue) { cmd.CommandText += " AND u.lastActivityDate < @lastActivityDate"; cmd.Parameters.AddWithValue("@lastActivityDate", userInactiveSinceDate); } if (authenticationOption == ProfileAuthenticationOption.Anonymous) cmd.CommandText += " AND u.isAnonymous = 1"; else if (authenticationOption == ProfileAuthenticationOption.Authenticated) cmd.CommandText += " AND u.isAnonymous = 0"; cmd.CommandText += String.Format(" LIMIT {0},{1}", pageIndex * pageSize, pageSize); ProfileInfoCollection pic = new ProfileInfoCollection(); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { ProfileInfo pi = new ProfileInfo( reader.GetString("name"), reader.GetBoolean("isAnonymous"), reader.GetDateTime("lastActivityDate"), reader.GetDateTime("lastUpdatedDate"), reader.GetInt32("profilesize")); pic.Add(pi); } } cmd.CommandText = "SELECT FOUND_ROWS()"; totalRecords = Convert.ToInt32(cmd.ExecuteScalar()); return pic; } }
internal string CurrentDatabase() { if (Database != null && Database.Length > 0) return Database; MySqlCommand cmd = new MySqlCommand("SELECT database()", this); return cmd.ExecuteScalar().ToString(); }
/// <summary> /// Creates the or fetch user id. /// </summary> /// <param name="connection">The connection.</param> /// <param name="username">The username.</param> /// <param name="applicationId">The application id.</param> /// <param name="authenticated">if set to <c>true</c> [authenticated].</param> /// <returns></returns> internal static int CreateOrFetchUserId(MySqlConnection connection, string username, int applicationId, bool authenticated) { Debug.Assert(applicationId > 0); // first attempt to fetch an existing user id MySqlCommand cmd = new MySqlCommand(@"SELECT id FROM my_aspnet_Users WHERE applicationId = @appId AND name = @name", connection); cmd.Parameters.AddWithValue("@appId", applicationId); cmd.Parameters.AddWithValue("@name", username); object userId = cmd.ExecuteScalar(); if (userId != null) return (int)userId; cmd.CommandText = @"INSERT INTO my_aspnet_Users VALUES (NULL, @appId, @name, @isAnon, Now())"; cmd.Parameters.AddWithValue("@isAnon", !authenticated); int recordsAffected = cmd.ExecuteNonQuery(); if (recordsAffected != 1) throw new ProviderException(Resources.UnableToCreateUser); cmd.CommandText = "SELECT LAST_INSERT_ID()"; return Convert.ToInt32(cmd.ExecuteScalar()); }
private MembershipUserCollection GetUsers(string username, string email, int pageIndex, int pageSize, out int totalRecords) { MembershipUserCollection users = new MembershipUserCollection(); try { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; string sql = @"SELECT SQL_CALC_FOUND_ROWS u.name,m.* FROM my_aspnet_Users u JOIN my_aspnet_Membership m ON m.userId=u.id WHERE u.applicationId=@appId"; if (username != null) { sql += " AND u.name LIKE @name"; cmd.Parameters.AddWithValue("@name", username); } else if (email != null) { sql += " AND m.Email LIKE @email"; cmd.Parameters.AddWithValue("@email", email); } sql += " ORDER BY u.id ASC LIMIT {0},{1}"; cmd.CommandText = String.Format(sql, pageIndex * pageSize, pageSize); cmd.Parameters.AddWithValue("@appId", app.FetchId(connection)); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) users.Add(GetUserFromReader(reader)); } cmd.CommandText = "SELECT FOUND_ROWS()"; cmd.Parameters.Clear(); totalRecords = Convert.ToInt32(cmd.ExecuteScalar()); } return users; } catch (MySqlException e) { if (WriteExceptionsToEventLog) WriteToEventLog(e, "GetUsers"); throw new ProviderException(exceptionMessage); } }
/// <summary> /// Roles the exists. /// </summary> /// <param name="rolename">The rolename.</param> /// <returns>true if the role name already exists in the database; otherwise, false. </returns> public override bool RoleExists(string rolename) { try { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); MySqlCommand cmd = new MySqlCommand( @"SELECT COUNT(*) FROM my_aspnet_Roles WHERE applicationId=@appId AND name LIKE @name", connection); cmd.Parameters.AddWithValue("@appId", app.FetchId(connection)); cmd.Parameters.AddWithValue("@name", rolename); int count = Convert.ToInt32(cmd.ExecuteScalar()); return count != 0; } } catch (Exception ex) { if (WriteExceptionsToEventLog) WriteToEventLog(ex, "RoleExists"); throw; } }
/// <summary> /// Gets the number of users currently accessing the application. /// </summary> /// <returns> /// The number of users currently accessing the application. /// </returns> public override int GetNumberOfUsersOnline() { TimeSpan onlineSpan = new TimeSpan(0, Membership.UserIsOnlineTimeWindow, 0); DateTime compareTime = DateTime.Now.Subtract(onlineSpan); try { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); MySqlCommand cmd = new MySqlCommand( @"SELECT COUNT(*) FROM my_aspnet_Membership m JOIN my_aspnet_Users u ON m.userId=u.id WHERE m.LastActivityDate > @date AND u.applicationId=@appId", connection); cmd.Parameters.AddWithValue("@date", compareTime); cmd.Parameters.AddWithValue("@appId", app.FetchId(connection)); return Convert.ToInt32(cmd.ExecuteScalar()); } } catch (MySqlException e) { if (WriteExceptionsToEventLog) WriteToEventLog(e, "GetNumberOfUsersOnline"); throw new ProviderException(exceptionMessage, e); } }
private int GetUserId(MySqlConnection connection, string username) { MySqlCommand cmd = new MySqlCommand( "SELECT id FROM my_aspnet_Users WHERE name=@name AND applicationId=@appId", connection); cmd.Parameters.AddWithValue("@name", username); cmd.Parameters.AddWithValue("@appId", app.FetchId(connection)); object id = cmd.ExecuteScalar(); return Convert.ToInt32(id); }
/// <summary> /// Execute a single command against a MySQL database. /// </summary> /// <param name="connection"><see cref="MySqlConnection"/> object to use</param> /// <param name="commandText">Command text to use for the command</param> /// <param name="commandParameters">Parameters to use for the command</param> /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns> public static object ExecuteScalar(MySqlConnection connection, string commandText, params MySqlParameter[] commandParameters) { //create a command and prepare it for execution MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = commandText; cmd.CommandType = CommandType.Text; if (commandParameters != null) foreach (MySqlParameter p in commandParameters) cmd.Parameters.Add(p); //execute the command & return the results object retval = cmd.ExecuteScalar(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; }