using MySQL 5 with ObjectDataSources in ASP.NET 2.0
来源:互联网 发布:广西南宁广电网络 编辑:程序博客网 时间:2024/04/29 15:27
Download source - 65.3 Kb
i created this example because I could not find a simple explanation for using MySQL 5 with ObjectDataSources in ASP.NET 2.0.
Introduction
i created this example because I could not find a simple explanation for using MySQL 5 with ObjectDataSources in ASP.NET 2.0.
let me say, I am really impressed with MySQL. I was able to install it easily on my Windows XP machine and get it running in about an hour. I am a long time MS SQL user, and was very frustrated with trying to use Oracle and Firebird. I realize, the problem is that I am spoiled from MS SQL Server, but hey I'm busy and I like easy to use tools :)
if you're getting started with MySQL and ASP.NET, then I recommend these steps:
- Go to the MySQL website, download and install “Current Release (recommended)”.
- Download and install: MySQL Administrator (to administer your MySQL server, the first download just installs only the server).
- Download and install: Connector/Net 1.0 (you need this to get your ASP.NET pages to talk to your MySQL server).
- You can also download: MySQL Query Browser – (a graphical client to work with your MySQL databases and run queries).
- Read and follow this guide: A Step-by-Step Guide to Using MySQL with ASP.NET.
Using the code
To install the code:
- You must have MySQL 5 up and running.
- Install MySQL Connector/Net 1.0.
- Create a MySQL 5 database named Test.
- Create a table in that database called Message:
CREATE TABLE test.message ( Entry_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, Name VARCHAR(45), Email VARCHAR(45), Message VARCHAR(200), PRIMARY KEY (Entry_ID) ) AUTO_INCREMENT=32 CHARACTER SET latin1 COLLATE latin1_swedish_ci;
- Create these four MySQL stored procedures in the Test database:
PROCEDURE `test`.`DeleteMessage`(IN param1 INT)BEGINDelete From test.messageWHERE Entry_ID = param1;END
PROCEDURE `test`.`InsertMessage`(IN param1 VARCHAR(50), IN param2 VARCHAR(50), IN param3 VARCHAR(200))BEGININSERT INTO message(Name, Email, Message)VALUES(param1,param2,param3);END
PROCEDURE `test`.`ShowAll`()BEGINSELECT message.Entry_ID, message.Name, message.Email, message.MessageFROM test.message;END
PROCEDURE `test`.`UpdateMessage`(IN paramkey INT, IN param1 VARCHAR(50), IN param2 VARCHAR(50), IN param3 VARCHAR(200))BEGINUPDATE messageSET Name = param1, Email = param2, Message = param3WHERE (message.Entry_ID = paramkey);END
- Unzip "MySQL" and configure IIS to point to it. Make sure you configure the web server to use ASP.NET 2.0.
- Open "web.config" and change the line:
to connect to your MySQL database.
- Browse to the default.aspx page through IIS.
this is the class that uses Generics to supply the data that is consumed by the ObjectDataSource control:
using System;using System.Collections.Generic;using System.Data;using MySql.Data.MySqlClient;using System.Configuration;using System.ComponentModel;[DataObject(true)]public static class MessagesDB{ private static string GetConnectionString() { return ConfigurationManager.ConnectionStrings ["MySQLConnectionString"].ConnectionString; } [DataObjectMethod(DataObjectMethodType.Select)] public static List GetMessages() { MySqlCommand cmd = new MySqlCommand("ShowAll", new MySqlConnection(GetConnectionString())); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection.Open(); MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); List MessageItemlist = new List (); while (dr.Read()) { MessageItem MessageItem = new MessageItem(); MessageItem.Entry_ID = Convert.ToInt32(dr["Entry_ID"]); MessageItem.Message = Convert.ToString(dr["Message"]); MessageItem.Name = Convert.ToString(dr["Name"]); MessageItem.Email = Convert.ToString(dr["Email"]); MessageItemlist.Add(MessageItem); } dr.Close(); return MessageItemlist; } [DataObjectMethod(DataObjectMethodType.Insert)] public static void InsertMessage(MessageItem MessageItem) { MySqlCommand cmd = new MySqlCommand("InsertMessage", new MySqlConnection(GetConnectionString())); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new MySqlParameter("param1", MessageItem.Name)); cmd.Parameters.Add(new MySqlParameter("param2", MessageItem.Email)); cmd.Parameters.Add(new MySqlParameter("param3", MessageItem.Message)); cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } [DataObjectMethod(DataObjectMethodType.Update)] public static int UpdateMessage(MessageItem MessageItem) { MySqlCommand cmd = new MySqlCommand("UpdateMessage", new MySqlConnection(GetConnectionString())); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new MySqlParameter("paramkey", MessageItem.Entry_ID)); cmd.Parameters.Add(new MySqlParameter("param1", MessageItem.Name)); cmd.Parameters.Add(new MySqlParameter("param2", MessageItem.Email)); cmd.Parameters.Add(new MySqlParameter("param3", MessageItem.Message)); cmd.Connection.Open(); int i = cmd.ExecuteNonQuery(); cmd.Connection.Close(); return i; } [DataObjectMethod(DataObjectMethodType.Delete)] public static int DeleteMessage(MessageItem MessageItem) { MySqlCommand cmd = new MySqlCommand("DeleteMessage", new MySqlConnection(GetConnectionString())); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new MySqlParameter("param1", MessageItem.Entry_ID)); cmd.Connection.Open(); int i = cmd.ExecuteNonQuery(); cmd.Connection.Close(); return i; }
the class above uses the class "MessageItem" to pass the parameters to and from the ObjectDataSource control:
using System;public class MessageItem{ int _Entry_ID; string _Message; string _Name; string _Email; public MessageItem() { } public int Entry_ID { get { return _Entry_ID; } set { _Entry_ID = value; } } public string Message { get { return _Message; } set { _Message = value; } } public string Name { get { return _Name; } set { _Name = value; } } public string Email { get { return _Email; } set { _Email = value; } }}
this is the .aspx file that contains the ObjectDataSource control as well as a GridView for editing data and a DetailsView for inserting a record:
- using MySQL 5 with ObjectDataSources in ASP.NET 2.0
- Working with Data in ASP.NET 2.0 :: Using TemplateFields in the GridView Control
- Using Cookies in ASP.NET
- Using Plupload in ASP.NET
- Exception handling with custom error pages in ASP.NET using C#.
- Using JavaScript Along with ASP.NET
- Using JavaScript Along with ASP.NET
- Using DataSets with ASP.NET Pages
- Using JSON with ASP.NET 3.5
- USING CORS WITH ASP NET WEB API
- Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI
- Using NHibernate and Log4Net in ASP.NET 2.0 applications
- Using the ASP.NET 2.0 ReportViewer in Local Mode
- Performance Improvement in ASP.NET using Caching
- Using AJAX (Atlas in Asp.Net)
- MVC architecture in ASP.Net using C#
- Ping Using XML-RPC In ASP.NET
- Using JSONP in ASP.NET MVC
- 使用链接元素A的小技巧
- MySQL 和 .Net2.0配合使用
- 06世界杯结果预测
- c# 添加图片水印,可以指定水印位置+生成缩略图[付上帅图1,2,3,4]
- 用SQL批量插入数据(原创)
- using MySQL 5 with ObjectDataSources in ASP.NET 2.0
- 实现页面的分帧显示
- ATLAS安装&资源
- 一些有用的sql语句实例
- 公布一下工资管理系统中核心部分工资计算的代码
- asp.net中XML文件作为中间件进行数据交换功能的实现(c#,sql2000)
- 马坦故事连载五--两头会响的叫子
- datalist或datagrid的数据源是数组的时候怎么写绑定?
- 给青年一代的11点忠告——Bill Gates