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