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

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:

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).

To install the code:

You must have MySQL 5 up and running.

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)

BEGIN

Delete From test.message

WHERE Entry_ID = param1;

END PROCEDURE `test`.`InsertMessage`(IN param1 VARCHAR(50), IN param2

VARCHAR(50), IN param3 VARCHAR(200))

BEGIN

INSERT INTO message(Name, Email, Message)

VALUES(param1,param2,param3);

END PROCEDURE `test`.`ShowAll`()

BEGIN

SELECT

message.Entry_ID,

message.Name,

message.Email,

message.Message

FROM

test.message;

END PROCEDURE `test`.`UpdateMessage`(IN paramkey INT, IN param1 VARCHAR(50),

IN param2 VARCHAR(50), IN param3 VARCHAR(200))

BEGIN

UPDATE message

SET Name = param1, Email = param2, Message = param3

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