i am working on this assignment. Search a database and save the results to a XML file. here is what i got so far
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Xml;
namespace BOOK
{
public partial class Form1 : Form
{
SqlConnection cn = new SqlConnection(@"Data Source=KRISTIANBH\SQLEXPRESS;Initial Catalog=Books;Integrated Security=True");
DataSet ds;
public Form1()
{
InitializeComponent();
}
private void getAll()
{
string sSql = " select * from Details";
SqlCommand cm = new SqlCommand(sSql, cn);
SqlDataReader dr = null;
try
{
cn.Open();
dr = cm.ExecuteReader();
listView1.Items.Clear();
while (dr.Read())
{
string BOOK_ID = dr["BOOK_ID"].ToString();
string BOOK_NAME = dr["BOOK_NAME"].ToString();
string AUTHOR = dr["AUTHOR"].ToString();
string ISBN = dr["ISBN"].ToString();
string DATE_PUBLISHED = dr["DATE_PUBLISHED"].ToString();
ListViewItem lv = listView1.Items.Add(BOOK_ID);
lv.SubItems.Add(BOOK_NAME);
lv.SubItems.Add(AUTHOR);
lv.SubItems.Add(ISBN);
lv.SubItems.Add(DATE_PUBLISHED);
}
dr.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void getAuthors()
{
string AuthorSearch;
AuthorSearch = txtSearch.Text;
string sSql = " select * from Details where AUTHOR = '" + AuthorSearch + "'";
SqlCommand cm = new SqlCommand(sSql, cn);
SqlDataReader dr = null;
try
{
cn.Open();
dr = cm.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
string BOOK_ID = dr["BOOK_ID"].ToString();
string BOOK_NAME = dr["BOOK_NAME"].ToString();
string AUTHOR = dr["AUTHOR"].ToString();
string ISBN = dr["ISBN"].ToString();
string DATE_PUBLISHED = dr["DATE_PUBLISHED"].ToString();
ListViewItem lv = listView1.Items.Add(BOOK_ID);
lv.SubItems.Add(BOOK_NAME);
lv.SubItems.Add(AUTHOR);
lv.SubItems.Add(ISBN);
lv.SubItems.Add(DATE_PUBLISHED);
}
dr.Close();
}
else
{
listView1.Items.Add("No data found");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
}
private void getISBN()
{
try
{
Double Number;
Number = Convert.ToDouble(txtSearch.Text);
string sSql = "Select * from Details where ISBN = '" + Number + "'";
SqlCommand cm = new SqlCommand(sSql, cn);
SqlDataReader dr = null;
cn.Open();
dr = cm.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
string BOOK_ID = dr["BOOK_ID"].ToString();
string BOOK_NAME = dr["BOOK_NAME"].ToString();
string AUTHOR = dr["AUTHOR"].ToString();
string ISBN = dr["ISBN"].ToString();
string DATE_PUBLISHED = dr["DATE_PUBLISHED"].ToString();
ListViewItem lv = listView1.Items.Add(BOOK_ID);
lv.SubItems.Add(BOOK_NAME);
lv.SubItems.Add(AUTHOR);
lv.SubItems.Add(ISBN);
lv.SubItems.Add(DATE_PUBLISHED);
}
dr.Close();
}
else
{
listView1.Items.Add("No data found");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
}
private void getTitle()
{
string Title;
Title = txtSearch.Text;
string sSql = "Select * from Details where BOOK_NAME = '" + Title + "'";
SqlCommand cm = new SqlCommand(sSql, cn);
SqlDataReader dr = null;
try
{
cn.Open();
dr = cm.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
string BOOK_ID = dr["BOOK_ID"].ToString();
string BOOK_NAME = dr["BOOK_NAME"].ToString();
string AUTHOR = dr["AUTHOR"].ToString();
string ISBN = dr["ISBN"].ToString();
string DATE_PUBLISHED = dr["DATE_PUBLISHED"].ToString();
ListViewItem lv = listView1.Items.Add(BOOK_ID);
lv.SubItems.Add(BOOK_NAME);
lv.SubItems.Add(AUTHOR);
lv.SubItems.Add(ISBN);
lv.SubItems.Add(DATE_PUBLISHED);
}
dr.Close();
}
else
{
listView1.Items.Add("No data found");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
if (comboBox1.Text == "All")
{
getAll();
}
else if (comboBox1.Text == "Title")
{
getTitle();
}
else if (comboBox1.Text == "Author")
{
getAuthors();
}
else if (comboBox1.Text == "ISBN")
{
getISBN();
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
bool changed = true;
if (changed)
{
listView1.Items.Clear();
txtSearch.Text = "";
}
}
private void btnSave_Click(object sender, EventArgs e)
{
string query = "insert into Details (BOOK_ID,BOOK_NAME,AUTHOR,ISBN) "
+ "Values ('" + txtBookID.Text.ToString() + "','" + txtTitle.Text.ToString() + "','" + txtAuthor.Text.ToString() + "','" + txtISBN.Text.ToString() + "')";
SqlCommand cm = new SqlCommand(query, cn);
SqlDataReader dr = null;
try
{
cn.Open();
dr = cm.ExecuteReader();
MessageBox.Show("Data saved");
while (dr.Read()) ;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
}
private void btnExport_Click(object sender, EventArgs e)
{
}
}
}