独立项目-建立Web服务器-02

一、设置服务器站点读取数据库内容

　　　　1.修改表中的CreateTime，如下图所示：

　　　　2.在表中（之前创建的表），填写数据，如下图所示：

二、VS2017中的脚本创建

　　　　1.在VS2017中，打开之前创建的工程“WebAccount”工程，

　　　　   创建Entity文件夹。如下图所示：

　　　　2.创建普通类，AccountEntity.cs类

namespace WebAccount.Entity
{
    public class AccountEntity
    {
        public int Id { get; set; }

        public string UserName { get; set; }

        public string Pwd { get; set; }

        public int YuanBao { get; set; }

        public int LastServerId { get; set; }

        public string LastServerName { get; set; }

        public DateTime CreateTime { get; set; }

        public DateTime UpdateTime { get; set; }
    }
}

　　　　3.在Models文件夹中，创建AccountDBModel.cs类

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using WebAccount.Entity;

namespace WebAccount.Models
{
    public class AccountDBModel
    {
        #region 创建单例

        /// <summary>
        /// 锁
        /// </summary>
        private static object lock_object = new object();


        private static AccountDBModel _instance;

        public static AccountDBModel Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (lock_object)
                    {
                        if (_instance == null)
                        {
                            _instance = new AccountDBModel();
                        }
                    }
                }
                return _instance;
            }
        }

        #endregion

        #region 属性
        //视图里面的服务器资源管理器进行设置并且添加的
        private const string connStr = @"Data Source=DESKTOP-63OA6R3DEAN;Initial Catalog=DBAccount;Integrated Security=True";
        #endregion

        /// <summary>
        /// 根据账户id，从数据库获取账号信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AccountEntity Get(int id)
        {
            //建立数据库连接
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //数据库打开
                conn.Open();

                //建立执行对象
                SqlCommand cmd = new SqlCommand("Account_Get",conn);

                //设置方式为使用存储过程
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                //添加查询参数
                cmd.Parameters.Add(new SqlParameter("@Id", id));

                using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                {
                    //如果有数据
                    if (dr.HasRows && dr.Read())
                    {                      

                        AccountEntity entity = new AccountEntity();

                        entity.Id = dr["Id"] is DBNull ? 0 : Convert.ToInt32(dr["Id"]);
                        entity.UserName = dr["UserName"] is DBNull ? string.Empty : dr["UserName"].ToString();
                        entity.Pwd = dr["Pwd"] is DBNull ? string.Empty : dr["Pwd"].ToString();
                        entity.YuanBao = dr["YuanBao"] is DBNull ? 0 : Convert.ToInt32(dr["YuanBao"]);
                        entity.LastServerId = dr["LastServerId"] is DBNull ? 0 : Convert.ToInt32(dr["LastServerId"]);
                        entity.LastServerName = dr["LastServerName"] is DBNull ? string.Empty : dr["LastServerName"].ToString();
                        entity.CreateTime = dr["CreateTime"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(dr["CreateTime"]);
                        entity.UpdateTime = dr["UpdateTime"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(dr["UpdateTime"]);

                        return entity;
                        
                    }
                }
            }

            return null;
        }

        /// <summary>
        /// 注册账号
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int Register(string userName, string pwd)
        {
            int userId = -1;
            //建立数据库连接
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //数据库打开
                conn.Open();

                //建立执行对象
                SqlCommand cmd = new SqlCommand("Account_Register", conn);

                //设置方式为使用存储过程
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@UserName", userName));
                cmd.Parameters.Add(new SqlParameter("@Pwd", pwd));

                userId = Convert.ToInt32(cmd.ExecuteScalar().ToString());
 
            }

            return userId;
        }
    }
}

　　　　4.创建RetValues.cs类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebAccount
{
    public class RetValue
    {
        public bool HasError;

        public string ErrorMsg;

        public object RetData;
    }
}

　　　　5.修改AccountController.cs类：

using LitJson;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using WebAccount.Entity;
using WebAccount.Models;

namespace WebAccount.Controllers.api
{
    public class AccountController : ApiController
    {
        // GET: api/Account
        public IEnumerable<string> Get()
        {
            return new string[] { "value1", "value2" };
        }

        // GET: api/Account/5
        public AccountEntity Get(int id)
        {           
            return AccountDBModel.Instance.Get(id);
        }

        // POST: api/Account
        public RetValue Post([FromBody]string jsonStr)
        {
            RetValue ret = new RetValue();

            try
            {
                JsonData jsonData = JsonMapper.ToObject(jsonStr);
                int type = Convert.ToInt32(jsonData["type"].ToString());
                string userName = jsonData["UserName"].ToString();
                string pwd = jsonData["Pwd"].ToString();

                //注册
                if (type == 0)
                {
                    ret.RetData = AccountDBModel.Instance.Register(userName, pwd);
                }
                //登陆
                else if (type == 1)
                {
                }
            }
            catch (Exception ex)
            {
                ret.HasError = true;
                ret.ErrorMsg = ex.Message;
            }

            return ret;
        }

        // PUT: api/Account/5
        public void Put(int id, [FromBody]string value)
        {
        }

        // DELETE: api/Account/5
        public void Delete(int id)
        {
        }
    }
}

三、数据库中的可编程性->存储过程

　　　　1.获取，Get存储过程，如下图：

　　　　2.注册，Register存储过程，如下图：

四、在Unity中连接数据库，并举例使用

　　　　1.创建NetWorkHttp.cs类：

#region 模块信息
// **********************************************************************
// Copyright (C) 2018 The company name
//
// 文件名(File Name):             NetWorkHttp.cs
// 作者(Author):                  Dean1874
// 创建时间(CreateTime):          2018-06-02 18:50:36
// 修改者列表(modifier):
// 模块描述(Module description):
// 
// **********************************************************************
#endregion

using System;
using System.Collections;
using UnityEngine;

/// <summary>
/// Http通讯管理
/// </summary>
public class NetWorkHttp : DontDesMonoSingleton<NetWorkHttp> 
{
    #region 属性
    /// <summary>
    /// Web请求回调
    /// </summary>
    private Action<CallBackArgs> m_CallBack;

    /// <summary>
    /// Web请求回调数据
    /// </summary>
    private CallBackArgs m_CallBackArgs;

    /// <summary>
    /// 是否繁忙
    /// </summary>
    private bool m_IsBusy = false;

    /// <summary>
    /// 是否繁忙
    /// </summary>
    public bool IsBusy
    {
        get
        {
            return m_IsBusy;
        }
    }
    #endregion 

    private void Start()
    {
        m_CallBackArgs = new CallBackArgs();
    }

    #region 发送web数据
    /// <summary>
    /// 发送web数据
    /// </summary>
    /// <param name="url">地址</param>
    /// <param name="callBack">回调</param>
    /// <param name="isPost">是否是请求</param>
    /// <param name="json">json数据</param>
    public void SendData(string url, Action<CallBackArgs> callBack, bool isPost = false, string json = null)
    {
        if (m_IsBusy)
        {
            return;
        }

        m_IsBusy = true;

        m_CallBack = callBack;

        //如果不是请求
        if (!isPost)
        {
            GetUrl(url);
        }
        else
        {
            PostUrl(url, json);
        }
    }

    #endregion

    #region GetUrl Get请求

    /// <summary>
    /// Get请求
    /// </summary>
    /// <param name="url"></param>
    private void GetUrl(string url)
    {
        WWW data = new WWW(url);
        StartCoroutine(Request(data));
    }


    #endregion

    #region PostUrl Post请求

    /// <summary>
    /// Post请求
    /// </summary>
    /// <param name="url"></param>
    /// <param name="json"></param>
    private void PostUrl(string url, string json)
    {
        //定义一个表单
        WWWForm form = new WWWForm();

        //给表单添加值
        form.AddField("", json);

        WWW data = new WWW(url, form);

        StartCoroutine(Request(data));
    }
    #endregion

    #region 请求服务器

    /// <summary>
    /// 请求服务器
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    private IEnumerator Request(WWW data)
    {
        yield return data;

        //返回数据，进行回调
        //设置当前不繁忙
        m_IsBusy = false;

        //如果没有错误
        if (string.IsNullOrEmpty(data.error))
        {
            //一样视为存在错误
            if (data.text == "null")
            {
                if (m_CallBack != null)
                {
                    m_CallBackArgs.HasError = true;
                    m_CallBackArgs.ErrorMsg = "未请求到数据";

                    //回调
                    m_CallBack(m_CallBackArgs);
                }
            }
            else
            {
                if (m_CallBack != null)
                {
                    m_CallBackArgs.HasError = false;
                    m_CallBackArgs.Data = data.text;
                    

                    //回调
                    m_CallBack(m_CallBackArgs);
                }
            }
        }
        else
        {
            if (m_CallBack != null)
            {
                m_CallBackArgs.HasError = true;
                m_CallBackArgs.ErrorMsg = data.error;

                //回调
                m_CallBack(m_CallBackArgs);
            }
        }
    }
    #endregion

    #region Web请求回调数据
    /// <summary>
    /// Web请求回调数据
    /// </summary>
    public class CallBackArgs : EventArgs
    {
        //是否有错
        public bool HasError;

        //错误内容
        public string ErrorMsg;

        //Jason数据
        public string Data;
    }
    #endregion 
}

　　　　2.创建AccountEntity.cs实体类，和服务器端的一样，可以直接复制过来用。

using System;

/// <summary>
/// 账户实体
/// </summary>
public class AccountEntity
{
    public int Id { get; set; }

    public string UserName { get; set; }

    public string Pwd { get; set; }

    public int YuanBao { get; set; }

    public int LastServerId { get; set; }

    public string LastServerName { get; set; }

    public DateTime CreateTime { get; set; }

    public DateTime UpdateTime { get; set; }
}

　　　　3.在实例创景中，创建Test_007_Http_00.cs类

#region 模块信息
// **********************************************************************
// Copyright (C) 2018 The company name
//
// 文件名(File Name):             Test_007_Http_00.cs
// 作者(Author):                  Dean1874
// 创建时间(CreateTime):          2018-06-02 19:20:19
// 修改者列表(modifier):
// 模块描述(Module description):
// 
// **********************************************************************
#endregion

using LitJson;
using System;
using UnityEngine;

public class Test_007_Http_00 : MonoBehaviour 
{
    private void Start()
    {
        //Get
        if (!NetWorkHttp.Instance.IsBusy)
        {
            NetWorkHttp.Instance.SendData(GlobalInit.WebAccountUrl + "api/account?id=3006", GetCallBack);
        }

        //Post
        if (!NetWorkHttp.Instance.IsBusy)
        {
            JsonData jsonData = new JsonData();

            jsonData["type"] = 0;//0=注册 1=登陆

            jsonData["UserName"] = "text322";
            jsonData["Pwd"] = "123";

            NetWorkHttp.Instance.SendData(GlobalInit.WebAccountUrl + "api/account", PostCallBack, isPost: true, json: jsonData.ToJson());
        }
    }

    private void PostCallBack(NetWorkHttp.CallBackArgs obj)
    {

        if (obj.HasError)
        {
            Debug.Log(obj.ErrorMsg);
        }
        else
        {

            RetValue ret = JsonMapper.ToObject<RetValue>(obj.Data);
            if (!ret.HasError)
            {
                Debug.Log("用户编号=" + ret.RetData);
            }
        }
    }

    private void GetCallBack(NetWorkHttp.CallBackArgs obj)
    {
        Debug.Log(obj.Data);

        if (obj.HasError)
        {
            Debug.Log(obj.ErrorMsg);
        }
        else
        {
            AccountEntity entity = LitJson.JsonMapper.ToObject<AccountEntity>(obj.Data);
            Debug.Log(entity.UserName);
        }
    }
}

　　　　4.运行脚本使用。