T8hVhDQM

1using System.Collections;
2using System.Collections.Generic;
3using UnityEngine;
4using System.Data;
5using Mono.Data.Sqlite;
6
7public class LeaderBoardManager : MonoBehaviour
8{
9    private string _connectionString;
10    private List<LeaderBoard> LeaderBoard = new List<LeaderBoard>();
11    [SerializeField]
12    private GameObject _scorePrefab;
13    [SerializeField]
14    private Transform _scoreParent;
15    public GameObject ScorePrefab { get => _scorePrefab; set => _scorePrefab = value; }
16    public Transform ScoreParent { get => _scoreParent; set => _scoreParent = value; }
17
18
19    // Start is called before the first frame update
20    void Start()
21    {
22        _connectionString = $"URI=file:{Application.persistentDataPath}/LeaderBoardDB.db";
23        CreateLeaderBoard();
24        ShowScores();
25    }
26
27    // Update is called once per frame
28    void Update()
29    {
30        
31    }
32
33    private void CreateLeaderBoard()
34    {
35        using (var dbConnection = new SqliteConnection(_connectionString))
36        {
37            dbConnection.Open();
38            //var dropCommand = dbConnection.CreateCommand();
39            //dropCommand.CommandText = "DROP TABLE IF EXISTS LeaderBoard";
40
41            var createCommand = dbConnection.CreateCommand();
42            createCommand.CommandText = "CREATE TABLE IF NOT EXISTS LeaderBoard (" +
43                "PlayerID INTEGER " +
44                "PRIMARY KEY AUTOINCREMENT NOT NULL " +
45                "UNIQUE," +
46                "Name     TEXT NOT NULL," +
47                "Score INTEGER  NOT NULL," +
48                "Date     DATETIME CONSTRAINT[CURRENT_DATE] DEFAULT(CURRENT_DATE)" +
49                "); ";
50            //dropCommand.ExecuteReader();
51            createCommand.ExecuteReader();
52            dbConnection.Close();
53        }
54    }
55
56    private void InsertScore(string name, int newScore)
57    {
58        using (IDbConnection dbConnection = new SqliteConnection(_connectionString))
59        {
60            dbConnection.Open();
61
62            using (IDbCommand dbCommand = dbConnection.CreateCommand())
63            {
64                dbCommand.CommandText = $"INSERT INTO LeaderBoard(Name,Score) VALUES('{name}','{newScore}');";
65                dbCommand.ExecuteScalar();
66            }
67
68            dbConnection.Close();
69        }
70    }
71
72    private void DeleteScore(int id)
73    {
74        using (var dbConnection = new SqliteConnection(_connectionString))
75        {
76            dbConnection.Open();
77            using (var dbCommand = dbConnection.CreateCommand())
78            {
79                dbCommand.CommandText = $"DELETE FROM LeaderBoard WHERE PlayerID = {id};";
80                dbCommand.ExecuteScalar();
81            }
82            dbConnection.Close();
83        }
84    }
85
86    private void InsertIntoLeaderBoard()
87    {
88        using (var dbConnection = new SqliteConnection(_connectionString))
89        {
90            dbConnection.Open();
91            var command = dbConnection.CreateCommand();
92            command.CommandText = "INSERT INTO LeaderBoard (" +
93                "Name," +
94                "Score" +
95                ")" +
96                "VALUES" +
97                "('Stephens', '999')";
98            command.ExecuteNonQuery();
99            dbConnection.Close();
100        }
101    }
102    
103    private void GetScores()
104    {
105        // Clear Leaderboard first
106        LeaderBoard.Clear();
107
108        using (var dbConnection = new SqliteConnection(_connectionString))
109        {
110            dbConnection.Open();
111
112            using (IDbCommand dbCmd = dbConnection.CreateCommand())
113            {
114                string sqlQuery  = "SELECT * FROM LeaderBoard";
115                dbCmd.CommandText = sqlQuery;
116
117                using (IDataReader reader = dbCmd.ExecuteReader())
118                {
119                    while (reader.Read())
120                    {
121                        LeaderBoard.Add(new LeaderBoard(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), reader.GetDateTime(3)));
122                    }
123                    dbConnection.Close();
124                    reader.Close();
125                }
126            }
127        }
128    }
129
130    private void ShowScores()
131    {
132        GetScores();
133        for (int i = 0; i < LeaderBoard.Count; i++)
134        {
135            GameObject tmpObj = Instantiate(ScorePrefab);
136
137            LeaderBoard tmpScore = LeaderBoard[i];
138
139            tmpObj.GetComponent<LeaderBoardScript>().SetScore("#" + (i+1).ToString(), tmpScore.Name, tmpScore.Score.ToString());
140
141            tmpObj.transform.SetParent(ScoreParent);
142        }
143    }
144
145}