ASP.Net web application connect to host SQL DataBase

Question

I have a simple Web API Application and I want to connect it to a DataBase presents in my host. This is in my web.config:

  <connectionStrings>
    <add name="JarasDB" connectionString="Data Source=localhost;Initial Catalog=JarasDB;Integrated Security=True;" providerName="System.Data.SqlClient" />
  </connectionStrings>

And I'm going to test it with following codes:

public string Get(int id)
{
    string connectionString = ConfigurationManager.ConnectionStrings["JarasDB"].ConnectionString;
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        try
        {
            //
            // Open the SqlConnection.
            //
            con.Open();
            //
            // The following code uses an SqlCommand based on the SqlConnection.
            //
            using (SqlCommand command = new SqlCommand("CREATE TABLE Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date datetime);", con))
                command.ExecuteNonQuery();

        }
        catch (Exception ex)
        {}
    }
    return strings[id];
}

After publishing project in my host what happens is The Connection String will be add in ASP.NET Configuration page in Plesk panel:

After calling Get method I expect to create a table in my database but nothing happens. I want to know where is my problem.

You can't get to localhost from your hosting provider. You need a database server accessible to your host. You could also consider an embedded database like SQLite if it meets your needs. — Crowcoder
– Crowcoder, Commented Nov 10, 2018 at 14:13
How do I connect database server using name id and password? — Mohammad Farahi
– Mohammad Farahi, Commented Nov 10, 2018 at 14:37
While I'm not familiar with Plesk, I see a "databases" tab in your screenshot. There are dozens of options but I would start there. — Crowcoder
– Crowcoder, Commented Nov 10, 2018 at 14:42
Your code (SqlConnection) and connection string are for Microsoft SQL Server, but your screenshot is of a MySQL database. You will need to use MySqlConnection to connect to it, e.g., mysql-net.github.io/MySqlConnector/tutorials/basic-api — Bradley Grainger
– Bradley Grainger, Commented Nov 11, 2018 at 1:18

Tetsuya Yamamoto · Accepted Answer · 2018-11-12 02:02:38Z

The problem occurred because you're using SQL Server connection string to connect against MySQL database, which doesn't work as expected. Ensure that MySQL Connector .NET is referenced in your project (i.e. include MySql.Data.dll and all related assemblies), then replace your connection string from this one:

<!-- Wrong (SQL Server connection string) -->
<add name="JarasDB" connectionString="Data Source=localhost;Initial Catalog=JarasDB;Integrated Security=True;" providerName="System.Data.SqlClient" />

to this example:

<!-- Correct -->
<add name="JarasDB" connectionString="Server=localhost;Port=3306;Database=jarasdb;Uid=UserID;Pwd=XXXXX" providerName="MySql.Data.MySqlClient"/>

Additionally, it is necessary to use MySqlConnection and MySqlCommand from MySql.Data.MySqlClient namespace to execute DDL query:

// add this line on top of 'using' lines
using MySql.Data.MySqlClient;

public string Get(int id)
{
    string connectionString = ConfigurationManager.ConnectionStrings["JarasDB"].ConnectionString;
    using (MySqlConnection con = new MySqlConnection(connectionString))
    {
        try
        {
            con.Open();
            using (MySqlCommand command = new MySqlCommand("CREATE TABLE Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date datetime);", con))
            {
                command.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        { 
            // throw exception here 
        }
    }
    return strings[id];
}

Reference: MySQL Connector .NET Connection Strings

Collectives™ on Stack Overflow

ASP.Net web application connect to host SQL DataBase

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related