C # Notes for connecting to MySQL

Link string: Server =Localhost; Database = weirui; uid = root; Pwd =

1. You can use '.' To connect to SQL Server, but use localhost to connect to MySQL.

2. When linking sqlserver, use @ before the sqlparameter parameter @. But is it used in MySQL? Now

Mysqlparameter [] para = {  New Mysqlparameter ( "  ? Username  "  , Mysqldbtype. varchar ),  New Mysqlparameter ( " ? Password  "  , Mysqldbtype. varchar ),  New Mysqlparameter ( "  ? Realname  "  , Mysqldbtype. varchar ),  New Mysqlparameter ( "  ? Remark  "  , Mysqldbtype. varchar)}; Para [  0 ]. Value = Data. Username; Para [  1 ]. Value = Data. Password; Para [  2 ]. Value = Data. realname; Para [  3 ]. Value = Data. remark;  String SQL = "  Insert into 'user' (username, password, realname, remark) values (? Username ,? Password ,? Realname ,? Remark)  "  ; //  String SQL = "insert into 'user' (username, password, realname, remark) values ('" + data. username + "','" + data. password + "','" + data. realname + "','" + data. remark + "')";          Int A = 0  ;  Using (Mysqlconnection conn = New  Mysqlconnection (mysqlhelper. connectionstringlocaltransaction) {conn. open (); = Mysqlhelper. executenonquery (Conn, commandtype. Text, SQL, para );}  Return Mysqlhelper. Result ();

3. in SQL Server, if the field name is the same as the keyword of SQL Server, use [keyword. However, in MySQL, the keyword "-" is used as the key on the left of "1" on the keyboard.

4. Use Limit to query the first few records in MySQL, for example: Select * From Table1 limit 6

Date Format in MySQL

Set @ dt = '2017-09-10 07:15:30. 123 ';

Select date (@ DT); -- 2008-09-10
Select time (@ DT); -- 07:15:30. 123456
Select Year (@ DT); -- 2008
Select quarter (@ DT); -- 3
Select month (@ DT); -- 9
Select Week (@ DT); -- 36
Select Day (@ DT); -- 10
Select hour (@ DT); -- 7
Select minute (@ DT); -- 15
Select second (@ DT); -- 30
Select microsecond (@ DT); -- 123456

Solve "unable to convert MySQL date/time value to system. datetime ";

Use. Net connector to connect to the MySQL database. When retrieving some data, you may get an error message:

"Unable to convert MySQL date/time value to system. datetime"

This is because there is a "0000-00-00" data value in the date column. To solve this problem, you can set the data to null, or set "allow zero datetime = true" in the connection string"

Example:

<Add name = "mysqlcon" connectionstring = "database = test; Data Source = localhost; user id = root; Password = 123456; pooling = false; charset = utf8; Port = 3306;Allow zero datetime = true"/>

Reference: http://forums.asp.net/t/1259101.aspx/1