public void Create(TagDto tag)
{
using (_dbCon.Open())
{
string query = "INSERT INTO [dbi434548_rockstars].[dbo].[Tags] (TagName) VALUES (@TagName)";
using (SqlCommand command = new SqlCommand(query, _dbCon.connection))
{
command.Parameters.AddWithValue("@TagName", tag.Name);
command.ExecuteNonQuery();
}
}
}
public void PlaceReservation(ReservationDTO C1)
{
using (_dbCon.Open())
{
string query = "INSERT INTO [dbo].[Reservations] (Email, VehicleID, StartDate, EndDate) VALUES (@Email, @VehicleID, @StartDate, @EndDate);";
using SqlCommand command = new SqlCommand(query, _dbCon.Connection);
command.Parameters.AddWithValue("@Email", C1.Email);
command.Parameters.AddWithValue("@VehicleID", C1.VehicleID);
command.Parameters.AddWithValue("@StartDate", C1.StartDate);
command.Parameters.AddWithValue("@EndDate", C1.EndDate);
command.ExecuteNonQuery();
}
}
public List <UserDto> GetAll()
{
var users = new List <UserDto>();
using (_dbCon.Open())
{
string query = "SELECT * FROM [Dbo].[User]";
using SqlCommand command = new SqlCommand(query, _dbCon.connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
UserDto dto = new UserDto()
{
UserId = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
Email = reader.GetString(3),
Password = reader.GetString(4),
Role = reader.GetString(5)
};
users.Add(dto);
}
_dbCon.Close();
}
return(users);
}
/* CREATE ------------------------------------------- CREATE ------------------------------------------------------ CREATE --------------------------------------------------- CREATE ------------------------------------ CREATE*/
public void Create(UserDTO C1)
{
using (_dbCon.Open())
{
string query = "INSERT INTO [dbo].[User] (Firstname, Lastname, Postalcode, Adres, Housenumber, Email, Role, Password) VALUES (@Firstname, @Lastname, @Postalcode, @Adres, @Housenumber, @Email, @Role, @Password);";
using SqlCommand command = new SqlCommand(query, _dbCon.Connection);
command.Parameters.AddWithValue("@Firstname", C1.Firstname);
command.Parameters.AddWithValue("@Lastname", C1.Lastname);
command.Parameters.AddWithValue("@Postalcode", C1.Postalcode);
command.Parameters.AddWithValue("@Adres", C1.Adres);
command.Parameters.AddWithValue("@Housenumber", C1.Housenumber);
command.Parameters.AddWithValue("@Email", C1.Email);
command.Parameters.AddWithValue("@Role", C1.Role);
command.Parameters.AddWithValue("@Password", C1.Password);
command.ExecuteNonQuery();
}
}
public List <ArticleDto> GetAll()
{
var articles = new List <ArticleDto>();
using (_dbCon.Open())
{
string query =
"SELECT Articles.ArticleID, Articles.AccountID, Articles.Title, Articles.ArticleText, Articles.DateCreated, Articles.Draft, Articles.LastEdited, Articles.Images, a1.TagID, t.TagName " +
"FROM Articles " +
"INNER JOIN ArticleTag a1 on Articles.ArticleID = a1.ArticleID " +
"INNER JOIN Tags t on a1.TagID = t.TagID;";
using (SqlCommand command = new SqlCommand(query, _dbCon.connection))
{
var reader = command.ExecuteReader();
while (reader.Read())
{
ArticleDto ArticleDTO = new ArticleDto
{
ArticleID = reader.GetInt32(0),
AccountID = reader.GetInt32(1),
Title = reader.GetString(2),
ArticleText = reader.GetString(3),
DateCreated = reader.GetDateTime(4),
Draft = reader.GetBoolean(5),
LastEdited = reader.GetDateTime(6),
Images = reader.GetString(7),
TagID = reader.GetInt32(8),
TagName = reader.GetString(9)
};
articles.Add(ArticleDTO);
}
_dbCon.Close();
}
}
return(articles);
}
/* CREATE ------------------------------------------- CREATE ------------------------------------------------------ CREATE --------------------------------------------------- CREATE ------------------------------------ CREATE*/
public void Create(VehicleDTO C1)
{
using (_dbCon.Open())
{
string query = "INSERT INTO Vehicle (Brandname, Modelname, Transmission, Enginepower, Weight, Acceleration, Cargospace, Seat, Rentalprice, Fueltype, ImageLink, CategoryID) VALUES (@Brandname, @Modelname, @Transmission, @Enginepower, @Weight, @Acceleration, @Cargospace, @Seat, @Rentalprice, @Fueltype, @ImageLink, @CategoryID);";
using SqlCommand command = new SqlCommand(query, _dbCon.Connection);
command.Parameters.AddWithValue("@Brandname", C1.Brandname);
command.Parameters.AddWithValue("@Modelname", C1.Modelname);
command.Parameters.AddWithValue("@Transmission", C1.Transmission);
command.Parameters.AddWithValue("@Enginepower", C1.Enginepower);
command.Parameters.AddWithValue("@Weight", C1.Weight);
command.Parameters.AddWithValue("@Acceleration", C1.Acceleration);
command.Parameters.AddWithValue("@Cargospace", C1.Cargospace);
command.Parameters.AddWithValue("@Seat", C1.Seat);
command.Parameters.AddWithValue("@Rentalprice", C1.RentalPrice);
command.Parameters.AddWithValue("@Fueltype", C1.Fueltype);
command.Parameters.AddWithValue("@ImageLink", C1.ImageLink);
command.Parameters.AddWithValue("@CategoryID", C1.CategoryID);
command.ExecuteNonQuery();
}
}
public void Create(ContactDTO C1)
{
using (_dbCon.Open())
{
string query = "Insert into [dbo].[Question] (Firstname, Lastname, Email, Question) VALUES (@Firstname, @Lastname, @Email, @Question);";
using SqlCommand command = new SqlCommand(query, _dbCon.connection);
command.Parameters.AddWithValue("@Firstname", C1.Firstname);
command.Parameters.AddWithValue("@Lastname", C1.Lastname);
command.Parameters.AddWithValue("@Email", C1.Email);
command.Parameters.AddWithValue("@Question", C1.Question);
command.ExecuteNonQuery();
}
}
public List <RoleDto> GetAll()
{
List <RoleDto> dtos = new List <RoleDto>();
using (_dbCon.Open())
{
string query = "SELECT * FROM Role";
using (SqlCommand getAll = new SqlCommand(query, _dbCon.connection))
{
var reader = getAll.ExecuteReader();
while (reader.Read())
{
RoleDto dto = new RoleDto()
{
Id = reader.GetInt32(0),
Name = reader.GetString(1)
};
dtos.Add(dto);
}
_dbCon.Close();
return(dtos);
}
}
}