Display multiple Query results C#

Question

4.00/5 (1 vote)

See more:

I am looking for a way to output multiple results from a single query, and be able to iterate through them so that I can Match single results to Form Fields. Here is a better example, and exactly what I am trying to do.

C#

string sql = "SELECT site_name, meter_no, address FROM meter "
 + "WHERE property_id = 39 "
 + "ORDER BY meter_no ASC LIMIT 2;";

I am outputting the result of this query currently to a PDF Form using itextsharp. The Site name, I only have to return once, but sometimes, there are multiple results of meter_no attached to each site name. The site name goes into a form field, and then isn't touched again. The meter_no is put into another field, but I want to be able to "Tab" through the results and fill in more fields based off of subsequent meter_no's tied to the same site name. Is this possible using C#? I am using Visual Studio, and My Database is a Postgresql.

If you need any clarification, do not hesitate to ask, I'm not sure if I did a suitable job explaining exactly what I need to accomplish.

Posted 18-Jun-12 3:55am

MikeVaros

Add a Solution

2 solutions

Solution 2

In Addition to Mike's solution you should urgently use Parameter Property to avoid SQL-Injection in your query.

C#

string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

Regards

Posted 18-Jun-12 6:15am

El_Codero

Comments

MikeVaros 18-Jun-12 14:01pm

Do you really think that would be necessary since I'm only using SELECT statements? I'm not a seasoned vet when it comes to SQL, so there's a good chance I'm wrong, and setting the Parameter Property is useful. My instinct would just tell me that if I was actually updating rows, I would need to have some security in place to prevent the query being hijacked. The data from the SELECT statements is processed, and inserted dynamically into PDF Forms.

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)

MikeVaros · Accepted Answer · 2012-06-18T05:48:00

C#

try
{
    conn.Open();
    NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
    NpgsqlDataReader dr = cmd.ExecuteReader();
    //NpgsqlTransaction tran = conn.BeginTransaction();

    while (dr.Read())
        //Retrieve Site Name and the first Meter Number/Address attached to the property id
    {
        String site_nameString = dr.GetString(0);
        String meterString = dr.GetString(1);
        String addressString = dr.GetString(2);

        while (dr.Read())
            //Retrieve the second Meter Number/Address.
        {
            NpgsqlCommand subCmd1 = new NpgsqlCommand(subSql1, subConn1);
            String meter2String = dr.GetString(1);
            String address2String = dr.GetString(2);

            while (dr.Read())
                //Retrieve the third Meter Number/Address.
            {
                NpgsqlCommand subCmd2 = new NpgsqlCommand(subSql2, subConn2);
                String meter3String = dr.GetString(1);
                String address3String = dr.GetString(2);