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 MySqlCommandBuilder( MySqlDataAdapter adapter )
: this() {
this.DataAdapter = adapter;
}
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 the first int32 Value
/// </summary>
/// <param name="Query"></param>
public int QueryCount( string Query ) {
DataTable result = new DataTable();
mLastError = null;
if( Query.Length == 0 )
return 0;
Open();
mAdapter = new MySqlDataAdapter();
mAdapter.SelectCommand = new MySqlCommand( Query, mConnection );
try {
mAdapter.Fill( result );
} catch( Exception ex ) {
System.Diagnostics.Debug.WriteLine( ex );
mLastError = ex;
return 0;
}
ResultTable table = new ResultTable( result );
if( table.Rows.Count == 0 )
return 0;
return table.Rows[ 0 ][ 0 ].GetInt32();
}
/// <summary>
/// Executes a Query and returns all Results in a <see cref="DataTable"/>
/// </summary>
/// <param name="Query"></param>
/// <returns>
/// DataTable withh all Results
/// <para>NOTE: the DataTable will be null if an Error occours</para>
/// </returns>
public ResultTable Query( string Query ) {
DataTable result = new DataTable();
mLastError = null;
if( Query.Length == 0 )
return new ResultTable();
Open();
mAdapter = new MySqlDataAdapter();
mAdapter.SelectCommand = new MySqlCommand( Query, mConnection );
try {
mAdapter.Fill( result );
} catch( Exception ex ) {
System.Diagnostics.Debug.WriteLine( ex );
mLastError = ex;
return new ResultTable();
}
return new ResultTable( 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;
}
