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