{"id":135,"date":"2015-05-13T11:59:16","date_gmt":"2015-05-13T03:59:16","guid":{"rendered":"http:\/\/qxu1606620110.my3w.com\/?p=135"},"modified":"2017-11-10T19:18:18","modified_gmt":"2017-11-10T11:18:18","slug":"mysqlhelper-mysql%e6%95%b0%e6%8d%ae%e5%ba%93%e5%b0%81%e8%a3%85api","status":"publish","type":"post","link":"http:\/\/www.u3d8.com\/?p=135","title":{"rendered":"MySqlHelper\u2014MySql\u6570\u636e\u5e93\u5c01\u88c5API"},"content":{"rendered":"<p>\u8c03\u7528MySqlHelper\u5185\u5bb9\uff1a<\/p>\n<pre class=\"lang:c# decode:true  \" title=\"\u8c03\u7528MySqlHelper\u5185\u5bb9\">string[] col = new string [3] { \"id\", \"name\", \"age\" };\r\nstring[] colType = new string [3] { \"int(5) primary key\", \"varchar(8)\", \"int(5)\" };\r\nstring[] values = new string [3] {\"1\", \"'a'\", \"28\" };\r\n\/\/ Use this for initialization\r\nvoid Start () {\r\n\tMySqlHelper sql = new MySqlHelper ();\r\n\tsql.Create( \"tableTest1\", col, colType);\r\n\tsql.Insert( \"tableTest1\", values);\r\n\tsql.Update( \"tableTest1\", new string[] { \"age\" }, new string[] { \"18\" }, \"id\" , \"1\");\r\n\tsql.Delete( \"tableTest1\", new string[] { \"age\" }, new string[] { \"18\" });\r\n\t\/\/ \u83b7\u53d6\u67e5\u8be2\u7ed3\u679c\u4fdd\u5b58\u5230DataSet\u53d8\u91cf\r\n\tDataSet ds = sql.Select(\"tableTest1\" , \"age\", \"id = 1\");\r\n\tif (ds != null )\r\n\t{\r\n\t\t\/\/ \u521b\u5efa\u4e34\u65f6\u8868\u4fdd\u5b58\u67e5\u8be2\u7ed3\u679c\r\n\t\tDataTable table = ds.Tables[0];\r\n\t\t\/\/ \u904d\u5386\u67e5\u8be2\u7ed3\u679c \u5e76\u8f93\u51fa\r\n\t\tforeach (DataRow row in table.Rows)\r\n\t\t{\r\n\t\t\tforeach (DataColumn column in table.Columns)\r\n\t\t\t{\r\n\t\t\t\tDebug.Log(row[column]);\r\n\t\t\t}\r\n\t\t}\r\n\t}\r\n}\r\n<\/pre>\n<p>MySqlHelper\u5185\u5bb9\uff1a<\/p>\n<pre class=\"lang:c# decode:true \" title=\"MySqlHelper\u5185\u5bb9\">using UnityEngine; \r\nusing System; \r\nusing System.Data; \r\nusing System.Collections;  \r\nusing MySql.Data.MySqlClient;\r\nusing MySql.Data;\r\nusing System.IO;\r\npublic class MySqlHelper\r\n{\r\n \r\n    public static MySqlConnection dbConnection;\r\n                 \/\/\u5982\u679c\u53ea\u662f\u5728\u672c\u5730\u7684\u8bdd\uff0c\u5199localhost\u5c31\u53ef\u4ee5\u3002\r\n   \/\/ static string host = \"localhost\"; \r\n                 \/\/\u5982\u679c\u662f\u5c40\u57df\u7f51\uff0c\u90a3\u4e48\u5199\u4e0a\u672c\u673a\u7684\u5c40\u57df\u7f51IP\r\n    static string host = \"qdm16206948.my3w.com\" ;\r\n    static string id = \"qdm16206948\" ;\r\n    static string pwd = \"woaijia345\" ;\r\n    static string database = \"qdm16206948_db\" ;\r\n \r\n                 publicMySqlHelper()\r\n                {\r\n                               Initialize();\r\n                }\r\n \r\n    \/\/\/ &lt;summary&gt;\r\n    \/\/\/ \u65b0\u5efa\u5e76\u6253\u5f00\u8fde\u63a5\r\n    \/\/\/ &lt;\/summary&gt;\r\n                 public static void Initialize()\r\n                {\r\n                                 try {\r\n                                                 string connectionString = string .Format(\"Server = {0};port={4};Database = {1}; User ID = {2}; Password = {3};\",host,database,id,pwd, \"3306\");\r\n                                                dbConnection = new MySqlConnection (connectionString);\r\n                                                dbConnection.Open();\r\n                                }\r\n        catch (Exception e){\r\n            throw new Exception( \"\u670d\u52a1\u5668\u8fde\u63a5\u5931\u8d25\uff0c\u8bf7\u91cd\u65b0\u68c0\u67e5\u662f\u5426\u6253\u5f00MySql\u670d\u52a1\u3002\" + e.Message.ToString()); \r\n                                }\r\n                }\r\n    \/\/\/ &lt;summary&gt;\r\n    \/\/\/ \u521b\u5efa\u8868\r\n    \/\/\/ \u53c2\u6570\uff1a\u8868\u540d\u79f0\u3001\u5c5e\u6027\u5217\u3001 \u5c5e\u6027\u5217\u7c7b\u578b\r\n    \/\/\/ &lt;\/summary&gt;\r\n                 public DataSet Create (string _TableName, string[] _ColsName, string [] _ColsType)\r\n    {\r\n        if (_ColsName.Length != _ColsType.Length) {\r\n            throw new Exception ( \"columns.Length != colType.Length\" );\r\n        }\r\n        try{\r\n            string query = \"CREATE TABLE \" + _TableName + \" (\" + _ColsName[0] + \" \" + _ColsType[0];\r\n\r\n            for (int i = 1; i &lt; _ColsName.Length; ++i)\r\n            {\r\n                query += \", \" + _ColsName[i] + \" \" + _ColsType[i];\r\n            }\r\n            query += \")\";\r\n            Debug.Log(\"~~\u521b\u5efa\u6210\u529f~~\" );\r\n            return ExecuteQuery(query);\r\n        }\r\n        catch {\r\n            return null ;\r\n        }\r\n    }\r\n\r\n    \/\/\/ &lt;summary&gt;\r\n    \/\/\/ \u521b\u5efa\u8868 \u81ea\u52a8ID\r\n    \/\/\/ \u53c2\u6570\uff1a\u8868\u540d\u79f0\u3001\u5c5e\u6027\u5217\u3001 \u5c5e\u6027\u5217\u7c7b\u578b\r\n    \/\/\/ &lt;\/summary&gt;\r\n                 public DataSet CreateAutoID (string _TableName, string[] _ColsName, string [] _ColsType)\r\n    {\r\n        if (_ColsName.Length != _ColsType.Length) {\r\n            throw new Exception ( \"columns.Length != colType.Length\" );\r\n        }\r\n        try {\r\n            string query = \"CREATE TABLE \" + _TableName + \" (\" + _ColsName[0] + \" \" + _ColsType[0] + \" NOT NULL AUTO_INCREMENT\";\r\n            for (int i = 1; i &lt; _ColsName.Length; ++i)\r\n            {\r\n                query += \", \" + _ColsName[i] + \" \" + _ColsType[i];\r\n            }\r\n            query += \", PRIMARY KEY (\" + _ColsName[0] + \")\" + \")\";\r\n            Debug.Log(\"~~\u521b\u5efa\u6210\u529f~~\" );\r\n            return ExecuteQuery(query);\r\n        }\r\n        catch {\r\n            return null ;\r\n        }\r\n    }\r\n\r\n    \/\/\/ &lt;summary&gt;\r\n    \/\/\/ \u63d2\u5165\u6570\u636e\r\n    \/\/\/ \u53c2\u6570\uff1a\u8868\u540d\u79f0\u3001\u503c\r\n    \/\/\/ \u7279\u70b9\uff1a\u4e0d\u9002\u7528\u81ea\u52a8ID\r\n    \/\/\/ &lt;\/summary&gt;\r\n                 public DataSet Insert (string _TableName, string[] _Values)\r\n    {\r\n        try {\r\n            string query = \"INSERT INTO \" + _TableName + \" VALUES (\" + _Values[0];\r\n\r\n            for (int i = 1; i &lt; _Values.Length; ++i) {\r\n\r\n                query += \", \" + _Values[i];\r\n\r\n            }\r\n            query += \")\";\r\n            Debug.Log(\"~~\u6dfb\u52a0\u6210\u529f~~\" );\r\n            return ExecuteQuery(query);\r\n        }\r\n        catch {\r\n            Debug.Log(\"\u6dfb\u52a0\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\u9519\u8bef\u540e\u91cd\u65b0\u518d\u8bd5\" );\r\n            return null ;\r\n        }\r\n    }\r\n\r\n    \/\/\/ &lt;summary&gt;\r\n    \/\/\/ \u63d2\u5165\u6570\u636e\r\n    \/\/\/ \u53c2\u6570\uff1a\u8868\u540d\u79f0\u3001\u5c5e\u6027\u5217\u3001\u503c\r\n    \/\/\/ \u7279\u70b9\uff1a\u53ef\u9009\u62e9\u6027\u6dfb\u52a0\u503c\r\n    \/\/\/ &lt;\/summary&gt;\r\n                 public DataSet Insert (string _TableName, string[] _ColsName, string [] _Values)\r\n    {\r\n        try {\r\n            if (_ColsName.Length != _Values.Length) {\r\n\r\n                throw new Exception( \"columns.Length != colType.Length\" );\r\n\r\n            }\r\n            string query = \"INSERT INTO \" + _TableName + \" (\" + _ColsName[0];\r\n            for (int i = 1; i &lt; _ColsName.Length; ++i) {\r\n\r\n                query += \", \" + _ColsName[i];\r\n            }\r\n\r\n            query += \") VALUES (\" + _Values[0];\r\n            for (int i = 1; i &lt; _Values.Length; ++i) {\r\n                query += \", \" + _Values[i];\r\n            }\r\n            query += \")\";\r\n            Debug.Log(\"~~\u6dfb\u52a0\u6210\u529f~~\" );\r\n            return ExecuteQuery(query);\r\n        }\r\n        catch {\r\n            Debug.Log(\"\u6dfb\u52a0\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\u9519\u8bef\u540e\u91cd\u65b0\u518d\u8bd5\" );\r\n            return null ;\r\n        }\r\n    }\r\n\r\n    \/\/\/ &lt;summary&gt;\r\n    \/\/\/ \u67e5\u8be2\u6570\u636e\r\n    \/\/\/ \u53c2\u6570\uff1a\u8868\u540d\u79f0\u3001\u67e5\u8be2\u5185\u5bb9\r\n    \/\/\/ &lt;\/summary&gt;\r\n    public DataSet Select(string _TableName, string _Select)\r\n    {\r\n        try\r\n        {\r\n            string query = \"select \" + _Select + \" from \" + _TableName;\r\n            return ExecuteQuery(query);\r\n        }\r\n        catch\r\n        {\r\n            Debug.Log(\"\u67e5\u8be2\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\u9519\u8bef\u540e\u91cd\u65b0\u518d\u8bd5\" );\r\n            return null ;\r\n        }\r\n    }\r\n\r\n    \/\/\/ &lt;summary&gt;\r\n    \/\/\/ \u67e5\u8be2\u6570\u636e\r\n    \/\/\/ \u53c2\u6570\uff1a\u8868\u540d\u79f0\u3001\u67e5\u8be2\u5185\u5bb9\u3001\u67e5\u8be2\u6761\u4ef6\r\n    \/\/\/ &lt;\/summary&gt;\r\n    public DataSet Select(string _TableName, string _Select, string _Condition)\r\n    {\r\n        try {\r\n            string query = \"select \" + _Select + \" from \" + _TableName + \" where \" + _Condition;\r\n            return ExecuteQuery(query);\r\n        }\r\n        catch {\r\n            Debug.Log(\"\u67e5\u8be2\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\u9519\u8bef\u540e\u91cd\u65b0\u518d\u8bd5\" );\r\n            return null ;\r\n        }\r\n    }\r\n\r\n    \/\/\/ &lt;summary&gt;\r\n    \/\/\/ \u4fee\u6539\u6570\u636e\r\n    \/\/\/ \u53c2\u6570\uff1a\u8868\u540d\u79f0\u3001\u51c6\u5907\u4fee\u6539\u7684\u5c5e\u6027\u5217\u3001\u51c6\u5907\u4fee\u6539\u7684\u5c5e\u6027\u5217\u7684\u503c\u3001\u6761\u4ef6\u4fe1\u606f\u5c5e\u6027\u5217\u3001\u6761\u4ef6\u4fe1\u606f\u5c5e\u6027\u5217\u7684\u503c\r\n    \/\/\/ &lt;\/summary&gt;\r\n    public DataSet Update(string _TableName, string[] _SetColsName, string[] _SetColsValues, string _ConditionColName, string _ConditionColValue)\r\n                {\r\n        try {\r\n            string query = \"UPDATE \" + _TableName + \" SET \" + _SetColsName[0] + \" = \" + _SetColsValues[0];\r\n            for (int i = 1; i &lt; _SetColsValues.Length; ++i) {\r\n\r\n                query += \", \" + _SetColsName[i] + \" =\" + _SetColsValues[i];\r\n            }\r\n            query += \" WHERE \" + _ConditionColName + \" = \" + _ConditionColValue + \" \";\r\n            Debug.Log(\"~~\u4fee\u6539\u6210\u529f~~\" );\r\n            return ExecuteQuery(query);\r\n        }\r\n        catch {\r\n            Debug.Log(\"\u4fee\u6539\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\u9519\u8bef\u540e\u91cd\u65b0\u518d\u8bd5\" );\r\n            return null ;\r\n        }\r\n                }\r\n\r\n    \/\/\/ &lt;summary&gt;\r\n    \/\/\/ \u5220\u9664\u6570\u636e\r\n    \/\/\/ \u53c2\u6570\uff1a\u8868\u540d\u79f0\u3001\u5c5e\u6027\u5217\u3001\u503c\r\n    \/\/\/ &lt;\/summary&gt;\r\n                 public DataSet Delete(string _TableName, string [] _ColsName,string [] _ColsValue)\r\n                {\r\n        try {\r\n            string query = \"DELETE FROM \" + _TableName + \" WHERE \" + _ColsName[0] + \" = \" + _ColsValues[0];\r\n            for (int i = 1; i &lt; _ColsValues.Length; ++i) {\r\n                query += \" or \" + _ColsName[i] + \" = \" + _ColsValues[i];\r\n            }\r\n            Debug.Log(\"~~\u5220\u9664\u6210\u529f~~\" );\r\n            return ExecuteQuery(query);\r\n        }\r\n        catch {\r\n            Debug.Log(\"\u5220\u9664\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\u9519\u8bef\u540e\u91cd\u65b0\u518d\u8bd5\" );\r\n            return null ;\r\n        }\r\n                }\r\n \r\n                 public  void Close()\r\n                {\r\n                                 if(dbConnection != null ) {\r\n                                                dbConnection.Close();\r\n                                                dbConnection.Dispose();\r\n                                                dbConnection = null;\r\n                                }\r\n                }\r\n\r\n    public static DataSet ExecuteQuery(string sqlString)\r\n    {\r\n        if (dbConnection.State == ConnectionState .Open) {\r\n            DataSet ds = new DataSet();\r\n            try {\r\n\r\n                MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);\r\n                da.Fill(ds);\r\n            }\r\n            catch (Exception ee) {\r\n                throw new Exception( \"SQL:\" + sqlString + \"\/n\" + ee.Message.ToString());\r\n            }\r\n            return ds;\r\n        }\r\n        return null ;\r\n    }\r\n}\r\n<\/pre>\n<p><!--more--><\/p>\n<p>MySqlHelper \u5934\u6587\u4ef6\u4e0b\u8f7d\u5730\u5740\uff1a<\/p>\n<p>\u94fe\u63a5: http:\/\/pan.baidu.com\/s\/1uaQAi \u5bc6\u7801: 3tsy<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u8c03\u7528MySqlHelper\u5185\u5bb9\uff1a string[] col = new stri &hellip; <a href=\"http:\/\/www.u3d8.com\/?p=135\">\u7ee7\u7eed\u9605\u8bfb <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[24],"tags":[30,29,31,32],"_links":{"self":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts\/135"}],"collection":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=135"}],"version-history":[{"count":10,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts\/135\/revisions"}],"predecessor-version":[{"id":1371,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts\/135\/revisions\/1371"}],"wp:attachment":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=135"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}