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;
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:
<add name="MySQLConnectionString" connectionString="server=localhost;
user id=myuser; password=mypass; database=test; pooling=false;"
providerName="MySql.Data.MySqlClient"/>
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<MessageItem> GetMessages()
{
MySqlCommand cmd = new MySqlCommand("ShowAll",
new MySqlConnection(GetConnectionString()));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
MySqlDataReader dr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
List<MessageItem> MessageItemlist = new List<MessageItem>();
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:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
TypeName="MessagesDB" OldValuesParameterFormatString="original_{0}"
SelectMethod="GetMessages" DataObjectTypeName="MessageItem"
DeleteMethod="DeleteMessage" InsertMethod="InsertMessage"
UpdateMethod="UpdateMessage">
</asp:ObjectDataSource>
<br />
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1"
DataKeyNames="Entry_ID">
<Columns>
<asp:BoundField DataField="Entry_ID" HeaderText="Entry_ID"
SortExpression="Entry_ID" Visible="False" />
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
<asp:BoundField DataField="Name"
HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Email"
HeaderText="Email" SortExpression="Email" />
<asp:BoundField DataField="Message"
HeaderText="Message" SortExpression="Message" />
</Columns>
</asp:GridView>
<br />
<strong><span style="text-decoration: underline">
Insert New Record:</span></strong><br />
<asp:DetailsView ID="DetailsView1" runat="server"
AutoGenerateRows="False" BorderStyle="None"
CellSpacing="5" DataSourceID="ObjectDataSource1"
DefaultMode="Insert" GridLines="None"
Height="50px" Width="300px">
<Fields>
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Email" HeaderText="Email" />
<asp:BoundField DataField="Message" HeaderText="Message" />
<asp:CommandField ButtonType="Button"
ShowInsertButton="True" ShowCancelButton="False" />
</Fields>
</asp:DetailsView>
Note
The assembly "MySql.Data.dll" is in the "/bin" directory so the "MySql.Data.MySqlClient
" will work.
I hope this helps!