//BACKUP EPICOR EAGLE (TO FILE)(*.SQL) public static void BackupMySqlDatabaseByTable() { //get connection string string myConnectionString = EpicorEagleConnect.GetSqlConnectionString(); //string constring = "server=localhost;user=root;pwd=qwerty;database=test;"; //string myConnectionString = "SERVER=10.0.1.7; " + // "DATABASE=EAGLEDW; " + // "UID=dbread; " + // "PASSWORD=havemox1e;"; //string myConnectionString = "SERVER=localhost; " + // "DATABASE=travelexperts; " + // "UID=root; " + // "PASSWORD=;"; try { string file = @"C:\_000_BACKUP\EpicorEagleDb.sql"; using (MySqlConnection conn = new MySqlConnection(myConnectionString)) { using (MySqlCommand cmd = new MySqlCommand()) { using (MySqlBackup mb = new MySqlBackup(cmd)) { cmd.Connection = conn; conn.Open(); //List<string> myTableList = new List<string> { "customers", "agents" }; List <string> myTableList = new List <string> { "ADR", "ADRV" }; mb.ExportInfo.TablesToBeExportedList = myTableList; mb.ExportToFile(file); conn.Close(); } } } } catch (MySqlException ex1) { Console.WriteLine("Message: " + ex1.Message + "\nStack: " + ex1.StackTrace); } catch (Exception ex) { Console.WriteLine("Message: " + ex.Message + "\nStack: " + ex.StackTrace); } }
// https://stackoverflow.com/questions/15210267/is-there-a-way-to-search-the-fields-of-all-tables-at-once-in-sql-server-ce public static void SearchText(string searchText) { //get connection string string myConnectionString = EpicorEagleConnect.GetSqlConnectionString(); //string connStr = "Data Source=Northwind40.sdf;Persist Security Info=False;"; //string myConnectionString = "SERVER=10.0.1.7; " + // "DATABASE=EAGLEDW; " + // "UID=dbread; " + // "PASSWORD=havemox1e; " + // "ConvertZeroDateTime=True;"; //FIX, for date/time error conversion DataTable dt = new DataTable(); try { string sql = "SELECT c.TABLE_NAME, c.COLUMN_NAME "; sql += "FROM INFORMATION_SCHEMA.COLUMNS AS c "; sql += "INNER JOIN INFORMATION_SCHEMA.Tables AS t ON t.TABLE_NAME = c.TABLE_NAME "; sql += "WHERE (c.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')) AND (t.TABLE_TYPE = 'TABLE') "; //SqlCeDataAdapter da = new SqlCeDataAdapter(sql, connStr); MySqlDataAdapter da = new MySqlDataAdapter(sql, myConnectionString); da.Fill(dt); foreach (DataRow dr in dt.Rows) { string dynSQL = "SELECT [" + dr["COLUMN_NAME"] + "]"; dynSQL += " FROM [" + dr["TABLE_NAME"] + "]"; dynSQL += " WHERE [" + dr["COLUMN_NAME"] + "] LIKE '%" + searchText + "%'"; DataTable result = new DataTable(); da = new MySqlDataAdapter(dynSQL, myConnectionString); da.Fill(result); foreach (DataRow r in result.Rows) { Console.WriteLine("Table Name: " + dr["TABLE_NAME"]); Console.WriteLine("Column Name: " + dr["COLUMN_NAME"]); Console.WriteLine("Value: " + r[0]); } } } catch (Exception e) { Console.Write(e.Message); } }
//public static DataTable GetInventoryBySku(string sku) //{ // DataTable dt = new DataTable(); // //do stuff...??? // return dt; // // https://stackoverflow.com/questions/20770438/how-to-bind-datatable-to-datagrid // // C# // // DataTable employeeData = CreateDataTable(); // // gridEmployees.DataContext = employeeData.DefaultView; // // XAML // //<DataGrid Name="gridEmployees" ItemsSource="{Binding}"> //} //public static void GetInventory() //{ // //GridData.ItemsSource = vDs.Tables["Book"].DefaultView; // //dgvMySql.ItemsSource = GetInventory(); //} //QUERY TO DATAGRIDVIEW (TABLE) //Example: dgvTableResults.DataSource = EpicorDB.MySqlDataToGridView("SELECT * FROM dw_customer LIMIT 3"); public static DataTable MySqlDataToDataTable(string myquery) { //create DataTable object to return DataTable t = null; //get connection string string myConnectionString = EpicorEagleConnect.GetSqlConnectionString(); //create mysql connection object MySqlConnection conn = new MySqlConnection(myConnectionString); try { //open connection conn.Open(); //using (MySqlDataAdapter a = new MySqlDataAdapter("SELECT * FROM dw_customer LIMIT 3", conn)) using (MySqlDataAdapter a = new MySqlDataAdapter(myquery, conn)) { // Use DataAdapter to fill DataTable t = new DataTable(); a.Fill(t); // put in table //dgv.DataSource = t; } } catch (MySqlException ex2) { throw ex2; } catch (Exception ex1) { throw ex1; } finally { conn.Close(); } return(t); }
public static void MySearch() { string myConnectionString = null; MySqlConnection connection; MySqlCommand command; MySqlDataAdapter adapter = new MySqlDataAdapter(); DataSet ds = new DataSet(); int i = 0; string firstSql = null; string secondSql = null; //get connection string myConnectionString = EpicorEagleConnect.GetSqlConnectionString(); //connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"; //myConnectionString = "SERVER=10.0.1.7; " + // "DATABASE=EAGLEDW; " + // "UID=dbread; " + // "PASSWORD=havemox1e; " + // "ConvertZeroDateTime=True;"; //FIX, for date/time error conversion firstSql = "SELECT * FROM dw_item"; secondSql = "SELECT * FROM ADR LIMIT 1"; //firstSql = "SELECT * FROM ADR LIMIT 3"; //secondSql = "SELECT * FROM INXCAT LIMIT 3"; connection = new MySqlConnection(myConnectionString); try { connection.Open(); command = new MySqlCommand(firstSql, connection); adapter.SelectCommand = command; adapter.Fill(ds, "First Table"); adapter.SelectCommand.CommandText = secondSql; adapter.Fill(ds, "Second Table"); //****************************************** //UNCOMMENT TO WRITE (note, large database create large XML files, do one table at a time...) //write to file //string filepath = @"C:\_dump\file.xml"; //ds.WriteXml(filepath); //****************************************** adapter.Dispose(); command.Dispose(); connection.Close(); foreach (DataRow row in ds.Tables[1].Rows) { foreach (DataColumn column in ds.Tables[1].Columns) { var result = row[column].ToString(); var colName = column.Caption; //if (result == "16.99") { var index = ds.Tables[1].Rows.IndexOf(row); Console.WriteLine("Row: " + index + ", Column: " + colName + ", Value: " + result); } } } Console.WriteLine("...end of search"); //retrieve first table data //for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) //{ // //Console.WriteLine(ds.Tables[0].Rows[i].ItemArray[0] + " -- " + ds.Tables[0].Rows[i].ItemArray[1]); //} //retrieve second table data //for (i = 0; i <= ds.Tables[1].Rows.Count - 1; i++) //{ // //Console.WriteLine(ds.Tables[1].Rows[i].ItemArray[0] + " -- " + ds.Tables[1].Rows[i].ItemArray[1]); //} } catch (Exception ex) { Console.WriteLine("Can not open connection ! "); } }