Connopen using dbdatareader reader cmdexecutereader

Info icon This preview shows pages 565–567. Sign up to view the full content.

cmd.Parameters.Add(stateParam); conn.Open(); using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string addressLine1 = reader.GetString(0); // AddressLine2 is nullable, so we need to be prepared to get // back either a string or a DBNull string addressLine2 = reader.GetValue(1) as string; string city = reader.GetString(2); Console.WriteLine(addressLine1); Console.WriteLine(addressLine2); Console.WriteLine(city); } } } The .NET Data Access Landscape | 541
Image of page 565

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

You might be wondering why we’re fiddling around with parameter objects when it would have been simpler to just put the state directly into the SQL string. This par- ticular example hardcodes the state, so that would have worked, but the technique here would be important if the value was picked at runtime. In general, building SQL queries with string concatenation is a dangerous thing to do—if any of the text comes from outside your code (e.g., from a form on a web page, or part of a URL) your code will be vulnerable to a SQL injection attack. Imagine that Example 14-1 was part of a web application, and state here came from part of a URL such as - info?state=California . Users are free to modify URLs—you can just type them into the address bar—so a malicious user might decide to modify that part of the URL. If the code just took the string from the URL and concatenated it directly into the SQL, we would effectively be giving anyone with an Internet connection the ability to run arbi- trary SQL commands on our database—SQL queries can contain multiple commands, so users would be able to add extra commands to run after the SELECT . Parameters are one way to avoid this, because the value of a parameter will not be treated as SQL. So it’s a good idea to get in the habit of using parameters whenever some part of the query needs to change at runtime. The API we used here directly reflects the steps needed to communicate with a database, so we have to write a lot of code to bridge between the queries, parameters, and columns of the database world and the world of C#. Just as a sneak preview, Example 14-2 shows the equivalent code using the Entity Framework. Notice that instead of having to build a parameter object for a parameterized query, we’ve just been able to use a LINQ where clause and the C# == comparison syntax. (The Entity Framework performs a parameterized query under the covers, so this is safe from SQL injection attacks.) Also notice that all the database columns are available as object properties, so we don’t have to call GetString or similar helpers to retrieve column values. Example 14-2. LINQ to Entities versus ADO.NET string state = "California"; using (var context = new AdventureWorksLT2008Entities()) { var addresses = from address in context.Addresses where address.StateProvince == state select address; foreach (var address in addresses) { Console.WriteLine(address.AddressLine1); Console.WriteLine(address.AddressLine2); Console.WriteLine(address.City); } } Example 14-1 has one obvious benefit in exchange for the complexity: we have com- plete control over the SQL query. You can’t see the SQL in
Image of page 566
Image of page 567
This is the end of the preview. Sign up to access the rest of the document.
  • Spring '15

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern