public List<Store> GetAllStore()
{
string cs = CocBook.Properties.Settings.Default.connectionString;
SqlConnection con = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("Select * from BookStore", con);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
List<Store> list = new List<Store>();
while (sdr.Read())
{
Store store = new Store();
store.ISBNBook = sdr["ISBN"].ToString();
string ISBN = store.ISBNBook;
store.Quantity = (int)sdr["Quantity"];
Book book = new Book();
BookDAL bookDAL = new BookDAL();
book = bookDAL.GetBookbyISBN(ISBN);
store.BookName = book.BookName;
store.Publisher = book.PublisherName;
store.Unit = book.Unit;
store.Price = book.Price;
list.Add(store);
}
con.Close();
return list;
}
private void btnImportFromExcel_Click(object sender, EventArgs e)
{
try
{
openFileDialog1.ShowDialog();
string filename = openFileDialog1.FileName;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkBook = xlApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 2;
BookDAL bookDAL = new BookDAL();
while (xlWorkSheet.get_Range("A" + i, "A" + i).Value2 != null)
{
Book book = new Book();
book.BookName = xlWorkSheet.get_Range("A" + i, "A" + i).Value2.ToString();
book.PublisherName = xlWorkSheet.get_Range("B" + i, "B" + i).Value2.ToString();
book.Unit = xlWorkSheet.get_Range("C" + i, "C" + i).Value2.ToString();
book.Price = int.Parse(xlWorkSheet.get_Range("D" + i, "D" + i).Value2.ToString());
book.ISBNBook = xlWorkSheet.get_Range("E" + i, "E" + i).Value2.ToString();
i++;
Book book1 = bookDAL.GetBookbyISBN(book.ISBNBook);
if (book1 == null)
{
bookDAL.CreateBook(book);
}
else if (book1 != book)
{
if (MessageBox.Show("Bạn có cập nhật " + book1.BookName + " - " + book1.PublisherName + " - " + book1.Price + " thành " + book.BookName + " - " + book.PublisherName + " - " + book.Price, "Xác nhận", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
bookDAL.UpdateBook(book1);
}
}
}
MessageBox.Show("Thêm sách thành công");
LoadAllData();
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch (Exception ex)
{
logger.MyLogFile(DateTime.Now.ToString(), "' Error '" + ex.Message + "'");
}
}
//Use in ViewStore
public Store GetBookStorebyISBN(string ISBNBookStore)
{
string cs = CocBook.Properties.Settings.Default.connectionString;
SqlConnection con = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("Select * from BookStore where ISBN = @ISBN", con);
cmd.Parameters.AddWithValue("ISBN", ISBNBookStore);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
Store store = new Store();
if (sdr.HasRows)
{
sdr.Read();
store.ISBNBook = sdr["ISBN"].ToString();
string ISBN = store.ISBNBook;
store.Quantity = (int)sdr["Quantity"];
BookDAL bookDAL = new BookDAL();
Book book = new Book();
book = bookDAL.GetBookbyISBN(ISBN);
store.BookName = book.BookName;
store.Publisher = book.PublisherName;
store.Unit = book.Unit;
store.Price = book.Price;
return store;
}
con.Close();
return null;
}
