Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Asp.Net Get (Fetch) Data from MySQL Database using C#, VB.NET

Nov 16, 2016
Introduction

Here we will learn how to get or retrieve data from mysql database in asp.net using c#, vb.net with example or asp.net web application fetch data from mysql database connection example or asp.net display data from mysql database connection using c#, vb.net with example or how to use mysql database in asp.net web application to retrieve and show the data in c#, vb.net example. By adding MySQL.Data.dll reference in our asp.net applications we can easily connect and fetch data from MySQL database in our applications.

Description


To use MySQL database in our asp.net web application first install MySQL database in your system using following url Download & Install MySQL. In case already if you MySQL installed server available means that would be fine.

We installed full MySQL server including MySQL Server, Work Bench and Visual Studio connector in our system. Once we finished MySQL installation that would be like as shown below

Now we will create new database in our MySQL Server for that click on Local instance server in MySQL Connections section like as shown in above image and enter credential details to connect our server.

Once we connect Go to the File --> New Model like as shown below.

Once we select New Model that will open Model editor like as shown below

If you want to rename database enter required name in Name field and click on Rename References button. Now we will create new table for that Double Click on Add Table like as shown below.

Once we click on Add Table new editor will open in that enter required table columns and click Save like as shown below

Once we finished creation of Employee table insert some dummy data in our table to show that data in our application like as shown below

Now we will see how to connect MySQL database from our asp.net application for that open visual studio and create new project like as shown below

Now right click on your project and select Manage Nuget Package reference like as shown below

Once we select Manage NuGet Packages it will open new window in that search for mysql and install MySQL.Data once installation done that will show tick mark like as shown below.

Once we install our MySQL.Data.dll reference will add it in our application like as shown below.

Now open your Default.aspx page and write the code like as shown below


<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Connect MySQL in Asp.Net Application</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvDetails" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>

Now open code behind file and write the code like as shown below.

C# Code


using System;
using MySql.Data.MySqlClient;
using System.Data;

namespace MySQLExample
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) {
BindGridviewData();
}
}
public void BindGridviewData() {
using (MySqlConnection con = new MySqlConnection("Data Source=localhost;Initial Catalog =mysqlsampledb;user id=root;password=dasari@123")) {
using (MySqlCommand cmd = new MySqlCommand("select * from employee", con)) {
con.Open();
DataSet ds = new DataSet();
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) {
da.Fill(ds);
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
}
}
}
}
}

If you observe above code we added “MySql.Data.MySqlClient” namespace to access MySQL connection commands.

Here our MySQL connection commands are same as SQL Connection but only difference is we added My at the starting of commands (for example MySqlConnection).

VB.NET Code


Imports MySql.Data.MySqlClient
Imports System.Data

Public Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGridviewData()
End If
End Sub
Public Sub BindGridviewData()
Using con As New MySqlConnection("Data Source=localhost;Initial Catalog =mysqlsampledb;user id=root;password=dasari@123")
Using cmd As New MySqlCommand("select * from employee", con)
con.Open()
Dim ds As New DataSet()
Using da As New MySqlDataAdapter(cmd)
da.Fill(ds)
gvDetails.DataSource = ds
gvDetails.DataBind()
End Using
End Using
End Using
End Sub
End Class

Now we will run and the see the result of our application that will be like as shown below

Demo

Following is the result of connecting MuSQL database in asp.net web applications.

Asp.Net Connect MySQL Database to Web Application using C#, VB.NET Example Result

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

1 comments :

Gopal Sharma said...

Very Nice, Clear and informative Article.Thanks Much. You are doing wonderful job.

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.