using Mono.Data.
Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlTypes;
public class DatabaseManager
{
private static string dbPath = "URI=file:UserData.db";
public static void InitializeDatabase()
{
using (var connection = new SqliteConnection(dbPath))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "CREATE TABLE
" +
"IF NOT EXISTS User
" +
" (UserID INTEGER PRIMARY KEY
AUTOINCREMENT " +
" ,CreatedOn TEXT
" +
" ,ModifiedOn TEXT
" +
" ,StateCode INTEGER
" +
" ,StatusCode INTEGER
" +
" ,Gender INTEGER
" +
" ,Name TEXT
" +
" ,Costume INTEGER
" +
" ,Language INTEGER
" +
" ,Money Double)
";
command.ExecuteNonQuery();
}
connection.Close();
}
}
public static int InsertUser(User user)
{
int affectedrows = 0;
DateTime today = DateTime.Now;
using (var connection = new SqliteConnection(dbPath))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = @"
INSERT INTO User (
CreatedOn
,ModifiedOn
,StateCode
,StatusCode
,Gender
,Name
,Costume
,Language
,Money)
VALUES ( @CreatedOn
,@ModifiedOn
,@StateCode
,@StatusCode
,@Gender
,@Name
,@Costume
,@Language
,@Money);";
command.Parameters.AddWithValue("@CreatedOn", today.ToString());
command.Parameters.AddWithValue("@ModifiedOn", today.ToString());
command.Parameters.AddWithValue("@StateCode", 1);
command.Parameters.AddWithValue("@StatusCode", 1);
command.Parameters.AddWithValue("@Gender", user.Gender);
command.Parameters.AddWithValue("@Name", user.Name);
command.Parameters.AddWithValue("@Costume", user.Costume);
command.Parameters.AddWithValue("@Language", user.Language);
command.Parameters.AddWithValue("@Money", user.Money);
affectedrows = command.ExecuteNonQuery();
}
connection.Close();
}
return affectedrows;
}
public static int UpdateUser(User user)
{
int affectedRows = 0;
DateTime today = DateTime.Now;
using (var connection = new SqliteConnection(dbPath))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "UPDATE User U
" +
"SET U.ModifiedOn = @ModifiedOn
" +
" ,U.Gender = @Gender
" +
" ,U.Name = CASE WHEN @Name IS NULL THEN
U.Name ELSE @Name END " +
" ,U.Costume = CASE WHEN @Costume IS NULL
THEN U.Costume ELSE @Costume END " +
" ,U.Language = CASE WHEN @Language IS
NULL THEN U.Language ELSE @Language END ";
// Parametreleri ekleme
command.Parameters.AddWithValue("@ModifiedOn", today.ToString());
command.Parameters.AddWithValue("@Gender", user.Gender);
command.Parameters.AddWithValue("@Name", user.Name);
command.Parameters.AddWithValue("@Costume", user.Costume);
command.Parameters.AddWithValue("@Language", user.Language);
affectedRows = command.ExecuteNonQuery();
}
connection.Close();
}
return affectedRows;
}
public static User GetLastUserData()
{
using (var connection = new SqliteConnection(dbPath))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT UserID " +
" ,CreatedOn " +
" ,ModifiedOn " +
" ,StateCode " +
" ,StatusCode " +
" ,Name " +
" ,Costume " +
" ,Language " +
" ,Money " +
" FROM User " +
" ORDER BY UserID DESC ";
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return new User
{
UserID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
CreatedOn = reader.IsDBNull(1) ? null :
reader.GetString(1),
ModifiedOn = reader.IsDBNull(2) ? null :
reader.GetString(2),
StateCode = reader.IsDBNull(3) ? false :
reader.GetBoolean(3),
StatusCode = reader.IsDBNull(4) ? 0 :
reader.GetInt32(4),
Name = reader.IsDBNull(5) ? null : reader.GetString(5),
Costume = reader.IsDBNull(6) ? 0 : reader.GetInt32(6),
Language = reader.IsDBNull(7) ? 0 : reader.GetInt32(7),
Money = reader.IsDBNull(8) ? 0 : reader.GetDouble(8)
};
}
}
}
connection.Close();
}
return null; // Eğer veri yoksa null döner
}
public static double GetUserMoney(int userId)
{
using (var connection = new SqliteConnection(dbPath))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT Money " +
"FROM User " +
"WHERE UserID = @UserId ";
command.Parameters.AddWithValue("@UserId", userId);
object result = command.ExecuteScalar();
return result != null ? Convert.ToDouble(result) : 0.0;
}
}
}
public static bool UpdateUserMoney(int userId, double amount)
{
using (var connection = new SqliteConnection(dbPath))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "UPDATE User " +
"SET Money = Money + @Amount " +
"WHERE UserID = @UserId ";
command.Parameters.AddWithValue("@Amount", amount);
command.Parameters.AddWithValue("@UserId", userId);
int rowsAffected = command.ExecuteNonQuery();
return rowsAffected > 0; // Güncelleme başarılıysa true döner
}
}
}
public static List<Mission> GetMissionListByMissionSection(string
missionSection)
{
List<Mission> missions = new List<Mission>();
using (var connection = new SqliteConnection(dbPath))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT MissionID " +
" ,StateCode " +
" ,StatusCode " +
" ,MissionName " +
" ,ReferanceMissionID " +
" ,Row " +
" ,MissionSection " +
"FROM Mission " +
"WHERE MissionSection = @MissionSection ";
command.Parameters.AddWithValue("@MissionSection", missionSection);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
missions.Add(new Mission
{
MissionID = reader.IsDBNull(0) ? null :
reader.GetString(0),
StateCode = reader.IsDBNull(1) ? 0 :
reader.GetInt32(1),
StatusCode = reader.IsDBNull(2) ? 0 :
reader.GetInt32(2),
MissionName = reader.IsDBNull(3) ? null :
reader.GetString(3),
ReferanceMissionID = reader.IsDBNull(4) ? null :
reader.GetString(4),
Row = reader.IsDBNull(5) ? 0 : reader.GetInt32(5),
MissionSection = reader.IsDBNull(6) ? 0 :
reader.GetInt32(6),
});
}
}
}
}
return missions;
}
public static void CompleteMission(string missionID)
{
using (var connection = new SqliteConnection(dbPath))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "UPDATE Mission " +
"SET IsCompleted = 1 " +
"WHERE MissionID = @MissionID ";
command.Parameters.AddWithValue("@MissionID", missionID);
command.ExecuteNonQuery();
}
connection.Close();
}
}
public static List<Country> GetAllCountryList()
{
List<Country> countries = new List<Country>();
using (var connection = new SqliteConnection(dbPath))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT CountryID " +
" ,CountryName " +
" ,StateCode " +
" ,StatusCode " +
" ,CountryRow " +
"FROM Country " +
"ORDER BY CountryRow ";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
countries.Add(new Country
{
CountryID = reader.GetInt32(0),
ContryName = reader.IsDBNull(1) ? null :
reader.GetString(1),
StateCode = reader.GetInt32(2),
StatusCode = reader.GetInt32(3),
CountryRow = reader.GetInt32(4)
});
}
}
}
connection.Close();
}
return countries;
}
public static City GetRandomCityDataByCountryID(int countryID)
{
City city = new City();
using (var connection = new SqliteConnection(dbPath))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT TOP(1) CityID " +
" ,StateCode " +
" ,StatusCode " +
" ,CityName " +
" ,CountryID " +
"FROM City " +
"WGERE CountryID = @CountryID ";
command.Parameters.AddWithValue("@CountryID", countryID);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
return new City
{
CityID = reader.GetInt32(0),
StateCode = reader.GetInt32(1),
StatusCode = reader.GetInt32(2),
CityName = reader.IsDBNull(3) ? null :
reader.GetString(1),
CountryID = reader.GetInt32(4)
};
}
}
}
connection.Close();
}
return city;
}
