· 9 years ago · Oct 31, 2016, 10:42 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 //tmpConn = new SqlConnection("server=(local)\\SQLEXPRESS;Trusted_Connection=yes");
27 sqlCreateDBQuery = string.Format("SELECT database_id FROM sys.databases WHERE Name = '{0}'", databaseName);
28 using (tmpConn)
29 {
30 using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
31 {
32 tmpConn.Open();
33 object resultObj = sqlCmd.ExecuteScalar();
34 int databaseID = 0;
35 if (resultObj != null)
36 {
37 int.TryParse(resultObj.ToString(), out databaseID);
38 }
39 tmpConn.Close();
40 result = (databaseID > 0);
41 }
42 }
43 }
44 catch (Exception ex)
45 {
46 result = false;
47 }
48
49 return result;
50 }
51 private void button1_Click(object sender, EventArgs e)
52 {
53 string pathProject = System.IO.Directory.GetCurrentDirectory();
54 string DataPathName = pathProject + "\\WorkplaceDatabaseData.mdf";
55
56 string LogPathName = pathProject + "\\WorkplaceDatabaseLog.ldf";
57 // C://WorkplaceDatabaseData.mdf
58
59 String str; //
60 SqlConnection mConnection = new SqlConnection("Server=localhost;Integrated security=SSPI");
61
62 str = "CREATE DATABASE WorkplaceDatabase ON PRIMARY " +
63 "(NAME = WorkPlaceDatabase_Data, " +
64 "FILENAME = '" + DataPathName + "', " +
65 "SIZE = 6MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
66 "LOG ON (NAME = WorkPlaceDatabase_Log, " +
67 "FILENAME = '" + LogPathName + "', " +
68 "SIZE = 1MB, " +
69 "MAXSIZE = 5MB, " +
70 "FILEGROWTH = 10%)";
71 string sql1;
72 SqlCommand mCommand = new SqlCommand(str, mConnection);
73
74 string strCreateTable = "CREATE TABLE myTable" +
75 "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," +
76 "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)";
77 SqlConnection mConnection2 = new SqlConnection("Server=localhost;Initial Catalog=WorkplaceDatabase;Integrated security=SSPI;database=WorkplaceDatabase");
78 SqlCommand mc2 = new SqlCommand(strCreateTable, mConnection2);
79 try
80 {
81 mConnection.Open();
82 mCommand.ExecuteNonQuery();
83
84 mConnection2.Open();
85 mc2.ExecuteNonQuery();
86 /*var commandStr = "If not exists (select name from sysobjects where name = 'Customer') CREATE TABLE Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date datetime)";
87
88 using (SqlCommand command = new SqlCommand(commandStr, mConnection))
89 command.ExecuteNonQuery();
90
91 SqlCommand cmd = new SqlCommand("create table <Table Name>(empno int,empname varchar(50),salary money);", mConnection);
92 cmd.ExecuteNonQuery();*/
93
94 ///////3
95
96
97 sql1 = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " +
98 "VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 23.98 ) ";
99 mc2 = new SqlCommand(sql1, mConnection2);
100 mc2.ExecuteNonQuery();
101
102 MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
103
104
105 }
106 catch (System.Exception ex)
107 {
108 MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
109 }
110 finally
111 {
112 if (mConnection.State == ConnectionState.Open)
113 {
114 mConnection.Close();
115 }
116 if (mConnection2.State == ConnectionState.Open)
117 {
118 mConnection2.Close();
119 }
120 }
121 }
122
123 private void button2_Click(object sender, EventArgs e)
124 { }
125
126 private void Form1_Load(object sender, EventArgs e)
127 {
128 string pathProject = System.IO.Directory.GetCurrentDirectory();
129 string DataPathName = pathProject + "\\WorkplaceDatabaseData.mdf";
130 string LogPathName = pathProject + "\\WorkplaceDatabaseLog.ldf";
131
132 SqlConnection mConnection = new SqlConnection("Server=localhost;Integrated security=SSPI");
133 bool databaseExist = CheckDatabaseExists(mConnection, "WorkplaceDatabase");
134 if (databaseExist == true)
135 {
136 label_state.Text = "Database already exists";
137 }
138 else
139 {
140 //Create database
141 string str;
142 str = "CREATE DATABASE WorkplaceDatabase ON PRIMARY " +
143 "(NAME = WorkPlaceDatabase_Data, " +
144 "FILENAME = '" + DataPathName + "', " +
145 "SIZE = 6MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
146 "LOG ON (NAME = WorkPlaceDatabase_Log, " +
147 "FILENAME = '" + LogPathName + "', " +
148 "SIZE = 1MB, " +
149 "MAXSIZE = 5MB, " +
150 "FILEGROWTH = 10%)";
151 SqlCommand sqlCommand = new SqlCommand(str, mConnection);
152 try
153 {
154 mConnection.Open();
155 sqlCommand.ExecuteNonQuery();
156 label_state.Text = "DataBase was Created Successfully";
157 }
158 catch (System.Exception ex)
159 {
160 MessageBox.Show(ex.ToString(), "SqlScript_task3", MessageBoxButtons.OK, MessageBoxIcon.Information);
161 }
162 finally
163 {
164 if (mConnection.State == ConnectionState.Open)
165 {
166 mConnection.Close();
167 }
168 }
169 }
170 }
171 }
172}