public static CatalogPieceSize GetPieceSizeByNo(int pPieceNo, int pSizeNo)
{
CatalogPieceSize size = new CatalogPieceSize();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\PineSpringsPottery.accdb";
OleDbConnection connection = new OleDbConnection(connectionString);
//Open connection if not already
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
string selectStatement = "SELECT PIECE_SIZE.SizeNo, PIECE_SIZE.Dimensions, PIECE_SIZE.BasePrice, PIECE_SIZE.SizeDescription, PIECE_SIZE.TotalPounds " +
"FROM PIECE_SIZE WHERE PIECE_SIZE.PieceNo = " + pPieceNo + " AND PIECE_SIZE.SizeNo = " + pSizeNo;
//Create Command for select statement
OleDbCommand selectCmd = new OleDbCommand(selectStatement, connection);
//execute reader and close reader
OleDbDataReader reader = selectCmd.ExecuteReader();
while (reader.Read())
{
size.sizeNo = Convert.ToInt32(reader["SizeNo"]);
size.totalPounds = Convert.ToDecimal(reader["TotalPounds"]);
size.sizeDescription = Convert.ToString(reader["SizeDescription"]);
size.dimensions = Convert.ToString(reader["Dimensions"]);
size.basePrice = Convert.ToDecimal(reader["BasePrice"]);
}
connection.Close();
return(size);
}
//Add a new row to size options
private void AddRow(int rowCount, CatalogPieceSize pSize)
{
//new row and formatting for table
RowStyle style = new RowStyle();
style.SizeType = SizeType.AutoSize;
style.Height = 30;
tableAddSizes.RowStyles.Add(style);
tableAddSizes.RowCount++;
//Pounds NUD
NumericUpDown tempnud = new NumericUpDown();
tempnud.Width = 75;
tempnud.DecimalPlaces = 2;
tempnud.Maximum = 100;
tempnud.Focus();
tempnud.Value = pSize.totalPounds;
nudPounds.Insert(rowCount - 1, tempnud);
tableAddSizes.Controls.Add(nudPounds[rowCount - 1], 0, rowCount);
//Dimensions Text
TextBox temp = new TextBox();
temp.Width = 155;
temp.MaxLength = 255;
temp.Text = pSize.dimensions;
txtDimensions.Insert(rowCount - 1, temp);
tableAddSizes.Controls.Add(txtDimensions[rowCount - 1], 1, rowCount);
//Descriptions Text
temp = new TextBox();
temp.Width = 150;
temp.MaxLength = 255;
temp.Text = pSize.sizeDescription;
txtDescriptions.Insert(rowCount - 1, temp);
tableAddSizes.Controls.Add(txtDescriptions[rowCount - 1], 2, rowCount);
//Prices NUD
tempnud = new NumericUpDown();
tempnud.Width = 85;
tempnud.DecimalPlaces = 2;
tempnud.Maximum = 100000;
tempnud.Value = pSize.basePrice;
nudPrices.Insert(rowCount - 1, tempnud);
tableAddSizes.Controls.Add(nudPrices[rowCount - 1], 3, rowCount);
//Delete Button
Label delete = new Label();
delete.ForeColor = Color.DarkRed;
delete.Text = "X";
delete.Click += new EventHandler(this.DeleteRow_Click);
if (rowCount == 1)
{
delete.Visible = false;
}
lblDeletes.Insert(rowCount - 1, delete);
//add row to table
tableAddSizes.Controls.Add(lblDeletes[rowCount - 1], 4, rowCount);
}
public static List <CatalogPiece> GetPieces()
{
List <CatalogPiece> pieces = new List <CatalogPiece>();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\PineSpringsPottery.accdb";
OleDbConnection connection = new OleDbConnection(connectionString);
//Open connection if not already
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
string selectStatement = "SELECT PIECE_CATALOG.PieceNo, PIECE_CATALOG.PieceName, PIECE_CATALOG.Description, PIECE_CATALOG.Picture.FileName, PIECE_CATALOG.Picture.FileData " +
"FROM PIECE_CATALOG ORDER BY PieceName;";
//Create Command for select statement
OleDbCommand selectCmd = new OleDbCommand(selectStatement, connection);
//execute reader and close reader
OleDbDataReader reader = selectCmd.ExecuteReader();
while (reader.Read())
{
CatalogPiece temp = new CatalogPiece();
temp.pieceNo = Convert.ToInt32(reader["PieceNo"]);
temp.pieceName = Convert.ToString(reader["PieceName"]);
temp.description = Convert.ToString(reader["Description"]);
string filename = Convert.ToString(reader["PIECE_CATALOG.Picture.FileName"]);
if (filename != "")
{
byte[] image = GetImageBytesFromOLEField((byte[])reader["PIECE_CATALOG.Picture.FileData"]);
try
{
MemoryStream memStream = new MemoryStream(image);
temp.picture = new Bitmap(memStream);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
temp.picture = new Bitmap((System.Drawing.Image)(Resources.noimage));
}
temp.sizeOptions = new List <CatalogPieceSize>();
string subSelectStatement = "SELECT PIECE_SIZE.SizeNo, PIECE_SIZE.Dimensions, PIECE_SIZE.BasePrice, PIECE_SIZE.SizeDescription, PIECE_SIZE.TotalPounds " +
"FROM PIECE_SIZE WHERE PIECE_SIZE.PieceNo = " + temp.pieceNo + " ORDER BY SizeNo;";
OleDbCommand subSelectCmd = new OleDbCommand(subSelectStatement, connection);
//execute reader and close reader
OleDbDataReader subReader = subSelectCmd.ExecuteReader();
while (subReader.Read())
{
CatalogPieceSize subTemp = new CatalogPieceSize();
subTemp.sizeNo = Convert.ToInt32(subReader["SizeNo"]);
subTemp.totalPounds = Convert.ToDecimal(subReader["TotalPounds"]);
subTemp.sizeDescription = Convert.ToString(subReader["SizeDescription"]);
subTemp.dimensions = Convert.ToString(subReader["Dimensions"]);
subTemp.basePrice = Convert.ToDecimal(subReader["BasePrice"]);
temp.sizeOptions.Add(subTemp);
}
pieces.Add(temp);
}
connection.Close();
return(pieces);
}