public async Task <Question> Create(Question question)
{
var con = await MysqlConnector.GetConnection();
var command = new MySqlCommand("INSERT INTO `questions`(`title`,`message`,`asker`) VALUES (@title, @message, @asker)", con);
if (command == null)
{
throw new Exception();
}
command.Parameters.AddWithValue("@title", question.Title);
command.Parameters.AddWithValue("@message", question.Message);
command.Parameters.AddWithValue("@asker", question.CreatedBy.Id);
var result = await command.ExecuteNonQueryAsync();
if (result < 1)
{
throw new Exception("failed to create question");
}
var createdQestion = await this.Find(command.LastInsertedId);
if (createdQestion == null)
{
throw new Exception("could not retrieve created queston");
}
return(createdQestion);
}
public async Task <User?> Find(long id)
{
var con = await MysqlConnector.GetConnection();
var command = new MySqlCommand("SELECT id, username, password, role FROM users WHERE id = @id LIMIT 0, 1", con);
if (command == null)
{
throw new Exception("");
}
command.Parameters.AddWithValue("@id", id);
var result = await command.ExecuteReaderAsync();
if (result == null)
{
throw new Exception("");
}
if (!await result.ReadAsync())
{
return(null);
}
return(new User(
result.GetInt64("id"),
result.GetString("username"),
result.GetString("password"),
Role.Parse <Role>(result.GetString("role"))
));
}
public void GetAuditLogsAsync_verifyonlyOne()
{
var mysqlConnector = new MysqlConnector();
var auditRepo = new AuditRepository(mysqlConnector);
var userId = 10;
var fromDate = DateTime.Today.AddDays(-1);
var toDate = DateTime.Today.AddDays(1);
var auditPayloadFake = Builder <AuditEventPayload> .CreateNew().Build();
var auditEventFake = Builder <AuditEvent> .CreateNew().With(r => r.EventPayload = auditPayloadFake)
.With(r => r.UserId = userId)
.With(r => r.EventTime = DateTime.Today)
.Build();
//Delete all record with same user id
var deleteresult = auditRepo.DeleteByUserIdAsync(userId).Result;
//Add fake record
var addresult = auditRepo.AddAsync(auditEventFake).Result;
//Get record with same UserId and Dates and it must be that one only
var onlyRecord = auditRepo.GetAuditLogsAsync(userId, fromDate, toDate).Result;
Assert.IsNotNull(onlyRecord);
Assert.IsTrue(onlyRecord.Count == 1);
Assert.IsNotNull(onlyRecord[0]);
Assert.AreEqual(auditEventFake.EventName, onlyRecord[0].EventName);
Assert.AreEqual(auditEventFake.UserId, onlyRecord[0].UserId);
Assert.AreEqual(auditEventFake.EventTime, onlyRecord[0].EventTime);
//TODO: More Validation
}
public IHttpActionResult Songster(Search search)
{
//if (Session["Uid"] == null)
//{
// return RedirectToAction("Index", "Login");
//}
MysqlConnector conn = new MysqlConnector();
var data = conn.GetData(search.ownerName, search.songName);
var d = data;
//foreach (DataRow item in d.Rows)
//{
// string name = item["SongName"].ToString();
// }
// List<GetStudentSearchModel> dtList = data.AsEnumerable()
//.Select(row => new GetStudentSearchModel
//{
// Id = row["Id"].ToString()
//}).ToList();
return(Ok(d));
}
static public int UpdatePassWord(string userName, string OldpassWord, string NewPassWord)
{
if (VerifyLogin(userName, OldpassWord) != 3)
{
return(0);
}
MysqlConnector mySql = new MysqlConnector();
mySql.SetServer(AESManager.AESDecrypt(AppVars.dbInfo.ipUrl, AppVars.AppParas.Enc0));
mySql.SetDataBase(AESManager.AESDecrypt(AppVars.dbInfo.dataName, AppVars.AppParas.Enc1));
mySql.SetUserID(AESManager.AESDecrypt(AppVars.dbInfo.userName, AppVars.AppParas.Enc2));
mySql.SetPassword(AESManager.AESDecrypt(AppVars.dbInfo.passWord, AppVars.AppParas.Enc3));
mySql.SetPort(AppVars.dbInfo.portNum.ToString());
mySql.SetCharset("utf-8");
string pwd = NewPassWord, salt = AppVars.AppParas.salt;
try
{
mySql.ExeUpdate(string.Format("update {0} set password = '******' where uid = {2}",
"user", MD5Manager.HashString(MD5Manager.HashString(NewPassWord) + salt), AppVars.AppParas.uid));
}
catch (Exception ex)
{
return(0);
}
AppVars.AppParas.isLogin = false;
return(2);
}
public void Play()
{
UserEmail = EmailText.text;
//C: \Users\hasee\AppData\LocalLow\DefaultCompany\mitobunny0707\Unity
path = Application.persistentDataPath + "/Unity/" + UserEmail + ".json";
if (!Validation.Validation.LocalEmailExistCheck(UserEmail))
{
if (!Validation.Validation.ServerEmailExistCheck(UserEmail))
{
AlertText.text = "The enter email has not been registered. Please register first.";
AlertWindow.SetActive(true);
return;
}
else
{
MysqlConnector mysqlConnector = new MysqlConnector();
string query = "select * from runoob.player_info where email = \'" + UserEmail + "\';";
List<string> result = mysqlConnector.Query(query)[0];
savePlayerInfoWithListString(result);
}
}
LoadingControl.GameStart();
}
static public int GetDatabaseContent()
{
MysqlConnector mySql = new MysqlConnector();
MySqlDataReader reader;
mySql.SetServer(AESManager.AESDecrypt(AppVars.dbInfo.ipUrl, AppVars.AppParas.Enc0));
mySql.SetDataBase(AESManager.AESDecrypt(AppVars.dbInfo.dataName, AppVars.AppParas.Enc1));
mySql.SetUserID(AESManager.AESDecrypt(AppVars.dbInfo.userName, AppVars.AppParas.Enc2));
mySql.SetPassword(AESManager.AESDecrypt(AppVars.dbInfo.passWord, AppVars.AppParas.Enc3));
mySql.SetPort(AppVars.dbInfo.portNum.ToString());
mySql.SetCharset("utf-8");
try
{
reader = mySql.ExeQuery(string.Format("select * from {0}", "vehicle_info"));
while (reader.Read())
{
AppVars.DataStruct tempData;
AppVars.initalDataCnt++;
tempData.ID = Int32.Parse(reader.GetValue(0).ToString());
tempData.Name = reader.GetValue(1).ToString();
tempData.NickName = reader.GetValue(2).ToString();
tempData.Type = Int32.Parse(reader.GetValue(3).ToString());
tempData.Count = Int32.Parse(reader.GetValue(4).ToString());
tempData.Cap = Int32.Parse(reader.GetValue(5).ToString());
tempData.Date1 = Int32.Parse(reader.GetValue(6).ToString());
tempData.Date2 = Int32.Parse(reader.GetValue(7).ToString());
tempData.Date3 = Int32.Parse(reader.GetValue(8).ToString());
tempData.Date4 = Int32.Parse(reader.GetValue(9).ToString());
tempData.Lenght = Int32.Parse(reader.GetValue(10).ToString());
tempData.Weight = Int32.Parse(reader.GetValue(11).ToString());
tempData.DescribeText = reader.GetValue(12).ToString();
String tempImageData = reader.GetValue(13).ToString();
tempData.ImageCount = tempImageData.Split(';').Length;
tempData.ImageUrl = new List <string>();
tempData.ImageUrl = tempImageData.Split(';').ToList();
tempData.Manu = reader.GetValue(14).ToString();
tempData.Runner = reader.GetValue(15).ToString();
String tempImageDescibeData = reader.GetValue(16).ToString();
tempData.ImageDescribe = new List <string>();
tempData.ImageDescribe = tempImageDescibeData.Split(';').ToList();
tempData.Speed = Int32.Parse(reader.GetValue(17).ToString());
tempData.AttachmentNum = 0;
tempData.Attachment = new List <string>();
tempData.AttachDescibe = new List <string>();
tempData.AttachImageCnt = 0;
tempData.AttachImage = new List <string>();
tempData.AttachNumber = new List <string>();
AppVars.initalData.Add(tempData);
}
}
catch (Exception ex)
{
return(0);
}
return(1);
}
public FormMain()
{
InitDBconf();
InitializeComponent();
globalDB = GlobalVar.globalDB;
// GlobalVar.UpdateDBList();
questForm = new FormQuestView();
tagForm = new FormTag();
}
void Start()
{
MysqlConnector mysqlConnector = new MysqlConnector();
mysqlConnector.SetServer("127.0.0.1");
mysqlConnector.SetPort("3306");
mysqlConnector.SetUserID("root");
mysqlConnector.SetPassword("root");
mysqlConnector.SetDataBase("databasehomework");
mysqlConnector.SetCharset("utf8");
SqlAccess sqla = new SqlAccess();
}
public async Task <Question?> FindComplete(long id)
{
var con = await MysqlConnector.GetConnection();
var command = new MySqlCommand(
"SELECT `id`, `title`, `message`, `asker` FROM `questions` WHERE `id` = @id LIMIT 0, 1",
con
);
if (command == null)
{
throw new Exception("");
}
command.Parameters.AddWithValue("@id", id);
var result = await command.ExecuteReaderAsync();
if (result == null)
{
throw new Exception("");
}
if (!await result.ReadAsync())
{
return(null);
}
// Yeah, this could've been an inner join, but let's keep things simple. normally you would have an ORM here too.
var user = await UserRepository.Find(result.GetInt64("asker"));
if (user == null)
{
throw new Exception("Question creator not found");
}
var question = new Question(
result.GetInt64("id"),
result.GetString("title"),
result.GetString("message"),
user
);
var answers = await AnswerRepository.ByQuestion(question.Id);
question.Answers = answers;
return(question);
}
//// Save and load the information of users in a jason file.
public void Save()
{
string Sex = sexchoice[0];
if (!ifMale)
{
Sex = sexchoice[1];
}
Player_Info _data = new Player_Info()
{
Email = Email,
FirstName = FirstName,
FamilyName = FamilyName,
Age = Age,
Sex = Sex,
City = City,
Country = Country,
Institution = Institution,
Score = Score,
Level = Level
};
string playerJson = JsonConvert.SerializeObject(_data, Formatting.Indented);
string UserPath = Application.persistentDataPath + "/Unity/" + Email + ".json";
//save the player information to the local Json file
try
{
File.WriteAllText(UserPath, playerJson);
AlertWindow2.SetActive(true);
}
catch (Exception ex)
{
AlertText.text = ex.Message;
AlertWindow.SetActive(true);
}
//save the player information to the database
string query = @"insert into runoob.player_info
(age, city, country, email, familyname, firstname, institute, level, score, sex, password, carrots) values
(" + Age + ", \"" + City + "\", '" + Country + "', '" + Email + "', '" + FamilyName + "', '" + FirstName + "', '"
+ Institution + "', " + Level + ", " + Score + ", '" + Sex + "', '" + _data.password + "', " +
_data.carrots + ")";
Debug.Log(query);
MysqlConnector mysqlConnector = new MysqlConnector();
mysqlConnector.Query(query);
}
public async Task <List <Answer> > ByQuestion(long questionId)
{
var con = await MysqlConnector.GetConnection();
var command = new MySqlCommand(
"SELECT `answers`.`id` as answerId, `message`, `users`.`id` authorId, `users`.`username` as userName, `users`.`role` as userRole "
+ "FROM `answers` "
+ "INNER JOIN `users` ON `answers`.`author` = `users`.`id` "
+ "WHERE `answers`.`question` = @id",
con
);
if (command == null)
{
throw new Exception("");
}
command.Parameters.AddWithValue("@id", questionId);
var result = await command.ExecuteReaderAsync();
if (result == null)
{
throw new Exception("");
}
var answers = new List <Answer>();
while (await result.ReadAsync())
{
var author = new User(
result.GetInt64("authorId"),
result.GetString("userName"),
"",
Role.Parse <Role>(result.GetString("userRole"))
);
var answer = new Answer(
result.GetInt64("answerId"),
author,
result.GetString("message")
);
answers.Add(answer);
}
return(answers);
}
public async Task TestQuery()
{
MysqlConnector connector = new MysqlConnector(new IPEndPoint(IPAddress.Parse("192.168.0.243"), 3306), "root", "123456");
try
{
await connector.ConnectAsync();
var result = connector.Query("SHOW VARIABLES LIKE '%char%';");
result = connector.Query("SELECT * FROM test.food");
var ok = connector.Update("UPDATE test.food SET Name = '123测试123小龙虾' WHERE Id = '69c09f4e-2f69-4dda-8820-11edbf6ab28a';");
}
finally
{
connector.Quit();
await connector.DisconnectAsync();
}
}
static public int VerifyLogin(string userName, string passWord)
{
MysqlConnector mySql = new MysqlConnector();
MySqlDataReader reader;
mySql.SetServer(AESManager.AESDecrypt(AppVars.dbInfo.ipUrl, AppVars.AppParas.Enc0));
mySql.SetDataBase(AESManager.AESDecrypt(AppVars.dbInfo.dataName, AppVars.AppParas.Enc1));
mySql.SetUserID(AESManager.AESDecrypt(AppVars.dbInfo.userName, AppVars.AppParas.Enc2));
mySql.SetPassword(AESManager.AESDecrypt(AppVars.dbInfo.passWord, AppVars.AppParas.Enc3));
mySql.SetPort(AppVars.dbInfo.portNum.ToString());
mySql.SetCharset("utf-8");
string pwd = "", salt = AppVars.AppParas.salt;
int uid = 0, type = 0;
try
{
reader = mySql.ExeQuery(string.Format("select * from {0} where username = '******'", "user", userName));
while (reader.Read())
{
uid = Int32.Parse(reader.GetValue(0).ToString());
pwd = reader.GetValue(1).ToString();
salt = reader.GetValue(2).ToString();
type = Int32.Parse(reader.GetValue(3).ToString());
}
}
catch (Exception ex)
{
return(0);
}
if (pwd == "" || salt == "")
{
return(1);
}
if (MD5Manager.HashString(MD5Manager.HashString(passWord) + salt) == pwd)
{
AppVars.AppParas.isAdmin = type == 0 ? true : false;
AppVars.AppParas.isLogin = true;
AppVars.AppParas.uid = uid;
AppVars.AppParas.UserName = userName;
return(2);
}
return(3);
}
public async Task <List <Question> > Get()
{
var con = await MysqlConnector.GetConnection();
var command = new MySqlCommand(
"SELECT `id`, `title`, `message`, `asker` FROM `questions`",
con
);
if (command == null)
{
throw new Exception("");
}
var result = await command.ExecuteReaderAsync();
if (result == null)
{
throw new Exception("");
}
List <Question> questions = new List <Question>();
while (await result.ReadAsync())
{
// Yeah, this could've been an inner join, but let's keep things simple. normally you would have an ORM here too.
var user = await UserRepository.Find(result.GetInt64("asker"));
if (user == null)
{
throw new Exception("Question creator not found");
}
questions.Add(new Question(
result.GetInt64("id"),
result.GetString("title"),
result.GetString("message"),
user
));
}
return(questions);
}
public static void Get(JObject data, string host, int port)
{
var channel = data["channel"].ToString();
var messages = new MysqlConnector().GetMessagesForCategory(channel);
string output = string.Empty;
foreach (var message in messages)
{
var replacedMessage = ContentEnchanter.EnchantMessage(message.Message);
output += $"<div class=\"incoming_msg\"><div class=\"received_msg\"><div class=\"received_withd_msg\"><p>{replacedMessage} - {message.Author} - {message.Timestamp}</p></div></div></div><br/>";
}
var chanelsToSend = new Dictionary <string, string>();
chanelsToSend.Add("action", "getMessages");
chanelsToSend.Add("value", Newtonsoft.Json.JsonConvert.SerializeObject(output));
var channelsJson = Newtonsoft.Json.JsonConvert.SerializeObject(chanelsToSend);
SendData(channelsJson, host, port);
}
public static Boolean ServerEmailExistCheck(string email)
{
MysqlConnector mysqlConnector = new MysqlConnector();
string query = "select count(Email) from runoob.player_info where Email = \"" + email + "\"";
List <List <string> > result = mysqlConnector.Query(query);
if (result.Count > 0)
{
if (result[0][0] == "0")
{
return(false);
}
return(true);
}
else
{
return(false);
}
}
public void AddAsync_VerifyLastInsert()
{
var mysqlConnector = new MysqlConnector();
var auditRepo = new AuditRepository(mysqlConnector);
//Build fake
var auditPayloadFake = Builder <AuditEventPayload> .CreateNew().Build();
var auditEventFake = Builder <AuditEvent> .CreateNew().With(r => r.EventPayload = auditPayloadFake).Build();
//Add Record
var addresult = auditRepo.AddAsync(auditEventFake).Result;
//Get Last Record
var lastRecord = auditRepo.GetLastRecordAsync().Result;
//Verify not null
Assert.IsNotNull(lastRecord);
Assert.AreEqual(auditEventFake.EventName, lastRecord.EventName);
//TODO: More Validation
}
public async Task Create(long questionId, Answer answer)
{
var con = await MysqlConnector.GetConnection();
var command = new MySqlCommand("INSERT INTO `answers`(`author`, `question`, `message`) VALUES (@author, @question, @message)", con);
if (command == null)
{
throw new Exception();
}
command.Parameters.AddWithValue("@author", answer.User.Id);
command.Parameters.AddWithValue("@question", questionId);
command.Parameters.AddWithValue("@message", answer.Message);
var result = await command.ExecuteNonQueryAsync();
if (result < 1)
{
throw new Exception("failed to create answer");
}
}
public async Task SetCorrectAnswer(long questionId, long answerId)
{
var con = await MysqlConnector.GetConnection();
var command = new MySqlCommand(
"UPDATE `questions` SET correctAnswer = @answer WHERE `id` = @id",
con
);
if (command == null)
{
throw new Exception("");
}
command.Parameters.AddWithValue("@answer", answerId);
command.Parameters.AddWithValue("@id", questionId);
var result = await command.ExecuteNonQueryAsync();
if (result < 1)
{
throw new Exception("Failed to set correct answer");
}
}
static public int RegNewUser(string userName, string passWord, string Email)
{
int VerifyExist = VerifyLogin(userName, passWord);
if (VerifyExist == 0)
{
return(0);
}
else if (VerifyExist != 1)
{
return(1);
}
MysqlConnector mySql = new MysqlConnector();
mySql.SetServer(AESManager.AESDecrypt(AppVars.dbInfo.ipUrl, AppVars.AppParas.Enc0));
mySql.SetDataBase(AESManager.AESDecrypt(AppVars.dbInfo.dataName, AppVars.AppParas.Enc1));
mySql.SetUserID(AESManager.AESDecrypt(AppVars.dbInfo.userName, AppVars.AppParas.Enc2));
mySql.SetPassword(AESManager.AESDecrypt(AppVars.dbInfo.passWord, AppVars.AppParas.Enc3));
mySql.SetPort(AppVars.dbInfo.portNum.ToString());
mySql.SetCharset("utf-8");
string pwd = passWord, salt = AppVars.AppParas.salt;
try
{
mySql.ExeUpdate(string.Format("insert into {0}({1},{2},{3}) values('{4}','{5}',{6},'{7}')",
"user", "username", "password", "type", userName, MD5Manager.HashString(MD5Manager.HashString(passWord) + salt), 1, Email));
}
catch (Exception ex)
{
return(0);
}
if (VerifyLogin(userName, passWord) == 3)
{
return(2);
}
return(0);
}
static public int GetAttachmentData()
{
MysqlConnector mySql = new MysqlConnector();
MySqlDataReader reader;
mySql.SetServer(AESManager.AESDecrypt(AppVars.dbInfo.ipUrl, AppVars.AppParas.Enc0));
mySql.SetDataBase(AESManager.AESDecrypt(AppVars.dbInfo.dataName, AppVars.AppParas.Enc1));
mySql.SetUserID(AESManager.AESDecrypt(AppVars.dbInfo.userName, AppVars.AppParas.Enc2));
mySql.SetPassword(AESManager.AESDecrypt(AppVars.dbInfo.passWord, AppVars.AppParas.Enc3));
mySql.SetPort(AppVars.dbInfo.portNum.ToString());
mySql.SetCharset("utf-8");
try
{
for (int i = 0; i < AppVars.initalData.Count(); i++)
{
AppVars.DataStruct tempData;
tempData = AppVars.initalData[i];
reader = mySql.ExeQuery(string.Format("select * from {0} where id = {1}", "vehicle_attach", tempData.ID.ToString()));
while (reader.Read())
{
tempData.AttachmentNum = (reader.GetValue(1).ToString()).Split(';').Length;
tempData.Attachment = (reader.GetValue(1).ToString()).Split(';').ToList();
tempData.AttachDescibe = (reader.GetValue(2).ToString()).Split(';').ToList();
tempData.AttachImageCnt = (reader.GetValue(3).ToString()).Split(';').Length;
tempData.AttachImage = (reader.GetValue(3).ToString()).Split(';').ToList();
tempData.AttachNumber = (reader.GetValue(4).ToString()).Split(';').ToList();
AppVars.initalData[i] = tempData;
}
}
}
catch (Exception ex)
{
return(0);
}
return(1);
}
static void Main(string[] args)
{
#region Sql Database
SqlConnector sqlconn = new SqlConnector();
Console.WriteLine("Fetching Data from Sql Database.......");
List <QuarticEmployee> SqlOutput = sqlconn.FetchDataFromSqlServer();
var table = new ConsoleTable("Name", "Role", "Location", "Technology");
table.AddRow(SqlOutput[0].Name, SqlOutput[0].Role_Assigned, SqlOutput[0].Location, SqlOutput[0].Tech_Field);
Console.Write(table);
#endregion
#region Mysql DataBase
MysqlConnector mysqlconn = new MysqlConnector();
Console.WriteLine("Fetching Data from MySql Database.......");
List <QuarticEmployee> MysqlOutput = mysqlconn.FetchDataFromMysql();
var table1 = new ConsoleTable("Name", "Role", "Location", "Technology");
table1.AddRow(MysqlOutput[0].Name, MysqlOutput[0].Role_Assigned, MysqlOutput[0].Location, MysqlOutput[0].Tech_Field);
Console.Write(table1);
#endregion
//#region Oracle DataBase
//OracleConnector oracleconn = new OracleConnector();
//Console.WriteLine("Fetching Data from Oracle Database.......");
//List<QuarticEmployee> OracleOutput = oracleconn.FetchDataFromOracle();
//var table2 = new ConsoleTable("Name", "Role", "Location", "Technology");
//table2.AddRow(OracleOutput[0].Name, OracleOutput[0].Role_Assigned, OracleOutput[0].Location, OracleOutput[0].Tech_Field);
//Console.Write(table2);
//#endregion
}
public static int CalculateHealth(int exp, float health_mod, int npcClass, int level, MysqlConnector con)
{
int basehp = con.GetBaseHP(level, npcClass, exp);
return(CalculateImpl(health_mod, basehp));
}
public static int CalculateMana(float mana_mod, int npcClass, int level, MysqlConnector con)
{
int basemana = con.GetBaseMana(level, npcClass);
return(CalculateImpl(mana_mod, basemana));
}
public static int CalculateArmor(float armor_mod, int npcClass, int level, MysqlConnector con)
{
int basearmor = con.GetBaseArmor(level, npcClass);
return(CalculateImpl(armor_mod, basearmor));
}
public static int CalculateArmor(float armor_mod, int npcClass, int level, MysqlConnector con)
{
int basearmor = con.GetBaseArmor(level, npcClass);
return CalculateImpl(armor_mod, basearmor);
}
public static int CalculateMana(float mana_mod, int npcClass, int level, MysqlConnector con)
{
int basemana = con.GetBaseMana(level, npcClass);
return CalculateImpl(mana_mod, basemana);
}
public static int CalculateHealth(int exp, float health_mod, int npcClass, int level, MysqlConnector con)
{
int basehp = con.GetBaseHP(level, npcClass, exp);
return CalculateImpl(health_mod, basehp);
}
// private ArrayList tag_Group_List = new ArrayList();
public FormTag()
{
globalDB = GlobalVar.globalDB;
InitializeComponent();
}
public void Load(MysqlConnector conn)
{
DataTable data = conn.GetCreatureDataTable(Core);
this.DataSource = data;
}
public ApiController(MysqlConnector mysql, PostgresConnector postgres)
{
_mysql = mysql;
_postgres = postgres;
}
public Trinity(MysqlConnector connector)
{
this.connector = connector;
}
public void Load(MysqlConnector conn)
{
DataTable data = conn.GetCreatureDataTable(Core);
this.DataSource = data;
}
