· 9 years ago · Oct 31, 2016, 12:28 PM
1using System;
2using System.Collections.Generic;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Linq;
7using System.Text;
8using System.Threading.Tasks;
9using System.Windows.Forms;
10using System.Data.SqlClient;
11
12namespace SqlScript_task3
13{
14 public partial class Form1 : Form
15 {
16 public Form1()
17 {
18 InitializeComponent();
19 }
20 private static bool CheckDatabaseExists(SqlConnection tmpConn, string databaseName)
21 {
22 string sqlCreateDBQuery;
23 bool result = false;
24 try
25 {
26 sqlCreateDBQuery = string.Format("SELECT database_id FROM sys.databases WHERE Name = '{0}'", databaseName);
27 using (tmpConn)
28 {
29 using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
30 {
31 tmpConn.Open();
32 object resultObj = sqlCmd.ExecuteScalar();
33 int databaseID = 0;
34 if (resultObj != null)
35 {
36 int.TryParse(resultObj.ToString(), out databaseID);
37 }
38 tmpConn.Close();
39 result = (databaseID > 0);
40 }
41 }
42 }
43 catch (Exception ex)
44 {
45 result = false;
46 }
47
48 return result;
49 }
50
51 private void Form1_Load(object sender, EventArgs e)
52 {
53 string pathProject = System.IO.Directory.GetCurrentDirectory();
54 string DataPathName = pathProject + "\\WorkplaceDatabaseData.mdf";
55 string LogPathName = pathProject + "\\WorkplaceDatabaseLog.ldf";
56
57 string connectionString = "Server=localhost;Integrated security=SSPI";
58
59 bool databaseExist = CheckDatabaseExists(new SqlConnection(connectionString), "WorkplaceDatabase");
60
61 SqlConnection mConnection = new SqlConnection(connectionString);
62 if (databaseExist == false)
63 {
64 //Create database
65 string strCreateDB = "CREATE DATABASE WorkplaceDatabase ON PRIMARY " +
66 "(NAME = WorkPlaceDatabase_Data, " +
67 "FILENAME = '" + DataPathName + "', " +
68 "SIZE = 6MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
69 "LOG ON (NAME = WorkPlaceDatabase_Log, " +
70 "FILENAME = '" + LogPathName + "', " +
71 "SIZE = 1MB, " +
72 "MAXSIZE = 5MB, " +
73 "FILEGROWTH = 10%)";
74 SqlCommand mCommand = new SqlCommand(strCreateDB, mConnection);
75 try
76 {
77 mConnection.Open();
78 mCommand.ExecuteNonQuery();
79 label_state.Text = "DataBase was Created Successfully";
80 }
81 catch (Exception ex)
82 {
83 MessageBox.Show(ex.ToString(), "SqlScript_task3", MessageBoxButtons.OK, MessageBoxIcon.Information);
84 }
85 finally
86 {
87 if (mConnection.State == ConnectionState.Open)
88 {
89 mConnection.Close();
90 }
91 }
92 }
93 else
94 {
95 label_state.Text = "Database already exists";
96 }
97 }
98 private void CreateTable(SqlConnection connection, string strRequest)
99 {
100 SqlCommand sqlCommand = new SqlCommand(strRequest, connection);
101 try
102 {
103 connection.Open();
104 sqlCommand.ExecuteNonQuery();
105 label_state_table.Text = "Tables has been created";
106 }
107 catch (Exception ex)
108 {
109 MessageBox.Show(ex.ToString(), "SqlScript_task3", MessageBoxButtons.OK, MessageBoxIcon.Information);
110 }
111 finally
112 {
113 if (connection.State == ConnectionState.Open)
114 {
115 connection.Close();
116 }
117 }
118 }
119 private void btnAddTables_Click(object sender, EventArgs e)
120 {
121 string connectionString = "Server=localhost;Initial Catalog=WorkplaceDatabase;Integrated security=SSPI;database=WorkplaceDatabase";
122 string[] strCreateRequest = new string[5];
123 strCreateRequest[0] = "IF NOT EXISTS (SELECT * FROM sys.objects " +
124 "WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U')) CREATE TABLE Person" +
125 "(PersonID INTEGER CONSTRAINT PKeyPersonID PRIMARY KEY," +
126 "First_name CHAR(50), Last_name CHAR(50), Birth_date CHAR(255), " +
127 "Address CHAR(255))";
128 strCreateRequest[1] = "IF NOT EXISTS (SELECT * FROM sys.objects " +
129 "WHERE object_id = OBJECT_ID(N'[dbo].[PhoneNumber]') AND type in (N'U')) CREATE TABLE PhoneNumber" +
130 "(NumberID INTEGER CONSTRAINT PKeyNumberID PRIMARY KEY," +
131 "PersonID INTEGER, Phone_number CHAR(255))";
132 strCreateRequest[2] = "IF NOT EXISTS (SELECT * FROM sys.objects " +
133 "WHERE object_id = OBJECT_ID(N'[dbo].[Company]') AND type in (N'U')) CREATE TABLE Company" +
134 "(CompanyID INTEGER CONSTRAINT PKeyCompanyID PRIMARY KEY," +
135 "Name CHAR(255))";
136 strCreateRequest[3] = "IF NOT EXISTS (SELECT * FROM sys.objects " +
137 "WHERE object_id = OBJECT_ID(N'[dbo].[BusinessAddress]') AND type in (N'U')) CREATE TABLE BusinessAddress" +
138 "(AddressID INTEGER CONSTRAINT PKeyAddressID PRIMARY KEY," +
139 "CompanyID INTEGER, Address CHAR(255))";
140 strCreateRequest[4] = "IF NOT EXISTS (SELECT * FROM sys.objects " +
141 "WHERE object_id = OBJECT_ID(N'[dbo].[PlaceOfEmployment]') AND type in (N'U')) CREATE TABLE PlaceOfEmployment" +
142 "(PlaceID INTEGER CONSTRAINT PKeyPlaceID PRIMARY KEY," +
143 "PersonID INTEGER, AddressID CHAR(255))";
144 for(int i = 0; i < 5; i++)
145 {
146 CreateTable(new SqlConnection(connectionString), strCreateRequest[i]);
147 }
148
149 }
150 }
151}