public List <Product> TestCase18MySql(int lower, int upper)
{
MySqlHandler sqlhander = new MySqlHandler();
var products = sqlhander.GetProductJoinProductTypeAndCompanyById(lower, upper);
return(products);
}
public List <Product> TestCase17MySql(int lower, int upper)
{
MySqlHandler sqlhander = new MySqlHandler();
var product = sqlhander.GetProductById(lower, upper);
return(product);
}
public IActionResult TestCase6(int lower, int upper)
{
MySqlHandler sqlhander = new MySqlHandler();
var products = sqlhander.GetProductJoinProductTypeAndCompanyById(lower, upper);
return(Ok(products));
}
public Product TestCase14MySql(int id)
{
MySqlHandler sqlhander = new MySqlHandler();
var product = sqlhander.GetProductById(id);
return(product);
}
// Warning - will not work - see comment above ProblemFour method invokation in Main()
public static void Main()
{
var mongoHandler = new MongoDbHandler();
var sqlHandler = new SqlServerHandler();
var mySqlHandler = new MySqlHandler();
var pdfHandler = new PdfHandler();
var xmlToSql = new XmlToSqlServerLoader();
var excellHandler = new ExcellHandler();
var mongoToSql = new MongoToSqlServerLoader();
var zipExtractor = new ZipExtractor();
var jsonHandler = new JsonHandler();
var sqliteHandler = new SqliteHandler();
var xmlHandler = new XmlHandler();
if (!Directory.Exists(OutputDirectory))
{
Directory.CreateDirectory(OutputDirectory);
}
//// Mongolab.com credentials - Username: TeamXenon , Passsword: xenon123
ProblemOne(mongoHandler, mongoToSql, zipExtractor, excellHandler);
ProblemTwo(pdfHandler);
ProblemThree(xmlHandler);
//// NOTE!!! - you need to go to CarParts.Data.MySql project and in its App.config file
//// you should change the password in the connectionString tag with which you connect to your localhost instance of the MySQL Workbench server.
ProblemFour(sqlHandler, mySqlHandler, jsonHandler);
ProblemFive(mongoHandler, xmlToSql);
ProblemSix(excellHandler, sqlHandler, sqliteHandler, mySqlHandler);
}
public IActionResult TestCase3(int id)
{
MySqlHandler sqlhander = new MySqlHandler();
Product product = sqlhander.GetProductJoinProductTypeAndCompanyById(id);
return(Ok(product));
}
public Product TestCase15MySql(int id)
{
MySqlHandler sqlhander = new MySqlHandler();
var product = sqlhander.GetProductJoinProductTypeAndCompanyById(id);
return(product);
}
private DataTable DoQuery(string query, Hashtable parameters)
{
DataTable dt = new DataTable();
MySqlHandler dbHandler = null;
try
{
dbHandler = new MySqlHandler(ConstDef.WEDO_DB_URL, dbPort, ConstDef.WEDO_DB, ConstDef.WEDO_DB_USER, ConstDef.WEDO_DB_PASSWORD);
dbHandler.Open();
dbHandler.SetQuery(query);
if (parameters != null)
{
foreach (string key in parameters.Keys)
{
dbHandler.Parameters(key, (string)parameters[key]);
}
}
dt = dbHandler.DoQuery();
}
catch (Exception e)
{
Logger.error("쿼리실행에러 : " + e.ToString());
throw new Exception("쿼리실행에러");
}
finally
{
dbHandler.Close();
}
return(dt);
}
public bool Fill <T>(T model) where T : class
{
ModelInfo mi = GetModelInfo <T>();
string format = "select * from {0} where {1}";
object fieldValue = ModelProperty <T> .GetValue(model as T, mi.IDFieldName);
CheckIDField <T>(model, mi, fieldValue);
List <MySqlParameter> list = new List <MySqlParameter> {
new MySqlParameter("@" + mi.IDFieldName, fieldValue)
};
format = string.Format(format, mi.TableName, mi.IDFieldName + "=@" + mi.IDFieldName);
using (MySqlDataReader reader = MySqlHandler.ExecuteReader(mi.Conn, format, list.ToArray()))
{
while (reader.Read())
{
foreach (PropertyInfo info in model.GetType().GetProperties())
{
object propertyValue;
if (DBNull.Value != reader[info.Name])
{
propertyValue = reader[info.Name];
ModelProperty <T> .SetValue(model as T, info.Name, propertyValue);
}
}
return(true); // Only return the first data
}
}
return(false);
}
public IActionResult TestCase8(int id)
{
MySqlHandler sqlhander = new MySqlHandler();
Product product = sqlhander.GetProductById(id);
return(Ok(product));
}
public IActionResult TestCase5(int lower, int upper)
{
MySqlHandler sqlhander = new MySqlHandler();
var product = sqlhander.GetProductById(lower, upper);
return(Ok(product));
}
public bool Insert <T>(T model) where T : class
{
ModelInfo mi = GetModelInfo <T>();
string format = "insert {0} ({1}) values({2});";
string clounms = "";
string values = "";
List <MySqlParameter> list = new List <MySqlParameter>();
foreach (PropertyInfo info in mi.ObjType.GetProperties())
{
string name = info.Name;
object obj2 = ModelProperty <T> .GetValue(model as T, name);
if (name != mi.IDFieldName)
{
if (clounms.Length > 0)
{
clounms = clounms + "," + name;
values = values + ",@" + name;
}
else
{
clounms = clounms + name;
values = values + "@" + name;
}
list.Add(new MySqlParameter("@" + name, obj2));
}
}
format = string.Format(format, mi.TableName, clounms, values) + "select @@identity;";
int propertyValue = MySqlHandler.ExecuteSqlScalar(mi.Conn, format, list.ToArray());
if (propertyValue <= 0)
{
return(false);
}
string str5 = mi.ObjType.GetProperty(mi.IDFieldName).PropertyType.ToString();
if (str5 == null)
{
return(false);
}
if (str5 == "System.Int32")
{
ModelProperty <T> .SetValue(model as T, mi.IDFieldName, propertyValue);
}
else if (str5 == "System.Int16")
{
ModelProperty <T> .SetValue(model as T, mi.IDFieldName, (short)propertyValue);
}
else
{
ModelProperty <T> .SetValue(model as T, mi.IDFieldName, (long)propertyValue);
}
return(true);
}
private static void ProblemFour(SqlServerHandler sqlHandler, MySqlHandler mySqlHandler, JsonHandler jsonHandler)
{
var reports = sqlHandler.ReadPartReports();
jsonHandler.GenerateJsonReports(reports);
mySqlHandler.WriteReports(reports);
Console.WriteLine("Successfully added json reports to the file system and to MySQL database.");
}
public bool Delete <T>(T model) where T : class
{
ModelInfo mi = GetModelInfo <T>();
string format = "delete from {0} where {1};";
List <MySqlParameter> list = new List <MySqlParameter>();
object fieldValue = ModelProperty <T> .GetValue(model as T, mi.IDFieldName);
CheckIDField <T>(model, mi, fieldValue);
format = string.Format(format, mi.TableName, mi.IDFieldName + "=@" + mi.IDFieldName);
list.Add(new MySqlParameter("@" + mi.IDFieldName, fieldValue));
if (MySqlHandler.ExecuteSql(mi.Conn, format, list.ToArray()) <= 0)
{
return(false);
}
return(true);
}
public bool Delete <T>(string idListsWillDel) where T : class
{
if (string.IsNullOrEmpty(idListsWillDel))
{
throw new Exception("Parameter idListsWillDel is null or empty!");
}
if (!new Regex(@"^[0-9,]+$").IsMatch(idListsWillDel.Trim()))
{
throw new Exception("Parameter idListsWillDel is illegal!");
}
ModelInfo mi = GetModelInfo <T>();
string sqlFormat = "delete from {0} where {1} in ({2})";
sqlFormat = string.Format(sqlFormat, mi.TableName, mi.IDFieldName, idListsWillDel);
int affectCount = MySqlHandler.ExecuteSql(mi.Conn, sqlFormat);
return(affectCount > 0);
}
public bool Update <T>(T model) where T : class
{
ModelInfo mi = GetModelInfo <T>();
string format = "update {0} set {1} where {2};";
string colunms = "";
string condition = "";
List <MySqlParameter> list = new List <MySqlParameter>();
foreach (PropertyInfo info in mi.ObjType.GetProperties())
{
string name = info.Name;
object fieldValue = ModelProperty <T> .GetValue(model as T, name);
list.Add(new MySqlParameter("@" + name, fieldValue));
if (name == mi.IDFieldName)
{
CheckIDField <T>(model, mi, fieldValue);
condition = mi.IDFieldName + "=@" + name;
}
else if (colunms.Length > 0)
{
string str5 = colunms;
colunms = str5 + "," + name + "=@" + name;
}
else
{
colunms = colunms + name + "=@" + name;
}
}
format = string.Format(format, mi.TableName, colunms, condition);
if (MySqlHandler.ExecuteSql(mi.Conn, format, list.ToArray()) <= 0)
{
return(false);
}
return(true);
}
private DataTable DoQuery(string query, Hashtable parameters)
{
DataTable dt = new DataTable();
MySqlHandler dbHandler = null;
try
{
dbHandler = new MySqlHandler(ConstDef.WEDO_DB_URL, dbPort, ConstDef.WEDO_DB, ConstDef.WEDO_DB_USER, ConstDef.WEDO_DB_PASSWORD);
dbHandler.Open();
dbHandler.SetQuery(query);
if (parameters != null)
{
foreach (string key in parameters.Keys)
{
dbHandler.Parameters(key, (string)parameters[key]);
}
}
dt = dbHandler.DoQuery();
}
catch (Exception e)
{
Logger.error("쿼리실행에러 : " + e.ToString());
throw new Exception("쿼리실행에러");
}
finally
{
dbHandler.Close();
}
return dt;
}
/// <summary>
/// 1. 기본 DB 데이터 생성
/// 2. 회사코드 관련 데이터 생성
/// </summary>
/// <returns></returns>
public bool GenerateData()
{
OnWriteLog("DB 데이터 생성");
Logger.info("GenerateData");
MySqlHandler handler = null;
string fileName = "";
try
{
OnWriteLog(string.Format("DB 접속:dbUrl[{0}]dbPort[{1}]defaultDb[{2}]dbUser[{3}]",
ConstDef.WEDO_DB_URL, dbPort, ConstDef.DEFAULT_DB, ConstDef.WEDO_DB_USER));
handler = new MySqlHandler(ConstDef.WEDO_DB_URL, dbPort, ConstDef.DEFAULT_DB, ConstDef.WEDO_DB_USER);
handler.Open();
fileName = ConstDef.MYSQL_CREATE_USER_FILE;
handler.ExecuteScriptByFileName(fileName);
OnWriteLog("DB 계정생성");
fileName = ConstDef.MYSQL_CREATE_DB_FILE;
handler.ExecuteScriptByFileName(fileName);
OnWriteLog("DB WeDo_DB생성");
}
catch (Exception ex)
{
OnWriteLog("DB 데이터 생성 실패");
Logger.error(string.Format("Sql script[{0}] 실행중 오류발생", fileName) + ex.ToString());
return false;
}
finally
{
if (handler != null) handler.Close();
}
//1. DB 데이터 생성
try
{
handler = new MySqlHandler(ConstDef.WEDO_DB_URL, dbPort, ConstDef.WEDO_DB, ConstDef.WEDO_DB_USER, ConstDef.WEDO_DB_PASSWORD);
handler.Open();
fileName = ConstDef.MYSQL_CREATE_TABLE_FILE;
handler.ExecuteScriptByFileName(fileName);
OnWriteLog("DB 테이블 생성");
OnWriteLog("----------------------------------------");
OnWriteLog("DB 기초데이터 생성을 시작합니다.\n시스템에 따라 1~10분정도의 시간이 소요됩니다.");
OnWriteLog("...");
fileName = ConstDef.MYSQL_INSERT_DATA_FILE;
handler.ExecuteScriptByFileName(fileName);
OnWriteLog("DB 기초데이터 생성");
//회사코드 등록관련
RegisterCompanyInfo(companyCd, companyName);
}
catch (Exception ex)
{
OnWriteLog("DB 데이터 생성 실패");
Logger.error(string.Format("Sql script[{0}] 실행중 오류발생", fileName) + ex.ToString());
return false;
}
finally
{
if (handler != null) handler.Close();
}
return true;
}
private void MsgSvrForm_Load(object sender, EventArgs e)
{
string temp = Process.GetCurrentProcess().ProcessName;
if (temp.IndexOf('.') < 0 )
{
AppName = temp;
} else {
AppName = temp.Substring(0, temp.IndexOf('.'));
}
AppConfigName = string.Format(ConstDef.APP_CONFIG_NAME, temp);
AppRegName = ConstDef.REG_APP_NAME;
UpdateTargetDir = ConstDef.WORK_DIR + ConstDef.UPDT_DIR;
UpdateAppDir = Application.StartupPath + ConstDef.UPDT_DIR;
licenseDir = ConstDef.WORK_DIR + ConstDef.LICENSE_DIR;
svr_FileCheck(); //로그파일, 폴더 생성
logWrite("svr_FileCheck() 완료!");
commctl.OnEvent += new CommCtl.CommCtl_MessageDelegate(RecvMessage);
loadConfigData();
timerForLicense = new System.Windows.Forms.Timer();
timerForLicense.Interval = 3600000;
timerForLicense.Tick += new EventHandler(timerForLicense_Tick);
timerForLicense.Start();
callLog_timer = new System.Windows.Forms.Timer();
callLog_timer.Interval = 300000;
callLog_timer.Tick += new EventHandler(callLog_timer_Tick);
callLog_timer.Start();
dbHandler = new MySqlHandler(WDdbHost, 3306, WDdbName, WDdbUser, WDdbPass);
dbHandler.LogWriteHandler += this.OnLogWrite;
if (server_type != null && server_device != null)
{
startServer();
}
else
{
setDevice();
}
}
private static void ProblemSix(ExcellHandler excellHandler, SqlServerHandler sqlHandler, SqliteHandler sqliteHandler, MySqlHandler mySqlHandler)
{
var reports = mySqlHandler.ReadReports();
var taxes = sqliteHandler.ReadTaxes();
excellHandler.GenerateExcellFile(reports, taxes, "VendorsFinancialReport.xlsx");
Console.WriteLine("Successfully generated excell file to ");
}
/// <summary>
/// 1. 기본 DB 데이터 생성
/// 2. 회사코드 관련 데이터 생성
/// </summary>
/// <returns></returns>
public bool GenerateData()
{
OnWriteLog("DB 데이터 생성");
Logger.info("GenerateData");
MySqlHandler handler = null;
string fileName = "";
try
{
OnWriteLog(string.Format("DB 접속:dbUrl[{0}]dbPort[{1}]defaultDb[{2}]dbUser[{3}]",
ConstDef.WEDO_DB_URL, dbPort, ConstDef.DEFAULT_DB, ConstDef.WEDO_DB_USER));
handler = new MySqlHandler(ConstDef.WEDO_DB_URL, dbPort, ConstDef.DEFAULT_DB, ConstDef.WEDO_DB_USER);
handler.Open();
fileName = ConstDef.MYSQL_CREATE_USER_FILE;
handler.ExecuteScriptByFileName(fileName);
OnWriteLog("DB 계정생성");
fileName = ConstDef.MYSQL_CREATE_DB_FILE;
handler.ExecuteScriptByFileName(fileName);
OnWriteLog("DB WeDo_DB생성");
}
catch (Exception ex)
{
OnWriteLog("DB 데이터 생성 실패");
Logger.error(string.Format("Sql script[{0}] 실행중 오류발생", fileName) + ex.ToString());
return(false);
}
finally
{
if (handler != null)
{
handler.Close();
}
}
//1. DB 데이터 생성
try
{
handler = new MySqlHandler(ConstDef.WEDO_DB_URL, dbPort, ConstDef.WEDO_DB, ConstDef.WEDO_DB_USER, ConstDef.WEDO_DB_PASSWORD);
handler.Open();
fileName = ConstDef.MYSQL_CREATE_TABLE_FILE;
handler.ExecuteScriptByFileName(fileName);
OnWriteLog("DB 테이블 생성");
OnWriteLog("----------------------------------------");
OnWriteLog("DB 기초데이터 생성을 시작합니다.\n시스템에 따라 1~10분정도의 시간이 소요됩니다.");
OnWriteLog("...");
fileName = ConstDef.MYSQL_INSERT_DATA_FILE;
handler.ExecuteScriptByFileName(fileName);
OnWriteLog("DB 기초데이터 생성");
//회사코드 등록관련
RegisterCompanyInfo(companyCd, companyName);
}
catch (Exception ex)
{
OnWriteLog("DB 데이터 생성 실패");
Logger.error(string.Format("Sql script[{0}] 실행중 오류발생", fileName) + ex.ToString());
return(false);
}
finally
{
if (handler != null)
{
handler.Close();
}
}
return(true);
}
private void button_Click(object sender, RoutedEventArgs e)
{
String[] data = MySqlHandler.MySqlSelectData("SELECT IP,ID from Server", "server=10.0.0.200;uid=root;pwd=Anakankoe99;database=Server");
Console.Write(data[0]);
}
public Query(Db database)
{
Field <ProductObjType>(
Product.product,
arguments: new QueryArguments(
new QueryArgument <IdGraphType> {
Name = "id"
}
),
resolve: context =>
{
var id = context.GetArgument <int>("id");
if (database == Db.Mongo)
{
MongoHandler mongo = new MongoHandler();
return(mongo.GetProductById(id));
}
else
{
MySqlHandler mySql = new MySqlHandler();
return(mySql.GetProductById(id));
}
}
);
Field <ListGraphType <ProductObjType> >(
"products",
arguments: new QueryArguments(
new QueryArgument <IdGraphType> {
Name = "lower"
},
new QueryArgument <IdGraphType> {
Name = "upper"
}
),
resolve: context =>
{
var lower = context.GetArgument <int>("lower");
var upper = context.GetArgument <int>("upper");
if (database == Db.Mongo)
{
MongoHandler mongo = new MongoHandler();
return(mongo.GetProductById(lower, upper));
}
else
{
MySqlHandler mySql = new BachelorProjectBackend.Repository.MySqlHandler();
return(mySql.GetProductById(lower, upper));
}
});
Field <ProductObjType>(
"productAndType",
arguments: new QueryArguments(
new QueryArgument <IdGraphType> {
Name = "id"
}
),
resolve: context =>
{
var id = context.GetArgument <int>("id");
if (database == Db.Mongo)
{
MongoHandler mongo = new MongoHandler();
return(mongo.GetProductJoinProductTypeById(id));
}
else
{
MySqlHandler mySql = new MySqlHandler();
return(mySql.GetProductJoinProductTypeById(id));
}
}
);
Field <ListGraphType <ProductObjType> >(
"productsAndTypes",
arguments: new QueryArguments(
new QueryArgument <IdGraphType> {
Name = "lower"
},
new QueryArgument <IdGraphType> {
Name = "upper"
}
),
resolve: context =>
{
var lower = context.GetArgument <int>("lower");
var upper = context.GetArgument <int>("upper");
if (database == Db.Mongo)
{
MongoHandler mongo = new MongoHandler();
return(mongo.GetProductJoinProductTypeById(lower, upper));
}
else
{
MySqlHandler mySql = new BachelorProjectBackend.Repository.MySqlHandler();
return(mySql.GetProductJoinProductTypeById(lower, upper));
}
});
Field <ProductObjType>(
"productAndTypeAndCompany",
arguments: new QueryArguments(
new QueryArgument <IdGraphType> {
Name = "id"
}
),
resolve: context =>
{
var id = context.GetArgument <int>("id");
if (database == Db.Mongo)
{
MongoHandler mongo = new MongoHandler();
return(mongo.GetProductJoinProductTypeAndCompanyById(id));
}
else
{
MySqlHandler mySql = new BachelorProjectBackend.Repository.MySqlHandler();
return(mySql.GetProductJoinProductTypeAndCompanyById(id));
}
});
Field <ListGraphType <ProductObjType> >(
"productsAndTypesAndCompanys",
arguments: new QueryArguments(
new QueryArgument <IdGraphType> {
Name = "lower"
},
new QueryArgument <IdGraphType> {
Name = "upper"
}
),
resolve: context =>
{
var lower = context.GetArgument <int>("lower");
var upper = context.GetArgument <int>("upper");
if (database == Db.Mongo)
{
MongoHandler mongo = new MongoHandler();
return(mongo.GetProductJoinProductTypeAndCompanyById(lower, upper));
}
else
{
MySqlHandler mySql = new BachelorProjectBackend.Repository.MySqlHandler();
return(mySql.GetProductJoinProductTypeAndCompanyById(lower, upper));
}
});
Field <CompanyObjType>(
Company.company,
resolve: context =>
{
if (database == Db.Mongo)
{
return(null);
}
else
{
return(null);
}
});
Field <DepartmentObjType>(
Department.department,
resolve: context =>
{
if (database == Db.Mongo)
{
return(null);
}
else
{
return(null);
}
});
Field <PersonObjType>(
Person.person,
resolve: context =>
{
if (database == Db.Mongo)
{
return(null);
}
else
{
return(null);
}
});
Field <ProductTypeObjType>(
ProductType.productType,
arguments: new QueryArguments(
new QueryArgument <IdGraphType> {
Name = "id"
}
),
resolve: context =>
{
var id = context.GetArgument <int>("id");
if (database == Db.Mongo)
{
MongoHandler mongo = new MongoHandler();
return(mongo.GetProductJoinProductTypeById(id));
}
else
{
MySqlHandler mySql = new MySqlHandler();
return(mySql.GetProductJoinProductTypeById(id));
}
});
}
