using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Create_DataTables
{
    class Program
    {

        SqlConnection connectA = new SqlConnection("Data Source=.\\spconger;initial catalog=Master;integrated security=true");
        SqlConnection connect = new SqlConnection("Data Source=.\\spconger;initial catalog=MitchellpaperCo;integrated security=true");

        static void Main(string[] args)
        {
            Program p = new Program();
            Console.WriteLine("creating database");
            p.CreateDatabase();
            Console.WriteLine("Adding customers");
            p.AddCustomers();
            Console.WriteLine("Million records");
            p.AddMillionRecords();
            Console.WriteLine("Database Finished. Press any key to exit");
            Console.ReadKey();

           
        }

        void CreateDatabase()
        {
            string dbsql = "Use Master; if exists (SELECT [name] from sys.Databases where [name]='MitchellPaperCo' ) Begin Drop Database MitchellPaperCo end; Create Database MitchellPaperCo; Use MitchellPaperCo; Create Table Customer(CustomerID int identity(1,1) primary Key,CustomerName nvarchar(50));Create Table Sale(SaleNumber int identity(1,1) primary key,SaleDate Datetime,SaleQuantity int,CustomerID int foreign key references Customer(CustomerID),SaleTotal money);";

            SqlCommand cmd = new SqlCommand(dbsql, connectA);

            try
            {
                connectA.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Database Created");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                connectA.Close();
            }

        }

        void AddCustomers()
        {
            string[] cust = new string[31];
            cust[0] = "James Johnson";
            cust[1] = "Laura Martin";
            cust[2] = "Amber Lewis";
            cust[3] = "Mark Collins";
            cust[4] = "Terry Nolan";
            cust[5] = "Tracy Laimer";
            cust[6] = "Peter Koon";
            cust[7] = "Li Nguyen";
            cust[8] = "Maureen Jenson";
            cust[9] = "Kelly Wisdom";
            cust[10] = "Ruth Brown";
            cust[11] = "Dennis Able";
            cust[12] = "Nada Newton";
            cust[13] = "Astrid Goody";
            cust[14] = "Bob Garrot";
            cust[15] = "Patricia Moore";
            cust[16] = "Lenei Kerns";
            cust[17] = "Brady Collins";
            cust[18] = "Tereasa Jones";
            cust[19] = "Rebecca Comer";
            cust[20] = "Alyssa Gorden";
            cust[21] = "Thomas Sanders";
            cust[22] = "Vern Watson";
            cust[23] = "Penny Lane";
            cust[24] = "Harry Smith";
            cust[25] = "Marcos  Martinez";
            cust[26] = "Leah Tanner";
            cust[27] = "Earnest Hannah";
            cust[28] = "Brenda Kanne";
            cust[29] = "Lynne Norton";
            cust[30] = "Pam Singer";

            SqlCommand cmd = new SqlCommand("Insert into Customer(CustomerName) Values(@Cust)", connect);
            cmd.Parameters.Add("@cust",SqlDbType.NVarChar);
            try
            {
                connect.Open();
                for (int i = 0; i < 31; i++)
                {
                    cmd.Parameters[0].Value = cust[i].ToString();
                    cmd.ExecuteNonQuery();
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                connect.Close();
            }

        }

        void AddMillionRecords()
        {
            //create a new command object
            SqlCommand cmd = new SqlCommand();
            //assign properties to the command object
            cmd.Connection = connect; //which connection to use
            // the SQL string with parameters
            cmd.CommandText = "Insert into Sale(SaleDate, SaleQuantity,CustomerID, SaleTotal) Values(@Date, @Quantity, @Cust, @tot)";

            //create a paramaters collection to match the
            //sql statement and give them SQL data types
            cmd.Parameters.Add("@Date", SqlDbType.DateTime);
            cmd.Parameters.Add("@Quantity", SqlDbType.Int);
            cmd.Parameters.Add("@Cust", SqlDbType.Int);
            cmd.Parameters.Add("@tot", SqlDbType.Money);

            //open the connection
            try
            {
                connect.Open();

                //loop a million times writing random values into the table
                for (int i = 1; i < 1000000; i++)
                {

                    Random r = new Random();
                    int c = r.Next(1, 31); //customer number
                    Random r2 = new Random();
                    int s = r2.Next(1, 100); //sale quantity
                    Random r3 = new Random();
                    int d = r3.Next(1, 28); //day
                    Random r4 = new Random();
                    int m = r4.Next(1, 12);//month
                    string mydate = m.ToString() + @"/" + d.ToString() + @"/2008";
                    double tot = s * 5.95;

                    //assign the random values to the parameters
                    cmd.Parameters["@Date"].Value = DateTime.Parse(mydate);
                    cmd.Parameters["@Quantity"].Value = s;
                    cmd.Parameters["@Cust"].Value = c;
                    cmd.Parameters["@tot"].Value = tot;

                    cmd.ExecuteNonQuery();//run the insert query

                    if (i % 10000 == 0)
                    {
                        Console.Write("*");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //close the connection
                connect.Close();
            }
        }
    }
}