private void cmdimport_Click(object sender, EventArgs e)
        {
            txtmsg.Visible = true;
            string line;
            IList<Product> products = new List<Product>();

            try
            {
                if (!txtpath.Text.Equals(""))
                {

                    if (File.Exists(path))
                    {

                        txtmsg.Text = "���ѧ�Ӣ�������� .........";
                        StreamReader file = null;
                        try
                        {
                            file = new StreamReader(path, Encoding.GetEncoding("windows-874"));
                            int i = 0;
                            Product product = null;
                            ProductService service = new ProductService();
                            int j = 1;
                            int count = 0;
                            int x = 0;
                            while ((line = file.ReadLine()) != null)
                            {
                                if (i != 0)
                                {
                                    string[] words = line.Split('\t');
                                    product = new Product();
                                    product.Code = words[0];
                                    product.BarCode = words[1];
                                    product.Name = words[2];
                                    product.Unit = words[3];
                                    product.Price1 = words[4];
                                    product.Price3 = words[5];
                                    product.Price4 = words[6];
                                    if (words[7] != null && !words[7].Equals(""))
                                    {
                                        product.CreateDate = Convert.ToDateTime(words[7]);
                                    }

                                    this.pBar1.Visible = true;
                                    this.pBar1.Value = x;
                                    x += 10;
                                    if (x > 100) x = 0;

                                    Product _product = service.getByBarCode(product.BarCode);

                                    if (_product == null)
                                    {

                                        bool save = service.saveProduct(product);
                                        if (save)
                                        {
                                            Console.WriteLine("Save Compate :" + product.ID);
                                            txtmsg.Visible = true;
                                            txtmsg.Text = "�Ӣ�������� ���º���� �ӹǹ : " + j + " ��¡��";
                                            j++;
                                        }
                                        else
                                        {
                                            Console.WriteLine("Save Not  Compate :" + product.ID);
                                        }

                                    }
                                    else if (checkdate(_product.CreateDate, product.CreateDate) == false)
                                    {

                                        // update
                                        bool update = service.updateProduct(product);
                                        if (update)
                                        {
                                            Console.WriteLine("update Compate :" + product.ID);
                                            txtmsg.Visible = true;
                                            txtmsg.Text = "�Ӣ�������� ���º���� �ӹǹ : " + j + " ��¡��";
                                            j++;
                                        }
                                        else
                                        {
                                            Console.WriteLine("update Not  Compate :" + product.ID);
                                        }

                                    }
                                    count++;

                                    //products.Add(product);
                                }
                                Console.WriteLine(line);
                                Console.WriteLine(i);
                                i++;

                            }

                            // insert product
                            //ProductService service = new ProductService();

                            //int j = 1;
                            //int count = 0;
                            //int x = 0;
                            //foreach (Product p in products)
                            //{
                            //    this.pBar1.Visible = true;
                            //    this.pBar1.Value = x;
                            //    x += 10;
                            //    if (x > 100) x = 0;

                            //    Product _product = service.getByBarCode(p.BarCode);

                            //    if (_product == null )
                            //    {
                            //        //DateTime cdate = _product.CreateDate;

                            //        bool save = service.saveProduct(p);
                            //        if (save)
                            //        {
                            //            Console.WriteLine("Save Compate :" + p.ID);
                            //            txtmsg.Visible = true;
                            //            txtmsg.Text = "�Ӣ�������� ���º���� �ӹǹ : " + j + " ��¡��";
                            //            j++;
                            //        }
                            //        else
                            //        {
                            //            Console.WriteLine("Save Not  Compate :" + p.ID);
                            //        }

                            //    }
                            //    else if (checkdate(_product.CreateDate, p.CreateDate) == false) {

                            //        // update
                            //        bool update = service.updateProduct(p);
                            //        if (update)
                            //        {
                            //            Console.WriteLine("update Compate :" + p.ID);
                            //            txtmsg.Visible = true;
                            //            txtmsg.Text = "�Ӣ�������� ���º���� �ӹǹ : " + j + " ��¡��";
                            //            j++;
                            //        }
                            //        else
                            //        {
                            //            Console.WriteLine("update Not  Compate :" + p.ID);
                            //        }

                            //    }
                            //    count++;

                            //}

                            if (count == products.Count)
                            {
                                this.pBar1.Value = 0;
                                this.pBar1.Visible = false;
                            }
                            else if (count == 0)
                            {
                                this.pBar1.Value = 0;
                                this.pBar1.Visible = false;
                            }

                            txtmsg.Text = "�Ӣ�������� ������  : " + (j - 1) + " ��¡��";

                        }
                        finally
                        {
                            if (file != null)
                                file.Close();
                        }
                    }
                    else {

                        txtmsg.Text = "�к���辺�����س���͡!!!";
                    }

                }
                else {

                    MessageBox.Show("��س����͡�������Ѻ�Ӣ�������� !!! ", "����͹");

                }

            }
            catch (Exception ex)
            {
                txtmsg.Visible = true;
                txtmsg.Text = "�������ö�Ӣ���������� ���ͧ�ҡ " + ex.Message;

            }
        }
        public bool updateProduct(Product product)
        {
            bool result = false;
            try
            {

                Conn = OpenConn();

                sb = new StringBuilder();

                sb.Append(" update Products ");
                sb.Append(" set Name ='" + product.Name + "',");
                sb.Append(" Code ='" + product.Code + "',");
                sb.Append(" BarCode ='" + product.BarCode  + "',");
                sb.Append(" Unit ='" + product.Unit  + "',");
                sb.Append(" Price1 ='" + product.Price1  + "',");
                sb.Append(" Price3 ='" + product.Price3 + "',");
                sb.Append(" Price4 ='" + product.Price4 + "',");
                sb.Append(" CreateDate ='" + DatetimeUtil.convertToDateThai2(product.CreateDate.ToString("dd/MM/yyyy")) + "'");
                sb.Append(" where BarCode ='" + product.BarCode + "'");

                string sqlAdd;
                sqlAdd = sb.ToString();

                tr = Conn.BeginTransaction();
                com = new SqlCeCommand();
                com.CommandText = sqlAdd;
                com.CommandType = CommandType.Text;
                com.Connection = Conn;
                com.Transaction = tr;
                com.Parameters.Clear();
                com.ExecuteNonQuery();

                tr.Commit();
                result = true;

            }
            catch (Exception ex)
            {

                tr.Rollback();
                Conn.Close();
                result = false;
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Conn.Close();
            }

            return result;
        }
        public bool saveProduct(Product product)
        {
            bool result = false;
            try
            {

                Conn = OpenConn();

                sb = new StringBuilder();
                sb.Append("INSERT INTO Products(Name,Code,BarCode,Unit,Price1,Price3,Price4,CreateDate)");
                sb.Append(" VALUES (@Name,@Code,@BarCode,@Unit,@Price1,@Price3,@Price4,@CreateDate)");

                string sqlAdd;
                sqlAdd = sb.ToString();

                tr = Conn.BeginTransaction();
                com = new SqlCeCommand();
                com.CommandText = sqlAdd;
                com.CommandType = CommandType.Text;
                com.Connection = Conn;
                com.Transaction = tr;
                com.Parameters.Clear();
                com.Parameters.Add("@Name", SqlDbType.NVarChar).Value = product.Name;
                com.Parameters.Add("@Code", SqlDbType.NVarChar).Value = product.Code;
                com.Parameters.Add("@BarCode", SqlDbType.NVarChar).Value = product.BarCode;
                com.Parameters.Add("@Unit", SqlDbType.NVarChar).Value = product.Unit;
                com.Parameters.Add("@Price1", SqlDbType.NVarChar).Value = product.Price1;
                com.Parameters.Add("@Price3", SqlDbType.NVarChar).Value = product.Price3;
                com.Parameters.Add("@Price4", SqlDbType.NVarChar).Value = product.Price4;
                com.Parameters.Add("@CreateDate", SqlDbType.NVarChar).Value = product.CreateDate;
                com.ExecuteNonQuery();

                tr.Commit();
                result = true;

            }
            catch (Exception ex)
            {

                tr.Rollback();
                Conn.Close();
                result = false;
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Conn.Close();
            }

            return result;
        }
        public IList<Product> searchByProductName(string _pname)
        {
            IList<Product> products = new List<Product>();
            Product product  = null;
            try
            {

                Conn = OpenConn();
                sb = new StringBuilder();
                sb.Append(" select ID,Name,Code,BarCode,Unit from Products p ");
                sb.Append(" where p.Name like '%" + _pname + "_%'");
                string sql = "";
                sql = sb.ToString();

                com = new SqlCeCommand();
                com.Connection = Conn;
                com.CommandText = sql;
                dr = com.ExecuteReader();
                {

                    DataTable dt = new DataTable();
                    dt.Load(dr);
                    int i = 1;
                    foreach (DataRow ds in dt.Rows)
                    {
                        product = new Product();
                        product.Index = i;
                        product.ID = Convert.ToInt32(ds[0].ToString());
                        product.Name = Convert.ToString(ds[1].ToString());
                        product.Code = Convert.ToString(ds[2].ToString());
                        product.BarCode = Convert.ToString(ds[3].ToString());
                        product.Unit = Convert.ToString(ds[4].ToString());
                        products.Add(product);
                        i++;
                    }

                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Conn.Close();
            }
            finally
            {
                Conn.Close();
            }

            return products;
        }
        // getById
        public Product getById(object _id)
        {
            Product product = null;

            try
            {
                Conn = OpenConn();
                sb = new StringBuilder();
                sb.Append("select ID,Name,Code,BarCode,Unit,CreateDate from Products p where p.ID=" + Convert.ToInt32(_id));
                string sql = "";
                sql = sb.ToString();

                com = new SqlCeCommand();
                com.Connection = Conn;
                com.CommandText = sql;
                dr = com.ExecuteReader();
                {

                    DataTable dt = new DataTable();
                    dt.Load(dr);

                    foreach (DataRow ds in dt.Rows)
                    {
                        product = new Product();
                        product.ID = Convert.ToInt32(ds[0].ToString());
                        product.Name = Convert.ToString(ds[1].ToString());
                        product.Code = Convert.ToString(ds[2].ToString());
                        product.BarCode = Convert.ToString(ds[3].ToString());
                        product.Unit= Convert.ToString(ds[4].ToString());
                        product.CreateDate  = Convert.ToDateTime(ds[5].ToString());

                    }

                }
                dr.Close();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Conn.Close();
            }
            finally {
                Conn.Close();
            }

            return product;
        }