· 10 years ago · Sep 26, 2016, 12:28 PM
1private void CheckIfDBExist()
2 {
3
4 SqlConnection tmpConn = new SqlConnection("server=(local)\SQLEXPRESS;user=sa;pwd=123");
5
6 string sqlCreateDBQuery = string.Format("if not exists (select name from sys.databases where name = 'Devrimer_v2222')create database Devrimer_v2222");
7
8 SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn);
9
10 string constr = Tools.Baglanti.ConnectionString.ToString();
11
12 try
13 {
14 tmpConn.Open();
15 int sonuc = sqlCmd.ExecuteNonQuery();
16
17 if (sonuc != 0)
18 {
19 tmpConn.Close();
20
21 string query = "if not exists(select * from sys.tables where name = 'Companies' ";
22 query += ")";
23 query += "CREATE TABLE [dbo].[Companies]([Id][int] IDENTITY(1, 1) NOT NULL,";
24 query += "[Name][nvarchar](100) NULL,";
25 query += "[Adress][nvarchar](500) NULL,";
26 query += "[TelNo][char](20) NULL,";
27 query += "[Active][bit] NOT NULL CONSTRAINT[DF_Companies_Active] DEFAULT((1)),";
28 query += "CONSTRAINT[PK_Companies] PRIMARY KEY CLUSTERED([Id] ASC ) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,";
29 query += "IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,";
30 query += "ALLOW_PAGE_LOCKS = ON) ON[PRIMARY] ) ON[PRIMARY]";
31 query += " SET ANSI_PADDING OFF";
32
33
34 using (SqlConnection con = new SqlConnection(constr))
35 {
36 using (SqlCommand cmd = new SqlCommand(query))
37 {
38 cmd.Connection = con;
39 con.Open();
40 cmd.ExecuteNonQuery();
41 con.Close();
42 }
43 }
44
45 string docQuery = "if not exists(select * from sys.tables where name = 'Documents' ";
46 docQuery += ")";
47 docQuery += "CREATE TABLE [dbo].[Documents]([Id] [int] IDENTITY(1,1) NOT NULL,";
48 docQuery += "[Name] [nvarchar](50) NULL,";
49 docQuery += "[Description] [nvarchar](500) NULL,";
50 docQuery += "[ValidPeriod] [int] NULL,";
51 docQuery += "[PeriodType] [nchar](10) NULL,";
52 docQuery += "[Active] [bit] NOT NULL CONSTRAINT [DF_Documents_Active] DEFAULT ((1)), ";
53 docQuery += "CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, ";
54 docQuery += "STATISTICS_NORECOMPUTE = OFF, ";
55 docQuery += "IGNORE_DUP_KEY = OFF, ";
56 docQuery += "ALLOW_ROW_LOCKS = ON, ";
57 docQuery += " ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";
58
59 using (SqlConnection con = new SqlConnection(constr))
60 {
61 using (SqlCommand cmd = new SqlCommand(docQuery))
62 {
63 cmd.Connection = con;
64 con.Open();
65 cmd.ExecuteNonQuery();
66 con.Close();
67 }
68 }
69 string EmpQuery = "if not exists(select * from sys.tables where name = 'Employees' ";
70 EmpQuery += ")";
71 EmpQuery += "CREATE TABLE [dbo].[Employees]( [Id] [int] IDENTITY(1,1) NOT NULL,";
72 EmpQuery += "[Name] [nvarchar](50) NULL,";
73 EmpQuery += "[SurName] [nvarchar](50) NULL,";
74 EmpQuery += "[Title] [nvarchar](50) NULL,";
75 EmpQuery += "[Gender] [tinyint] NULL,";
76 EmpQuery += "[MaritalStatus] [tinyint] NULL,";
77 EmpQuery += "[Tckn] [char](11) NULL,";
78 EmpQuery += "[ReportTo] [int] NULL,";
79 EmpQuery += "[BirthDate] [date] NULL, ";
80 EmpQuery += "[TelNo] [char](20) NULL, ";
81 EmpQuery += "[Adress] [nvarchar](500) NULL, ";
82 EmpQuery += "[Email] [nvarchar](250) NULL, ";
83 EmpQuery += "[CompanyID] [int] NULL,";
84 EmpQuery += "[StartWorkingDate] [date] NULL, ";
85 EmpQuery += "[Active] [bit] NOT NULL CONSTRAINT [DF_Employees_Active] DEFAULT ((1)), ";
86 EmpQuery += " CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, ";
87 EmpQuery += "STATISTICS_NORECOMPUTE = OFF, ";
88 EmpQuery += "IGNORE_DUP_KEY = OFF, ";
89 EmpQuery += "ALLOW_ROW_LOCKS = ON, ";
90 EmpQuery += "ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ";
91 EmpQuery += ";";
92 EmpQuery += "SET ANSI_PADDING OFF ";
93 EmpQuery += "ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_Companies1] FOREIGN KEY([CompanyID]) REFERENCES [dbo].[Companies] ([Id]) ";
94 EmpQuery += ";";
95 EmpQuery += "ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Companies1] ";
96
97 using (SqlConnection con = new SqlConnection(constr))
98 {
99 using (SqlCommand cmd = new SqlCommand(EmpQuery))
100 {
101 cmd.Connection = con;
102 con.Open();
103 cmd.ExecuteNonQuery();
104 con.Close();
105 }
106 }
107
108 string userQuery = "if not exists(select * from sys.tables where name = 'Users' ";
109 userQuery += ")";
110 userQuery += "CREATE TABLE [dbo].[Users]( [Id] [int] IDENTITY(1,1) NOT NULL, ";
111 userQuery += "[EmployeID] [int] NULL, ";
112 userQuery += "[UserName] [nvarchar](50) NULL, ";
113 userQuery += "[Password] [nchar](10) NULL, ";
114 userQuery += "[Active] [bit] NOT NULL CONSTRAINT [DF_Users_Aktif] DEFAULT ((1)), ";
115 userQuery += " CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, ";
116 userQuery += "STATISTICS_NORECOMPUTE = OFF, ";
117 userQuery += "IGNORE_DUP_KEY = OFF, ";
118 userQuery += "ALLOW_ROW_LOCKS = ON,";
119 userQuery += "ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ";
120
121 using (SqlConnection con = new SqlConnection(constr))
122 {
123 using (SqlCommand cmd = new SqlCommand(userQuery))
124 {
125 cmd.Connection = con;
126 con.Open();
127 cmd.ExecuteNonQuery();
128 con.Close();
129 }
130 }
131
132 string docEmpQuery = "if not exists(select * from sys.tables where name = 'EmployeeDocuments' ";
133 docEmpQuery += ")";
134 docEmpQuery += "CREATE TABLE [dbo].[EmployeeDocuments]( [EmployeeID] [int] NOT NULL, ";
135 docEmpQuery += "[DocumentID] [int] NOT NULL, ";
136 docEmpQuery += "[GivenDate] [date] NULL, ";
137 docEmpQuery += "[LastValidDate] [date] NULL, ";
138 docEmpQuery += "CONSTRAINT [PK_EmployeeDocuments] PRIMARY KEY CLUSTERED ( [EmployeeID] ASC, ";
139 docEmpQuery += "[DocumentID] ASC )WITH (PAD_INDEX = OFF, ";
140 docEmpQuery += "STATISTICS_NORECOMPUTE = OFF, ";
141 docEmpQuery += "IGNORE_DUP_KEY = OFF, ";
142 docEmpQuery += "ALLOW_ROW_LOCKS = ON,";
143 docEmpQuery += "ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]";
144 docEmpQuery += ";";
145 docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] WITH CHECK ADD CONSTRAINT [FK_EmployeeDocuments_Documents1] FOREIGN KEY([DocumentID])REFERENCES [dbo].[Documents] ([Id]) ";
146 docEmpQuery += ";";
147 docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] CHECK CONSTRAINT [FK_EmployeeDocuments_Documents1] ";
148 docEmpQuery += "; ";
149 docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] WITH CHECK ADD CONSTRAINT [FK_EmployeeDocuments_Employees1] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].Employees] ([Id]) ";
150 docEmpQuery += ";";
151 docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] CHECK CONSTRAINT [FK_EmployeeDocuments_Employees1] ";
152
153 using (SqlConnection con = new SqlConnection(constr))
154 {
155 using (SqlCommand cmd = new SqlCommand(docEmpQuery))
156 {
157 cmd.Connection = con;
158 con.Open();
159 cmd.ExecuteNonQuery();
160 con.Close();
161 }
162 }
163 }
164 }
165
166 catch (Exception hata)
167 {
168 MessageBox.Show(string.Format(sqlCmd.CommandText.ToString() + "nnHata: " + hata.Message.ToString()));
169 }
170 finally
171 {
172 tmpConn.Close();
173 }
174 }
175}