Click here to Skip to main content
11,412,834 members (77,094 online)
Click here to Skip to main content

MySQL 5 C# sample code using ObjectDataSources

, 15 May 2006 BSD
Rate this:
Please Sign up or sign in to vote.
A simple example using MySQL 5 and stored procedures with ObjectDataSources and Generics, in ASP.NET 2.0.

Sample Image

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

If you're getting started with MySQL and ASP.NET, then I recommend these steps:

  1. Go to the MySQL website, download and install “Current Release (recommended).
  2. Download and install: MySQL Administrator (to administer your MySQL server, the first download just installs only the server).
  3. Download and install: Connector/Net 1.0 (you need this to get your ASP.NET pages to talk to your MySQL server).
  4. You can also download: MySQL Query Browser – (a graphical client to work with your MySQL databases and run queries).
  5. Read and follow this guide: A Step-by-Step Guide to Using MySQL with ASP.NET.

Using the code

To install the code:

  1. You must have MySQL 5 up and running.
  2. Install MySQL Connector/Net 1.0.
  3. Create a MySQL 5 database named Test.
  4. 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;
  5. 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
  6. Unzip "MySQL" and configure IIS to point to it. Make sure you configure the web server to use ASP.NET 2.0.
  7. 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.

  8. 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!

License

This article, along with any associated source code and files, is licensed under The BSD License

Share

About the Author

defwebserver
Software Developer (Senior) http://ADefWebserver.com
United States United States
Michael Washington is a Microsoft MVP. He is a ASP.NET and
C# programmer.
He is the founder of
LightSwitchHelpWebsite.com

He has a son, Zachary and resides in Los Angeles with his wife Valerie.

He is the Author of:
Follow on   Twitter

Comments and Discussions

 
Questionthanks Pin
emperatorali at 2-Nov-12 21:24
memberemperatorali2-Nov-12 21:24 
GeneralMy vote of 5 Pin
Member 7746684 at 18-Apr-12 14:06
memberMember 774668418-Apr-12 14:06 
QuestionThank u Pin
saeedaly at 16-Apr-12 22:01
membersaeedaly16-Apr-12 22:01 
Generalthank for thius Article Pin
fresherincode at 23-Feb-12 3:08
memberfresherincode23-Feb-12 3:08 
GeneralRe: thank for thius Article Pin
defwebserver at 23-Feb-12 3:11
memberdefwebserver23-Feb-12 3:11 
QuestionHow to connect to a remote MySQL server using a C# application. Pin
hosseinhaddad at 25-Oct-11 23:58
memberhosseinhaddad25-Oct-11 23:58 
Questionnot getting the concept Pin
abdul1231231 at 18-Oct-11 0:53
memberabdul123123118-Oct-11 0:53 
AnswerRe: not getting the concept Pin
defwebserver at 18-Oct-11 3:21
mvpdefwebserver18-Oct-11 3:21 
GeneralBetter updated example with use of DataList and GridView Pin
simpa at 14-Mar-11 2:21
membersimpa14-Mar-11 2:21 
GeneralRe: Better updated example with use of DataList and GridView Pin
defwebserver at 3-Jul-11 15:08
mvpdefwebserver3-Jul-11 15:08 
GeneralRemote MySql Server Connection with C# and .NET Compact Framework 2.0 Pin
Asif Basha at 8-Sep-09 22:17
memberAsif Basha8-Sep-09 22:17 
GeneralRe: Remote MySql Server Connection with C# and .NET Compact Framework 2.0 Pin
defwebserver at 9-Sep-09 3:02
memberdefwebserver9-Sep-09 3:02 
GeneralThanks Pin
multa at 27-Mar-09 16:02
membermulta27-Mar-09 16:02 
Generalconnecting VB with Mysql Pin
boris111 at 6-Mar-09 20:13
memberboris1116-Mar-09 20:13 
QuestionCan't Create Procedure, need help Pin
zie1986 at 7-Jan-09 0:08
memberzie19867-Jan-09 0:08 
QuestionInsert, delete and updated without stored proceduces in database? Pin
Hoh Tat Heng at 4-Dec-08 22:25
memberHoh Tat Heng4-Dec-08 22:25 
AnswerRe: Insert, delete and updated without stored proceduces in database? Pin
defwebserver at 5-Dec-08 3:21
memberdefwebserver5-Dec-08 3:21 
GeneralHelp from SP Pin
njuniorba at 17-Oct-08 7:48
membernjuniorba17-Oct-08 7:48 
GeneralRe: Help from SP Pin
defwebserver at 17-Oct-08 8:09
memberdefwebserver17-Oct-08 8:09 
Generalwhank for help Pin
Ravipabbathi at 2-Jun-08 21:38
memberRavipabbathi2-Jun-08 21:38 
QuestionDataObjectMethod Select by record ID Pin
punt3r at 22-May-08 15:09
memberpunt3r22-May-08 15:09 
AnswerRe: DataObjectMethod Select by record ID Pin
defwebserver at 22-May-08 16:18
memberdefwebserver22-May-08 16:18 
GeneralRe: DataObjectMethod Select by record ID Pin
punt3r at 22-May-08 17:13
memberpunt3r22-May-08 17:13 
GeneralRe: DataObjectMethod Select by record ID Pin
defwebserver at 22-May-08 18:04
memberdefwebserver22-May-08 18:04 
GeneralRe: DataObjectMethod Select by record ID Pin
punt3r at 22-May-08 18:45
memberpunt3r22-May-08 18:45 
GeneralRe: DataObjectMethod Select by record ID Pin
punt3r at 22-May-08 19:52
memberpunt3r22-May-08 19:52 
GeneralRe: DataObjectMethod Select by record ID Pin
defwebserver at 23-May-08 3:01
memberdefwebserver23-May-08 3:01 
GeneralRe: DataObjectMethod Select by record ID Pin
punt3r at 23-May-08 3:21
memberpunt3r23-May-08 3:21 
GeneralC# Form communicate with MySql Database Pin
bdiepeveen at 13-Apr-08 22:10
memberbdiepeveen13-Apr-08 22:10 
QuestionGrid View - Online Exam Application. [modified] Pin
Ashok H at 30-Aug-07 20:04
memberAshok H30-Aug-07 20:04 
QuestionProblem by assigning data into the Detailsview item for inserting new records Pin
NewIn123 at 6-Aug-07 23:38
memberNewIn1236-Aug-07 23:38 
GeneralThanks Pin
Yulianto. at 18-Jul-07 17:55
memberYulianto.18-Jul-07 17:55 
GeneralGeneral advice on v. 5+ &amp; 1.0.7 + connectors Pin
plemon at 15-Feb-07 11:33
memberplemon15-Feb-07 11:33 
Question#42000SELECT command denied [modified] Pin
Rex1024 at 26-Sep-06 3:44
memberRex102426-Sep-06 3:44 
QuestionRe: #42000SELECT command denied Pin
dylf at 20-Apr-07 10:55
memberdylf20-Apr-07 10:55 
AnswerRe: #42000SELECT command denied Pin
yrodrigu at 19-Oct-08 9:30
memberyrodrigu19-Oct-08 9:30 
QuestionHow to build a Selectable Master GridView with a Details DetailView page based on your example? Pin
Perth_shan at 4-Sep-06 23:13
memberPerth_shan4-Sep-06 23:13 
AnswerRe: How to build a Selectable Master GridView with a Details DetailView page based on your example? Pin
Perth_shan at 6-Sep-06 21:00
memberPerth_shan6-Sep-06 21:00 
GeneralConnection to MySQL Pin
Dewald Troskie at 7-Aug-06 11:32
memberDewald Troskie7-Aug-06 11:32 
GeneralConnectDB MySQL4.1 Pin
HoanglkKHTN at 19-Jul-06 23:04
memberHoanglkKHTN19-Jul-06 23:04 
GeneralRe: ConnectDB MySQL4.1 Pin
mardc at 28-Dec-06 7:42
membermardc28-Dec-06 7:42 
GeneralRe: ConnectDB MySQL4.1 Pin
Member 1624150 at 13-Apr-08 10:14
memberMember 162415013-Apr-08 10:14 
Generalnulls in datareader Pin
pkellner at 1-Jun-06 15:52
memberpkellner1-Jun-06 15:52 
GeneralRe: nulls in datareader Pin
defwebserver at 1-Jun-06 16:12
memberdefwebserver1-Jun-06 16:12 
GeneralNice article, I moved from MySQL to MS-SQL though [modified] Pin
neilio at 28-May-06 8:30
memberneilio28-May-06 8:30 
GeneralRe: Nice article, I moved from MySQL to MS-SQL though Pin
coolg444 at 7-Apr-07 7:25
membercoolg4447-Apr-07 7:25 
GeneralRe: Nice article, I moved from MySQL to MS-SQL though Pin
neilio at 7-Apr-07 7:51
memberneilio7-Apr-07 7:51 
GeneralExcellent Article Pin
Dewald Troskie at 26-May-06 3:43
memberDewald Troskie26-May-06 3:43 
GeneralHelped a lot :) Pin
plintus at 16-May-06 6:55
memberplintus16-May-06 6:55 
GeneralVery good... Pin
Bacos at 16-May-06 3:23
memberBacos16-May-06 3:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150427.1 | Last Updated 15 May 2006
Article Copyright 2006 by defwebserver
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid