trolovecro Posted August 28, 2022 Posted August 28, 2022 (edited) CREATING SERVER SIDE SCRIPT USING ENTITY FRAMEWORK CORE AND POSTGRESQL - Intro After little bit of researching, and learning a bit more about EF core, I made a decision to create tutorial how to implement it in RAGE-MP script with PostgreSQL. First of all... This tutorial may not be perfect, but all members can write ideas and different approaches. If we all find it better, I will modify this tutorial. Also, this tutorial is not made for complete new guys in programming and scripting in RAGE-MP. Not because I think someone cant do it, but this is a bit complex and may not be perfect for new guys... After all, all are welcome to try and I will try to give the answer on any question you submit. - Entity Framework Core Intro I will try to simplife this story behind Entity Framework Core. For much more details you can Google it and find it on Microsoft official page. Micosoft Entity Framework In my words: Entity Framework Core is data access technology which help us by-pass writing queries to database and make our project cleaner. EF Core is using model type approache where we write our models in the shape of c# classes. In this way EF Core will generate all necessary queries to create new table in context of our model written in c# with neccessery primary key and foreign keys to our database. What is primary and foreign keys ??. This may look to complicated and stupid, and it would be if you are creating simple small scripts in RAGE-MP. Maybe there is no need for this, but I think there is a lot of developers who wants to create some roleplay script which is becoming over complicated in short amount of time. One more thing that is really important before using EF Core. That is LINQ ( Language-Integrated Query ) library. I will not get into it, because its contain a lot of functions and this is not point of this tutorial. Check LINQ here ! - PostgreSQL Database Intro I am not expert in databases and cant tell you which one is better and faster or which one should you use for your project. I prefer PostgreSQL because its FREE and simple to use. Dont care about speed and technical specification. Syntax is similar to SQL (not the same) and most important like I said its free. If you prefer more SQL, MySQL or SQLite you can use it also with EF Core just need to download correct NuGet for your database. In this tutorial I will move fast through this section and show you only how to download PostgreSQL, create instance of your server and couple of simple steps how to update or add new row in your table. So lets start with database first ... - Downloading and installing PostgreSQL - 1. Lets visit PostgreSQL and download installation setup. PostgreSQL Download LINK. Download the installation file and run it. - 2. After download, open it and move through installation setup. Choose your components to be installed. You dont need stack builder so you can uncheck that if you want. - 3. Select your directory for database, press NEXT and then insert your password for super user account. Dont forget that password. You will use it for connecting to database server. - 4. Insert port on which database will run. Default port is 5432, if you want to run it on another port, be sure that its not use by some program. - 5. After all necessary inputs, just press next next next until its finish installation. You will need to restart your PC and its done. - 6. Lets open up pgAdmin that we have installed with postgreSQL. Navigate to installed folder of postgreSQL or write in window search bar "pgAdmin". - 7. You should have one server group called "Servers". Double click on it and pgAdmin will ask you for password. Remember password you wrote in installation setup? Enter that password. - 8. If you typed in correct password you will enter in that server group and find one server instance called "PostgreSQL 14". If you dont have it, you can create your own instance of server. - 9. That should be all from postgreSQL. Images are copied from tutorial "How to Downolad and Install PostgreSQL on Windows" written by Aleksandar Kovačevič. You can also check tutorial on This link to see more information about postgreSQL. - Installing NuGet packages and connecting to postgreSQL. In this tutorial I am using Visual Studio IDE with targeted framework 3.1... I first started with 2.0 because GTANetwork.API, then figured out that I need to make reference to Bootstrap.dll ... Never mind, you will see that I used EF core compatible with netcore 2.0 or greater. Stick with that! Also make sure that every NuGet package for EF Core is the same version! If its not you will have problems at migrations and updating data base. Follow the tutorial... I will make more notes about that through steps. - 1. Open your script in visual studio, In Solution Explorer press right mouse button > Go to "Manage NuGet packages" > Install next packages. - I highlighted versions on right side. You can see that they all have same version. When you open installation window, press dropdown menu and select that version "2.1.0" or if you are using greater netcore like 3.1 you need to check which version of following packages support your targeted framework. On next image I highlighted Dependencies. You see how 2.1.0 is using Version 2.0. This is because my Targeted framework is 2.0. To see your targeted framework press right mouse button in Solution Explorer > Properties > In section "Application/Taget framework" you can see your framework and change it if you want. - 2. Okey, now when we installed everything we need, lets create some code. We will start by creating new class called postgreSQL.cs in constructor we will create try catch block where we will try to connect to our postgreSQL. If we successfully connect to database we will print version of postgreSQL. If not we will print Exception. public string connectionString = "Host=localhost;Username=postgres;Password=mypassword;Database=postgres"; public postgreSQL() { NAPI.Util.ConsoleOutput("Trying to start postgreSQL script ..."); try { var connection = new NpgsqlConnection(connectionString); connection.Open(); var sql = "SELECT version()"; var cmd = new NpgsqlCommand(sql, connection); var version = cmd.ExecuteScalar().ToString(); NAPI.Util.ConsoleOutput($"Succesfully connected to PostgreSQL Version: {version}"); } catch (Exception ex) { NAPI.Util.ConsoleOutput($"Error: Can't connect to the database."); NAPI.Util.ConsoleOutput($"Exception: {ex.Message.ToString()}"); } finally { } } Check variable "connectionString". Its our connection string for postgreSQL. Host is your localhost or "127.0.0.1". Username you can find in pgAdmin clicking right mouse button on server "PostgreSQL 14" and going to Connection section of window. There you can find everything expect password. Password you need to input is password from postgreSQL installation for super user. Be sure to write everything right in connection string. Database we are using is postgres, also default made by postgreSQL. If you want you can create your new database called "Server" or something like that. Be sure that match with connection string database parameter. Also be sure to create instance of this object and call it in OnResourceStart or put it in contructor of starting class... public postgreSQL sqlConnection; public mainScript() { sqlConnection = new postgreSQL(); } WARNING: If you dont want to be asked for password from localhost you can change postgreSQL settings "pg_hba.conf" and below METHOD set everything as "trust". "pg_hba.conf" is in postgreSQL installation folder. In my case: C:\Program Files\PostgreSQL\14\data for more information about postgresql access visit THIS link. - Lets try to connect. If console will print PostgreSQL then we connected to our database. If will print exception. - As you can see I connected successfully to database and it printed installed verison. - 3. Now lets create new folder in our project. Right click on solution explorer > Add > Add new folder. Call it "DataContext". In DataContext folder create "DataContext.cs" class. Paste next code in that class. internal class DataContext : DbContext { public string connectionString = "Host=127.0.0.1;Database=server;Username=postgres;Password=mypassword"; protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseNpgsql(connectionString); } As you can see we have class DataContext that inherit DbContext, again connection string, and override function called "OnConfiguration". For now we dont need to sniff other possibilities... This is all we need for now. We will come back later when we create some models. So lets go... - 5. Create new folder in our project. Right mouse button on Solution Explorer > Add >New Folder. Lets call it "Models". In Models folder lets create classes, Right mouse button on folder "Models" > Add > Class... Lets call it "playerModel.cs", also create one more class and call it "weaponModel.cs". Okey we have some basic models. Copy this code and put inside playerModel and weaponModel. internal class playerModel { [Key] public Guid Id { get; set; } public string socialClubName { get; set; } public string rageName { get; set; } public string characterName { get; set; } public int healthBar { get; set; } public List<weaponModel> playerWeapons; } This is our playerModel properties. We have primary key Id. This is important. Lets say I didnt add Id property... Entity framework would give me a error, because this model doesnt have "Primary key". Also before Id property I added "Key" Decorator. Guid type object is not required. You can use also number. Pay attention to playerWeapons. Its a list that contain weaponModel. So player can have multiple weapons as we already know... Now lets put next code inside your "weaponModel.cs" class. internal class weaponModel { public Guid Id { get; set; } public string weaponName { get; set; } public int weaponAmmo { get; set; } public playerModel playerModel { get; set; } } This is our weaponModel properties. As you can see there is not much inside just few parameters. property playerModel is actually interesting. Why? Because EF Core will automatically create Foreign key that will refer to playerModel Id. This is called 1:n relationships where player can have MANY weapon models, but one weapon model can be owned just by one player! For more you visit This link. - 4. Lets go back to our "DataContext.cs" class. And add this models inside our context. internal class DataContext : DbContext { public DbSet<playerModel> playerModel { get; set; } public DbSet<weaponModel> weaponModel { get; set; } public string connectionString = "Host=127.0.0.1;Database=postgres;Username=postgres;Password=mypassword"; protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseNpgsql(connectionString); } You can see that we added 2 new properties inside over DataContext. Entity Framework will generate all code for us with this models. Its important to add it in DataContext. Not to much to say... If you are interested in what this actually do, google it. Lets move to another part. - 5. Lets create our first migrations. First lets create folder "Migrations" In our project. Right mouse button in Solution explorer > Add > Folder. Lets switch to Package Manager Console. You can open it in next steps... Tools > NuGet Package Manager >Package Manager Console. Should be opened in bottom of your Visual Studio IDE. Okey now lets make some migrations. In your Package Manager Console write next command. "firstMigration" is just a name of this migration. You can call it whatever you want. Add-Migration firstMigration If everything was without error you should see next text. You can also undo this snapshot with "Remove-Migration" if you missed something. Okey now lets update database. - 6. In the Package Manager Console input next command: "update-database". As you can see I had some problems with authentication. If you have similar problem refer to this text I wrote: Quote WARNING: If you dont want to be asked for password from localhost you can change postgreSQL settings "pg_hba.conf" and below METHOD set everything as "trust". "pg_hba.conf" is in postgreSQL installation folder. In my case: C:\ProgramFiles\PostgreSQL\14\data for more information about postgresql access visit THIS link. After I change everything to trust, Console said "Applying migration '200220828182213_firstMigraton' > 'Done.' And now we know everything was done successfully... Lets check our database now. Open pgAdmin, connect to your server using your password. Navigate to database you are using. Go to Schemas > public >Tables. You should see 3 tables. We see _EFMigrationsHistory where all migrations will be stored and also our playerModel and weaponModel table. Lets check that also. Right mouse button on playerModel > View/Edit Data > All rows. We can see all properties we set in our playerModel class here in database... Expect playerWeapons List... Lets open weaponModel table in database... Here we can see playerModelId. So when player save some weapon it will be saved in weaponModel table, but it will refer on that player with foreign key. - 7. Okey now we done with everything... But how can we fetch data from database and store it on server. For purpose of this tutorial I made some class to fetch data from database and save data. Create new class with right mouse button in Solution Explorer > Add >Class.. Lets call it "OnPlayerLogin.cs" Put next code inside it... internal class OnPlayerLogin : Script { public DataContext dataContext; public OnPlayerLogin() { dataContext = new DataContext(); } [ServerEvent(Event.ResourceStart)] public void ResourceStart() { NAPI.Util.ConsoleOutput("OnPlayerLogin started"); } [ServerEvent(Event.PlayerSpawn)] public async void OnPlayerSpawn(Player player) { NAPI.Chat.SendChatMessageToPlayer(player, $"Hello {player.SocialClubName}"); await GetAllDataFromDatabaseAsync(player); var allProperty = typeof(playerModel).GetProperties(); foreach (PropertyInfo property in allProperty) { string EntityDataName = property.Name.ToString(); var result = NAPI.Data.GetEntityData(player, EntityDataName).ToString(); NAPI.Chat.SendChatMessageToPlayer(player, "propertyName ", EntityDataName); NAPI.Chat.SendChatMessageToPlayer(player, "value: ", result); } player.Health = NAPI.Data.GetEntityData(player, nameof(playerModel.healthBar)); } /// <summary> /// Async function that pull all data from database and store it with SetEntityData method. /// Data is stored in patern of given player model context. /// </summary> /// <param name="player"></param> /// <returns>System.Threading.Tasks.Task</returns> public async Task GetAllDataFromDatabaseAsync(Player player) { await Task.Run(() => { // Dont use social club name. Use Social club ID. var result = dataContext.playerModel.Where(x => x.socialClubName == player.SocialClubName).First(); var allProperty = typeof(playerModel).GetProperties(); foreach (PropertyInfo property in allProperty) { string EntityDataName = property.Name.ToString(); dynamic value = property.GetValue(result); NAPI.Data.SetEntityData(player, EntityDataName, value); NAPI.Util.ConsoleOutput($"{EntityDataName}: {property.GetValue(result)}"); } }); } /// <summary> /// Async function that save all data from GetEntityData method to Database. /// Data is saved in patern of given player model context. /// </summary> /// <param name="player"></param> /// <returns>System.Threading.Tasks.Task</returns> public async Task SaveAllDataToDatabaseAsync(Player player) { await Task.Run(() => { var result = dataContext.playerModel.Where(x => x.socialClubName == player.SocialClubName).First(); var allProperty = typeof(playerModel).GetProperties(); foreach (PropertyInfo property in allProperty) { string EntityDataName = property.Name.ToString(); property.SetValue(result, NAPI.Data.GetEntityData(player, EntityDataName)); } dataContext.SaveChanges(); }); } [Command("savedata", "Use /savedata", GreedyArg = true)] public void CMD_SavePosition(Player player) { NAPI.Chat.SendChatMessageToPlayer(player, "Started saving all your data. Please wait ..."); SaveAllDataToDatabaseAsync(player).ContinueWith(task => NAPI.Chat.SendChatMessageToPlayer(player, "Finished all saving ...")); } [Command("hp", "Use /hp", GreedyArg = true)] public void SetLowHP(Player player) { NAPI.Data.SetEntityData(player, nameof(playerModel.healthBar), 10); player.Health = 10; } } In code above 90% is just dump code for testing. Only 2 functions are important. Lets check them. /// <summary> /// Async function that pull all data from database and store it with SetEntityData method. /// Data is stored in patern of given player model context. /// </summary> /// <param name="player"></param> /// <returns>System.Threading.Tasks.Task</returns> public async Task GetAllDataFromDatabaseAsync(Player player) { await Task.Run(() => { // Dont use social club name. Use Social club ID. var result = dataContext.playerModel.Where(x => x.socialClubName == player.SocialClubName).First(); var allProperty = typeof(playerModel).GetProperties(); foreach (PropertyInfo property in allProperty) { string EntityDataName = property.Name.ToString(); dynamic value = property.GetValue(result); NAPI.Data.SetEntityData(player, EntityDataName, value); NAPI.Util.ConsoleOutput($"{EntityDataName}: {property.GetValue(result)}"); } }); } /// <summary> /// Async function that save all data from GetEntityData method to Database. /// Data is saved in patern of given player model context. /// </summary> /// <param name="player"></param> /// <returns>System.Threading.Tasks.Task</returns> public async Task SaveAllDataToDatabaseAsync(Player player) { await Task.Run(() => { var result = dataContext.playerModel.Where(x => x.socialClubName == player.SocialClubName).First(); var allProperty = typeof(playerModel).GetProperties(); foreach (PropertyInfo property in allProperty) { string EntityDataName = property.Name.ToString(); property.SetValue(result, NAPI.Data.GetEntityData(player, EntityDataName)); } dataContext.SaveChanges(); }); } This 2 functions are refering to our playerModel class, check for all properties inside and loop through it so we can pull it from database and store in SetEntityData function OR to Save it from GetEntityData to database. The magic behind this is after adding new properties to playerModel class, we need to create new migration with "Add-Migration" command, and "Update-Database", after that we are able to use our new properties and access it with very easy. Example is "/hp" command. Check next code. [Command("hp", "Use /hp", GreedyArg = true)] public void SetLowHP(Player player) { // nameof(playerModel.healthBar return "healthBar" as a string NAPI.Data.SetEntityData(player, nameof(playerModel.healthBar), 10); player.Health = 10; } - 8. Now lets test it. I will manualy add to my database my social club name and other parameters, lets check if its getting loaded inside game. Go to pgAdmin > playerModel table > View/Edit Data > All rows. Yellow button is for adding new row. I used online Guid generator from HERE. After adding data dont forget to press F6 to update playerModel table. Watch out! Dont press enter to add new line to columns case it will add "\n" sign for new line. Just check you dont have new lines in text editor inside pgAdmin when adding new text in some columns. We can see how game pulled from database. Lets check one more time code and for the end make conclusion on this tutorial. /// <summary> /// Async function that pull all data from database and store it with SetEntityData method. /// Data is stored in patern of given player model context. /// </summary> /// <param name="player"></param> /// <returns>System.Threading.Tasks.Task</returns> public async Task GetAllDataFromDatabaseAsync(Player player) { await Task.Run(() => { // Dont use social club name. Use Social club ID. var result = dataContext.playerModel.Where(x => x.socialClubName == player.SocialClubName).First(); var allProperty = typeof(playerModel).GetProperties(); foreach (PropertyInfo property in allProperty) { string EntityDataName = property.Name.ToString(); dynamic value = property.GetValue(result); NAPI.Data.SetEntityData(player, EntityDataName, value); NAPI.Util.ConsoleOutput($"{EntityDataName}: {property.GetValue(result)}"); } }); } - Tutorial Conclusion. This code is definitely not code you would use in your script. There is no any error handling or better management. That was not even the point of this tutorial. My point was to introduce you to EF Core and how you can use it in RAGE MP. We can see there is a little bit more work and preparation for script. But once you create everything right, every other step will be easier, also your code will be much cleaner. Unlucky for you didn't cover much more functions with LINQ. How to chain functions, get all weapon models where foreign key is connected with my account ... Also I didn't even used "using" keyword to dispose instance after program leaves that block where I call dataContext, which is not great. But like I said, main goal was not to create perfect script. If you guys find some problems in this tutorial, write it and I will try to fix it. Edited August 28, 2022 by trolovecro Security Issue with server.
trolovecro Posted August 28, 2022 Author Posted August 28, 2022 Sorry guys for little mess. Had problem with page security.
Eager Posted December 2, 2022 Posted December 2, 2022 Hello, Thanks a lot for this Had to download a lot of packages to make it works... Do you know how to get rid of these error messages ? Trying to start postgreSQL script ... Une opération non bloquante sur un socket n'a pas pu être achevée immédiatement. [::1]:5432 A PostgreSQL type with the name hstore was not found in the database A PostgreSQL type with the name citext was not found in the database A PostgreSQL type with the name unknown was not found in the database Specified method is not supported. Specified method is not supported. Succesfully connected to PostgreSQL Version: PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit
Eager Posted December 2, 2022 Posted December 2, 2022 Also, i'v got a warning about : Microsoft.EntityFrameworkCore.resources.dll missing
sudo Posted January 9, 2023 Posted January 9, 2023 Hi, should versions 2.10 be used for .NET Core 3.1 or can I choose the latest version that supports .NET Core 2.0 or greater?
trolovecro Posted January 14, 2023 Author Posted January 14, 2023 Quote Hi, should versions 2.10 be used for .NET Core 3.1 or can I choose the latest version that supports .NET Core 2.0 or greater? You can upgrade your EF Core to 3.1, but be careful you need to upgrade all your modules to same version otherwise it will not work.
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now