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

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.
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 te***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 te***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   me***ge.Entry_ID,  me***ge.Name,   me***ge.Email,   me***ge.MessageFROM ; te***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     (me***ge.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 "we***onfig" and change the line:

to connect to your MySQL database.

Browse to the de***lt.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 Sy***m.Collections.Generic;using Sy***m.Data;using My***.Data.MySqlClient;using Sy***m.Configuration;using Sy***m.ComponentModel;[DataObject(true)]public static class MessagesDB{    private static string GetConnectionString()    {        return Co***gurationManager.ConnectionStrings ;       ["MySQLConnectionString"].ConnectionString;    }    [DataObjectMethod(Da***bjectMethodType.Select)]    public static List GetMessages()    {        MySqlCommand cmd = new MySqlCommand("ShowAll",                            new MySqlConnection(GetConnectionString()));        cm***ommandType = Co***ndType.StoredProcedure;        cm***onnection.Open();        MySqlDataReader dr =            cm***xecuteReader(Co***ndBehavior.CloseConnection);        List MessageItemlist = new List();        while (dr.Read())        {            MessageItem MessageItem = new MessageItem();            Me***geItem.Entry_ID = Co***rt.ToInt32(dr["Entry_ID"]);            Me***geItem.Message = Co***rt.ToString(dr["Message"]);            Me***geItem.Name = Co***rt.ToString(dr["Name"]);            Me***geItem.Email = Co***rt.ToString(dr["Email"]);            Me***geItemlist.Add(MessageItem);        }        dr.Close();        return MessageItemlist;    }    [DataObjectMethod(Da***bjectMethodType.Insert)]    public static void InsertMessage(MessageItem MessageItem)    {        MySqlCommand cmd = new MySqlCommand("InsertMessage",                            new MySqlConnection(GetConnectionString()));        cm***ommandType = Co***ndType.StoredProcedure;        cm***arameters.Add(new MySqlParameter("param1", Me***geItem.Name));        cm***arameters.Add(new MySqlParameter("param2", Me***geItem.Email));        cm***arameters.Add(new MySqlParameter("param3", Me***geItem.Message));        cm***onnection.Open();        cm***xecuteNonQuery();        cm***onnection.Close();    }    [DataObjectMethod(Da***bjectMethodType.Update)]    public static int UpdateMessage(MessageItem MessageItem)    {        MySqlCommand cmd = new MySqlCommand("UpdateMessage",                            new MySqlConnection(GetConnectionString()));        cm***ommandType = Co***ndType.StoredProcedure;        cm***arameters.Add(new MySqlParameter("paramkey", Me***geItem.Entry_ID));        cm***arameters.Add(new MySqlParameter("param1", Me***geItem.Name));        cm***arameters.Add(new MySqlParameter("param2", Me***geItem.Email));        cm***arameters.Add(new MySqlParameter("param3", Me***geItem.Message));        cm***onnection.Open();        int i = cm***xecuteNonQuery();        cm***onnection.Close();        return i;    }    [DataObjectMethod(Da***bjectMethodType.Delete)]    public static int DeleteMessage(MessageItem MessageItem)    {        MySqlCommand cmd = new MySqlCommand("DeleteMessage",                 new MySqlConnection(GetConnectionString()));        cm***ommandType = Co***ndType.StoredProcedure;        cm***arameters.Add(new MySqlParameter("param1", Me***geItem.Entry_ID));        cm***onnection.Open();        int i = cm***xecuteNonQuery();        cm***onnection.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:

Download source - 65.3 Kb