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 GetMinionNames(string connectionString)
{
Console.Write("Give me a villan Id : ");
string input = Console.ReadLine();
int id = int.Parse(input);
bool villainExists = true;
string query = "use MinionsDb\n";
query += "select Name from Villains where Id =" + id;
QueryHooks.ExecSelectQuery(connectionString, query, (SqlDataReader reader) =>
{
if (reader.HasRows == false)
{
Console.WriteLine("No villain with ID {0} exists in the database.", id);
villainExists = false;
return;
}
while (reader.Read())
{
Console.WriteLine("Villain: " + reader.GetString(0));
}
});
if (!villainExists)
{
return;
}
query = "use MinionsDb\n";
query += @"select
M.Name, M.Age
from
Villains as V
join MinionsVillains as MV on MV.VillainId = V.Id
join Minions as M on M.Id = MV.MinionId
where
V.Id = " + id;
QueryHooks.ExecSelectQuery(connectionString, query, (SqlDataReader reader) =>
{
if (reader.HasRows == false)
{
Console.WriteLine("(no minions)");
return;
}
int i = 1;
while (reader.Read())
{
Console.WriteLine("{0}. {1} {2}", i++, reader.GetString(0), reader.GetInt32(1));
}
});
}
public static bool IsEmptyReader(string connectionString, string query)
{
bool ret = false;
QueryHooks.ExecSelectQuery(connectionString, query, (reader) =>
{
if (reader.HasRows == false)
{
ret = true;
}
});
return(ret);
}
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 PrintAllMinionNames(string connectionString)
{
string query = "use MinionsDb\n";
query += @"select * from Minions";
List <string> minions = new List <string>();
QueryHooks.ExecSelectQuery(connectionString, query, (reader) =>
{
while (reader.Read())
{
minions.Add(reader.GetString(2));
}
});
Console.WriteLine("Before :");
Console.WriteLine(String.Join <string>(", ", minions));
string[] result = new string[minions.Count];
int i = 0;
int first = 0;
int last = minions.Count - 1;
while (i < minions.Count)
{
if (i % 2 == 0)
{
result[first] = minions[i];
first++;
}
else
{
result[last] = minions[i];
last--;
}
i++;
}
Console.WriteLine("After :");
Console.WriteLine(String.Join(", ", result));
}
public static void StoredProc(string connectionString)
{
Console.Write("Give me id : ");
int minionId = int.Parse(Console.ReadLine());
SqlConnection connection = new SqlConnection(
@"Server=.\SQLEXPRESS;
database=MinionsDb;
Integrated Security=true");
SqlCommand command = new SqlCommand();
int affected = 0;
using (connection)
using (command)
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@MinionId", SqlDbType.Int));
command.Parameters[0].Value = minionId;
command.Connection = connection;
command.CommandText = "usp_GetOlder";
affected = command.ExecuteNonQuery();
}
string query = "use MinionsDb\n";
query += "select * from Minions";
QueryHooks.ExecSelectQuery(connectionString, query, (reader) =>
{
while (reader.Read())
{
int age = reader.GetInt32(1);
string name = reader.GetString(2);
Console.WriteLine("{0} {1}", age, name);
}
});
}
public static void GetVillains(string connectionString)
{
string query = "use MinionsDb\n";
query += @"select
V.Name,
count(M.Id) as MinionCount
from
Villains as V
join MinionsVillains as MV on MV.VillainId = V.Id
join Minions as M on M.Id = MV.MinionId
group by
V.Name
order by
MinionCount desc" ;
QueryHooks.ExecSelectQuery(connectionString, query, (SqlDataReader reader) =>
{
while (reader.Read())
{
Console.WriteLine(reader.GetString(0) + " " + reader.GetInt32(1));
}
});
}
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));
}
public static void AddMinion(string connectionString)
{
Console.Write("Give me input :");
string input = Console.ReadLine();
string[] spliInput = input.Split(' ');
string minionName = spliInput[1];
int minionAge = int.Parse(spliInput[2]);
string minionTown = spliInput[3];
input = Console.ReadLine();
spliInput = input.Split(' ');
string villainName = spliInput[1];
string query = "use MinionsDb\n";
query += @"select * from Towns where TownName = '" + minionTown + "'";
QueryHooks.ExecSelectQuery(connectionString, query, (reader) =>
{
if (reader.HasRows == false)
{
Console.WriteLine("Town {0} was added to the database.", minionTown);
InsertionQueries.InsertIntoTowns(connectionString, minionTown, null);
return;
}
});
int minionTownId = 0;
QueryHooks.ExecSelectQuery(connectionString, query, (reader) =>
{
while (reader.Read())
{
minionTownId = reader.GetInt32(0);
}
});
query = "use MinionsDb\n";
query += @"select * from Villains where Name = '" + villainName + "'";
QueryHooks.ExecSelectQuery(connectionString, query, (reader) =>
{
if (reader.HasRows == false)
{
Console.WriteLine("Villain {0} was added to the database.", villainName);
InsertionQueries.InsertIntoVillains(connectionString, villainName, "evil");
return;
}
});
int villainId = 0;
QueryHooks.ExecSelectQuery(connectionString, query, (reader) =>
{
while (reader.Read())
{
villainId = reader.GetInt32(0);
}
});
int minionCount = (int)QueryHooks.ExecScalarQuery(connectionString, "use MinionsDb; select count(*) from Minions");
InsertionQueries.InsertIntoMinions(connectionString, minionAge, minionName, minionTownId);
InsertionQueries.InsertIntoMinionsVillains(connectionString, minionCount + 1, villainId);
Console.WriteLine("Successfully added {0} to be minion of {1}.", minionName, villainName);
}
