public virtual DataTable GetUsers(string[] restrictions)
{
StringBuilder sb = new StringBuilder("SELECT Host, User FROM mysql.user");
if (restrictions != null && restrictions.Length > 0)
sb.AppendFormat(CultureInfo.InvariantCulture, " WHERE User LIKE '{0}'", restrictions[0]);
MySqlDataAdapter da = new MySqlDataAdapter(sb.ToString(), connection);
DataTable dt = new DataTable();
da.Fill(dt);
dt.TableName = "Users";
dt.Columns[0].ColumnName = "HOST";
dt.Columns[1].ColumnName = "USERNAME";
return dt;
}
public virtual DataTable GetDatabases(string[] restrictions)
{
Regex regex = null;
int caseSetting = 1;
string sql = "SHOW DATABASES";
// if lower_case_table_names is zero, then case lookup should be sensitive
// so we can use LIKE to do the matching.
if (caseSetting == 0)
{
if (restrictions != null && restrictions.Length >= 1)
sql = sql + " LIKE '" + restrictions[0] + "'";
}
else if (restrictions != null && restrictions.Length >= 1 && restrictions[0] != null)
regex = new Regex(restrictions[0], RegexOptions.IgnoreCase);
MySqlDataAdapter da = new MySqlDataAdapter(sql, connection);
DataTable dt = new DataTable();
da.Fill(dt);
DataTable table = new DataTable("Databases");
table.Columns.Add("CATALOG_NAME", typeof(string));
table.Columns.Add("SCHEMA_NAME", typeof(string));
foreach (DataRow row in dt.Rows)
{
if (caseSetting != 0 && regex != null &&
!regex.Match(row[0].ToString()).Success) continue;
DataRow newRow = table.NewRow();
newRow[1] = row[0];
table.Rows.Add(newRow);
}
return table;
}
public virtual DataTable GetIndexes(string[] restrictions)
{
DataTable dt = new DataTable("Indexes");
dt.Columns.Add("INDEX_CATALOG", typeof(string));
dt.Columns.Add("INDEX_SCHEMA", typeof(string));
dt.Columns.Add("INDEX_NAME", typeof(string));
dt.Columns.Add("TABLE_NAME", typeof(string));
dt.Columns.Add("UNIQUE", typeof(bool));
dt.Columns.Add("PRIMARY", typeof(bool));
dt.Columns.Add("TYPE", typeof(string));
dt.Columns.Add("COMMENT", typeof(string));
// Get the list of tables first
int max = restrictions == null ? 4 : restrictions.Length;
string[] tableRestrictions = new string[Math.Max(max, 4)];
if (restrictions != null)
restrictions.CopyTo(tableRestrictions, 0);
tableRestrictions[3] = "BASE TABLE";
DataTable tables = GetTables(tableRestrictions);
foreach (DataRow table in tables.Rows)
{
string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`",
MySqlHelper.DoubleQuoteString((string)table["TABLE_SCHEMA"]),
MySqlHelper.DoubleQuoteString((string)table["TABLE_NAME"]));
MySqlDataAdapter da = new MySqlDataAdapter(sql, connection);
DataTable indexes = new DataTable();
da.Fill(indexes);
foreach (DataRow index in indexes.Rows)
{
long seq_index = (long)index["SEQ_IN_INDEX"];
if (seq_index != 1) continue;
if (restrictions != null && restrictions.Length == 4 &&
restrictions[3] != null &&
!index["KEY_NAME"].Equals(restrictions[3])) continue;
DataRow row = dt.NewRow();
row["INDEX_CATALOG"] = null;
row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"];
row["INDEX_NAME"] = index["KEY_NAME"];
row["TABLE_NAME"] = index["TABLE"];
row["UNIQUE"] = (long)index["NON_UNIQUE"] == 0;
row["PRIMARY"] = index["KEY_NAME"].Equals("PRIMARY");
row["TYPE"] = index["INDEX_TYPE"];
row["COMMENT"] = index["COMMENT"];
dt.Rows.Add(row);
}
}
return dt;
}
/// <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;
}
/// <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();
cn.Open();
MySqlDataAdapter da = new MySqlDataAdapter(commandText, cn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
cb.ToString();
da.Update(ds, tablename);
cn.Close();
}
public void beginGetTable(AsyncCallback callback)
{
dataTable = new DataTable();
SetAsyncCallback(callback);
watch.Restart();
adapter = new MySqlDataAdapter(this.command);
//adapter.BeginFill(dataTable, new AsyncCallback(tableFilled), this.command);
}
public MySqlCommandBuilder(MySqlDataAdapter adapter)
: this()
{
DataAdapter = adapter;
}
public DataTable getTable()
{
DataTable dataTable = new DataTable();
watch.Restart();
try
{
//using (DONGDataAdapter
using (adapter = new MySqlDataAdapter(this.command))
{
adapter.Fill(dataTable);
//adapter.Fill(dataTable, this.command);
}
}
catch (Exception exception)
{
Writer.LogQueryError(exception, this.command.CommandText);
}
DatabaseStats.totalQueryTime += watch.ElapsedMilliseconds;
DatabaseStats.totalQueries++;
return dataTable;
}
public DataRow getRow()
{
watch.Restart();
DataRow row = null;
try
{
DataTable dataTable = new DataTable();
//DataSet dataSet = new DataSet();
using (adapter = new MySqlDataAdapter(this.command))
{
//adapter.Fill(dataSet);
adapter.Fill(dataTable);
}
if (dataTable.Rows.Count > 0) //Found one or more results
{
//Return the first one
row = dataTable.Rows[0];
}
//if ((dataSet.Tables.Count > 0) && (dataSet.Tables[0].Rows.Count == 1))
//{
// row = dataSet.Tables[0].Rows[0];
//}
}
catch (Exception exception)
{
Writer.LogQueryError(exception, this.command.CommandText);
}
DatabaseStats.totalQueryTime += watch.ElapsedMilliseconds;
DatabaseStats.totalQueries++;
return row;
}
private DataTable GetTable(string sql)
{
DataTable table = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, connection);
da.Fill(table);
return table;
}
private DataTable GetParametersFromIS(string[] restrictions, DataTable routines)
{
DataTable parms = new DataTable();
if (routines == null || routines.Rows.Count == 0)
{
if (restrictions == null)
{
// first fill our table with the proper structure
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE 1=2", connection);
da.Fill(parms);
}
else
GetParametersForRoutineFromIS(parms, restrictions);
}
else foreach (DataRow routine in routines.Rows)
{
if (restrictions != null && restrictions.Length >= 3)
restrictions[2] = routine["ROUTINE_NAME"].ToString();
GetParametersForRoutineFromIS(parms, restrictions);
}
parms.TableName = "Procedure Parameters";
return parms;
}
private void GetParametersForRoutineFromIS(DataTable dt, string[] restrictions)
{
Debug.Assert(dt != null);
string[] keys = new string[5];
keys[0] = "SPECIFIC_CATALOG";
keys[1] = "SPECIFIC_SCHEMA";
keys[2] = "SPECIFIC_NAME";
keys[3] = "ROUTINE_TYPE";
keys[4] = "PARAMETER_NAME";
StringBuilder sql = new StringBuilder(@"SELECT * FROM INFORMATION_SCHEMA.PARAMETERS");
// now get our where clause and append it if there is one
string where = GetWhereClause(null, keys, restrictions);
if (!String.IsNullOrEmpty(where))
sql.AppendFormat(CultureInfo.InvariantCulture, " WHERE {0}", where);
MySqlDataAdapter da = new MySqlDataAdapter(sql.ToString(), connection);
da.Fill(dt);
}
