Unity direct database

Posted by dale282 on Wed, 01 Jan 2020 02:42:02 +0100

Take time today to take a note, and also review the previous things to record the Unity connection database.

Before you start, add the DLL you want to reference:

If not, you can download it on your own.

Enter the text.

First of all, in order to connect easily, use XML configuration file for connection information:

    <DBConfig>
     <ip>127.0.0.1</ip>
     <port>3306</port>
     <DBName>DBName</DBName>
      <id>root</id> 
      <passWord></passWord>
      </DBConfig>

Next, read the configuration file in Unity:

    private string host = "";
    private string id = "";
    private string pwd = "";
    private string database = "";
    private string port = "";

    private string xmlLocalpath = Application.dataPath + @"/DBConfig.xml";

    private void ReadLocalXml()
    {
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(xmlLocalpath);
        XmlNode xmlroot = xmlDoc.SelectSingleNode("DBConfig");

        XmlNodeList nodelist = xmlroot.ChildNodes;
        host = nodelist[0].InnerText;
        port = nodelist[1].InnerText;
        database = nodelist[2].InnerText;
        id = nodelist[3].InnerText;
        pwd = nodelist[4].InnerText;
    }

Next, use the read profile information to link the database and disconnect the link:

    public static MySqlConnection dbConnection;

    /// <summary>
    ///Open database
    /// </summary>
    /// <param name="connectionString"></param>
    public void OpenDB()
    {
        try
        {
            string connectionString = string.Format("Server = {0};port={4};Database = {1}; User ID = {2}; Password = {3}; Charset = {5}", host, database, id, pwd, port,"utf8");
            dbConnection = new MySqlConnection(connectionString);
            dbConnection.Open();
        }
        catch (Exception e)
        {
            Debug.LogError("Server connection failed, please recheck whether it is open MySql service:" + e.Message.ToString());
        }
    }

    /// <summary>
    ///Close database connection
    /// </summary>
    public void CloseDB()
    {
        if (dbConnection != null)
        {
            dbConnection.Close();
            dbConnection.Dispose();
            dbConnection = null;
        }

        Debug.Log("Disconnect database......");

    }

Next is the statement execution method:

    /// <summary>
    ///Execute query statement
    /// </summary>
    /// <param name="sqlQuery"></param>
    /// <returns></returns>
    public static DataSet ExecuteQuery(string sqlString)
    {
        if (dbConnection == null)
        {
            return null;
        }

        if (dbConnection.State == ConnectionState.Open)
        {
            DataSet ds = new DataSet();
            try
            {
                MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);
                da.Fill(ds);
            }
            catch (Exception ee)
            {
                Debug.LogError("SQL:" + sqlString + "/n" + ee.Message.ToString());
            }

            return ds;
        }
        return null;

    }

Here, database connection, closing and statement execution are all completed.

Finally, we will introduce how to use it to obtain data as an example:

     /// <summary>
    ///Get name table ID property
    /// </summary>
    public void GetNameTableID()
    {
    int id = 0;
        DataSet em = ConnectSqlDB.ExecuteQuery("Select * from name"); //Name is the table name
        if (em != null)
        {
            DataTable table = em.Tables[0];

            foreach (DataRow row in table.Rows)
            {    
                foreach (DataColumn column in table.Columns)
                {
                    if (column.ColumnName == "id")
                    {
                        id = Convert.ToInt32(row["id"]);
                    }
                }
            }
        }
        Debug.LogError("id = "+id );    
}

The business code will not be introduced too much. If you are interested, you can learn MySql.

Topics: Database Unity xml MySQL