Koneksi asp net dengan mysql

This example describes using ASP.NET/MySql.Data to connect to a MySQL Database. A few important things you need before you get started:

• Knowledge of computer programming.
• Microsoft® Visual Studio .NET.
• MySql Connector/NET on your development computer. For more information, click here.
• Knowledge of MySql and specifically the MySql.Data Namespace.
• A setup MySql Database.

To Connect to a MySQL Database Using ASP.NET

1. Find your database's connection strings (Plesk).

   Note: Change the your password value to your real database password value.

2. Using Microsoft Visual Studio .NET create an ASP.NET Project.
3. Add a reference to MySql.Data.dll.
4. Replace the value in the following code with your_ConnectionString with your database information.
5. Insert the following code into your project including your modified your_ConnectionString value:

   Note: If your MySql database was created with the Allow Direct Database Access enabled, you can connect to the database from your development computer. If you did not enable Allow Direct Database Access, your MySql is in a secure environment and you cannot connect to the database from your development computer. A connection can only be successful when your code is deployed to the hosting site.

MySql.Data.MySqlClient.MySqlConnection mySqlConnection = new 

  MySql.Data.MySqlClient.MySqlConnection();

mySqlConnection.ConnectionString = “your_ConnectionString”;
try 

  {

       mySqlConnection.Open();
       switch (mySqlConnection.State)

         {

                case System.Data.ConnectionState.Open:

                        // Connection has been made

                        break;

                case System.Data.ConnectionState.Closed:

                        // Connection could not be made, throw an error

                        throw new Exception("The database connection state is Closed");

                        break;

                default:

                        // Connection is actively doing something else

                        break;

         }
       // Place Your Code Here to Process Data //

  }

  catch (MySql.Data.MySqlClient.MySqlException mySqlException)

  {

         // Use the mySqlException object to handle specific MySql errors

  }

  catch (Exception exception)

  {

         // Use the exception object to handle all other non-MySql specific errors

  }

  finally 

  {

         // Make sure to only close connections that are not in a closed state

         if (mySqlConnection.State != System.Data.ConnectionState.Closed)

         {

                // Close the connection as a good Garbage Collecting practice

                mySqlConnection.Close();

         }

  }

In this article I will explain how to use and connect to MySQL database in ASP.Net application with the help of MySQLConnector using C# and VB.Net.

For this article, I will be populating GridView control with records from MySQL Database.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

Note: The SQL for creating the Table is provided in the attached sample code.

Download and Install the MySQL Connector

You will need to download and install the MySQLConnector in order to connect to the MySQL database in ASP.Net.

After installation is complete you need to open Windows Explorer and look for the MySql installation in the Program Files folder of your Windows drive.

There you will find a folder for MySQL Connector and inside that you will find the MySql.Data.dll which you need to copy inside the BIN folder of your project.

MySql Connection String

Below is the connection string to the MySql Database.

<connectionStrings>

    <add name="constr" connectionString="Data Source=localhost;port=3306;Initial Catalog=SampleDB;User Id=mudassar;password=pass@123"/>

</connectionStrings>

Namespaces

You will need to import the following namespaces.

C#

using System.Data;

using System.Configuration;

using MySql.Data.MySqlClient;

VB.Net

Imports System.Data

Imports System.Configuration

Imports MySql.Data.MySqlClient

Binding the GridView with records from MySQL Database Table

If you are aware of ADO.Net then using MySql will be lot simpler as the MySql Connector classes have very similar names to that of the ADO.Net classes. For example in ADO.Net we have SqlConnection class and the corresponding class in MySql is MySqlConnection.

Inside the Page Load event of the page, I am populating the GridView control with records from the MySql database using a DataTable.

C#

protected void Page_Load(object sender, EventArgs e)

{

    if (!this.IsPostBack)

    {

            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

            using (MySqlConnection con = new MySqlConnection(constr))

            {

                using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM Customers"))

                {

                    using (MySqlDataAdapter sda = new MySqlDataAdapter())

                    {

                        cmd.Connection = con;

                        sda.SelectCommand = cmd;

                        using (DataTable dt = new DataTable())

                        {

                            sda.Fill(dt);

                            GridView1.DataSource = dt;

                            GridView1.DataBind();

                        }

                    }

                }

            }

    }

}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

    If Not Me.IsPostBack Then

        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

        Using con As New MySqlConnection(constr)

            Using cmd As New MySqlCommand("SELECT * FROM Customers")

                Using sda As New MySqlDataAdapter()

                    cmd.Connection = con

                    sda.SelectCommand = cmd

                    Using dt As New DataTable()

                        sda.Fill(dt)

                        GridView1.DataSource = dt

                        GridView1.DataBind()

                    End Using

                End Using

            End Using

        End Using

    End If

End Sub

Demo

Downloads