C#•16mo 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()•16mo 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•16mo 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•16mo ago

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

Ⰽⰰⱈⰻⰽⱄ•16mo ago

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

Despy()•16mo 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•16mo ago

Add reference

Preda•16mo ago

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

Ⰽⰰⱈⰻⰽⱄ•16mo ago

Don't use that

Preda•16mo ago

Why?

Ⰽⰰⱈⰻⰽⱄ•16mo ago

It's Oracles driver which is very problematic

Preda•16mo ago

Hmm, which then?

Ⰽⰰⱈⰻⰽⱄ•16mo 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.

Ⰽⰰⱈⰻⰽⱄ•16mo ago

Thet is community made driver written from scratch

Preda•16mo ago

Does it connect to the Workbench?/

Ⰽⰰⱈⰻⰽⱄ•16mo 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•16mo ago

So I can test my Persistence with the MySqlConnector?

Ⰽⰰⱈⰻⰽⱄ•16mo ago

yes

Preda•16mo ago

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

Preda•16mo ago

got this err

jcotton42•16mo ago

Is that your code?

Preda•16mo ago

Yes

jcotton42•16mo ago

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

Preda•16mo ago

should I throw exc?

jcotton42•16mo ago

No Just remove the catch entirely

Preda•16mo ago

jcotton42•16mo ago

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

Preda•16mo ago

Preda•16mo ago

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

jcotton42•16mo 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•16mo ago

Preda•16mo ago

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

jcotton42•16mo 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•16mo 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•16mo 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•16mo ago

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

Preda•16mo ago

Omgg

jcotton42•16mo ago

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

Preda•16mo 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•16mo ago

jcotton42•16mo ago

Does the device table have a building column?

Preda•16mo ago

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

jcotton42•16mo ago

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

Preda•16mo ago

And device is in aggregation with the place/building

jcotton42•16mo ago

devicemapper?

Preda•16mo ago

Yes

jcotton42•16mo ago

And that is?

Preda•16mo ago

Class

jcotton42•16mo ago

Am I going to have to pull teeth? Provide details

Preda•16mo ago

jcotton42•16mo 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•16mo 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•16mo ago

Accord•16mo 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•16mo 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•16mo 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