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