/// <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;
}
/// <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;
}
private void SetParameterValues(MySqlCommand cmd, DataRow dataRow)
{
foreach (MySqlParameter p in cmd.Parameters)
{
if (p.ParameterName.Length >= 8 && p.ParameterName.Substring(0, 8).Equals("Original"))
p.Value = dataRow[ p.SourceColumn, DataRowVersion.Original ];
else
p.Value = dataRow[ p.SourceColumn, DataRowVersion.Current ];
}
}
/// <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 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 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 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.");
}
}
}
}
}
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 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();
}
/// <summary>
/// Creates a clone of this MySqlCommand object. CommandText, Connection, and Transaction properties
/// are included as well as the entire parameter list.
/// </summary>
/// <returns>The cloned MySqlCommand object</returns>
public object Clone()
{
MySqlCommand clone = new MySqlCommand(cmdText, connection, curTransaction);
foreach (MySqlParameter p in parameters)
{
clone.Parameters.Add((p as ICloneable).Clone());
}
return clone;
}
private void SetToInsert(MySqlCommand cmd)
{
cmd.CommandText = "INSERT INTO " + _bb.Table +
this.FIELDS + " " +
this.VALUES + "; " +
"SELECT LAST_INSERT_ID()";
SetParams(cmd);
}
private void SetToUpdate(MySqlCommand cmd)
{
cmd.CommandText = "UPDATE " + _bb.Table + " " + this.SET + " WHERE Id = @ID";
cmd.Parameters.Add("@Id", _bb.Id);
SetParams(cmd);
}
private void SetParams(MySqlCommand cmd)
{
Hashtable paramHash = _bb.ParamHash;
IDictionaryEnumerator paramEnum = paramHash.GetEnumerator();
object val;
while(paramEnum.MoveNext()) {
val = paramEnum.Value;
if (val is String){
val = Replacements(val.ToString());
}
cmd.Parameters.Add((string)paramEnum.Key, val);
}
}
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;
}
/// <summary>
/// Execute a single command against a MySQL database.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
/// <param name="commandText">Command text to use for the command</param>
/// <param name="commandParameters">Parameters to use for the command</param>
/// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
public static object ExecuteScalar(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 );
//execute the command & return the results
object retval = cmd.ExecuteScalar();
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// Initializes a new instance of the MySqlDataAdapter class with the specified MySqlCommand as the SelectCommand property.
/// </summary>
/// <param name="selectCommand"></param>
public MySqlDataAdapter( MySqlCommand selectCommand )
{
SelectCommand = selectCommand;
}
/*
* Because the user should not be able to directly create a
* DataReader object, the constructors are
* marked as internal.
*/
internal MySqlDataReader( MySqlCommand cmd, CommandBehavior behavior)
{
this.command = cmd;
connection = (MySqlConnection)command.Connection;
commandBehavior = behavior;
}
/// <summary>
/// Initializes a new instance of the MySqlDataAdapter class with a SelectCommand and a MySqlConnection object.
/// </summary>
/// <param name="selectCommandText"></param>
/// <param name="conn"></param>
public MySqlDataAdapter( string selectCommandText, MySqlConnection conn)
{
SelectCommand = new MySqlCommand( selectCommandText, conn );
}
public override DataTable ExecuteSQL(string SQLText)
{
try {
MySqlCommand command = new MySqlCommand ();
command.Connection = Connection;
command.CommandText = SQLText;
DataSet resultSet = null;
lock (adapter) {
adapter.SelectCommand = command;
adapter.Fill (resultSet);
}
return resultSet.Tables[0];
} catch {
return null;
}
}
/// <summary>
/// Initializes a new instance of the MySqlDataAdapter class with a SelectCommand and a connection string.
/// </summary>
/// <param name="selectCommandText"></param>
/// <param name="selectConnString"></param>
public MySqlDataAdapter( string selectCommandText, string selectConnString)
{
SelectCommand = new MySqlCommand( selectCommandText,
new MySqlConnection(selectConnString) );
}
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));
}
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();
}
}
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));
}
private MySqlCommand CreateUpdateCommand()
{
if (_updateCmd != null) return _updateCmd;
MySqlCommand cmd = CreateBaseCommand();
StringBuilder setstr = new StringBuilder();
foreach (DataRow schemaRow in _schema.Rows)
{
string colname = Quote((string)schemaRow["ColumnName"]);
if (! IncludedInUpdate(schemaRow)) continue;
if (setstr.Length > 0)
setstr.Append(", ");
MySqlParameter p = CreateParameter(schemaRow, false);
cmd.Parameters.Add(p);
setstr.Append( colname + "=@" + p.ParameterName );
}
cmd.CommandText = "UPDATE " + Quote(_tableName) + " SET " + setstr.ToString() +
" WHERE " + CreateOriginalWhere(cmd);
cmd.CommandText += "; " + CreateFinalSelect(false);
_updateCmd = cmd;
return cmd;
}
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();
}
}
private MySqlCommand CreateInsertCommand()
{
if (_insertCmd != null) return _insertCmd;
MySqlCommand cmd = CreateBaseCommand();
StringBuilder setstr = new StringBuilder();
StringBuilder valstr = new StringBuilder();
foreach (DataRow schemaRow in _schema.Rows)
{
string colname = Quote((string)schemaRow["ColumnName"]);
if (!IncludedInInsert(schemaRow)) continue;
if (setstr.Length > 0)
{
setstr.Append(", ");
valstr.Append(", ");
}
MySqlParameter p = CreateParameter(schemaRow, false);
cmd.Parameters.Add(p);
setstr.Append( colname );
valstr.Append( "@" + p.ParameterName );
}
cmd.CommandText = "INSERT INTO " + Quote(_tableName) + " (" + setstr.ToString() + ") " +
" VALUES (" + valstr.ToString() + ")";
cmd.CommandText += "; " + CreateFinalSelect(true);
_insertCmd = cmd;
return cmd;
}
/// <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 string CreateOriginalWhere(MySqlCommand cmd)
{
StringBuilder wherestr = new StringBuilder();
foreach (DataRow row in _schema.Rows)
{
if (! IncludedInWhereClause(row)) continue;
// first update the where clause since it will contain all parameters
if (wherestr.Length > 0)
wherestr.Append(" AND ");
string colname = Quote((string)row["ColumnName"]);
MySqlParameter op = CreateParameter(row, true);
cmd.Parameters.Add(op);
wherestr.Append( "(" + colname + "=@" + op.ParameterName);
if ((bool)row["AllowDBNull"] == true)
wherestr.Append( " or (" + colname + " IS NULL and @" + op.ParameterName + " IS NULL)");
wherestr.Append(")");
}
return wherestr.ToString();
}
