[code language="C#"]

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SqlParametersDemo
{
    class Program
    {
        static string connectionString = "server=local;database=yourdatabase;userid=youruserid;password=yourpassword;";
        static void Main(string[] args)
        {
            ShowDemo();
            Console.ReadLine();
        }

        static void ShowDemo()
        {
            SimpleQuery();
            SimpleParameterQuery();
            SqlParameterQuery();
        }

        /// <summary>
        /// This function demonstrates how to generate a DataTable using a simple sql statement
        /// </summary>
        private static void SimpleQuery()
        {
            string sqlStatement = "select * from users;";

            DataTable table = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(sqlStatement, connectionString);
            adapter.Fill(table);

            PrintHeader("Simple Query.");
            IterateThruDataTableRowsAndColumns(table);
        }

        /// <summary>
        /// This function demonstrates how to pass a parameter to sql staement and then generate the DataTable
        /// </summary>
        private static void SimpleParameterQuery()
        {
            int userid = 2;
            string sqlStatement = "select * from users where userid = " + userid + ";";

            DataTable table = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(sqlStatement, connectionString);
            adapter.Fill(table);

            PrintHeader("Simple Parameter Query.");
            IterateThruDataTableRowsAndColumns(table);
        }

        /// <summary>
        /// This function demonstrates how to use SQLParameters to pass values to a sql statement.
        /// </summary>
        private static void SqlParameterQuery()
        {
            int userid = 1;
            string sqlStatement = "select * from users where userid = @userid;";

            DataTable table = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(sqlStatement, connectionString);

            //assign a new sqlparameter to our adapter command
            adapter.SelectCommand.Parameters.Add(new SqlParameter("@userid", userid));
           
            //
            adapter.Fill(table);

            PrintHeader("SQL Parameter query.");
            IterateThruDataTableRowsAndColumns(table);
        }

        /// <summary>
        /// This function demonstrates how to use SQLParameters to pass values to a sql statement.
        /// </summary>
        private static void StoredProcedureWithSqlParametersQuery()
        {
            int userid = 1;
            string username = "keithrull";
            string sqlStatement = "sp_Users_GetUserList";

            DataTable table = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(sqlStatement, connectionString);

            //tell the SqlDataAdapter that the command string is a stored procedure;
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

            //we can add as many parameters as we want
            adapter.SelectCommand.Parameters.Add(new SqlParameter("@userid", userid));
            adapter.SelectCommand.Parameters.Add(new SqlParameter("@username", username));

            //fill our datatable
            adapter.Fill(table); //<-- this can be a datatable or a dataset

            //print the header to the screen
            PrintHeader("Stored Procedure Parameter query.");
            IterateThruDataTableRowsAndColumns(table);
        }

        static void IterateThruDataTableRowsAndColumns(DataTable table)
        {
            //iterate thru the rows
            foreach (DataRow dataRow in table.Rows)
            {
                //iterate thru the columns
                foreach (DataColumn dataColumn in table.Columns)
                {
                    Console.Write(dataRow[dataColumn].ToString());
                    Console.Write("|");
                }
                Console.WriteLine("");
            }
            Console.WriteLine("");
        }

        static void PrintHeader(string headline)
        {
           
            Console.WriteLine(headline);
            Console.WriteLine("----------------------------------------");
        }
    }
}

[/code]