public DataTable QueryToTable(string query, string table)
{
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, mysql);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, table);
return dataSet.Tables[0];
}
/// <summary>Retorna todos os tuplos de uma tabela</summary>
internal static DataSet getAll( string table, string query )
{
string connectionString = OrionGlobals.getConnectionString("connectiostring-mysql");
string queryString = "select * from " + table +" " + query;
DataSet dsSections = null;
try {
MySqlDataAdapter sections = new MySqlDataAdapter( queryString, connectionString );
dsSections = new DataSet();
sections.Fill( dsSections );
} catch( Exception e ) {
Chronos.Utils.Log.log("Connection String: {0}", connectionString);
Chronos.Utils.Log.log("Error: " + e.Message);
throw;
}
return dsSections;
}
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 MySqlAdapter (string database) : base (database)
{
dataAdapter = new MySqlDataAdapter ();
}
/// <summary>
/// Updates the given table with data from the given <see cref="DataSet"/>
/// </summary>
/// <param name="connectionString">Settings to use for the update</param>
/// <param name="commandText">Command text to use for the update</param>
/// <param name="ds"><see cref="DataSet"/> containing the new data to use in the update</param>
/// <param name="tablename">Tablename in the dataset to update</param>
public static void UpdateDataSet( string connectionString, string commandText, DataSet ds, string tablename )
{
MySqlConnection cn = new MySqlConnection( connectionString );
cn.Open();
MySqlDataAdapter da = new MySqlDataAdapter( commandText, cn );
MySqlCommandBuilder cb = new MySqlCommandBuilder( da );
da.Update( ds, tablename );
cn.Close();
}
/// <summary>
/// Executes a single SQL command and returns the resultset in a <see cref="DataSet"/>.
/// The state of the <see cref="MySqlConnection"/> object remains unchanged after execution
/// of this method.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
/// <param name="commandText">Command to execute</param>
/// <param name="commandParameters">Parameters to use for the command</param>
/// <returns><see cref="DataSet"/> containing the resultset</returns>
public static DataSet ExecuteDataset(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 );
//create the DataAdapter & DataSet
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
// detach the MySqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
//return the dataset
return ds;
}
private void RunMySql()
{
try
{
MySqlDataAdapter da = new MySqlDataAdapter((MySqlCommand)command);
command.CommandText = sqlText.Text;
command.Connection.Open();
DataTable dt = new DataTable();
da.Fill(dt);
dataGrid.DataSource = dt;
command.Connection.Close();
dataGrid.Expand(-1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Overloaded. Initializes a new instance of the SqlCommandBuilder class.
/// </summary>
public MySqlCommandBuilder( MySqlDataAdapter adapter )
{
_adapter = adapter;
_adapter.RowUpdating += new MySqlRowUpdatingEventHandler( OnRowUpdating );
}