CODEWITHSUNDEEP
c#sql-serverwpf
TheGoogles
TheGoogles

Reputation: 15

How to create a Database from SQL script inside Visual Studio and add it to the project?

I have a SQL file which creates a database which I need for my WPF project. However, the only way I know how to build a database is from scratch, but my code actually has the SQL commands which drop the database and then recreate it with the tables and information that goes inside those tables. How would I run this script? I tried to follow some of the examples which were already posted on this website but they are either too old, or don't exactly apply to my situation.

I have installed NuGet packages in my project:

I tried to run the SQL script without the commands to drop the database and create database, but I get an error

object "table Product" already exists

USE master; 
GO 
 
--Delete the OMS (Order Management System) database if it exists. 
IF EXISTS(SELECT * from sys.databases WHERE name='OMS') 
BEGIN 
    DROP DATABASE OMS; 
END 

CREATE DATABASE OMS;
GO

USE OMS;
GO

CREATE TABLE Product 
( 
     idProduct SMALLINT PRIMARY KEY NOT NULL,
     ProductName varchar(25),
     Description varchar(100),
     Price DECIMAL(6,2)
);
           
INSERT INTO Product (idProduct, ProductName, Description, Price) 
VALUES (1, 'CapressoBar Model #351', 'A fully programmable pump espresso machine and 10-cup coffee maker complete with GoldTone filter', 99.99);

INSERT INTO Product (idProduct, ProductName, Description, Price) 
VALUES (2,'Capresso Ultima', 'Coffee and Espresso and Cappuccino Machine. Brews from one espresso to two six ounce cups of coffee', 129.99);

INSERT INTO Product (idProduct, ProductName, Description, Price) 
VALUES (3,'Eileen 4-cup French Press', 'A unique coffeemaker from those proud craftsmen in windy Normandy.', 32.50);

INSERT INTO Product (idProduct, ProductName, Description,Price) 
VALUES (4,'Coffee Grinder', 'Avoid blade grinders! This mill grinder allows you to choose a fine grind to a coarse grind.', 28.50);

INSERT INTO Product (idProduct, ProductName, Description,Price) 
VALUES (5,'Sumatra', 'Spicy and intense with herbal aroma. ', 10.50);

INSERT INTO Product (idProduct, ProductName, Description,Price) 
VALUES (6,'Guatamala', 'heavy body, spicy twist, aromatic and smokey flavor.', 10.00);

INSERT INTO Product (idProduct, ProductName, Description,Price) 
VALUES (7,'Columbia', 'dry, nutty flavor and smoothness',10.80);

INSERT INTO Product (idProduct, ProductName, Description,Price) 
VALUES (8,'Brazil', 'well-balanced mellow flavor, a medium body with hints of cocoa and a mild, nut-like aftertaste', 10.80);

INSERT INTO Product (idProduct, ProductName, Description,Price) 
VALUES (9,'Ethiopia', 'distinctive berry-like flavor and aroma, reminds many of a fruity, memorable wine. ', 10.00);

INSERT INTO Product (idProduct, ProductName, Description,Price) 
VALUES (10,'Espresso', 'dense, caramel-like sweetness with a soft acidity. Roasted somewhat darker than traditional Italian.',  10.00);

  
CREATE TABLE Shopper 
(
    idShopper INT PRIMARY KEY NOT NULL,
    Email varchar(25) not null,
    FirstName varchar(15),
    LastName varchar(20),
    Address varchar(40),
    City varchar(20),
    StateProvince varchar(20),
    Country varchar(20),
    ZipCode varchar(15)
);

INSERT INTO Shopper (idshopper, FirstName, LastName, Address, City, StateProvince, Country, ZipCode, Email)
VALUES (21, 'John', 'Carter', '21 Front St.', 'Raleigh','NC','USA','54822', '[email protected]');
INSERT INTO Shopper (idshopper, FirstName, LastName, Address, City, StateProvince, Country, ZipCode, Email)
VALUES (22, 'Margaret', 'Somner', '287 Walnut Drive', 'Cheasapeake', 'VA','USA','23321', '[email protected]');
INSERT INTO Shopper (idshopper, FirstName, LastName, Address, City, StateProvince, Country, ZipCode, Email)
VALUES (23, 'Kenny', 'Ratman', '1 Fun Lane', 'South Park', 'NC','USA','54674',  '[email protected]');
INSERT INTO Shopper (idshopper, FirstName, LastName, Address, City, StateProvince, Country, ZipCode, Email)
VALUES (24, 'Camryn', 'Sonnie', '40162 Talamore', 'South Riding','VA','USA','20152','[email protected]');
INSERT INTO Shopper (idshopper, FirstName, LastName, Address, City, StateProvince, Country, ZipCode, Email)
VALUES (25, 'Scott', 'Savid', '11 Pine Grove', 'Hickory', 'VA','USA','22954',  '[email protected]');
INSERT INTO Shopper (idshopper, FirstName, LastName, Address, City, StateProvince, Country, ZipCode, Email)
VALUES (26, 'Monica', 'Cast', '112 W. 4th', 'Greensburg','VA','USA','27754', '[email protected]');
INSERT INTO Shopper (idshopper, FirstName, LastName, Address, City, StateProvince, Country, ZipCode, Email)
VALUES (27, 'Pete', 'Parker', '1 Queens', 'New York','NY','USA','67233', '[email protected]');
  

CREATE TABLE Basket
(
    idBasket INT PRIMARY KEY NOT NULL,
    idShopper INT,
    Quantity TINYINT,
    SubTotal DECIMAL(7,2),
    OrderDate datetime not null,
    CONSTRAINT bskt_idshopper_fk FOREIGN KEY (idShopper) REFERENCES Shopper(idShopper) 
);

insert into Basket(idbasket, quantity, idshopper,subtotal,OrderDate)
    values (3, 3, 21,26.60,'23-JAN-2019');
insert into Basket (idbasket, quantity, idshopper,subtotal,OrderDate)
    values (4, 1, 21, 28.50,'12-FEB-2020');
insert into Basket(idbasket, quantity, idshopper,subtotal,OrderDate)
    values (5, 4, 22, 41.60, '19-FEB-2020');
insert into Basket(idbasket, quantity, idshopper,subtotal,OrderDate)
    values (6, 3, 22, 149.99, '01-MAR-2020');
insert into Basket(idbasket, quantity, idshopper,subtotal,OrderDate)
    values (7, 2, 23, 21.60,'26-JAN-2019');
insert into Basket(idbasket, quantity, idshopper,subtotal,OrderDate)
    values (8, 2, 23, 21.60,'16-FEB-2021');
insert into Basket(idbasket, quantity, idshopper,subtotal,OrderDate)
    values (9, 2, 23, 21.60,'02-MAR-2021');
insert into Basket(idbasket, quantity, idshopper,subtotal,OrderDate)
    values (10, 3, 24, 38.90,'07-FEB-2020');
insert into Basket(idbasket, quantity, idshopper,subtotal,OrderDate)
    values (11, 1, 24, 10.00, '27-FEB-2021');
insert into Basket(idbasket, quantity, idshopper,subtotal,OrderDate)
    values (12, 7, 25, 72.40, '19-FEB-2020');
insert into Basket(idbasket, quantity, idshopper,subtotal,OrderDate)
    values (15, 2, 27, 16.20,'14-FEB-2021');
insert into Basket (idbasket, quantity, idshopper,subtotal,OrderDate)
    values (16, 2, 27, 21.69, '24-FEB-2021');


CREATE TABLE BasketItem (
    idBasketItem INT PRIMARY KEY NOT NULL,
    idProduct SMALLINT,
    Quantity TINYINT,
    idBasket INT,
    CONSTRAINT bsktitem_bsktid_fk FOREIGN KEY (idBasket) REFERENCES Basket(idBasket),
    CONSTRAINT bsktitem_idprod_fk FOREIGN KEY (idproduct) REFERENCES Product(idproduct));

insert into BasketItem
      values (15, 6, 1, 3);
insert into BasketItem
      values (16, 8,  2, 3);
insert into BasketItem
      values (17, 4, 1, 4);
insert into BasketItem
      values (18, 7, 1, 5);
insert into BasketItem
      values (19, 8, 1, 5);
insert into BasketItem
      values (20, 9,1, 5);
insert into BasketItem
      values (21, 10, 1, 5);
insert into BasketItem
      values (22, 10, 2, 6);
insert into BasketItem
      values (23, 2, 1, 6);
insert into BasketItem
      values (24, 7, 1, 7);
insert into BasketItem
      values (25, 8, 1, 7);
insert into BasketItem
      values (26, 7, 1, 8);
insert into BasketItem
      values (27, 8, 1, 8);
insert into BasketItem
      values (28, 7, 1, 9);
insert into BasketItem
      values (29, 8, 1, 9);
insert into BasketItem
      values (30, 6, 1, 10);
insert into BasketItem
      values (31, 8, 1, 10);
insert into BasketItem
      values (32, 4, 1, 10);
insert into BasketItem
      values (33, 9, 1, 11);
insert into BasketItem
      values (34, 8, 2, 12);
insert into BasketItem
      values (35, 9, 2, 12);
insert into BasketItem
      values (36, 6, 2, 12);
insert into BasketItem
      values (37, 7, 1, 12);
insert into BasketItem
      values (40, 8, 1, 15);
insert into BasketItem
      values (41, 7, 1, 15);
insert into BasketItem
      values (42, 8, 1, 16);
insert into BasketItem
      values (43, 7, 1, 16);

I have drugged and dropped the SQL script and it executed normally in my (localdb)\MSSQLLocalDB: SQL script executed

Upvotes: 0

Views: 1445

Answers (1)

Nicholas Hunter
Nicholas Hunter

Reputation: 1845

Try splitting the script into individual commands and using two database connections.

One database connection to database=master. You use that one to drop and recreate the OMS database.

const string connectionString1 = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=master;Integrated Security=True;";
using (SqlConnection cn1 = new SqlConnection(connectionString1)) {
    cn1.Open();
    string[] cmds = { 
        @"
            --Delete the OMS (Order Management System) database if it exists. 
            IF EXISTS(SELECT * from sys.databases WHERE name='OMS') 
            BEGIN 
                DROP DATABASE OMS; 
            END 
        ", @"
            CREATE DATABASE OMS;
        "};
    using (SqlCommand cmd = new SqlCommand()) {
        cmd.Connection = cn1;
        foreach (string sql in cmds) {
            cmd.CommandText = sql;
            int recordsAffected = cmd.ExecuteNonQuery();
            Console.WriteLine($"Executed sql Command '{sql}', {recordsAffected} record(s) affected.");
        }
    }
}

The other connection defaults to database=OMS. You use that to create the database objects.

const string connectionString2 = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=OMS;Integrated Security=True;";
using (SqlConnection cn2 = new SqlConnection(connectionString2)) {
    cn2.Open();
    string[] cmds = { 
        "CREATE TABLE Product ...",
        "insert into Product( ..."
    };
    using (SqlCommand cmd = new SqlCommand()) {
        cmd.Connection = cn2;
        foreach (string sql in cmds) {
            cmd.CommandText = sql;
            int recordsAffected = cmd.ExecuteNonQuery();
            Console.WriteLine($"Executed sql Command '{sql}', {recordsAffected} record(s) affected.");
        }
    }
}

Upvotes: 2

Related Questions