//<Snippet28> void UpdateDB() { System.Data.DataTable DeletedChildRecords = dsNorthwind1.Orders.GetChanges(System.Data.DataRowState.Deleted); System.Data.DataTable NewChildRecords = dsNorthwind1.Orders.GetChanges(System.Data.DataRowState.Added); System.Data.DataTable ModifiedChildRecords = dsNorthwind1.Orders.GetChanges(System.Data.DataRowState.Modified); try { if (DeletedChildRecords != null) { daOrders.Update(DeletedChildRecords); } if (NewChildRecords != null) { daOrders.Update(NewChildRecords); } if (ModifiedChildRecords != null) { daOrders.Update(ModifiedChildRecords); } dsNorthwind1.AcceptChanges(); } catch (Exception ex) { // Update error, resolve and try again } finally { if (DeletedChildRecords != null) { DeletedChildRecords.Dispose(); } if (NewChildRecords != null) { NewChildRecords.Dispose(); } if (ModifiedChildRecords != null) { ModifiedChildRecords.Dispose(); } } }
public void UpdateDb() { try { System.Threading.Monitor.Enter(sqlDataAdapter1); //Lock the dataadapter if (sqlConnection2.State == ConnectionState.Closed) { sqlConnection2.Open(); } sqlDataAdapter1.Update(_table); _table.Rows.Clear(); } catch (Exception exc) { Debug.WriteLine(exc.ToString()); throw; //rethrow the same exception } finally { if (sqlConnection2.State != ConnectionState.Closed) { sqlConnection2.Close(); } System.Threading.Monitor.Exit(sqlDataAdapter1); //Unlock the dataadapter } }
private void dlPlaces_UpdateCommand(object source, System.Web.UI.WebControls.DataListCommandEventArgs e) { // Find the updated controls TextBox addr = (TextBox)e.Item.FindControl("txtAddress"); TextBox notes = (TextBox)e.Item.FindControl("txtNotes"); Label place = (Label)e.Item.FindControl("lblPlaceID"); // Reload the dataset and locate the relevant row adPlaces.Fill(dsPlaces); string sql = "PlaceID = '" + place.Text + "'"; PlaceData.PlaceRow row = (PlaceData.PlaceRow) dsPlaces.Place.Select(sql)[0]; // Set the values using the typed properties row.Address = addr.Text; row.Notes = notes.Text; // Update the row in the database adPlaces.Update(new DataRow[] { row }); // Reset datalist state and bind dlPlaces.EditItemIndex = -1; dlPlaces.SelectedIndex = e.Item.ItemIndex; dlPlaces.DataBind(); }
public bool Save(DataSetMuseum dataSet, Connection conn, Transaction tr) { dataAdapter = new System.Data.SqlClient.SqlDataAdapter(); dataAdapter.InsertCommand = new System.Data.SqlClient.SqlCommand(string.Format("INSERT INTO Rent (ExhibitID, MuseumID, Start, [End]) " + "VALUES (@ExhibitID, @MuseumID, @Start, @End);"), conn.getConnection(), tr.getTransaction()); dataAdapter.InsertCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.InsertCommand.Parameters.Add("@ExhibitID", SqlDbType.Int, 11, "ExhibitID"); dataAdapter.InsertCommand.Parameters.Add("@MuseumID", SqlDbType.Int, 11, "MuseumID"); dataAdapter.InsertCommand.Parameters.Add("@Start", SqlDbType.DateTime, 11, "Start"); dataAdapter.InsertCommand.Parameters.Add("@End", SqlDbType.DateTime, 11, "End"); dataAdapter.UpdateCommand = new System.Data.SqlClient.SqlCommand(string.Format("UPDATE Rent SET ExhibitID = @ExhibitID, MuseumID = @MuseumID, Start = @Start, [End] = @End WHERE ID = @ID;"), conn.getConnection(), tr.getTransaction()); dataAdapter.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.UpdateCommand.Parameters.Add("@ExhibitID", SqlDbType.Int, 11, "ExhibitID"); dataAdapter.UpdateCommand.Parameters.Add("@MuseumID", SqlDbType.Int, 11, "MuseumID"); dataAdapter.UpdateCommand.Parameters.Add("@Start", SqlDbType.DateTime, 11, "Start"); dataAdapter.UpdateCommand.Parameters.Add("@End", SqlDbType.DateTime, 11, "End"); dataAdapter.DeleteCommand = new System.Data.SqlClient.SqlCommand(string.Format("DELETE FROM Rent WHERE ID = @ID;"), conn.getConnection(), tr.getTransaction()); dataAdapter.DeleteCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.DeleteCommand.Parameters.Add("@ExhibitID", SqlDbType.Int, 11, "ExhibitID"); dataAdapter.DeleteCommand.Parameters.Add("@MuseumID", SqlDbType.Int, 11, "MuseumID"); dataAdapter.DeleteCommand.Parameters.Add("@Start", SqlDbType.DateTime, 11, "Start"); dataAdapter.DeleteCommand.Parameters.Add("@End", SqlDbType.DateTime, 11, "End"); var countUpdate = dataAdapter.Update(dataSet, dataSet.Rent.TableName); return((countUpdate > 0) ? true : false); }
/// <summary> /// 修改数据库 /// </summary> /// <param name="sql"></param> /// <param name="tb"></param> private void databaseupdate(string sql, DataTable tb) { //数据更行到数据库 System.Data.SqlClient.SqlConnection connect = new System.Data.SqlClient.SqlConnection(); connect.ConnectionString = FrmMdiMain.Database.ConnectionString;// " server=x6x8-20100320QL\\SQLEXPRESS;database=trasen_Emr_test;UID=sa;Password=sa8920993"; connect.Open(); System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(); adapter.SelectCommand = new System.Data.SqlClient.SqlCommand(sql, connect); System.Data.SqlClient.SqlCommandBuilder sqlcom = new System.Data.SqlClient.SqlCommandBuilder(adapter); //DataTable newtb = new DataTable(); // newtb.TableName = "tb"; DataSet ds = new DataSet(); adapter.TableMappings[0].ColumnMappings.Add("", ""); adapter.TableMappings.Add("df", "fdf"); adapter.Fill(ds); //ds.Tables[0].Rows[2]["note"] = "开户银行2"; ds.Tables[1].Rows[0]["bbid"] = 1; adapter.InsertCommand = sqlcom.GetInsertCommand(); adapter.DeleteCommand = sqlcom.GetDeleteCommand(); adapter.UpdateCommand = sqlcom.GetUpdateCommand(); int i = adapter.Update(ds); tb.AcceptChanges(); sqlcom.RefreshSchema(); connect.Close(); }
private void freeContainer(string AssignMode, string ThisContainer) { // DBConnect = New SqlClient.SqlConnection("Server=tcp:WTC-Ceres.database.windows.net,1433;Initial Catalog=WTC-Ceres;Persist Security Info=False;User ID=CeresAdmin;Password=C3r3$@dm!n;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;") // DBConnect.Open() int RecCount = 0; var DbObjAdapter = new System.Data.SqlClient.SqlDataAdapter("Select * From [Containers In-Out] Where [Container Number] = '" + ThisContainer + "' and ([Container Rejected] = 0 or [Container Rejected] is null)", DBConnect); var DbObjCommand = new System.Data.SqlClient.SqlCommandBuilder(DbObjAdapter); var DbObjDataTable = new DataTable(); DbObjAdapter.Fill(DbObjDataTable); if (DbObjDataTable.Rows.Count == 1) { RecCount = DbObjDataTable.Rows.Count - 1; DbObjDataTable.Rows[RecCount]["In-Gate To Excel"] = false; DbObjDataTable.Rows[RecCount]["Assigned To Excel"] = false; if (AssignMode == Conversions.ToString('\0')) { DbObjDataTable.Rows[RecCount]["Assigned To Storage"] = false; DbObjDataTable.Rows[RecCount]["Assigned To Extra"] = false; DbObjDataTable.Rows[RecCount]["Excel Booking Number"] = DBNull.Value; DbObjDataTable.Rows[RecCount]["Booking Number"] = DBNull.Value; DbObjDataTable.Rows[RecCount]["MinibookingId"] = DBNull.Value; DbObjAdapter.Update(DbObjDataTable); } EmployeeActivityLogService.Insert(My.MyProject.Forms.FrmLoginWindow._userID, "Can: " + ThisContainer, "FREE", "CONTAINER", "Container was released from storage"); } }
private void button4_Click(object sender, System.EventArgs e) { DataSet ds = ((DataTable)dataGrid1.DataSource).DataSet; try { DataAdapter1.Update(ds); } catch (Exception ex) { if (ds.HasErrors) { foreach (DataTable dt in ds.Tables) { if (dt.HasErrors) { foreach (DataRow dr in dt.Rows) { if (dr.HasErrors) { MessageBox.Show(String.Format("Row: {0} = {1}", dr["au_id"], dr.RowError)); foreach (DataColumn dc in dr.GetColumnsInError()) { MessageBox.Show(String.Format("Error en la columna: {0}", dc.ColumnName)); } dr.ClearErrors(); dr.RejectChanges(); } } } } } } }
private void button1_Click_5(object sender, EventArgs e) { using (System.Data.SqlClient.SqlDataAdapter DA = new System.Data.SqlClient.SqlDataAdapter("SELECT FolderID FROM Folders WHERE FolderID > 192 AND FolderID < 1080", ConnectionStrings.LightConnectionString)) { using (DataTable DT = new DataTable()) { DA.Fill(DT); using (System.Data.SqlClient.SqlDataAdapter sDA = new System.Data.SqlClient.SqlDataAdapter("SELECT TOP 0 * FROM DocumentsPermissions", ConnectionStrings.LightConnectionString)) { using (System.Data.SqlClient.SqlCommandBuilder CB = new System.Data.SqlClient.SqlCommandBuilder(sDA)) { using (DataTable sDT = new DataTable()) { sDA.Fill(sDT); foreach (DataRow Row in DT.Rows) { DataRow NewRow = sDT.NewRow(); NewRow["FolderID"] = Row["FolderID"]; NewRow["UserID"] = 374; NewRow["UserTypeID"] = 0; sDT.Rows.Add(NewRow); } sDA.Update(sDT); } } } } } }
private void Sumbit_Click(object sender, Infragistics.WebUI.WebDataInput.ButtonEventArgs e) { daPermission.Update(sm1.权限模块); PopulateDataSet(); BindGrid(); // BindGrid(); }
public bool Save(DataSetMuseum dataSet, Connection conn, Transaction tr) { dataAdapter = new System.Data.SqlClient.SqlDataAdapter(); dataAdapter.InsertCommand = new System.Data.SqlClient.SqlCommand(string.Format("INSERT INTO Exhibition (Name, Start, [End], ShowroomID) " + "VALUES (@Name, @Start, @[End], @ShowroomID);"), conn.getConnection(), tr.getTransaction()); dataAdapter.InsertCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.InsertCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 255, "Name"); dataAdapter.InsertCommand.Parameters.Add("@Start", SqlDbType.DateTime, 11, "Start"); dataAdapter.InsertCommand.Parameters.Add("@[End]", SqlDbType.DateTime, 11, "[End]"); dataAdapter.InsertCommand.Parameters.Add("@ShowroomID", SqlDbType.Int, 11, "ShowroomID"); dataAdapter.UpdateCommand = new System.Data.SqlClient.SqlCommand(string.Format("UPDATE Exhibition SET Name = @Name, Start = @Start, [End] = @[End], ShowroomID = @ShowroomID WHERE ID = @ID;"), conn.getConnection(), tr.getTransaction()); dataAdapter.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 255, "Name"); dataAdapter.UpdateCommand.Parameters.Add("@Start", SqlDbType.DateTime, 11, "Start"); dataAdapter.UpdateCommand.Parameters.Add("@[End]", SqlDbType.DateTime, 11, "[End]"); dataAdapter.UpdateCommand.Parameters.Add("@ShowroomID", SqlDbType.Int, 11, "ShowroomID"); dataAdapter.DeleteCommand = new System.Data.SqlClient.SqlCommand(string.Format("DELETE FROM Exhibition WHERE ID = @ID;"), conn.getConnection(), tr.getTransaction()); dataAdapter.DeleteCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.DeleteCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 255, "Name"); dataAdapter.DeleteCommand.Parameters.Add("@Start", SqlDbType.DateTime, 11, "Start"); dataAdapter.DeleteCommand.Parameters.Add("@[End]", SqlDbType.DateTime, 11, "[End]"); dataAdapter.DeleteCommand.Parameters.Add("@ShowroomID", SqlDbType.Int, 11, "ShowroomID"); var countUpdate = dataAdapter.Update(dataSet, dataSet.Exhibition.TableName); return((countUpdate > 0) ? true : false); }
//--------------------------------------------------------------------- void OtherSnips1() { //--------------------------------------------- System.Data.SqlClient.SqlDataAdapter SqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter(); System.Data.DataSet Dataset1 = new System.Data.DataSet(); Dataset1.Tables.Add(new System.Data.DataTable("Table1")); //<Snippet26> try { SqlDataAdapter1.Update(Dataset1.Tables["Table1"]); } catch (Exception e) { // Error during Update, add code to locate error, reconcile // and try to update again. } //</Snippet26> //--------------------------------------------- NorthwindDataSet northwindDataSet = new NorthwindDataSet(); //<Snippet12> string xmlData = northwindDataSet.GetXml(); //</Snippet12> //<Snippet13> string filePath = "ENTER A VALID FILEPATH"; northwindDataSet.WriteXml(filePath); //</Snippet13> //--------------------------------------------- //<Snippet15> NorthwindDataSetTableAdapters.RegionTableAdapter regionTableAdapter = new NorthwindDataSetTableAdapters.RegionTableAdapter(); regionTableAdapter.Insert(5, "NorthWestern"); //</Snippet15> //--------------------------------------------- //<Snippet16> System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection("YOUR CONNECTION STRING"); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "INSERT Region (RegionID, RegionDescription) VALUES (5, 'NorthWestern')"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); //</Snippet16> }
public void Update(IDbDataAdapter da, DataTable table) { System.Data.SqlClient.SqlDataAdapter dda = da as System.Data.SqlClient.SqlDataAdapter; if (dda == null) { throw new ArgumentException("DataAdapter MUST be an SqlDataAdapter", "da"); } dda.Update(table); }
/// <summary> /// Called when the user clicks the Update button - persist all changes to the database /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void updateButton_Click(object sender, System.EventArgs e) { // Update the database int modified = supplierDataAdapter.Update(supplierDataSet, "Supplier"); if (modified > 0) { MessageBox.Show(string.Format("Modified {0} rows", modified)); } }
// Public Sub saveAssigment(ByVal containerList As Dictionary(Of String, Integer), ByVal bookingNumber As String, shouldHoldCOnt As Boolean) // Dim recordID As Integer, ContainerMinibooking As New ContainerMinibookingService // For Each containerNumb In containerList // recordID = containerNumb.Value // dbCeres.saveContrAssignment(recordID, bookingNumber, selectedMiniBookingId, True, True, False, False, False, shouldHoldCOnt) // EmployeeActivityLogService.Insert(FrmLoginWindow._userID, "CanID: " + recordID.ToString + " | MbID: " + selectedMiniBookingId.ToString, "ASSIGN", "CONTAINER", "Container assigned to booking") // ContainerMinibooking.Insert(recordID, selectedMiniBookingId) // Next // End Sub private void AssignContainerToStorage(string AssignMode, string ThisContainer, string ThisBookingNumber) { int RecCount = 0; var DbObjAdapter = new System.Data.SqlClient.SqlDataAdapter("Select * From [Containers In-Out] Where [Container Number] = '" + ThisContainer + "'", DBConnect); var DbObjCommand = new System.Data.SqlClient.SqlCommandBuilder(DbObjAdapter); var DbObjDataTable = new System.Data.DataTable(); DbObjAdapter.Fill(DbObjDataTable); if (DbObjDataTable.Rows.Count > 0) { RecCount = DbObjDataTable.Rows.Count - 1; DbObjDataTable.Rows[RecCount]["In-Gate To Excel"] = false; DbObjDataTable.Rows[RecCount]["Assigned To Excel"] = false; if (AssignMode == Conversions.ToString('\0')) { DbObjDataTable.Rows[RecCount]["Assigned To Storage"] = false; DbObjDataTable.Rows[RecCount]["Assigned To Extra"] = false; DbObjDataTable.Rows[RecCount]["Excel Booking Number"] = ""; DbObjDataTable.Rows[RecCount]["Booking Number"] = ""; DbObjAdapter.Update(DbObjDataTable); } else if (AssignMode == Conversions.ToString('\u0001')) // ' If Storage { DbObjDataTable.Rows[RecCount]["Assigned To Storage"] = true; DbObjDataTable.Rows[RecCount]["Assigned To Extra"] = false; DbObjDataTable.Rows[RecCount]["Excel Booking Number"] = ThisBookingNumber; DbObjDataTable.Rows[RecCount]["Excel Booking Number"] = ThisBookingNumber; DbObjDataTable.Rows[RecCount]["MinibookingId"] = DBNull.Value; DbObjAdapter.Update(DbObjDataTable); } else if (AssignMode == Conversions.ToString('\u0002')) { DbObjDataTable.Rows[RecCount]["Assigned To Storage"] = false; DbObjDataTable.Rows[RecCount]["Assigned To Extra"] = true; DbObjDataTable.Rows[RecCount]["Excel Booking Number"] = ThisBookingNumber; DbObjDataTable.Rows[RecCount]["Booking Number"] = ThisBookingNumber; DbObjDataTable.Rows[RecCount]["MinibookingId"] = selectedMBId; DbObjAdapter.Update(DbObjDataTable); } } // DBConnect.Close() }
private void TextBox_Validated(object sender, System.EventArgs e) { // Open the connection sqlConnectionNorthwind.Open(); // Update sqlDataAdapterEmployees.Update(dataSetEmployees1, "Employees"); // Refresh the data in the dataset sqlDataAdapterEmployees.Fill(dataSetEmployees1); // Close the connection again sqlConnectionNorthwind.Close(); }
private void button3_Click(object sender, EventArgs e) //submit { System.Data.SqlClient.SqlCommandBuilder cb; DataRow dRow = ds1.Tables["bedrijf"].NewRow(); cb = new System.Data.SqlClient.SqlCommandBuilder(da); dRow[1] = textBox1.Text; //naam dRow[2] = textBox2.Text; //voorvoegsel dRow[3] = textBox3.Text; //zoeknaam dRow[4] = textBox4.Text; //straat dRow[5] = textBox5.Text; dRow[6] = textBox6.Text; dRow[7] = textBox7.Text; dRow[8] = textBox8.Text; dRow[9] = textBox9.Text; dRow[10] = textBox10.Text; dRow[11] = textBox11.Text; dRow[12] = textBox12.Text; dRow[13] = textBox13.Text; dRow[14] = textBox14.Text; dRow[15] = textBox15.Text; dRow[16] = textBox16.Text; dRow[17] = textBox17.Text; dRow[18] = textBox18.Text; dRow[19] = textBox19.Text; dRow[20] = textBox20.Text; dRow[21] = textBox21.Text; ds1.Tables["bedrijf"].Rows.Add(dRow); MaxRows = MaxRows + 1; inc = MaxRows - 1; da.Update(ds1, "bedrijf"); MessageBox.Show("record toegevoegd"); }
public void Update() { udb.Connect(); // 先删除 try { U_Zwd.U_Pub_Zwd.RemoveAll(dtDeleted); dta.Update(dstDeleted); U_Zwd.U_Pub_Zwd.CopyAll(dt, dtDeleted); dtDeleted.AcceptChanges(); } catch (Exception ee) { if (udb.Debug) { new U_Zwd.frm_Debug(dtDeleted, ee.Message).Show(); } } // 后增加 try { dt.Clear(); U_Zwd.U_Pub_Zwd.CopyAll(dtDeleted, dt); dta.Update(dst); } catch (Exception ee) { if (udb.Debug) { new U_Zwd.frm_Debug(dt, ee.Message).Show(); } } udb.DisConnect(); }
private void BttBuscar_Click(object sender, EventArgs e) { string con = Settings.Default.SCOOPConnectionString; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(con); string sql = "select codigo, descricao, unidade, preco, grupo from insumo where descricao like '%" + TxtBoxPesquisar.Text + "%'"; System.Data.SqlClient.SqlDataAdapter dAdapter = new System.Data.SqlClient.SqlDataAdapter(sql, conn); System.Data.SqlClient.SqlCommandBuilder cBuilder = new System.Data.SqlClient.SqlCommandBuilder(dAdapter); DataTable dTable = new DataTable(); dAdapter.Fill(dTable); insumoBindingSource.DataSource = dTable; InsumodataGridView1.DataSource = insumoBindingSource; dAdapter.Update(dTable); }
private void BttPesquisar_Click(object sender, EventArgs e) { string con = Settings.Default.SCOOPConnectionString; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(con); string sql = "select * from bairro where descricao like '%" + TxtBoxNome.Text + "%'"; System.Data.SqlClient.SqlDataAdapter dAdapter = new System.Data.SqlClient.SqlDataAdapter(sql, conn); System.Data.SqlClient.SqlCommandBuilder cBuilder = new System.Data.SqlClient.SqlCommandBuilder(dAdapter); DataTable dTable = new DataTable(); dAdapter.Fill(dTable); bairroBindingSource.DataSource = dTable; dataGridView1.DataSource = bairroBindingSource; dAdapter.Update(dTable); }
public void UpdateDataSource(DataSetBook Changerows) { try { conn.Open(); da.Update(Changerows); } catch (System.Exception E) { this.ErrorHandle(E); } finally { conn.Close(); } }
/// <summary> /// 修改数据库 /// </summary> /// <param name="sql"></param> /// <param name="tb"></param> public static void databaseupdate(string sql, DataTable tb) { //数据更行到数据库 System.Data.SqlClient.SqlConnection connect = new System.Data.SqlClient.SqlConnection(); connect.ConnectionString = FrmMdiMain.Database.ConnectionString;// " server=x6x8-20100320QL\\SQLEXPRESS;database=trasen_Emr_test;UID=sa;Password=sa8920993"; connect.Open(); System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(); adapter.SelectCommand = new System.Data.SqlClient.SqlCommand(sql, connect); System.Data.SqlClient.SqlCommandBuilder sqlcom = new System.Data.SqlClient.SqlCommandBuilder(adapter); //DataSet ds = new DataSet(); //System.Data.SqlClient.SqlTransaction sqltra = connect.BeginTransaction(); //adapter.TableMappings[0].ColumnMappings.Add("", ""); // adapter.TableMappings.Add("df", "fdf"); //adapter.Fill(ds); //ds.Tables[0].Rows[2]["note"] = "开户银行2"; //ds.Tables[1].Rows[0]["bbid"] = 1; //tb.PrimaryKey = new DataColumn[] { tb.Columns["Bsid"] }; //tb.Rows[0]["reason"] = "1"; //tb.Rows[1]["reason"] = "1"; //tb.Rows[2]["reason"] = "1"; //tb.Columns["Bsid"].ColumnName = "path_step_item_id"; //tb.Columns["Parent_id"].ColumnName = "step_item_kind_id"; //tb.Columns["order_spec"].ColumnName = "notes"; DataTable tbnew = tb.GetChanges(DataRowState.Modified); DataTable tbdel = tb.GetChanges(DataRowState.Deleted); adapter.InsertCommand = sqlcom.GetInsertCommand(); adapter.DeleteCommand = sqlcom.GetDeleteCommand(); adapter.UpdateCommand = sqlcom.GetUpdateCommand(); int i = 0; if (tb.GetChanges() != null) { i = adapter.Update(tb); } tb.AcceptChanges(); sqlcom.RefreshSchema(); //tb.Columns["path_step_item_id"].ColumnName = "Bsid"; //tb.Columns["step_item_kind_id"].ColumnName = "Parent_id"; //tb.Columns["notes"].ColumnName = "order_spec"; // sqltra.Commit(); connect.Close(); }
private void BtnActualizar_Click(object sender, EventArgs e) { try { //creo commnad builder para generar automaticamente comandos de actualizacion System.Data.SqlClient.SqlCommandBuilder _comando = new System.Data.SqlClient.SqlCommandBuilder(_DAautor); //actualizar informacion _DAautor.Update(_DS.Tables["Autor"]); _DS.AcceptChanges(); //si llego aca, todo ok LblError.Text = "Actualizacion Exitosa"; } catch (Exception ex) { LblError.Text = ex.Message; } }
public bool Save(DataSetMuseum dataSet, Connection conn, Transaction tr) { dataAdapter = new System.Data.SqlClient.SqlDataAdapter(); dataAdapter.InsertCommand = new System.Data.SqlClient.SqlCommand(string.Format("INSERT INTO Restorer (Name) VALUES (@Name);"), conn.getConnection(), tr.getTransaction()); dataAdapter.InsertCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.InsertCommand.Parameters.Add("@Name", SqlDbType.NChar, 50, "Name"); dataAdapter.UpdateCommand = new System.Data.SqlClient.SqlCommand(string.Format("UPDATE Restorer SET Name = @Name WHERE ID = @ID;"), conn.getConnection(), tr.getTransaction()); dataAdapter.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NChar, 50, "Name"); dataAdapter.DeleteCommand = new System.Data.SqlClient.SqlCommand(string.Format("DELETE FROM Restorer WHERE ID = @ID;"), conn.getConnection(), tr.getTransaction()); dataAdapter.DeleteCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.DeleteCommand.Parameters.Add("@Name", SqlDbType.NChar, 50, "Name"); var countUpdate = dataAdapter.Update(dataSet, dataSet.Restorer.TableName); return((countUpdate > 0) ? true : false); }
protected void Page_Load(object sender, System.EventArgs e) { System.Data.SqlClient.SqlCommandBuilder builder = new System.Data.SqlClient.SqlCommandBuilder(UserAdapter); //string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; //CommonFunctions.Connection.ConnectionString = connectionstring; UserAdapter.SelectCommand.Parameters["@UserID"].Value = userid; if (CommonFunctions.SyncFill(UserAdapter, UserSet) > 0) { PropertiesAdapter.SelectCommand.Parameters["@UserID"].Value = userid; //lock (CommonFunctions.Connection) PropertiesAdapter.Fill(PropertiesSet); foreach (System.Data.DataRow datarow in PropertiesSet.Tables["Properties"].Rows) { int propertyid = (int)datarow["ID"]; PhotosAdapter.SelectCommand.Parameters["@PropertyID"].Value = propertyid; //lock (CommonFunctions.Connection) PhotosAdapter.Fill(PhotosSet); foreach (System.Data.DataRow datarow2 in PhotosSet.Tables["PropertyPhotos"].Rows) { if (System.IO.File.Exists(Request.PhysicalApplicationPath + System.Configuration.ConfigurationManager.AppSettings["ImagesSubfolderPath"] + datarow2["FileName"])) { System.IO.File.Delete(Request.PhysicalApplicationPath + System.Configuration.ConfigurationManager.AppSettings["ImagesSubfolderPath"] + datarow2["FileName"]); } } } UserSet.Tables["Users"].Rows[0].Delete(); //lock (CommonFunctions.Connection) UserAdapter.Update(UserSet); } Response.Redirect(backlinkurl); }
private void saveClick(object sender, System.EventArgs e) { try { DataSet changedData = territoriesDataset1.GetChanges(); if (changedData == null) // no changes to update { return; } // check for errors DataTable dt = changedData.Tables[0]; DataRow[] badRows = dt.GetErrors(); if (badRows.Length == 0) { // no errors, update database territoriesConnection.Open(); int numRows = territoriesAdapter.Update(changedData); territoriesConnection.Close(); MessageBox.Show("Updated " + numRows + " rows", "Success"); territoriesDataset1.AcceptChanges(); } else { // find errors and inform user string errorMsg = null; foreach (DataRow row in badRows) { foreach (DataColumn col in row.GetColumnsInError()) { errorMsg += row.GetColumnError(col) + "\n"; } } MessageBox.Show("Errors in data: " + errorMsg, "Please fix", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); territoriesDataset1.RejectChanges(); territoriesConnection.Close(); } }
protected override void DataAdapterUpdate(System.Data.DataSet tabela, string strNomeTabela) { if (m_bUserCanUpdateDB) { OpenConnection(); if (m_Connection.State == System.Data.ConnectionState.Open) { if (tabela.GetChanges() != null) { m_Transaction = m_Connection.BeginTransaction(); m_CommandSelect.Transaction = m_Transaction; try { //HACK:Testing timeout if (m_DataAdapter.InsertCommand != null) { m_DataAdapter.InsertCommand.CommandTimeout = 600; } if (m_DataAdapter.UpdateCommand != null) { m_DataAdapter.UpdateCommand.CommandTimeout = 600; } if (m_DataAdapter.DeleteCommand != null) { m_DataAdapter.DeleteCommand.CommandTimeout = 600; } //END m_DataAdapter.Update(tabela, strNomeTabela); m_Transaction.Commit(); DataPersist = false; } catch (System.Exception eEcp) { m_excError = eEcp; ShowDialogUpdateError(strNomeTabela); m_Transaction.Rollback(); } } } CloseConnection(); } }
private void button1_Click_3(object sender, EventArgs e) { using (System.Data.SqlClient.SqlDataAdapter DA = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM Folders WHERE FolderID > 85", ConnectionStrings.LightConnectionString)) { using (System.Data.SqlClient.SqlCommandBuilder CB = new System.Data.SqlClient.SqlCommandBuilder(DA)) { using (DataTable DT = new DataTable()) { DA.Fill(DT); for (int i = 0; i < DT.Rows.Count; i++) { DT.Rows[i]["FTPHost"] = true; } DA.Update(DT); } } } }
public bool Save(DataSetMuseum dataSet, Connection conn, Transaction tr) { dataAdapter = new System.Data.SqlClient.SqlDataAdapter(); dataAdapter.InsertCommand = new System.Data.SqlClient.SqlCommand(string.Format("INSERT INTO Exhibit_Exhibition (ExhibitID, ExhibitionID) " + "VALUES (@ExhibitID, @ExhibitionID);"), conn.getConnection(), tr.getTransaction()); dataAdapter.InsertCommand.Parameters.Add("@ExhibitID", SqlDbType.Int, 11, "ExhibitID"); dataAdapter.InsertCommand.Parameters.Add("@ExhibitionID", SqlDbType.Int, 11, "ExhibitionID"); dataAdapter.UpdateCommand = new System.Data.SqlClient.SqlCommand(string.Format("UPDATE Exhibit_Exhibition SET ExhibitID = @ExhibitID WHERE ExhibitionID = @ExhibitionID;"), conn.getConnection(), tr.getTransaction()); dataAdapter.UpdateCommand.Parameters.Add("@ExhibitID", SqlDbType.Int, 11, "ExhibitID"); dataAdapter.UpdateCommand.Parameters.Add("@ExhibitionID", SqlDbType.Int, 11, "ExhibitionID"); dataAdapter.DeleteCommand = new System.Data.SqlClient.SqlCommand(string.Format("DELETE FROM Exhibit_Exhibition WHERE ID = @ID;"), conn.getConnection(), tr.getTransaction()); dataAdapter.DeleteCommand.Parameters.Add("@ExhibitID", SqlDbType.Int, 11, "ExhibitID"); dataAdapter.DeleteCommand.Parameters.Add("@ExhibitionID", SqlDbType.Int, 11, "ExhibitionID"); var countUpdate = dataAdapter.Update(dataSet, dataSet.Exhibit_Exhibition.TableName); return((countUpdate > 0) ? true : false); }
/// <summary> /// 修改数据库 /// </summary> /// <param name="sql"></param> /// <param name="tb"></param> private void databaseupdate(string sql, DataTable tb) { //数据更行到数据库 System.Data.SqlClient.SqlConnection connect = new System.Data.SqlClient.SqlConnection(); connect.ConnectionString = TrasenFrame.Forms.FrmMdiMain.Database.ConnectionString;// " server=x6x8-20100320QL\\SQLEXPRESS;database=trasen_Emr_test;UID=sa;Password=sa8920993"; connect.Open(); System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(); adapter.SelectCommand = new System.Data.SqlClient.SqlCommand(sql, connect); System.Data.SqlClient.SqlCommandBuilder sqlcom = new System.Data.SqlClient.SqlCommandBuilder(adapter); DataTable newtb = new DataTable(); newtb.TableName = "tb"; adapter.Fill(newtb); adapter.InsertCommand = sqlcom.GetInsertCommand(); adapter.DeleteCommand = sqlcom.GetDeleteCommand(); adapter.UpdateCommand = sqlcom.GetUpdateCommand(); int i = adapter.Update(tb); tb.AcceptChanges(); sqlcom.RefreshSchema(); connect.Close(); }
public bool Save(DataSetMuseum dataSet, Connection conn, Transaction tr) { dataAdapter = new System.Data.SqlClient.SqlDataAdapter(); dataAdapter.InsertCommand = new System.Data.SqlClient.SqlCommand(string.Format("INSERT INTO Exhibit (Name, CategoryID, CreatedDate, AppearanceDate, Photo, Description, CrutchID) " + "VALUES (@Name, @CategoryID, @CreatedDate, @AppearanceDate, @Photo, @Description, @CrutchID);"), conn.getConnection(), tr.getTransaction()); //dataAdapter.InsertCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.InsertCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name"); dataAdapter.InsertCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 11, "CategoryID"); dataAdapter.InsertCommand.Parameters.Add("@CreatedDate", SqlDbType.NVarChar, 50, "CreatedDate"); dataAdapter.InsertCommand.Parameters.Add("@AppearanceDate", SqlDbType.DateTime, 11, "AppearanceDate"); dataAdapter.InsertCommand.Parameters.Add("@Photo", SqlDbType.NVarChar, 255, "Photo"); dataAdapter.InsertCommand.Parameters.Add("@Description", SqlDbType.NText, 10000, "Description"); dataAdapter.InsertCommand.Parameters.Add("@CrutchID", SqlDbType.Int, 11, "CrutchID"); dataAdapter.UpdateCommand = new System.Data.SqlClient.SqlCommand(string.Format("UPDATE Exhibit SET Name = @Name, CategoryID = @CategoryID, CreatedDate = @CreatedDate, AppearanceDate = @AppearanceDate, Photo = @Photo, Description = @Description, CrutchID = @CrutchID WHERE ID = @ID;"), conn.getConnection(), tr.getTransaction()); dataAdapter.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name"); dataAdapter.UpdateCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 11, "CategoryID"); dataAdapter.UpdateCommand.Parameters.Add("@CreatedDate", SqlDbType.NVarChar, 50, "CreatedDate"); dataAdapter.UpdateCommand.Parameters.Add("@AppearanceDate", SqlDbType.DateTime, 11, "AppearanceDate"); dataAdapter.UpdateCommand.Parameters.Add("@Photo", SqlDbType.NVarChar, 255, "Photo"); dataAdapter.UpdateCommand.Parameters.Add("@Description", SqlDbType.NText, 10000, "Description"); dataAdapter.UpdateCommand.Parameters.Add("@CrutchID", SqlDbType.Int, 11, "CrutchID"); dataAdapter.DeleteCommand = new System.Data.SqlClient.SqlCommand(string.Format("DELETE FROM Exhibit WHERE ID = @ID;"), conn.getConnection(), tr.getTransaction()); dataAdapter.DeleteCommand.Parameters.Add("@ID", SqlDbType.Int, 11, "ID"); dataAdapter.DeleteCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name"); dataAdapter.DeleteCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 11, "CategoryID"); dataAdapter.DeleteCommand.Parameters.Add("@CreatedDate", SqlDbType.NVarChar, 50, "CreatedDate"); dataAdapter.DeleteCommand.Parameters.Add("@AppearanceDate", SqlDbType.DateTime, 11, "AppearanceDate"); dataAdapter.DeleteCommand.Parameters.Add("@Photo", SqlDbType.NVarChar, 255, "Photo"); dataAdapter.DeleteCommand.Parameters.Add("@Description", SqlDbType.NText, 10000, "Description"); dataAdapter.DeleteCommand.Parameters.Add("@CrutchID", SqlDbType.Int, 11, "CrutchID"); var countUpdate = dataAdapter.Update(dataSet, dataSet.Exhibit.TableName); return((countUpdate > 0) ? true : false); }
public static void UpdateDataTable(System.Data.DataTable dt, string strSQL) { using (System.Data.Common.DbConnection connection = GetConnection()) { using (System.Data.Common.DbDataAdapter daInsertUpdate = new System.Data.SqlClient.SqlDataAdapter()) { using (System.Data.Common.DbCommand cmdSelect = connection.CreateCommand()) { cmdSelect.CommandText = strSQL; System.Data.Common.DbCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(); cb.DataAdapter = daInsertUpdate; daInsertUpdate.SelectCommand = cmdSelect; daInsertUpdate.UpdateCommand = cb.GetUpdateCommand(); daInsertUpdate.DeleteCommand = cb.GetDeleteCommand(); daInsertUpdate.InsertCommand = cb.GetInsertCommand(); daInsertUpdate.Update(dt); } // End Using cmdSelect } // End Using daInsertUpdate } // End Using con }
private void btnOk_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlTransaction tran = null; try{ this.receipt["Vendor"] = this.cbxVendors.SelectedValue; this.receipt["VendorName"] = this.cbxVendors.Text; this.receipt["Paid"] = this.dtpPeceiptDate.Value; this.receipt["Price"] = this.total_price; this.receipt["Discount"] = this.total_discount; if ( ( this.cbxDiscountCards.SelectedValue == null ) || System.Convert.IsDBNull( this.cbxDiscountCards.SelectedValue ) ) this.receipt["DiscountCard"] = System.Convert.DBNull; else this.receipt["DiscountCard"] = this.cbxDiscountCards.SelectedValue; this.receipt["Number"] = this.tbxReceiptNumber.Text; this.receipt["Comment"] = this.tbxComment.Text; this.receipt["Created"] = System.DateTime.Now; this.receipt["Updated"] = System.DateTime.Now; this.cConnection.Open(); using ( tran = this.cConnection.BeginTransaction()){ System.Data.SqlClient.SqlCommand rcmd = new System.Data.SqlClient.SqlCommand(); if (this.type == ReceiptType.New || this.type == ReceiptType.Cloned ){ rcmd = Purchases.Receipt.InsertCommand(this.receipt); }else{ rcmd = Purchases.Receipt.UpdateCommand(this.receipt); } rcmd.Connection = this.cConnection; rcmd.Transaction = tran; rcmd.ExecuteNonQuery(); System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(); sda.InsertCommand = Purchases.ReceiptContent.InsertCommand(); sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Purchases.ReceiptContent.UpdateCommand(); sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.DeleteCommand = Purchases.ReceiptContent.DeleteCommand(); sda.DeleteCommand.Connection = this.cConnection; sda.DeleteCommand.Transaction = tran; sda.Update(this.contents); //TODO Update discount card balance tran.Commit(); } this.cConnection.Close(); this.DialogResult = DialogResult.OK; }catch( System.Exception ex ){ MessageBox.Show(ex.Message); if( tran != null ) tran.Rollback(); }finally{ if (this.cConnection.State == ConnectionState.Open) this.cConnection.Close(); } }
public static void InsertUpdateDataTable(string strTableName, System.Data.DataTable dt) { string connectionString = GetConnectionString(); string strSQL = string.Format("SELECT * FROM [{0}] WHERE 1 = 2 ", strTableName.Replace("]", "]]")); using (System.Data.Common.DbConnection connection = GetConnection()) { using (System.Data.Common.DbDataAdapter daInsertUpdate = new System.Data.SqlClient.SqlDataAdapter()) { using (System.Data.Common.DbCommand cmdSelect = connection.CreateCommand()) { cmdSelect.CommandText = strSQL; System.Data.Common.DbCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(); cb.DataAdapter = daInsertUpdate; daInsertUpdate.SelectCommand = cmdSelect; daInsertUpdate.InsertCommand = cb.GetInsertCommand(); daInsertUpdate.UpdateCommand = cb.GetUpdateCommand(); daInsertUpdate.DeleteCommand = cb.GetDeleteCommand(); daInsertUpdate.Update(dt); } // End Using cmdSelect } // End Using daInsertUpdate } // End Using connection }
bool ImportSaved(string filename, out string error) { bool done = false; error = ""; try { System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(); command.CommandText = "SELECT * FROM Purchases.Receipts\n" + "SELECT * FROM Purchases.ReceiptContents\n" + "SELECT * FROM Producer.Products\n" + "SELECT * FROM Producer.ProductTypes\n" + "SELECT * FROM Producer.Categories\n" + "SELECT * FROM Purchases.Vendors\n" + "SELECT * FROM Purchases.DiscountCards\n" + "SELECT * FROM Purchases.CardBalance\n" + "SELECT * FROM Producer.Makers\n" + "SELECT * FROM Persons.Users\n" ; command.Connection = this.cConnection; System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(command); string table = "Receipts"; System.Data.DataSet dst_ds = new System.Data.DataSet(table); sda.Fill(dst_ds); dst_ds.Tables[0].TableName = "Receipts"; dst_ds.Tables[1].TableName = "ReceiptContents"; dst_ds.Tables[2].TableName = "Products"; dst_ds.Tables[3].TableName = "ProductTypes"; dst_ds.Tables[4].TableName = "Categories"; dst_ds.Tables[5].TableName = "Vendors"; dst_ds.Tables[6].TableName = "DiscountCards"; dst_ds.Tables[7].TableName = "CardBalance"; dst_ds.Tables[8].TableName = "Makers"; dst_ds.Tables[9].TableName = "Users"; System.Data.DataSet rec_ds = new System.Data.DataSet(table); string xsd_file = ""; string xml_file = ""; if (filename.EndsWith(".xsd", true, System.Globalization.CultureInfo.CurrentCulture)) { xsd_file = filename; xml_file = filename.Substring(0, filename.Length - 4) + ".xml"; } else if (filename.EndsWith(".xml", true, System.Globalization.CultureInfo.CurrentCulture)) { xml_file = filename; xsd_file = filename.Substring(0, filename.Length - 4) + ".xsd"; } else { xml_file = filename + ".xml"; xsd_file = filename + ".xsd"; } rec_ds.ReadXmlSchema(xsd_file); rec_ds.ReadXml(xml_file); dst_ds.Merge(rec_ds, true); // dst_ds. System.Data.DataSet cng_ds = dst_ds.GetChanges(DataRowState.Added); if (cng_ds != null) { this.cConnection.Open(); System.Data.SqlClient.SqlTransaction tran = this.cConnection.BeginTransaction(); sda = new System.Data.SqlClient.SqlDataAdapter(); // Merging vendors sda.InsertCommand = Vault.Users.Insert(); sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Vault.Users.Update(); sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.Update(cng_ds.Tables["Users"]); // Merging vendors sda.InsertCommand = Purchases.Vendor.Insert(); sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Purchases.Vendor.Update(); sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.Update(cng_ds.Tables["Vendors"]); // Merging discount cards sda.InsertCommand = Purchases.DiscountCard.Insert(); sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Purchases.DiscountCard.Update(); sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.Update(cng_ds.Tables["DiscountCards"]); // Merging discount cards balance sda.InsertCommand = Purchases.CardBalance.Insert(); sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Purchases.CardBalance.Update(); sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.Update(cng_ds.Tables["CardBalance"]); // Merging makers sda.InsertCommand = Producer.Maker.Insert(); sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Producer.Maker.Update(); sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.Update(cng_ds.Tables["Makers"]); // Merging categories sda.InsertCommand = Producer.Categories.Insert(); sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Producer.Categories.Update(); sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.Update(cng_ds.Tables["Categories"]); // Merging products' types sda.InsertCommand = Producer.ProductTypes.Insert(); sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Producer.ProductTypes.Update(); sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.Update(cng_ds.Tables["ProductTypes"]); // Merging products sda.InsertCommand = Producer.Product.Insert(); sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Producer.Product.Update(); sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.Update(cng_ds.Tables["Products"]); // Merging receipts sda.InsertCommand = Purchases.Receipt.InsertCommand(null); // null as we insert all new rows sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Purchases.Receipt.UpdateCommand(null); // null as we update all rows sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.Update(cng_ds.Tables["Receipts"]); // Merging receipts' contents sda.InsertCommand = Purchases.ReceiptContent.InsertCommand(); sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Purchases.ReceiptContent.UpdateCommand(); sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.Update(cng_ds.Tables["ReceiptContents"]); tran.Commit(); this.cConnection.Close(); //this.receipts.Rows.Add(new_row); //this.AddNewRow(this.receipts.Rows.Count - 1, new_row); //this.CalculateTotal(false); } done = true; } catch (Exception ex) { error = ex.Message; } finally { if (this.cConnection.State != ConnectionState.Closed) this.cConnection.Close(); } return done; }
public DataTable SaveData() { _detailRow.EndEdit(); //Will return a data table so //can merge with typed data table //Todo: Refactor string connectionString = string.Format( @"Data Source=.\sqlexpress; Initial Catalog={0}; Integrated Security=true", "Northwind"); string commandText = "select * from customers"; System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(commandText,connectionString); //http://stackoverflow.com/q/6569012/139698 System.Data.SqlClient.SqlCommandBuilder projectBuilder = new System.Data.SqlClient.SqlCommandBuilder(da); DataTable newDt = _dataSources[0].GetChanges(DataRowState.Modified); da.Update(newDt); _dataSources[0].AcceptChanges(); return _dataSources[0]; }
private void btnOk_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlTransaction tran = null; try{ this.receipt["Vendor"] = this.cbxVendors.SelectedValue; this.receipt["VendorName"] = this.cbxVendors.Text; this.receipt["Payed"] = dtpPeceiptDate.Value; this.receipt["Price"] = this.total_price; this.receipt["Discount"] = this.total_discount; if ( ( this.cbxDiscountCards.SelectedValue == null ) || System.Convert.IsDBNull( this.cbxDiscountCards.SelectedValue ) ) this.receipt["DiscountCard"] = System.Convert.DBNull; else this.receipt["DiscountCard"] = this.cbxDiscountCards.SelectedValue; this.receipt["Number"] = this.tbxReceiptNumber.Text; this.receipt["Comment"] = this.tbxComment.Text; this.receipt["Created"] = System.DateTime.Now; this.receipt["Updated"] = System.DateTime.Now; this.cConnection.Open(); using ( tran = this.cConnection.BeginTransaction()){ // using ( System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope() ){ // System.Data.SqlClient.SqlDataAdapter sdarc = new System.Data.SqlClient.SqlDataAdapter(); // System.Data.SqlClient.SqlDataAdapter rsda = new System.Data.SqlClient.SqlDataAdapter(); System.Data.SqlClient.SqlCommand rcmd = new System.Data.SqlClient.SqlCommand(); if (this.isNew){ //sdarc.InsertCommand = Purchases.Receipt.InsertCommand(this.receipt); //sdarc.InsertCommand.Connection = this.cConnection; //sdarc.InsertCommand.Transaction = tran; System.Data.SqlClient.SqlCommand idcmd = new System.Data.SqlClient.SqlCommand("SELECT NEWID() AS MaxID FROM Purchases.Receipts", this.cConnection, tran); // int receipt_id = (int)idcmd.ExecuteScalar(); this.receipt["ReceiptID"] = idcmd.ExecuteScalar(); rcmd = Purchases.Receipt.InsertCommand(this.receipt); for (int k = 0; k < this.contents.Rows.Count; k++){ this.contents.Rows[k]["ReceiptID"] = this.receipt["ReceiptID"]; } }else{ //sdarc.UpdateCommand = Purchases.Receipt.UpdateCommand(this.receipt); //sdarc.UpdateCommand.Connection = this.cConnection; //sdarc.UpdateCommand.Transaction = tran; rcmd = Purchases.Receipt.UpdateCommand(this.receipt); // rsda.UpdateCommand = rcmd; } rcmd.Connection = this.cConnection; rcmd.Transaction = tran; rcmd.ExecuteNonQuery(); // int id = (int)rcmd.ExecuteScalar(); // System.Data.SqlClient.SqlParameter p = rcmd.Parameters.Add("@ReceiptID", SqlDbType.Int); // p.Direction = ParameterDirection.Output; // rsda.Update(new DataRow[] { this.receipt }); // sdarc.Update(new DataRow[] { this.receipt } ); System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(); sda.InsertCommand = Purchases.ReceiptContent.InsertCommand(); sda.InsertCommand.Connection = this.cConnection; sda.InsertCommand.Transaction = tran; sda.UpdateCommand = Purchases.ReceiptContent.UpdateCommand(); sda.UpdateCommand.Connection = this.cConnection; sda.UpdateCommand.Transaction = tran; sda.DeleteCommand = Purchases.ReceiptContent.DeleteCommand(); sda.DeleteCommand.Connection = this.cConnection; sda.DeleteCommand.Transaction = tran; sda.Update(this.contents); //TODO Update discount card tran.Commit(); } this.cConnection.Close(); this.DialogResult = DialogResult.OK; }catch( System.Exception ex ){ MessageBox.Show(ex.Message); if( tran != null ) tran.Rollback(); }finally{ if (this.cConnection.State == ConnectionState.Open) this.cConnection.Close(); } }