Connect ASP.NET to Amazon RDS MariaDB

Question

Preface: I have already and can connect to the respective databases in Amazon RDS from MySQL Workbench hence rendering my username, instance url, port number as well as password to be correct.

I am creating an online application for ASP.NET and need to connect to Amazon RDS s MariaDB instead. I tried to do it via web.config or c# code way but both doesn't work. Advice needed.

Method 1

Web.config:

<add name="rdbs" connectionString="Server=xxxxxx.xxxxxx.ap-southeast-1.rds.amazonaws.com:3306; Database=xxx; Uid=xxxx; Pwd=xxxx;" providerName="MySql.Data.MySqlClient"/> 

For my C# code side:

string connStr = ConfigurationManager.ConnectionStrings["rdbs"].ConnectionString;
        using (SqlConnection sqlConnection = new SqlConnection(connStr))

Method 2

System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
    builder["Initial Catalog"] = "xxxx";
    builder["Data Source"] = "xxxxx.xxxx.ap-southeast-1.rds.amazonaws.com";
    builder["integrated Security"] = true;
    builder["Uid"] = "xxxx";
    builder["Pwd"] = "xxxx";

    string connexionString = builder.ConnectionString;
    SqlConnection connexion = new SqlConnection(connexionString);
    try { connexion.Open(); return true; }
    catch { return false; }

This is the form of errors I am facing:

Message "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)"

Thanks!

Answer 1

This actually solved it.

1. Grab MySql.Data.Entity from Nuget

2. Have this in web.config

   <add name="connRDB" connectionString="Data Source=xxxxx.xxxx.ap-southeast-1.rds.amazonaws.com;port=3306;Initial Catalog=xxxxx;User Id=xxxxx;password=xxxx" providerName="MySql.Data.MySqlClient" />
   

3. On the code side

   string constr = ConfigurationManager.ConnectionStrings["connRDB"].ConnectionString;
   using (MySqlConnection conn = new MySqlConnection(constr))
           {
               using (MySqlCommand cmd = new MySqlCommand("Select * FROM orders"))
               {
                   using (MySqlDataAdapter sda = new MySqlDataAdapter())
                   {
                       cmd.Connection = conn;
                       sda.SelectCommand = cmd;
                       using (DataTable dt = new DataTable())
                       {
                           sda.Fill(dt);
                           GridView1.DataSource = dt;
                           GridView1.DataBind();
                       }
                   }
               }
           }