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 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 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 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); } }