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