MySQL 5 C# sample code using ObjectDataSources
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)
BEGIN
Delete From test.message
WHERE Entry_ID = param1;
ENDPROCEDURE `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);
ENDPROCEDURE `test`.`ShowAll`()
BEGIN
SELECT
message.Entry_ID,
message.Name,
message.Email,
message.Message
FROM
test.message;
ENDPROCEDURE `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:
Insert New Record: Note
the assembly "
MySql.Data.dll
" is in the "
/bin" directory so the "MySql.Data.MySqlClient" will work.
i hope this helps!
About defwebserver
Los Angeles
Web Developer
Click
here to view defwebserver's online profile. (2006-5-30:02:28)
| 感谢原创者的辛勤劳动，希望对您有所帮助，转载请注明原出处。 |
