如何使用 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);
   }
}

在編譯程序時，我在數據庫中只有連接失敗消息框。我發現正確，如何克服程序以獲取連接成功消息框。

好吧，您正在填充ds數據集 - 但隨後您正在檢查dt數據表中是否存在行……當然，這永遠不會奏效！

如果您只需要一個DataTable- 只需單獨使用和填充該數據表 - 不需要DataSet. 另外，將你的SqlConnectionandSqlCommand放入使用這樣的塊中：

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

並且僅僅因為您沒有返回任何行dt.Rows並不一定意味著您的連接失敗……可能只是沒有符合您的搜尋條件的行！連接工作得很好——但 SQL 命令沒有返回任何行。

引用自：https://stackoverflow.com/questions/18041846