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