CODEWITHSUNDEEP
mysqlazure
Rene M
Rene M

Reputation: 123

How to connect to the Azure MySQL In App localdb using c#

I tried several ways no without any success (OdbcConnection, MySqlConnection, ...). With PHP it was working within a minute. But I want to access the database using a Web Service (asmx).

The Web Service is returning correct information back:

MYSQLCONNSTR_localdb = Environment.GetEnvironmentVariable("MYSQLCONNSTR_localdb").ToString();
dbhost = Regex.Match(MYSQLCONNSTR_localdb, @"Data Source=(.+?);").Groups[1].Value;
dbname = Regex.Match(MYSQLCONNSTR_localdb, @"Database=(.+?);").Groups[1].Value;
dbusername = Regex.Match(MYSQLCONNSTR_localdb, @"User Id=(.+?);").Groups[1].Value;
dbpassword = Regex.Match(MYSQLCONNSTR_localdb, @"Password=(.+?)$").Groups[1].Value;

Database=localdb;Data Source=127.0.0.1:xxxxx;User Id=azure;Password=blabla

But how to access the database localdb?

With: new OdbcConnection("DRIVER={MySQL ODBC 5.3 unicode Driver}; ...

Using this method I receive error: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I expect it works different to a MySQL database in Azure. But how for MySQL In App?

Thanks for help in advance!

Upvotes: 2

Views: 3232

Answers (2)

Olorunfemi Davis
Olorunfemi Davis

Reputation: 1090

Here is what I did to solve mine: My app is an ASP Core WebAPI. it's already here https://stackoverflow.com/a/56092886/2768516 

  public class LocalAppDb : DbContext
    {

        public DbSet<LocalUser> Users { get; set; }
        //Other Data Sets....

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            string connection = Environment.GetEnvironmentVariable("MYSQLCONNSTR_localdb");
            string dbhost = Regex.Match(connection, @"Data Source=(.+?);").Groups[1].Value;
            string server = dbhost.Split(':')[0].ToString();
            string port = dbhost.Split(':')[1].ToString();
            string dbname = Regex.Match(connection, @"Database=(.+?);").Groups[1].Value;
            string dbusername = Regex.Match(connection, @"User Id=(.+?);").Groups[1].Value;
            string dbpassword = Regex.Match(connection, @"Password=(.+?)$").Groups[1].Value;

            string connectionString2 = $@"server={server};userid={dbusername};password={dbpassword};database={dbname};port={port};pooling = false; convert zero datetime=True;";

            optionsBuilder.UseMySql(connectionString2);
        }

    }

then I can call the DB Class like this: 

using (LocalAppDb context = new LocalAppDb())
   {
       var result = context.Users.ToList(); 
       //or context.Insert....., update,,,,etc
   }

Upvotes: 2

Wagner Bertolini Junior
Wagner Bertolini Junior

Reputation: 1200

I just had this problem too. You must pass the port separated, for instance:

string connectionString = 
   string.Format("Server=127.0.0.1; Port=54511; Database=localdb; Uid=azure; Pwd=password");

MySqlConnection conn = new MySqlConnection(connectionString);

If you are using a hibernate.cfg.xml (inside bin folder):

<?xml version="1.0" encoding="utf-8"?>

<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory name="NHibernate.Test">
    <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
    <property name="connection.connection_string">
      Database=mydb;Data Source=127.0.0.1;Port=54511;User Id=azure;Password=password      
    </property>
    <property name="dialect">NHibernate.Dialect.MySQLDialect</property>
  </session-factory>
</hibernate-configuration>

Upvotes: 2

Related Questions