private bool load_file(object sender, DoWorkEventArgs e, out string msg, int emt)
{
msg = null;
try
{
int i = 0;
int cnt = data.Count;
STEmitent st = get_emt(emt);
string connectionstring = st.c_string;
SqlConnection connect = new SqlConnection(connectionstring);
connect.Open();
if (connect.State != ConnectionState.Open)
{
msg = string.Format("No connection to DB. CS: {0}", connectionstring); return(false);
}
backgroundWorker1.ReportProgress(0);
string query = "UPDATE Contract SET CNSIPartCode=@1 WHERE ContrCode=@2";
SqlCommand cmd;
foreach (STRowUpdateContract item in data)
{
cmd = new SqlCommand(query, connect);
cmd.Parameters.Add(crp(SqlDbType.Text, "@1", item.mdmcode, false));
cmd.Parameters.Add(crp(SqlDbType.Int, "@2", item.contrcode, false));
cmd.ExecuteNonQuery();
i += 1;
double d = ((i * 1.0) / cnt) * 100;
backgroundWorker1.ReportProgress((int)d);
}
}
catch (Exception ex)
{
msg = ex.Message;
log.LogLine("Neither record was written to database.");
return(false);
}
return(true);
}
private STEmitent get_emt(int emt)
{
STEmitent ret = new STEmitent();
if (lst.Count <= 0)
{
return(ret);
}
var results = from myRow in lst.AsEnumerable()
where myRow.code == emt
select myRow;
foreach (var vr in results)
{
ret = vr;
}
return(ret);
}
private STEmitent get_emt(string emtname)
{
STEmitent ret = new STEmitent();
if (string.IsNullOrEmpty(emtname))
{
return(ret);
}
if (lst.Count <= 0)
{
return(ret);
}
var results = from myRow in lst.AsEnumerable()
where myRow.name == emtname
select myRow;
foreach (var vr in results)
{
ret = vr;
}
return(ret);
}
private void StartProc(object sender, DoWorkEventArgs e)
{
using (OracleConnection connection = new OracleConnection(config.connectionstring))
{
string msg = null;
List <STEmitent> lst_emt = new List <STEmitent>();
List <STRowRcpData> data;
int cnt_r, cnt_b;
try
{
connection.Open();
dtBegin = new DateTime(dateTimePickerDTB.Value.Year, dateTimePickerDTB.Value.Month, dateTimePickerDTB.Value.Day, 0, 0, 0, 0);
dtEnd = new DateTime(dateTimePickerDTE.Value.Year, dateTimePickerDTE.Value.Month, dateTimePickerDTE.Value.Day, 23, 59, 59, 0);
for (int i = 0; i <= (checkedListBoxEmt.Items.Count - 1); i++)
{
if (checkedListBoxEmt.GetItemChecked(i))
{
string emtname = checkedListBoxEmt.Items[i].ToString();
STEmitent st = get_emt(emtname);
if (st.code > 0)
{
lst_emt.Add(st);
}
}
}
if (lst_emt.Count > 0)
{
log.LogLine(string.Format("-------------------------", emtname));
log.LogLine(string.Format("Timespan: {0}-{1} ", dtBegin.ToString("dd-MM-yyyy"), dtEnd.ToString("dd-MM-yyyy")));
log.LogLine(string.Format("-------------------------", emtname));
foreach (STEmitent emt in lst_emt)
{
emtname = emt.name;
log.LogLine(string.Format("{0} start processing...", emtname));
if (backgroundWorker1.CancellationPending)
{
e.Cancel = true;
backgroundWorker1.ReportProgress(0);
return;
}
backgroundWorker1.ReportProgress(0);
data = new List <STRowRcpData>();
if (!GetData(emt.c_string, out data, out msg))
{
log.LogLine(msg); continue;
}
log.LogLine(string.Format("{0} rows prepare to writing", data.Count));
cnt_r = 0;
cnt_b = 0;
if (data.Count > 0)
{
// удаляем период
delete_data(emt.code, connection);
int cnt = data.Count;
int pc = 0;
int i = 0;
foreach (STRowRcpData item in data)
{
if (backgroundWorker1.CancellationPending)
{
e.Cancel = true;
backgroundWorker1.ReportProgress(0);
return;
}
if (!SetData(item, out msg, connection))
{
cnt_b++; log.LogLine(msg);
}
else
{
cnt_r++;
}
i += 1;
double d = ((i * 1.0) / cnt) * 100;
backgroundWorker1.ReportProgress((int)d);
}
}
log.LogLine(string.Format("{0}/{1} rows added/missed", cnt_r, cnt_b));
log.LogLine(string.Format("{0} end processing...", emtname));
}
}
connection.Close();
}
catch (Exception ex) { MessageBox.Show(ex.TargetSite + " " + ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error); }
}
}
public List <STEmitent> GetData(int trcode)
{
List <STEmitent> list_data = new List <STEmitent>();
STEmitent item;
OracleConnection connet = new OracleConnection();
try
{
connet = new OracleConnection(config.connectionstring);
connet.Open();
if (connet.State != ConnectionState.Open)
{
log.LogLine(string.Format("No connection to DB Oracle. CS: {0}", config.connectionstring)); return(list_data);
}
string query = string.Format("SELECT EmtCode,EmtName,Address,Phone,PostIndex,E_mail,BankCode,Account, " +
"DeptCode,Shifr_OKPO,Shifr_UNN,Shifr_OKED,TransCode,PeriodBeg,PeriodEnd,DirectName," +
"BuchName,CBSCode,ConnectionString,EmtShortName FROM {0}.Emitent WHERE TransCode={1} ORDER BY EmtCode",
"RCD", trcode);
OracleCommand cmd = new OracleCommand(query, connet);
OracleDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
item = new STEmitent();
item.code = reader.GetInt32(0);
item.name = reader["EmtName"].ToString().Trim();
item.address = reader["Address"].ToString().Trim();
item.phone = reader["Phone"].ToString().Trim();
item.postindex = reader["PostIndex"].ToString().Trim();
item.email = reader["E_mail"].ToString().Trim();
if (!reader.IsDBNull(6))
{
item.bankcode = reader.GetInt32(6);
}
item.account = reader["Account"].ToString().Trim();
if (!reader.IsDBNull(8))
{
item.deptcode = reader.GetInt32(8);
}
item.OKPO = reader["Shifr_OKPO"].ToString().Trim();
item.UNN = reader["Shifr_UNN"].ToString().Trim();
item.OKED = reader["Shifr_OKED"].ToString().Trim();
if (!reader.IsDBNull(12))
{
item.transcode = reader.GetInt32(12);
}
if (!reader.IsDBNull(13))
{
item.periodbegin = reader.GetDateTime(13);
}
if (!reader.IsDBNull(14))
{
item.periodend = reader.GetDateTime(14);
}
item.directname = reader["DirectName"].ToString().Trim();
item.buchname = reader["BuchName"].ToString().Trim();
if (!reader.IsDBNull(17))
{
item.CBScode = reader.GetInt32(17);
}
if (!reader.IsDBNull(18))
{
item.c_string = reader.GetString(18);
}
else
{
item.c_string = null;
}
if (!reader.IsDBNull(19))
{
item.shortname = reader.GetString(19);
}
else
{
item.shortname = null;
}
list_data.Add(item);
}
}
reader.Dispose();
}
catch (Exception ex) { log.LogLine(ex.Message); if (connet.State == ConnectionState.Open)
{
connet.Close();
}
}
return(list_data);
}
