C#•2y ago

❔ Connecting a MySQL server to a C# project

Recently ive had to learn MySQL and how it works but i cannot find out how to connect the actual MySQL server to a C# project in Visual Studio

57 Replies

Despy()OP•2y ago

ive installed the connector and all of that i have it all installed on my pc, i just dont know how to actually connect it

mindhardt•2y ago

Usually you use your database lib and connect to a db using connection string, that is it Dunno which lib you personally use but for things like SQLite or Npgsql (postgres) you do something like

string connstr = "Host=localhost;User=root;Password=password";
using var conn = new SQLConnection(connstr);

string connstr = "Host=localhost;User=root;Password=password";
using var conn = new SQLConnection(connstr);

This is obviously a pseudocode

Angius•2y ago

Whatever it is that you use to connect to the database... read the documentation of that

Ⰽⰰⱈⰻⰽⱄ•2y ago

Did you install MySQLConnector or official Oracle Connector? Because official Oracle Connector is problematic and should be omitted

Despy()OP•2y ago

ye ive installed all of it its just the trouble of actually connecting the database or server idk how exactly its called to the actual project i have ill get it done

Preda•2y ago

Add reference

Preda•2y ago

To your domain project And also check for the same version Also create an App.config file To declare your ConnectionString

Ⰽⰰⱈⰻⰽⱄ•2y ago

Don't use that

Preda•2y ago

Why?

Ⰽⰰⱈⰻⰽⱄ•2y ago

It's Oracles driver which is very problematic

Preda•2y ago

Hmm, which then?

Ⰽⰰⱈⰻⰽⱄ•2y ago

https://www.nuget.org/packages/MySqlConnector

MySqlConnector 2.2.5

A truly async MySQL ADO.NET provider, supporting MySQL Server, MariaDB, Percona Server, Amazon Aurora, Azure Database for MySQL and more.

Ⰽⰰⱈⰻⰽⱄ•2y ago

Thet is community made driver written from scratch

Preda•2y ago

Does it connect to the Workbench?/

Ⰽⰰⱈⰻⰽⱄ•2y ago

Compared to MySql.Data (i.e., Oracle’s MySQL Connector/NET), MySqlConnector has the following benefits: Asynchronous I/O support Higher performance (see benchmarks) and lower memory usage More reliable (see bugs fixed) Leading edge: first MySQL driver to support .NET Core, DbBatch, DateOnly, DbDataSource and other new .NET features Better compatibility with non-MySQL databases (MariaDB, Amazon Aurora, etc.) Better license (MIT, not GPL 2.0 with Universal FOSS Exception or commercial license) Faster pace of development (regular NuGet releases, not once per quarter) Development happens in the open on GitHub To database, yes But it's not available via workbench, but via nuget

Preda•2y ago

So I can test my Persistence with the MySqlConnector?

Ⰽⰰⱈⰻⰽⱄ•2y ago

yes

Preda•2y ago

I ll give a shot @Karix quick question, should the ConnectionString be declared through App.config?

Preda•2y ago

got this err

jcotton42•2y ago

Is that your code?

Preda•2y ago

Yes

jcotton42•2y ago

Don't handle exceptions like that You're throwing away useful information

Preda•2y ago

should I throw exc?

jcotton42•2y ago

No Just remove the catch entirely

Preda•2y ago

jcotton42•2y ago

In any case, what's the exception being thrown?

Preda•2y ago

Preda•2y ago

That's what I don't understand Where is the mistake while making the connection

jcotton42•2y ago

Show your query It's connecting just fine It fired off a query, for back a result, but you asked for a column that wasn't in the result

Preda•2y ago

Preda•2y ago

So that's my DB Should I send an export without data or script ?

jcotton42•2y ago

No I don't want to see your database I want to see the query your code is running Show the $code surrounding this

MODiX•2y ago

To post C# code type the following: ```cs // code here ``` Get an example by typing $codegif in chat If your code is too long, post it to: https://paste.mod.gg/

Preda•2y ago

internal List<Device> GetDevicesFromDB()
        {
            List<Device> Devices = new List<Device>();
            MySqlConnection connection = new MySqlConnection(ConnectionString);
            String cmdString = "SELECT * FROM tbldevice";
            MySqlCommand cmd = new MySqlCommand(cmdString, connection);
            try
            {
                connection.Open();
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    Device Device = new Device((int)dataReader["building"], (String)dataReader["model"], (decimal)dataReader["price"]);
                    Devices.Add(Device);
                }
            }
            catch (MySqlException mse)
            {
                throw mse;
            }
            finally { connection.Close(); }
            return Devices;

internal List<Device> GetDevicesFromDB()
        {
            List<Device> Devices = new List<Device>();
            MySqlConnection connection = new MySqlConnection(ConnectionString);
            String cmdString = "SELECT * FROM tbldevice";
            MySqlCommand cmd = new MySqlCommand(cmdString, connection);
            try
            {
                connection.Open();
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    Device Device = new Device((int)dataReader["building"], (String)dataReader["model"], (decimal)dataReader["price"]);
                    Devices.Add(Device);
                }
            }
            catch (MySqlException mse)
            {
                throw mse;
            }
            finally { connection.Close(); }
            return Devices;

jcotton42•2y ago

You're reading from device in that code But from building in your screenshot

Preda•2y ago

Omgg

jcotton42•2y ago

Also, you should ditch that catch block The throw is replacing the exception's stack trace A proper rethrow is just throw;

Preda•2y ago

internal List<Building> GetBuildingsFromDB()
        {
            List<Building> Buildings = new List<Building>();
            MySqlConnection connection = new MySqlConnection(ConnectionString);
            String cmdString = "SELECT * FROM tblbuilding";
            MySqlCommand cmd = new MySqlCommand(cmdString, connection);
            try
            {
                connection.Open();
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    Building building = new Building((Int32)dataReader["building"], (String)dataReader["city"], (String)dataReader["street"], (String)dataReader["number"]);
                }
            }
            catch (MySqlException mse)
            {
                throw mse;
            }
            finally { connection.Close(); }
            return Buildings;

internal List<Building> GetBuildingsFromDB()
        {
            List<Building> Buildings = new List<Building>();
            MySqlConnection connection = new MySqlConnection(ConnectionString);
            String cmdString = "SELECT * FROM tblbuilding";
            MySqlCommand cmd = new MySqlCommand(cmdString, connection);
            try
            {
                connection.Open();
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    Building building = new Building((Int32)dataReader["building"], (String)dataReader["city"], (String)dataReader["street"], (String)dataReader["number"]);
                }
            }
            catch (MySqlException mse)
            {
                throw mse;
            }
            finally { connection.Close(); }
            return Buildings;

I will try. so the problem is my db

Preda•2y ago

jcotton42•2y ago

Does the device table have a building column?

Preda•2y ago

No I foudn Problem is Why is it jumping into the devicemapper since I am asking for the buildings

jcotton42•2y ago

If you're asking for buildings Then why are you assigning to a Device?

Preda•2y ago

And device is in aggregation with the place/building

jcotton42•2y ago

devicemapper?

Preda•2y ago

Yes

jcotton42•2y ago

And that is?

Preda•2y ago

Class

jcotton42•2y ago

Am I going to have to pull teeth? Provide details

Preda•2y ago

jcotton42•2y ago

You're asking for a building column But assigning to a Device instance Yet you said devices don't have a building column

Preda•2y ago

True I have to recheck my code Since the mappers were badly made Now I see what my error were. Im glad you helped me!

Preda•2y ago

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Preda•2y ago

Any help on why the ConnectionString is still empty and its not getting the info from the constructor? Also added a watch to the ConnectionString and to the Buildings List.

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Connecting a MySQL server to a C# project