ASP.NET连接MySQL数据库方法,测试可行
1、具体如何装VS和MySQL数据库我这里就不再赘述,网上有很多很全面的资料。不过MySQL安装后默认是没有客户端工具的,只是一个服务器存储数据,为了方便你要再下载一个客户端工具,我这里介绍使用HeidiSql,挺简介挺好用的。新手用这个足够了。
2、ASP.NET连接MySQL需要一个组件(.net本身不提供访问MySQL的驱动)MySQL.Data.Dll,下载地址:http://pan.baidu.com/s/1sjLcDGh
3、将MySQL.Data.Dll文件拷贝到你的bin目录下,在工程下添加引用。
4、在代码页里添加using Mysql.Data.MysqlClient;然后再在Page_Load函数里写MysqlConnection,接下来要干的就和Sqlserver完全相似了。
下面提供3段代码供参考,一个sql执行代码,一个代码页,一个是HTML页:
-- -------------------------------------------------------- -- 主机: 127.0.0.1 -- 服务器版本: 5.6.16 - MySQL Community Server (GPL) -- 服务器操作系统: Win32 -- HeidiSQL 版本: 8.2.0.4675 -- -------------------------------------------------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -- 导出 book 的数据库结构 CREATE DATABASE IF NOT EXISTS `book` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `book`; -- 导出 表 book.bookstore 结构 CREATE TABLE IF NOT EXISTS `bookstore` ( `id` int(2) unsigned NOT NULL AUTO_INCREMENT, `bookName` varchar(50) DEFAULT NULL, `publicationDate` date DEFAULT NULL, `author` varchar(50) DEFAULT NULL, `remarks` text, KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='书店'; -- 正在导出表 book.bookstore 的数据:~6 rows (大约) /*!40000 ALTER TABLE `bookstore` DISABLE KEYS */; REPLACE INTO `bookstore` (`id`, `bookName`, `publicationDate`, `author`, `remarks`) VALUES (6, '简爱', '1956-08-23', '杨盼', '乡村小女孩与城市高富帅的爱情故事'), (1, '新青年', '1924-10-01', '鲁迅', '代表中国先进文化的前进方向'), (2, '天龙八部', '2014-02-18', '金庸', '热血江湖情谊'), (3, '新天龙', '2234-03-14', '普希金', '假如生活欺骗了你,不要悲伤,不要心急,忧郁的日子里需要镇静'), (4, '黄继光', '1937-12-18', '董存rui', '你猜我和黄继光谁炸的碉堡,谁碉堡?'), (5, '论语', '0221-05-29', '孔子', '大学之道,在明明德,在亲民,在止于至上'); /*!40000 ALTER TABLE `bookstore` ENABLE KEYS */; -- 导出 过程 book.bookstoresp 结构 DELIMITER // CREATE ` PROCEDURE `bookstoresp`(IN `s` INT) BEGIN if s = 1 then update bookstore set bookname = "新天龙" where id = 2; else update bookstore set bookname = "新天龙" where id = 3; end if; select * from bookstore; END// DELIMITER ; /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */; /*!40101 SET */;
********************************************************************************************************
using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using MySql.Data.MySqlClient; namespace MySqlpro { public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string query = "select * from bookstore"; MySqlConnection myConnection = new MySqlConnection("server=localhost;user ); MySqlCommand myCommand = new MySqlCommand(query,myConnection); DataSet ds = new DataSet(); myConnection.Open(); MySqlDataAdapter Da = new MySqlDataAdapter(myCommand); Da.Fill(ds); GridView1.DataSource = ds.Tables[0]; GridView1.DataBind(); //myCommand.ExecuteNonQuery(); //MySqlDataReader myDataReader = myCommand.ExecuteReader(); //string bookres = ""; //while(myDataReader.Read()==true) //{ // bookres += myDataReader["id"]; // bookres += myDataReader["bookname"]; // bookres += myDataReader["author"]; //} //myDataReader.Close(); myConnection.Close(); //Label1.Text = bookres; } } }
************************************************************************************************************
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="MySqlpro._Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>无标题页</title> </head> <body> <form runat="server"> <div> <asp:GridView runat="server" CellPadding="4" ForeColor="#333333" GridLines="None"> <RowStyle BackColor="#EFF3FB" /> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#2461BF" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> </div> </form> </body> </html>