1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. GTA Network forum is now in archive mode.

agrSQL - SQL class allows to handle MySQL database

Discussion in 'Scripts' started by AgresivD, Mar 7, 2017.

  1. AgresivD

    AgresivD Member

    Messages:
    8
    Likes Received:
    14
    Joined:
    Jan 12, 2017
    agrSQL - SQL class allows to handle the MySQL database
    Introduction
    Hello everyone,
    First of all I would like to say that this is the first time I release a script related GTANetwork,
    so please go easy on me with the comments.

    This class provides some OOP functions to communicate with MySQL and run queries on it.
    At this point it's somewhat basic but I think it's useful for most of the queries you would normally run on an avarage GTANetwork server database.

    It is build to work with MySql.Data.dll, That means that it can run unthreaded queries, threaded queries and parallel queries with the ability to pass
    the result to a specified callback.

    Installation

    Like any other class, place the agrSQL.cs & MySql.Data.dll inside your resource project and add to your meta.xml this lines:
    PHP:
      <script src="agrsSQL.cs" type="server" lang="csharp" />
      <
    export class="agrSQL" function="Connect" />
      <
    export class="agrSQL" function="Query" />
      <
    export class="agrSQL" function="Condition" />
      <
    export class="agrSQL" function="Select" />
      <
    export class="agrSQL" function="ReadTable" />
      <
    export class="agrSQL" function="Update" />
      <
    export class="agrSQL" function="Count" />
      <
    export class="agrSQL" function="Insert" />
      <
    export class="agrSQL" function="Delete" />
      <
    export class="agrSQL" function="Disconnect" />
    The first line Links a agrSQL.cs script file with your resource,
    and the other lines Used to export agrSQL class functions for other resources to use.

    [NOTE]: All these functions are in class agrSQL and without their statement through the "export" You may get error when use of them.

    Functions
    The script provides functions that will help you build insert, delete, update and select queries.
    Here's a list of all the functions this script provides:
    PHP:
    public bool Connect(string serverstring databasestring uidstring passwd)
    public 
    object Query(string querybool ret false)
    public List<
    Dictionary<stringobject>> Select(string tablestring condition ""string key "*")
    public 
    DataTable ReadTable(ref DataSet dsstring tablestring condition ""string key "*")
    public 
    void Update(string tablestring conditionparams KeyValuePair<stringobject>[] list)
    public 
    int Count(string tablestring condition "")
    public 
    void Insert(string tableparams KeyValuePair<stringobject>[] list)
    public 
    void Delete(string tablestring condition)
    public 
    void Disconnect()
    Usage Concept
    The idea is very simple, you must first create an object that Define and through it you can take any action with database.
    In addition, you can create several connections simultaneously in different databases.
    Then you can make use of any of the functions listed above.
     
    Amit_B likes this.
  2. AgresivD

    AgresivD Member

    Messages:
    8
    Likes Received:
    14
    Joined:
    Jan 12, 2017
    Examples
    Consider the following table in some database:
    we'll call it users.
    After you do that, define the agrSQL object someware in your code:
    PHP:
    agrSQL sqlLink = new agrSQL();
    Now you ready to use all these agrSQL functions.

    • Connect
    Creates a direct connection for the purpose of reading and writing to the database.
    PHP:
    sqlLink.Connect("server""database""user""password");
    Another form of use, Define these lines in your meta.xml:
    PHP:
      <settings>
        <
    setting name="agrsql_server" value="your_server_ip" default="127.0.0.1"/>
        <
    setting name="agrsql_password" value="your_password" default=""/>
        <
    setting name="agrsql_uid" value="your_user" default="root"/>
        <
    setting name="agrsql_db" value="your_database" default="" />
      </
    settings>
    The API of GTANetwork offers the possibility to read these data with using the function API.getSetting. Your code should be like that:
    PHP:
    string Server API.getSetting<string>("agrsql_server"),
        
    DB API.getSetting<string>("agrsql_db"),
        
    Password API.getSetting<string>("agrsql_password"),
        
    UID API.getSetting<string>("agrsql_uid");
    sqlLink.Connect(ServerDBUIDPassword);
    agrSQL.Connect returns true and print the SQL Version of your database, otherwise return false if the connection fails

    • Query
    Sends a command to the database.

    I chose to leave that function public and not private uses of different programmers can do.
    Such as running a few queries at the same time:
    PHP:
    string query1 "INSERT INTO `users` (`Name`,`Password`,`Score`) VALUES('Test','123456','10');";
    string query2 "INSERT INTO `users` (`Name`,`Password`,`Score`) VALUES('Test1','123321','0');";
    sqlLink.Query(query1 query2string.Empty);
    This code add 2 new rows in table "users".

    • Select
    Select statement for SQL.
    PHP:
    List<Dictionary<stringobject>> result;
    result sqlLink.Select("users""`Name` = 'AgresivD'""ID");
    API.consoleOutput("there are {0} results: "result.Count);
    for (
    int i 0result.Countxi++)
        
    API.consoleOutput(result[i]["ID"] + " ");
    This codes runs a search in users table and find the all users that have the Name "AgresivD", after that its output how much results founds and prints are ID`s.
    With use Key parameter as a function define above ^ we must ask what values we want to retrieve the results we received, so the use of the following:
    PHP:
    result sqlLink.Select("users""`Name` = 'AgresivD'""Name,Score");
    Now use the following code will properly:
    PHP:
    API.consoleOutput(result[i]["Name"] + ":" result[i]["Score"]);
    Use the following form function will let you read the entire table "users" and you will be able to retrieve the key parameter values you typed "Name,Score,Password,ID"
    PHP:
    result sqlLink.Select("users"string.Empty, "Name,Score,Password,ID");
    [NOTE]: Attempt to extract value was not specified in the key parameter will bring the error.

    • ReadTable
    Select statement for SQL based on DataSet.
    PHP:
    public void LoadHouses()
    {
        
    DataSet dsResVar = new DataSet();
        
    sqlLink.ReadTableref dsResVar"houses"string.Empty, "id,x,y,z,owner,status");
        for(
    int i 0sqlLink.Count("houses"); ki++)
        {
            
    House addNew = new House(
                (int)
    dsResVar["houses"].Rows[i]["id"],
                
    Convert.ToDouble(dsResVar.Tables["houses"].Rows["i"]["x"]),
                
    Convert.ToDouble(dsResVar.Tables["houses"].Rows["i"]["y"]),
                
    Convert.ToDouble(dsResVar.Tables["houses"].Rows["i"]["z"]),
                
    Owners.FoundID((int)dsResVar["houses"].Rows[i]["owner"]),
                (int)
    dsResVar["houses"].Rows[i]["status"]);
            
    Houses.Add(addNew);
            
    API.consoleOutput("Loaded house: " + (1) + " / " k);
        }
    }
    LoadHouses function simulates the use of loading houses in the game, so you can see the form of the use of ReadTable, very useful function.

    • Update
    Updates the database query accordance with the condition.
    PHP:
    sqlLink.Update("users""`Name` = 'AgresivD'",
        new 
    KeyValuePair<stringobject>("Password""1Ch25D"),
        new 
    KeyValuePair<stringobject>("Score""50"));
    This example finds the user have the Name "AgresivD" and change he's password & score.

    • Count
    Returns the number of rows in accordance with the condition.
    PHP:
    int countUsers sqlLink.Count("users");
    API.consoleOutput("users: {0} "countUsers);
    This example returns how much rows "users" table contains.
    Another use could be to do with this function:
    PHP:
    int countUser sqlLink.Count("users""`Name` = 'AgresivD'");
    if(
    countUser 0API.consoleOutput("AgresivD exists in users {0}"countUser);
    else 
    API.consoleOutput("AgresivD not exists in users");
    • Insert
    Adds new entries to the table.
    PHP:
    sqlLink.Insert("users", new KeyValuePair<stringobject>("Name""Dan"),
        new 
    KeyValuePair<stringobject>("Password""123456"),
        new 
    KeyValuePair<stringobject>("Score""30"));
    This example add new row to table users with Name = Dan and Password = 123456 and Score = 30.

    • Delete
    Deleting row from table in accordance with the condition.
    PHP:
    sqlLink.Delete("users"string.Empty);
    This example remove all rows in table "users".
    PHP:
    sqlLink.Delete("users""`Score` > '50'");
    This example remove all rows that have score greater then 50 from table "users"

    • Disconnect
    Severing the connection between the database to software.
    PHP:
    sqlLink.Disconnect();
    This close the connect between server to database, after you do this you can create new connection with using sqlLink object that you already create.

    Links
    SQL Tutorial
    agrSQL github page



    * I found no bugs tests done, should you find one you are welcome to inform me about it. *
    - I am in the process of writing "houses system" fully realize the agrSQL class. -soon...
    - Credit to Amit_B for Improve and took part in the development.
     
    lRoberth, Amit_B and Mickail like this.
  3. MrBoolean

    MrBoolean New Member Donator

    Messages:
    3
    Likes Received:
    2
    Joined:
    Mar 4, 2017
    Looks awesome - I'll definitely be checking it out.
     
    AgresivD likes this.
  4. Petoche

    Petoche Member

    Messages:
    17
    Likes Received:
    1
    Joined:
    Mar 11, 2017
    Hi, seems pretty cool but i got some error when i start the server if you can help.

    missing type or namespace for "Data" "MySql" "MySqlConnection" "DataSet" "DataTable"

    Fixed :

    add
    <assembly ref="System.Data.dll"/>
    <assembly ref="MySql.Data.dll"/>
    <assembly ref="System.Xml.dll"/>

    to your meta.xml
     
    Last edited: Mar 12, 2017
  5. AgresivD

    AgresivD Member

    Messages:
    8
    Likes Received:
    14
    Joined:
    Jan 12, 2017
    You're right, I missed it. Thank you
     
  6. lRoberth

    lRoberth Member

    Messages:
    13
    Likes Received:
    1
    Joined:
    Apr 10, 2017
    The GitHub link is dead
     
  7. leejewitt

    leejewitt Member

    Messages:
    29
    Likes Received:
    0
    Joined:
    Apr 2, 2017
  8. Hansrutger

    Hansrutger Well-Known Member Donator

    Messages:
    114
    Likes Received:
    36
    Joined:
    Mar 4, 2017
    https://github.com/Agresiv/IDASQL

    Think he updated it to version 2.0 and called it something else, seems to be the same (similar) thing anyways.
     
  9. leejewitt

    leejewitt Member

    Messages:
    29
    Likes Received:
    0
    Joined:
    Apr 2, 2017
    Thanks
     
  10. Draex

    Draex Active Member

    Messages:
    64
    Likes Received:
    31
    Joined:
    Apr 2, 2017
    You should use select and query carefully, because of possible sql injections. I'd rather recomend using prepared mysql statements.
    Since usernames can be anything you must consider names like " ';DROP TABLE users --", which can delete your entire user database.
     

Share This Page