How to call a stored procedure using ADO.NET?

ado.net, asp.net, c#, sql-server, stored-procedures

Solution

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!

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

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.

Problem

Original source