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