Click here to Skip to main content
6,295,667 members and growing! (16,767 online)
Email Password   helpLost your password?
Database » Database » General     Intermediate License: The BSD License

MySQL 5 C# sample code using ObjectDataSources

By defwebserver

A simple example using MySQL 5 and stored procedures with ObjectDataSources and Generics, in ASP.NET 2.0.
C#, SQL, HTML, Windows, .NET 2.0, ASP.NET, ADO.NET, MySQL, VS2005, DBA, Dev
Posted:15 May 2006
Views:127,523
Bookmarked:76 times
Announcements
Loading...
 
Search    
Advanced Search
printPrint   Broken Article?Report       add Share
  Discuss Discuss   Recommend Article Email
21 votes for this article.
Popularity: 5.36 Rating: 4.05 out of 5
1 vote, 4.8%
1

2
3 votes, 14.3%
3
5 votes, 23.8%
4
12 votes, 57.1%
5

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

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

About the Author

defwebserver


Member
Michael Washington is a Website developer and an ASP.NET, C#, and Visual Basic programmer. He has extensive knowledge in process improvement, billing systems, and credit card transaction processing. He is a DotNetNuke Core member and has been involved with DotNetNuke for nearly 4 years. He is the author of numerous DotNetNuke modules and tutorials. He is one of the founding members of the Southern California DotNetNuke Users group (http://socaldug.org). He has a son, Zachary and resides in Los Angeles with his wife Valerie.
Occupation: Web Developer
Location: United States United States

Other popular Database articles:

Article Top
You must Sign In to use this message board.
FAQ FAQ 
 
Noise Tolerance  Layout  Per page   
 Msgs 1 to 25 of 41 (Total in Forum: 41) (Refresh)FirstPrevNext
GeneralThanks Pinmembermulta16:02 27 Mar '09  
Generalconnecting VB with Mysql Pinmemberboris11120:13 6 Mar '09  
QuestionCan't Create Procedure, need help Pinmemberzie19860:08 7 Jan '09  
GeneralInsert, delete and updated without stored proceduces in database? PinmemberHoh Tat Heng22:25 4 Dec '08  
GeneralRe: Insert, delete and updated without stored proceduces in database? Pinmemberdefwebserver3:21 5 Dec '08  
GeneralHelp from SP Pinmembernjuniorba7:48 17 Oct '08  
GeneralRe: Help from SP Pinmemberdefwebserver8:09 17 Oct '08  
Generalwhank for help PinmemberRavipabbathi21:38 2 Jun '08  
QuestionDataObjectMethod Select by record ID Pinmemberpunt3r15:09 22 May '08  
AnswerRe: DataObjectMethod Select by record ID Pinmemberdefwebserver16:18 22 May '08  
GeneralRe: DataObjectMethod Select by record ID Pinmemberpunt3r17:13 22 May '08  
GeneralRe: DataObjectMethod Select by record ID Pinmemberdefwebserver18:04 22 May '08  
GeneralRe: DataObjectMethod Select by record ID Pinmemberpunt3r18:45 22 May '08  
GeneralRe: DataObjectMethod Select by record ID Pinmemberpunt3r19:52 22 May '08  
GeneralRe: DataObjectMethod Select by record ID Pinmemberdefwebserver3:01 23 May '08  
GeneralRe: DataObjectMethod Select by record ID Pinmemberpunt3r3:21 23 May '08  
GeneralC# Form communicate with MySql Database Pinmemberbdiepeveen22:10 13 Apr '08  
QuestionGrid View - Online Exam Application. [modified] PinmemberAshok H20:04 30 Aug '07  
QuestionProblem by assigning data into the Detailsview item for inserting new records PinmemberNewIn12323:38 6 Aug '07  
GeneralThanks PinmemberYulianto.17:55 18 Jul '07  
GeneralGeneral advice on v. 5+ & 1.0.7 + connectors Pinmemberplemon11:33 15 Feb '07  
Question#42000SELECT command denied [modified] PinmemberRex10243:44 26 Sep '06  
QuestionRe: #42000SELECT command denied Pinmemberdylf10:55 20 Apr '07  
AnswerRe: #42000SELECT command denied Pinmemberyrodrigu9:30 19 Oct '08  
GeneralHow to build a Selectable Master GridView with a Details DetailView page based on your example? PinmemberPerth_shan23:13 4 Sep '06  

General General    News News    Question Question    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

PermaLink | Privacy | Terms of Use
Last Updated: 15 May 2006
Editor: Smitha Vijayan
Copyright 2006 by defwebserver
Everything else Copyright © CodeProject, 1999-2009
Web15 | Advertise on the Code Project