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