Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# PostgreSQL query
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.
 
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 4:55am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

            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);
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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

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
  Permalink  
Comments
MikeVaros at 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.

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

  Print Answers RSS
0 OriginalGriff 390
1 Sergey Alexandrovich Kryukov 329
2 Afzaal Ahmad Zeeshan 244
3 BillWoodruff 210
4 Maciej Los 192
0 OriginalGriff 5,560
1 DamithSL 4,476
2 Maciej Los 3,942
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,175


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 18 Jun 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100