コード例 #1
0
        private void btnSearchS_Click(object sender, EventArgs e)
        {
            string str = txtSearchS.Text;

            string sqlQuery = string.Format("SELECT * FROM dbo.students " +
                                            "WHERE studentId LIKE '{0}'" +
                                            "OR name LIKE '{0}'" +
                                            "OR lastname LIKE '{0}'" +
                                            "OR birthdate LIKE '{0}'" +
                                            "OR gender LIKE '{0}'", str);

            using (SqlConnection connection = ConnectToDB.ConnectDB())
            {
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    using (SqlDataAdapter adapt = new SqlDataAdapter(command))
                    {
                        DataSet dataSet = new DataSet();
                        adapt.Fill(dataSet);

                        dgvStudents.DataSource = dataSet.Tables[0];
                    }
                }
            }
        }
コード例 #2
0
        private void btnSearchB_Click(object sender, EventArgs e)
        {
            string str = txtSearchB.Text;

            string sqlQuery = string.Format("SELECT A.bookId, A.name, B.lastname AS author, C.name AS genre " +
                                            "FROM dbo.books AS A, dbo.authors AS B, dbo.types AS C " +
                                            "WHERE A.authorId = B.authorId " +
                                            "AND A.typeId = C.typeId " +
                                            "AND (A.bookId LIKE '{0}' " +
                                            "OR A.name LIKE '{0}' " +
                                            "OR B.name LIKE '{0}' " +
                                            "OR C.name LIKE '{0}') ", str);

            using (SqlConnection connection = ConnectToDB.ConnectDB())
            {
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    using (SqlDataAdapter adapt = new SqlDataAdapter(command))
                    {
                        DataSet dataSet = new DataSet();
                        adapt.Fill(dataSet);

                        dgvBooks.DataSource = dataSet.Tables[0];
                    }
                }
            }
        }
コード例 #3
0
        private void btnRemoveB_Click(object sender, EventArgs e)
        {
            string id = dgvBooks.CurrentRow.Cells[0].Value.ToString();

            string sqlQuery = string.Format("DELETE FROM dbo.books WHERE bookId = {0}", id);

            using (SqlConnection connection = ConnectToDB.ConnectDB())
            {
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    command.ExecuteNonQuery();
                }
            }

            PopulateBooksTable();

            MessageBox.Show("Successfully removed.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
コード例 #4
0
        public void PopulateStudentsTable()
        {
            string sqlQuery = "SELECT * FROM dbo.students ORDER BY studentid ASC";

            using (SqlConnection connection = ConnectToDB.ConnectDB())
            {
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    using (SqlDataAdapter adapt = new SqlDataAdapter(command))
                    {
                        DataSet dataSet = new DataSet();
                        adapt.Fill(dataSet);

                        dgvStudents.DataSource = dataSet.Tables[0];
                    }
                }
            }
        }
コード例 #5
0
        private void btnAddS_Click(object sender, EventArgs e)
        {
            string sqlQuery = string.Format("INSERT INTO dbo.students (name, lastname, birthdate, gender) " +
                                            "VALUES ('{0}', '{1}', '{2}', '{3}')", txtStudentName.Text,
                                            txtStudentLastname.Text, txtStudentBirthdate.Text, cmbStudentGender.Text);

            string dateFormat = "yyyy-mm-dd";

            DateTime dateTime;

            if (DateTime.TryParseExact(txtStudentBirthdate.Text, dateFormat, CultureInfo.InvariantCulture,
                                       DateTimeStyles.None, out dateTime) &&
                txtStudentName.Text != String.Empty &&
                txtStudentLastname.Text != String.Empty)
            {
                using (SqlConnection connection = ConnectToDB.ConnectDB())
                {
                    using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }

                MessageBox.Show("Successfully added.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);

                PopulateStudentsTable();
            }
            else if (txtStudentName.Text == String.Empty)
            {
                MessageBox.Show("Enter the name.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else if (txtStudentLastname.Text == String.Empty)
            {
                MessageBox.Show("Enter the last name.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("Enter date in the following fromat: YYYY-MM-DD.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
コード例 #6
0
        public void PopulateBooksTable()
        {
            string sqlQuery = "SELECT A.bookId, A.name, B.lastname AS author, C.name AS genre " +
                              "FROM dbo.books AS A, dbo.authors AS B, dbo.types AS C " +
                              "WHERE A.authorId = B.authorId " +
                              "AND A.typeId = C.typeId " +
                              "ORDER BY A.bookid ASC";

            using (SqlConnection connection = ConnectToDB.ConnectDB())
            {
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    using (SqlDataAdapter adapt = new SqlDataAdapter(command))
                    {
                        DataSet dataSet = new DataSet();
                        adapt.Fill(dataSet);

                        dgvBooks.DataSource = dataSet.Tables[0];
                    }
                }
            }
        }
コード例 #7
0
        private void btnAddB_Click(object sender, EventArgs e)
        {
            int typeId   = 0;
            int authorId = 0;

            if (txtBookName.Text != String.Empty &&
                txtAuthorName.Text != String.Empty &&
                txtAuthorLastname.Text != String.Empty)
            {
                string sqlQueryA = string.Format("SELECT typeId " +
                                                 "FROM dbo.types " +
                                                 "WHERE name " +
                                                 "LIKE '{0}'", cmbBooksGenres.Text);

                using (SqlConnection connection = ConnectToDB.ConnectDB())
                {
                    using (SqlCommand command = new SqlCommand(sqlQueryA, connection))
                    {
                        using (SqlDataAdapter adapt = new SqlDataAdapter(command))
                        {
                            DataSet dataSet = new DataSet();
                            adapt.Fill(dataSet);
                            typeId = Int32.Parse(dataSet.Tables[0].Rows[0]["typeId"].ToString());
                        }
                    }
                }

                string sqlQueryB = string.Format("SELECT authorId " +
                                                 "FROM dbo.authors " +
                                                 "WHERE name " +
                                                 "LIKE '{0}' " +
                                                 "AND lastname " +
                                                 "LIKE '{1}'", txtAuthorName.Text, txtAuthorLastname.Text);

                using (SqlConnection connection = ConnectToDB.ConnectDB())
                {
                    using (SqlCommand command = new SqlCommand(sqlQueryB, connection))
                    {
                        using (SqlDataAdapter adapt = new SqlDataAdapter(command))
                        {
                            DataSet dataSet = new DataSet();
                            adapt.Fill(dataSet);
                            try
                            {
                                authorId = Int32.Parse(dataSet.Tables[0].Rows[0]["authorId"].ToString());
                            }
                            catch (IndexOutOfRangeException ex)
                            {
                                authorId = 0;
                            }
                        }
                    }
                }

                if (authorId != 0)
                {
                    string sqlQueryC = string.Format("INSERT INTO dbo.books (name, authorId, typeId) " +
                                                     "VALUES ('{0}', " +
                                                     "(SELECT authorId FROM dbo.authors WHERE authorId = {1}), " +
                                                     "(SELECT typeId FROM dbo.types WHERE typeId = {2}))", txtBookName.Text, authorId, typeId);

                    using (SqlConnection connection = ConnectToDB.ConnectDB())
                    {
                        using (SqlCommand command = new SqlCommand(sqlQueryC, connection))
                        {
                            command.ExecuteNonQuery();
                        }
                    }
                }
                else if (authorId == 0)
                {
                    string sqlQueryD = string.Format("INSERT INTO dbo.authors (name, lastname) " +
                                                     "VALUES ('{0}', '{1}')", txtAuthorName.Text, txtAuthorLastname.Text);

                    using (SqlConnection connection = ConnectToDB.ConnectDB())
                    {
                        using (SqlCommand command = new SqlCommand(sqlQueryD, connection))
                        {
                            command.ExecuteNonQuery();
                        }
                    }

                    using (SqlConnection connection = ConnectToDB.ConnectDB())
                    {
                        using (SqlCommand command = new SqlCommand(sqlQueryB, connection))
                        {
                            using (SqlDataAdapter adapt = new SqlDataAdapter(command))
                            {
                                DataSet dataSet = new DataSet();
                                adapt.Fill(dataSet);
                                authorId = Int32.Parse(dataSet.Tables[0].Rows[0]["authorId"].ToString());
                            }
                        }
                    }

                    string sqlQueryC = string.Format("INSERT INTO dbo.books (name, authorId, typeId) " +
                                                     "VALUES ('{0}', " +
                                                     "(SELECT authorId FROM dbo.authors WHERE authorId = {1}), " +
                                                     "(SELECT typeId FROM dbo.types WHERE typeId = {2}))", txtBookName.Text, authorId, typeId);

                    using (SqlConnection connection = ConnectToDB.ConnectDB())
                    {
                        using (SqlCommand command = new SqlCommand(sqlQueryC, connection))
                        {
                            command.ExecuteNonQuery();
                        }
                    }
                }

                MessageBox.Show("Successfully added.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);

                PopulateBooksTable();
            }
            else if (txtBookName.Text == String.Empty)
            {
                MessageBox.Show("Enter the book name.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else if (txtAuthorName.Text == String.Empty)
            {
                MessageBox.Show("Enter the author's name.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else if (txtAuthorLastname.Text == String.Empty)
            {
                MessageBox.Show("Enter the author's last name.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }