private void TimeoutExpired(object commandObject)
{
MySqlCommand cmd = (commandObject as MySqlCommand);
if (cmd == null)
{
Logger.LogWarning(Resources.TimeoutExpiredNullObject);
return;
}
cmd.timedOut = true;
try
{
cmd.Cancel();
}
catch (Exception ex)
{
// if something goes wrong, we log it and eat it. There's really nothing
// else we can do.
if (connection.Settings.Logging)
{
Logger.LogException(ex);
}
}
}
private void TimeoutExpired(object commandObject)
{
MySqlCommand cmd = (commandObject as MySqlCommand);
// wait for the query to be sent
querySent.WaitOne();
if (cmd.canCancel)
{
cmd.timedOut = true;
cmd.Cancel();
}
}
public DataTable GetDataTable(string strSQL, List<Parameter> Pars)
{
DataTable dt = new DataTable();
try
{
Open();
MySqlCommand sqlCand = new MySqlCommand(strSQL, SqlConn);
if ((Pars != null) && (Pars.Count > 0))
{
foreach (Parameter par in Pars)
{
MySqlParameter spar = new MySqlParameter();
spar.Value = par.pvalues;
spar.ParameterName = par.pname;
sqlCand.Parameters.Add(spar);
}
}
MySqlDataAdapter sqlda = new MySqlDataAdapter(sqlCand);
sqlCand.ExecuteNonQuery();
sqlda.Fill(dt);
sqlCand.Cancel();
}
catch (Exception ex)
{
// flog_Class.WriteFlog(strSQL + "\r\n" + ex.Message);
}
// Close();
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.ToLower().Trim();
}
}
catch (Exception ex)
{
//string str4 = strSQL + "\r\n" + ex.Message;
//flog_Class.WriteFlog(str4);
}
return dt;
}
public long insertUser(User user)
{
try
{
user.password = _dbUtil.ecryptPsw(user.password);
cmd = _dbUtil.CreateCmd(INSERT_SQL);
cmd.Parameters.AddWithValue("@" + COL_LOGIN, user.login);
cmd.Parameters.AddWithValue("@" + COL_PSW, user.password);
cmd.Parameters.AddWithValue("@" + COL_NAME, user.name);
cmd.Parameters.AddWithValue("@" + COL_SURNAME, user.surname);
cmd.Parameters.AddWithValue("@" + COL_EMAIL, user.email);
cmd.Parameters.AddWithValue("@" + COL_BIRTH, user.birth_date);
cmd.ExecuteNonQuery();
long retId = cmd.LastInsertedId;
return(retId);
}
catch (Exception e) {
cmd.Cancel();
Console.WriteLine(e);
return(-1);
}
}
//public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
public override MembershipUserCollection GetAllUsers(string providerName, string applicationname, int pageIndex, int pageSize)
{
MembershipUserCollection users = new MembershipUserCollection();
using (MySqlConnection conn = new MySqlConnection(GetConnectionString()))
{
using (MySqlCommand cmd = new MySqlCommand(QRY_GET_ALL_USER_BY_APPLICATION, conn))
{
cmd.Parameters.Add("@applicationname", MySqlDbType.VarChar, 100).Value = applicationname;
MySqlDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
int counter = 0;
int startIndex = pageSize * pageIndex;
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
MembershipUser u = GetUserFromReader(providerName, reader);
users.Add(u);
}
if (counter >= endIndex)
cmd.Cancel();
++counter;
}
}
catch (MySqlException ex)
{
Logger.Instance.Write(LogLevel.Error, ex, "GetAllUsers<MySqlException>");
}
finally
{
if (reader != null)
reader.Close();
conn.Close();
}
}
}
return users;
}
public override MembershipUserCollection FindUsersByName(string providerName, string usernameToMatch, string applicationname, int pageIndex, int pageSize, out int totalRecords)
{
MembershipUserCollection users = new MembershipUserCollection();
using (MySqlConnection conn = new MySqlConnection(GetConnectionString()))
{
using (MySqlCommand cmd = new MySqlCommand(QRY_SELECT_USER_COUNT_BY_NAME, conn))
{
cmd.Parameters.Add("@username", MySqlDbType.VarChar, 50).Value = usernameToMatch;
cmd.Parameters.Add("@applicationname", MySqlDbType.VarChar, 100).Value = applicationname;
MySqlDataReader reader = null;
try
{
conn.Open();
totalRecords = (int)cmd.ExecuteScalar();
if (totalRecords <= 0) { return users; }
cmd.CommandText = QRY_GET_USER_BY_NAME_AND_APPLICATION_ORDERED;
reader = cmd.ExecuteReader();
int counter = 0;
int startIndex = pageSize * pageIndex;
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
MembershipUser u = GetUserFromReader(providerName, reader);
users.Add(u);
}
if (counter >= endIndex) { cmd.Cancel(); }
counter++;
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "FindUsersByName");
throw new ProviderException(EXCEPTION_MESSAGE);
}
else
{
throw;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
}
}
return users;
}
private void removeToolStripMenuItem_Click(object sender, EventArgs e)
{
MySqlCommand command = new MySqlCommand(string.Format("DROP USER {0}", UsersList.SelectedItem), conn);
try
{
command.ExecuteNonQuery();
UsersList.Items.Remove(UsersList.SelectedItem);
}
catch
{
if (command != null)
command.Cancel();
}
}
public bool ExecuteSQL(string strSQL, List<Parameter> Pars)
{
int count = -1;
try
{
Open();
MySqlCommand sqlCand = new MySqlCommand(strSQL, SqlConn);
if ((Pars != null) && (Pars.Count > 0))
{
foreach (Parameter par in Pars)
{
MySqlParameter spar = new MySqlParameter();
spar.Value = par.pvalues;
spar.ParameterName = par.pname;
sqlCand.Parameters.Add(spar);
}
}
count = sqlCand.ExecuteNonQuery();
sqlCand.Cancel();
}
catch (Exception ex)
{
//string str4 = strSQL + "\r\n" + ex.Message;
//flog_Class.WriteFlog(str4);
}
Close();
if (count > 0)
return true;
else
return false;
}
public void ExecuteReaderReturnsReaderAfterCancel()
{
execSQL("DROP TABLE IF EXISTS TableWithDateAsPrimaryKey");
execSQL("DROP TABLE IF EXISTS TableWithStringAsPrimaryKey");
createTable("CREATE TABLE TableWithDateAsPrimaryKey(PrimaryKey date NOT NULL, PRIMARY KEY (PrimaryKey))", "InnoDB");
createTable("CREATE TABLE TableWithStringAsPrimaryKey(PrimaryKey nvarchar(50) NOT NULL, PRIMARY KEY (PrimaryKey))", "InnoDB");
string connStr = GetConnectionString(true);
using (MySqlConnection connection = new MySqlConnection(connStr))
{
connection.Open();
MySqlCommand command = new MySqlCommand("SELECT PrimaryKey FROM TableWithDateAsPrimaryKey", connection);
IDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
DataTable dataTableSchema = reader.GetSchemaTable();
command.Cancel();
reader.Close();
command = new MySqlCommand("SELECT PrimaryKey FROM TableWithStringAsPrimaryKey", connection);
reader = command.ExecuteReader(CommandBehavior.KeyInfo);
Assert.IsNotNull(reader);
dataTableSchema = reader.GetSchemaTable();
Assert.AreEqual("PrimaryKey", dataTableSchema.Rows[0][dataTableSchema.Columns[0]]);
reader.Close();
}
}
private void bConnect_Click(object sender, EventArgs e)
{
if (what == "add")
{
if (Password.Text == CPassword.Text)
{
MySqlCommand command = new MySqlCommand(string.Format("CREATE USER '{0}'@'{1}' IDENTIFIED BY '{2}'", User.Text, Host.Text, Password.Text), conn);
try
{
command.ExecuteNonQuery();
}
catch
{
if (command != null)
command.Cancel();
}
parent.UsersList.Items.Add(string.Format("'{0}'@'{1}'", User.Text, Host.Text));
Close();
}
else
MessageBox.Show("Passwords must mutch", "Wrong data", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (what == "change")
{
if (Password.Text != "" && Password.Text == CPassword.Text)
{
MySqlCommand command = new MySqlCommand(string.Format("SET PASSWORD FOR {0} = PASSWORD('{1}')", parent.UsersList.SelectedItem, Password.Text), conn);
try
{
command.ExecuteNonQuery();
}
catch
{
if (command != null)
command.Cancel();
}
Close();
}
else
if (Password.Text != "")
MessageBox.Show("Passwords must mutch", "Wrong data", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (User.Text != "" && Host.Text != "")
{
MySqlCommand command = new MySqlCommand(string.Format("RENAME USER {0} TO '{1}'@'{2}'", parent.UsersList.SelectedItem, User.Text, Host.Text), conn);
try
{
command.ExecuteNonQuery();
parent.UsersList.Items.Remove(parent.UsersList.SelectedItem);
parent.UsersList.Items.Add(string.Format("'{0}'@'{1}'", User.Text, Host.Text));
}
catch
{
if (command != null)
command.Cancel();
}
Close();
}
}
}
/// <summary>
/// Gets a collection of all the users in the data source in pages of data.
/// </summary>
/// <param name="pageIndex">The index of the page of results to return. <paramref name="pageIndex"/> is zero-based.</param>
/// <param name="pageSize">The size of the page of results to return.</param>
/// <param name="totalRecords">The total number of matched users.</param>
/// <returns>
/// A <see cref="T:System.Web.Security.MembershipUserCollection"/> collection that contains a page of <paramref name="pageSize"/><see cref="T:System.Web.Security.MembershipUser"/> objects beginning at the page specified by <paramref name="pageIndex"/>.
/// </returns>
public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
{
MySqlConnection conn = new MySqlConnection(connectionString);
MySqlCommand cmd = new MySqlCommand(@"SELECT Count(*) FROM mysql_Membership
WHERE ApplicationName = ?ApplicationName", conn);
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName;
MembershipUserCollection users = new MembershipUserCollection();
MySqlDataReader reader = null;
totalRecords = 0;
try
{
conn.Open();
totalRecords = Convert.ToInt32(cmd.ExecuteScalar());
if (totalRecords <= 0)
{
return users;
}
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
" FROM mysql_Membership " + " WHERE ApplicationName = ?ApplicationName " +
" ORDER BY Username Asc";
cmd.Parameters.Clear();
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName.ToString();
reader = cmd.ExecuteReader();
int counter = 0;
int startIndex = pageSize * pageIndex;
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
MembershipUser u = GetUserFromReader(reader);
users.Add(u);
}
if (counter >= endIndex)
{
cmd.Cancel();
}
counter += 1;
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetAllUsers");
throw new ProviderException(exceptionMessage);
}
else
{
throw;
}
}
finally
{
if (!(reader == null))
{
reader.Close();
}
conn.Close();
}
return users;
}
/// <summary>
/// Gets a collection of membership users where the user name contains the specified user name to match.
/// </summary>
/// <param name="usernameToMatch">The user name to search for.</param>
/// <param name="pageIndex">The index of the page of results to return. <paramref name="pageIndex"/> is zero-based.</param>
/// <param name="pageSize">The size of the page of results to return.</param>
/// <param name="totalRecords">The total number of matched users.</param>
/// <returns>
/// A <see cref="T:System.Web.Security.MembershipUserCollection"/> collection that contains a page of <paramref name="pageSize"/><see cref="T:System.Web.Security.MembershipUser"/> objects beginning at the page specified by <paramref name="pageIndex"/>.
/// </returns>
public override MembershipUserCollection FindUsersByName(string usernameToMatch,
int pageIndex, int pageSize, out int totalRecords)
{
MembershipUserCollection users = new MembershipUserCollection();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand(@"SELECT Count(*) FROM mysql_Membership
WHERE Username LIKE ?UsernameSearch AND ApplicationName = ?ApplicationName", conn);
cmd.Parameters.AddWithValue("?UsernameSearch", usernameToMatch);
cmd.Parameters.AddWithValue("?ApplicationName", pApplicationName);
try
{
conn.Open();
totalRecords = Convert.ToInt32(cmd.ExecuteScalar());
if (totalRecords <= 0)
return users;
cmd.CommandText =
@"SELECT PKID, Username, Email, PasswordQuestion, Comment,
IsApproved, IsLockedOut, CreationDate, LastLoginDate, LastActivityDate,
LastPasswordChangedDate, LastLockedOutDate FROM mysql_Membership
WHERE Username LIKE ?UsernameSearch AND ApplicationName = ?ApplicationName
ORDER BY Username Asc";
cmd.Parameters["?UsernameSearch"].Value = usernameToMatch;
cmd.Parameters["?ApplicationName"].Value = pApplicationName;
using (MySqlDataReader reader = cmd.ExecuteReader())
{
int counter = 0;
int startIndex = pageSize * pageIndex;
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
MembershipUser u = GetUserFromReader(reader);
users.Add(u);
}
if (counter >= endIndex)
cmd.Cancel();
counter += 1;
}
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "FindUsersByName");
throw new ProviderException(exceptionMessage);
}
throw;
}
}
return users;
}
private void OK_Click(object sender, EventArgs e)
{
if (what == "grant")
{
MySqlCommand command;
if (Option.Checked)
command = new MySqlCommand(string.Format("GRANT {0} ON {1}.{2} TO {3} WITH GRANT OPTION", Privilege.Text, DataBase.Text, Table.Text, parent.UsersList.SelectedItem), conn);
else
command = new MySqlCommand(string.Format("GRANT {0} ON {1}.{2} TO {3}", Privilege.Text, DataBase.Text, Table.Text, parent.UsersList.SelectedItem), conn);
try
{
command.ExecuteNonQuery();
}
catch
{
if (command != null)
command.Cancel();
}
}
if (what == "revoke")
{
MySqlCommand command = new MySqlCommand(string.Format("REVOKE {0} ON {1}.{2} FROM {3}", Privilege.Text, DataBase.Text, Table.Text, parent.UsersList.SelectedItem), conn);
try
{
command.ExecuteNonQuery();
}
catch
{
if (command != null)
command.Cancel();
}
}
Close();
}
public void DoWork()
{
using (MySqlConnection conn = new MySqlConnection(GlobalUtils.TopSecret.MySqlCS))
using (MySqlCommand command = new MySqlCommand())
{
try
{
conn.Open();
//conn.StatisticsEnabled = true;
command.Connection = conn;
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
return;
}
try
{
using (MySqlTransaction sqlTran = conn.BeginTransaction())
{
command.Transaction = sqlTran;
MySqlDataReader reader;
List<string> commands = GetCommands(com);
foreach (string c in commands)
{
command.CommandText = c;
using (reader = command.ExecuteReader())
{
ShowResultSet(reader);
while (reader.NextResult())
ShowResultSet(reader);
}
}
//var stats = conn.RetrieveStatistics();
//using (TextWriter tw = new StreamWriter(path + ".stats"))
//{
// tw.WriteLine("Execution time: {0} sec, rows selected: {1}, rows affected: {2}",
// Math.Round((double)(long)stats["ExecutionTime"] / 1000, 2),
// stats["SelectRows"],
// stats["IduRows"]);
//}
}
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
if (command != null)
command.Cancel();
}
}
}
