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