Photon Server服务器端（一）

目录

1.控制台应用程序跟MySQL建立连接

2.利用建立好的链接执行查询命令MySQLDataReader

3.利用程序对数据进行插入Insert操作

4.利用程序对数据进行更新Update和删除Delete操作

5.利用ExecuteScalar查询得到一个值的结果

6.在查询的时候添加参数 

---

1.控制台应用程序跟MySQL建立连接

       添加引用MySql.Data.dll

            记得引入命名空间：using MySql.Data.MySqlClient;

static void Main(string[] args)
        {
            string connectStr = "server=127.0.0.1;port=3306;database=my_schema;user=root;password=root";
            MySqlConnection conn = new MySqlConnection(connectStr);
            try
            {
                conn.Open();
                Console.WriteLine("已经建立连接");
            }
            catch(Exception e)
            {
                Console.WriteLine(e.ToString());
            }finally
            {
                conn.Close();
            }
            Console.ReadKey();
        }

2.利用建立好的链接执行查询命令MySQLDataReader

static void Main(string[] args)
        {
            string connectStr = "server=127.0.0.1;port=3306;database=my_schema;user=root;password=root";
            MySqlConnection conn = new MySqlConnection(connectStr);
            try
            {
                conn.Open();
                string sql = "select * from users";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //cmd.ExecuteReader();执行一些查询
                //cmd.ExecuteNonQuery();插入  删除
                //cmd.ExecuteScalar();执行一些查询，返回一个单个的值
                MySqlDataReader reader = cmd.ExecuteReader();
                reader.Read();//读取下一页数据，如果读取成功，返回true；如果没有下一页了，读取失败的话，返回false；
                Console.WriteLine(reader[0].ToString() + reader[1].ToString() + reader[2].ToString());
                reader.Read();
                Console.WriteLine(reader[0].ToString() + reader[1].ToString() + reader[2].ToString());
                Console.WriteLine("---------------------------------");
                while (reader .Read())
                {
                    reader.Read();
                    //Console.WriteLine(reader[0].ToString() + reader[1].ToString() + reader[2].ToString());
                    Console.WriteLine(reader .GetInt32 ("id")+reader .GetString ("username")+reader .GetString ("password"));//相比上面这种方式更加直观
                }
            }
            catch(Exception e)
            {
                Console.WriteLine(e.ToString());
            }finally
            {
                conn.Close();
            }
            Console.ReadKey();
        }

 

3.利用程序对数据进行插入Insert操作

string connectStr = "server=127.0.0.1;port=3306;database=my_schema;user=root;password=root";
            MySqlConnection conn = new MySqlConnection(connectStr);
            try
            {
                conn.Open();
                string sql = "insert into users(username,password) values ('lalala','12345')";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                conn.Close();
            }
            Console.ReadKey();

 =》  

4.利用程序对数据进行更新Update和删除Delete操作

• Update:

            string connectStr = "server=127.0.0.1;port=3306;database=my_schema;user=root;password=root";
            MySqlConnection conn = new MySqlConnection(connectStr);
            try
            {
                conn.Open();
                string sql = "update users set username='aaaaa', password='1111' where id=2 ";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                conn.Close();
            }
            Console.ReadKey();

=》

• delete:

string connectStr = "server=127.0.0.1;port=3306;database=my_schema;user=root;password=root";
            MySqlConnection conn = new MySqlConnection(connectStr);
            try
            {
                conn.Open();
                string sql = "delete from users where id=4 ";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                conn.Close();
            }
            Console.ReadKey();

=》

5.利用ExecuteScalar查询得到一个值的结果

string connectStr = "server=127.0.0.1;port=3306;database=my_schema;user=root;password=root";
            MySqlConnection conn = new MySqlConnection(connectStr);
            try
            {
                conn.Open();
                string sql = "select count(*) from users ";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //MySqlDataReader reader = cmd.ExecuteReader();
                //reader.Read();
                //int count= Convert .ToInt32 (reader[0].ToString());这种方式麻烦
                object o = cmd.ExecuteScalar();//执行一些查询，返回一个单个的值（这种方式相对简单）
                int count = Convert.ToInt32(o.ToString());
                Console.WriteLine(count);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                conn.Close();
            }
            Console.ReadKey();

6.在查询的时候添加参数 

        static void Main(string[] args)
        {
            Console.WriteLine(VerifyUser("aaaaa", "1111"));
            Console.WriteLine(VerifyUser("aaaaa", "1"));
            Console.ReadKey();
        }
        static bool VerifyUser(string username, string password)
        {
            string connectStr = "server=127.0.0.1;port=3306;database=my_schema;user=root;password=root";
            MySqlConnection conn = new MySqlConnection(connectStr);
            try
            {
                conn.Open();
                //string sql = "select * from users where username='" + username + "' and password='" + password + "'";
                //这种方式一看就不直观，因为分号太多，容易搞混。所有还是下面这种方式比较好
                //MySqlCommand cmd = new MySqlCommand(sql, conn);

                string sql = "select * from users where username=@username and password=@password";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("username", username);
                cmd.Parameters.AddWithValue("password", password);

                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                conn.Close();
            }
            return false;
        }