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