public static void TestINDEX()
{
//MySqlTemplate.DROP("Test1");
//CreateTableElement ct = new CreateTableElement("Test1");
//ct.Add("ID", DataType.INT, true);
//ct.Add("Name", DataType.CHAR10, true);
//ct.Add("Age", DataType.INT);
//ct.Create();
//for (int i = 0; i < 800; i++)
//{
// MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 1000 + i, "张三", 16 });
//}
//MySqlTemplate.INDEX("Test1", "ByID", "ID");
//MySqlTemplate.INDEX("Test1", "ByID", "", false);
List <Test2> list = MySqlTemplate.SELECT <Test2>(new string[] { "Test1" }, new string[] { "ID" }, "ID>1556");
foreach (var item in list)
{
Console.WriteLine("ID:{0} Name:{1} Age:{2}", item.ID, item.Name, item.Age);
}
Console.WriteLine();
}
public static void TestNULL()
{
MySqlTemplate.DROP("Test1");
CreateTableElement ct = new CreateTableElement("Test1");
ct.Add("ID", DataType.INT, true);
ct.Add("Name", DataType.VARCHAR40, true);
ct.Add("Age", DataType.INT);
ct.Create();
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 100, "张三", 16 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 101, "王五", null });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 102, "李四", null });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 103, "赵六", 55 });
//string cmd = "SELECT * FROM Test1 WHERE Age IS NULL;";
string cmd = "SELECT * FROM Test1 WHERE Age IS NOT NULL;";
DataSet obj = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, cmd, null);
List <Test2> list = MySqlTemplate.IList <Test2>(obj);
foreach (var item in list)
{
Console.WriteLine("ID:{0} Name:{1} Age:{2}", item.ID, item.Name, item.Age);
}
}
// 检测账号是否已占用
public int CheckUser(string accountNumber)
{
string where = string.Format("accountNumber = '{0}'", accountNumber);
var users = MySqlTemplate.SELECT <User>(new string[] { "user" }, new string[] { "*" }, where);
if (users != null)
{
return(0);
}
return(1001);
}
public User ReadUser(int userId)
{
string where = string.Format("userId = '{0}'", userId);
var users = MySqlTemplate.SELECT <User>(new string[] { "user" }, new string[] { "*" }, where);
foreach (var item in users)
{
return(item);
}
return(null);
}
public static void CreateUser()
{
MySqlTemplate.DROP("user");
CreateTableElement ct = new CreateTableElement("user");
ct.Add("userId", DataType.INT, true, true);
ct.Add("accountNumber", DataType.VARCHAR40, true, true);
ct.Add("password", DataType.VARCHAR40);
ct.Add("roleId", DataType.INT);
ct.Create();
}
// 读取用户
public User ReadUser(string accountNumber)
{
string where = string.Format("accountNumber = '{0}'", accountNumber);
var users = MySqlTemplate.SELECT <User>(new string[] { "user" }, new string[] { "*" }, where);
foreach (var item in users)
{
return(item);
}
return(null);
}
public static void CreateSetting()
{
MySqlTemplate.DROP("setting");
CreateTableElement ct = new CreateTableElement("setting");
ct.Add("nextUserId", DataType.INT, true);
ct.Add("nextRoleId", DataType.INT, true);
ct.Add("levelUpAddAttributePoint", DataType.INT, true);
ct.Create();
MySqlTemplate.INSERT("setting", new string[] { "nextUserId", "nextRoleId", "levelUpAddAttributePoint" }, new object[] { 1000, 1000, 5 });
}
public bool RoleIsExisting(string roleName)
{
string where = string.Format("roleName = '{0}'", roleName);
var roles = MySqlTemplate.SELECT <Entity>(new string[] { "role" }, new string[] { "*" }, where);
if (roles.Count < 1)
{
return(false);
}
return(roles[0] != null);
}
// 检测密码正确与否
public int CheckUserPassword(string accountNumber, string password)
{
string where = string.Format("accountNumber = '{0}'", accountNumber);
var users = MySqlTemplate.SELECT <User>(new string[] { "user" }, new string[] { "*" }, where);
foreach (var item in users)
{
if (item.password == password)
{
return(0);
}
}
return(1002);
}
public static void TestALTER()
{
MySqlTemplate.DROP("Test1");
CreateTableElement ct = new CreateTableElement("Test1");
ct.Add("ID", DataType.INT, true);
ct.Add("Name", DataType.CHAR10, true);
ct.Add("Age", DataType.INT);
ct.Create();
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 100, "张三", 16 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 101, "王五", 17 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 102, "李四", 18 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 103, "赵六", 55 });
MySqlTemplate.ALTER("Test1", "Age", AlterType.DROP);
string cmd = "SELECT * FROM Test1";
DataSet obj = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, cmd, null);
List <Test2> list = MySqlTemplate.IList <Test2>(obj);
foreach (var item in list)
{
Console.WriteLine("ID:{0} Name:{1} Age:{2}", item.ID, item.Name, item.Age);
}
Console.WriteLine();
MySqlTemplate.ALTER("Test1", "Gold", AlterType.ADD, DataType.INT, "ID");
MySqlTemplate.UPDATE("Test1", new string[] { "Gold" }, new object[] { 2000 });
cmd = "SELECT * FROM Test1";
obj = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, cmd, null);
list = MySqlTemplate.IList <Test2>(obj);
foreach (var item in list)
{
Console.WriteLine("ID:{0} Name:{1} Gold:{2}", item.ID, item.Name, item.Gold);
}
Console.WriteLine();
MySqlTemplate.ALTER("Test1", "Name", AlterType.MODIFY, DataType.VARCHAR40);
MySqlTemplate.ALTER("Test1", "Name", AlterType.CHANGE, DataType.VARCHAR40, "Nickname");
MySqlTemplate.ALTER("Test1", "MyTest", AlterType.RENAME);
}
public static void TestJOIN()
{
MySqlTemplate.DROP("Test1");
CreateTableElement ct = new CreateTableElement("Test1");
ct.Add("ID", DataType.INT, true);
ct.Add("Name", DataType.VARCHAR40, true);
ct.Add("Age", DataType.INT);
ct.Create();
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 100, "张三", 16 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 101, "王五", 26 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 102, "李四", 18 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 103, "赵六", 55 });
MySqlTemplate.DROP("Test2");
ct.Clear();
ct = new CreateTableElement("Test2");
ct.Add("Name", DataType.VARCHAR40, true);
ct.Add("Level", DataType.INT);
ct.Create();
MySqlTemplate.INSERT("Test2", new string[] { "Name", "Level" }, new object[] { "张三", 6 });
MySqlTemplate.INSERT("Test2", new string[] { "Name", "Level" }, new object[] { "王五", 7 });
MySqlTemplate.INSERT("Test2", new string[] { "Name", "Level" }, new object[] { "李四", 12 });
MySqlTemplate.INSERT("Test2", new string[] { "Name", "Level" }, new object[] { "xx", 99 });
// 普通 join
//string cmd = "SELECT a.Name, a.Age, b.Level FROM Test1 a INNER JOIN Test2 b ON a.Name = b.Name";
// LEFT join
//string cmd = "SELECT a.Name, a.Age, b.Level FROM Test1 a LEFT JOIN Test2 b ON a.Name = b.Name";
// RIGHT join
string cmd = "SELECT a.Name, a.Age, b.Level FROM Test1 a RIGHT JOIN Test2 b ON a.Name = b.Name";
DataSet obj = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, cmd, null);
List <Test2> list = MySqlTemplate.IList <Test2>(obj);
foreach (var item in list)
{
Console.WriteLine("Name:{0} Age:{1} Level:{2}", item.Name, item.Age, item.Level);
}
}
public static void CreateRole()
{
MySqlTemplate.DROP("role");
CreateTableElement ct = new CreateTableElement("role");
ct.Add("roleId", DataType.INT, true, true);
ct.Add("roleName", DataType.VARCHAR40);
ct.Add("level", DataType.INT);
ct.Add("exp", DataType.LONG);
ct.Add("fixedSTR", DataType.FLOAT);
ct.Add("fixedDEX", DataType.FLOAT);
ct.Add("fixedMAG", DataType.FLOAT);
ct.Add("fixedCON", DataType.FLOAT);
ct.Add("potentialSTR", DataType.FLOAT);
ct.Add("potentialDEX", DataType.FLOAT);
ct.Add("potentialMAG", DataType.FLOAT);
ct.Add("potentialCON", DataType.FLOAT);
ct.Create();
}
// 读取角色
public Role ReadRole(int roleId)
{
Role role;
if (!roleDic.TryGetValue(roleId, out role))
{
string where = string.Format("roleId = {0}", roleId);
var roles = MySqlTemplate.SELECT <Role>(new string[] { "role" }, new string[] { "*" }, where);
if (roles.Count > 0)
{
role = roles[0];
}
if (role != null)
{
roleDic[role.roleId] = role;
}
}
return(role);
}
// 添加角色
public int AddNewRole(int userId, Role role)
{
User user = DataManager.Instance.ReadUser(userId);
if (user != null)
{
role.roleId = ++settings.nextRoleId;
MySqlTemplate.UPDATE("user", new string[] { "roleId" }, new object[] { role.roleId });
MySqlTemplate.UPDATE("setting", new string[] { "nextRoleId" }, new object[] { settings.nextRoleId });
MySqlTemplate.INSERT("role", new string[] { "roleId", "roleName", "level", "exp", "fixedSTR", "fixedDEX", "fixedMAG", "fixedCON",
"potentialSTR", "potentialDEX", "potentialMAG", "potentialCON" }, new object[] {
role.roleId, role.roleName, role.level, role.exp, role.fixedSTR,
role.fixedDEX, role.fixedMAG, role.fixedCON, role.potentialSTR, role.potentialDEX,
role.potentialMAG, role.potentialCON
});
return(0);
}
return(1);
}
// 添加用户
public int AddNewUser(string accountNumber, string password)
{
try
{
User user = new User();
user.userId = ++settings.nextUserId;
user.accountNumber = accountNumber;
user.password = password;
MySqlTemplate.INSERT("user", new string[] { "userid", "accountNumber", "password" },
new string[] { settings.nextUserId.ToString(), accountNumber, password });
MySqlTemplate.UPDATE("setting", new string[] { "nextUserId" }, new object[] { settings.nextUserId });
MyLog.Log("add new user " + settings.nextUserId);
return(0);
}
catch (Exception e)
{
MyLog.Error(e.Message + e.TargetSite);
return(1000);
}
}
private string GetElementCmd()
{
string element = "";
string primark = " PRIMARY KEY ( `RowId`";
foreach (Element e in elementList)
{
element += string.Format(" `{0}`", e.keyName);
element += MySqlTemplate.GetDataTypeCmd(e.dataType);
if (e.isNotNull)
{
element += " NOT NULL";
}
element += ",";
if (e.isPrimarkKey)
{
primark += string.Format(", `{0}`", e.keyName);
}
}
primark += "))ENGINE=InnoDB" + MySqlTemplate.GetEncodingCmd(encoding);
return(element + primark);
}
public static List <T> SELECT <T>(string[] tableNames, string[] keys, string where = "", bool asc = true, string[] sortKeys = null)
{
DataSet data = SELECT(tableNames, keys, where, asc, sortKeys);
return(MySqlTemplate.IList <T>(data));
}
// 读取设定信息
private void ReadSettings()
{
List <Settings> list = MySqlTemplate.SELECT <Settings>(new string[] { "setting" }, new string[] { "*" });
settings = list[0];
}