How to return multiple values from query

Question

0.00/5 (No votes)

See more:

I am currently trying to use while loop for my query but its failing to return multiple records. the current output i am getting is one record, instead i should be getting 3 in total.

Please advice what I may be doing wrong - Many thanks.

How I am testing the query:

C#

protected void Page_Load(object sender, EventArgs e)
        {

            string issue = storyURLSF();
            Label1.Text = Server.HtmlEncode(issue);
        }

What I have tried:

C#

public static string storyURLSF()
        {
            string article = "";
            int publication = 0;
            int issue = 0;
            int storyid = 0;

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["##"].ConnectionString);

            string commandtext = "#####";
            SqlCommand command = new SqlCommand(commandtext, con);
            con.Open();
            command.Parameters.Add(new SqlParameter("title", article));
            command.Parameters.Add(new SqlParameter("PUBLICATION_ID", publication));
            command.Parameters.Add(new SqlParameter("ISSUE_ID", issue));
            command.Parameters.Add(new SqlParameter("STORYID", storyid));

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {

                string name = reader.GetString(0);
                int pub = reader.GetInt32(1);
                int iss = reader.GetInt32(2);
                int sid = reader.GetInt32(3);

                //convertion to string
                string str = Convert.ToString(pub);
                string iss1 = Convert.ToString(iss);
                string sid1 = Convert.ToString(sid);


                var builder = new UriBuilder("http://www.structuredcreditinvestor.com/Article.asp?");
                builder.Port = -1;
                var query = HttpUtility.ParseQueryString(builder.Query);
                // string nm = System.Web.HttpUtility.UrlPathEncode(name);
                query["article"] = name;
                query["ISS"] = str;
                query["PUB"] = iss1;
                query["SID"] = sid1;
                builder.Query = query.ToString();
                string url = builder.ToString();
                return url;
            }
            return article;
        }

Posted 22-Jun-16 1:51am

miss786

Updated 22-Jun-16 21:53pm

Garth J Lancaster

v2

Add a Solution

Comments

[no name] 22-Jun-16 8:10am

I think the problem is in your query (comandTex). verify the query by running in the sql management studio. if there it returns 3 then problem is in this code.

F-ES Sitecore 22-Jun-16 8:19am

"return url;" - this terminates the function so stops your loop so you only return the first url. If you want to return multiple urls you'll need to return List<string>, so create a List<string> object at the start of your function, and add the url to the list in your loop, then return the List<string> after the loop.

Your next problem is how you show that list on the client, you can't ToString it as a list has no text representation, you'll need to do something to make it display ok on the page, but we don't know what you're ultimately trying to do so it's hard to suggest a solution.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2016-06-22T02:17:00

you problem appears to be with this

C#

var builder = new UriBuilder("http://www.structuredcreditinvestor.com/Article.asp?");
builder.Port = -1;
var query = HttpUtility.ParseQueryString(builder.Query);
// string nm = System.Web.HttpUtility.UrlPathEncode(name);
query["article"] = name;
query["ISS"] = str;
query["PUB"] = iss1;
query["SID"] = sid1;
builder.Query = query.ToString();
string url = builder.ToString();
return url;

you loop through the returned data, but build a new object to return every time rather than extend/add to the data - hence what I'm pretty sure you'll see is the data returned as the last record selected from the database - using the debugger should confirm this

I dont know what 'methodology' you're using with this builder/query - If I were returning a string, I would init it outside of the while reader.Read() loop, and extend/add to it inside the loop - then when the loop is finished, I'd return the entire string - I dont know how that works/translates into the tools you're using

Prateek Dalbehera · Answer 2 · 2016-06-22T21:53:00

Hi miss786,

in while loop you are calling "return", so in first time only it will return the current url and go out of the loop, so it will not enter into loop again. that's why , you are not getting 3 values.

Also, if want to return 3 values than return type of the method should be a string array or list or if its string then it should be concatenated with some delimiters.

Below is solution to your problem,

1. string array or list

C#

//public static ArrayList storyURLSF()
public static string[] storyURLSF()
        {
            string article = "";
            string[] articles = new string[3];//if only 3 for sure else use arraylist if it varies
            int count = 0; // to keeep a count to add in array
            ArrayList alArticles = new ArrayList();//if arraylist, then you should change return type
            int publication = 0;
            int issue = 0;
            int storyid = 0;
 
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["##"].ConnectionString);
 
            string commandtext = "#####";
            SqlCommand command = new SqlCommand(commandtext, con);
            con.Open();
            command.Parameters.Add(new SqlParameter("title", article));
            command.Parameters.Add(new SqlParameter("PUBLICATION_ID", publication));
            command.Parameters.Add(new SqlParameter("ISSUE_ID", issue));
            command.Parameters.Add(new SqlParameter("STORYID", storyid));
 
            SqlDataReader reader = command.ExecuteReader();
 
            while (reader.Read())
            {
 
                string name = reader.GetString(0);
                int pub = reader.GetInt32(1);
                int iss = reader.GetInt32(2);
                int sid = reader.GetInt32(3);
 
                //convertion to string
                string str = Convert.ToString(pub);
                string iss1 = Convert.ToString(iss);
                string sid1 = Convert.ToString(sid);
 

                var builder = new UriBuilder("http://www.structuredcreditinvestor.com/Article.asp?");
                builder.Port = -1;
                var query = HttpUtility.ParseQueryString(builder.Query);
                // string nm = System.Web.HttpUtility.UrlPathEncode(name);
                query["article"] = name;
                query["ISS"] = str;
                query["PUB"] = iss1;
                query["SID"] = sid1;
                builder.Query = query.ToString();
                string url = builder.ToString();
                //Your mistake here
                //return url;
                //Solution:
		articles[count] = url;//assign & increase count
                 count ++; //increment
		//or if arraylist(which is better if memory is not an issue)
                alArticles.Add(url);
            }
	    //after looping is finished, then only return
            // make sure return type is array or arraylist
            return articles; //if array
	//OR
	return alArticles;

        }

if you want string return type, then append each url by a delimiter and spilt it for individual values.

Thanks,
Prateek

How to return multiple values from query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0