CODEWITHSUNDEEP
c#asp.netsql-servervisual-studiowebforms
Omar Hussein
Omar Hussein

Reputation: 1147

Asynchronous calls in ASP.net webforms stored procedures

Stored procedures

CREATE PROCEDURE Contributor_Search
    @fullname VARCHAR(60)
AS
    SELECT 
        C.id, years_of_experience, portfolio_link, specialization,
        notified_id, email, first_name, middle_name, last_name,
        birth_date, age 
    FROM 
        Contributor C 
    INNER JOIN 
        [User] U ON C.id = U.id
    WHERE 
        U.first_name + ' ' + U.middle_name + ' ' + U.last_name = @fullname

CREATE PROCEDURE Show_Original_Content
    @contributor_id INT
AS
    IF @contributor_id IS NULL
        SELECT * 
        FROM Original_Content OC 
        INNER JOIN Content C ON OC.id = C.id 
        INNER JOIN Contributor CO ON C.contributor_id = CO.id 
        WHERE OC.filter_status = 1
    ELSE
        SELECT * 
        FROM Original_Content OC 
        INNER JOIN Content C ON OC.id = C.id 
        INNER JOIN Contributor CO ON C.contributor_id = CO.id 
        WHERE OC.filter_status = 1 AND CO.id = @contributor_id

I want to run the first stored procedure if input is provided, and if not just jump into the second with null; if input is provided and it's ran however, I would like to get an ID using the first procedure and then use it in the second procedure, this is my current approach which unfortunately does not work.

protected void btnSearch_Click(object sender, EventArgs e)
{
    string connectionStr = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=iEgypt;";

    if(inputName.Value.Trim() != "")
    {
        using (SqlConnection con = new SqlConnection(connectionStr))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Contributor_Search";
            cmd.CommandType = CommandType.StoredProcedure;

            if (inputName.Value.Trim() != "")
            {
                SqlParameter param = new SqlParameter("@fullname", inputName.Value);
                cmd.Parameters.Add(param);
            }
            else
            {
                SqlParameter param = new SqlParameter("@fullname", DBNull.Value);
                cmd.Parameters.Add(param);
            }

            con.Open();

            SqlDataReader rdr = cmd.ExecuteReader();

            id = rdr[0].ToString();
            con.Close();
        }
    }

    using (SqlConnection con = new SqlConnection(connectionStr))
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "Show_Original_Content";
        cmd.CommandType = CommandType.StoredProcedure;

        if (id != "")
        {
            SqlParameter param = new SqlParameter("@contributor_id", inputName.Value);
            cmd.Parameters.Add(param);
        }
        else
        {
            SqlParameter param = new SqlParameter("@contributor_id", DBNull.Value);
            cmd.Parameters.Add(param);
        }

        con.Open();

        SqlDataReader rdr = cmd.ExecuteReader();

        gvSearchResults.DataSource = rdr;
        gvSearchResults.DataBind();
    }
}

Any help is much appreciated.

Upvotes: 2

Views: 196

Answers (1)

dana
dana

Reputation: 18155

In the comments, @vjgn suggests calling the Read method before accessing the rows in the SqlDataReader. For example:

SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
    id = rdr[0].ToString();
}

That should work... Or alternatively you could use the ExecuteScalar method and not have to worry about opening the reader:

id = cmd.ExecuteScalar()?.ToString() ?? "";

The problem with that is now you are having to check for null, then converting it into an empty string. It is probably better to avoid converting to empty string and just check for both in your if statement.

id = cmd.ExecuteScalar()?.ToString();

...

if (!String.IsNullOrEmpty(id))

Another potential point of error is if the record has a null value in first_name, middle_name, or last_name then your search won't find any results because when you concatenate a null value with a non-null value, you get null. Try changing the where clause to the following:

isnull(U.first_name,'') + ' ' + isnull(U.middle_name,'') + ' ' + isnull(U.last_name,'') = @fullname

This looks like it does an exact search...

Upvotes: 1

Related Questions