.net 编程mysql_老外的.net与mysql存储过程编程

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

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.

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 ssage ( 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 ssageWHERE 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 ssage;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 "nfig" and change the line:

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:

本文原创发布php中文网，转载请注明出处，感谢您的尊重！