/// <summary>
/// SQL助手执行完毕后，记录日志
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void SQLHelper_OnExecuted(object sender, SqlCommandExecutionEventArgs e)
{
if (e != null)
{
var log = new DatabaseLogInfo();
//获取用户信息
if (e.UserInfo != null)
{
log.ClientID = e.UserInfo.ClientID;
log.UserID = e.UserInfo.UserID;
}
//获取T-SQL相关信息
if (e.Command != null)
{
TSQL tsql = new TSQL();
tsql.CommandText = e.Command.GenerateTSQLText();
if (e.Command.Connection != null)
{
tsql.DatabaseName = e.Command.Connection.Database;
tsql.ServerName = e.Command.Connection.DataSource;
}
tsql.ExecutionTime = e.ExecutionTime;
log.TSQL = tsql;
}
Loggers.DEFAULT.Database(log);
}
}
public Dictionary <string, string> GetSql(Dictionary <string, List <Columns> > dict)
{
Dictionary <string, string> result = new Dictionary <string, string>();
foreach (var tablename in dict.Keys)
{
List <Columns> columns = dict[tablename];
TSQL model = new TSQL()
{
ClassName = GetCSharpNameFromSqlName(tablename),
Columns = columns,
PkColumns = columns.Where(p => p.PK).ToList(),
OtherColumns = columns.Where(p => !p.PK).ToList(),
ModelSpaceName = "Mx",
Namespace = baseClassNamespaces + ".DataAccess.SqlServer",
TableName = tablename,
ShareNamespace = "MXWater",
JsonName = GetJsonNameFromSqlName(tablename),
};
string content = model.TransformText();
result.Add(model.ClassName + "Storage", content);
}
return(result);
}
private void menu_TSQL_Click(object sender, EventArgs e)
{
//TODO - Add menu to connect remote
Model.AddSingleProperty("SQL", "TSQL");
//will attempt to connect to local machine,
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder("Data Source = (local); Integrated Security = True");
TSQL TSQL = new TSQL(builder);
//get list of databases
List <object> databases = new List <object>(TSQL.GetDatabases());
Model.AddMultiProperty("databases", databases);
//Update form with new information
Form f = new form_SelectDatabase(Model, this);
f.Show();
}
static void Main(string[] args)
{/*
* DateTime finalDate = new DateTime();
* string a = "DEL20180920";
* DateTime.TryParseExact(a.Remove(0, 3), "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out finalDate);
* a = finalDate.ToString("dd/MM/yyyy");
* Console.WriteLine(a.Remove(0,3));
* Console.Read();
*/
ConfigOps.ReadConfig();
Console.WriteLine("Iniciando Procedimiento de Extracción...");
Logger.WriteLog("\n\r" + DateTime.Now + "\n\rIniciando Procedimiento de Extracción...");
try
{
List <ExtractedData> result1 = TSQL.Extract(FileType.Empresa001);
/*List<ExtractedData> result1 = new List<ExtractedData>()
* {
* new ExtractedData()
* {
* VIN = "TEST",Colour = "TEST", ControlError = "TEST", Customer = "TEST", Engine = "TEST",
* Folio = "TEST", Pedimento = "TEST", PedimentoDate = "TEST", BillingDate = DateTime.Now
* },
* new ExtractedData()
* {
* VIN = "TEST",Colour = "TEST", ControlError = "TEST", Customer = "TEST", Engine = "TEST",
* Folio = "TEST", Pedimento = "TEST", PedimentoDate = "TEST", BillingDate = DateTime.Now
* },
* new ExtractedData()
* {
* VIN = "TEST",Colour = "TEST", ControlError = "TEST", Customer = "TEST", Engine = "TEST",
* Folio = "TEST", Pedimento = "TEST", PedimentoDate = "TEST", BillingDate = DateTime.Now
* }
* };*/
result1 = Writer.TreatSpecialData(result1);
if (result1.Count > 0)
{
TSQL.UpdateSelectedRegs(FileType.Empresa001);
string path = Writer.CreateFile(result1, FileType.Empresa001);
Console.WriteLine("\n\rComenzando escritura de datos--------------->>>");
Logger.WriteLog("\n\rComenzando escritura de datos--------------->>>");
Writer.count = 0;
foreach (var item in result1)
{
Writer.WriteFile(item, path);
}
Logger.WriteLog("Se escribieron " + Writer.count + " registros en este documento");
Console.WriteLine("Escritura de datos finalizada");
Logger.WriteLog("Escritura de datos finalizada");
}
else
{
Console.WriteLine("No hay datos de empresa 1 a escribir...");
Logger.WriteLog("No hay datos de empresa 1 a escribir...");
}
List <ExtractedData> result2 = TSQL.Extract(FileType.Empresa004);
/*List<ExtractedData> result2 = new List<ExtractedData>()
* {
* new ExtractedData()
* {
* VIN = "TEST",Colour = "TEST", ControlError = "TEST", Customer = "TEST", Engine = "TEST",
* Folio = "TEST", Pedimento = "TEST", PedimentoDate = "TEST", BillingDate = DateTime.Now
* },
* new ExtractedData()
* {
* VIN = "TEST",Colour = "TEST", ControlError = "TEST", Customer = "TEST", Engine = "TEST",
* Folio = "TEST", Pedimento = "TEST", PedimentoDate = "TEST", BillingDate = DateTime.Now
* },
* new ExtractedData()
* {
* VIN = "TEST",Colour = "TEST", ControlError = "TEST", Customer = "TEST", Engine = "TEST",
* Folio = "TEST", Pedimento = "TEST", PedimentoDate = "TEST", BillingDate = DateTime.Now
* }
* };*/
result2 = Writer.TreatSpecialData(result2);
if (result2.Count > 0)
{
TSQL.UpdateSelectedRegs(FileType.Empresa004);
string path = Writer.CreateFile(result2, FileType.Empresa004);
Console.WriteLine("\n\rComenzando escritura de datos--------------->>>");
Logger.WriteLog("\n\rComenzando escritura de datos--------------->>>");
Writer.count = 0;
foreach (var item in result2)
{
Writer.WriteFile(item, path);
}
Logger.WriteLog("Se escribieron " + Writer.count + " registros en este documento");
Console.WriteLine("Escritura de datos finalizada");
Logger.WriteLog("Escritura de datos finalizada");
}
else
{
Console.WriteLine("No hay datos de empresa 4 a escribir...");
Logger.WriteLog("No hay datos de empresa 4 a escribir...");
}
}
catch (Exception ex)
{
Console.WriteLine("Error de ejecución: " + ex.Message);
}
}
public void ContextUpdate()
{
//Attempt to connect to database
if (Model.CheckSingleProperty("SQL"))
{
string db_type = "";
Model.GetSingleProperty("SQL", out db_type);
switch (db_type)
{
case "MySQL":
if (!Model.CheckSingleProperty("SelectedDatabase"))
{
try
{
MySQL mySql;
//Connect to SQL server
string server, username, password;
server = Model.GetSingleProperty("server", out server);
username = Model.GetSingleProperty("username", out username);
password = Model.GetSingleProperty("password", out password);
mySql = new MySQL(server, username, password);
//get SQL tables information
List <object> databases = new List <object>(mySql.GetDatabases());
Model.AddMultiProperty("databases", databases);
//Update form with new information
Form f = new form_SelectDatabase(Model, this);
f.Show();
}
catch (Exception)
{
throw;
}
}
else
{
try
{
MySQL mySql;
//Connect to SQL server
string server, username, password, database;
server = Model.GetSingleProperty("server", out server);
username = Model.GetSingleProperty("username", out username);
password = Model.GetSingleProperty("password", out password);
database = Model.GetSingleProperty("SelectedDatabase", out database);
mySql = new MySQL(server, username, password, database);
//update database with database name
mySql.updateDatabase();
//get SQL information
List <object> storedProcedures = new List <object>(mySql.GetProcedures());
Model.AddMultiProperty("storedProcedures", storedProcedures);
List <object> functions = new List <object>(mySql.GetFunctions());
Model.AddMultiProperty("functions", functions);
List <object> views = new List <object>(mySql.GetViews());
Model.AddMultiProperty("views", views);
//Update Titles
string title;
Model.GetSingleProperty("SelectedDatabase", out title);
lbl_CurrentDB.Text = title;
//Update form with new information
Form f = new form_Root(Model);
f.TopLevel = false;
f.Parent = tabRoot;
f.Show();
}
catch (Exception)
{
throw;
}
}
break;
case "TSQL":
if (Model.CheckSingleProperty("SelectedDatabase"))
{
//Connect to DB and get all data
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder("Data Source = (local); Integrated Security = True");
TSQL TSQL = new TSQL(builder);
//Update Titles
string title;
Model.GetSingleProperty("SelectedDatabase", out title);
lbl_CurrentDB.Text = title;
TSQL.SetSchema(title);
List <object> storedProcedures = new List <object>(TSQL.GetProcedures());
Model.AddMultiProperty("storedProcedures", storedProcedures);
List <object> functions = new List <object>(TSQL.GetFunctions());
Model.AddMultiProperty("functions", functions);
List <object> views = new List <object>(TSQL.GetViews());
Model.AddMultiProperty("views", views);
//Update form with new information
Form f = new form_Root(Model);
f.TopLevel = false;
f.Parent = tabRoot;
f.Show();
}
break;
default:
break;
}
}
}