· 9 years ago · Oct 31, 2016, 12:12 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
99 private void btnAddTables_Click(object sender, EventArgs e)
100 {
101 string connectionString = "Server=localhost;Initial Catalog=WorkplaceDatabase;Integrated security=SSPI;database=WorkplaceDatabase";
102 string strTablePerson = "IF NOT EXISTS (SELECT * FROM sys.objects " +
103 "WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U')) CREATE TABLE Person" +
104 "(PersonID INTEGER CONSTRAINT PKeyPersonID PRIMARY KEY," +
105 "First_name CHAR(50), Last_name CHAR(50), Birth_date CHAR(255), " +
106 "Address CHAR(255))";
107 string strTablePhoneNumber = "IF NOT EXISTS (SELECT * FROM sys.objects " +
108 "WHERE object_id = OBJECT_ID(N'[dbo].[PhoneNumber]') AND type in (N'U')) CREATE TABLE PhoneNumber" +
109 "(NumberID INTEGER CONSTRAINT PKeyNumberID PRIMARY KEY," +
110 "PersonID INTEGER, Phone_number CHAR(255))";
111 string strTableCompany = "IF NOT EXISTS (SELECT * FROM sys.objects " +
112 "WHERE object_id = OBJECT_ID(N'[dbo].[Company]') AND type in (N'U')) CREATE TABLE Company" +
113 "(CompanyID INTEGER CONSTRAINT PKeyCompanyID PRIMARY KEY," +
114 "Name CHAR(255))";
115 string strTableBusinessAddress = "IF NOT EXISTS (SELECT * FROM sys.objects " +
116 "WHERE object_id = OBJECT_ID(N'[dbo].[BusinessAddress]') AND type in (N'U')) CREATE TABLE BusinessAddress" +
117 "(AddressID INTEGER CONSTRAINT PKeyAddressID PRIMARY KEY," +
118 "CompanyID INTEGER, Address CHAR(255))";
119 string strTablePlaceOfEmployment = "IF NOT EXISTS (SELECT * FROM sys.objects " +
120 "WHERE object_id = OBJECT_ID(N'[dbo].[PlaceOfEmployment]') AND type in (N'U')) CREATE TABLE PlaceOfEmployment" +
121 "(PlaceID INTEGER CONSTRAINT PKeyPlaceID PRIMARY KEY," +
122 "PersonID INTEGER, AddressID CHAR(255))";
123 SqlConnection connection = new SqlConnection(connectionString);
124
125 SqlCommand cmdTablePerson = new SqlCommand(strTablePerson, connection);
126 SqlCommand cmdTablePhoneNumber = new SqlCommand(strTablePhoneNumber, connection);
127 SqlCommand cmdTableCompany = new SqlCommand(strTableCompany, connection);
128 SqlCommand cmdTableBusinessAddress = new SqlCommand(strTableBusinessAddress, connection);
129 SqlCommand cmdTablePlaceOfEmployment = new SqlCommand(strTablePlaceOfEmployment, connection);
130 try
131 {
132 connection.Open();
133 cmdTablePerson.ExecuteNonQuery();
134 cmdTablePhoneNumber.ExecuteNonQuery();
135 cmdTableCompany.ExecuteNonQuery();
136 cmdTableBusinessAddress.ExecuteNonQuery();
137 cmdTablePlaceOfEmployment.ExecuteNonQuery();
138 label_state_table.Text = "Tables has been created";
139 }
140 catch (Exception ex)
141 {
142 MessageBox.Show(ex.ToString(), "SqlScript_task3", MessageBoxButtons.OK, MessageBoxIcon.Information);
143 }
144 finally
145 {
146 if (connection.State == ConnectionState.Open)
147 {
148 connection.Close();
149 }
150 }
151 }
152 }
153}