· 9 years ago · Oct 31, 2016, 11:52 AM
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 SqlConnection connection = new SqlConnection(connectionString);
112
113 SqlCommand cmdTablePerson = new SqlCommand(strTablePhoneNumber, connection);
114 //SqlCommand cmdTablePhoneNumber = new SqlCommand(strCreateTable, connection);
115 try
116 {
117 connection.Open();
118 cmdTablePerson.ExecuteNonQuery();
119
120 label_state_table.Text = "Tables has been created";
121 }
122 catch (Exception ex)
123 {
124 MessageBox.Show(ex.ToString(), "SqlScript_task3", MessageBoxButtons.OK, MessageBoxIcon.Information);
125 }
126 finally
127 {
128 if (connection.State == ConnectionState.Open)
129 {
130 connection.Close();
131 }
132 }
133 }
134 }
135}