CODEWITHSUNDEEP
.netsql-serverc#-4.0
Antarr Byrd
Antarr Byrd

Reputation: 26169

Using data from query

I want to pass the records found by my select query into a Geocoding function. I have the function and the query but don't know how to pass when select to the function.

//Geocoding Constructor
GeocodeAddress(geodata address);


//Method calling Query
static void updateDBS()
{
    string conStr =
     @"Data Source=PEGASUS2\sqlexpress;Initial Catalog=gdata;Integrated Security=True";

    //MessageBox.Show("Constr=" + conStr);
    SqlConnection con = new SqlConnection();
    con.ConnectionString = conStr;
    con.Open();

    string stm = @"SELECT TOP 200 [id]
          ,[agency]
          ,[calltime]
          ,[incidentType]
          ,[city]
          ,[state]
          ,[intersection]
    FROM [gdata].[dbo].[geodata]
    ORDER BY id ASC";

    //SqlCommand cmd = new SqlCommand(stm, con);
    //SqlDataReader reader = cmd.ExecuteReader();

    con.Close();
}

Upvotes: 1

Views: 76

Answers (2)

ChrisLively
ChrisLively

Reputation: 88092

Something like the following should work. I have no idea what properties exist in the geodata class or even what method you want to call, but here's the general structure for iterating through a result set from your query.

using (SqlConnection con = new SqlConnection(conStr)) {
    con.Open();
    using (SqlCommand cmd = new SqlCommand(stm, con)) {
        using (SqlDataReader reader = cmd.ExecuteReader()) {
            while (reader.Read()) {
                geodata address = new geodata();
                // assign properties to address object
                address.Agency = reader["agency"].ToString();
                // call your method
            }
        }
    }
}

Note the use of Using clauses around everything that implements IDisposable. This is very important for working with unmanaged code.

Upvotes: 1

taylonr
taylonr

Reputation: 10790

You'd have to do something like:

List<GeoCode> geoCodes = new List<GeoCode>();
while(reader.Read())
{
   GeoCode geoCode = new GeoCode();
   geoCode.Agency = reader.GetString(reader.GetOrdinal("agency"));
   geoCode.CallTime = reader.GetDateTime(reader.GetOrdinal("calltime"));
   geoCodes.Add(geoCode);
}

Another option would be to use a framework, like EntityFramework. That would allow you to get a list of GeoCodes by doing something like:

var geoCodes = data.GeoData.OrderBy(g => g.Id).Take(200);

Where data is your data context in EF. This returns an IEnumerable of GeoData objects.

Upvotes: 1

Related Questions