C# 创建调用存储过程

一、创建存储过程

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

二、调用存储过程

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;
    }
}

三、创建实体类

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;
        }
    }
}


四、前台页面

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

五、配置文件

<?xml version="1.0"?>
<configuration>
 <appSettings/>
 <connectionStrings>
  <add name="MySQLConnectionString" connectionString="server=localhost; user id=root; password=mypass; database=test; pooling=false;" providerName="MySql.Data.MySqlClient"/>
 </connectionStrings>
 <system.web>
 </system.web>
</configuration>