C# 创建调用存储过程

一、创建存储过程

PROCEDURE `test`.`DeleteMessage`(IN param1 INT)BEGINDelete From test.messageWHERE Entry_ID = param1;ENDPROCEDURE `test`.`InsertMessage`(IN param1 VARCHAR(50), IN param2 VARCHAR(50), IN param3 VARCHAR(200))BEGININSERT INTO message(Name, Email, Message)VALUES(param1,param2,param3);ENDPROCEDURE `test`.`ShowAll`()BEGINSELECT   message.Entry_ID,   message.Name,   message.Email,   message.MessageFROM  test.message;ENDPROCEDURE `test`.`UpdateMessage`(IN paramkey INT, IN param1 VARCHAR(50), IN param2 VARCHAR(50), IN param3 VARCHAR(200))BEGINUPDATE    messageSET              Name = param1, Email = param2, Message = param3WHERE     (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>