public List <HowTo> SqlGetAllHowTos()
{
string queryString = "SELECT * FROM AllHowTos";
List <HowTo> howToList = new List <HowTo>();
using (SqlConnection connection = new SqlConnection(connectionString()))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
HowTo howTo = new HowTo();
howTo.Id = reader.GetInt32(reader.GetOrdinal("HowToId"));
howTo.Created = reader.GetDateTime(reader.GetOrdinal("Created"));
howTo.Title = reader.GetString(reader.GetOrdinal("Title"));
howTo.Description = reader.GetString(reader.GetOrdinal("Description"));
howTo.CreatedBy = GetFullName(reader.GetString(reader.GetOrdinal("CreatedBy")));
howTo.Department = GetDepartment(reader.GetInt32(reader.GetOrdinal("Department")));
howToList.Add(howTo);
}
connection.Close();
return(howToList);
}
}
public void SqlUpdateHowTo(HowTo howTo)
{
string queryString = "UPDATE AllHowTos SET Title=@title, Description=@description, CreatedBy=@createdBy, Department=@department WHERE HowToId=" + howTo.Id;
using (SqlConnection connection = new SqlConnection(connectionString()))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
command.CommandText = queryString;
SqlParameter titleParam = new SqlParameter("@title", howTo.Title);
SqlParameter descriptionParam = new SqlParameter("@description", howTo.Description);
SqlParameter createdByParam = new SqlParameter("@createdBy", howTo.CreatedBy);
SqlParameter departmentParam = new SqlParameter("@department", howTo.Department);
command.Parameters.AddRange(new SqlParameter[] { titleParam, descriptionParam, createdByParam, departmentParam });
command.ExecuteNonQuery();
connection.Close();
}
}
public void SqlAddHowTo(HowTo howTo)
{
string queryString = "INSERT INTO [dbo].[AllHowTos] ([Title], [Description], [CreatedBy], [Department]) VALUES (@title, @description, @createdBy, @department);";
using (SqlConnection connection = new SqlConnection(connectionString()))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
command.CommandText = queryString;
SqlParameter titleParam = new SqlParameter("@title", howTo.Title);
SqlParameter descriptionParam = new SqlParameter("@description", howTo.Description);
SqlParameter createdByParam = new SqlParameter("@createdBy", howTo.CreatedBy);
SqlParameter departmentParam = new SqlParameter("@department", howTo.Department);
command.Parameters.AddRange(new SqlParameter[] { titleParam, descriptionParam, createdByParam, departmentParam });
command.ExecuteNonQuery();
connection.Close();
}
}
public List <HowTo> SqlSearchAllHowTos(string search)
{
string[] words = search.Split('_');
string queryString = "SELECT * FROM dbo.AllHowTos WHERE Description LIKE '%" + words[0] + "%'";
for (int i = 1; i < words.Count(); i++)
{
queryString = queryString + " AND Description LIKE '%" + words[i] + "%'";
}
List <HowTo> howToList = new List <HowTo>();
using (SqlConnection connection = new SqlConnection(connectionString()))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
HowTo howTo = new HowTo();
howTo.Id = reader.GetInt32(reader.GetOrdinal("HowToId"));
howTo.Created = reader.GetDateTime(reader.GetOrdinal("Created"));
howTo.Title = reader.GetString(reader.GetOrdinal("Title"));
howTo.Description = reader.GetString(reader.GetOrdinal("Description"));
howTo.CreatedBy = GetFullName(reader.GetString(reader.GetOrdinal("CreatedBy")));
howTo.Department = GetDepartment(reader.GetInt32(reader.GetOrdinal("Department")));
howToList.Add(howTo);
}
connection.Close();
return(howToList);
}
}