MySQL于C#交互练习代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace CSharpToPhotonServer
{
    class Program
    {
        //cmd.ExecuteReader();        //执行一些查询
        //cmd.ExecuteNonQuery();      //插入，删除
        //cmd.ExecuteScalar();        //执行一些查询返回一个单个的值
        private static void Main(string[] args)
        {
            string connectIP = @"server = XXXXX.com;";
            string connectPort = @"port = ?????;";
            string connectUser = @"user = root;";
            string connectPassword = @"password = XXXXX;";
            string connectDatabase = @"database = MyGameDB;";

            MySqlConnection conn = new MySqlConnection(connectIP + connectPort + connectUser + connectPassword + connectDatabase);
            try
            {
                conn.Open();
                Console.WriteLine("已经建立连接");

                string sqlInsert = "INSERT INTO `MyGameDB`.`Account` (`AccountNum`, `AccountPassword`, `AccountName`, `registerDate`)";
                string sqlInsertVALUES = "VALUES ('1264881', 'sdhgdd6', 'fsddSHhjdj', '" + DateTime.Now +"');";
                //MySqlInsert(sqlInsert + sqlInsertVALUES, conn);       //插入

                string sqlReader = "SELECT * FROM MyGameDB.Account;";
                //MySqlReader(sqlReader, conn);     //查询

                string sqlUpdate = "UPDATE `MyGameDB`.`Account` SET `AccountVIP` = 'YES', `AccountLevel` = '9' WHERE (`ID` = '2');";
                //MySqlUpdate(sqlUpdate, conn);     //修改

                int DeleteID = 11;
                string sqlDelete = "DELETE FROM `MyGameDB`.`Account` WHERE (`ID` = '" + DeleteID + "');";
                //MySqlDelete(sqlDelete, conn);       //删除


                string sqlCount = "select count(*) from MyGameDB.Account;";
                //MySqlDataReader reader = MySqlCount(sqlCount, conn);
                //reader.Read();
                //Console.WriteLine(reader[0]);

                //object o = MySqlCount_2(sqlCount, conn);
                //int outNum = Convert.ToInt32(o);
                //Console.WriteLine(outNum);


                Console.WriteLine(VerifyUser("XXXXX", "XXXXX", conn));

            }
            catch (Exception e)
            {
                Console.WriteLine("连接失败");
                Console.WriteLine(e.ToString());
            }
            finally
            {
                conn.Close();
                Console.WriteLine("连接已关闭");
            }

            Console.ReadKey();


            MySqlDataReader MySqlCount(string sql, MySqlConnection coon)
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(sql, coon);
                    MySqlDataReader reader = cmd.ExecuteReader();     //返回值是数据库中受影响的数据行数
                    Console.WriteLine("查询数据成功");
                    return reader;
                }
                catch
                {
                    Console.WriteLine("查询数据失败");
                    return null;
                }
            }

            object MySqlCount_2(string sql, MySqlConnection coon)
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(sql, coon);
                    object o = cmd.ExecuteScalar();     //返回值是数据库中受影响的数据行数
                    Console.WriteLine("查询数据成功");
                    return o;
                }
                catch
                {
                    Console.WriteLine("查询数据失败");
                    return null;
                }
            }

            void MySqlDelete(string sql, MySqlConnection coon)
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(sql, coon);
                    int result = cmd.ExecuteNonQuery();     //返回值是数据库中受影响的数据行数
                    Console.WriteLine("成功删除[" + result + "]条数据");
                }
                catch
                {
                    Console.WriteLine("删除数据失败");
                }
            }


            void MySqlUpdate(string sql, MySqlConnection coon)
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(sql, coon);
                    int result = cmd.ExecuteNonQuery();     //返回值是数据库中受影响的数据行数
                    Console.WriteLine("成功更新[" + result + "]条数据");
                }
                catch
                {
                    Console.WriteLine("更新数据失败");
                }
            }

            void MySqlInsert(string sql, MySqlConnection coon)
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(sql, coon);
                    int result = cmd.ExecuteNonQuery();     //返回值是数据库中受影响的数据行数
                    Console.WriteLine("成功插入[" + result + "]条数据");
                }
                catch
                {
                    Console.WriteLine("插入数据失败");
                }
                

            }

            void MySqlReader(string sql, MySqlConnection coon)
            {
                MySqlCommand cmd = new MySqlCommand(sql, coon);
                MySqlDataReader reader = cmd.ExecuteReader();

                //reader.Read();
                //ReaderDB(reader);
                
                while(reader.Read() == true)
                {
                    ReaderDB(reader);
                }



            }

            void ReaderDB(MySqlDataReader reader)
            {
                try
                {
                    Console.Write("ID: " + reader.GetInt32(0));
                    Console.Write("\t" + "Num: " + reader.GetInt32(1) + "\t");
                    Console.Write("\t" + "Password: " + reader.GetString(2));
                    Console.Write("\t" + "Name: " + reader.GetString(3) + "\t");
                    Console.Write("\t" + "VIP: " + reader.GetString(4) + "\t");
                    Console.WriteLine("\t" + "Level: " + reader.GetInt32(5));
                }
                catch
                {
                    Console.WriteLine("读取失败");
                }
            }



        }

        private static bool VerifyUser(string userName, string userPassword, MySqlConnection coon)
        {
            try
            {
                //string sql = "SELECT * FROM MyGameDB.Account where AccountNum = '" + userName + "' and AccountPassword = '" + userPassword + "';";
                string sql = "SELECT * FROM MyGameDB.Account where AccountNum = @userNum and AccountPassword = @Password;";

                MySqlCommand cmd = new MySqlCommand(sql, coon);
                cmd.Parameters.AddWithValue("userNum", userName);
                cmd.Parameters.AddWithValue("Password", userPassword);

                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read() == true)
                {
                    Console.WriteLine("验证成功，登录完成！");
                    return true;
                }
                else
                {
                    Console.WriteLine("发生未知错误");
                }
            }
            catch
            {
                Console.WriteLine("用户名和密码错误，验证失败");
            }
            return false;
        }

        //private static void MySqlFun(string sql, MySqlConnection coon)
        //{
        //    MySqlCommand cmd = new MySqlCommand(sql, coon);
        //    //cmd.ExecuteReader();        //执行一些查询
        //    //cmd.ExecuteNonQuery();      //插入，删除
        //    //cmd.ExecuteScalar();        //执行一些查询返回一个单个的值

        //    MySqlDataReader reader = cmd.ExecuteReader();

        //    reader.Read();
        //    Console.WriteLine(reader[0].ToString() + reader[1].ToString() + reader[2].ToString() + reader[3].ToString() + reader[4].ToString() + reader[5].ToString());
        
        //}

    }
}