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("----------------------------------------");
      }
   }
}