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