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