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];
}
}
}
}
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];
}
}
}
}
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);
}
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];
}
}
}
}
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);
}
}
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];
}
}
}
}
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);
}
}