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
In previous articles I explained asp.net mvc crud operations using entity framework, asp.net mvc populate dropdownlist from database example, asp.net mvc get data from database using ado.net with example, jQuery autocomplete highlight matching search results, asp.net use cookies to store and read from website and many articles related to asp.net, mvc, angularjs, jQuery. Now I will explain how to connect and get data from mysql database in asp.net web application using c#, vb.net with example.
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.
|
|
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 Email
|
|||
0 comments :
Note: Only a member of this blog may post a comment.