POC - ASP.NET & MYSQL部分

標籤：

1. ASP.NET 用MYSQL是需要引入MYSQL的包的，這個在VS的TOOLS-> extension manager裡可以搜到的

2. 接下來是WEBCONFIG裡的配置資訊

  <connectionStrings>  <add name="connStr" connectionString="User Id=root;host=localhost;Database=test;password=1234;" providerName="MySql.Data.MySqlClient"/>  </connectionStrings>

3.MYSQL C#串連代碼

string strConn = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;            MySqlConnection myConnection = new MySqlConnection(strConn);            MySqlCommand myCommand = new MySqlCommand(QUERY, myConnection);            myConnection.Open();            MySqlDataReader myDataReader = myCommand.ExecuteReader();            while (myDataReader.Read() == true)            {                myDataReader["欄位"].ToString();                         }            myDataReader.Close();

4. 將XLSX轉換成CSV/TXT，然後資料匯入MYSQL

load data local infile "d:/sql.txt" into table <table_name> ignore 1 lines;

 

5.在處理MYSQL資料時遇到的一些問題

　　5.1 按指定順序排序

order by instr（‘value,value,value,value‘, <欄位名>）

　　5.2 需要建立一個日曆表來作為輔助表，需要寫個procedure

CREATE TABLE `tmpdate` (date varchar(20),amount int(2));DELIMITER $$DROP PROCEDURE IF EXISTS zj$$CREATE PROCEDURE zj(i DATE,j DATE)BEGINWHILE i<=j DOINSERT INTO `tmpdate` VALUES(i,0);SET i=i+INTERVAL 1 DAY;END WHILE;END$$DELIMITER ;call zj(‘2014-03-03‘,‘2015-12-31‘);select * from `tmpdate`;

 　　5.3 C#代碼中sql語句中帶變數的串連問題，如下param1,是由‘ " +param1 + " ‘ 組成，外單引，內雙引。

query = "select * from (select * from tmpdate where NOT EXISTS" +                     "(select Date from wmissue where Date=tmpdate.Date) union(select " +                     "Date,count(*) from (select * from wmissue where Customer=‘"+param1+"‘) B group by Date)) A where Date between " +                     "DATE_SUB(DATE_FORMAT(now(),‘%Y-%m-%d‘),interval 1 week) and DATE_FORMAT(now(),‘%Y-%m-%d‘) order by Date";

 

POC - ASP.NET & MYSQL部分