如何使用ado.net调用存储过程 [英] How to call a stored procedure using ado.net

问题描述

private void button1_Click(object sender, EventArgs e)
{
    try
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Data Source=*******;Initial Catalog=ChatApp;User ID=Chatapplication;Password=****";
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        string chatroomidno = textBox1.Text;
        string chatroomname = textBox2.Text;
        //cmd.CommandText = "Select ChatRoomID=@ChatRoomID,ChatRoomName=@ChatRoomName from tblChatRoom";
        //cmd.Connection = conn;
        SqlDataAdapter adapt = new SqlDataAdapter("Chatroomapp",conn);
        adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
        DataSet ds=new DataSet();
        DataTable dt = new DataTable();
        adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomID", SqlDbType.VarChar, 100));
        adapt.SelectCommand.Parameters["@ChatRoomID"].Value = chatroomidno;
        adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomName", SqlDbType.VarChar, 50));
        adapt.SelectCommand.Parameters["@ChatRoomName"].Value = chatroomname;
        adapt.Fill(ds, "tblChatRoom");
        if (dt.Rows.Count > 0)
        {
            MessageBox.Show("Connection Succedded");
        }
        else
        {
            MessageBox.Show("Connection Fails");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error", ex.Message);
    }
}

在编译程序时，我只有连接失败消息框，在数据库中。我发现是正确的，如何克服该程序以获得连接成功的消息框。

While compiling the program I got only connection fails message box, in the database. I found correct, how to overcome the program to get the connection succeeded message box.

推荐答案

好吧，您正在填写 ds 数据集-但随后您要检查 dt 数据表中是否存在行...这永远不会

Well, you're filling the ds data set - but then you're checking the dt data table for presence of rows... that's never going to work, of course!

如果您只需要一个 DataTable -只需单独使用并填写该数据表-无需 DataSet 的开销。另外，将您的 SqlConnection 和 SqlCommand 放入如下所示的块中：

If you only need a single DataTable - just use and fill that data table alone - no need for the overhead of a DataSet. Also, put your SqlConnection and SqlCommand into using blocks like this:

using (SqlConnection conn = new SqlConnection("Data Source=*******;Initial Catalog=ChatApp;User ID=Chatapplication;Password=****"))
using (SqlCommand cmd = new SqlCommand("Chatroomapp", conn))
{
    string chatroomidno = textBox1.Text;
    string chatroomname = textBox2.Text;

    SqlDataAdapter adapt = new SqlDataAdapter(cmd);
    adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
    adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomID", SqlDbType.VarChar, 100));
    adapt.SelectCommand.Parameters["@ChatRoomID"].Value = chatroomidno;
    adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomName", SqlDbType.VarChar, 50));
    adapt.SelectCommand.Parameters["@ChatRoomName"].Value = chatroomname;

    // fill the data table - no need to explicitly call `conn.Open()` - 
    // the SqlDataAdapter automatically does this (and closes the connection, too)
    DataTable dt = new DataTable();
    adapt.Fill(dt);

    if (dt.Rows.Count > 0)
    {
       MessageBox.Show("Connection Succedded");
    }
    else
    {
       MessageBox.Show("Connection Fails");
    }
}

仅因为您没有获得<$中的任何行c $ c> dt.Rows 不一定意味着您的连接失败了……..可能只是没有与您的搜索标准匹配的行！连接工作正常-但是SQL命令没有返回任何行。

And just because you get back no rows in dt.Rows doesn't necessarily mean that your connection failed..... it could just be that there are no rows that match your search critieria! The connection worked just fine - but the SQL command just didn't return any rows.

这篇关于如何使用ado.net调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！