private static void Test_Command_Cancel()
{
using (OleDbCommand cmd = new OleDbCommand("select * from code;", conn))
{
cmd.ExecuteReader();
cmd.Cancel();
OleDbCommand cmd_clone = cmd.Clone();
Assert.AreEqual(cmd.ToString(), cmd_clone.ToString());
}
}
/// <summary>
/// Ищет дубликат в записи с параметрами в param
/// </summary>
/// <param name="table">Целевая таблица</param>
/// <param name="con"></param>
/// <param name="param"></param>
/// <param name="primary_key"></param>
/// <param name="id">Главный ключ записи дубликата</param>
/// <returns>
/// id - есть ли дубликат
/// </returns>
public static Dictionary<int, int> find_all_duplicates(string table, DataTable source_t, OleDbConnection target_con, OleDbCommand select_com, string primary_key)
{
Dictionary<int, int> old_new_keys = new Dictionary<int, int>();
OleDbCommand select_target = select_com.Clone();
select_target.Connection = target_con;
OleDbDataReader reader = select_target.ExecuteReader();
DataTable target_t = new DataTable();
target_t.Load(reader);
foreach (DataRow source_row in source_t.Rows)
{
foreach (DataRow target_row in target_t.Rows)
{
bool rows_equal = true;
foreach (DataColumn col in source_t.Columns)
{
if (col.ColumnName != primary_key)
{
if (!source_row[col.ColumnName].Equals(target_row[col.ColumnName]))
{
rows_equal = false;
break;
}
}
}
if (rows_equal)
{
old_new_keys[(int)source_row[primary_key]] = (int)target_row[primary_key];
break;
}
}
}
return old_new_keys;
}
private void updateTable(string tableName, string data)
{
String query = "SELECT * FROM " + tableName;
OleDbDataAdapter adp = new OleDbDataAdapter(query, con);
OleDbCommand insetCommand = new OleDbCommand();
insetCommand.Connection = con;
insetCommand.CommandType = CommandType.Text;
insetCommand.CommandText = "INSERT INTO " + tableName + " (Id, national_id, station, work_day, shift, barcode_time, check1, check2, check3, check4, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
foreach (string col in new String[] { "Id", "national_id", "station", "work_day", "shift", "barcode_time", "check1", "check2", "check3", "check4", "notes" })
{
OleDbParameter p = new OleDbParameter();
p.SourceColumn = col;
insetCommand.Parameters.Add(p);
}
adp.InsertCommand = insetCommand;
OleDbCommand updateCommand = insetCommand.Clone();
updateCommand.CommandText = "UPDATE " + tableName + " SET national_id=?, station=?, work_day=?, shift=?, barcode_time=?, check1=?, check2=?, check3=?, check4=?, notes=? WHERE Id=?";
OleDbParameter idParam = updateCommand.Parameters[0];
updateCommand.Parameters.Remove(idParam);
updateCommand.Parameters.Add(idParam);
adp.UpdateCommand = updateCommand;
DataSet dsXLS = new DataSet();
adp.Fill(dsXLS);
DataTable table = dsXLS.Tables[0];
table.PrimaryKey = new DataColumn[] { table.Columns["id"] };
string[] records = data.Split(new string[] { "\n" }, StringSplitOptions.None);
foreach (string record in records)
{
string[] fields = record.Split(',');
string id = fields[0];
string[] id_fields = id.Split('_');
string nationalId = id_fields[0];
string station = id_fields[1];
string work_day = id_fields[3];
string shift = id_fields[4];
string barcod_time = fields[1];
string check1 = fields[2];
string check2 = fields[3];
string check3 = fields[4];
string check4 = fields[5];
string notes = fields[6];
DataRow[] found = table.Select("Id='" + id + "'");
DataRow row;
if (found.Length > 0)
row = found[0];
else
{
row = table.NewRow();
row["Id"] = id;
table.Rows.Add(row);
}
row["national_id"] = nationalId;
row["station"] = station;
row["work_day"] = work_day;
row["shift"] = shift;
row["barcode_time"] = barcod_time;
row["check1"] = check1;
row["check2"] = check2;
row["check3"] = check3;
row["check4"] = check4;
row["notes"] = notes;
}
adp.Update(dsXLS);
}
private void SaveLoginTime()
{
OleDbConnection con = MCTEFollowUp.Connection;
OleDbCommand cmd = new OleDbCommand(string.Format("INSERT INTO userin (UserID, datein) VALUES ({0}, Date())", TheSessionsTEFollowUp.UserID), con);
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (OleDbException ex)
{
}
cmd.Clone();
}
public DataTable ContactList(string tbl, string legal)
{
DataTable dt = new DataTable(tbl);
if (tLegal == legal.Trim() && IsDaysLeft())
{
string sqlStr = "SELECT * FROM " + tbl;
try
{
OleDbConnection cnn = new OleDbConnection(Base.cnnStr);
cnn.Open();
OleDbCommand cmd = new OleDbCommand(sqlStr, cnn);
OleDbDataReader drr = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataRow dr;
dt.Columns.Add("mailbox", Type.GetType("System.String"));
dt.Columns.Add("rname", Type.GetType("System.String"));
while (drr.Read())
{
dr = dt.NewRow();
dr["mailbox"] = EncDec.Decrypt(drr["mailbox"].ToString(), Base.hashKey);
dr["rname"] = EncDec.Decrypt(drr["rname"].ToString(), Base.hashKey);
dt.Rows.Add(dr);
}
dt.AcceptChanges();
tbl = null;
sqlStr = null;
cnn.Close();
drr.Close();
cmd.Clone();
drr.Dispose();
cmd.Dispose();
cnn.Dispose();
ds.Dispose();
dr = null;
ds = null;
drr = null;
cmd = null;
cnn = null;
}
catch
{
}
finally
{
}
}
return dt;
}
