/// <summary>
/// I don't like this setup but can't think of a better way of doing
/// right now.
/// </summary>
/// <param name="connection"></param>
public void SetServerVariables(MySqlConnection connection)
{
if (serverVariablesSet) return;
// retrieve the encoding that should be used for character data
MySqlCommand cmd = new MySqlCommand("show variables like 'max_allowed_packet'", connection);
try
{
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
driver.MaxPacketSize = reader.GetInt64( 1 );
reader.Close();
}
catch (Exception)
{
driver.MaxPacketSize = 1047552;
}
cmd.CommandText = "show variables like 'character_set'";
driver.Encoding = System.Text.Encoding.Default;
try
{
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
driver.Encoding = CharSetMap.GetEncoding( reader.GetString(1) );
reader.Close();
}
catch
{
throw new MySqlException("Failure to initialize connection");
}
serverVariablesSet = true;
}
public object[] QueryToArray(string query)
{
MySqlDataReader rd;
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = mysql;
cmd.CommandText = query;
rd = cmd.ExecuteReader();
if (!rd.Read())
return null;
object[] vals = new object[rd.FieldCount];
rd.GetValues(vals);
rd.Close();
return vals;
}
private void Load(int id, bool loadExternal)
{
using (MySqlConnection conn = (MySqlConnection)this.Conn){
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + _table + " WHERE id = @id", conn);
cmd.Parameters.Add("@id", id);
conn.Open();
using(MySqlDataReader dr = cmd.ExecuteReader()){
dr.Read();
this.Load(dr);
}
}
if (loadExternal){
LoadExternal();
}
}
private void Load(string id, bool loadExternal)
{
_effId = new EffortID(id);
using (MySqlConnection conn = (MySqlConnection)this.Conn){
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + _table + " WHERE extId = @extId AND type = @type ORDER BY 1", conn);
cmd.Parameters.Add("@extID", _effId.NumericPart);
cmd.Parameters.Add("@type", _effId.TypeChar.ToString());
conn.Open();
using(MySqlDataReader dr = cmd.ExecuteReader()){
if (dr.HasRows) {
dr.Read();
this.Load(dr);
SetId(_id); // Load internal id after load since we are using external id for load.
}else{
// No data in private DB so create entry corresponding to external db
this._extId = _effId.NumericPart;
MarkNew();
}
}
}
if (loadExternal){
LoadExternal();
}
}
public SQLData()
{
if (Connection != null)
Console.WriteLine ("Connected to mysql");
if (failed == false) {
// Create database if needded
MySqlCommand command = new MySqlCommand ("show databases;", Connection);
IDataReader reader = command.ExecuteReader();
bool found = false;
while (reader.Read())
if (reader.GetString(0) == "gtk_binding")
found = true;
reader.Close();
if (found == false) {
command = new MySqlCommand ("create database gtk_binding", Connection);
command.ExecuteNonQuery();
}
Database = "gtk_binding";
Connection.Close();
connection = null;
if (Connection != null)
Console.WriteLine ("Using database " + Connection.Database);
// Create table if needded
command = new MySqlCommand ("show tables;", Connection);
reader = command.ExecuteReader();
found = false;
while (reader.Read()) {
if (reader.GetString(0) == "binding_test")
found = true;
Console.WriteLine ("Table: " + reader.GetString(0));
}
reader.Close();
if (found == false) {
Console.WriteLine ("Creating Table binding_test");
command = new MySqlCommand ("CREATE TABLE `gtk_binding`.`binding_test` (" +
"`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY," +
"`string_data` TEXT CHARACTER SET utf8 NOT NULL COMMENT 'String Data'," +
"`integer_data` INT NOT NULL DEFAULT 12 COMMENT 'Integer Value'," +
"`float_data` DOUBLE NOT NULL DEFAULT 13" +
")" +
"ENGINE = InnoDB " +
"CHARACTER SET utf8 " +
"COMMENT = 'Table with simple test data';" +
"insert into binding_test (string_data, integer_data, float_data) values ('othervalue1', 5, 4);" +
"insert into binding_test (string_data, integer_data, float_data) values ('othervalue2', 6, 3);" +
"insert into binding_test (string_data, integer_data, float_data) values ('othervalue3', 7, 44);" +
"insert into binding_test (string_data, integer_data, float_data) values ('othervalue4', 8, 45);" +
"insert into binding_test (string_data, integer_data, float_data) values ('somevalue', 3, 7);", Connection);
command.ExecuteNonQuery();
}
Console.WriteLine ("Playground for this demo is set up ;)");
// Connection.
Console.WriteLine ("Opening table");
///get the dataadapter values
MySqlDataAdapter dataAdapter = new MySqlDataAdapter("select * from binding_test", Connection);
///Initialize, insert, update and delete commands for the database
// InitializeCommands();
///fillup the dataset now
DataSet dataSet = new DataSet();
// demoTable = new DataTable();
///fillup the data adapter now
dataAdapter.Fill(dataSet,"binding_test");
demoTable = dataSet.Tables["binding_test"];
Console.WriteLine ("Read {0} rows successfully", demoTable.Rows.Count);
}
else
Console.WriteLine ("Not connected & exiting!");
}
public override UserSchema[] GetUsers()
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
using (MySqlCommand command = new MySqlCommand ()) {
command.Connection = connection;
command.CommandText =
"SELECT DISTINCT user from mysql.user where user != '';";
MySqlDataReader r = command.ExecuteReader ();
while (r.Read ()) {
UserSchema user = new UserSchema ();
user.Provider = this;
user.Name = r.GetString (0);
collection.Add (user);
}
r.Close ();
}
return (UserSchema[]) collection.ToArray (typeof (UserSchema));
}
public override TableSchema[] GetTables()
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
using (MySqlCommand command = new MySqlCommand ()) {
command.Connection = Connection;
command.CommandText =
"SHOW TABLES;";
MySqlDataReader r = command.ExecuteReader ();
while (r.Read ()) {
TableSchema table = new TableSchema ();
table.Provider = this;
table.Name = r.GetString (0);
collection.Add (table);
}
r.Close ();
}
return (TableSchema[]) collection.ToArray (typeof (TableSchema));
}
public override ConstraintSchema[] GetTableConstraints(TableSchema table)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
using (MySqlCommand command = new MySqlCommand ()) {
command.Connection = Connection;
command.CommandText =
"";
MySqlDataReader r = command.ExecuteReader ();
while (r.Read ()) {
ConstraintSchema constraint = new ConstraintSchema ();
constraint.Provider = this;
// TODO: Implement
collection.Add (constraint);
}
r.Close ();
}
return (ConstraintSchema[]) collection.ToArray (
typeof (ConstraintSchema));
}
public override ColumnSchema[] GetTableColumns(TableSchema table)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
using (MySqlCommand command = new MySqlCommand ()) {
command.Connection = Connection;
// XXX: Use String.Format cause mysql parameters suck assmar.
command.CommandText =
String.Format ("DESCRIBE {0}", table.Name);
MySqlDataReader r = command.ExecuteReader ();
while (r.Read ()) {
ColumnSchema column = new ColumnSchema ();
column.Provider = this;
column.Name = r.GetString (0);
column.DataTypeName = r.GetString (1);
column.NotNull = r.IsDBNull (2);
column.Default = r.GetString (4);
column.Options["extra"] = r.GetString (5);
collection.Add (column);
}
r.Close ();
}
return (ColumnSchema[]) collection.ToArray (typeof (ColumnSchema));
}
public void Load(int id)
{
SetId(id);
using(MySqlConnection conn = (MySqlConnection) this.Conn){
conn.Open();
using(MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + this.Table + " WHERE ID = @ID", conn)){
cmd.Parameters.Add("@Id", this.Id);
using (MySqlDataReader dr = cmd.ExecuteReader()){
if (dr.HasRows) {
dr.Read();
this.Load(dr);
}else{
throw new Exception("Groups " + id + " doesn't exist.");
}
}
}
}
}
/// <summary>
/// Executes a single command against a MySQL database, possibly inside an existing transaction.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use for the command</param>
/// <param name="transaction"><see cref="MySqlTransaction"/> object to use for the command</param>
/// <param name="commandText">Command text to use</param>
/// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command</param>
/// <param name="ExternalConn">True if the connection should be preserved, false if not</param>
/// <returns><see cref="MySqlDataReader"/> object ready to read the results of the command</returns>
private static MySqlDataReader ExecuteReader(MySqlConnection connection, MySqlTransaction transaction, string commandText, MySqlParameter[] commandParameters, bool ExternalConn )
{
//create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.Transaction = transaction;
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
if (commandParameters != null)
foreach (MySqlParameter p in commandParameters)
cmd.Parameters.Add( p );
//create a reader
MySqlDataReader dr;
// call ExecuteReader with the appropriate CommandBehavior
if (ExternalConn)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return dr;
}
public static MySqlDataReader Load(MySqlConnection conn, string table, string pk, int value)
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + table + " WHERE " + pk + " = @PK", conn);
cmd.Parameters.Add("@PK", value);
return cmd.ExecuteReader();
}
public static MySqlDataReader LoadWhereColumnIs(MySqlConnection conn, string table, string key, string value)
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + table + " WHERE " + key + " = @Id " +
"ORDER BY 1", conn);
cmd.Parameters.Add("@Id", value);
MySqlDataReader dr = cmd.ExecuteReader();
return dr;
}
/*public MySqlDataReader Load() {
MySqlConnection conn = (MySqlConnection) _bb.Conn;
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + _bb.Table + " WHERE ID = @ID", conn);
cmd.Parameters.Add("@Id", _bb.Id);
return cmd.ExecuteReader();
}*/
public static MySqlDataReader LoadAll(MySqlConnection conn, string table)
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + table, conn);
MySqlDataReader dr = cmd.ExecuteReader();
return dr;
}
