Login
MySQL » MySql 5 sample
 
MySQL 5 C# sample code using ObjectDataSources


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 http://www.mysql.com/ 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

To install the code:

1) You must have MySQL 5 up and running

2) Install
MySQL Connector/Net 1.0 from: http://dev.mysql.com/downloads/connector/net/1.0.html

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 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 the "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 this 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 that the assembly "MySql.Data.dll" in the "/bin" directory so the "MySql.Data.MySqlClient" will work.

I hope this helps!

      

Click on the Download button in the bottom right hand corner to download sample
  Search for     Sort by     
 
C# MySql 5 sample code using ObjectDataSources
Click to view Author: Michael Washington
Author's EMail Address:
Number of Downloads: 1472  File size: 65 K
Date Created: 5/7/2006 2:54:46 PM
Date Uploaded: 12/17/2006 11:42:34 AM

File Description:
MySQL 5 C# sample code using ObjectDataSources

Rating:   COMMENTS (7)  DOWNLOAD

[<< BACK]    1 OF 1   [NEXT >>] 
    

Terms Of Use | Privacy Statement | Adefwebserver - Los Angeles, CA Dynnamite DotNetNuke Skins & Modules