WEB开发网
开发学院WEB开发ASP.NET 老外的.net与mysql存储过程编程 阅读

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

 2007-06-07 17:24:02 来源:WEB开发网   
核心提示:created this example because I could not find a simple explanation for using MySQL 5 with ObjectDataSources in asp.net 2.0.Introductioni created this example be

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 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:

Download source - 65.3 Kb

Tags:老外 net mysql

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接