Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 893
user2132564 Avatar asked Aug 04 '13 10:08

user2132564


People also ask

What is the command type to be used for consuming the stored procedure?

In addition to commands built with strings, the SqlCommand type can be used to execute stored procedures. There are two tasks required to make this happen: let the SqlCommand object know which stored procedure to execute and tell the SqlCommand object that it is executing a stored procedure.

How use stored procedure in C# console application?

You can execute a stored procedure programmatically using the command object. Instead of passing a SQL statement, you pass the stored procedure name as the SQL statement to execute a stored procedure. Each data provider provides a command object to execute SQL statements.


1 Answers

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.

like image 193
marc_s Avatar answered Oct 03 '22 18:10

marc_s