// Reader query - Basic - Get all rows from table Students
public static void BasicReaderQuery_1()
{
string query = "SELECT * FROM Students";
List <SqlParameter> parameters = new List <SqlParameter>();
ArrayList results = ConnectedDBAccess.ExecuteQuery <Student>(query, parameters);
printResults(results);
}
public ArrayList GetAllStudents()
{
string query = "SELECT * FROM Students";
List <SqlParameter> parameters = new List <SqlParameter>();
ArrayList results = ConnectedDBAccess.ExecuteQuery <Student>(query, parameters);
return(results);
}
// NonQuery - basic - set the age of all students from usa to 45
public static void BasicNonQuery_4()
{
string query = "UPDATE Students SET age=45 WHERE country='USA'";
List <SqlParameter> parameters = new List <SqlParameter>();
int rowsAffected = ConnectedDBAccess.ExecuteNonQuery(query, parameters);
Console.WriteLine(rowsAffected + " rows affected");
}
// Reader query - with parameter - Get all rows from table Students where age < 40
public static void ReaderQueryWithParam_2()
{
string query = "SELECT * FROM Students WHERE age < @AGE";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("AGE", 40));
ArrayList results = ConnectedDBAccess.ExecuteQuery <Student>(query, parameters);
printResults(results);
}
public bool DeleteStudent(int id)
{
string query = "DELETE FROM Students WHERE id = @ID";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("ID", id));
int rowsAffected = ConnectedDBAccess.ExecuteNonQuery(query, parameters);
return(rowsAffected > 0);
}
public Student GetStudent(int id)
{
string query = "SELECT * FROM Students WHERE id = @ID";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("ID", id));
ArrayList results = ConnectedDBAccess.ExecuteQuery <Student>(query, parameters);
return((Student)results[0]);
}
public ArrayList GetStudentsByCountry(string country)
{
string query = "SELECT * FROM Students WHERE country = @COUNTRY";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("COUNTRY", country));
ArrayList results = ConnectedDBAccess.ExecuteQuery <Student>(query, parameters);
return(results);
}
// Reader query - Stored procedure - Get all rows from table Students
public static void BasicReaderQueryStoredPorcedure_6()
{
/*
* Defined in DB as:
* CREATE PROCEDURE getAllStudents AS
* SELECT * FROM dbo.Students
*/
string query = "getAllStudents";
List <SqlParameter> parameters = new List <SqlParameter>();
ArrayList results = ConnectedDBAccess.ExecuteQuery <Student>(query, parameters, CommandType.StoredProcedure);
printResults(results);
}
// NonQuery - Stored procedure - insert a new row to table students
public static void NonQueryStoredProcedure_7()
{
/*
* Defined in DB as:
* CREATE PROCEDURE updateAge AS
* UPDATE dbo.Students SET age = 25 WHERE first_name='Dan'
*/
string queryName = "updateAge";
List <SqlParameter> parameters = new List <SqlParameter>();
int rowsAffected = ConnectedDBAccess.ExecuteNonQuery(queryName, parameters, CommandType.StoredProcedure);
Console.WriteLine(rowsAffected + " rows affected");
}
// NonQuery - with parameters - insert a new row to table students
public static void NonQueryWithParams_5()
{
string query = "INSERT INTO Students(first_name,last_name,age,country) VALUES(@FIRST_NAME,@LAST_NAME,@AGE,@COUNTRY)";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("FIRST_NAME", "Alberto"));
parameters.Add(new SqlParameter("LAST_NAME", "Pancas"));
parameters.Add(new SqlParameter("AGE", 22));
parameters.Add(new SqlParameter("COUNTRY", "Spain"));
int rowsAffected = ConnectedDBAccess.ExecuteNonQuery(query, parameters);
Console.WriteLine(rowsAffected + " rows affected");
}
