[C#] [MYSQL] Connect C# script to MYSQL

[DrMagus5]

Thx very much guys for the other answer, I was able to create a system to kick/ban/teleport/etc…
Now I have another question, I can take all the data in the game and use this data perfectly, thx to:

[How-to] Setting up C# and creating a basic resource

C# development is now officially covered in the documentation of FiveM; But feel free to use the old guide, keep it mind that this is no longer maintained and potentially might not work anymore.

Old guide

Since I havent found any guides on how to create a resource using C# (on this forum atleast) I’ve decided to pass on the knowledge on how to properly setup the C# environment and create a simple script with it. Not that it’s hard to do so. My goal is to cover as much of the C# basics, if s…

@Syntasu @Briglair @element.22 @304bl hope I written everyone to thx them.

But now I want to store that data and I don’t know hot to connect to MYSQL.
I know a bit of MYSQL but I never connected the database to a script.

Is there any guide about that? I need external plugin/dll or everyting is provided by the FX server?
I need something like that?

[Release] MySQL Async Library - 3.3.2

MySQL Async Library You can find it here with all the instructions : https://github.com/brouznouf/fivem-mysql-async This lib does not replace EssentialMode, it offers however async methods and compatible API for people only needing Essential Mode for MySQL The current API should be stable, but it may change before a first stable release. Feel free to raise Issues/Pull request on Github Current features Async method for mysql by using a callback Sync method also available but not recommended …

Thx very much again guys .

[Syntasu]

This should work just fine one the server, The MySQL Async library also uses this under the hood.

http://zetcode.com/db/mysqlcsharptutorial/

[DrMagus5]

Thx very much @Syntasu :D! I’ll try that :D!

[DrMagus5]

Hello Syntasu, I tryed to follow that great guide but when I use the code I get the error :

Server stack trace:
  at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR (System.Int32 errorCode) [0x0000a] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0
  at (wrapper cominterop) CitizenFX.Core.IScriptHost:OpenHostFile (string)
  at (wrapper cominterop-invoke) CitizenFX.Core.IScriptHost:OpenHostFile (string)
  at CitizenFX.Core.MonoScriptRuntime+WrapScriptHost.OpenHostFile (System.String fileName) [0x00000] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\MonoScriptRuntime.cs:254
  at (wrapper remoting-invoke-with-check) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (string)
  at (wrapper xdomain-dispatch) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (object,byte[]&,byte[]&,string)

Exception rethrown at [0]:
  at (wrapper xdomain-invoke) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (string)
  at CitizenFX.Core.InternalManager.LoadAssembly (System.String name) [0x00000] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\InternalManager.cs:112
Exception loading assembly MySql.Data: System.IO.FileNotFoundException: Unable to find the specified file.


Server stack trace:
  at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR (System.Int32 errorCode) [0x0000a] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0
  at (wrapper cominterop) CitizenFX.Core.IScriptHost:OpenHostFile (string)
  at (wrapper cominterop-invoke) CitizenFX.Core.IScriptHost:OpenHostFile (string)
  at CitizenFX.Core.MonoScriptRuntime+WrapScriptHost.OpenHostFile (System.String fileName) [0x00000] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\MonoScriptRuntime.cs:254
  at (wrapper remoting-invoke-with-check) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (string)
  at (wrapper xdomain-dispatch) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (object,byte[]&,byte[]&,string)

Exception rethrown at [0]:
  at (wrapper xdomain-invoke) CitizenFX.Core.MonoScriptRuntime+WrapScriptHost:OpenHostFile (string)
  at CitizenFX.Core.InternalManager.LoadAssembly (System.String name) [0x00000] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\InternalManager.cs:112
error in mono_error_set_assembly_load: Could not load file or assembly 'MySql.Data, Version=8.0.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies.

Failed to instantiate instance of script MySqlTut.DBConnect: System.TypeLoadException: Could not load type of field 'MySqlTut.DBConnect:dbConn' (4) due to: Could not load file or assembly 'MySql.Data, Version=8.0.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. assembly:MySql.Data, Version=8.0.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d type:<unknown type> member:<none>
  at System.Reflection.MonoCMethod.InternalInvoke (System.Object obj, System.Object[] parameters) [0x00018] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0
  at System.RuntimeType.CreateInstanceMono (System.Boolean nonPublic) [0x000a8] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0 
  at System.RuntimeType.CreateInstanceSlow (System.Boolean publicOnly, System.Boolean skipCheckThis, System.Boolean fillCache, System.Threading.StackCrawlMark& stackMark) [0x00009] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0
  at System.RuntimeType.CreateInstanceDefaultCtor (System.Boolean publicOnly, System.Boolean skipCheckThis, System.Boolean fillCache, System.Threading.StackCrawlMark& stackMark) [0x00027] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0
  at System.Activator.CreateInstance (System.Type type, System.Boolean nonPublic) [0x00020] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0
  at System.Activator.CreateInstance (System.Type type) [0x00000] in <0123fd5b1a1040fe9d70a7e0d4b28acb>:0
  at CitizenFX.Core.InternalManager.CreateAssemblyInternal (System.Byte[] assemblyData, System.Byte[] symbolData) [0x00069] in C:\gl\builds\4ff63adb\0\cfx\fivem\code\client\clrcore\InternalManager.cs:78

The same of this guy:

[HELP] [FXServer] MySql.Data.dll official connector throwing IndexOutOfRangeException

I have been trying to get the official Oracle MySql connector to work in C# in FiveM’s new FXServer for a while. It loads fine, but errors out on the simplest example possible; see below. The connection string is 100% a valid connection string, and works without any issues outside of FXServer in a standalone project. The source is included, but for convenience, here is the source I use in the example: using CitizenFX.Core; using MySql.Data.MySqlClient; namespace MySqlOfficialConnectorError {…

I get this error when I try to write :

    : BaseScript

The full code is this:

using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

using CitizenFX.Core;
using CitizenFX.Core.Native;

namespace MySqlTut
{
    public class DBConnect : BaseScript
    {


        //database stuff
        private const String SERVER = "127.0.0.1";
        private const String DATABASE = "test";
        private const String UID = "root";
        private const String PASSWORD = "";
        private static MySqlConnection dbConn;

        // User class stuff
        public int Id { get; private set; }

        public String Username { get; private set; }

        public String Password { get; private set; }

        public DBConnect(){
        }


        private DBConnect(int id, String u, String p)
        {
            Id = id;
            Username = u;
            Password = p;
        }

        public static void InitializeDB()
        {
            MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
            builder.Server = SERVER;
            builder.UserID = UID;
            builder.Password = PASSWORD;
            builder.Database = DATABASE;

            String connString = builder.ToString();

            builder = null;

            Console.WriteLine(connString);

            dbConn = new MySqlConnection(connString);

        }

        public static List<DBConnect> GetUsers()
        {
            List<DBConnect> users = new List<DBConnect>();

            String query = "SELECT * FROM users";

            MySqlCommand cmd = new MySqlCommand(query, dbConn);

            dbConn.Open();

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                int id = (int)reader["id"];
                String username = reader["username"].ToString();
                String password = reader["password"].ToString();

                DBConnect u = new DBConnect(id, username, password);

                users.Add(u);
            }

            reader.Close();

            dbConn.Close();

            return users;
        }

        public static DBConnect Insert(String u, String p)
        {
            String query = string.Format("INSERT INTO users(username, password) VALUES ('{0}', '{1}')", u, p);

            MySqlCommand cmd = new MySqlCommand(query, dbConn);

            dbConn.Open();

            cmd.ExecuteNonQuery();
            int id = (int)cmd.LastInsertedId;

            DBConnect user = new DBConnect(id, u, p);

            dbConn.Close();

            return user;

        }
        public void Update(string u, string p)
        {
            String query = string.Format("UPDATE users SET username='{0}', password='{1}' WHERE ID={2}", u, p, Id);

            MySqlCommand cmd = new MySqlCommand(query, dbConn);

            dbConn.Open();

            cmd.ExecuteNonQuery();

            dbConn.Close();
        }

        public void Delete()
        {
            String query = string.Format("DELETE FROM users WHERE ID={0}", Id);

            MySqlCommand cmd = new MySqlCommand(query, dbConn);

            dbConn.Open();

            cmd.ExecuteNonQuery();

            dbConn.Close();
        }
    }
}

[Liam1]

Does anyone know how I do this with LUA code

[DrMagus5]

I didn’t tried but for Lua you can find this : [Release] MySQL Async Library - 3.3.1

But for c# there is nothing about.

[Liam1]

Ok thanks but not sure what I am looking for or how this help?

[DrMagus5]

There is a guide on how to use and what you need to write, all the steps : https://github.com/brouznouf/fivem-mysql-async/blob/master/README.md

[Liam1]

TRhanks but how do I use the indetifyer to identify people by there id

[Liam1]

Also can I do this client side or does it have to be server side?

[Liam1]

I tried all different way I justcant figure how to do it

[DrMagus5]

For this you should watch how to script in Lua, this is specific topic for mysql (Im talking about the id), that should be done in Lua but I just know c# I never learned Lua. About mysql technically I think could be done also client side but normally is done server side.

Hope I answered all the question even if I don’t know how to script in Lua. Anyway for Lua I suggest to open a specific topic, or a moderator will stop us to talk for off topic (remember to put also in the topic [Lua] as language like I wrote [C#].

[304bl]

hi friendly dev !!

if you want to use mysql with c# you should use this library : https://github.com/mysql-net/MySqlConnector

that’s the one i use , and it work perfectly.
bad point you have to write your own mysql manager with it.

and for make it work you have to put few dll on the same folder of your script otherwise you will have IOFileNotFoundException

the dll are :
MySqlConnector.dll
System.Buffers.dll
System.Runtime.InteropServices.RuntimeInformation.dll
System.Threading.Tasks.Extensions.dll

good luck !

ps: C# is the best

Credit to @boron who gave me the link of the lib

[DrMagus5]

@304bl

mysql manager

Thx very much 304bl I remember you also answered some questions about the trigger when I was tring for the first time to communicate from client to server in C# :D!

Anyway I tried to compile the MySqlConnector but I get this error:

Gravità	Codice	Descrizione	Progetto	File	Riga	Stato eliminazione
Errore		Il pacchetto Microsoft.AspNetCore.Mvc 2.0.0 non è compatibile con netcoreapp2.0 (.NETCoreApp,Version=v2.0). Il pacchetto Microsoft.AspNetCore.Mvc 2.0.0 supporta: netstandard2.0 (.NETStandard,Version=v2.0)

He say that Microsoft.AspNetCore.Mvc 2.0.0 is not compatible with netcoreapp2.0 but with netstandard2.0 … what I should do XD?

[304bl]

i don’t know what is that error , can you check the framework you are using for build your project ?

i’m using the framework 4.5 , if your is higher try to set it to 4.5 and try again.

yea i remember , glad to see you keep coding ^^

[DrMagus5]

@304bl

i don’t know what is that error , can you check the framework you are using for build your project ?

i’m using the framework 4.5 , if your is higher try to set it to 4.5 and try again.

yea i remember , glad to see you keep coding ^^

The strange problem is that: when I go to compile the source code the framework is like locked, the area where you can change the framework is grey and I can’t change it. In the other projects I can see and I can change it. Now I’m updating Visual Studio, it will took a bit but maybe will fix it…

[304bl]

before adding the mysqlconnector to the ref of your project you didn’t had this issue ?

[DrMagus5]

I mean when I try to compile the mysqlconnector (the link you provided), this source code : https://github.com/mysql-net/MySqlConnector

I opened the MySqlConnector.sln , and tried to compile to get the .dll .

[304bl]

maybe you choose a wrong kind of project in VS when you create one , i don’t know

[304bl]

ohhh i see , wait i 'll send you a link with the 4 dll needed

http://mind2world.com/dl/mysqlConnector.rar

don’t forget to put them in your script folder