/// <summary>
/// Executes a single command against a MySQL database. The <see cref="MySqlConnection"/> is assumed to be
/// open when the method is called and remains open after the method completes.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
/// <param name="commandText">SQL command to be executed</param>
/// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command.</param>
/// <returns></returns>
public static int ExecuteNonQuery( 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 );
int result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return result;
}
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!");
}