// Solo Music Store
SqlConnection scMusicStore;
Directory.CreateDirectory(@"C:\Solo Music");
using (scMusicStore = new SqlConnection("Data Source=(local);Integrated Security=True;"))
{
SqlCommand cmdDatabase = new SqlCommand("IF EXISTS ( " +
" SELECT name " +
" FROM sys.databases " +
" WHERE name = N'SoloMusicStore1' " +
")" +
"DROP DATABASE SoloMusicStore1; " +
"CREATE DATABASE SoloMusicStore1;",
scMusicStore);
scMusicStore.Open();
cmdDatabase.ExecuteNonQuery();
MessageBox.Show("A database named SoloMusic has been created.",
"College Park Auto Repair",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
using (scMusicStore = new SqlConnection("Data Source=(local);Database='SoloMusicStore1';Integrated Security=True;"))
{
SqlCommand cmdDatabase = new SqlCommand("CREATE SCHEMA Inventory;", scMusicStore);
scMusicStore.Open();
cmdDatabase.ExecuteNonQuery();
}
using (scMusicStore = new SqlConnection("Data Source=(local);Database='SoloMusicStore1';Integrated Security=True;"))
{
SqlCommand cmdDatabase = new SqlCommand("CREATE SCHEMA Sales;", scMusicStore);
scMusicStore.Open();
cmdDatabase.ExecuteNonQuery();
}
using (scMusicStore = new SqlConnection("Data Source=(local);Database='SoloMusicStore1';Integrated Security=True;"))
{
SqlCommand cmdDatabase = new SqlCommand("CREATE TABLE Inventory.StoreItems " +
"( " +
" StoreItemID int identity(1, 1), " +
" ItemNumber nvarchar(10) unique, " +
" Category nvarchar(25), " +
" SubCategory nvarchar(25), " +
" ItemName nvarchar(100) null, " +
" UnitPrice money, " +
" Constraint PK_StoreItems Primary Key(ItemNumber)" +
");", scMusicStore);
scMusicStore.Open();
cmdDatabase.ExecuteNonQuery();
}
using (scMusicStore = new SqlConnection("Data Source=(local);Database='SoloMusicStore1';Integrated Security=True;"))
{
SqlCommand cmdDatabase = new SqlCommand("CREATE TABLE Sales.CustomersOrders " +
"( " +
" ReceiptNumber int identity(100001, 1), " +
" ItemNumber nvarchar(10), " +
" ItemName nvarchar(100), " +
" UnitPrice money, " +
" TotalSale money, " +
" Constraint PK_CustomersOrders Primary Key(ReceiptNumber)" +
");", scMusicStore);
scMusicStore.Open();
cmdDatabase.ExecuteNonQuery();
}
using (scMusicStore = new SqlConnection("Data Source=(local);Database='SoloMusicStore1';Integrated Security=True;"))
{
SqlCommand cmdDatabase = new SqlCommand("CREATE TABLE Inventory.SoldItems " +
"( " +
" SoldItemID int identity(1, 1) not null, " +
" ReceiptNumber int not null, " +
" ItemNumber nvarchar(10), " +
" ItemName nvarchar(100), " +
" UnitPrice money, " +
" TotalSale money, " +
" CONSTRAINT PK_SoldItems Primary Key(SoldItemID)" +
");", scMusicStore);
scMusicStore.Open();
cmdDatabase.ExecuteNonQuery();
}
MessageBox.Show("The tables have been created.",
"Solo Music",
MessageBoxButtons.OK, MessageBoxIcon.Information);
using (scMusicStore = new SqlConnection("Data Source=(local);Database='SoloMusicStore1';Integrated Security=True;"))
{
SqlCommand cmdDatabase = new SqlCommand("INSERT INTO Inventory.StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice) " +
"VALUES(N'930485', N'Keyboards', N'Synthesizers', N'Roland JUNO-Gi Synthesizer ', 780), " +
" (N'485948', N'Drums', N'Acoustic Drum Sets', N'Sound Percussion Complete 5-Piece Drum Set with Cymbals & Hardware', 350), " +
" (N'920820', N'Miscellaneous', N'DJ Accessories', N'Pioneer RMX-1000 Remix Station Black', 650), " +
" (N'406033', N'Guitars', N'Electric Guitars', N'B.C. Rich Pro X Custom Special X3 Mockingbird Electric Guitar Tobacco Burst', 395.95), " +
" (N'358460', N'Bass', N'Electric Basses', N'Fender Deluxe P Bass Special 4-String Bass ', 695.95), " +
" (N'724799', N'Accessories', N'Cables', N'Monster Cable S-100 XLR Microphone Cable ', 14.00), " +
" (N'582693', N'Keyboards', N'Organs', N'Roland VK-88 Combo Organ', 5695.50), " +
" (N'350250', N'Guitars', N'Acoustic Guitars', N'FA-100 Houstic', 190.00), " +
" (N'332085', N'Miscellaneous', N'Multitrack Recorders', N'Zoom R8 8-Track SD Recorder, Sampler & USB Interface', 295.75), " +
" (N'836360', N'Brass Instruments', N'Trumpets', N'Brasswind Model II Student Bb Trumpet', 180), " +
" (N'415158', N'Drums', N'Electronic Drum Sets', N'Simmons SD5X Electronic Drum Set', 425.75), " +
" (N'886182', N'Keyboards', N'Synthesizers', N'Roland Jupiter-80 Synthesizer', 3525.50), " +
" (N'516080', N'Guitars', N'Acoustic-Electric Guitars', N'Taylor 214ce Rosewood/Spruce Grand Auditorium Acoustic-Electric Guitar', 1000), " +
" (N'536949', N'Live Sound', N'Packages', N'Phonic Powerpod 620 Plus / S710 PA Package', 295.95), " +
" (N'414913', N'Woodwinds ', N'Saxophones', N'Allora Vienna Series Intermediate Alto Saxophone', 795.95), " +
" (N'161553', N'Miscellaneous', N'Multitrack Recorders', N'TASCAM DP-32 Digital 32-Track Portastudio', 795.95), " +
" (N'355862', N'Guitars', N'Electric Guitars', N'PRS SE Custom 24 Electric Guitar', 595.85), " +
" (N'293488', N'Bass', N'Electric Basses', N'Ibanez SR1206E 6-String Electric Bass Vintage', 1250), " +
" (N'330088', N'Keyboards', N'Synthesizers', N'Korg MicroKORG Synthesizer/Vocoder', 415.55), " +
" (N'115599', N'Accessories', N'Cables', N'Right-On Cable', 98.95), " +
" (N'402882', N'Accessories', N'Cables', N'Monster Cable Digilink 5 Pin MIDI Cable', 9.95), " +
" (N'937528', N'Guitars', N'Electric Guitars', N'ESP LTD EC-256FM Electric Guitar', 395.95), " +
" (N'355582', N'Miscellaneous', N'Speakers', N'Peavey PR 12 Speaker Pair', 360), " +
" (N'140864', N'Brass Instruments', N'Trombones', N'Allora Student Series Bb Trombone Model AATB-102', 215.50), " +
" (N'173031', N'Drums', N'Hi-Hat Cymbals', N'Zildjian ZXT Solid Hi-Hat Cymbal (Pair) 14 Inches', 145.75), " +
" (N'217790', N'Live Sound', N'Microphones', N'MXL 3000 Mic Bundle', 245.85), " +
" (N'676184', N'Keyboards', N'Pianos', N'Williams Overture 88 Key Digital Piano', 595.95), " +
" (N'406266', N'Live Sound', N'Microphones', N'MXL V63M Condenser Studio Microphone', 99.95), " +
" (N'357020', N'Drums', N'Acoustic Drum Sets', N'Ludwig Breakbeats by Questlove 4-Piece Shell Pack Azure Sparkle', 395.95), " +
" (N'486021', N'Keyboards', N'Synthesizers', N'Roland BK-9 Backing Keyboard', 2495.85), " +
" (N'686659', N'Bass', N'Electric Basses', N'Fender American Deluxe Jazz Bass V 5-String Electric Bass', 1795.95), " +
" (N'583746', N'Guitars', N'Acoustic Guitars', N'Yamaha FG700S Folk Acoustic Guitar', 225.50), " +
" (N'388835', N'Drums', N'Acoustic Drum Sets', N'Gretsch Drums Energy 5-Piece Drum Set with Hardware and Sabian SBR Cymbals', 695.95), " +
" (N'258395', N'Accessories', N'Cables', N'Monster Cable Performer 500 Speaker Cable', 21.5), " +
" (N'769138', N'Live Sound', N'Amplifiers', N'Acoustic Lead Guitar Series G120 DSP 120W Guitar Combo Amp', 199.95), " +
" (N'275157', N'Keyboards', N'Synthesizers', N'Yamaha S90XS 88-Key Balanced Weighted Hammer Action Synthesizer', 2450), " +
" (N'843814', N'Guitars', N'Acoustic Guitars', N'Fender CD100 12-String Acoustic Guitar Natural', 255.5), " +
" (N'281141', N'Orchestra', N'Violins', N'Florea Recital II Violin Outfit', 150), " +
" (N'966060', N'Drums', N'Electronic Drum Sets', N'Simmons SDXpress2 Compact 5-Piece Electronic Drum Kit', 225.5), " +
" (N'559606', N'Live Sound', N'Packages', N'Gear One PA2400 / Kustom KPC15 Mains and Monitors Package', 696.95), " +
" (N'725504', N'Miscellaneous', N'Mixers', N'Nady PMX-1600 16 Channel/4 Bus Powered Mixer w/DSP Effects', 649.95), " +
" (N'972405', N'Guitars', N'Electric Guitars', N'ESP LTD EC-256FM Electric Guitar', 395.95), " +
" (N'434426', N'Keyboards', N'Pianos', N'Suzuki S-350 Mini Grand Digital Piano', 2500), " +
" (N'259592', N'Accessories', N'Cables', N'Mogami Gold Instrument Cable Angled - Straight Cable', 42.25), " +
" (N'382730', N'Orchestra', N'Violins', N'Mendini 4/4 MV300 Solid Wood Violin in Satin Finish', 59.95), " +
" (N'849926', N'Bass', N'Electric Basses', N'Squier by Fender Vintage Modified Jazz Bass ''77, Amber', 325.5);",
scMusicStore);
scMusicStore.Open();
cmdDatabase.ExecuteNonQuery();
}
MessageBox.Show("Records have been added to the tables.",
"Solo Music",
MessageBoxButtons.OK, MessageBoxIcon.Information);