📄 backenddb.cs
字号:
using System;
using System.Data;
using System.Windows.Forms;
using Npgsql;
namespace CO_Full_Server
{
/// <summary>
/// Summary description for BackendDB.
/// </summary>
public class BackendDB : System.Object
{
private static NpgsqlConnection m_Database;
public BackendDB()
{
//
// TODO: Add constructor logic here
//
}
//Sets the application's database
public static void SetDB(NpgsqlConnection DB)
{
m_Database = DB;
}
public static bool StartDefault(string UserName, string Password)
{
UserName = MakeSafeString(UserName);
Password = MakeSafeString(Password);
try
{
m_Database = new NpgsqlConnection("server=127.0.0.1;user id=" + UserName + ";password=" + Password + ";database=Conquer;ssl=no");
m_Database.Open();
return true;
}
catch (Exception)
{
return false;
}
}
public static NpgsqlConnection Database()
{
return m_Database;
}
public static bool IsDBNull(ref object Data)
{
if (Data.GetType().ToString() == "System.DBNull")
{
return true;
}
else
{
return false;
}
}
public static void BuildTables()
{
DataSet DS = new System.Data.DataSet();
NpgsqlDataAdapter DA = new NpgsqlDataAdapter("SELECT name FROM sqlite_master WHERE type = 'table' and name = 'ITEMS'", m_Database);
DA.Fill(DS, "Items");
if (DS.Tables["Items"].Rows.Count > 0)
{
}
else
{
NpgsqlCommand Com = new NpgsqlCommand("CREATE TABLE ITEMS (ItemID INTEGER, ItemName TEXT, " +
"JobReq INTEGER, ProfReq INTEGER, LvlReq INTEGER, StrReq INTEGER, DexReq INTEGER, StamReq INTEGER, " +
"ManaReq INTEGER, U1 TEXT, U2 TEXT, Price INTEGER, U3 TEXT, MaxAttack INTEGER, MinAttack INTEGER, " +
"Def INTEGER, Dex INTEGER, Dodge INTEGER, Health INTEGER, Mana INTEGER, U4 TEXT, U5 TEXT, U6 TEXT, U7 TEXT, " +
"MagicAttack INTEGER, MagicDefense INTEGER, Range INTEGER, Freq INTEGER, Description TEXT) ", m_Database);
Com.ExecuteNonQuery();
}
//0-3 4 Item ID
//4-19 16 Item Description (8 bit ASCII)
//20 1 Profession Requirement
//21 1 Proficiency Requirement
//22-23 2 Level Requirement
//24-25 2 Strength Requirement
//26-27 2 Dexterity Requirement
//28-29 2 Stamina Requirement (always 0)
//30-31 2 Mana Requirement (always 0)
//32-33 2 Unknown1
//34-35 2 Unknown2
//36-39 4 Price
//40-43 4 Unknown3
//44-45 2 Max Attack
//46-47 2 Min Attack
//48-49 2 Defense
//50-51 2 Dexterity
//52-53 2 Dodge
//54-55 2 Health
//56-57 2 Mana
//58-59 2 Unknown4
//60-61 2 Unknown5
//62-65 4 Unknown6
//66-67 2 Unknown7
//68-69 2 Magic Attack
//70-71 2 Magic Defense
//72-73 2 Range
//74-75 2 Frequency (value *.064 = Displayed)
//76-203 128 Description
DA = new NpgsqlDataAdapter("SELECT name FROM sqlite_master WHERE type = 'table' and name = 'CLASSMAST'", m_Database);
DA.Fill(DS, "ClassMast");
if (DS.Tables["ClassMast"].Rows.Count > 0)
{
}
else
{
NpgsqlCommand Com = new NpgsqlCommand("CREATE TABLE CLASSMAST ( ClassID INTEGER PRIMARY KEY, Description TEXT )", m_Database);
Com.ExecuteNonQuery();
}
DA = new NpgsqlDataAdapter("SELECT name FROM sqlite_master WHERE type = 'table' and name = 'CLASSITEMS'", m_Database);
DA.Fill(DS, "ClassItems");
if (DS.Tables["ClassItems"].Rows.Count > 0)
{
}
else
{
NpgsqlCommand Com = new NpgsqlCommand("CREATE TABLE CLASSITEMS (UID INTEGER PRIMARY KEY, ClassID INTEGER, ItemID INTEGER)", m_Database);
Com.ExecuteNonQuery();
}
//"CREATE TABLE PROJECTS " & _
//"(" & _
//" projectname TEXT, " & _
//" qbjobid NUMERIC, " & _
//" qbitemid NUMERIC, " & _
//" billableflag NUMERIC, " & _
//" suppressfromreporting NUMERIC, " & _
//" projectid INTEGER PRIMARY KEY AUTOINCREMENT, " & _
//" commentinglevel NUMERIC" & _
//") ", DataBase)
}
public static int AuthenticateUser(string UserName, string Password, string IPAddress)
{
UserName = MakeSafeString(UserName);
Password = MakeSafeString(Password);
NpgsqlDataAdapter DA = new NpgsqlDataAdapter("SELECT * FROM \"Account\" WHERE \"AccountID\" = '" + UserName + "' AND \"Password\" = '" + Password + "'", m_Database);
DataSet DS = new DataSet();
DA.Fill(DS, "Authentication");
if (DS.Tables["Authentication"].Rows.Count == 1)
{
NpgsqlCommand Com = new NpgsqlCommand("UPDATE \"Account\" SET \"LastLogon\" = CURRENT_TIMESTAMP, \"LogonCount\" = \"LogonCount\" + 1, \"IPAddress\" = '" + IPAddress + "/32' WHERE \"AccountID\" = '" + UserName + "' AND \"Password\" = '" + Password + "'", m_Database);
Com.ExecuteNonQuery();
int Auth = Convert.ToInt32(DS.Tables["Authentication"].Rows[0]["LogonAction"]);
Auth = Auth | Convert.ToInt32(DS.Tables["Authentication"].Rows[0]["Flags"]);
return Auth;
}
else
{
DA = new NpgsqlDataAdapter("SELECT * FROM \"Account\" WHERE \"AccountID\" = '" + UserName + "'", m_Database);
DS = new DataSet();
DA.Fill(DS, "Authentication");
if (DS.Tables["Authentication"].Rows.Count == 1)
{
if (DS.Tables["Authentication"].Rows[0]["Password"].ToString().Trim() == "")
{
NpgsqlCommand Com = new NpgsqlCommand("UPDATE \"Account\" SET \"Password\" = '" + Password + "' WHERE \"AccountID\" = '" + UserName + "'", m_Database);
Com.ExecuteNonQuery();
int Auth = Convert.ToInt32(DS.Tables["Authentication"].Rows[0]["LogonAction"]);
Auth = Auth | Convert.ToInt32(DS.Tables["Authentication"].Rows[0]["Flags"]);
return Auth;
}
else
{
return 0;
}
}
else
{
return 0;
}
}
}
public static string LookupServer(string ServerName)
{
ServerName = MakeSafeString(ServerName).ToLower();
NpgsqlDataAdapter DA = new NpgsqlDataAdapter("SELECT \"IPAddress\" FROM \"Servers\" WHERE \"ServerName\" = '" + ServerName + "'", m_Database);
DataSet DS = new DataSet();
DA.Fill(DS, "Server");
if (DS.Tables["Server"].Rows.Count == 1)
{
return (string)DS.Tables["Server"].Rows[0]["IPAddress"];
}
else
{
return "129.101.89.126";
}
}
public static bool CreateCharacter(string CharacterName, uint Mesh, uint Class, string Account)
{
Stats DefaultStats;
int DBMesh = 0;
if (Mesh == 0x03eb)
DBMesh = 0x000a3d1b;
else if (Mesh == 0x03ec)
DBMesh = 0x000a3d1c;
else if (Mesh == 0x07d1)
DBMesh = 0x001eb361;
else if (Mesh == 0x07d2)
DBMesh = 0x001eb362;
if (DBMesh == 0)
return false;
if (Class == 0x0a || Class == 0x14 || Class == 0x28 || Class == 0x64)
{
DefaultStats = GetDefaultStats(1, Class);
}
else
return false;
CharacterName = MakeSafeString(CharacterName);
Account = MakeSafeString(Account);
NpgsqlCommand Com = new NpgsqlCommand("INSERT INTO \"Characters\" (\"Account\"," +
" \"Name\", \"Level\", \"Class\", \"Model\", \"Strength\", \"Agility\", \"Spirit\"," +
" \"Vitality\") VALUES ('" + Account + "', '" + CharacterName + "', 1, " + Class + ", " +
DBMesh + ", " + DefaultStats.Strength + ", " + DefaultStats.Agility + ", " +
DefaultStats.Spirit + ", " + DefaultStats.Vitality + ")", m_Database);
try
{
Com.ExecuteNonQuery();
Com = new NpgsqlCommand("UPDATE \"Account\" SET \"LogonAction\" = 1 WHERE \"AccountID\" = '" + Account + "'", m_Database);
Com.ExecuteNonQuery();
return true;
}
catch (Exception)
{
return false;
}
}
public static string MakeSafeString(string Input)
{
string NewString;
NewString = Input.Replace("'", "''");
NewString = NewString.Replace("\"", "\"\"");
NewString = NewString.Replace("/*", "");
NewString = NewString.Replace("*/", "");
return NewString;
}
public static bool CharExists(string CharacterName)
{
CharacterName = MakeSafeString(CharacterName);
NpgsqlDataAdapter DA = new NpgsqlDataAdapter("SELECT * FROM \"Characters\" WHERE \"Name\" = '" + CharacterName + "'", m_Database);
DataSet DS = new DataSet();
DA.Fill(DS, "Character");
if (DS.Tables["Character"].Rows.Count >= 1)
{
return true;
}
else
return false;
}
public static Stats GetDefaultStats(int Level, uint Class)
{
Stats DefaultStats = new Stats();
Class = Class / 10;
if (Class == 1 || Class == 2 || Class == 4 || Class == 10 || Class == 13 || Class == 14)
{
NpgsqlDataAdapter DA = new NpgsqlDataAdapter("SELECT * FROM \"DefaultStats\" WHERE \"Level\" = '" + Level +
"' AND \"Class\" = " + Class, m_Database);
DataSet DS = new DataSet();
DA.Fill(DS, "Stats");
if (DS.Tables["Stats"].Rows.Count == 1)
{
DefaultStats.Strength = (int)DS.Tables["Stats"].Rows[0]["Strength"];
DefaultStats.Agility = (int)DS.Tables["Stats"].Rows[0]["Agility"];
DefaultStats.Vitality = (int)DS.Tables["Stats"].Rows[0]["Vitality"];
DefaultStats.Spirit = (int)DS.Tables["Stats"].Rows[0]["Spirit"];
}
}
return DefaultStats;
}
public static Character[] GetChars(string AccountID)
{
Character[] Chars;
NpgsqlDataAdapter DA = new NpgsqlDataAdapter("SELECT * FROM \"Characters\" WHERE \"Account\" = '" + AccountID +
"'", m_Database);
DataSet DS = new DataSet();
DA.Fill(DS, "Characters");
int x = 0;
Chars = new Character[DS.Tables["Characters"].Rows.Count];
foreach (DataRow DR in DS.Tables["Characters"].Rows)
{
Chars[x] = new Character();
Chars[x].Class = Character.ClassFromID((int)DR["Class"]);
Chars[x].Model = (int)DR["Model"];
Chars[x].Name = ((string)DR["Name"]).Trim();
Chars[x].CharID = Convert.ToUInt32((int)DR["CharacterID"]);
x++;
}
return Chars;
}
public static Character GetFullChar(uint CharID)
{
Character Char = new Character();
NpgsqlDataAdapter DA = new NpgsqlDataAdapter("SELECT * FROM \"Characters\" WHERE \"CharacterID\" = '" + CharID +
"'", m_Database);
DataSet DS = new DataSet();
DA.Fill(DS, "Character");
if (DS.Tables["Character"].Rows.Count > 0)
{
DataRow DR = DS.Tables["Character"].Rows[0];
Location CurrentLoc = Char.CurrentLoc;
Char.Class = Character.ClassFromID((int)DR["Class"]);
Char.Model = (int)DR["Model"];
Char.Name = ((string)DR["Name"]).Trim();
Char.CharID = Convert.ToUInt32((int)DR["CharacterID"]);
Char.PKPoints = (int)DR["PKPoints"];
Char.Level = (short)DR["Level"];
CurrentLoc.X = (int)DR["LocationX"];
CurrentLoc.Y = (int)DR["LocationY"];
Char.CurrentLoc = CurrentLoc;
Char.Money = (Int64)DR["Money"];
Char.Map = (World.Maps)(Convert.ToUInt32((int)DR["Map"]));
Char.Exp = (int)DR["Experience"];
Char.HP = (int)DR["HP"];
Char.MP = (int)DR["MP"];
Char.PKPoints = (int)DR["PKPoints"];
Char.StatPoints = (short)DR["StatPoints"];
Char.Strength = (int)DR["Strength"];
Char.Mana = (int)DR["Spirit"];
Char.Dexterity = (int)DR["Agility"];
Char.Vitality = (int)DR["Vitality"];
}
else
{
throw new Exception("Character not found in database");
}
return Char;
}
public static void SynchronizeCharacter(Character Char)
{
string SQL = "UPDATE \"Characters\" SET \"Class\" = '" + (uint)(Char.Class) + "', \"Model\" = " + Char.Model +
", \"Name\" = '" + Char.Name + "', \"PKPoints\" = " + Char.PKPoints + ", \"Level\" = " + Char.Level +
", \"LocationX\" = " + Char.CurrentLoc.X + ", \"LocationY\" = " + Char.CurrentLoc.Y + ", \"Money\" = "
+ Char.Money + ", \"Map\" = " + (uint)(Char.Map) + ", \"Experience\" = " + Char.Exp + ", \"HP\" = " + Char.HP +
", \"MP\" = " + Char.MP + ", \"StatPoints\" = " + Char.StatPoints + ", \"Strength\" = " + Char.Strength +
", \"Spirit\" = " + Char.Mana + ", \"Agility\" = " + Char.Dexterity + ", \"Vitality\" = " + Char.Vitality +
" WHERE \"CharacterID\" = " + (uint)(Char.CharID - 0x00120000);
//SQL = MakeSafeString(SQL);
NpgsqlCommand Com = new NpgsqlCommand(SQL, m_Database);
Com.ExecuteNonQuery();
}
public static void SetStatus(string Account, int Status)
{
try
{
Account = MakeSafeString(Account);
string SQL = "UPDATE \"Account\" SET \"LogonAction\" = " + Status + " WHERE \"AccountID\" = '" + Account + "'";
NpgsqlCommand Com = new NpgsqlCommand(SQL, m_Database);
Com.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}
}⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -