public static void ReleaseConnection(MySqlConnectionString connstr, Connection conn)
{
lock (s_queueLock)
{
s_connPoolAgent.ReleaseConnection(connstr, conn);
}
}
public static Connection GetConnection(MySqlConnectionString connstr)
{
lock (s_queueLock)
{
return s_connPoolAgent.GetConnection(connstr);
}
}
public static void ReleaseConnection(MySqlConnectionString connstr, Connection conn)
{
lock (s_queueLock)
{
s_connPoolAgent.ReleaseConnection(connstr, conn);
}
}
public static Connection GetConnection(MySqlConnectionString connstr)
{
lock (s_queueLock)
{
return(s_connPoolAgent.GetConnection(connstr));
}
}
private void Load_Appoinement(object sender, Kidshistory e)
{
string id = (string)e.notification;
MySqlConnectionString connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.UseConnectionPool = true;
conn.Open();
string result = "";
try
{
var cmd = new MySqlCommand("SELECT detail, appointment FROM notification " +
"WHERE username_id=@id", conn);
cmd.Parameters.AddWithValue("@id", id);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result += reader.GetString("appointment");
result += "|" + reader.GetString("detail");
e.notification = result;
}
if (result == "")
{
e.notification = "No data";
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
conn.Close();
}
private void Health_Load_age_gender(object sender, Kidshistory e) //age gender
{
string query = (string)e.health;
MySqlConnectionString connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.UseConnectionPool = true;
conn.Open();
string result = "";
try
{
var cmd = new MySqlCommand(query, conn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result = reader.GetString("gender");
result = result + "|" + reader.GetString("age");
e.health = result;
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
conn.Close();
}
private void Load_vaccine(object sender, Kidshistory e)
{
//throw new NotImplementedException();
string id = (string)e.vaccine;
MySqlConnectionString connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.UseConnectionPool = true;
conn.Open();
string result = "";
try
{
var cmd = new MySqlCommand("SELECT injected_vaccine FROM vaccine WHERE username_id=@id", conn);
cmd.Parameters.AddWithValue("@id", id);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result = reader.GetString("injected_vaccine");
e.vaccine = result;
Console.WriteLine("List: {0} by id:{1}", result, id);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
if (result == "")
{
e.vaccine = "Fail";
}
conn.Close();
}
public void generate_connectionString_with_basic_values()
{
MySqlConnectionString mySqlConnectionString = new MySqlConnectionString();
string connectionString = mySqlConnectionString.GetConnectionString();
Assert.AreEqual("Server=;Database=;", connectionString);
}
private void Login(object sender, Kidshistory e)
{
bool status = false;
string myname = "";
string mypassword;
string user_id = "";
string word = (string)e.users;
string[] words = word.Split('|');
string username = words[0].ToString();
string password = words[1].ToString();
MySqlConnectionString connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.UseConnectionPool = true;
conn.Open();
try
{
var cmd = new MySqlCommand("SELECT username,password,username_id FROM users WHERE " +
"username='******' AND password='******'", conn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
myname = reader.GetString("username");
mypassword = reader.GetString("password");
if (myname == username && mypassword == password)
{
myname = reader.GetString("username");
mypassword = reader.GetString("password");
user_id = reader.GetString("username_id");
Console.WriteLine("Username: "******"\n" + "Password: "******"Success" + "|" + user_id; //response data
var cmd2 = new MySqlCommand("UPDATE users SET State=@S WHERE username=@user", conn);
cmd2.Parameters.AddWithValue("@S", 1);
cmd2.Parameters.AddWithValue("@user", myname);
cmd2.ExecuteNonQuery();
}
else
{
e.users = "Fail" + "|" + 0;
Console.WriteLine("Login failed");
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
conn.Close();
}
public MySqlConnectionStringBuilder(string server, string database)
{
mySqlConnectionString = new MySqlConnectionString()
{
Server = server,
Database = database,
IntegratedSecurity = true
};
}
public MySqlConnectionStringBuilder(string server, string database, string user, string password)
{
mySqlConnectionString = new MySqlConnectionString()
{
Server = server,
Database = database,
User = user,
Password = password
};
}
public static void T_FloatingRange()
{
MySqlConnectionString connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.Open();
{
string sql = "drop table if exists test002";
var cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
{
//num3 decimal(65,30) is max range is possible
//num3 decimal if not define range defualt is decimal(10,0)
string sql = "create table test002(col_id int(10) unsigned not null auto_increment," +
"num1 float, num2 double, num3 decimal(65,30), primary key(col_id) )";
var cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
{
string sql = "insert into test002 (num1, num2, num3) values (?num1, ?num2, ?num3)";
var cmd = new MySqlCommand(sql, conn);
cmd.Prepare();
var pars = cmd.Parameters;
pars.AddWithValue("?num1", 10.15);
pars.AddWithValue("?num2", -101.5);
pars.AddWithValue("?num3", 1015.00);
cmd.ExecuteNonQuery();
pars.ClearDataValues();
pars.AddWithValue("?num1", 10.15d);
pars.AddWithValue("?num2", -101.5f);
pars.AddWithValue("?num3", (decimal)1015.00);
cmd.ExecuteNonQuery();
pars.ClearDataValues();
pars.AddWithValue("?num1", float.MaxValue);
pars.AddWithValue("?num2", double.MaxValue);
//decimal of C# have the number of digits to the right of the decimal point less or equal 5 digits
pars.AddWithValue("?num3", decimal.MaxValue);
cmd.ExecuteNonQuery();
pars.ClearDataValues();
pars.AddWithValue("?num1", float.MinValue);
pars.AddWithValue("?num2", double.MinValue);
pars.AddWithValue("?num3", decimal.MinValue);
cmd.ExecuteNonQuery();
}
conn.Close();
Report.WriteLine("ok");
}
public void ReleaseConnection(MySqlConnectionString connstr, Connection conn)
{
Queue<Connection> found;
//not found
if (!s_connQueue.TryGetValue(connstr.ConnSignature, out found))
{
found = new Queue<Connection>();
s_connQueue.Add(connstr.ConnSignature, found);
}
conn.IsStoredInConnPool = true;
found.Enqueue(conn);
}
public void generate_connectionString_with_Server_Database_and_Encription()
{
MySqlConnectionString mySqlConnectionString = new MySqlConnectionString()
{
Server = "server",
Database = "database",
Encrypt = true
};
string connectionString = mySqlConnectionString.GetConnectionString();
Assert.AreEqual("Server=server;Database=database;Encrypt=true;", connectionString);
}
public void generate_connectionString_with_Server_Database_and_Port()
{
MySqlConnectionString mySqlConnectionString = new MySqlConnectionString()
{
Server = "server",
Database = "database",
Port = 8080
};
string connectionString = mySqlConnectionString.GetConnectionString();
Assert.AreEqual("Server=server;Database=database;Port=8080;", connectionString);
}
public void generate_connectionString_with_Server_Database_and_IntegratedSecurity()
{
MySqlConnectionString mySqlConnectionString = new MySqlConnectionString
{
Server = "server",
Database = "database",
IntegratedSecurity = true
};
string connectionString = mySqlConnectionString.GetConnectionString();
Assert.AreEqual("Server=server;Database=database;IntegratedSecurity=yes;", connectionString);
}
public void generate_connectionString_with_Server_Database_and_DefaultCommandTimeout()
{
MySqlConnectionString mySqlConnectionString = new MySqlConnectionString()
{
Server = "server",
Database = "database",
DefaultCommandTimeout = 20
};
string connectionString = mySqlConnectionString.GetConnectionString();
Assert.AreEqual("Server=server;Database=database;default command timeout=20;", connectionString);
}
public void generate_connectionString_with_Server_Database_and_AllowZeroDateTime()
{
MySqlConnectionString mySqlConnectionString = new MySqlConnectionString()
{
Server = "server",
Database = "database",
AllowZeroDateTime = true
};
string connectionString = mySqlConnectionString.GetConnectionString();
Assert.AreEqual("Server=server;Database=database;AllowZeroDateTime=True;", connectionString);
}
public void generate_connectionString_with_Server_Database_and_AllowBatch()
{
MySqlConnectionString mySqlConnectionString = new MySqlConnectionString()
{
Server = "server",
Database = "database",
AllowBatch = false
};
string connectionString = mySqlConnectionString.GetConnectionString();
Assert.AreEqual("Server=server;Database=database;AllowBatch=False;", connectionString);
}
public void generate_connectionString_with_Server_Database_and_CertificatePassword()
{
MySqlConnectionString mySqlConnectionString = new MySqlConnectionString()
{
Server = "server",
Database = "database",
CertificatePassword = "******"
};
string connectionString = mySqlConnectionString.GetConnectionString();
Assert.AreEqual("Server=server;Database=database;CertificatePassword=certificate;", connectionString);
}
public void generate_connectionString_with_Server_Database_and_SSLMode_Required()
{
MySqlConnectionString mySqlConnectionString = new MySqlConnectionString()
{
Server = "server",
Database = "database",
SSLMode = MySqlConnectionString.SSLModes.Required
};
string connectionString = mySqlConnectionString.GetConnectionString();
Assert.AreEqual("Server=server;Database=database;SslMode=Required;", connectionString);
}
public void generate_connectionString_with_Server_Database_and_ConnectionTimeout()
{
MySqlConnectionString mySqlConnectionString = new MySqlConnectionString()
{
Server = "server",
Database = "database",
ConnectionTimeout = 5
};
string connectionString = mySqlConnectionString.GetConnectionString();
Assert.AreEqual("Server=server;Database=database;Connection Timeout=5;", connectionString);
}
public void ReleaseConnection(MySqlConnectionString connstr, Connection conn)
{
Queue <Connection> found;
//not found
if (!s_connQueue.TryGetValue(connstr.ConnSignature, out found))
{
found = new Queue <Connection>();
s_connQueue.Add(connstr.ConnSignature, found);
}
conn.IsStoredInConnPool = true;
found.Enqueue(conn);
}
public void create_basic_connectionString_with_IntegratedSecurity()
{
MySqlConnectionString mySqlConnectionStringExpected = new MySqlConnectionString()
{
Server = "server",
Database = "database",
IntegratedSecurity = true
};
MySqlConnectionString mySqlConnectionStringBuilder = new MySqlConnectionStringBuilder("server", "database");
Assert.AreEqual(mySqlConnectionStringExpected.GetConnectionString(), mySqlConnectionStringBuilder.GetConnectionString());
}
public void not_add_User_and_no_removes_IntegratedSecurity_to_ConnectionString_when_User_is_Empty()
{
MySqlConnectionString mySqlConnectionStringExpected = new MySqlConnectionString()
{
Server = "server",
Database = "database",
IntegratedSecurity = true
};
MySqlConnectionString mySqlConnectionStringBuilder =
new MySqlConnectionStringBuilder("server", "database").WithUser(string.Empty);
Assert.AreEqual(mySqlConnectionStringExpected.GetConnectionString(), mySqlConnectionStringBuilder.GetConnectionString());
}
public void not_add_Password_and_no_removes_IntegratedSecurity_to_ConnectionString_when_Password_is_Null()
{
MySqlConnectionString mySqlConnectionStringExpected = new MySqlConnectionString()
{
Server = "server",
Database = "database",
IntegratedSecurity = true
};
MySqlConnectionString mySqlConnectionStringBuilder =
new MySqlConnectionStringBuilder("server", "database").WithPassword(null);
Assert.AreEqual(mySqlConnectionStringExpected.GetConnectionString(), mySqlConnectionStringBuilder.GetConnectionString());
}
public void generate_connectionString_with_Server_Database_user_and_password()
{
MySqlConnectionString mySqlConnectionString = new MySqlConnectionString
{
Server = "server",
Database = "database",
User = "******",
Password = "******"
};
string connectionString = mySqlConnectionString.GetConnectionString();
Assert.AreEqual("Server=server;Database=database;Uid=user;Pwd=password;", connectionString);
}
public void create_basic_connectionString_with_User_and_Password()
{
MySqlConnectionString mySqlConnectionStringExpected = new MySqlConnectionString()
{
Server = "server",
Database = "database",
User = "******",
Password = "******"
};
MySqlConnectionString mySqlConnectionStringBuilder = new MySqlConnectionStringBuilder("server", "database", "user", "password");
Assert.AreEqual(mySqlConnectionStringExpected.GetConnectionString(), mySqlConnectionStringBuilder.GetConnectionString());
}
public void remove_User_and_Password_when_sets_true_IntegratedSecurity()
{
MySqlConnectionString mySqlConnectionStringExpected = new MySqlConnectionString()
{
Server = "server",
Database = "database",
IntegratedSecurity = true
};
MySqlConnectionString mySqlConnectionStringBuilder =
new MySqlConnectionStringBuilder("server", "database", "user", "password").WithIntegratedSecurity(true);
Assert.AreEqual(mySqlConnectionStringExpected.GetConnectionString(), mySqlConnectionStringBuilder.GetConnectionString());
}
public void add_User_and_removes_IntegratedSecurity_to_ConnectionString_when_User_contains_text()
{
MySqlConnectionString mySqlConnectionStringExpected = new MySqlConnectionString()
{
Server = "server",
Database = "database",
User = "******"
};
MySqlConnectionString mySqlConnectionStringBuilder =
new MySqlConnectionStringBuilder("server", "database").WithUser("user");
Assert.AreEqual(mySqlConnectionStringExpected.GetConnectionString(), mySqlConnectionStringBuilder.GetConnectionString());
}
public void add_CertificatePassword_to_ConnectionString()
{
MySqlConnectionString mySqlConnectionStringExpected = new MySqlConnectionString()
{
Server = "server",
Database = "database",
IntegratedSecurity = true,
CertificatePassword = "******"
};
MySqlConnectionString mySqlConnectionStringBuilder =
new MySqlConnectionStringBuilder("server", "database").WithCertificatePassword("certificate password");
Assert.AreEqual(mySqlConnectionStringExpected.GetConnectionString(), mySqlConnectionStringBuilder.GetConnectionString());
}
public void add_AllowZeroDateTime_to_ConnectionString()
{
MySqlConnectionString mySqlConnectionStringExpected = new MySqlConnectionString()
{
Server = "server",
Database = "database",
IntegratedSecurity = true,
AllowZeroDateTime = true
};
MySqlConnectionString mySqlConnectionStringBuilder =
new MySqlConnectionStringBuilder("server", "database").WithAllowZeroDateTime(true);
Assert.AreEqual(mySqlConnectionStringExpected.GetConnectionString(), mySqlConnectionStringBuilder.GetConnectionString());
}
public void add_ConnectionTimeout_to_ConnectionString()
{
MySqlConnectionString mySqlConnectionStringExpected = new MySqlConnectionString()
{
Server = "server",
Database = "database",
IntegratedSecurity = true,
ConnectionTimeout = 222
};
MySqlConnectionString mySqlConnectionStringBuilder =
new MySqlConnectionStringBuilder("server", "database").WithConnectionTimeout(222);
Assert.AreEqual(mySqlConnectionStringExpected.GetConnectionString(), mySqlConnectionStringBuilder.GetConnectionString());
}
public Connection GetConnection(MySqlConnectionString connstr)
{
Queue<Connection> found;
//not found
if (!s_connQueue.TryGetValue(connstr.ConnSignature, out found))
{
return null;
}
if (found.Count > 0)
{
var conn = found.Dequeue();
//TODO: check if conn is valid
conn.IsStoredInConnPool = false;
return conn.State == ConnectionState.Connected ? conn : null;
}
else
{
return null;
}
}
