public void ExecuteVoid(string query) { var connection = _sqlServer.Connect(); try { using (var command = new SqlCommand(query, connection)) command.ExecuteNonQuery(); } catch { throw new SqlException(_path, "ExecuteVoid()"); } finally { _sqlServer.CloseConnection(); } }
public User Get(string username) { var connection = _sqlServer.Connect(); var query = $"SELECT id, username, password FROM user_table WHERE username = '******';"; User user = null; try { using (var command = new SqlCommand(query, connection)) using (var reader = command.ExecuteReader()) { if (!reader.HasRows) { return(null); } while (reader.Read()) { user = new User { Username = reader.GetDefaultString("username"), Password = reader.GetDefaultString("password") } } ; return(user); } } catch { throw new NonExistingUserException(_path, "Get()"); } finally { _sqlServer.CloseConnection(); } }
public User GetUserWithArtworks(int?id) { var connection = _sqlServer.Connect(); var query = $"SELECT " + $"u.username, " + $"u.id, " + $"u.profile_image_url, " + $"a.id AS artworkId, " + $"a.piece_name, " + $"a.customer_name, " + $"a.image_url, " + $"a.customer_contact, " + $"a.shipping_cost, " + $"a.material_cost, " + $"a.sale_price, " + $"a.height, " + $"a.width, " + $"a.time_spent_minutes, " + $"a.shape, " + $"a.payment_type, " + $"a.is_commission, " + $"a.is_payment_collected, " + $"a.date_started, " + $"a.date_finished " + $"FROM user_table u " + $"INNER JOIN artwork_table a " + $"ON u.id = a.user_id " + $"WHERE u.id = {id};"; User user = null; try { using (var command = new SqlCommand(query, connection)) using (var reader = command.ExecuteReader()) if (reader.HasRows) { while (reader.Read()) { if (user == null) { user = new User { Id = reader.GetDefaultInt("id"), Username = reader.GetDefaultString("username"), ProfileImgUrl = reader.GetDefaultString("profile_image_url") } } ; var artwork = new ArtWork { Id = reader.GetDefaultInt("artworkId"), PieceName = reader.GetDefaultString("piece_name"), CustomerName = reader.GetDefaultString("customer_name"), CustomerContact = reader.GetDefaultString("customer_contact"), ImgUrl = reader.GetDefaultString("image_url"), Shape = reader.GetDefaultString("shape"), PaymentType = reader.GetDefaultString("payment_type"), ShippingCost = reader.GetNullableDecimal("shipping_cost"), MaterialCost = reader.GetNullableDecimal("material_cost"), SalePrice = reader.GetNullableDecimal("sale_price"), HeightInches = reader.GetNullableInt("height"), WidthInches = reader.GetNullableInt("width"), TimeSpentMinutes = reader.GetNullableInt("time_spent_minutes"), DateStarted = reader.GetNullableDateTime("date_started"), DateFinished = reader.GetNullableDateTime("date_finished"), IsCommission = reader.GetNullableBool("is_commission"), IsPaymentCollected = reader.GetNullableBool("is_payment_collected"), }; user.ArtWorks.Add(artwork); } } if (user != null) { return(user); } _sqlServer.CloseConnection(); return(GetDataWithoutArtworks(id)); } catch { return(null); } finally { _sqlServer.CloseConnection(); } }