private void Search() { List <object> list = new List <object>(); string DocNum = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString(); System.Data.SqlClient.SqlConnection oleConnection = new System.Data.SqlClient.SqlConnection(sql); oleConnection.Open(); DataSet dsSql = new DataSet(); System.Data.SqlClient.SqlDataAdapter oa = new System.Data.SqlClient.SqlDataAdapter("select * from StorageChecking", oleConnection); oa.Fill(dsSql); System.Data.SqlClient.SqlCommandBuilder scb = new System.Data.SqlClient.SqlCommandBuilder(oa); dtn = dsSql.Tables[0]; DateTime date = DateTime.Now; int j = dtn.Rows.Count; var dtnGroup = from i in dtn.AsEnumerable() group i by new { t1 = i.Field <string>("DocumentNum") } into g select new HisCheckInv { operationtime = g.FirstOrDefault().Field <DateTime>("operationTime"), TotalRecord = g.Count(), TotalPrice = g.Sum(p => p.Field <decimal>("pricecount")), operationuser = g.FirstOrDefault().Field <string>("operationUser"), DocumentNum = g.FirstOrDefault().Field <string>("DocumentNum").ToString() }; if (dtnGroup != null) { dataGridView1.DataSource = dtnGroup.ToList(); } oleConnection.Close(); }
private void PCDSearch() { if (textBox1.Text == "" || textBox1.Text == null) { return; } System.Data.SqlClient.SqlConnection oleConnection = new System.Data.SqlClient.SqlConnection(sql); oleConnection.Open(); DataSet dsSql = new DataSet(); System.Data.SqlClient.SqlDataAdapter oa = new System.Data.SqlClient.SqlDataAdapter("select * from StorageChecking where DocumentNum like '%" + textBox1.Text.Trim() + "%'", oleConnection); oa.Fill(dsSql); System.Data.SqlClient.SqlCommandBuilder scb = new System.Data.SqlClient.SqlCommandBuilder(oa); dtn = dsSql.Tables[0]; DateTime date = DateTime.Now; var dtnGroup = from i in dtn.AsEnumerable() group i by new { t1 = i.Field <string>("DocumentNum") } into g select new HisCheckInv { operationtime = g.FirstOrDefault().Field <DateTime>("operationTime"), TotalRecord = g.Count(), TotalPrice = g.Sum(p => p.Field <decimal>("pricecount")), operationuser = g.FirstOrDefault().Field <string>("operationUser"), DocumentNum = g.FirstOrDefault().Field <string>("DocumentNum").ToString() }; if (dtnGroup != null) { dataGridView1.DataSource = dtnGroup.ToList(); } oleConnection.Close(); }
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 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"); } }
public string FetchActiveBookings() { string SQL = ""; string BookingLine = ""; string BookingNumber = ""; string CustomerName = ""; SQL = "Select Distinct [Original Booking], [Customer Name] From [Bookings] Order By [Original Booking]"; DBConnect = new System.Data.SqlClient.SqlConnection(DatabaseHelperCeres.StrConnWTCCeres); DBConnect.Open(); var DbObjDataTable = new System.Data.DataTable(); var DbObjAdapter = new System.Data.SqlClient.SqlDataAdapter(SQL, DBConnect); var DbObjCommand = new System.Data.SqlClient.SqlCommandBuilder(DbObjAdapter); DbObjAdapter.Fill(DbObjDataTable); foreach (DataRow BookNum in DbObjDataTable.Rows) { BookingNumber = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(BookNum["Original Booking"]), 40); CustomerName = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(BookNum["Customer Name"]), 50); BookingLine += Conversions.ToString('\0') + BookingNumber + CustomerName + Conversions.ToString('\0'); } DBConnect.Close(); return(BookingLine); }
private void button5_Click(object sender, EventArgs e)//update { System.Data.SqlClient.SqlCommandBuilder cb; cb = new System.Data.SqlClient.SqlCommandBuilder(da); System.Data.DataRow dRow2 = ds1.Tables["bedrijf"].Rows[inc]; dRow2[1] = textBox1.Text; //naam dRow2[2] = textBox2.Text; //voorvoegsel dRow2[3] = textBox3.Text; //zoeknaam dRow2[4] = textBox4.Text; //straat dRow2[5] = textBox5.Text; dRow2[6] = textBox6.Text; dRow2[7] = textBox7.Text; dRow2[8] = textBox8.Text; dRow2[9] = textBox9.Text; dRow2[10] = textBox10.Text; dRow2[11] = textBox11.Text; dRow2[12] = textBox12.Text; dRow2[13] = textBox13.Text; dRow2[14] = textBox14.Text; dRow2[15] = textBox15.Text; dRow2[16] = textBox16.Text; dRow2[17] = textBox17.Text; dRow2[18] = textBox18.Text; dRow2[19] = textBox19.Text; dRow2[20] = textBox20.Text; dRow2[21] = textBox21.Text; da.Update(ds1, "bedrijf"); MessageBox.Show("record bijgewerkt"); }
private string FetchSteamShipLines() { string SSLineCode = ""; string AllSSlines = ""; // 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() var DbObjDataTable = new DataTable(); var DbObjAdapter = new System.Data.SqlClient.SqlDataAdapter("Select CompanyCode, CompanyName From VW_CompanyCompanyType where DescCompanyType = 'Steamship Line' Order By CompanyCode ", DBConnect); var DbObjCommand = new System.Data.SqlClient.SqlCommandBuilder(DbObjAdapter); DbObjAdapter.Fill(DbObjDataTable); ComboSSLineList.Items.Clear(); ComboSSLineList.Items.Add("--------[ALL STEAM SHIP LINES]--------"); foreach (DataRow SSLineList in DbObjDataTable.Rows) { SSLineCode = Strings.RTrim(SSLineList["CompanyCode"].ToString().ToUpper()); ComboSSLineList.Items.Add("(" + SSLineCode + ") " + SSLineList["CompanyName"].ToString().ToUpper()); AllSSlines += MdlContainerManagement.DoFunctions.PadToString(SSLineCode, 10) + MdlContainerManagement.DoFunctions.PadToString(SSLineList["CompanyName"].ToString(), 40); } // DBConnect.Close() return(AllSSlines); }
/// <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(); }
public void Close() { if (this.DataAdapter != null) { try { using (this.DataAdapter) { } } catch { } } if (this.CommandBuilder != null) { try { using (this.CommandBuilder) { } } catch { } } if (this.Connection != null) { try { using (this.Connection) this.Connection.Close(); } catch { } } this.DataAdapter = null; this.CommandBuilder = null; this.Connection = null; }
public DbCommandBuilder GetCommandBuilder(DbDataAdapter adapter) { string dbType = db.GetDataAdapter().GetType().ToString(); Type createType = null; switch (dbType) { case "System.Data.Odbc.OdbcDataAdapter": createType = new System.Data.Odbc.OdbcCommandBuilder().GetType(); break; case "System.Data.OleDb.OleDbDataAdapter": createType = new System.Data.OleDb.OleDbCommandBuilder().GetType(); break; case "System.Data.SqlClient.SqlDataAdapter": createType = new System.Data.SqlClient.SqlCommandBuilder().GetType(); break; /*case "System.Data.SqlServerCe.SqlCeCommand": * createType = "System.Data.SqlServerCe.SqlCeCommandBuilder"; * break;*/ default: return(null); } if (adapter != null) { return((DbCommandBuilder)System.Activator.CreateInstance(createType, adapter)); } else { return((DbCommandBuilder)System.Activator.CreateInstance(createType)); } }
//update method public void UpdateDB(DataSet ds, string table) { //create a command builder to reconnect to the database System.Data.SqlClient.SqlCommandBuilder cb; //set the comamnd builder to be our existing dataadapter cb = new System.Data.SqlClient.SqlCommandBuilder(Da); Da.Update(ds, table); }
//needed to update our DB public void UpdateDB(System.Data.DataSet dataSet) { //Command Builder object for auto update System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(da1); //Update our DB cb.DataAdapter.Update(dataSet.Tables[0]); }
public void UpdateDatabase(System.Data.DataSet ds) { System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon); System.Data.SqlClient.SqlDataAdapter da_1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con); System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(da_1); cb.DataAdapter.Update(ds.Tables[0]); }
//update method public void UpdateDB(DataSet ds, string table) { System.Data.SqlClient.SqlCommandBuilder cb; //if the table is students perform the update on the students table in the database if (table == "Students") { cb = new System.Data.SqlClient.SqlCommandBuilder(DaStudents); DaStudents.Update(ds, table); } }
private void loadgrid(int reference) { string query = null; switch (reference) { case (1): //showing all query = "SELECT [ID] as [Cheque No], [Name], FORMAT([Date], 'dd/MM/yyyy') as [Date], [Pay], [Rupees], [Rs] FROM tblCheque"; lblGridCaption.Text = "Showing all records"; break; case (2): //calenderpick query = "SELECT [ID] as [Cheque No], [Name], FORMAT([Date], 'dd/MM/yyyy') as [Date], [Pay], [Rupees], [Rs] FROM tblCheque WHERE [Date] = #" + calendar1.SelectedEventDate + "#"; lblGridCaption.Text = "Showing records on: " + calendar1.SelectedEventDate.ToString("dd/MM/yyyy"); break; case (3): //search var builder = new System.Data.SqlClient.SqlCommandBuilder(); string fieldname = builder.QuoteIdentifier(cmbLookin.Text); if ((cmbLookin.Text.Trim() == "") || (cmbLookin.Text == "Cheque No")) { fieldname = "[ID]"; } query = "SELECT [ID] as [Cheque No], [Name], FORMAT([Date], 'dd/MM/yyyy') as [Date], [Pay], [Rupees], [Rs] FROM tblCheque WHERE " + fieldname + " LIKE '%' + @value + '%'"; lblGridCaption.Text = "Search " + fieldname + " - " + txtSearch.Text; break; } try { dset.Clear(); OleDbCommand cmd = new OleDbCommand(query, dbcon); cmd.Parameters.Add(new OleDbParameter("value", txtSearch.Text)); //parameter when use search dadapt = new OleDbDataAdapter(cmd); dadapt.Fill(dset, "tblCheque"); dgvCheque.DataSource = dset; dgvCheque.DataMember = "tblCheque"; dgvCheque.ClearSelection(); selectedrecord[0] = null; selectedrecord[1] = null; selectedrecord[2] = null; selectedrecord[3] = null; selectedrecord[4] = null; btnPrint.Enabled = false; btnEdit.Enabled = false; btnDelete.Enabled = false; } catch (Exception ex) { MessageBox.Show("Exception in loadgrid(): " + ex.Message, "Exception Handler", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void btnUpdate_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlCommandBuilder cb; cb = new System.Data.SqlClient.SqlCommandBuilder(da); System.Data.DataRow dRow2 = ds1.Tables["Materials"].Rows[matid]; dRow2[0] = matid; dRow2[1] = tbCategory.Text; dRow2[2] = tbName.Text; da.Update(ds1, "Materials"); MessageBox.Show("Data Updated!"); }
private string FetchAllDeliveredCans() { string TempLine = ""; var DbObjDataTable = new DataTable(); var DbObjAdapter = new System.Data.SqlClient.SqlDataAdapter("Select * From [Containers Ready Delivered] Order By [Delivered Booking]", DatabaseHelperCeres.StrConnWTCCeres); var DbObjCommand = new System.Data.SqlClient.SqlCommandBuilder(DbObjAdapter); DbObjAdapter.Fill(DbObjDataTable); foreach (DataRow ReadyDeliverRow in DbObjDataTable.Rows) { TempLine += DoFunctions.PadToString(Conversions.ToString(ReadyDeliverRow["Delivered Booking"]), 40) + DoFunctions.MKI(Conversions.ToInteger(ReadyDeliverRow["Delivered"])); } return(TempLine); }
private void button6_Click(object sender, EventArgs e)//delete { System.Data.SqlClient.SqlCommandBuilder cb; cb = new System.Data.SqlClient.SqlCommandBuilder(da); ds1.Tables["bedrijf"].Rows[inc].Delete(); MaxRows--; inc = 0; NavigateRecord(); da.Update(ds1, "bedrijf"); MessageBox.Show("record verwijderd"); }
private void btnDelete_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlCommandBuilder cb; cb = new System.Data.SqlClient.SqlCommandBuilder(da); ds1.Tables["Workers"].Rows[inc].Delete(); MaxRows--; inc = 0; NavigateRecords(); da.Update(ds1, "Workers"); MessageBox.Show("Record Deleted."); }
// Private Sub HarvesterAlive1_FetchHarvesterStatus() // HarvesterAlive1.TakeLastActive(FetchLastActive(DatabaseHelperCeres.StrConnWTCCeres)) // End Sub private DateTime FetchLastActive(string DBConnect) { try { var DbObjDataTable = new DataTable(); var DbObjAdapter = new System.Data.SqlClient.SqlDataAdapter("Select * From [Harvester Alive]", DBConnect); var DbObjCommand = new System.Data.SqlClient.SqlCommandBuilder(DbObjAdapter); DbObjAdapter.Fill(DbObjDataTable); if (DbObjDataTable.Rows.Count == 1) { return(Conversions.ToDate(DbObjDataTable.Rows[0]["Last Alive"])); } else { return(default);
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); }
// ************************************************************************ #endregion #region Constructor public clsDataBaseAccessSqlServer(ref mdlTratamentoErro.clsTratamentoErro TratadorErro, string strHost, string strPort, string strDataBaseName, string strUser, string strPassword) : base(ref TratadorErro, strUser, strPassword) { m_enumConnectionType = ConnectionType.PATH; m_strHost = strHost; m_strPort = m_strPort; m_strDataBaseName = strDataBaseName; GenerateConnectionString(); m_DataAdapter = new System.Data.SqlClient.SqlDataAdapter(); m_CommandSelect = new System.Data.SqlClient.SqlCommand(); m_CommandSelect.Connection = m_Connection; m_DataAdapter.SelectCommand = m_CommandSelect; m_CommandBuilder = new System.Data.SqlClient.SqlCommandBuilder(m_DataAdapter); m_nDefaultPort = 1433; }
/// <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 inputAbsBox_KeyDown(object sender, KeyEventArgs e) { if (e.Key == Key.Enter) { var value = ItemRow[ColumnName]; ColumnValue.Absolute = (T)Convert.ChangeType(value, typeof(T)); ItemRow[ColumnName] = value; var parent = Application.Current.Windows[0] as MainWindow; var builder = new System.Data.SqlClient.SqlCommandBuilder(inventory.Adapter); inventory.Adapter.UpdateCommand = builder.GetUpdateCommand(); inventory.Update(ItemRow); parent.DropClickMenuIfActive(); } }
public U_Dt_Zwd(string strSQL, U_Zwd.U_Db_Zwd pudb) { udb = pudb; udb.Connect(); dta = new System.Data.SqlClient.SqlDataAdapter(strSQL, udb.Connection); cmd = new System.Data.SqlClient.SqlCommandBuilder(dta); dst = new System.Data.DataSet(); dta.Fill(dst); dt = dst.Tables[0]; dstDeleted = new System.Data.DataSet(); dta.Fill(dstDeleted); dtDeleted = dstDeleted.Tables[0]; udb.DisConnect(); }
public U_Dt_Zwd(string strSQL, U_Zwd.U_Db_Zwd pudb) { udb = pudb; udb.Connect(); dta = new System.Data.SqlClient.SqlDataAdapter(strSQL, udb.Connection); cmd = new System.Data.SqlClient.SqlCommandBuilder(dta); dst = new System.Data.DataSet(); dta.Fill(dst); dt = dst.Tables[0]; dstDeleted = new System.Data.DataSet(); dta.Fill(dstDeleted); dtDeleted = dstDeleted.Tables[0]; udb.DisConnect(); }
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); }
// 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 btnSave_Click(object sender, EventArgs e) { // Connection builder to reconnect to database. System.Data.SqlClient.SqlCommandBuilder cb; cb = new System.Data.SqlClient.SqlCommandBuilder(da); // Set a new datarow DataRow dRow = ds1.Tables["Materials"].NewRow(); // Set data to the new row. dRow[2] = tbName.Text; dRow[1] = tbCategory.Text; // Add the datarow to the dataset. ds1.Tables["Materials"].Rows.Add(dRow); // Update database with dataset. da.Update(ds1, "Materials"); MessageBox.Show("Entry Added"); LoadMaterials(); }
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 string FetchShippingLines() { string ShipLineCode; string ShipLineName; string FinalLine = ""; var DbObjAdapter = new System.Data.SqlClient.SqlDataAdapter("Select Code, Name From Companies where Type = 'Steamship Line' Order by Name Asc", DBConnect); var DbObjCommand = new System.Data.SqlClient.SqlCommandBuilder(DbObjAdapter); var DbObjDataTable = new System.Data.DataTable(); DbObjAdapter.Fill(DbObjDataTable); foreach (DataRow ContainerRow in DbObjDataTable.Rows) { ShipLineCode = Conversions.ToString(ContainerRow["Code"]).ToUpper(); ShipLineName = MdlContainerManagement.DoFunctions.PadToString("(" + Strings.RTrim(ShipLineCode) + ") " + Strings.RTrim(Conversions.ToString(ContainerRow["Name"]).ToUpper()), (short)50); FinalLine += ShipLineCode + ShipLineName; } return(FinalLine); }
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 IDbConnector Read( ISqlCommand sqlCommand, out IDbTable dbTable ) { this.ResetStateProperties(); this.Connection = this.OpenSqlConnection(); using (var cmd = sqlCommand.GenerateSqlCommandInstance <System.Data.SqlClient.SqlCommand>()) { cmd.Connection = this.Connection; this.DataAdapter = this.GetDataAdapter(cmd); this.CommandBuilder = this.GetCommandBuilder(this.DataAdapter); var DataTable = new System.Data.DataTable(); this.DataAdapter.Fill(DataTable); dbTable = new DbTable(DataTable); } this.Connection.Close(); return(this); }
private void btnSave_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlCommandBuilder cb; cb = new System.Data.SqlClient.SqlCommandBuilder(da); DataRow dRow = ds1.Tables["Workers"].NewRow(); dRow[1] = textBox1.Text; dRow[2] = textBox2.Text; dRow[3] = textBox3.Text; ds1.Tables["Workers"].Rows.Add(dRow); MaxRows = MaxRows + 1; inc = MaxRows - 1; da.Update(ds1, "Workers"); NavigateRecords(); MessageBox.Show("Entry Added!"); }
/// <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(); }
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"); }
//protected System.Data.SqlClient.SqlConnection Connection; protected void Page_Load(object sender, System.EventArgs e) { System.Data.SqlClient.SqlCommandBuilder builder = new System.Data.SqlClient.SqlCommandBuilder(PropertiesAdapter); if (propertyid != -1) { //string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; //CommonFunctions.Connection.ConnectionString = connectionstring; PropertiesAdapter.SelectCommand.Parameters["@PropertyID"].Value = propertyid; if (CommonFunctions.SyncFill(PropertiesAdapter, PropertiesSet) > 0) { PropertiesSet.Tables["Properties"].Rows[0]["IfApproved"] = true; PropertiesSet.Tables["Properties"].Rows[0]["PublishedDate"] = DateTime.Now; //lock (CommonFunctions.Connection) PropertiesAdapter.Update(PropertiesSet); } } Response.Redirect(backlinkurl); }
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 }
//To actually update the database public void UpdateDatabase(System.Data.DataSet ds) { System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(da_1);//In order to do an automatic update cb.DataAdapter.Update(ds.Tables[0]);//dataadapter-property.its method -update. }
public void UpdateDatabase(System.Data.DataSet ds) { System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(da_1); cb.DataAdapter.Update(ds.Tables[0]); MessageBox.Show("From Update Database Method."); }
private void btnUpdate_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlCommandBuilder cb; cb = new System.Data.SqlClient.SqlCommandBuilder(da); System.Data.DataRow dRow2 = ds1.Tables["Workers"].Rows[inc]; dRow2[1] = textBox1.Text; dRow2[2] = textBox2.Text; dRow2[3] = textBox3.Text; da.Update(ds1, "Workers"); MessageBox.Show("Data Updated!"); }
public void UpdateDatabase(System.Data.DataSet ds) { System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(dataAdap1); cb.DataAdapter.Update(ds.Tables[0]); }
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 }
/// <summary> /// Update one row /// </summary> /// <param name="pFileString">File String</param> /// <param name="pUpdateSQL">Update SQL Statement</param> /// <param name="pCommand">Command</param> /// <returns>bool</returns> public static bool Update_1_Row(string pFileString, string pSelectSQL, string pKeyString, System.Data.Common.DbDataReader pDataReader) { bool result = false; System.Data.SqlClient.SqlConnection Conn = null; System.Data.OleDb.OleDbConnection ConnOle = null; System.Data.SqlClient.SqlDataAdapter Adapter = null; System.Data.OleDb.OleDbDataAdapter AdapterOle = null; System.Data.SqlClient.SqlCommandBuilder builderSQL = null; System.Data.OleDb.OleDbCommandBuilder builderOLE = null; System.Data.SqlClient.SqlCommand cmdSqL = null; System.Data.Common.DbCommand cmdOle = null; DataSet dataSet = new DataSet(); DataTable Temp = new DataTable(); string ConnectionString = DBReadExecute.ParseConnectionString(pFileString); StringBuilder UpdateSQL = new StringBuilder(); try { Type SQLServerType = Type.GetType("Epi.Data.SqlServer.SqlDBFactory, Epi.Data.SqlServer"); if (DBReadExecute.DataSource.GetType().AssemblyQualifiedName == SQLServerType.AssemblyQualifiedName) { //case DBReadExecute.enumDataSouce.SQLServer: Conn = new System.Data.SqlClient.SqlConnection(ConnectionString); Adapter = new System.Data.SqlClient.SqlDataAdapter(pSelectSQL, Conn); //Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName); Adapter.FillSchema(dataSet, SchemaType.Source); builderSQL = new System.Data.SqlClient.SqlCommandBuilder(Adapter); Conn.Open(); cmdSqL = Conn.CreateCommand(); cmdSqL = builderSQL.GetInsertCommand(); cmdSqL.CommandTimeout = 1500; UpdateSQL.Append("Update "); UpdateSQL.Append(pSelectSQL.Replace("Select * From ", "")); UpdateSQL.Append(" Set "); foreach (System.Data.SqlClient.SqlParameter param in cmdSqL.Parameters) { //string FieldName = param.ParameterName.TrimStart(new char[] { '@' }); string FieldName = param.SourceColumn; try { StringBuilder TUpdateSQL = new StringBuilder(); if (pDataReader[FieldName] != DBNull.Value && !string.IsNullOrEmpty(pDataReader[FieldName].ToString())) { TUpdateSQL.Append("["); TUpdateSQL.Append(FieldName); TUpdateSQL.Append("]="); switch (pDataReader[FieldName].GetType().ToString()) { case "System.Boolean": if (Convert.ToBoolean(pDataReader[FieldName]) == false) { TUpdateSQL.Append("0"); } else { TUpdateSQL.Append("1"); } break; case "System.Int32": case "System.Decimal": case "System.Double": case "System.Single": case "System.Byte": TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''")); break; default: TUpdateSQL.Append("'"); TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''")); TUpdateSQL.Append("'"); break; } TUpdateSQL.Append(","); } UpdateSQL.Append(TUpdateSQL); } catch (Exception ex) { // do nothing } } UpdateSQL.Length = UpdateSQL.Length - 1; UpdateSQL.Append(" Where "); UpdateSQL.Append(pKeyString); //builderOLE = null; cmdSqL = null; cmdSqL = Conn.CreateCommand(); cmdSqL.CommandText = UpdateSQL.ToString(); cmdSqL.ExecuteNonQuery(); //break; } else { //case DBReadExecute.enumDataSouce.MSAccess: //case DBReadExecute.enumDataSouce.MSAccess2007: //case DBReadExecute.enumDataSouce.MSExcel: //case DBReadExecute.enumDataSouce.MSExcel2007: ConnOle = new System.Data.OleDb.OleDbConnection(ConnectionString); AdapterOle = new System.Data.OleDb.OleDbDataAdapter(pSelectSQL, ConnOle); //Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName); AdapterOle.FillSchema(dataSet, SchemaType.Source); AdapterOle.Fill(Temp); builderOLE = new System.Data.OleDb.OleDbCommandBuilder(); builderOLE.DataAdapter = AdapterOle; ConnOle.Open(); cmdOle = ConnOle.CreateCommand(); cmdOle = builderOLE.GetInsertCommand(); cmdOle.CommandTimeout = 1500; UpdateSQL.Append("Update "); UpdateSQL.Append(pSelectSQL.Replace("Select * From ", "")); UpdateSQL.Append(" Set "); foreach (System.Data.OleDb.OleDbParameter param in cmdOle.Parameters) { //string FieldName = param.ParameterName.TrimStart(new char[] { '@' }); string FieldName = param.SourceColumn; try { StringBuilder TUpdateSQL = new StringBuilder(); if (pDataReader[FieldName] != DBNull.Value && !string.IsNullOrEmpty(pDataReader[FieldName].ToString())) { TUpdateSQL.Append("["); TUpdateSQL.Append(FieldName); TUpdateSQL.Append("]="); switch (pDataReader[FieldName].GetType().ToString()) { case "System.Int32": case "System.Decimal": case "System.Boolean": case "System.Double": case "System.Single": case "System.Byte": TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''")); break; default: TUpdateSQL.Append("'"); TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''")); TUpdateSQL.Append("'"); break; } TUpdateSQL.Append(","); } UpdateSQL.Append(TUpdateSQL); } catch (Exception ex) { // do nothing } } UpdateSQL.Length = UpdateSQL.Length - 1; UpdateSQL.Append(" Where "); UpdateSQL.Append(pKeyString); builderOLE = null; cmdOle = null; cmdOle = ConnOle.CreateCommand(); cmdOle.CommandText = UpdateSQL.ToString(); //DBReadExecute.ExecuteSQL(pFileString, InsertSQL.ToString()); cmdOle.ExecuteNonQuery(); //break; } } catch (System.Exception ex) { Logger.Log(DateTime.Now + ": " + ex.Message); } finally { if (Conn != null) { Conn.Close(); } if (ConnOle != null) { ConnOle.Close(); } } result = true; return result; }
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]; }