数据库之序列化和反序列化

数据库的加强版，牢记序列化和反序列化

using System;
using MySql.Data;
using System.Data;
using System.IO;
using MySql.Data.MySqlClient;
using System.Text.RegularExpressions;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
namespace DataMgrTest
{
    public class Player
    {
       public  string id;
        public PlayerData data;
        public Player()
        {
            data = new PlayerData();
        }
    }
    [Serializable]
    public class PlayerData{

    public  int age=0;
        public string name = "";
    }
    public class DataMgr
    {
        MySqlConnection sqlConn;

        public static DataMgr instance;
            public DataMgr()
            {
                instance = this;
                Connect();
            }
        public void Connect()
        {

            string connStr = "Database=test;Data Source=127.0.0.1;";
            connStr += "User Id=root;Password=123;port=3306";
            sqlConn = new MySqlConnection(connStr);
            try
            {
                //打开数据
                sqlConn.Open();
            }
            catch (Exception e)
            {
                Console.Write("[DataMgr]Connect " + e.Message);
                return;
            }
        }

        public bool CanRegister(string id) {

            if (!IsSafe (id)) {
                return false;

            }
            //定义SQl语句
            string cmdStr = string.Format("select * from user where id='{0}';", id);  
            MySqlCommand cmd = new MySqlCommand (cmdStr, sqlConn);  
            try 
            {
                //执行查
                MySqlDataReader dataReader = cmd.ExecuteReader (); 
                bool hasRows = dataReader.HasRows;
                dataReader.Close();
                return !hasRows;
            }
            catch(Exception e)
            {
                Console.WriteLine("[DataMgr]CanRegister fail " + e.Message);
                return false;
            }
        }



        public bool IsSafe(string str){
            return  !Regex.IsMatch(str, @"[-|;|,|\/|\(|\)|\[|\]|\}|\{|%|@|\*|!|\']");
        }
        public bool Register(string id, string pw)
        {
            //防sql注入
            if (!IsSafe (id) || !IsSafe (pw)) 
            {
                Console.WriteLine("[DataMgr]Register 使用非法字符");
                return false;
            }
            //能否注册
            if (!CanRegister(id)) 
            {
                Console.WriteLine("[DataMgr]Register !CanRegister+不能注册");
                return false;
            }
            //写入数据库User表
            string cmdStr = string.Format("insert into user set id ='{0}' ,pwd ='{1}';", id, pw);
            MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch(Exception e)
            {
                Console.WriteLine("[DataMgr]Register " + e.Message);
                return false;
            }
        }
        //创建角色
        public bool CreatePlayer(string id)
        {
            //防sql注入
            if (!IsSafe(id))
                return false;
            //序列化
            IFormatter formatter = new BinaryFormatter ();
            MemoryStream stream = new MemoryStream ();
            PlayerData data = new PlayerData ();

            try 
            {
                formatter.Serialize(stream, data);
            } 
            catch(Exception e) 
            {
                Console.WriteLine("[DataMgr]CreatePlayer 序列化 " + e.Message);
                return false;
            }
            byte[] byteArr = stream.ToArray();
            //写入数据库
            string cmdStr = string.Format ("insert into player set id ='{0}',playerdata =@data;",id);
            MySqlCommand cmd = new MySqlCommand (cmdStr, sqlConn);
            //添加命令参数
            cmd.Parameters.Add ("@data", MySqlDbType.Blob);
            //添加对应命令参数的值，
            cmd.Parameters[0].Value = byteArr;
            cmd.Parameters.Add("@data", MySqlDbType.Blob);
            //添加对应命令参数的值，
            cmd.Parameters[0].Value = byteArr;

            try 
            {
                cmd.ExecuteNonQuery ();
                return true;
            } 
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]CreatePlayer 写入 " + e.Message);
                return false;
            }
        }
        //检测用户名密码
        public bool CheckPassWord(string id, string pw)
        {
            //防sql注入
            if (!IsSafe(id) || !IsSafe(pw))
                return false;
            //查询语句
            string cmdStr = string.Format("select * from user where id='{0}' and pwd='{1}';", id, pw);
            //构建查询命令类
            MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
            try
            {
                //查询
                MySqlDataReader dataReader = cmd.ExecuteReader();
                bool hasRows = dataReader.HasRows;
                dataReader.Close();
                return hasRows;
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]CheckPassWord " + e.Message);
                return false;
            }
        }
        //获取玩家数据
        public PlayerData GetPlayerData(string id)
        {
            PlayerData playerData = null;
            //防sql注入
            if (!IsSafe(id))
                return playerData;
            //查询
            string cmdStr = string.Format("select * from player where id ='{0}';", id);
            MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
            byte[] buffer;
            try
            {
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (!dataReader.HasRows)
                {
                    dataReader.Close();
                    return playerData;
                }
                //开始读取缓冲区的数据
                dataReader.Read();
                //playerdata数据第二列，它的索引是1
                //0代表是第一行
                //null代表不向任何字节数组中写入
                //buffer = new byte[1024];
                //目的就是得到数组的长度
                long len = dataReader.GetBytes(1, 0, null, 0, 0);//1是data  
                //构建buffer数组
                buffer = new byte[len];
                dataReader.GetBytes(1, 0, buffer, 0, (int)len);
                dataReader.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]GetPlayerData 查询 " + e.Message);
                return playerData;
            }
            //反序列化
            MemoryStream stream = new MemoryStream(buffer);

            try
            {
                IFormatter formatter = new BinaryFormatter();
                playerData = (PlayerData)formatter.Deserialize(stream);
                return playerData;
            }
            catch (SerializationException e)
            {
                Console.WriteLine("[DataMgr]GetPlayerData 反序列化 " + e.Message);
                return playerData;
            }
        }

        public bool SavePlayer(Player player)
        {
            string id = player.id;
            PlayerData playerData = player.data;
            //序列化
            IFormatter formatter = new BinaryFormatter();
            MemoryStream stream = new MemoryStream();
            try
            {
                //
                formatter.Serialize(stream, playerData);
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]SavePlayer 序列化 " + e.Message);
                return false;
            }
            byte[] byteArr = stream.ToArray();
            //写入数据库

            string cmdStr = string.Format
                ("update player set data =@data where id = '{0}';", player.id);
            MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
            cmd.Parameters.Add("@data", MySqlDbType.Blob);
            cmd.Parameters[0].Value = byteArr;
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]CreatePlayer 写入 " + e.Message);
                return false;
            }
        }
    }
}