Asp.net C# Mysql使用存储过程从数据库中获取数据

Question

我正在尝试使用C#中的存储过程从MySQL数据库中获取数据，更准确地说，我正在遵循三层体系结构。我不确定以下代码中的错误之处，我已经尝试了所有可能的小帮助，将非常感谢。

DataServices层

    public DataSet FetchLoginDetails(string SchoolID)
    {
        object[] objparam = new object[1];
        objparam[0] = new MySqlParameter
        {
            ParameterName = "@SchoolID",
            DbType = System.Data.DbType.String,
            Value =SchoolID
        };

        DataSet dsdataRes = ExecuteQuery("Storeprociduer", objparam);
        return dsdataRes;

    }

BusinessServices

    private ITimeTableRepository ObjTimeTable
    {
        get { return UnityManager.Resolve<ITimeTableRepository>(); }
    }
    public DataSet FetchLoginDetails(string SchoolID)
    {
        return ObjTimeTable.FetchLoginDetails(SchoolID);
    }

BusnessServices.Interfaces

public interface ITimeTableBO
    {
        DataSet FetchLoginDetails(string SchoolID);
    }

UI .cs

public void LoadLoginDetails(DataSet ds)
    {
        if (ds.Tables.Count > 0)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                ddlSubject.Items.Clear();

                ListItem item1 = new ListItem();
                item1.Text = "Choose a Subject";
                item1.Value = "-1";
                ddlSubject.Items.Add(item1);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {

                    ListItem item = new ListItem();
             item.Text = ds.Tables[0].Rows[i]["UserName"].ToString();
           item.Value = ds.Tables[0].Rows[i]["UserName"].ToString();
                    ddlSubject.Items.Add(item);
                }
            }

Aspx代码

                        <asp:DropDownList ID="ddlSubject" runat="server" Width="150px" 

Height="27px" ClientIDMode="Static"
                            AutoPostBack="true" >
                            <asp:ListItem Value="-1" Text="Choose a Subject"></asp:ListItem>
                        </asp:DropDownList>

存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `Storeprociduer`(
SchoolID 

VARCHAR(255)
)
BEGIN

select UserName from scoolage_login where SchoolID = 

SchoolID ;


END

Answer 1

如果不查看代码的其他细节，我假设您无法连接到MySQL数据库并从其中检索数据。看一下MySqlCommand对象。还有一些代码示例：

https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html

尝试如下所示：

    using (MySqlConnection con = new MySqlConnection(yourConnectionString))
    {
        using (MySqlCommand cmd = new MySqlCommand("Storeprociduer", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@SchoolID", SchoolID);
            using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                // Do something with results
            }
        }
    }

也可以看看这个SO问题：

calling mysql storedprocedure from c#?