Poc-asp.net & MySQL Section

1. asp. NET with MySQL is required to introduce a MySQL package, which can be found in the VS tools-> extension Manager

2. Next is the configuration information in the Webconfig

  < connectionStrings >  <  name= "ConnStr"  connectionString= "User id=root;host=localhost;d atabase=test;password=1234; " ProviderName = "MySql.Data.MySqlClient" />  </ connectionStrings >

3.MYSQL C # Connection code

stringstrconn = configurationmanager.connectionstrings["ConnStr"].            ConnectionString; Mysqlconnection myconnection=Newmysqlconnection (strconn); Mysqlcommand mycommand=NewMysqlcommand (QUERY, myconnection);Myconnection.open (); Mysqldatareader MyDataReader=Mycommand.executereader ();  while(Mydatareader.read () = =true) {mydatareader[the field"].             ToString (); } mydatareader.close ();

4. Convert xlsx to Csv/txt, then data into MySQL

load data local infile "D:/intotable1 lines;

5. Some problems when working with MySQL data

5.1 Sort by specified order

Order  by InStr ('value,value,value,value'< field name >)

5.2 Need to create a calendar table as a secondary table, you need to write a procedure

CREATE TABLE' Tmpdate ' (datevarchar( -), Amountint(2));D elimiter $$DROP PROCEDURE IF EXISTSzj$$CREATE PROCEDUREZJ (i date,j DATE)BEGIN whileI<=J DoINSERT  into' Tmpdate 'VALUESI0);SETI=I+INTERVAL1  Day;END  while;END$ $DELIMITER; call ZJ ('2014-03-03','2015-12-31');Select *  from' Tmpdate ';

5.3 C # code in the SQL statement with variables in the connection problem, the following param1, is composed of ' "+param1 +" ', external single-cited, internal double-cited.

Query="Select *  from(Select *  fromTmpdatewhere  not EXISTS"+                     "(SelectDate fromWmissuewhereDate=Tmpdate. Date)Union(Select"+"Date,Count(*) from(Select *  fromWmissuewhereCustomer='  "+param1+"') BGroup  byDate)) AwhereDatebetween"+"Date_sub (Date_format (now),'%y-%m-%d'), Interval1Week andDate_format (now (),'%y-%m-%d')Order  byDate ";

Poc-asp.net & MySQL Section