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

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

Display multiple Query results C#

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Display multiple Query results C#

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us