CODEWITHSUNDEEP
c#mysqlsqlasp.netasp.net-web-api
Mohammad Farahi
Mohammad Farahi

Reputation: 1066

ASP.Net web application connect to host SQL DataBase

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: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. phpMyAdmin

Upvotes: 0

Views: 1735

Answers (1)

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

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

Upvotes: 1

Related Questions