How use the parameterized queries in C#

Introduction

In msdn forums, a common question is regarding database retrieval and updation. To help such users I thought of bringing up this article. I want to give a concrete example, which will demonstrate the usage. To start with, I have a Employee database with a table named EmployeeDetails witht the following structure.

In the below code sample, I will show how to insert a record and get it back. To avoid the problems that can cause from SQL Injection, .Net framework has introduced Parameterized queries. Another way to get around this situation is using stored procedures.

using System;
using System.Data.SqlClient;

namespace ConsoleApplication2
{    
    class Program
    {
        public static void Main(string[] args)
        {
            string connectionString = "server=.;initial catalog=employee;user id=sa;password=sa123";

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("INSERT INTO EmployeeDetails Values(@Id, @Name, @Address)", conn))
                {
                    cmd.Parameters.Add("@Id", System.Data.SqlDbType.Int);
                    cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar);
                    cmd.Parameters.Add("@Address", System.Data.SqlDbType.VarChar);

                    cmd.Parameters["@Id"].Value = 4;
                    cmd.Parameters["@Name"].Value = "Rooney";
                    cmd.Parameters["@Address"].Value = "Manchester United";

                    int rowsInserted = cmd.ExecuteNonQuery();

                    Console.WriteLine("Rows inserted = " + rowsInserted);
                }

                using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeDetails", conn))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader["Id"].ToString() + " " + reader["Name"].ToString() + " " + reader["Address"].ToString());
                        }
                    }
                    else
                    {
                        Console.WriteLine("No Rows");
                    }
                }
            }
        }
    }
}

Hope this was helpful.