/// <summary> /// Save the changed values in the table /// </summary> /// <param name="sender">contains the sender of the event, so if you had one method bound to multiple controls, you can distinguish them.</param> /// <param name="e">refers to the event arguments for the used event, they usually come in the form of properties/functions/methods that get to be available on it.</param> private void btnSave_Click(object sender, EventArgs e) { try { this.Validate(); if (dgvList.IsCurrentRowDirty || dgvList.IsCurrentCellDirty) { dgvList.CommitEdit(DataGridViewDataErrorContexts.Commit); dgvList.EndEdit(); } string tableName = Ribbon.AppVariables.TableName; //string sql = "SELECT * FROM @tableName"; string sql = "SELECT * FROM " + tableName; SqlCeConnection cn = new SqlCeConnection(Data.Connection()); SqlCeCommandBuilder scb = default(SqlCeCommandBuilder); SqlCeDataAdapter sda = new SqlCeDataAdapter(sql, cn); //sda.SelectCommand.Parameters.AddWithValue("@tableName", tableName); //sda.SelectCommand.Parameters.AddWithValue("@tableName", System.Data.SqlDbType.NVarChar).Value = tableName; //sda.SelectCommand.Parameters.Add(new SqlCeParameter //{ // ParameterName = "@tableName" // , Value = tableName // , SqlDbType = System.Data.SqlDbType.NVarChar // , Size = 50 //}); sda.TableMappings.Add("Table", tableName); scb = new SqlCeCommandBuilder(sda); switch (tableName) { case "TableAlias": sda.Update(Data.TableAliasTable); Data.CreateTableAliasTable(); break; case "DateFormat": sda.Update(Data.DateFormatTable); Data.CreateDateFormatTable(); break; } Ribbon.ribbonref.InvalidateRibbon(); } catch (Exception ex) { ErrorHandler.DisplayMessage(ex); } }
private void Save_Click(object sender, EventArgs e) { // Recover the datatable from the datasource of the grid DataTable t = DoctorView.DataSource as DataTable; a.Update(t); }
/// <summary> /// Die Daten kopieren. Hierbei die neue Struktur beachten /// </summary> /// <param name="tableName"></param> /// <param name="columns"></param> private void CopyData(string tableName, List <Column> columns) { string cmd = string.Format("SELECT * from [{0}]", tableName); OleDbCommand comm = new OleDbCommand(cmd, hdbConnection); OleDbDataReader reader = comm.ExecuteReader(); SqlCeDataAdapter adap = new SqlCeDataAdapter(cmd, sdfConnection); adap.InsertCommand = CreateInsertCommand(tableName, columns); adap.InsertCommand.Connection = sdfConnection; DataTable dt = new DataTable(); adap.Fill(dt); while (reader.Read()) { DataRow row = dt.NewRow(); List <object> values = new List <object>(); for (int i = 0; i < reader.FieldCount; i++) { values.Add(reader.GetValue(i)); } row.ItemArray = values.ToArray(); dt.Rows.Add(row); } adap.Update(dt); }
public void FillData() { try { using (SqlCeConnection con = new SqlCeConnection(conString)) { con.Open(); using (SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT * FROM table1", con)) { DataTable t = new DataTable(); a.Fill(t); dataGridView1.DataSource = t; a.Update(t); } con.Close(); } MainWindow m = new MainWindow(); ((DataTable)this.dataGridView1.DataSource).DefaultView.RowFilter = "Equip =" + "'" + m.TS3 + "'"; // ((DataTable)this.dataGridView1.DataSource).DefaultView.RowFilter = "Equip =" + "'" + m.toolStripStatusLabel3.Text.ToString() + "'"; // ((DataTable)this.dataGridView1.DataSource).DefaultView.RowFilter = "Equip =" + "'" + textBox13.Text.ToString() + "'"; } catch (Exception ex) { MainWindow m = new MainWindow(); m.Log("FillData Error" + ex.ToString()); } }
/// <summary> /// Returns all the members assigned to the specified personal trainer /// </summary> /// <param name="trainer_id"></param> /// <returns></returns> public static DataTable GetMembersByPersonalTrainer(int trainer_id) { DataTable dataset; using (SqlCeConnection con = DB.GetSqlCeConnection()) { String sql = "SELECT Members.Id, Members.CardNumber, Members.LastName, Members.FirstName, Members.HomePhone, Members.CellPhone, Members.Email, " + "Trainers.FirstName + ' ' + Trainers.LastName AS PersonalTrainer " + "FROM Members INNER JOIN " + "Trainers ON Members.PersonalTrainer = Trainers.Id " + "WHERE (Trainers.Id = @trainer_id) "; SqlCeCommand cmd = new SqlCeCommand(sql, con); cmd.Parameters.AddWithValue("@trainer_id", trainer_id); SqlCeDataAdapter sda = new SqlCeDataAdapter(); sda.SelectCommand = cmd; dataset = new DataTable(); sda.Fill(dataset); sda.Update(dataset); return(dataset); } }
public static void DeleteFolderFromDB(string folderPath, string dbFilePath) { using (SqlCeConnection con = CreateConnection(dbFilePath)) { con.Open(); SqlCeDataAdapter da = new SqlCeDataAdapter("Select * FROM Folders", con); da.DeleteCommand = new SqlCeCommand( "DELETE FROM Folders WHERE id = @original_id " + "and name = @original_name"); da.DeleteCommand.Parameters.Add("@original_id", SqlDbType.Int, 0, "id"); da.DeleteCommand.Parameters.Add("@original_name", SqlDbType.NVarChar, 255, "name"); da.DeleteCommand.Connection = con; DataSet ds = new DataSet("Folder"); DataTable dt = new DataTable("Folders"); dt.Columns.Add(new DataColumn("id", typeof(int))); dt.Columns.Add(new DataColumn("name", typeof(string))); ds.Tables.Add(dt); da.Fill(ds, "Folders"); int ind = -1; for (int i = 0; i < folderList.Count; i++) { if (folderList[i] == folderPath.Replace("'", "`")) { ind = i; break; } } string folderid = ds.Tables["Folders"].Rows[ind]["id"].ToString(); dt.Rows[ind].Delete(); da.Update(ds, "Folders"); string sql = "DELETE FROM Songs WHERE folder_id = " + folderid; SqlCeCommand com = new SqlCeCommand(sql, con); com.ExecuteNonQuery(); } }
//This is kinda dirty. Be interesting to time delete / recreate all versus, seek - update or insert. //TODO: Wrapp a stopwatch around this public void SaveLibrary(NavigatableCollection <Track> library) { //Delete the library first DeleteLibrary(); //TODO: Repopulate it DataTable dt = new DataTable(); using (var objConn = new SqlCeConnection(ConnectionString)) { objConn.Open(); var adapter = new SqlCeDataAdapter("Select Id, TrackName, TrackPathName, Artist, TrackNumber, Album From Track", objConn); var objCommandBuilder = new SqlCeCommandBuilder(adapter); adapter.Fill(dt); foreach (var track in library) { dt.Rows.Add(new object[] { track.Id, track.TrackName, track.TrackPathName, track.Artist, null, track.Album }); //We're not currently parsing the track number } adapter.Update(dt); objConn.Close(); } }
/// <summary> /// Сохранить каналы в БД /// </summary> private bool UpdateCnls(DataTable dataTable, string descr) { int updRows = 0; int errRows = 0; DataRow[] rowsInError = null; SqlCeDataAdapter sqlAdapter = dataTable.ExtendedProperties["DataAdapter"] as SqlCeDataAdapter; updRows = sqlAdapter.Update(dataTable); if (dataTable.HasErrors) { rowsInError = dataTable.GetErrors(); errRows = rowsInError.Length; } if (errRows == 0) { writer.WriteLine(string.Format(descr, updRows)); } else { writer.WriteLine(string.Format(descr, updRows) + ". " + string.Format(AppPhrases.ErrorsCount, errRows)); foreach (DataRow row in rowsInError) { writer.WriteLine(string.Format(AppPhrases.CnlError, row[0], row.RowError)); } } return(errRows == 0); }
/// <summary> /// Executes the respective command for each inserted, updated, or deleted row in the DataSet. /// </summary> /// <remarks> /// e.g.: /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); /// </remarks> /// <param name="insertCommand">A valid SQL statement to insert new records into the data source</param> /// <param name="deleteCommand">A valid SQL statement to delete records from the data source</param> /// <param name="updateCommand">A valid SQL statement used to update records in the data source</param> /// <param name="dataSet">The DataSet used to update the data source</param> /// <param name="tableName">The DataTable used to update the data source.</param> public static void UpdateDataset(SqlCeCommand insertCommand, SqlCeCommand deleteCommand, SqlCeCommand updateCommand, DataSet dataSet, string tableName) { if (insertCommand == null) { throw new ArgumentNullException("insertCommand"); } if (deleteCommand == null) { throw new ArgumentNullException("deleteCommand"); } if (updateCommand == null) { throw new ArgumentNullException("updateCommand"); } if (string.IsNullOrEmpty(tableName)) { throw new ArgumentNullException("tableName"); } // Create a SqlCeDataAdapter, and dispose of it after we are done using (var dataAdapter = new SqlCeDataAdapter()) { // Set the data adapter commands dataAdapter.UpdateCommand = updateCommand; dataAdapter.InsertCommand = insertCommand; dataAdapter.DeleteCommand = deleteCommand; // Update the dataset changes in the data source dataAdapter.Update(dataSet, tableName); // Commit all the changes made to the DataSet dataSet.AcceptChanges(); } }
private void button1_Click(object sender, EventArgs e) { try { if (sender.Equals(button1) == true) { this.Close(); } else { if (Main.Amatrix.mgt == "") { DataTable table = new DataTable(); table = (DataTable)dgv.DataSource; DataTable table2 = new DataTable(); using (var con = new SqlCeConnection(Properties.Settings.Default.Amdtbse_4ConnectionString)) using (var adapter = new SqlCeDataAdapter("SELECT * FROM prod_warranties", con)) using (new SqlCeCommandBuilder(adapter)) { adapter.Fill(table2); con.Open(); adapter.Update(table); } } else { asql.Save(dtp, "prod_warranties", Main.Amatrix.mgt); } } } catch (Exception erty) { } }
private void button1_Click(object sender, EventArgs e) { object[] param = new object[6]; if (txtName.Text == "") { MessageBox.Show("لطفا نام شخص را وارد نمایید"); return; } if (picFace.ImageLocation == "") { MessageBox.Show("لطفا تصویر شخص را انتخاب نمایید"); return; } if (personTable.Rows.Count >= 1) { param[0] = (int)(personTable.Rows[personTable.Rows.Count - 1].ItemArray[0]) + 1; } else { param[0] = 1; } param[1] = txtName.Text; if (txtAge.Text != "") { param[2] = (int)(float.Parse(txtAge.Text)); } param[3] = rdMale.Checked; //param[4] = picFace.Image; if (picFace.ImageLocation != null && picFace.ImageLocation != "") { param[4] = picFace.ImageLocation; param[5] = System.IO.File.ReadAllBytes(picFace.ImageLocation); } else { string path = System.IO.Path.GetTempFileName(); picFace.Image.Save(path); param[4] = path; param[5] = System.IO.File.ReadAllBytes(path); //System.IO.File.Delete(path); } // param[4] = System.IO.File.ReadAllBytes(picFace.ImageLocation); personTable.Rows.Add(param); sda.Update(personTable); }
private void button2_Click(object sender, EventArgs e) { tb.Clear(); foreach (DataGridViewRow dgvr in dataGridView1.SelectedRows) { if (Main.Amatrix.mgt == "") { SqlCeConnection conn = new SqlCeConnection(signsTableAdapter.Connection.ConnectionString); SqlCeCommand cmd = new SqlCeCommand("", conn); cmd.CommandText = "SELECT * FROM Signs WHERE [Employee First Name] = '" + dgvr.Cells[0].Value + "' AND [Employee Last Name] = '" + dgvr.Cells[1].Value + "' AND DatePart(dd,[Employee Time In]) = DatePart(dd,getdate()) AND DatePart(mm,[Employee Time In]) = DatePart(mm,getdate()) AND DatePart(yy,[Employee Time In]) = DatePart(yy,getdate())"; conn.Open(); SqlCeDataReader dr = cmd.ExecuteReader(); tb.Load(dr); } else { tb = basql.Execute(Main.Amatrix.mgt, "SELECT * FROM Signs WHERE [Employee First Name] = '" + dgvr.Cells[0].Value + "' AND [Employee Last Name] = '" + dgvr.Cells[1].Value + "' AND DatePart(dd,[Employee Time In]) = DatePart(dd,getdate()) AND DatePart(mm,[Employee Time In]) = DatePart(mm,getdate()) AND DatePart(yy,[Employee Time In]) = DatePart(yy,getdate())", "Signs", tb); } dgv.DataSource = tb; foreach (DataGridViewRow dgvrr in dgv.Rows) { if (dgvrr.Index != dgv.Rows.Count - 1) { if (dgvrr.Cells[3].Value == DBNull.Value) { dgvrr.Cells[3].Value = DateTime.Now; Dtp1 = (DateTime)dgvrr.Cells[2].Value; Dtp2 = (DateTime)dgvrr.Cells[3].Value; tsp = (Dtp2 - Dtp1); dgvrr.Cells[4].Value = tsp.Hours.ToString() + ":" + tsp.Minutes.ToString(); } } } //Save dgv.CurrentCell = dgv[0, dgv.Rows.Count - 1]; DataTable table = new DataTable(); table = (DataTable)dgv.DataSource; DataTable table2 = new DataTable(); if (Main.Amatrix.mgt == "") { using (var con = new SqlCeConnection(signsTableAdapter.Connection.ConnectionString)) using (var adapter = new SqlCeDataAdapter("SELECT * FROM Signs", con)) using (new SqlCeCommandBuilder(adapter)) { adapter.Fill(table2); con.Open(); adapter.Update(table); } } else { asql.Save(table, "Signs", Main.Amatrix.mgt); } } this.Close(); }
public int changeRow(int index, IData obj) { int ret = 0; try { DataRowView dr = dataView[index]; dr.BeginEdit(); obj.ThisToRow(dr.Row); dr.EndEdit(); ret = Adapter.Update(dataTable); // Поиск измененного номера записи в dataView по индексному полю } catch (Exception e) { } return(ret); // Возвращает новое положение или -1 если ошибка }
private bool saveData(SqlCeConnection connection) { SqlCeTransaction transaction = null; try { using (SqlCeCommand command = connection.CreateCommand()) { command.CommandText = "Items"; command.CommandType = CommandType.TableDirect; using (SqlCeDataAdapter adapter = new SqlCeDataAdapter(command)) { adapter.InsertCommand = getInsertCommand(connection); adapter.UpdateCommand = getUpdateCommand(connection); adapter.DeleteCommand = getDeleteCommand(connection); adapter.AcceptChangesDuringUpdate = false; DataTable table = (DataTable)itemsGrid.DataSource; transaction = connection.BeginTransaction(); adapter.InsertCommand.Transaction = transaction; adapter.UpdateCommand.Transaction = transaction; adapter.DeleteCommand.Transaction = transaction; adapter.Update(table); transaction.Commit(); table.AcceptChanges(); } } } catch (Exception ex) { if (transaction != null) { transaction.Rollback(); } string message = "An error occurred in saving the data. \n" + "The error text is as follows:\n" + Global.getExceptionText(ex); SystemSounds.Exclamation.Play(); Cursor.Current = Cursors.Default; MessageBox.Show(message, "Error in Saving Data", MessageBoxButtons.OK, MessageBoxIcon.Error); ErrorLogger.LogError(ex); return(false); } finally { if (transaction != null) { transaction.Dispose(); } } return(true); }
internal static void UpdateAdapter(DataSet dataSet, string tableName) { if (dbCon.State == ConnectionState.Closed) { dbCon.Open(); } dataAdapter.Update(dataSet, tableName); dbCon.Close(); }
private void UpdateDataBase() { conn.Open(); SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da); da.Update(ds.Tables[0]); conn.Close(); dirtyData = false; }
private void FormUpdate_Closing(object sender, CancelEventArgs e) { // Force the current modification to complete. this.BindingContext[dtabCategories].EndCurrentEdit(); // Push dataset changes back to database. daptCategories.Update(dsetDB, "Categories"); }
private void updateDatabase() { SqlCeCommand cmd = new SqlCeCommand(CmdString, con); SqlCeDataAdapter sda = new SqlCeDataAdapter(cmd); SqlCeCommandBuilder com = new SqlCeCommandBuilder(sda); DataSet ds = new DataSet(); sda.Update(dt); dt.Clear(); sda.Fill(dt); }
public void SaveTableDataChanges(DataTable tableData) { if (tableData == null) { return; } using (var conn = new SqlCeConnection(ConnectionString)) using (var adapter = new SqlCeDataAdapter("SELECT * FROM " + tableData.TableName, conn)) using (new SqlCeCommandBuilder(adapter)) adapter.Update(tableData); }
private void Save_Button_Click(object sender, RoutedEventArgs e) { using (var cmd = new SqlCeCommand("SELECT * FROM CompanyInfo", database_.GetConnection())) { SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd); SqlCeCommandBuilder builder = new SqlCeCommandBuilder(adapter); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.Update(company_); } company_.AcceptChanges(); DialogResult = true; }
/// <SUMMARY> /// Checks if there is a row with changes and /// writes it to the database /// </SUMMARY> private void UpdateRowToDatabase() { try { if (LastDataRow != null) { if (LastDataRow.RowState == DataRowState.Modified || LastDataRow.RowState == DataRowState.Added || LastDataRow.RowState == DataRowState.Deleted) { DataRow[] rows = new DataRow[1]; rows[0] = LastDataRow; dAdapter.Update(rows); } } } catch (Exception ex) { MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex)); } }
// 保存设置到数据库 private void Save_Settings_Button_Click(object sender, RoutedEventArgs e) { using (var cmd = new SqlCeCommand("SELECT * FROM Settings", database_.GetConnection())) { SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd); SqlCeCommandBuilder builder = new SqlCeCommandBuilder(adapter); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.Update(settings_); } settings_.AcceptChanges(); textBoxMessage.Text = "设置已保存"; }
private void buttonSave_Click(object sender, EventArgs e) { con = new SqlCeConnection(source); con.Open(); cmd = new SqlCeCommand(@"Select * From Persons ", con); adapter = new SqlCeDataAdapter(cmd); SqlCeCommandBuilder CB = new SqlCeCommandBuilder(adapter); adapter.Update(dataSet); MessageBox.Show("Сохранено!"); }
static private DataTable SaveDynamicCollection_Deletes(esDataRequest request) { SqlCeCommand cmd = null; DataTable dataTable = CreateDataTable(request); using (esTransactionScope scope = new esTransactionScope()) { using (SqlCeDataAdapter da = new SqlCeDataAdapter()) { da.AcceptChangesDuringUpdate = false; da.ContinueUpdateOnError = request.ContinueUpdateOnError; try { cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0].ModifiedColumns); esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); DataRow[] singleRow = new DataRow[1]; // Delete each record foreach (esEntitySavePacket packet in request.CollectionSavePacket) { DataRow row = dataTable.NewRow(); dataTable.Rows.Add(row); SetOriginalValues(request, packet, row, true); row.AcceptChanges(); row.Delete(); singleRow[0] = row; da.Update(singleRow); if (row.HasErrors) { request.FireOnError(packet, row.RowError); } dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow } } finally { esTransactionScope.DeEnlist(cmd); } } scope.Complete(); } return(request.Table); }
private void svebtn_Click(object sender, EventArgs e) { if (Main.Amatrix.mgt == "") { try { signsTableAdapter1.Update(bSAMDataSet1); } catch (Exception erty) { //Save dataGridView1.CurrentCell = null; DataTable table = new DataTable(); table = bSAMDataSet1.Signs; DataTable table2 = new DataTable(); using (var con = new SqlCeConnection(signsTableAdapter1.Connection.ConnectionString)) using (var adapter = new SqlCeDataAdapter("SELECT * FROM Signs", con)) using (new SqlCeCommandBuilder(adapter)) { adapter.Fill(table2); con.Open(); adapter.Update(table); } } } else { try { asql.Save(bSAMDataSet1.Signs, "Signs", Main.Amatrix.mgt); } catch (Exception erty) { //Save dataGridView1.CurrentCell = null; DataTable table = new DataTable(); table = bSAMDataSet1.Signs; DataTable table2 = new DataTable(); using (var con = new SqlConnection(Main.Amatrix.mgt)) using (var adapter = new SqlDataAdapter("SELECT * FROM Signs", con)) using (new SqlCommandBuilder(adapter)) { adapter.Fill(table2); con.Open(); adapter.Update(table); } } } }
override public void update(CaseListDataSet.CaseListTableDataTable table) { try { if (m_connection.State == ConnectionState.Closed) { m_connection.Open(); } SqlCeTransaction se = m_connection.BeginTransaction(IsolationLevel.Serializable); try { m_adapter.Update(table); } catch (Exception ex) { m_connection.Close(); m_connection.Open(); se = m_connection.BeginTransaction(IsolationLevel.Serializable); m_adapter.Update(table); } se.Commit(CommitMode.Immediate); } catch (SqlCeException ex) { if (!ex.Message.Contains("duplicate")) { throw; } } catch { throw; } }
private void button1_Click(object sender, EventArgs e) { foreach (DataGridViewRow dgvr in dataGridView1.SelectedRows) { DataRow row; row = bSAMDataSet.Signs.NewRow(); row[0] = dgvr.Cells[0].Value; row[1] = dgvr.Cells[1].Value; row[2] = DateTime.Now; row[4] = 0; row[5] = ""; row[6] = DateTime.Now.Date.ToString() + DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString() + ":" + DateTime.Now.Second.ToString() + ":" + DateTime.Now.Millisecond.ToString(); tb = bSAMDataSet.Signs; try { tb.Rows.Add(row); } catch (Exception erty) { row[6] = DateTime.Now.Date.ToString() + DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString() + ":" + DateTime.Now.Second.ToString() + ":" + (DateTime.Now.Millisecond + 1).ToString(); tb.Rows.Add(row); } dgv.DataSource = tb; } //signsTableAdapter.Update(bSAMDataSet); //Save dgv.CurrentCell = null; DataTable table = new DataTable(); table = (DataTable)dgv.DataSource; DataTable table2 = new DataTable(); if (Main.Amatrix.mgt == "") { using (var con = new SqlCeConnection(signsTableAdapter.Connection.ConnectionString)) using (var adapter = new SqlCeDataAdapter("SELECT * FROM Signs", con)) using (new SqlCeCommandBuilder(adapter)) { adapter.Fill(table2); con.Open(); adapter.Update(table); } } else { asql.Save(table, "Signs", Main.Amatrix.mgt); } this.Close(); }
private void button1_Click(object sender, EventArgs e) { try { if (dataGridView2[0, 0].Value != DBNull.Value && dataGridView2[0, dataGridView2.CurrentRow.Index].Value != DBNull.Value) { setprods(); foreach (DataGridViewRow dgvr in dataGridView2.SelectedRows) { try { dgvr.Cells[3].Value = "Shipping (Logistics)"; dgvr.Cells[4].Value = ID; } catch (Exception erty) { } } DataTable table = new DataTable(); try { dataGridView2.CurrentCell = dataGridView2[0, dataGridView2.CurrentRow.Index + 1]; } catch (Exception erty) { dataGridView2.CurrentCell = dataGridView2[0, dataGridView2.CurrentRow.Index - 1]; } table = (DataTable)dataGridView2.DataSource; DataTable table2 = new DataTable(); if (Main.Amatrix.mgt == "") { using (var con = new SqlCeConnection(Properties.Settings.Default.Amdtbse_4ConnectionString)) using (var adapter = new SqlCeDataAdapter("SELECT * FROM prod_bulk", con)) using (new SqlCeCommandBuilder(adapter)) { adapter.Fill(table2); con.Open(); adapter.Update(table); } } else { asql.Save(table, "prod_bulk", Main.Amatrix.mgt); } opr_temp.glue(dataGridView1[1, dataGridView1.CurrentRow.Index].Value.ToString()); abtclse.Start(); } } catch (Exception erty) { Am_err ner = new Am_err(); ner.tx("The Selected Row is Not Valid. Operation Aborted."); } }
/// <SUMMARY> /// Checks if there is a row with changes and /// writes it to the database /// </SUMMARY> private void UpdateRowToDatabase() { try { if (LastDataRow != null) { if (LastDataRow.RowState == DataRowState.Modified) { DataRow[] rows = new DataRow[1]; rows[0] = LastDataRow; dAdapter.Update(rows); } } } catch (System.Data.SqlServerCe.SqlCeException sqlCe) { Connect.ShowErrors(sqlCe); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void Save_Click(object sender, EventArgs e) { using (SqlCeConnection con = new SqlCeConnection(.....)) { // Prepare again the adapter with a valid select command SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT * FROM Doctor", con); // Force the building of the internal command objects of the adapter SqlCeCommandBuilder cb = new SqlCeCommandBuilder(a); // Recover the datatable from the datasource of the grid DataTable t = DoctorView.DataSource as DataTable; // Update the table with DataRows changed, deleted or inserted a.Update(t); } }
private void sve_Click(object sender, EventArgs e) { DataTable table = new DataTable(); table = (DataTable)dgv.DataSource; DataTable table2 = new DataTable(); using (var con = new SqlCeConnection(connxt)) using (var adapter = new SqlCeDataAdapter("SELECT * FROM [" + choosebook.Text + "]", con)) using (new SqlCeCommandBuilder(adapter)) { adapter.Fill(table2); con.Open(); adapter.Update(table); } }
//=========================================================================== public void upload(DataTable dados) { //Atualiza as informacoes contidas no adaptadorEdit SqlCeCommandBuilder cmd = new SqlCeCommandBuilder(adaptadorEdit); // diz que a query é referente a esses dados coletados adaptadorEdit.UpdateCommand = cmd.GetUpdateCommand(); // procura comando de update try { adaptadorEdit.Update(dados); } catch (Exception erro) { MessageBox.Show(erro.Message); } }
private void ChangeSetting_Click(object sender, RoutedEventArgs e) { try { SqlCeCommand cmd = thisConnection.CreateCommand(); cmd.CommandText = "UPDATE Patient SET Setting = \'" + SettingTxt.Text + "\' WHERE PtID=0 , EquipID=1, Control='Lever'" ; SqlCeDataAdapter adp = new SqlCeDataAdapter(cmd); DataSet ds = new DataSet(); adp.Update(ds.Tables[0]); //PatientView.DataContext = ds.Tables[0]; //rdr.Close(); cmd.Dispose(); } catch (SqlException err) { Console.WriteLine(err.Message); } }
private void saveFileDialog1_FileOk(object sender, CancelEventArgs e) { //if (ExcelFilename == null) //{ // // DialogResult result = saveFileDialog1.ShowDialog(); ExcelFilename = saveFileDialog1.FileName.ToString(); textBox33.Text = ExcelFilename.ToString(); //} using (SqlCeConnection con = new SqlCeConnection(conString)) { con.Open(); using (SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT * FROM table1", con)) { DataTable t = new DataTable(); a.Fill(t); // dataGridView1.DataSource = t; a.Update(t); object missing = System.Reflection.Missing.Value; if (ExcelFilename.IndexOf(@".") == 0)//if extension not on filename { MessageBox.Show("Filename extension not selected, must select text or Excel file. Export Aborted", "scopefocus"); return; } if (Path.GetExtension(ExcelFilename.Substring(1)) == ".txt" || Path.GetExtension(ExcelFilename.Substring(1)) == ".TXT") TextCSV_FromDataTable(t, ExcelFilename); else Excel_FromDataTable(t); } con.Close(); // textBox33.Text = ExcelFilename.ToString(); } }
/// <summary> /// Update the databse for a player /// </summary> /// <param name="newInfo"></param> private void UpdatePlayerInfo(PlayerInfo newInfo) { BattleBoatsDataSet dataset = new BattleBoatsDataSet(); PlayerInfo info = new PlayerInfo(); String select = "SELECT * FROM PlayerInfo WHERE UserName = \'" + newInfo.PlayerName + "\'"; SqlCeDataAdapter adapter = new SqlCeDataAdapter(select, conn); adapter.Fill(dataset, "PlayerInfo"); SqlCeCommandBuilder builder = new SqlCeCommandBuilder(adapter); string update = builder.GetUpdateCommand().CommandText; if (dataset.PlayerInfo.Rows.Count == 1)//New user, create table entries for them { dataset.PlayerInfo.Rows[0]["UserName"] = newInfo.PlayerName; dataset.PlayerInfo.Rows[0]["AmmoUpgrades"] = newInfo.Ammo_Level; dataset.PlayerInfo.Rows[0]["ArmourUpgrades"] = newInfo.Armour_Level; dataset.PlayerInfo.Rows[0]["Money"] = newInfo.Money; dataset.PlayerInfo.Rows[0]["SpeedUpgrades"] = newInfo.Speed_Level; dataset.PlayerInfo.Rows[0]["ShipModel"] = newInfo.Ship_Model_Name; adapter.Update(dataset, "PlayerInfo"); } }
static private DataTable SaveDynamicEntity(esDataRequest request) { bool needToDelete = request.EntitySavePacket.RowState == esDataRowState.Deleted; DataTable dataTable = CreateDataTable(request); using (SqlCeDataAdapter da = new SqlCeDataAdapter()) { da.AcceptChangesDuringUpdate = false; DataRow row = dataTable.NewRow(); dataTable.Rows.Add(row); SqlCeCommand cmd = null; switch (request.EntitySavePacket.RowState) { case esDataRowState.Added: cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, request.EntitySavePacket); SetModifiedValues(request, request.EntitySavePacket, row); break; case esDataRowState.Modified: cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, request.EntitySavePacket); SetOriginalValues(request, request.EntitySavePacket, row, false); SetModifiedValues(request, request.EntitySavePacket, row); row.AcceptChanges(); row.SetModified(); break; case esDataRowState.Deleted: cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, null); SetOriginalValues(request, request.EntitySavePacket, row, true); row.AcceptChanges(); row.Delete(); break; } if (!needToDelete && request.Properties != null) { request.Properties["esDataRequest"] = request; request.Properties["esEntityData"] = request.EntitySavePacket; dataTable.ExtendedProperties["props"] = request.Properties; } DataRow[] singleRow = new DataRow[1]; singleRow[0] = row; if (!request.IgnoreComputedColumns) { da.RowUpdated += new SqlCeRowUpdatedEventHandler(OnRowUpdated); } try { esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, request.EntitySavePacket, "SaveEntityDynamic", System.Environment.StackTrace)) { try { da.Update(singleRow); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion { da.Update(singleRow); } } finally { esTransactionScope.DeEnlist(cmd); } if (request.EntitySavePacket.RowState != esDataRowState.Deleted && cmd.Parameters != null) { foreach (SqlCeParameter param in cmd.Parameters) { switch (param.Direction) { case ParameterDirection.Output: case ParameterDirection.InputOutput: request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value; break; } } } } return dataTable; }
public void WriteToSQL(string message, string tags, SqlCeConnection databaseConnection) { if ((_name.Length == 0) && (active == true)) { throw (new ApplicationException("Can't write to log, no name set", null)); } else { DataSet data = new DataSet(); SqlCeDataAdapter adapter = new SqlCeDataAdapter(String.Format( "SELECT * FROM {0}", _name), databaseConnection); SqlCeCommandBuilder commands = new SqlCeCommandBuilder(adapter); adapter.FillSchema(data, SchemaType.Source, _name); DataRow newRow = data.Tables[_name].NewRow(); if (message.Length > 255) { message = message.Substring(0, 255); } newRow["time"] = DateTime.UtcNow; newRow["tags"] = tags; newRow["text"] = message; data.Tables[_name].Rows.Add(newRow); adapter.Update(data, _name); } }
public void ImportAllCsv() { string fileToImport = appConfig.csvFile; if (Form1.browseCSV != "") { fileToImport = Form1.browseCSV; Form1.browseCSV = ""; } else { try { if (!File.Exists(appConfig.csvFile)) Message("Import feil: Fant ikke CSV fil eller ingen tilgang."); } catch { Message("Import feil: Fant ikke CSV fil eller ingen tilgang."); } } Message("Importerer fra " + fileToImport + ".."); try { try { var engine = new FileHelperEngine(typeof(csvImport)); var resCSV = engine.ReadFile(fileToImport) as csvImport[]; try { if (resCSV.Length > 0) { DateTime dtFirst = DateTime.MaxValue; DateTime dtLast = DateTime.MinValue; int valider = 0; var norway = new CultureInfo("nb-NO"); // sjekk for først og siste dato, samt hvilken kategorier den inneholder for (int i = 0; i < resCSV.Length; i++) { DateTime dtTemp = Convert.ToDateTime(resCSV[i].Dato.ToString()); if (DateTime.Compare(dtTemp, dtFirst) < 0) { // Datoen er eldre dtFirst = dtTemp; } if (DateTime.Compare(dtTemp, dtLast) > 0) { // Datoen er nyere dtLast = dtTemp; } if (resCSV[i].Kgm.StartsWith("2") || resCSV[i].Kgm.StartsWith("4") || resCSV[i].Kgm.StartsWith("5")) { valider++; } } Message("CSV inneholder " + valider + " gyldige transaksjoner, solgt mellom " + dtFirst.ToString("dddd d. MMMM yyyy", norway) + " og " + dtLast.ToString("dddd d. MMMM yyyy", norway) + "."); string strFirst = dtFirst.ToString("yyy-MM-dd"); string strLast = dtLast.ToString("yyy-MM-dd"); if (valider > 0) { con = new SqlCeConnection(Form1.SqlConStr); con.Open(); var command = new SqlCeCommand("DELETE FROM tblSalg WHERE (Dato >= '" + strFirst + "') AND (Dato <= '" + strLast + "')", con); var result = command.ExecuteNonQuery(); if (appConfig.debug) bgWorkerImport.ReportProgress(0, "Slettet " + result + " transaksjoner."); command = new SqlCeCommand("SELECT * FROM tblSalg;", con); var da = new SqlCeDataAdapter(command); ds = new DataSet(); da.Fill(ds, "tblSalg"); var ca = new SqlCeCommandBuilder(da); Message("Prosesserer " + resCSV.Length.ToString("#,##0") + " transaksjoner.."); for (int i = 0; i < resCSV.Length; i++) { // Ta bare med antall ulik 0 if (resCSV[i].Antall != 0 && !resCSV[i].Kgm.Contains("99999") && appConfig.importAll || (resCSV[i].Antall != 0 && (resCSV[i].Kgm.StartsWith("531") || resCSV[i].Kgm.StartsWith("533") || resCSV[i].Kgm.StartsWith("580") || resCSV[i].Kgm.StartsWith("534") || resCSV[i].Kgm.StartsWith("280") || resCSV[i].Kgm.StartsWith("224") || resCSV[i].Kgm.StartsWith("480") || resCSV[i].Kgm.StartsWith("431")) && !appConfig.importAll) ) { // dRow[?] // 0 = SalgID // 1 = Selgerkode // 2 = Varegruppe // 3 = Varekode // 4 = Dato // 5 = Antall DataRow dRow = ds.Tables["tblSalg"].NewRow(); dRow[1] = resCSV[i].Sk; // Selgerkode if (!appConfig.importAll) { if (resCSV[i].Kgm.StartsWith("531")) // desktops dRow[2] = 531; if (resCSV[i].Kgm.StartsWith("533")) // laptops dRow[2] = 533; if (resCSV[i].Kgm.StartsWith("534")) // nettbrett dRow[2] = 534; if (resCSV[i].Kgm.StartsWith("580")) // tjenester dRow[2] = 580; if (resCSV[i].Kgm.StartsWith("280")) // nettbrett dRow[2] = 280; if (resCSV[i].Kgm.StartsWith("224")) // tjenester dRow[2] = 224; if (resCSV[i].Kgm.StartsWith("480")) // nettbrett dRow[2] = 480; if (resCSV[i].Kgm.StartsWith("431")) // tjenester dRow[2] = 431; } else { dRow[2] = resCSV[i].Kgm.Substring(0, 3); // Ta med alle kategorier. } dRow[3] = resCSV[i].Varenummer; string varDato = resCSV[i].Dato.ToString(); dRow[4] = Convert.ToDateTime(varDato); dRow[5] = resCSV[i].Antall; dRow[6] = resCSV[i].Btokr; dRow[7] = resCSV[i].Avd; dRow[8] = resCSV[i].Salgspris; dRow[9] = resCSV[i].BilagsNr; ds.Tables["tblSalg"].Rows.Add(dRow); } } // Send data til SQL server og avslutt forbindelsen da.Update(ds, "tblSalg"); con.Close(); Message("Importering fullført!"); } else { Message("Import feil: Fant ingen gyldige transaksjoner."); } } else { Message("Import feil: Ingen transaksjoner funnet! Kontroller om eksportering er korrekt eller sjekk innstillinger."); } } catch (Exception ex) { Message("Import feil: Unntak ved prosessering av transaksjoner. Feilmelding: " + ex.ToString()); } } catch (Exception ex) { MessageBox.Show("Feil ved lesing av CSV.\n" + ex.ToString(), "KGSA - Importering avbrutt", MessageBoxButtons.OK, MessageBoxIcon.Error); Message("Import feil: Unntak ved lesing av CSV: " + ex.ToString()); } } catch (Exception ex) { Message("Import feil: Ukjent feil ved imprtering. (" + fileToImport + ") Feilmelding: " + ex.ToString()); } }
public void TestRowState() { this.tracer.WriteLine("测试属性 RowState"); #region "是否测试从不同位置载入 DataRow 后 RowState 的状态?" if (this.tracer.WaitInputAChar("是否测试从不同位置载入 DataRow 后 RowState 的状态?") == 'y') { try { using (DataSet dataSet = new DataSet()) { File.WriteAllText(@"temp.xml", "<?xml version=\"1.0\" encoding=\"utf-8\" ?><TestDS xmlns=\"http://tempuri.org/TestDS.xsd\">" + "<Student>" + "<Name>小明</Name>" + "<Age>12</Age>" + "</Student>" + "</TestDS>"); this.tracer.WriteLine("写入包含表 Student(Name, Age) 的 xml 到 temp.xml"); try { dataSet.ReadXml(@"temp.xml"); this.tracer.WriteLine("读取 temp.xml"); foreach (DataTable table in dataSet.Tables) { this.tracer.WriteLine(string.Format("表 {0}", table.TableName)); foreach (DataRow row in table.Rows) this.tracer.WriteLine(string.Format("状态: {0}", row.RowState)); } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } using (DataSet dataSet = new DataSet()) { dataSet.Tables.Add("Student"); dataSet.Tables["Student"].Columns.AddRange( new DataColumn[] { new DataColumn("Name", typeof(string)), new DataColumn("Age", typeof(int)) } ); this.tracer.WriteLine("创建表 Student(Name, Age) 到数据集"); dataSet.Tables["Student"].Rows.Add("小明", "11"); this.tracer.WriteLine("向表 Student 添加数据"); foreach (DataTable table in dataSet.Tables) { this.tracer.WriteLine(string.Format("表 {0}", table.TableName)); foreach (DataRow row in table.Rows) this.tracer.WriteLine(string.Format("状态: {0}", row.RowState)); } } using (DataSet dataSet = new DataSet()) { /* * Test.sdf 始终复制到 bin 目录下 * */ SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Student", @"Data Source=|DataDirectory|\Test.sdf"); try { adapter.Fill(dataSet); this.tracer.WriteLine("从本地 Test.sdf 数据库填充数据集"); foreach (DataTable table in dataSet.Tables) { this.tracer.WriteLine(string.Format("表 {0}", table.TableName)); foreach (DataRow row in table.Rows) this.tracer.WriteLine(string.Format("状态: {0}", row.RowState)); } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } using (DataSet dataSet = new DataSet()) { /* * Test.sdf 始终复制到 bin 目录下 * */ SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Student", @"Data Source=|DataDirectory|\Test.sdf"); adapter.AcceptChangesDuringFill = false; this.tracer.WriteLine(string.Format("将 AcceptChangesDuringFill 属性设置为 false")); try { adapter.Fill(dataSet); this.tracer.WriteLine("从本地 Test.sdf 数据库填充数据集"); foreach (DataTable table in dataSet.Tables) { this.tracer.WriteLine(string.Format("表 {0}", table.TableName)); foreach (DataRow row in table.Rows) this.tracer.WriteLine(string.Format("状态: {0}", row.RowState)); } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } this.tracer.WaitPressEnter(); } #endregion #region "是否测试修改, 更改, 删除后的 DataRow.RowState 转化?" if (this.tracer.WaitInputAChar("是否测试修改, 更改, 删除后的 DataRow.RowState 转化?") == 'y') { try { using (DataSet dataSet = new DataSet()) { /* * Test.sdf 始终复制到 bin 目录下 * */ SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Student", @"Data Source=|DataDirectory|\Test.sdf"); try { adapter.Fill(dataSet); this.tracer.WriteLine("从本地 Test.sdf 数据库填充数据集"); this.tracer.WriteLine(string.Format("表 Student 第 1 条记录的状态为: {0}", dataSet.Tables[0].Rows[0].RowState)); int age = Convert.ToInt32(dataSet.Tables[0].Rows[0]["Age"]); dataSet.Tables[0].Rows[0]["Age"] = 22; this.tracer.WriteLine(string.Format("修改表 Student 第 1 条记录的年龄后, 状态为: {0}", dataSet.Tables[0].Rows[0].RowState)); dataSet.Tables[0].Rows[0]["Age"] = age; this.tracer.WriteLine(string.Format("修改表 Student 第 1 条记录的年龄为原值后, 状态为: {0}", dataSet.Tables[0].Rows[0].RowState)); SqlCeCommandBuilder builder = new SqlCeCommandBuilder(adapter); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.Update(dataSet); this.tracer.WriteLine("将数据集的修改更新到本地 Test.sdf 数据库"); this.tracer.WriteLine(string.Format("表 Student 第 1 条记录的状态为: {0}", dataSet.Tables[0].Rows[0].RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } using (DataSet dataSet = new DataSet()) { dataSet.Tables.Add("Student"); dataSet.Tables["Student"].Columns.AddRange( new DataColumn[] { new DataColumn("Name", typeof(string)), new DataColumn("Age", typeof(int)) } ); this.tracer.WriteLine("创建表 Student(Name, Age) 到数据集"); dataSet.Tables["Student"].Rows.Add("小明", "11"); this.tracer.WriteLine("向表 Student 添加数据"); this.tracer.WriteLine(string.Format("表 Student 第 1 条记录的状态为: {0}", dataSet.Tables["Student"].Rows[0].RowState)); dataSet.Tables["Student"].Rows[0]["Age"] = 22; this.tracer.WriteLine(string.Format("修改表 Student 第 1 条记录的年龄后, 状态为: {0}", dataSet.Tables["Student"].Rows[0].RowState)); dataSet.Tables["Student"].Rows[0]["Name"] = "jack"; this.tracer.WriteLine(string.Format("修改表 Student 第 1 条记录的姓名后, 状态为: {0}", dataSet.Tables["Student"].Rows[0].RowState)); } using (DataSet dataSet = new DataSet()) { /* * Test.sdf 始终复制到 bin 目录下 * */ SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Student", @"Data Source=|DataDirectory|\Test.sdf"); SqlCeCommandBuilder builder = new SqlCeCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); adapter.AcceptChangesDuringUpdate = false; this.tracer.WriteLine(string.Format("将 AcceptChangesDuringUpdate 属性设置为 false")); try { adapter.Fill(dataSet); this.tracer.WriteLine("从本地 Test.sdf 数据库填充数据集"); DataRow newRow = dataSet.Tables[0].Rows.Add("小心心", 10); dataSet.Tables[0].Rows[0]["Age"] = 22; dataSet.Tables[0].Rows[1].Delete(); this.tracer.WriteLine(string.Format("表 Student 第 1 条记录的状态为: {0}", dataSet.Tables[0].Rows[0].RowState)); this.tracer.WriteLine(string.Format("表 Student 第 2 条记录的状态为: {0}", dataSet.Tables[0].Rows[1].RowState)); this.tracer.WriteLine(string.Format("表 Student 第 3 条记录的状态为: {0}", dataSet.Tables[0].Rows[2].RowState)); this.tracer.WriteLine(string.Format("表 Student 新记录的状态为: {0}", newRow.RowState)); adapter.Update(dataSet); this.tracer.WriteLine("将数据集更新到本地 Test.sdf 数据库"); this.tracer.WriteLine(string.Format("表 Student 第 1 条记录的状态为: {0}", dataSet.Tables[0].Rows[0].RowState)); this.tracer.WriteLine(string.Format("表 Student 第 2 条记录的状态为: {0}", dataSet.Tables[0].Rows[1].RowState)); this.tracer.WriteLine(string.Format("表 Student 第 3 条记录的状态为: {0}", dataSet.Tables[0].Rows[2].RowState)); this.tracer.WriteLine(string.Format("表 Student 新记录的状态为: {0}", newRow.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } using (DataSet dataSet = new DataSet()) { /* * Test.sdf 始终复制到 bin 目录下 * */ SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Student", @"Data Source=|DataDirectory|\Test.sdf"); try { adapter.Fill(dataSet); this.tracer.WriteLine("从本地 Test.sdf 数据库填充数据集"); this.tracer.WriteLine(string.Format("表 Student 第 1 条记录的状态为: {0}", dataSet.Tables[0].Rows[0].RowState)); this.tracer.WriteLine(string.Format("表 Student 第 2 条记录的状态为: {0}", dataSet.Tables[0].Rows[1].RowState)); DataRow row1 = dataSet.Tables[0].Rows[0]; DataRow row2 = dataSet.Tables[0].Rows[1]; row1.Delete(); this.tracer.WriteLine(string.Format("对第 1 条记录调用 Delete 方法后状态为 {0}", row1.RowState)); dataSet.Tables[0].Rows.Remove(row2); this.tracer.WriteLine(string.Format("对第 2 条记录调用 Remove 方法后状态为 {0}", row2.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } using (DataSet dataSet = new DataSet()) { dataSet.Tables.Add("Student"); dataSet.Tables["Student"].Columns.AddRange( new DataColumn[] { new DataColumn("Name", typeof(string)), new DataColumn("Age", typeof(int)) } ); this.tracer.WriteLine("创建表 Student(Name, Age) 到数据集"); dataSet.Tables["Student"].Rows.Add("小明", "11"); dataSet.Tables["Student"].Rows.Add("小哈", "13"); this.tracer.WriteLine("向表 Student 添加数据"); this.tracer.WriteLine(string.Format("表 Student 第 1 条记录的状态为: {0}", dataSet.Tables["Student"].Rows[0].RowState)); this.tracer.WriteLine(string.Format("表 Student 第 2 条记录的状态为: {0}", dataSet.Tables["Student"].Rows[1].RowState)); DataRow row1 = dataSet.Tables["Student"].Rows[0]; DataRow row2 = dataSet.Tables["Student"].Rows[1]; row1.Delete(); this.tracer.WriteLine(string.Format("对第 1 条记录调用 Delete 方法后状态为 {0}", row1.RowState)); dataSet.Tables["Student"].Rows.Remove(row2); this.tracer.WriteLine(string.Format("对第 2 条记录调用 Remove 方法后状态为 {0}", row2.RowState)); } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } this.tracer.WaitPressEnter(); } #endregion #region "是否测试使用 AcceptChanges, RejectChanges, SetAdded, SetModified 方法后 DataRow.RowState 的转化?" if (this.tracer.WaitInputAChar("是否测试使用 AcceptChanges, RejectChanges, SetAdded, SetModified 方法后 DataRow.RowState 的转化?") == 'y') { try { using (DataSet dataSet = new DataSet()) { /* * Test.sdf 始终复制到 bin 目录下 * */ SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Student", @"Data Source=|DataDirectory|\Test.sdf"); try { adapter.Fill(dataSet); this.tracer.WriteLine("从本地 Test.sdf 数据库填充数据集"); DataRow row1 = dataSet.Tables[0].Rows[0]; DataRow row2 = dataSet.Tables[0].Rows[1]; DataRow row3 = dataSet.Tables[0].Rows[2]; DataRow row4 = dataSet.Tables[0].Rows[3]; this.tracer.WriteLine(string.Format("表 Student 第 1 条记录的状态为: {0}", row1.RowState)); row1.AcceptChanges(); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 AcceptChanges 方法后的状态为: {0}", row1.RowState)); row2["Age"] = 21; this.tracer.WriteLine(string.Format("对表 Student 第 2 条记录修改后的状态为: {0}", row2.RowState)); row2.AcceptChanges(); this.tracer.WriteLine(string.Format("对表 Student 第 2 条记录调用 AcceptChanges 方法后的状态为: {0}", row2.RowState)); row3.SetAdded(); this.tracer.WriteLine(string.Format("对表 Student 第 3 条记录调用 SetAdded 方法后的状态为: {0}", row3.RowState)); row3.AcceptChanges(); this.tracer.WriteLine(string.Format("对表 Student 第 3 条记录调用 AcceptChanges 方法后的状态为: {0}", row3.RowState)); row4.Delete(); this.tracer.WriteLine(string.Format("对表 Student 第 4 条记录调用 Delete 方法后的状态为: {0}", row4.RowState)); row4.AcceptChanges(); this.tracer.WriteLine(string.Format("对表 Student 第 4 条记录调用 AcceptChanges 方法后的状态为: {0}", row4.RowState)); try { dataSet.Tables[0].Rows.Remove(row1); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 Remove 方法后的状态为: {0}", row1.RowState)); row1.AcceptChanges(); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 AcceptChanges 方法后的状态为: {0}", row1.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } using (DataSet dataSet = new DataSet()) { /* * Test.sdf 始终复制到 bin 目录下 * */ SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Student", @"Data Source=|DataDirectory|\Test.sdf"); try { adapter.Fill(dataSet); this.tracer.WriteLine("从本地 Test.sdf 数据库填充数据集"); DataRow row1 = dataSet.Tables[0].Rows[0]; DataRow row2 = dataSet.Tables[0].Rows[1]; DataRow row3 = dataSet.Tables[0].Rows[2]; DataRow row4 = dataSet.Tables[0].Rows[3]; this.tracer.WriteLine(string.Format("表 Student 第 1 条记录的状态为: {0}", row1.RowState)); row1.RejectChanges(); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 RejectChanges 方法后的状态为: {0}", row1.RowState)); row2["Age"] = 21; this.tracer.WriteLine(string.Format("对表 Student 第 2 条记录修改后的状态为: {0}", row2.RowState)); row2.RejectChanges(); this.tracer.WriteLine(string.Format("对表 Student 第 2 条记录调用 RejectChanges 方法后的状态为: {0}", row2.RowState)); row3.SetAdded(); this.tracer.WriteLine(string.Format("对表 Student 第 3 条记录调用 SetAdded 方法后的状态为: {0}", row3.RowState)); row3.RejectChanges(); this.tracer.WriteLine(string.Format("对表 Student 第 3 条记录调用 RejectChanges 方法后的状态为: {0}", row3.RowState)); row4.Delete(); this.tracer.WriteLine(string.Format("对表 Student 第 4 条记录调用 Delete 方法后的状态为: {0}", row4.RowState)); row4.RejectChanges(); this.tracer.WriteLine(string.Format("对表 Student 第 4 条记录调用 RejectChanges 方法后的状态为: {0}", row4.RowState)); dataSet.Tables[0].Rows.Remove(row1); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 Remove 方法后的状态为: {0}", row1.RowState)); row1.RejectChanges(); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 RejectChanges 方法后的状态为: {0}", row1.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } using (DataSet dataSet = new DataSet()) { /* * Test.sdf 始终复制到 bin 目录下 * */ SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Student", @"Data Source=|DataDirectory|\Test.sdf"); try { adapter.Fill(dataSet); this.tracer.WriteLine("从本地 Test.sdf 数据库填充数据集"); DataRow row1 = dataSet.Tables[0].Rows[0]; DataRow row2 = dataSet.Tables[0].Rows[1]; DataRow row3 = dataSet.Tables[0].Rows[2]; DataRow row4 = dataSet.Tables[0].Rows[3]; try { this.tracer.WriteLine(string.Format("表 Student 第 1 条记录的状态为: {0}", row1.RowState)); row1.SetAdded(); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 SetAdded 方法后的状态为: {0}", row1.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } try { row2["Age"] = 21; this.tracer.WriteLine(string.Format("对表 Student 第 2 条记录修改后的状态为: {0}", row2.RowState)); row2.SetAdded(); this.tracer.WriteLine(string.Format("对表 Student 第 2 条记录调用 SetAdded 方法后的状态为: {0}", row2.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } try { row3.SetAdded(); this.tracer.WriteLine(string.Format("对表 Student 第 3 条记录调用 SetAdded 方法后的状态为: {0}", row3.RowState)); row3.SetAdded(); this.tracer.WriteLine(string.Format("对表 Student 第 3 条记录调用 SetAdded 方法后的状态为: {0}", row3.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } try { row4.Delete(); this.tracer.WriteLine(string.Format("对表 Student 第 4 条记录调用 Delete 方法后的状态为: {0}", row4.RowState)); row4.SetAdded(); this.tracer.WriteLine(string.Format("对表 Student 第 4 条记录调用 SetAdded 方法后的状态为: {0}", row4.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } try { dataSet.Tables[0].Rows.Remove(row1); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 Remove 方法后的状态为: {0}", row1.RowState)); row1.SetAdded(); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 SetAdded 方法后的状态为: {0}", row1.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } using (DataSet dataSet = new DataSet()) { /* * Test.sdf 始终复制到 bin 目录下 * */ SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Student", @"Data Source=|DataDirectory|\Test.sdf"); try { adapter.Fill(dataSet); this.tracer.WriteLine("从本地 Test.sdf 数据库填充数据集"); DataRow row1 = dataSet.Tables[0].Rows[0]; DataRow row2 = dataSet.Tables[0].Rows[1]; DataRow row3 = dataSet.Tables[0].Rows[2]; DataRow row4 = dataSet.Tables[0].Rows[3]; try { this.tracer.WriteLine(string.Format("表 Student 第 1 条记录的状态为: {0}", row1.RowState)); row1.SetModified(); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 SetModified 方法后的状态为: {0}", row1.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } try { row2["Age"] = 21; this.tracer.WriteLine(string.Format("对表 Student 第 2 条记录修改后的状态为: {0}", row2.RowState)); row2.SetModified(); this.tracer.WriteLine(string.Format("对表 Student 第 2 条记录调用 SetModified 方法后的状态为: {0}", row2.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } try { row3.SetAdded(); this.tracer.WriteLine(string.Format("对表 Student 第 3 条记录调用 SetAdded 方法后的状态为: {0}", row3.RowState)); row3.SetModified(); this.tracer.WriteLine(string.Format("对表 Student 第 3 条记录调用 SetModified 方法后的状态为: {0}", row3.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } try { row4.Delete(); this.tracer.WriteLine(string.Format("对表 Student 第 4 条记录调用 Delete 方法后的状态为: {0}", row4.RowState)); row4.SetModified(); this.tracer.WriteLine(string.Format("对表 Student 第 4 条记录调用 SetModified 方法后的状态为: {0}", row4.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } try { dataSet.Tables[0].Rows.Remove(row1); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 Remove 方法后的状态为: {0}", row1.RowState)); row1.SetModified(); this.tracer.WriteLine(string.Format("对表 Student 第 1 条记录调用 SetModified 方法后的状态为: {0}", row1.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } this.tracer.WaitPressEnter(); } #endregion #region "是否测试使用 ImportRow, Copy 方法后 DataRow.RowState 的转化?" if (this.tracer.WaitInputAChar("是否测试使用 ImportRow, Copy 方法后 DataRow.RowState 的转化?") == 'y') { try { using (DataSet dataSet = new DataSet()) { dataSet.Tables.Add("Student"); dataSet.Tables["Student"].Columns.AddRange( new DataColumn[] { new DataColumn("Name", typeof(string)), new DataColumn("Age", typeof(int)) } ); this.tracer.WriteLine("创建表 Student(Name, Age) 到数据集 dataSet"); DataSet dataSet1 = dataSet.Clone(); this.tracer.WriteLine("克隆数据集 dataSet 到 dataSet1"); DataRow row1 = dataSet.Tables["Student"].Rows.Add("小明", 12); DataRow row2 = dataSet.Tables["Student"].Rows.Add("小红", 10); row2.AcceptChanges(); DataRow row3 = dataSet.Tables["Student"].Rows.Add("小刚", 15); row3.AcceptChanges(); row3["Age"] = 14; DataRow row4 = dataSet.Tables["Student"].Rows.Add("小芳", 18); row4.AcceptChanges(); row4.Delete(); DataRow row5 = dataSet.Tables["Student"].NewRow(); this.tracer.WriteLine("为 dataSet 中的表 Student 添加数据"); this.tracer.WriteLine(string.Format("记录 row1 的状态为 {0}", row1.RowState)); this.tracer.WriteLine(string.Format("记录 row2 的状态为 {0}", row2.RowState)); this.tracer.WriteLine(string.Format("记录 row3 的状态为 {0}", row3.RowState)); this.tracer.WriteLine(string.Format("记录 row4 的状态为 {0}", row4.RowState)); this.tracer.WriteLine(string.Format("记录 row5 的状态为 {0}", row5.RowState)); dataSet1.Tables["Student"].ImportRow(row1); dataSet1.Tables["Student"].ImportRow(row2); dataSet1.Tables["Student"].ImportRow(row3); dataSet1.Tables["Student"].ImportRow(row4); dataSet1.Tables["Student"].ImportRow(row5); this.tracer.WriteLine(string.Format("dataSet1 表 Student 第 1 条记录的状态为: {0}", dataSet1.Tables["Student"].Rows[0].RowState)); this.tracer.WriteLine(string.Format("dataSet1 表 Student 第 2 条记录的状态为: {0}", dataSet1.Tables["Student"].Rows[1].RowState)); this.tracer.WriteLine(string.Format("dataSet1 表 Student 第 3 条记录的状态为: {0}", dataSet1.Tables["Student"].Rows[2].RowState)); this.tracer.WriteLine(string.Format("dataSet1 表 Student 第 4 条记录的状态为: {0}", dataSet1.Tables["Student"].Rows[3].RowState)); try { this.tracer.WriteLine(string.Format("dataSet1 表 Student 第 5 条记录的状态为: {0}", dataSet1.Tables["Student"].Rows[4].RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } using (DataSet dataSet = new DataSet()) { dataSet.Tables.Add("Student"); dataSet.Tables["Student"].Columns.AddRange( new DataColumn[] { new DataColumn("Name", typeof(string)), new DataColumn("Age", typeof(int)) } ); this.tracer.WriteLine("创建表 Student(Name, Age) 到数据集 dataSet"); DataRow row1 = dataSet.Tables["Student"].Rows.Add("小明", 12); DataRow row2 = dataSet.Tables["Student"].Rows.Add("小红", 10); row2.AcceptChanges(); DataRow row3 = dataSet.Tables["Student"].Rows.Add("小刚", 15); row3.AcceptChanges(); row3["Age"] = 14; DataRow row4 = dataSet.Tables["Student"].Rows.Add("小芳", 18); row4.AcceptChanges(); row4.Delete(); DataRow row5 = dataSet.Tables["Student"].NewRow(); this.tracer.WriteLine("为 dataSet 中的表 Student 添加数据"); this.tracer.WriteLine(string.Format("记录 row1 的状态为 {0}", row1.RowState)); this.tracer.WriteLine(string.Format("记录 row2 的状态为 {0}", row2.RowState)); this.tracer.WriteLine(string.Format("记录 row3 的状态为 {0}", row3.RowState)); this.tracer.WriteLine(string.Format("记录 row4 的状态为 {0}", row4.RowState)); this.tracer.WriteLine(string.Format("记录 row5 的状态为 {0}", row5.RowState)); DataSet dataSet1 = dataSet.Copy(); this.tracer.WriteLine("复制数据集 dataSet 到 dataSet1"); this.tracer.WriteLine(string.Format("dataSet1 表 Student 第 1 条记录的状态为: {0}", dataSet1.Tables["Student"].Rows[0].RowState)); this.tracer.WriteLine(string.Format("dataSet1 表 Student 第 2 条记录的状态为: {0}", dataSet1.Tables["Student"].Rows[1].RowState)); this.tracer.WriteLine(string.Format("dataSet1 表 Student 第 3 条记录的状态为: {0}", dataSet1.Tables["Student"].Rows[2].RowState)); this.tracer.WriteLine(string.Format("dataSet1 表 Student 第 4 条记录的状态为: {0}", dataSet1.Tables["Student"].Rows[3].RowState)); try { this.tracer.WriteLine(string.Format("dataSet1 表 Student 第 5 条记录的状态为: {0}", dataSet1.Tables["Student"].Rows[4].RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } this.tracer.WaitPressEnter(); } #endregion #region "是否测试访问不同 RowState 的 DataRow 中的数据?" if (this.tracer.WaitInputAChar("是否测试访问不同 RowState 的 DataRow 中的数据?") == 'y') { try { using (DataSet dataSet = new DataSet()) { /* * Test.sdf 始终复制到 bin 目录下 * */ SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Student", @"Data Source=|DataDirectory|\Test.sdf"); try { adapter.Fill(dataSet); this.tracer.WriteLine("从本地 Test.sdf 数据库填充数据集"); DataRow row1 = dataSet.Tables[0].Rows[0]; DataRow row2 = dataSet.Tables[0].Rows[1]; this.tracer.WriteLine(string.Format("第 1 条记录, 姓名: {0}, 年龄: {1}", row1["Name"], row1["Age"])); row1["Age"] = 50; this.tracer.WriteLine(string.Format("修改并访问第 1 条记录当前数据, 姓名: {0}, 年龄: {1}", row1["Name"], row1["Age"])); row1.Delete(); this.tracer.WriteLine(string.Format("对第 1 条记录调用 Delete 方法并访问, 姓名: {0}, 年龄: {1}", row1["Name", DataRowVersion.Original], row1["Age", DataRowVersion.Original])); try { dataSet.Tables[0].Rows.Remove(row2); this.tracer.WriteLine(string.Format("对第 2 条记录调用 Remove 方法并访问, 姓名: {0}, 年龄: {1}", row2["Name", DataRowVersion.Original], row2["Age", DataRowVersion.Original])); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } this.tracer.WaitPressEnter(); } #endregion #region "是否测试获取 DataTable 中不同 RowState 的 DataRow?" if (this.tracer.WaitInputAChar("是否测试获取 DataTable 中不同 RowState 的 DataRow?") == 'y') { try { using (DataSet dataSet = new DataSet()) { /* * Test.sdf 始终复制到 bin 目录下 * */ SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Student", @"Data Source=|DataDirectory|\Test.sdf"); try { adapter.Fill(dataSet); this.tracer.WriteLine("从本地 Test.sdf 数据库填充数据集"); DataRow row4 = dataSet.Tables[0].Rows[3]; dataSet.Tables[0].Rows[0]["Age"] = 21; this.tracer.WriteLine("修改第 1 条记录"); dataSet.Tables[0].Rows[1].Delete(); this.tracer.WriteLine("对第 2 条记录调用 Delete 方法"); dataSet.Tables[0].Rows.Remove(dataSet.Tables[0].Rows[2]); this.tracer.WriteLine("移除第 3 条记录"); dataSet.Tables[0].Rows.Add("小新", 10); this.tracer.WriteLine("添加新的记录"); this.tracer.WriteLine("使用 GetChanges 方法获取状态为 Added 的记录的副本"); foreach (DataRow row in dataSet.Tables[0].GetChanges(DataRowState.Added).Rows) this.tracer.WriteLine(string.Format("\t姓名: {0}, 年龄: {1}, 行状态: {2}", row["Name"], row["Age"], row.RowState)); this.tracer.WriteLine("使用 GetChanges 方法获取状态为 Modified 的记录的副本"); foreach (DataRow row in dataSet.Tables[0].GetChanges(DataRowState.Modified).Rows) this.tracer.WriteLine(string.Format("\t姓名: {0}, 年龄: {1}, 行状态: {2}", row["Name"], row["Age"], row.RowState)); this.tracer.WriteLine("使用 GetChanges 方法获取状态为 Unchanged 的记录的副本"); foreach (DataRow row in dataSet.Tables[0].GetChanges(DataRowState.Unchanged).Rows) { this.tracer.WriteLine(string.Format("\t姓名: {0}, 年龄: {1}, 行状态: {2}", row["Name"], row["Age"], row.RowState)); row["Age"] = 22; this.tracer.WriteLine(string.Format("\t修改此副本记录为姓名: {0}, 年龄: {1}, 行状态: {2}", row["Name"], row["Age"], row.RowState)); this.tracer.WriteLine(string.Format("\t原始记录为姓名: {0}, 年龄: {1}, 行状态: {2}", row4["Name"], row4["Age"], row4.RowState)); } this.tracer.WriteLine("使用 GetChanges 方法获取状态为 Deleted 的记录的副本"); foreach (DataRow row in dataSet.Tables[0].GetChanges(DataRowState.Deleted).Rows) this.tracer.WriteLine(string.Format("\t姓名: {0}, 年龄: {1}, 行状态: {2}", row["Name", DataRowVersion.Original], row["Age", DataRowVersion.Original], row.RowState)); this.tracer.WriteLine("使用 GetChanges 方法获取状态为 Modified 和 Added 的记录的副本"); foreach (DataRow row in dataSet.Tables[0].GetChanges(DataRowState.Modified | DataRowState.Added).Rows) this.tracer.WriteLine(string.Format("\t姓名: {0}, 年龄: {1}, 行状态: {2}", row["Name"], row["Age"], row.RowState)); try { this.tracer.WriteLine("使用 GetChanges 方法获取状态为 Detached 的记录的副本"); foreach (DataRow row in dataSet.Tables[0].GetChanges(DataRowState.Detached).Rows) this.tracer.WriteLine("发现一条状态为 Detached 的记录, 但无法访问"); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } this.tracer.WriteLine("使用没有参数的 GetChanges 方法获取记录的副本"); foreach (DataRow row in dataSet.Tables[0].GetChanges().Rows) this.tracer.WriteLine(string.Format("\t行状态: {0}", row.RowState)); this.tracer.WriteLine("使用没有参数的 GetChanges 方法获取记录的副本"); foreach (DataRow row in dataSet.Tables[0].GetChanges().Rows) this.tracer.WriteLine(string.Format("\t行状态: {0}", row.RowState)); } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } } } catch (Exception err) { this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); } this.tracer.WaitPressEnter(); } #endregion }
public void SumWorkHoursAndDays(BudgetInfo budget_info) { try { var con = new SqlCeConnection(FormMain.SqlConStr); con.Open(); var command = new SqlCeCommand("SELECT * FROM tblBudgetSelger WHERE BudgetId = " + budget_info.budget_id, con); var da = new SqlCeDataAdapter(command); var ds = new DataSet(); da.Fill(ds, "tblBudgetTimer"); var ca = new SqlCeCommandBuilder(da); DataTable dtTimer = main.database.GetSqlDataTable("SELECT * FROM tblBudgetTimer WHERE BudgetId = " + budget_info.budget_id); if (dtTimer.Rows.Count == 0) return; for (int i = 0; i < dtTimer.Rows.Count; i++) { for (int b = 0; b < ds.Tables["tblBudgetTimer"].Rows.Count; b++) { if (ds.Tables["tblBudgetTimer"].Rows[b]["Selgerkode"].ToString() == dtTimer.Rows[i]["Selgerkode"].ToString()) { decimal timer = 0, dager = 0; for (int c = 3; c < dtTimer.Columns.Count; c++) { if (!DBNull.Value.Equals(dtTimer.Rows[i][c])) { decimal t = Convert.ToDecimal(dtTimer.Rows[i][c]); timer += t; dager++; } } ds.Tables["tblBudgetTimer"].Rows[b]["Timer"] = timer; ds.Tables["tblBudgetTimer"].Rows[b]["Dager"] = dager; } } } da.Update(ds, "tblBudgetTimer"); // Oppdater timeantall oppdaterings tid con = new SqlCeConnection(FormMain.SqlConStr); con.Open(); command = new SqlCeCommand("SELECT * FROM tblBudget WHERE Id = " + budget_info.budget_id, con); da = new SqlCeDataAdapter(command); ds = new DataSet(); da.Fill(ds, "tblBudget"); ca = new SqlCeCommandBuilder(da); if (ds.Tables["tblBudget"].Rows.Count > 0) ds.Tables["tblBudget"].Rows[0]["Updated"] = DateTime.Now; else Log.n("Fikk ikke satt tidspunkt for timeantall oppdatering.", Color.Red); da.Update(ds, "tblBudget"); con.Close(); con.Dispose(); } catch(Exception ex) { Log.Unhandled(ex); } }
private static DataTable SaveDynamicCollection_InsertsUpdates(tgDataRequest request) { DataTable dataTable = CreateDataTable(request); using (tgTransactionScope scope = new tgTransactionScope()) { using (SqlCeDataAdapter da = new SqlCeDataAdapter()) { da.AcceptChangesDuringUpdate = false; da.ContinueUpdateOnError = request.ContinueUpdateOnError; SqlCeCommand cmd = null; if (!request.IgnoreComputedColumns) { da.RowUpdated += new SqlCeRowUpdatedEventHandler(OnRowUpdated); } foreach (tgEntitySavePacket packet in request.CollectionSavePacket) { if (packet.RowState != tgDataRowState.Added && packet.RowState != tgDataRowState.Modified) continue; DataRow row = dataTable.NewRow(); dataTable.Rows.Add(row); switch (packet.RowState) { case tgDataRowState.Added: cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, packet); SetModifiedValues(request, packet, row); break; case tgDataRowState.Modified: cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, packet); SetOriginalValues(request, packet, row, false); SetModifiedValues(request, packet, row); row.AcceptChanges(); row.SetModified(); break; } request.Properties["tgDataRequest"] = request; request.Properties["esEntityData"] = packet; dataTable.ExtendedProperties["props"] = request.Properties; DataRow[] singleRow = new DataRow[1]; singleRow[0] = row; try { tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, packet, "SaveCollectionDynamic", System.Environment.StackTrace)) { try { da.Update(singleRow); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { da.Update(singleRow); } if (row.HasErrors) { request.FireOnError(packet, row.RowError); } } finally { tgTransactionScope.DeEnlist(cmd); dataTable.Rows.Clear(); } if (!row.HasErrors && packet.RowState != tgDataRowState.Deleted && cmd.Parameters != null) { foreach (SqlCeParameter param in cmd.Parameters) { switch (param.Direction) { case ParameterDirection.Output: case ParameterDirection.InputOutput: packet.CurrentValues[param.SourceColumn] = param.Value; break; } } } } } scope.Complete(); } return dataTable; }
/// <summary> /// Searches for members /// </summary> /// <param name="search_by">The db column where we search</param> /// <param name="keyword">What we want to find</param> /// <returns></returns> public static DataTable AdvancedSearch(string search_by, string keyword) { DataTable dataset; using (SqlCeConnection con = DB.GetSqlCeConnection()) { String sql = "SELECT Members.Id, Members.CardNumber, Members.LastName, Members.FirstName, Members.HomePhone, " + "Members.CellPhone, Members.Email, (Trainers.FirstName + ' ' + Trainers.LastName) AS PersonalTrainer " + "FROM Members LEFT OUTER JOIN Trainers ON Members.PersonalTrainer = Trainers.Id " + "WHERE Members."+search_by+" LIKE '%"+ @keyword +"%' "; SqlCeCommand cmd = new SqlCeCommand(sql, con); cmd.Parameters.AddWithValue("@keyword", keyword); SqlCeDataAdapter sda = new SqlCeDataAdapter(); sda.SelectCommand = cmd; dataset = new DataTable(); sda.Fill(dataset); sda.Update(dataset); return dataset; } }
/// <summary> /// retrieves all members /// </summary> /// <returns>datatable with all member info</returns> public static DataTable GetAllMembers() { DataTable dataset; using (SqlCeConnection con = DB.GetSqlCeConnection()) { String sql = "SELECT Members.Id, Members.CardNumber, Members.LastName, Members.FirstName, Members.HomePhone, " + "Members.CellPhone, Members.Email, (Trainers.FirstName + ' ' + Trainers.LastName) AS PersonalTrainer " + "FROM Members LEFT OUTER JOIN Trainers ON Members.PersonalTrainer = Trainers.Id "; SqlCeCommand cmd = new SqlCeCommand(sql, con); SqlCeDataAdapter sda = new SqlCeDataAdapter(); sda.SelectCommand = cmd; dataset = new DataTable(); sda.Fill(dataset); //BindingSource bSource = new BindingSource(); //bSource.DataSource = dataset; //membersDataGridView.DataSource = bSource; sda.Update(dataset); return dataset; } }
/// <summary> /// Get the info for the player out of the database and return in /// </summary> /// <param name="tag">The GamerTag of the player</param> /// <returns>PlayerInfo for the gamer</returns> private PlayerInfo GetPlayerInfo(String tag) { BattleBoatsDataSet dataset = new BattleBoatsDataSet(); PlayerInfo info = new PlayerInfo(); String select = "SELECT * FROM PlayerInfo WHERE UserName = \'" + tag + "\'"; SqlCeDataAdapter adapter = new SqlCeDataAdapter(select, conn); adapter.Fill(dataset, "PlayerInfo"); if (dataset.PlayerInfo.Rows.Count == 0)//New user, create table entries for them { BattleBoatsDataSet.PlayerInfoRow playerrow = dataset.PlayerInfo.NewPlayerInfoRow(); playerrow.UserName = tag; playerrow.AmmoUpgrades = 0; playerrow.ArmourUpgrades = 0; playerrow.Money = 0; playerrow.SpeedUpgrades = 0; playerrow.ShipModel = "Basic"; dataset.PlayerInfo.AddPlayerInfoRow(playerrow); SqlCeCommandBuilder builder = new SqlCeCommandBuilder(adapter); builder.QuotePrefix = "["; builder.QuoteSuffix = "]"; adapter.Update(dataset, "PlayerInfo"); } info.PlayerName = dataset.PlayerInfo[0].UserName; info.Ammo_Level = dataset.PlayerInfo[0].AmmoUpgrades; info.Armour_Level = dataset.PlayerInfo[0].ArmourUpgrades; info.Money = dataset.PlayerInfo[0].Money; info.Speed_Level = dataset.PlayerInfo[0].SpeedUpgrades; info.Ship_Model_Name = dataset.PlayerInfo[0].ShipModel; return info; }
public void UpdateBudgetSelgerkoder(int budgetId) { try { var currentBudgetInfo = GetBudgetInfo(budgetId); if (currentBudgetInfo != null) { var sk = main.salesCodes.GetBudgetSelgerkoder(currentBudgetInfo.kategori); var command = new SqlCeCommand("SELECT * FROM tblBudgetSelger WHERE BudgetId = " + budgetId, main.connection); var da = new SqlCeDataAdapter(command); var ds = new DataSet(); da.Fill(ds, "tblBudgetSelger"); var ca = new SqlCeCommandBuilder(da); foreach (string selger in sk) { var foundAuthors = ds.Tables["tblBudgetSelger"].Select("Selgerkode = '" + selger + "'"); if (foundAuthors.Length == 0) { DataRow dRow = ds.Tables["tblBudgetSelger"].NewRow(); dRow["BudgetId"] = budgetId; dRow["Selgerkode"] = selger; dRow["Timer"] = 0; dRow["Dager"] = 0; dRow["Multiplikator"] = 1; dRow["Comment"] = ""; ds.Tables["tblBudgetSelger"].Rows.Add(dRow); } } da.Update(ds, "tblBudgetSelger"); command = new SqlCeCommand("SELECT * FROM tblBudgetTimer WHERE BudgetId = " + budgetId, main.connection); da = new SqlCeDataAdapter(command); ds = new DataSet(); da.Fill(ds, "tblBudgetTimer"); ca = new SqlCeCommandBuilder(da); foreach (string selger in sk) { var foundAuthors = ds.Tables["tblBudgetTimer"].Select("Selgerkode = '" + selger + "'"); if (foundAuthors.Length == 0) { DataRow dRow = ds.Tables["tblBudgetTimer"].NewRow(); dRow["BudgetId"] = budgetId; dRow["Selgerkode"] = selger; ds.Tables["tblBudgetTimer"].Rows.Add(dRow); } } da.Update(ds, "tblBudgetTimer"); } } catch(Exception ex) { Log.Unhandled(ex); } }
private static void AddFolderToDB(object folder_param) { string folderPath = ((folder_params)folder_param).folderpath; using (SqlCeConnection con = CreateConnection(((folder_params)folder_param).filepath)) { con.Open(); string sql = "SELECT Count(*) FROM Folders WHERE name='" + folderPath.Replace("'", "`") + "'"; SqlCeCommand com = new SqlCeCommand(sql, con); if (Convert.ToInt32(com.ExecuteScalar()) != 0) { return; } SqlCeDataAdapter da = new SqlCeDataAdapter("Select * FROM Folders", con); da.InsertCommand = new SqlCeCommand( "INSERT INTO Folders (name) values(@folder_name)"); da.InsertCommand.Parameters.Add("@folder_name", SqlDbType.NVarChar, 255, "name"); da.InsertCommand.Connection = con; DataSet ds = new DataSet("Folder"); DataTable dt = new DataTable("Folders"); dt.Columns.Add(new DataColumn("id", typeof(int))); dt.Columns.Add(new DataColumn("name", typeof(string))); ds.Tables.Add(dt); da.Fill(ds, "Folders"); DataRow drAdd = dt.NewRow(); int RowCount = ds.Tables["Folders"].Rows.Count - 1; if (RowCount >= 0) { drAdd["id"] = int.Parse(ds.Tables["Folders"].Rows[RowCount]["id"].ToString()) + 1; } else { drAdd["id"] = 1; } drAdd["name"] = folderPath.Replace("'", "`"); dt.Rows.Add(drAdd); da.Update(ds, "Folders"); } }
static private DataTable SaveDynamicCollection_Deletes(esDataRequest request) { SqlCeCommand cmd = null; DataTable dataTable = CreateDataTable(request); using (esTransactionScope scope = new esTransactionScope()) { using (SqlCeDataAdapter da = new SqlCeDataAdapter()) { da.AcceptChangesDuringUpdate = false; da.ContinueUpdateOnError = request.ContinueUpdateOnError; try { cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0].ModifiedColumns); esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); DataRow[] singleRow = new DataRow[1]; // Delete each record foreach (esEntitySavePacket packet in request.CollectionSavePacket) { DataRow row = dataTable.NewRow(); dataTable.Rows.Add(row); SetOriginalValues(request, packet, row, true); row.AcceptChanges(); row.Delete(); singleRow[0] = row; da.Update(singleRow); if (row.HasErrors) { request.FireOnError(packet, row.RowError); } dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow } } finally { esTransactionScope.DeEnlist(cmd); } } scope.Complete(); } return request.Table; }
void FillData() { try { using (SqlCeConnection con = new SqlCeConnection(conString)) { con.Open(); using (SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT * FROM table1", con)) { DataTable t = new DataTable(); a.Fill(t); dataGridView1.DataSource = t; a.Update(t); // FileLog2(GetCreateFromDataTableSQL("table1", t)); //ytry to create the create string from here 7-25-14 } con.Close(); } ((DataTable)this.dataGridView1.DataSource).DefaultView.RowFilter = "Equip =" + "'" + toolStripStatusLabel3.Text.ToString() + "'"; // ((DataTable)this.dataGridView1.DataSource).DefaultView.RowFilter = "Equip =" + "'" + textBox13.Text.ToString() + "'"; } catch (Exception ex) { Log("FillData Error" + ex.ToString()); } }
/// <summary> /// Executes the respective command for each inserted, updated, or deleted row in the DataSet. /// </summary> /// <remarks> /// e.g.: /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); /// </remarks> /// <param name="insertCommand">A valid transact-SQL statement to insert new records into the data source</param> /// <param name="deleteCommand">A valid transact-SQL statement to delete records from the data source</param> /// <param name="updateCommand">A valid transact-SQL statement used to update records in the data source</param> /// <param name="dataSet">The DataSet used to update the data source</param> /// <param name="tableName">The DataTable used to update the data source.</param> public static void UpdateDataset(SqlCeCommand insertCommand, SqlCeCommand deleteCommand, SqlCeCommand updateCommand, DataSet dataSet, string tableName) { if( insertCommand == null ) throw new ArgumentNullException( "insertCommand" ); if( deleteCommand == null ) throw new ArgumentNullException( "deleteCommand" ); if( updateCommand == null ) throw new ArgumentNullException( "updateCommand" ); if( tableName == null || tableName.Length == 0 ) throw new ArgumentNullException( "tableName" ); // Create a SqlDataAdapter, and dispose of it after we are done SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(); try { // Set the data adapter commands dataAdapter.UpdateCommand = updateCommand; dataAdapter.InsertCommand = insertCommand; dataAdapter.DeleteCommand = deleteCommand; // Update the dataset changes in the data source dataAdapter.Update (dataSet,tableName); // Commit all the changes made to the DataSet dataSet.AcceptChanges(); } catch (SqlCeException E) {string strError=E.Message;} finally{dataAdapter.Dispose();} }
///// <summary> ///// gets the member's name by specified id ///// </summary> ///// <param name="name"></param> ///// <returns>member name</returns> //public static int GetMemberIdByName(string name) //{ // int id = 0; // string query = "SELECT Id FROM Members WHERE LastName = @lname"; // using (SqlCeConnection con = DB.GetSqlCeConnection()) // { // SqlCeCommand cmd = new SqlCeCommand(query, con); // cmd.Parameters.AddWithValue("@lname", name); // SqlCeDataReader reader = cmd.ExecuteReader(); // while (reader.Read()) // { // id = reader.GetInt32(0); // } // } // return id; //} /// <summary> /// retrieves all members who have been assigned to the specified plan /// </summary> /// <returns></returns> public static DataTable GetMembersByPlan( int plan_id ) { DataTable dataset; using (SqlCeConnection con = DB.GetSqlCeConnection()) { String sql = "SELECT DISTINCT " + "Members.Id, Members.CardNumber, Members.LastName, Members.FirstName, Members.HomePhone, Members.CellPhone, Members.Email, " + "Trainers.FirstName + ' ' + Trainers.LastName AS PersonalTrainer " + "FROM Memberships INNER JOIN " + "Members ON Memberships.Member = Members.Id " + "LEFT OUTER JOIN "+ "Trainers ON Members.PersonalTrainer = Trainers.Id " + "WHERE (Memberships.[Plan] = @plan_id) "; SqlCeCommand cmd = new SqlCeCommand(sql, con); cmd.Parameters.AddWithValue("@plan_id", plan_id); SqlCeDataAdapter sda = new SqlCeDataAdapter(); sda.SelectCommand = cmd; dataset = new DataTable(); sda.Fill(dataset); sda.Update(dataset); return dataset; } }
static private DataTable SaveDynamicCollection_Deletes(esDataRequest request) { SqlCeCommand cmd = null; DataTable dataTable = CreateDataTable(request); using (esTransactionScope scope = new esTransactionScope()) { using (SqlCeDataAdapter da = new SqlCeDataAdapter()) { da.AcceptChangesDuringUpdate = false; da.ContinueUpdateOnError = request.ContinueUpdateOnError; try { cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0].ModifiedColumns); esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); DataRow[] singleRow = new DataRow[1]; // Delete each record foreach (esEntitySavePacket packet in request.CollectionSavePacket) { DataRow row = dataTable.NewRow(); dataTable.Rows.Add(row); SetOriginalValues(request, packet, row, true); row.AcceptChanges(); row.Delete(); singleRow[0] = row; #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, packet, "SaveCollectionDynamic", System.Environment.StackTrace)) { try { da.Update(singleRow); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion { da.Update(singleRow); } if (row.HasErrors) { request.FireOnError(packet, row.RowError); } dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow } } finally { esTransactionScope.DeEnlist(cmd); } } scope.Complete(); } return request.Table; }
//View All button private void button17_Click(object sender, EventArgs e) { try { using (SqlCeConnection con = new SqlCeConnection(conString)) { con.Open(); using (SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT * FROM table1", con)) { DataTable t = new DataTable(); a.Fill(t); dataGridView1.DataSource = t; a.Update(t); } con.Close(); } } catch (Exception ex) { Log("View All SQL data Error" + ex.ToString()); } }
public static void UpdatePrefs(DataSet dataSet) { SqlCeConnection conn = BackEndUtils.GetSqlConnection(); try { SqlCeDataAdapter da = new SqlCeDataAdapter(Application_Settings_SQL.commandGetAllAppConfig, conn); SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da); da.Update(dataSet); } finally { conn.Close(); } }
private void Datagridconfiguration_CurrentCellChanged(object sender, EventArgs e) { using (SqlCeConnection cn = new SqlCeConnection(CAN_Monitor_Functions._connstring)) { try { //MessageBox.Show(dtconfiguration.TableName); sda = new SqlCeDataAdapter(string.Format("SELECT * FROM {0}", datatablescolumn.Rows[Comboboxconfiguration.SelectedIndex][0].ToString()), cn); sbuilder = new SqlCeCommandBuilder(sda); sda.Update(dtconfiguration); } catch (SqlCeException sqlex) { MessageBox.Show(sqlex.Message, "fail with filling dataset"); } catch (Exception ex) { MessageBox.Show(ex.Message); } } }
/// <summary> /// Slett selgerkoder som ligger dobbelt /// </summary> /// <returns>Rapport i streng format</returns> public string DeleteDuplicates() { var command = new SqlCeCommand("SELECT * FROM tblSelgerkoder WHERE Avdeling = '" + main.appConfig.Avdeling + "'", main.connection); var dt = main.database.GetSqlDataTable("SELECT * FROM tblSelgerkoder WHERE Avdeling = '" + main.appConfig.Avdeling + "'"); int result = 0; bool TeknikerAlle = false; string returnMsg = ""; object r = dt.Compute("Count(Selgerkode)", "[Provisjon] = 'TeknikerAlle'"); if (!DBNull.Value.Equals(r)) result = Convert.ToInt32(r); else result = 0; if (result > 0) TeknikerAlle = true; var da = new SqlCeDataAdapter(command); var ds = new DataSet(); da.Fill(ds, "tblSelgerkoder"); var ca = new SqlCeCommandBuilder(da); bool slettTeknikerAlle = false; for (int i = 0; i < ds.Tables["tblSelgerkoder"].Rows.Count; i++) { try { string selger = ds.Tables["tblSelgerkoder"].Rows[i]["Provisjon"].ToString().Trim(); if (selger.Length > 0) ds.Tables["tblSelgerkoder"].Rows[i]["Provisjon"] = selger; } catch { } provisjon = ds.Tables["tblSelgerkoder"].Rows[i]["Provisjon"].ToString(); navn = ds.Tables["tblSelgerkoder"].Rows[i]["Selgerkode"].ToString(); kategori = ds.Tables["tblSelgerkoder"].Rows[i]["Kategori"].ToString(); if (TeknikerAlle && provisjon == "TeknikerAlle") { slettTeknikerAlle = true; continue; } result = 0; r = dt.Compute("Count(Selgerkode)", "[Selgerkode] = '" + navn + "'"); if (!DBNull.Value.Equals(r)) result = Convert.ToInt32(r); else result = 0; if (result > 1) { returnMsg = "Selgerkoder: Fant duplikat. Slettet selgerkode " + navn + " med provisjon " + provisjon; ds.Tables["tblSelgerkoder"].Rows[i].Delete(); break; } if (TeknikerAlle && provisjon == "Tekniker") { returnMsg = "Selgerkoder: Med TeknikerAlle valgt på en selgerkode, kan ikke andre motta tekniker-provisjon. Slettet selgerkode " + navn + " med provisjon " + provisjon; ds.Tables["tblSelgerkoder"].Rows[i].Delete(); break; } if (TeknikerAlle && provisjon == "TeknikerAlle" && slettTeknikerAlle) { returnMsg = "Selgerkoder: Fant duplikat av TeknikerAlle provisjon. Bare en selgerkode kan motta tekniker-provisjon på alle salg. Slettet selgerkode " + navn + " med provisjon " + provisjon; ds.Tables["tblSelgerkoder"].Rows[i].Delete(); break; } } da.Update(ds, "tblSelgerkoder"); return returnMsg; }