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; }
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; }
/// <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; }
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 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); }