C#如何連接Mysql數(shù)據(jù)庫？詳解報錯異常和增刪改查

=?=

catch?(MySqlException?ex) {????switch?(ex.Number) ????{????????case?0: ????????Console.WriteLine("Cannot?connect?to?server.??Contact?administrator");????????break;????case?1045: ????????Console.WriteLine("Invalid?username/password,?please?try?again");????????break; ????} }

string?sql=?"select?*?from?user"; MySqlCommand?cmd?=?new?MySqlCommand(sql,conn); MySqlDataReader?reader?=cmd.ExecuteReader();//執(zhí)行ExecuteReader()返回一個MySqlDataReader對象while?(reader.Read())//初始索引是-1，執(zhí)行讀取下一行數(shù)據(jù)，返回值是bool{????//Console.WriteLine(reader[0].ToString()?+?reader[1].ToString()?+?reader[2].ToString());????//Console.WriteLine(reader.GetInt32(0)+reader.GetString(1)+reader.GetString(2)); ????Console.WriteLine(reader.GetInt32("userid")?+?reader.GetString("username")?+?reader.GetString("password"));//"userid"是數(shù)據(jù)庫對應(yīng)的列名，推薦這種方式}

//string?sql?=?"select?*?from?user?where?username='"+username+"'?and?password='"+password+"'";?//我們自己按照查詢條件去組拼string?sql?=?"select?*?from?user?where?username=@para1?and?password=@para2";//在sql語句中定義parameter，然后再給parameter賦值MySqlCommand?cmd?=?new?MySqlCommand(sql,?conn); cmd.Parameters.AddWithValue("para1",?username); cmd.Parameters.AddWithValue("para2",?password);  MySqlDataReader?reader?=?cmd.ExecuteReader();if?(reader.Read())//如果用戶名和密碼正確則能查詢到一條語句，即讀取下一行返回true{????return?true; }

string?sql?=?"select?count(*)?from?user"; MySqlCommand?cmd?=?new?MySqlCommand(sql,?conn); Object?result=cmd.ExecuteScalar();//執(zhí)行查詢，并返回查詢結(jié)果集中第一行的第一列。所有其他的列和行將被忽略。select語句無記錄返回時，ExecuteScalar()返回NULL值if?(result?!=?null) {????int?count?=?int.Parse(result.ToString()); }

string?sql?=?"insert?into?user(username,password,registerdate)?values('啊寬','123','"+DateTime.Now+"')";//string?sql?=?"delete?from?user?where?userid='9'";//string?sql?=?"update?user?set?username='啊哈',password='123'?where?userid='8'";MySqlCommand?cmd?=?new?MySqlCommand(sql,conn);int?result?=cmd.ExecuteNonQuery();//3.執(zhí)行插入、刪除、更改語句。執(zhí)行成功返回受影響的數(shù)據(jù)的行數(shù)，返回1可做true判斷。執(zhí)行失敗不返回任何數(shù)據(jù)，報錯，下面代碼都不執(zhí)行

String?connetStr?=?"server=127.0.0.1;user=root;password=root;database=minecraftdb;"; MySqlConnection?conn?=?new?MySqlConnection(connetStr); conn.Open();//必須打開通道之后才能開始事務(wù)MySqlTransaction?transaction?=?conn.BeginTransaction();//事務(wù)必須在try外面賦值不然catch里的transaction會報錯:未賦值Console.WriteLine("已經(jīng)建立連接");try{????string?date?=?DateTime.Now.Year?+?"-"?+?DateTime.Now.Month?+?"-"?+?DateTime.Now.Day;????string?sql1=?"insert?into?user(username,password,registerdate)?values('啊寬','123','"?+?date?+?"')"; ????MySqlCommand?cmd1?=?new?MySqlCommand(sql1,conn); ????cmd1.ExecuteNonQuery();????string?sql2?=?"insert?into?user(username,password,registerdate)?values('啊寬','123','"?+?date?+?"')"; ????MySqlCommand?cmd2?=?new?MySqlCommand(sql2,?conn); ????cmd2.ExecuteNonQuery(); }catch?(MySqlException?ex) { ????Console.WriteLine(ex.Message); ????transaction.Rollback();//事務(wù)ExecuteNonQuery()執(zhí)行失敗報錯，username被設(shè)置unique ????conn.Close(); }finally{????if?(conn.State?!=?ConnectionState.Closed) ????{ ????????transaction.Commit();//事務(wù)要么回滾要么提交，即Rollback()與Commit()只能執(zhí)行一個 ????????conn.Close(); ????} }