83LFiukw

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
124        private void button1_Click_1(object sender, EventArgs e)
125        {
126            string path = System.IO.Directory.GetCurrentDirectory();
127            path += "\\";
128            MessageBox.Show(path, "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
129           
130        }
131
132        private void button2_Click(object sender, EventArgs e)
133        {
134            SqlConnection mConnection = new SqlConnection("Server=localhost;Integrated security=SSPI");
135
136            bool value = CheckDatabaseExists(mConnection, "WorkplaceDatabase");
137            if(value == true)
138            {
139                MessageBox.Show("EXIST", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
140            }
141            else
142            MessageBox.Show("Does not EXIST", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
143
144        }
145
146        private void Form1_Load(object sender, EventArgs e)
147        {
148            SqlConnection mConnection = new SqlConnection("Server=localhost;Integrated security=SSPI");
149
150        }
151    }
152}