如何从 Unity 连接到数据库

How to connect to database from Unity(如何从 Unity 连接到数据库)

问题描述

我正在尝试通过 Unity 连接到 MS SQL 数据库.但是，当我尝试打开连接时，我得到一个 IOException: Connection lost.

I am trying to connect to a MS SQL database through Unity. However, when I try to open a connection, I get an IOException: Connection lost.

我已从 UnityEditorDataMonolibmono2.0 导入 System.Data.dll.我正在使用以下代码:

I have imported System.Data.dll from UnityEditorDataMonolibmono2.0. I am using the following code:

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

 public class SQL_Controller : MonoBehaviour {

     string conString = "Server=myaddress.com,port;" +
             "Database=databasename;" +
             "User ID=username;" +
             "Password=password;";

     public string GetStringFromSQL()
     {
         LoadConfig();
         string result = "";

         SqlConnection connection = new SqlConnection(conString);
         connection.Open();
         Debug.Log(connection.State);
         SqlCommand Command = connection.CreateCommand();
         Command.CommandText = "select * from Artykuly2";
         SqlDataReader ThisReader = Command.ExecuteReader();
         while (ThisReader.Read())
         {
             result = ThisReader.GetString(0);
         }
         ThisReader.Close();
         connection.Close();

         return result;
     }
 }

这是我得到的错误:

IOException: Connection lost
Mono.Data.Tds.Protocol.TdsComm.GetPhysicalPacketHeader ()
Mono.Data.Tds.Protocol.TdsComm.GetPhysicalPacket ()
Mono.Data.Tds.Protocol.TdsComm.GetByte ()
Mono.Data.Tds.Protocol.Tds.ProcessSubPacket ()
Mono.Data.Tds.Protocol.Tds.NextResult ()
Mono.Data.Tds.Protocol.Tds.SkipToEnd ()
Rethrow as TdsInternalException: Server closed the connection.
Mono.Data.Tds.Protocol.Tds.SkipToEnd ()
Mono.Data.Tds.Protocol.Tds70.Connect (Mono.Data.Tds.Protocol.TdsConnectionParameters connectionParameters)
Mono.Data.Tds.Protocol.Tds80.Connect (Mono.Data.Tds.Protocol.TdsConnectionParameters connectionParameters)

请忽略此方法的任何安全风险，我需要这样做以进行测试，安全性稍后会出现.感谢您的宝贵时间.

Please disregard any security risks with this approach, I NEED to do this for testing, security will come later. Thank you for your time.

推荐答案

请忽略此方法的任何安全风险

Please disregard any security risks with this approach

不要这样做.安全性是在之前还是之后来并不重要.您将结束重新编写整个代码，因为 密码 在您的应用程序中是硬编码的，可以轻松地 进行反编译和检索.现在以正确的方式进行连接，这样您就不必重新编写整个应用程序.

Do not do it like this. It doesn't matter if security will come before or after. You will end of re-writing the whole code because the password is hard-coded in your application which can be decompiled and retrieved easily. Do the connection the correct way now so that you won't have to re-write the whole application.

使用 php、perl 或任何您熟悉的语言在您的服务器上运行您的数据库命令，但这应该在服务器上完成.

Run your database command on your server with php, perl or whatever language you are comfortable with but this should be done on the server.

在 Unity 中，使用 WWW 或 UnityWebRequest 类与该脚本进行通信，然后，您将能够从 Unity 向服务器发送和接收信息.示例有很多 那里.即使这样，您仍然需要实现自己的安全性，但这比您现在拥有的要好得多.

From Unity, use the WWW or UnityWebRequest class to communicate with that script and then, you will be able to send and receive information from Unity to the server. There are many examples out there. Even with this, you still need to implement your own security but this is much more better than what you have now.

您还可以使用 json 接收多个数据.

You can also receive data multiple with json.

以下是来自 this Unity wiki 的完整示例.它展示了如何使用服务器端的 php 和客户端的 Unity + C# 与 Unity 中的数据库进行交互.

Below is a complete example from this Unity wiki. It shows how to interact with a database in Unity using php on the server side and Unity + C# on the client side.

服务器端:

使用 PDO 添加分数:

<?php
        // Configuration
        $hostname = 'localhot';
        $username = 'yourusername';
        $password = 'yourpassword';
        $database = 'yourdatabase';

        $secretKey = "mySecretKey"; // Change this value to match the value stored in the client javascript below 

        try {
            $dbh = new PDO('mysql:host='. $hostname .';dbname='. $database, $username, $password);
        } catch(PDOException $e) {
            echo '<h1>An error has ocurred.</h1><pre>', $e->getMessage() ,'</pre>';
        }

        $realHash = md5($_GET['name'] . $_GET['score'] . $secretKey); 
        if($realHash == $hash) { 
            $sth = $dbh->prepare('INSERT INTO scores VALUES (null, :name, :score)');
            try {
                $sth->execute($_GET);
            } catch(Exception $e) {
                echo '<h1>An error has ocurred.</h1><pre>', $e->getMessage() ,'</pre>';
            }
        } 
?>

使用 PDO 检索分数:

<?php
    // Configuration
    $hostname = 'localhost';
    $username = 'yourusername';
    $password = 'yourpassword';
    $database = 'yourdatabase';

    try {
        $dbh = new PDO('mysql:host='. $hostname .';dbname='. $database, $username, $password);
    } catch(PDOException $e) {
        echo '<h1>An error has occurred.</h1><pre>', $e->getMessage() ,'</pre>';
    }

    $sth = $dbh->query('SELECT * FROM scores ORDER BY score DESC LIMIT 5');
    $sth->setFetchMode(PDO::FETCH_ASSOC);

    $result = $sth->fetchAll();

    if(count($result) > 0) {
        foreach($result as $r) {
            echo $r['name'], "	", $r['score'], "
";
        }
    }
?>

在服务器上启用跨域策略:

此文件应命名为crossdomain.xml"并放置在您的 Web 服务器的根目录中.Unity 要求您希望通过 WWW 请求访问的网站具有跨域策略.

This file should be named "crossdomain.xml" and placed in the root of your web server. Unity requires that websites you want to access via a WWW Request have a cross domain policy.

<?xml version="1.0"?>
<cross-domain-policy>
<allow-access-from domain="*"/>
</cross-domain-policy>

<小时>

客户端/统一端:

来自 Unity 的客户端代码连接到服务器，与 PDO 交互并根据调用的函数添加或检索分数.此客户端代码稍作修改以使用最新的 Unity 版本进行编译.

The client code from Unity connects to the server, interacts with PDO and adds or retrieves score depending on which function is called. This client code is slightly modified to compile with the latest Unity version.

private string secretKey = "mySecretKey"; // Edit this value and make sure it's the same as the one stored on the server
public string addScoreURL = "http://localhost/unity_test/addscore.php?"; //be sure to add a ? to your url
public string highscoreURL = "http://localhost/unity_test/display.php";

//Text to display the result on
public Text statusText;

void Start()
{
    StartCoroutine(GetScores());
}

// remember to use StartCoroutine when calling this function!
IEnumerator PostScores(string name, int score)
{
    //This connects to a server side php script that will add the name and score to a MySQL DB.
    // Supply it with a string representing the players name and the players score.
    string hash = Md5Sum(name + score + secretKey);

    string post_url = addScoreURL + "name=" + WWW.EscapeURL(name) + "&score=" + score + "&hash=" + hash;

    // Post the URL to the site and create a download object to get the result.
    WWW hs_post = new WWW(post_url);
    yield return hs_post; // Wait until the download is done

    if (hs_post.error != null)
    {
        print("There was an error posting the high score: " + hs_post.error);
    }
}

// Get the scores from the MySQL DB to display in a GUIText.
// remember to use StartCoroutine when calling this function!
IEnumerator GetScores()
{
    statusText.text = "Loading Scores";
    WWW hs_get = new WWW(highscoreURL);
    yield return hs_get;

    if (hs_get.error != null)
    {
        print("There was an error getting the high score: " + hs_get.error);
    }
    else
    {
        statusText.text = hs_get.text; // this is a GUIText that will display the scores in game.
    }
}

public string Md5Sum(string strToEncrypt)
{
    System.Text.UTF8Encoding ue = new System.Text.UTF8Encoding();
    byte[] bytes = ue.GetBytes(strToEncrypt);

    // encrypt bytes
    System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
    byte[] hashBytes = md5.ComputeHash(bytes);

    // Convert the encrypted bytes back to a string (base 16)
    string hashString = "";

    for (int i = 0; i < hashBytes.Length; i++)
    {
        hashString += System.Convert.ToString(hashBytes[i], 16).PadLeft(2, '0');
    }

    return hashString.PadLeft(32, '0');
}

这只是如何正确执行此操作的示例.如果您需要实现会话功能并关心安全性，请查看 OAuth 2.0 协议.应该有现有的库来帮助您开始使用 OAuth 协议.

This is just an example on how to properly do this. If you need to implement session feature and care about security, look into the OAuth 2.0 protocol. There should be existing libraries that will help get started with the OAuth protocol.

这篇关于如何从 Unity 连接到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！