public static void DropDatabase(string connectionString, string databaseName)
{
var dropQuery = "alter database " + databaseName + " set single_user with rollback immediate\n";
dropQuery += "drop database " + databaseName;
QueryHooks.ExecTSQLQuery(connectionString, dropQuery);
}
public static void TownsUpper(string connectionString)
{
Console.Write("Give me country name : ");
string countryName = Console.ReadLine();
string query = "use MinionsDb;\n";
query += "select * from Towns where CountryName = '" + countryName + "'";
if (QueryHooks.IsEmptyReader(connectionString, query))
{
Console.WriteLine("No town names were affected.");
return;
}
List <string> towns = new List <string>();
QueryHooks.ExecSelectQuery(connectionString, query, (reader) =>
{
while (reader.Read())
{
towns.Add(reader.GetString(1).ToUpper());
}
});
query = "use MinionsDb;\n";
query += @"update Towns
set TownName = UPPER(TownName)
where CountryName = '" + countryName + "'";
int affectedRows = QueryHooks.ExecTSQLQuery(connectionString, query);
Console.WriteLine("{0} town names were affected.", affectedRows);
Console.WriteLine('[' + string.Join <string>(",", towns) + ']');
}
public static void IncreaseMinionAge(string connectionString)
{
Console.WriteLine("Give me Ids : ");
int[] ids = Console.ReadLine().Split(' ').Select(x => int.Parse(x)).ToArray();
string query = "use MinionsDb\n";
query += @"select * from Minions";
QueryHooks.ExecSelectQuery(connectionString, query, (reader) =>
{
while (reader.Read())
{
int currId = reader.GetInt32(0);
int age = reader.GetInt32(1);
string[] names = reader
.GetString(2)
.Split(' ')
.Select((x) =>
{
var arr = x.ToCharArray();
arr[0] = Char.ToUpper(arr[0]);
return(String.Join("", arr));
})
.ToArray();
string nameUpper = String.Join(" ", names).Trim();
if (ids.Contains(currId))
{
string updateQuery = "use MinionsDb\n";
updateQuery += string.Format("update Minions set Name = '{0}', Age = {1} where Id = {2}",
nameUpper, age + 1, currId);
QueryHooks.ExecTSQLQuery(connectionString, updateQuery);
}
}
});
QueryHooks.ExecSelectQuery(connectionString, query, (reader) =>
{
while (reader.Read())
{
int age = reader.GetInt32(1);
string name = reader.GetString(2);
Console.WriteLine("{0} {1}", name, age);
}
});
}
public static void DropMinionTables(string connectionString)
{
string dropTablesQuery = File.ReadAllText(Constants.DropTablesSql);
QueryHooks.ExecTSQLQuery(connectionString, dropTablesQuery);
}
public static void CreateMinionTables(string connectionString)
{
string createTablesQuery = File.ReadAllText(Constants.CreateTablesSql);
QueryHooks.ExecTSQLQuery(connectionString, createTablesQuery);
}
public static void CreateDatabase(string connectionString, string databaseName)
{
QueryHooks.ExecTSQLQuery(connectionString, string.Format("create database {0}", databaseName));
}