public static DataSet ExecuteDataset(MySqlConnection connection, string commandText, params MySqlParameter[] commandParameters) {
MySqlCommand selectCommand = new MySqlCommand();
selectCommand.Connection = connection;
selectCommand.CommandText = commandText;
selectCommand.CommandType = CommandType.Text;
if (commandParameters != null) {
foreach (MySqlParameter parameter in commandParameters) {
selectCommand.Parameters.Add(parameter);
}
}
MySqlDataAdapter adapter = new MySqlDataAdapter(selectCommand);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
selectCommand.Parameters.Clear();
return dataSet;
}
public static void UpdateDataSet(string connectionString, string commandText, DataSet ds, string tablename) {
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(commandText, connection);
new MySqlCommandBuilder(adapter).ToString();
adapter.Update(ds, tablename);
connection.Close();
}
/// <summary>
/// Executes a Query and returns all Results in a <see cref="DataTable"/>
/// </summary>
/// <param name="query"></param>
/// <param name="arg"></param>
/// <returns>
/// DataTable withh all Results
/// <para>NOTE: the DataTable will be empty if an Error occours</para>
/// </returns>
public virtual DataTable Query(string query, params object[] arg) {
query = string.Format(query, arg);
var result = new DataTable();
LastError = null;
if (query.Length == 0) {
return result;
}
Open();
Adapter = new MySqlDataAdapter {
SelectCommand = new MySqlCommand(query, Connection)
};
try {
Adapter.Fill(result);
} catch (Exception ex) {
System.Diagnostics.Debug.WriteLine(ex);
LastError = ex;
return new DataTable();
}
return result;
}
public virtual DataTable GetUsers(string[] restrictions) {
StringBuilder builder = new StringBuilder("SELECT Host, User FROM mysql.user");
if ((restrictions != null) && (restrictions.Length > 0)) {
builder.AppendFormat(CultureInfo.InvariantCulture, " WHERE User LIKE '{0}'", new object[] { restrictions[0] });
}
MySqlDataAdapter adapter = new MySqlDataAdapter(builder.ToString(), this.connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
dataTable.TableName = "Users";
dataTable.Columns[0].ColumnName = "HOST";
dataTable.Columns[1].ColumnName = "USERNAME";
return dataTable;
}
public virtual DataTable GetIndexes(string[] restrictions) {
DataTable table = new DataTable("Indexes");
table.Columns.Add("INDEX_CATALOG", typeof(string));
table.Columns.Add("INDEX_SCHEMA", typeof(string));
table.Columns.Add("INDEX_NAME", typeof(string));
table.Columns.Add("TABLE_NAME", typeof(string));
table.Columns.Add("UNIQUE", typeof(bool));
table.Columns.Add("PRIMARY", typeof(bool));
foreach (DataRow row in this.GetTables(restrictions).Rows) {
MySqlDataAdapter adapter = new MySqlDataAdapter(string.Format("SHOW INDEX FROM `{0}`.`{1}`", row["TABLE_SCHEMA"], row["TABLE_NAME"]), this.connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
foreach (DataRow row2 in dataTable.Rows) {
long num = (long)row2["SEQ_IN_INDEX"];
if ((num == 1L) && (((restrictions == null) || (restrictions.Length != 4)) || ((restrictions[3] == null) || row2["KEY_NAME"].Equals(restrictions[3])))) {
DataRow row3 = table.NewRow();
row3["INDEX_CATALOG"] = null;
row3["INDEX_SCHEMA"] = row["TABLE_SCHEMA"];
row3["INDEX_NAME"] = row2["KEY_NAME"];
row3["TABLE_NAME"] = row2["TABLE"];
row3["UNIQUE"] = ((long)row2["NON_UNIQUE"]) == 0L;
row3["PRIMARY"] = row2["KEY_NAME"].Equals("PRIMARY");
table.Rows.Add(row3);
}
}
}
return table;
}
public virtual DataTable GetDatabases(string[] restrictions) {
Regex regex = null;
int num = int.Parse(this.connection.driver.Property("lower_case_table_names"));
string selectCommandText = "SHOW DATABASES";
if (num == 0) {
if ((restrictions != null) && (restrictions.Length >= 1)) {
selectCommandText = selectCommandText + " LIKE '" + restrictions[0] + "'";
}
} else if (((restrictions != null) && (restrictions.Length >= 1)) && (restrictions[0] != null)) {
regex = new Regex(restrictions[0], RegexOptions.IgnoreCase);
}
MySqlDataAdapter adapter = new MySqlDataAdapter(selectCommandText, this.connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
DataTable table2 = new DataTable("Databases");
table2.Columns.Add("CATALOG_NAME", typeof(string));
table2.Columns.Add("SCHEMA_NAME", typeof(string));
foreach (DataRow row in dataTable.Rows) {
if (((num == 0) || (regex == null)) || regex.Match(row[0].ToString()).Success) {
DataRow row2 = table2.NewRow();
row2[1] = row[0];
table2.Rows.Add(row2);
}
}
return table2;
}
public MySqlCommandBuilder(MySqlDataAdapter adapter)
: this() {
this.DataAdapter = adapter;
}