public ArrayList createTable()
{
ArrayList values = new ArrayList();
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "select Crotalii.CodBare, Crotalii.Crotalia, Crotalii.Nume, Crotalii.Rasa, Crotalii.DataNasterii, Crotalii.FermaId, Ferme_CCL.Nume as NumeFerma from Crotalii, Ferme_CCL WHERE Crotalii.FermaId = Ferme_CCL.ID ";
if (Crotalia.Text.Trim().Length > 0)
{
cmd.CommandText += " AND Crotalii.Crotalia Like '%" + Crotalia.Text.Trim() + "%'";
}
if (Ferma.Text.Trim().Length > 0)
{
cmd.CommandText += " AND Crotalii.CodBare Like '%" + Ferma.Text.Trim() + "%'";
}
cmd.CommandText += " ORDER BY Crotalii.CodBare, Crotalii.Crotalia";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
int nrrec = 0;
while (reader.Read())
{
Crotalia kr = new Crotalia();
kr.codbare = Convert.ToString(reader["Codbare"]);
kr.crotalia = Convert.ToString(reader["crotalia"]);
kr.nume = Convert.ToString(reader["Nume"]);
kr.rasa = Convert.ToString(reader["Rasa"]);
kr.ferma = Convert.ToString(reader["NumeFerma"]);
kr.fermaid = Convert.ToString(reader["FermaId"]);
kr.datanasterii = Convert.ToString(reader["DataNasterii"]);
values.Add(kr);
// add to values
nrrec++;
}
lcount.Text = nrrec + " crotalii";
reader.Close();
cnn.Close();
return(values);
}
public ArrayList createTable()
{
ArrayList values = new ArrayList();
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "select Crotalii.CodBare, Crotalii.Crotalia, Crotalii.Nume, Crotalii.Rasa, Crotalii.DataNasterii, Crotalii.FermaId, Ferme_CCL.Nume as NumeFerma from Crotalii, Ferme_CCL WHERE Crotalii.FermaId = Ferme_CCL.ID ";
if (Crotalia.Text.Trim().Length > 0)
cmd.CommandText += " AND Crotalii.Crotalia Like '%" + Crotalia.Text.Trim() + "%'";
if (Ferma.Text.Trim().Length > 0)
cmd.CommandText += " AND Crotalii.CodBare Like '%" + Ferma.Text.Trim() + "%'";
cmd.CommandText += " ORDER BY Crotalii.CodBare, Crotalii.Crotalia";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
int nrrec = 0;
while (reader.Read())
{
Crotalia kr = new Crotalia();
kr.codbare = Convert.ToString(reader["Codbare"]);
kr.crotalia = Convert.ToString(reader["crotalia"]);
kr.nume = Convert.ToString(reader["Nume"]);
kr.rasa = Convert.ToString(reader["Rasa"]);
kr.ferma = Convert.ToString(reader["NumeFerma"]);
kr.fermaid = Convert.ToString(reader["FermaId"]);
kr.datanasterii = Convert.ToString(reader["DataNasterii"]);
values.Add(kr);
// add to values
nrrec++;
}
lcount.Text = nrrec + " crotalii";
reader.Close();
cnn.Close();
return values;
}
public void UpdateCrotaliiBV(string filepath)
{
Crotalia kr = new Crotalia();
string strConn;
int posOfSlash = filepath.LastIndexOf(@"\");
string filelogpath = filepath.Substring(0, filepath.Length - 4) + "_" + DateTime.Now.ToString("ddMMyyyy") +
".txt";
string logfilename = filelogpath.Substring(posOfSlash + 1);
filelogpath = filelogpath.Replace(" ", "_");
logfilename = logfilename.Replace(" ", "_");
string dirServer = StaticDataHelper.SettingsManager.CaleFizicaServer;
string filename = filepath.Substring(posOfSlash + 1);
loggerCrotalii.Info("Start Actualizare crotalii");
filepath = filepath.Replace(@"\", @"\\");
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filepath + ";" +
"Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
OleDbConnection cn;
DataTable dtTables;
cn = new OleDbConnection(strConn);
cn.Open();
dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
for (int i = 0; i < dtTables.Rows.Count; i++)
{
string tblstr = dtTables.Rows[i]["TABLE_NAME"].ToString();
OleDbDataAdapter myCommand = new OleDbDataAdapter();
myCommand.SelectCommand = new OleDbCommand("SELECT * FROM [" + tblstr + "]", cn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
DataTable table;
table = myDataSet.Tables[0];
cn.Close();
string crot = "";
string codbare = "";
int header = 0;
string rasa = "";
string datanasterii = "";
int fermaid = 0;
int asocid = 0;
string nume = "";
try
{
foreach (DataRow row in table.Rows)
{
header++;
if (header > 1 && row[1].ToString().Trim() != "")
{
codbare = row[1].ToString().Trim();
crot = row[0].ToString().Trim();
if (crot == null || crot.Trim().Length == 0)
{
crot = tblstr.Replace("$", "_") + codbare;
}
try
{
fermaid = Int32.Parse(row[2].ToString().Trim());
asocid = Int32.Parse(row[3].ToString().Trim());
}
catch (Exception ex)
{
loggerCrotalii.Error(string.Format("UpdateCrotaliiBV|ERROR{0}", ex.Message));
}
rasa = row[4].ToString().Trim();
datanasterii = row[5].ToString().Trim();
nume = row[6].ToString().Trim();
try
{
if (datanasterii.Length > 4)
{
DateTime dt = DateTime.Parse(datanasterii);
datanasterii = dt.ToString("dd/MM/yyyy");
}
}
catch (Exception ex)
{
loggerCrotalii.Error(crot + " " + datanasterii + ex.Message);
}
int errCode = 0;
// test crot
string query = "SELECT * FROM CROTALII WHERE Crotalia ='" + crot + "'";
if (ItemExist(query))
errCode = 1;
// verif. dupa cod ferma
query = "SELECT Count(*) FROM FERME_CCL Where ID = " + fermaid;
if (!ItemExist(query))
errCode = 2;
query = "SELECT Count(*) FROM FABRICI WHERE ID = " + asocid;
if (!ItemExist(query))
errCode = 3;
if (errCode == 0)
{
query = "SELECT * FROM CROTALII WHERE FermaId = " + fermaid + " AND CodBare= '" + codbare +
"'";
if (!ItemExist(query))
InsertCrot(codbare, crot, rasa, datanasterii, nume, fermaid);
else
UpdateCrot(codbare, crot, rasa, datanasterii, nume, fermaid);
}
else
{
string errMess = "";
switch (errCode)
{
case 1:
errMess = "Crotalia " + crot + " exista deja !";
break;
case 2:
errMess = "Ferma " + fermaid + " nu exista !";
break;
case 3:
errMess = "Asociatia " + asocid + " nu exista !";
break;
default:
break;
}
loggerCrotalii.Error(errMess);
}
}
}
}
catch (Exception ex)
{
loggerCrotalii.Error(crot + " : " + ex.Message);
}
} //end update table
}
