Unity SqlServer数据库数据的读取与解析

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using UnityEngine;

/// <summary>
/// SqlServer数据库数据的读取与解析
/// </summary>
public class SqlServerConfigParse : MonoBehaviour
{
    public static SqlServerConfigParse instance;

    public bool canConnectSqlServer = false;

    SqlConnection connection = null;

    SqlDataAdapter dataAdapter = null;

    #region 操作语句
    string connectStr = "server={0};database={1};uid={2};pwd={3}";

    string insert_Browser = "INSERT INTO FangZhen_Browser(BrowserName,X,Y,Width,Height,url,StationID,SceneModeIndex) VALUES('{0}',{1},{2},{3},{4},'{5}','{6}',{7})";

    string update_Browser = "Update FangZhen_Browser set X={0},Y={1},Width={2},Height={3},url='{4}',StationID='{5}',SceneModeIndex={6} where BrowserName='{7}'";

    string delet_Browser = "Delete from FangZhen_Browser where BrowserName='{0}'";

    string findStr = "select * from {0}";

    string findRowStr = "SELECT * FROM {0} WHERE BrowserName='{1}'";
    #endregion

    private void Awake()
    {
        instance = this;

        InitConnection();
    }

    void InitConnection()
    {
        DataBaseConfig config = JsonConfigParse.GetDataBaseConfigByJson();

        if (!ConfigurationData.IsPingAimIP(config.IP))
        {
            canConnectSqlServer = false;
            return;
        }

        connectStr = string.Format(ConfigurationData.configSqlConnectionStr, config.IP, config.database, config.uid, config.pwd);

        connection = new SqlConnection(connectStr);

        try
        {
            connection.Open();

            canConnectSqlServer = true;
        }
        catch (System.Exception)
        {
            canConnectSqlServer = false;
            throw;
        }
    }

    #region 数据读取
    /// <summary>
    /// 获取窗口配置信息
    /// </summary>
    /// <returns></returns>
    public List<BrowserConfig> GetBrowserMsgFromSqlServer()
    {
        List<BrowserConfig> MsgDic = new List<BrowserConfig>();

        string sql = "select * from FangZhen_Browser";

        dataAdapter = new SqlDataAdapter(sql, connection);

        DataTable dt = new DataTable();

        dataAdapter.Fill(dt);

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            BrowserConfig browserMsg = new BrowserConfig
            {
                BrowserName = dt.Rows[i]["BrowserName"].ToString(),
                X = float.Parse(dt.Rows[i]["X"].ToString()),
                Y = float.Parse(dt.Rows[i]["Y"].ToString()),
                Width = float.Parse(dt.Rows[i]["Width"].ToString()),
                Height = float.Parse(dt.Rows[i]["Height"].ToString()),
                Url = dt.Rows[i]["url"].ToString(),
                StationID = dt.Rows[i]["StationID"].ToString(),
                SceneModeIndex = int.Parse(dt.Rows[i]["SceneModeIndex"].ToString())
            };

            if (!MsgDic.Contains(browserMsg))
            {
                MsgDic.Add(browserMsg);
            }
        }

        connection.Close();

        return MsgDic;
    }
    #endregion

    #region 数组写入
    /// <summary>
    /// 更新窗口表
    /// </summary>
    /// <param name="browsers"></param>
    public void UpdateBrowserForSqlServer(BrowserConfig browser)
    {
        SqlCommand cmd = new SqlCommand();

        if (connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }

        //查询表中是否已存在此名称窗口
        string sql = string.Format(findRowStr, "FangZhen_Browser", browser.BrowserName);

        dataAdapter = new SqlDataAdapter(sql, connection);

        DataTable dt = new DataTable();

        dataAdapter.Fill(dt);

        //存在，更新窗口
        if (dt.Rows.Count != 0)
        {
            cmd.CommandText = string.Format(update_Browser, browser.X, browser.Y, browser.Width,
                browser.Height, browser.Url, browser.StationID, browser.SceneModeIndex, browser.BrowserName);
        }
        //不存在，添加窗口
        else
        {
            cmd.CommandText = string.Format(insert_Browser, browser.BrowserName, browser.X, browser.Y,
            browser.Width, browser.Height, browser.Url, browser.StationID, browser.SceneModeIndex);
        }

        cmd.Connection = connection;

        cmd.ExecuteNonQuery();

        connection.Close();
    }

    /// <summary>
    /// 删除窗口表中的指定元素
    /// </summary>
    /// <param name="browser"></param>
    public void DeletBrowserFromSqlServer(string browserName)
    {
        SqlCommand cmd = new SqlCommand();

        if (connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }

        cmd.Connection = connection;

        cmd.CommandText = string.Format(delet_Browser, browserName);

        cmd.ExecuteNonQuery();

        connection.Close();
    }
    #endregion

    #region 数据查找
    public BrowserConfig Select(string n)//查找数据 并返回
    {
        BrowserConfig data = null;
        using (connection)
        {
            try
            {
                //表示一个Sql 这里指向Conn的存储过程
                SqlCommand select = new SqlCommand("select * from FangZhen_Browser where BrowserName='" + n + "'", connection);
                SqlDataReader sr = select.ExecuteReader(); // 使用上面的语句查询数据库
                while (sr.Read()) // 读取数据
                    data = new BrowserConfig
                    {
                        BrowserName = sr["BrowserName"].ToString(),
                        X = float.Parse(sr["X"].ToString()),
                        Y = float.Parse(sr["Y"].ToString()),
                        Width = float.Parse(sr["Width"].ToString()),
                        Height = float.Parse(sr["Height"].ToString()),
                        Url = sr["url"].ToString(),
                        StationID = sr["StationID"].ToString(),
                        SceneModeIndex = int.Parse(sr["url"].ToString())
                    }; //类似于字典的操作

                Debug.Log(data.BrowserName);
            }
            catch (SqlException ex)
            {
                Debug.LogError(ex.Message);
            }
        }
        return data;
    }
    #endregion
}