private void frmWhatMedia_Load(object sender, EventArgs e) { try { string connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile; string query = "select Title, isbn as Checkout_Code from (select title, isbn from book union select title, upc from movie union select title,upc from game union select album,upc from cd) order by title;"; OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); BindingSource bs = new BindingSource(); bs.DataSource = dt; dgvMedia.DataSource = bs; da.Update(dt); databaseHandler d = new databaseHandler(); d.closeDatabaseConnection(); DataGridViewColumn c = dgvMedia.Columns[0]; c.Width = 155; dgvMedia.ClearSelection(); } catch (Exception ee) { MessageBox.Show(ee.Message); } }
protected void Button1_Click1(object sender, EventArgs e) { string username=this.TextBox1.Text; string con="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+System.Web.HttpContext.Current.Server.MapPath("App_data/db1.mdb"); OleDbConnection ole=new OleDbConnection(con); DataSet ds=new DataSet(); DataRow drow; ole.Open(); OleDbCommand olec=new OleDbCommand("select * from yonghu where yname='"+username+"'",ole); int count=Convert.ToInt32(olec.ExecuteScalar()); OleDbDataAdapter oda=new OleDbDataAdapter ("select * from yonghu",ole); OleDbCommandBuilder ob=new OleDbCommandBuilder(oda); oda.Fill(ds,"tab"); if (count > 0) { this.Label1.Text = "昵称已存在,请重新输入!"; } else { drow = ds.Tables["tab"].NewRow(); drow[0] = ds.Tables["tab"].Rows.Count + 1; drow[1] = this.TextBox1.Text; ds.Tables["tab"].Rows.Add(drow); oda.Update(ds, "tab"); ole.Close(); Session["name"] = Request["TextBox1"]; Application["message"] = ""; Response.Redirect("HTMLPage.htm"); } }
protected void Button3_Click(object sender, EventArgs e) { if (this.IsValid) { string con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Web.HttpContext.Current.Server.MapPath("App_data/db1.mdb"); OleDbConnection ole = new OleDbConnection(con); DataSet ds = new DataSet(); DataRow drow; ole.Open(); OleDbDataAdapter oda = new OleDbDataAdapter("select * from yonghu", con); OleDbCommandBuilder ob = new OleDbCommandBuilder(oda); oda.Fill(ds, "tab"); int num = ds.Tables["tab"].Rows.Count; drow = ds.Tables["tab"].NewRow(); drow[0] = num + 1; num++; drow[1] = TextBox2.Text; drow[2] = TextBox3.Text; ds.Tables["tab"].Rows.Add(drow); oda.Update(ds, "tab"); ole.Close(); this.Panel3.Visible = false; this.Label2.Visible = true; this.Label3.Visible = true; this.Button4.Visible = true; } }
private void InsertIntoExcel() { // need to update the row so the DA does the insert... foreach (System.Data.DataRow r in ds.Tables[0].Rows) { r["Extracted"] = System.DateTime.Now; // update the row! } da.UpdateCommand = new System.Data.OleDb.OleDbCommand(sqlInsert); da.UpdateCommand.Connection = ConnDest; da.UpdateCommand.Parameters.Add("@ProductId", System.Data.OleDb.OleDbType.Integer, 4, "ProductId"); da.UpdateCommand.Parameters.Add("@ProductName", System.Data.OleDb.OleDbType.VarWChar, 40, "ProductName"); da.UpdateCommand.Parameters.Add("@QuantityPerUnit", System.Data.OleDb.OleDbType.VarWChar, 20, "QuantityPerUnit"); da.UpdateCommand.Parameters.Add("@UnitPrice", System.Data.OleDb.OleDbType.Currency, 8, "UnitPrice"); da.UpdateCommand.Parameters.Add("@UnitsInStock", System.Data.OleDb.OleDbType.SmallInt, 2, "UnitsInStock"); da.UpdateCommand.Parameters.Add("@Extracted", System.Data.OleDb.OleDbType.Date, 8, "Extracted"); da.Update(ds, "Extracto"); // in the event you want to update a datasource via a different DataAdapter -- // for example you want to fill from a System.Data.SqlClient.DataAdapter and // then Update via a System.Data.Oledb.OledbDataAdapter -- then you could define // two distinct DataAdapters. Fill the DataSet with the first DA, then Update // with the second DA. }
private void RemoveDBImage(int Index) { string stringConnection = (strProvider + strDataSource); string stringSQL = "SELECT * FROM [tblImages]"; dbConnection.ConnectionString = (strProvider + strDataSource); dbConnection.Open(); dbAdapter = new OleDbDataAdapter(stringSQL, dbConnection); dbAdapter.DeleteCommand = dbDeleteCommand; System.Data.DataSet dataSet = new DataSet("tblImages"); dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture; dbAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; dbAdapter.Fill(dataSet, "tblImages"); dataSet.Tables["tblImages"].Rows[Index].Delete(); dbAdapter.Update(dataSet, "tblImages"); dbConnection.Close(); CmbBxImages.Items.Remove(CmbBxImages.Text); if ((Index == CmbBxImages.Items.Count)) { CmbBxImages.SelectedIndex = (Index - 1); } else { CmbBxImages.SelectedIndex = Index; } }
private void clears() { txtNameSearch.Text = "Enter all or part of a name here..."; txtCCSearch.Text = "Scan a customer card here..."; try { connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile; string query = "select * from customer order by last_name;"; OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); BindingSource bs = new BindingSource(); bs.DataSource = dt; dgvCustomers.DataSource = bs; da.Update(dt); databaseHandler d = new databaseHandler(); d.closeDatabaseConnection(); } catch (Exception ee) { MessageBox.Show(ee.Message); } }
static void Main(string[] args) { //dateTime.DataSet1TableAdapters.table1TableAdapter table = new dateTime.DataSet1TableAdapters.table1TableAdapter(); //table.Insert("arif", "khan", "hasan", "1988-01-10", "100.00"); //Console.ReadLine(); OleDbConnection con = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=D:\Documents and Settings\axkhan2\Desktop\table\table1.dbf"); con.Open(); OleDbDataAdapter adapt = new OleDbDataAdapter(@"select * from 'D:\Documents and Settings\axkhan2\Desktop\table\table1.dbf'", con); OleDbCommandBuilder build = new OleDbCommandBuilder(adapt); DataSet set = new DataSet(); adapt.Fill(set); DataTable table = set.Tables[0]; DataRow row = table.NewRow(); row["firstname"] = "arifkhan"; row["lastname"] = "khan"; row["middlename"] = "mname"; row["dob"] = DateTime.Now; row["amount"] = 100; table.Rows.Add(row); //adapt.Update(table); //adapt.UpdateCommand = build.GetUpdateCommand(); adapt.Update(table); //cmd.ExecuteNonQuery(); }
public void searcher(string field, string column) { try { connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile; string query = "select ISBN, Title, Author, Publisher, Binding, pub_date as Publication_Date, Pages, Available from book where " + column + " like '%" + escapeHandling(field) + "%' order by Author, Title;"; OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); BindingSource bs = new BindingSource(); bs.DataSource = dt; dgvBook.DataSource = bs; da.Update(dt); databaseHandler d = new databaseHandler(); d.closeDatabaseConnection(); } catch (Exception ee) { MessageBox.Show(ee.Message); } dgvBook.ClearSelection(); }
/// <summary>Delete the specified Cause from the specified Issue.</summary> /// <param name="pIssueID">ID of the Issue the cause applies to.</param> /// <param name="pCauseID">ID of the Cause to delete.</param> /// <returns>ID of the modified/created cause row.</returns> public void Delete(int pIssueID, int pCauseID) { try { using(OleDbDataAdapter oDA = new OleDbDataAdapter(String.Format("Select * from Causes where issue_id = {0} and cause_id = {1} order by cause_seq", pIssueID, pCauseID), this.DataObject.ConnectionString)) using(OleDbCommandBuilder oCB = new OleDbCommandBuilder(oDA)) using(OleDbConnection oConn = new OleDbConnection(this.DataObject.ConnectionString)) { // Get the list of previously saved Causes to the current Issue. oConn.Open(); DataSet oDS = new DataSet("Causes"); oDA.Fill(oDS); // If we found any...And we're updating an existing Cause... if(oDS.Tables[0].Rows.Count > 0) { DataRow oRow = oDS.Tables[0].Rows[0]; oRow.Delete(); oDA.Update(oDS); } } } catch(Exception e) { throw new Exception("Unable to save new Issue Cause!\nError: " + e.Message); } }
/// <summary> /// 插入数据通过Datatable /// </summary> /// <param name="_dt"></param> /// <returns>影响记录条数</returns> public override int DataTableInsert(DataTable _dt) { bool flag = false; int _nResult = 0; if (_dt == null) return _nResult; string _sCmdText = string.Format("select * from {0} where 1=2", _dt.TableName); OleDbCommand _Command = (OleDbCommand)CreateCommand(_sCmdText, CommandType.Text); OleDbDataAdapter _adapter = new OleDbDataAdapter(_Command); OleDbDataAdapter _adapter1 = new OleDbDataAdapter(_Command); OleDbCommandBuilder _builder = new OleDbCommandBuilder(_adapter1); _adapter.InsertCommand = _builder.GetInsertCommand(); if (_adapter.InsertCommand.Parameters.Count < _dt.Columns.Count) { flag = true;//因为表中有自增字段,所以CommandBuild生成的InserttCommand的参数中少了自增字段 foreach (DataColumn _dc in _dt.Columns) { if (!_adapter.InsertCommand.Parameters.Contains(_dc.ColumnName)) { _adapter.InsertCommand.CommandText = _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.IndexOf(") VALUES"), ',' + _dc.ColumnName); _adapter.InsertCommand.CommandText = _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.Length - 1, ",?"); _adapter.InsertCommand.Parameters.Add("@" + _dc.ColumnName, OleDbType.Decimal, _dc.MaxLength, _dc.ColumnName); if (_adapter.InsertCommand.Parameters.Count >= _dt.Columns.Count) break; } } } if (flag) this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} on", _dt.TableName)); this.BeginTransaction(); try { _adapter.InsertCommand.Transaction = _Command.Transaction; _Command.CommandText = "delete from " + _dt.TableName; _Command.ExecuteNonQuery(); _nResult = _adapter.Update(_dt); this.CommitTransaction(); } catch (Exception ex) { this.RollbackTransaction(); throw ex; } finally { if (flag) this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} OFF", _dt.TableName)); } return _nResult; }
public void Gravar(DataSet ds, string tabela) { OleDbCommandBuilder cmdBuilder; OleDbDataAdapter adaptador; adaptador = new OleDbDataAdapter("select * from " + tabela, conexão); cmdBuilder = new OleDbCommandBuilder(adaptador); adaptador.Update(ds, tabela); }
private void izracun_sektorjev_Click(object sender, System.EventArgs e) { oleDbDataAdapter1.Update(dataSet11, "sektorji"); _3.st_vs_zas_sek = 0; System.Data.OleDb.OleDbDataReader dr; con_1.Open(); dr = oleDbSelectCommand1.ExecuteReader(); while (dr.Read()) { if (_3.st_vseh_mest_za_sektor == (int)dr["st_gledalcev"]) { _3.st_vs_zas_sek++; } } dr.Close(); con_1.Close(); st_zas_sektorjev_2.Text = Convert.ToString(_3.st_vs_zas_sek); }
public static void CapNhatBang(DataSet ds) { OleDbConnection cn = DataProvider.ConnectionData(); string strSQL = "Select * From LoaiSach"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); da.Update(ds.Tables[0]); //B5: Dong ket noi CSDL cn.Close(); }
private void dataTable_RowChanged(object sender, DataRowChangeEventArgs e) { try { oleDbDataAdapter1.Update(dataSet1); } catch (Exception exp) { MessageBox.Show(exp.Message); } }
public static void Main(string[] args) { WriteToEventLog("Calling AEPM Service..."); var dt = new DataTable(); var dset = new DataSet(); Guid = args[0]; //retrieve any records needing updating try { using(var conn = new OleDbConnection(ConfigurationManager.AppSettings["AS400ConnectionStringDev"])) { string sql = GetUnprocessMasterRecsString(); var objDataAdapter = new OleDbDataAdapter(); var cmd = new OleDbCommand(sql, conn); objDataAdapter.SelectCommand = cmd; objDataAdapter.SelectCommand.CommandTimeout = 0; dt.Clear(); dset.Clear(); objDataAdapter.Fill(dt); objDataAdapter.Fill(dset, "currentSelections"); var cb = new OleDbCommandBuilder(objDataAdapter); AddParameters(cb); //update records to 'S' for submitted for (int i = 0; i < dset.Tables["currentSelections"].Rows.Count; i++) { dset.Tables["currentSelections"].Rows[i]["G_RETRN"] = 'S'; } objDataAdapter.Update(dset, "currentSelections"); //submit records asynch for (int i = 0; i < dt.Rows.Count; i++) { TestAsync(dt, i); } } } catch (Exception ex) { WriteToEventLog(ex); } }
static void Main(string[] args) { string myConnectionStringMDB = "Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=C:\Users\Gord\Desktop\fromCE.mdb;"; string myConnectionStringSQL = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" + @"Data Source=C:\__tmp\myData.sdf;"; using (OleDbConnection conSQL = new OleDbConnection(), conMDB = new OleDbConnection()) { conSQL.ConnectionString = myConnectionStringSQL; conSQL.Open(); conMDB.ConnectionString = myConnectionStringMDB; conMDB.Open(); using (OleDbCommand cmdSQL = new OleDbCommand(), cmdMDB = new OleDbCommand()) { cmdSQL.CommandType = System.Data.CommandType.Text; cmdSQL.Connection = conSQL; cmdSQL.CommandText = "SELECT * FROM [Table1]"; var daSQL = new System.Data.OleDb.OleDbDataAdapter(); daSQL.SelectCommand = cmdSQL; var dtSQL = new System.Data.DataTable(); daSQL.Fill(dtSQL); System.Data.DataTable dtMDB = dtSQL.Clone(); foreach (System.Data.DataRow drSQL in dtSQL.Rows) { // .ImportRow() would import the row as "Unchanged", so create a new one System.Data.DataRow drMDB = dtMDB.NewRow(); drMDB.ItemArray = drSQL.ItemArray; dtMDB.Rows.Add(drMDB); } cmdMDB.CommandType = System.Data.CommandType.Text; cmdMDB.Connection = conMDB; cmdMDB.CommandText = "INSERT INTO [Table1] ([ID], [Field 1]) VALUES (?, ?)"; cmdMDB.Parameters.Add("?", OleDbType.Integer, 1, "ID"); cmdMDB.Parameters.Add("?", OleDbType.VarWChar, 255, "Field 1"); var daMDB = new System.Data.OleDb.OleDbDataAdapter(); daMDB.InsertCommand = cmdMDB; daMDB.Update(dtMDB); } conSQL.Close(); conMDB.Close(); } Console.WriteLine("Done."); System.Threading.Thread.Sleep(2000); }
static void Main(string[] args) { string myConnectionStringMDB = "Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=C:\Users\Gord\Desktop\fromCE.mdb;"; string myConnectionStringSQL = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" + @"Data Source=C:\__tmp\myData.sdf;"; using (OleDbConnection conSQL = new OleDbConnection(), conMDB = new OleDbConnection()) { conSQL.ConnectionString = myConnectionStringSQL; conSQL.Open(); conMDB.ConnectionString = myConnectionStringMDB; conMDB.Open(); using (OleDbCommand cmdSQL = new OleDbCommand(), cmdMDB = new OleDbCommand()) { cmdSQL.CommandType = System.Data.CommandType.Text; cmdSQL.Connection = conSQL; cmdSQL.CommandText = "SELECT * FROM [Table1]"; var daSQL = new System.Data.OleDb.OleDbDataAdapter(); daSQL.SelectCommand = cmdSQL; var dtSQL = new System.Data.DataTable(); daSQL.Fill(dtSQL); System.Data.DataTable dtMDB = dtSQL.Clone(); foreach (System.Data.DataRow drSQL in dtSQL.Rows) { // .ImportRow() would import the row as "Unchanged", so create a new one System.Data.DataRow drMDB = dtMDB.NewRow(); drMDB.ItemArray = drSQL.ItemArray; dtMDB.Rows.Add(drMDB); } cmdMDB.CommandType = System.Data.CommandType.Text; cmdMDB.Connection = conMDB; cmdMDB.CommandText = "SELECT * FROM [Table1]"; var daMDB = new System.Data.OleDb.OleDbDataAdapter(); daMDB.SelectCommand = cmdMDB; var cbuilderMDB = new OleDbCommandBuilder(daMDB); cbuilderMDB.QuotePrefix = "["; cbuilderMDB.QuoteSuffix = "]"; daMDB.Update(dtMDB); } conSQL.Close(); conMDB.Close(); } Console.WriteLine("Done."); System.Threading.Thread.Sleep(2000); }
private void updater_Click(object sender, EventArgs e) { command.CommandText = "UPDATE Дисциплины_обучение SET Балл = ? WHERE (Код = ?)"; command.Parameters.Add("Балл", dabse.OleDbType.Integer, 50, "Балл"); command.Parameters.Add(new dabse.OleDbParameter("Original_Код", dabse.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, (byte)0, (byte)0, "Код", System.Data.DataRowVersion.Original, null)); adapter.UpdateCommand = command; command.Connection = Date_baseone; try { int amount = adapter.Update(setOfData, "Дисциплины_обучение"); MessageBox.Show("Обновлено " + amount + " записей"); } catch (Exception WrongSituation) { MessageBox.Show(WrongSituation.Message, "Недоразумение, упс..."); } }
public static void Main(string [] str) { string path = "D:\\temp\\csc\\dataset"; string table = path+"\\temp.dbf"; OleDbConnection con = new OleDbConnection("Provider=VFPOLEDB.1;Data Source=D:\\temp\\csc\\dataset"); con.Open(); try { OleDbDataAdapter da = new OleDbDataAdapter("select * from "+table, con); // da.SelectCommand = new OleDbCommand("select * from "+table, con); da.UpdateCommand = new OleDbCommand("update "+ table+"set fname = ? where id = 1", con); da.UpdateCommand.Parameters.Add("fname", OleDbType.VarChar, 200, "@fname"); da.UpdateCommand.Parameters.Add("fname", OleDbType.VarChar, 200, "@fname").SourceVersion = DataRowVersion.Original; DataTable tb = new DataTable(); da.Fill(tb); /* DataSet ds = new DataSet(); da.Fill(ds); DataTable tb = ds.Tables[0]; DataRow rw = tb.NewRow(); rw["id"] = 4; rw["fname"] = "afreen"; rw["lname"] = "khan"; rw["mname"] = "arif"; */ // tb.Rows.Add(rw); Console.WriteLine("Enter you first name"); DataRow rw = tb.Rows[0]; rw["fname"] = Console.ReadLine(); // Console.WriteLine("row added to table"); // OleDbCommandBuilder build = new OleDbCommandBuilder(da); da.Update(tb); Console.WriteLine("Table updated"); } catch(OleDbException ex) { Console.WriteLine(ex.Message); } finally { con.Close(); } }
private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { string color, sValue; // Gets the value of the key field of the row being updated string key = DataGrid1.DataKeys[e.Item.ItemIndex].ToString(); // Gets get the value of the controls (textboxes) that the user // updated. The DataGrid columns are exposed as the Cells collection. // Each cell has a collection of controls. In this case, there is only one // control in each cell -- a TextBox control. To get its value, // you copy the TextBox to a local instance (which requires casting) // and extract its Text property. // // The first column -- Cells(0) -- contains the Update and Cancel buttons. TextBox tb; // Gets the value tb = (TextBox)(e.Item.Cells[2].Controls[0]); color = tb.Text; // Gets the value tb = (TextBox)(e.Item.Cells[3].Controls[0]); sValue = tb.Text; // Finds the row in the dataset table that matches the // one the user updated in the grid. This example uses a // special Find method defined for the typed dataset, which // returns a reference to the row. dsTable.MyTableRow r; r = dsTable1.MyTable.FindByid(int.Parse(key)); // Updates the dataset table. r.colors = color; r.values = int.Parse(sValue); // Calls a SQL statement to update the database from the dataset oleDbDataAdapter1.Update(dsTable1); // Takes the DataGrid row out of editing mode DataGrid1.EditItemIndex = -1; // Refreshes the grid DataGrid1.DataBind(); }
private void saver_Click(object sender, EventArgs e) { command = new dabse.OleDbCommand(); command.CommandText = "UPDATE Группы WHERE Номер = ?"; command.Parameters.Add(new dabse.OleDbParameter("Original_Номер", dabse.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, (byte)0, (byte)0, "Номер", System.Data.DataRowVersion.Original, null)); command.Connection = Date_baseone; adapter.UpdateCommand = command; try { int kol = adapter.Update(setOfData, nameoftable); MessageBox.Show("Обновлено " + kol + " записей"); Date_baseone.Close(); } catch (Exception Ситуация_однако) { MessageBox.Show(Ситуация_однако.Message, "Недоразумение"); } }
private void saver_Click(object sender, EventArgs e) { command.CommandText = "UPDATE Специальности SET Название = ?, Срок_обучения = ? WHERE (Шифр = ?)"; command.Parameters.Add("Название", dabse.OleDbType.VarWChar, 50, "Название"); command.Parameters.Add("Срок_обучения", dabse.OleDbType.VarWChar, 50, "Срок_обучения"); command.Parameters.Add(new dabse.OleDbParameter("Original_Шифр", dabse.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, (byte)0, (byte)0, "Шифр", System.Data.DataRowVersion.Original, null)); command.Connection = Date_baseone; adapter.UpdateCommand = command; try { int kol = adapter.Update(setOfData, nameoftable); MessageBox.Show("Обновлено " + kol + " записей"); //Date_baseone.Close(); } catch (Exception Ситуация_однако) { MessageBox.Show(Ситуация_однако.Message, "Недоразумение"); } }
protected void Button1_Click(object sender, EventArgs e) { string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Web.HttpContext.Current.Server.MapPath("app_data/db1.mdb"); OleDbConnection ole = new OleDbConnection(strconn); DataSet ds = new DataSet(); DataRow drow; ole.Open(); OleDbDataAdapter oda = new OleDbDataAdapter("select * from yh", strconn); OleDbCommandBuilder ob = new OleDbCommandBuilder(oda); oda.Fill(ds, "tab"); drow = ds.Tables["tab"].NewRow(); drow[0] = Session["yname"]; drow[1] = this.TextBox1.Text; ds.Tables["tab"].Rows.Add(drow); oda.Update(ds, "tab"); DataList1.DataSource = ds.Tables["tab"]; DataList1.DataBind(); ole.Close(); }
private void deleteToolStripMenuItem_Click(object sender, EventArgs e) { string row = dgvMovies[0, dgvMovies.CurrentRow.Index].Value.ToString(); if (MessageBox.Show("Are you sure?", "Confirm Deletion", MessageBoxButtons.YesNo) == DialogResult.No) { MessageBox.Show("Request ignored"); dgvMovies.ClearSelection(); return; } try { connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile; string query = "delete from Movie where UPC = '" + row + "';"; string query2 = "delete from Actor where UPC = '" + row + "';"; OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString); OleDbDataAdapter da2 = new OleDbDataAdapter(query2, connectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); OleDbCommandBuilder cb2 = new OleDbCommandBuilder(da2); DataTable dt = new DataTable(); da.Fill(dt); BindingSource bs = new BindingSource(); bs.DataSource = dt; dgvMovies.DataSource = bs; da.Update(dt); } catch (Exception ex) { MessageBox.Show("Unexpected error: " + ex); } finally { databaseHandler d = new databaseHandler(); d.closeDatabaseConnection(); } refresh(); dgvMovies.ClearSelection(); }
public void Write(CubeMetadata metadata) { if (metadata == null) throw new ArgumentNullException(); GetSheets(); if (_sheets.IndexOf(SheetName + "$") == -1) CreateNewSheet(SheetName); RaiseProgressStatus("Opening Xls file"); DataTable dataTable = MetadataFileFormat.WriteInDataTable(metadata); dataTable.TableName = SheetName + "$"; _rowTotal = dataTable.Rows.Count; using (var conn = new OleDbConnection(GetConnectionString(Filename))) { using (var da = new OleDbDataAdapter()) { var sb = new StringBuilder(); sb.AppendFormat("INSERT INTO [{0}$] (", SheetName); foreach (DataColumn col in dataTable.Columns) sb.AppendFormat(" {0},", col.ColumnName); sb.Remove(sb.Length - 1, 1); //Remove the last comma sb.Append(") VALUES ("); for (int i = 0; i < dataTable.Columns.Count; i++) sb.Append(" ?,"); sb.Remove(sb.Length - 1, 1); //Remove the last comma sb.Append(")"); da.InsertCommand = new OleDbCommand(sb.ToString(), conn); foreach (DataColumn col in dataTable.Columns) da.InsertCommand.Parameters.Add(string.Format("@{0}", col.ColumnName), OleDbType.VarChar, 255, col.ColumnName); da.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated); conn.Open(); da.Update(dataTable); da.RowUpdated -= new OleDbRowUpdatedEventHandler(OnRowUpdated); } } RaiseProgressStatus("Xls file written"); }
private void mineralsBindingNavigatorSaveItem_Click(object sender, EventArgs e) { DataSet dataSet = new DataSet(); string queryString = "SELECT * FROM minerals"; string connectionString = @"Data Source=remote.failgaming.com,1433\athena;Initial Catalog=ore_stock;User ID=test;Password=FailZ0rz69"; using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = new OleDbCommand(queryString, connection); OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter); adapter.Fill(dataSet); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.Update(dataSet); } this.Validate(); this.mineralsBindingSource.EndEdit(); this.tableAdapterManager.UpdateAll(this.ore_stockDataSet1); }
private void button2_Click(object sender, EventArgs e) { command.CommandText = "UPDATE [Phones] SET [phone] = ?, fio = ? WHERE ([Nn] = ?)"; command.Parameters.Add("phone", oleDB.OleDbType.VarChar, 50, "phone"); command.Parameters.Add("fio", oleDB.OleDbType.VarChar, 50, "fio"); command.Parameters.Add(new oleDB.OleDbParameter("Original_Nn", oleDB.OleDbType.Integer, 0, ParameterDirection.Input, false, (byte)0, (byte)0, "Nn", DataRowVersion.Original, null)); adapter.UpdateCommand = command; command.Connection = connection; try { int kol = adapter.Update(dataSet, "Phones"); MessageBox.Show("Обновлено " + kol + " записей"); } catch (Exception ex) { MessageBox.Show(ex.Message, "Недоразумение"); } }
//Fills the grid with data using the SQL statement. public void updateGrid(string DDL, MetroGrid grid) { //Start instantiation OleDbDataAdapter DA = new OleDbDataAdapter(DDL, getSetCon()); DataTable DT = new DataTable(); //End instantiation try { getSetCon().Open(); //Opens connection DA.Fill(DT); } catch (Exception ex) { MetroMessageBox.Show(form, "Unsuccessful \n" + ex.Message); } grid.DataSource = DT; //Sets the data source. DA.Update(DT); //Updates the data adpapter. con.Close(); //Closes con. }
private void InsertDBImage(string FileName) { System.IO.FileStream fileStream = new System.IO.FileStream(FileName, System.IO.FileMode.Open, System.IO.FileAccess.Read); long imageSize = fileStream.Length; byte[] imageData = new Byte[fileStream.Length]; fileStream.Read(imageData, 0, System.Convert.ToInt32(fileStream.Length)); fileStream.Close(); string FName = GetFileName(FileName); string stringConnection = strProvider + strDataSource; string stringSQL = "SELECT * FROM [tblImages]"; dbConnection.ConnectionString = (strProvider + strDataSource); dbConnection.Open(); dbAdapter = new OleDbDataAdapter(stringSQL, dbConnection); dbAdapter.InsertCommand = dbInsertCommand; System.Data.DataSet dataSet = new DataSet("tblImages"); dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture; dbAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; dbAdapter.Fill(dataSet, "tblImages"); System.Data.DataRow newRow = dataSet.Tables["tblImages"].NewRow(); newRow["ImageName"] = FName; newRow["Image"] = imageData; dataSet.Tables["tblImages"].Rows.Add(newRow); try { dbAdapter.Update(dataSet, "tblImages"); CmbBxImages.Items.Add(FName); dbConnection.Close(); CmbBxImages.SelectedIndex = (CmbBxImages.Items.Count - 1); } catch (System.Exception ex) { AccusoftError(ex, lblError); dbConnection.Close(); } }
private void saver_Click(object sender, EventArgs e) { сommand.CommandText = "UPDATE Студенты SET Фамлия=?, Имя=?, Отчество=?, Специальность=?, Гражданство=?, Форма_обучения=?, Признак_отчисления=? WHERE Шифр=?"; сommand.Parameters.Add("Фамилия", dabse.OleDbType.VarWChar, 50, "Фамилия"); сommand.Parameters.Add("Имя", dabse.OleDbType.VarWChar, 50, "Имя"); сommand.Parameters.Add("Специальность", dabse.OleDbType.Integer, 50, "Специальность"); сommand.Parameters.Add("Гражданство", dabse.OleDbType.VarWChar, 50, "Гражданство"); сommand.Parameters.Add("Форма_обучения", dabse.OleDbType.VarWChar, 50, "Форма_обучения"); сommand.Parameters.Add("Признак_отчисления", dabse.OleDbType.Integer, 50, "Признак_отчисления"); сommand.Parameters.Add(new dabse.OleDbParameter("Original_Шифр", dabse.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, (byte)0, (byte)0, "Шифр", System.Data.DataRowVersion.Original, null)); сommand.Connection = Date_baseone; adapter.UpdateCommand = сommand; try { int kol = adapter.Update(setOfData, nameoftable); MessageBox.Show("Обновлено " + kol + " записей"); Date_baseone.Close(); } catch (Exception Ситуация_однако) { MessageBox.Show(Ситуация_однако.Message, "Недоразумение"); } }
public DataSet AtualizaBanco(DataSet Pds, string Psql) { System.Data.OleDb.OleDbConnection cnx = new System.Data.OleDb.OleDbConnection(ConexaoOledb); try { System.Data.OleDb.OleDbDataAdapter Adp = new System.Data.OleDb.OleDbDataAdapter(); Adp.SelectCommand = new System.Data.OleDb.OleDbCommand(Psql, cnx); System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(Adp); cnx.Open(); Adp.Update(Pds); } catch (Exception ex) { throw ex; } finally { cnx.Close(); } return(Pds); }
public void InserOrderDetail(Order order) { DataSet ds=convertToTable(order.OrderProductds, order.OrderID); myCmd = new OleDbCommand("Select * From OrderDetails", myConnection); myCmd.Transaction = transaction; OleDbDataAdapter adapter = new OleDbDataAdapter(myCmd); OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter); adapter.InsertCommand = builder.GetInsertCommand(); try { adapter.Update(ds, "ShoppingBasketDs"); } catch (Exception ex) { throw ex; } }
// Sauvegarde tous les changements effectué dans le dataset public void SaveDataSet(string tableName, DataSet dataSet) { if (dataSet.HasChanges() == false) return; switch (connType) { case ConnectionType.DATABASE_MSSQL: { try { var conn = new SqlConnection(connString); var adapter = new SqlDataAdapter("SELECT * from " + tableName, conn); var builder = new SqlCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); lock (dataSet) // lock dataset to prevent changes to it { adapter.ContinueUpdateOnError = true; DataSet changes = dataSet.GetChanges(); adapter.Update(changes, tableName); PrintDatasetErrors(changes); dataSet.AcceptChanges(); } conn.Close(); } catch (Exception ex) { throw new DatabaseException("Can not save table " + tableName, ex); } break; } case ConnectionType.DATABASE_ODBC: { try { var conn = new OdbcConnection(connString); var adapter = new OdbcDataAdapter("SELECT * from " + tableName, conn); var builder = new OdbcCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); DataSet changes; lock (dataSet) // lock dataset to prevent changes to it { adapter.ContinueUpdateOnError = true; changes = dataSet.GetChanges(); adapter.Update(changes, tableName); dataSet.AcceptChanges(); } PrintDatasetErrors(changes); conn.Close(); } catch (Exception ex) { throw new DatabaseException("Can not save table ", ex); } break; } case ConnectionType.DATABASE_MYSQL: { return; } case ConnectionType.DATABASE_OLEDB: { try { var conn = new OleDbConnection(connString); var adapter = new OleDbDataAdapter("SELECT * from " + tableName, conn); var builder = new OleDbCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); DataSet changes; lock (dataSet) // lock dataset to prevent changes to it { adapter.ContinueUpdateOnError = true; changes = dataSet.GetChanges(); adapter.Update(changes, tableName); dataSet.AcceptChanges(); } PrintDatasetErrors(changes); conn.Close(); } catch (Exception ex) { throw new DatabaseException("Can not save table", ex); } break; } } }
private void txtUPCsearch_TextChanged(object sender, EventArgs e) { if (txtNameSearch.Text.Length >= 0) { try { connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile; string query = "select * from employee where first_name like '%" + txtNameSearch.Text + "%' or last_name like '%" + txtNameSearch.Text + "%' order by last_name;"; OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); BindingSource bs = new BindingSource(); bs.DataSource = dt; dgvEmployees.DataSource = bs; da.Update(dt); d.closeDatabaseConnection(); } catch (Exception ee) { MessageBox.Show(ee.Message); } } }
private void searcher(string field, string column) { try { connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile; string query = "select * from employee where " + column + " like '%" + escapeHandling(field) + "%' order by last_name;"; OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); BindingSource bs = new BindingSource(); bs.DataSource = dt; dgvEmployees.DataSource = bs; da.Update(dt); d.closeDatabaseConnection(); } catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void refreshList() { try { connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile; string query = "select * from employee order by last_name;"; OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); BindingSource bs = new BindingSource(); bs.DataSource = dt; dgvEmployees.DataSource = bs; da.Update(dt); } catch (Exception ex) { MessageBox.Show("There was a problem loading the employee list.\n" + ex); } finally { d.closeDatabaseConnection(); } }
public bool UpdatePlenos(Contradiccion contradiccion) { OleDbConnection connection = new OleDbConnection(connectionString); OleDbDataAdapter dataAdapter; DataSet dataSet = new DataSet(); DataRow dr; bool completed = false; try { string sqlCadena = "SELECT * FROM Contradicciones WHERE IdContradiccion = @IdContradiccion"; dataAdapter = new OleDbDataAdapter(); dataAdapter.SelectCommand = new OleDbCommand(sqlCadena, connection); dataAdapter.SelectCommand.Parameters.AddWithValue("@IdContradiccion", contradiccion.IdContradiccion); dataAdapter.Fill(dataSet, "Contradicciones"); dr = dataSet.Tables["Contradicciones"].Rows[0]; dr.BeginEdit(); dr["OficioPleno"] = contradiccion.OficioPlenos; if (contradiccion.FEnvioOfPlenos != null) { dr["FEnvioOfPleno"] = contradiccion.FEnvioOfPlenos; dr["FEnvioOfPlenoInt"] = DateTimeUtilities.DateToInt(contradiccion.FEnvioOfPlenos); } else { dr["FEnvioOfPleno"] = System.DBNull.Value; dr["FEnvioOfPlenoInt"] = 0; } dr["OficioGeneradoPleno"] = Convert.ToInt16(contradiccion.OficioPlenoGenerado); dr["RutaOficioPleno"] = contradiccion.OPlenoFilePath; dr["IdContradiccion"] = contradiccion.IdContradiccion; dr.EndEdit(); dataAdapter.UpdateCommand = connection.CreateCommand(); dataAdapter.UpdateCommand.CommandText = "UPDATE Contradicciones SET OficioPleno = @OficioPleno,FEnvioOfPleno = @FEnvioOfPleno," + "FEnvioOfPlenoInt = @FEnvioOfPlenoInt, OficioGeneradoPleno = @OficioGeneradoPleno,RutaOficioPleno = @RutaOficioPleno " + " WHERE IdContradiccion = @IdContradiccion"; dataAdapter.UpdateCommand.Parameters.Add("@OficioPleno", OleDbType.VarChar, 0, "OficioPleno"); dataAdapter.UpdateCommand.Parameters.Add("@FEnvioOfPleno", OleDbType.Date, 0, "FEnvioOfPleno"); dataAdapter.UpdateCommand.Parameters.Add("@FEnvioOfPlenoInt", OleDbType.Numeric, 0, "FEnvioOfPlenoInt"); dataAdapter.UpdateCommand.Parameters.Add("@OficioGeneradoPleno", OleDbType.Numeric, 0, "OficioGeneradoPleno"); dataAdapter.UpdateCommand.Parameters.Add("@RutaOficioPleno", OleDbType.VarChar, 0, "RutaOficioPleno"); dataAdapter.UpdateCommand.Parameters.Add("@IdContradiccion", OleDbType.Numeric, 0, "IdContradiccion"); dataAdapter.Update(dataSet, "Contradicciones"); dataSet.Dispose(); dataAdapter.Dispose(); completed = true; } catch (OleDbException ex) { string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name; ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,ContradiccionModel", "OficiosPleno"); } catch (Exception ex) { string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name; ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,ContradiccionModel", "OficiosPleno"); } finally { connection.Close(); } return completed; }
public bool UpdateContradiccion(Contradiccion contradiccion) { OleDbConnection connection = new OleDbConnection(connectionString); OleDbDataAdapter dataAdapter; DataSet dataSet = new DataSet(); DataRow dr; bool completed = false; try { string sqlCadena = "SELECT * FROM Contradicciones WHERE IdContradiccion = @IdContradiccion"; dataAdapter = new OleDbDataAdapter(); dataAdapter.SelectCommand = new OleDbCommand(sqlCadena, connection); dataAdapter.SelectCommand.Parameters.AddWithValue("@IdContradiccion", contradiccion.IdContradiccion); dataAdapter.Fill(dataSet, "Contradicciones"); dr = dataSet.Tables["Contradicciones"].Rows[0]; dr.BeginEdit(); dr["NumAsunto"] = contradiccion.NumAsunto; dr["AnioAsunto"] = contradiccion.AnioAsunto; dr["OficioAdmision"] = contradiccion.OficioAdmision; if (contradiccion.FechaOficioAdmin != null) { dr["FechaAdminOficio"] = contradiccion.FechaOficioAdmin; dr["FechaAdminOficioInt"] = DateTimeUtilities.DateToInt(contradiccion.FechaOficioAdmin); } else { dr["FechaAdminOficio"] = System.DBNull.Value; dr["FechaAdminOficioInt"] = 0; } dr["@RutaArchivoOficio"] = contradiccion.OficioFilePath; if (contradiccion.FechaCorreo != null) { dr["@FechaAdminCorreo"] = contradiccion.FechaCorreo; dr["@FechaAdminCorreoInt"] = contradiccion.FechaCorreoInt; } else { dr["@FechaAdminCorreo"] = System.DBNull.Value; dr["@FechaAdminCorreoInt"] = 0; } dr["@RutaArchivoCorreo"] = contradiccion.CorreoFilePath; dr["@Tema"] = contradiccion.Tema; dr["IdContradiccion"] = contradiccion.IdContradiccion; dr.EndEdit(); dataAdapter.UpdateCommand = connection.CreateCommand(); dataAdapter.UpdateCommand.CommandText = "UPDATE Contradicciones SET NumAsunto = @NumAsunto, AnioAsunto = @AnioAsunto, OficioAdmision = @OficioAdmision " + " FechaAdminOficio = @FechaAdminOficio, FechaAdminOficioInt = @FechaAdminOficioInt, RutaArchivoOficio = @RutaArchivoOficio," + " FechaAdminCorreo = @FechaAdminCorreo, FechaAdminCorreoInt = @FechaAdminCorreoInt, RutaArchivoCorreo = @RutaArchivoCorreo, Tema = @Tema " + " WHERE IdContradiccion = @IdContradiccion"; dataAdapter.UpdateCommand.Parameters.Add("@NumAsunto", OleDbType.VarChar, 0, "NumAsunto"); dataAdapter.UpdateCommand.Parameters.Add("@AnioAsunto", OleDbType.VarChar, 0, "AnioAsunto"); dataAdapter.UpdateCommand.Parameters.Add("@OficioAdmision", OleDbType.VarChar, 0, "OficioAdmision"); dataAdapter.UpdateCommand.Parameters.Add("@FechaAdminOficio", OleDbType.Date, 0, "FechaAdminOficio"); dataAdapter.UpdateCommand.Parameters.Add("@FechaAdminOficioInt", OleDbType.Numeric, 0, "FechaAdminOficioInt"); dataAdapter.UpdateCommand.Parameters.Add("@RutaArchivoOficio", OleDbType.VarChar, 0, "RutaArchivoOficio"); dataAdapter.UpdateCommand.Parameters.Add("@FechaAdminCorreo", OleDbType.Date, 0, "FechaAdminCorreo"); dataAdapter.UpdateCommand.Parameters.Add("@FechaAdminCorreoInt", OleDbType.Numeric, 0, "FechaAdminCorreoInt"); dataAdapter.UpdateCommand.Parameters.Add("@RutaArchivoCorreo", OleDbType.VarChar, 0, "RutaArchivoCorreo"); dataAdapter.UpdateCommand.Parameters.Add("@Tema", OleDbType.VarChar, 0, "Tema"); dataAdapter.UpdateCommand.Parameters.Add("@IdContradiccion", OleDbType.Numeric, 0, "IdContradiccion"); dataAdapter.Update(dataSet, "Contradicciones"); dataSet.Dispose(); dataAdapter.Dispose(); completed = true; } catch (OleDbException ex) { string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name; ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,ContradiccionModel", "OficiosPleno"); } catch (Exception ex) { string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name; ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,ContradiccionModel", "OficiosPleno"); } finally { connection.Close(); } return completed; }
//TODO: provide the UI interface CheckBox to overwrite existing differing Diam and Species Group from Biosum and FVS //Otherwise, make a copy? and or not do anything? //TODO: Change back to Private public void DbRecsTdgTsg2ProcDbCopy(bool overwrite) { if (!overwrite) { return; //can't do anything now if user don't want replace table yet. } //bool blnRowDiff=false; //15-MAY-2017: These tables were renamed and moved but this code is obsolete so it was not updated string strSqlTdg = "SELECT * FROM TREE_DIAM_GROUPS"; string strSqlTsg = "SELECT * FROM TREE_SPECIES_GROUPS"; string strSqlDelTdg = "DELETE FROM TREE_DIAM_GROUPS"; string strSqlDelTsg = "DELETE FROM TREE_SPECIES_GROUPS"; DataTable dtTdg = new DataTable("TDG"); DataTable dtTsg = new DataTable("TSG");; DataTable dtTdgTgt = new DataTable("TDGTGT"); DataTable dtTsgTgt = new DataTable("TSGTGT"); DataSet dsMaster = new DataSet("FVS_TREE"); dsMaster.Tables.Add(dtTdg); dsMaster.Tables.Add(dtTsg); OLE.OleDbConnection oleConx2Master = null; OLE.OleDbDataAdapter oleAdaptTdg = null; OLE.OleDbDataAdapter oleAdaptTsg = null; OLE.OleDbConnection oleConx2Proc = null; OLE.OleDbDataAdapter oleAdaptTdgTgt = null; OLE.OleDbDataAdapter oleAdaptTsgTgt = null; OLE.OleDbCommandBuilder oleCmdBTdgTgt = null; OLE.OleDbCommandBuilder oleCmdBTsgTgt = null; try { oleConx2Master = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, this.strPathMasterDbFile)); oleConx2Master.Open(); oleAdaptTdg = new System.Data.OleDb.OleDbDataAdapter(strSqlTdg, oleConx2Master); oleAdaptTdg.Fill(dtTdg); oleAdaptTsg = new System.Data.OleDb.OleDbDataAdapter(strSqlTsg, oleConx2Master); oleAdaptTsg.Fill(dtTsg); //TODO: Made assumption that the table structures exist ... need to create the Table //If 2 tables exists in strPathProcDbFile, then delete records and import them from here. oleConx2Proc = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, this.strPathProcDbFile)); oleConx2Proc.Open(); oleAdaptTdgTgt = new System.Data.OleDb.OleDbDataAdapter(strSqlTdg, oleConx2Proc); oleAdaptTsgTgt = new System.Data.OleDb.OleDbDataAdapter(strSqlTsg, oleConx2Proc); oleAdaptTdgTgt.Fill(dtTdgTgt); oleAdaptTsgTgt.Fill(dtTsgTgt); oleCmdBTdgTgt = new System.Data.OleDb.OleDbCommandBuilder(oleAdaptTdgTgt); oleCmdBTsgTgt = new System.Data.OleDb.OleDbCommandBuilder(oleAdaptTsgTgt); int tdgMatch = 0, tsgMatch = 0; foreach (DataRow dr in dtTdg.Rows) { foreach (DataRow dr2 in dtTdgTgt.Rows) { if (dr.ItemArray.ToString().CompareTo(dr2.ItemArray.ToString()) == 0) { tdgMatch++; } } } foreach (DataRow dr in dtTsg.Rows) { foreach (DataRow dr2 in dtTsgTgt.Rows) { if (dr.ItemArray.ToString().CompareTo(dr2.ItemArray.ToString()) == 0) { tsgMatch++; } } } //flag value compare difference in both row and value if (tdgMatch != dtTdg.Rows.Count || tsgMatch != dtTsg.Rows.Count || dtTdg.Rows.Count != dtTdgTgt.Rows.Count || dtTsg.Rows.Count != dtTsgTgt.Rows.Count) { // blnRowDiff=true; if (dtTdgTgt.Rows.Count > 0 && overwrite) { OLE.OleDbCommand oCmd = oleConx2Proc.CreateCommand(); oCmd.CommandText = strSqlDelTdg; oCmd.ExecuteNonQuery(); dtTdgTgt.Rows.Clear(); dtTdgTgt.AcceptChanges(); } } if (dtTsgTgt.Rows.Count > 0 && overwrite) { OLE.OleDbCommand oCmd = oleConx2Proc.CreateCommand(); oCmd.CommandText = strSqlDelTsg; oCmd.ExecuteNonQuery(); dtTsgTgt.Rows.Clear(); dtTsgTgt.AcceptChanges(); } dtTdgTgt.BeginLoadData(); dtTsgTgt.BeginLoadData(); foreach (DataRow dr in dtTdg.Rows) { dtTdgTgt.LoadDataRow(dr.ItemArray, false); } foreach (DataRow dr in dtTsg.Rows) { dtTsgTgt.LoadDataRow(dr.ItemArray, false); } dtTdgTgt.EndLoadData(); dtTsgTgt.EndLoadData(); oleAdaptTdgTgt.Update(dtTdgTgt); dtTdgTgt.AcceptChanges(); oleAdaptTsgTgt.Update(dtTsgTgt); dtTsgTgt.AcceptChanges(); } catch (OLE.OleDbException oe) { throw oe; } finally { if (oleConx2Master.State == ConnectionState.Open) { oleConx2Master.Close(); } oleConx2Master = null; oleAdaptTdg = null; oleAdaptTsg = null; if (oleConx2Proc.State == ConnectionState.Open) { oleConx2Proc.Close(); } oleAdaptTdgTgt = null; oleAdaptTsgTgt = null; } }
public void InsertQuery(string QueryText, DataSet ds) { string path = HttpContext.Current.Server.MapPath("~/App_Data/ARC_ORG_DB.mdb"); OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path); OleDbCommand sqlcmd = new OleDbCommand(QueryText, connection); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.InsertCommand = sqlcmd; adapter.Update(ds); }
//1) Delete the recs from FFE with related RX ID //2) Append into FFE from FVS_Potfire public int DbRecsUpdateFfeFromPotFire(string strRxFilePath) { int intRet = 0; string strRxId = this.UtilHelperGetRxString(strRxFilePath); string strSqlSrcPotFire = "SELECT * FROM FVS_PotFire"; string strSqlTgtFfe = "SELECT * FROM FFE WHERE biosum_cond_id = '{0}'"; // TODO: NOT_CALC_YN VALUE? string[] strSqlSrcFilterPotFire = new string[15] { "StandID", "CaseID", " ", "Flame_Len_Severe", "Flame_Len_Mod", "Fire_Type_Severe", "Fire_Type_Mod", "Torch_Index", "Crown_Index", "Canopy_Ht", "Canopy_Density", "Mortality_BA_Severe", "Mortality_BA_Mod", "Mortality_VOL_Severe", "Mortality_VOL_Mod" }; string[] strSqlTgtFilterFfe = new string[15] { "biosum_cond_id", "rx", "post_not_calc_yn", "post_tot_flame_sev", "post_tot_flame_mod", "post_fire_type_sev", "post_fire_type_mod", "post_torch_index", "post_crown_index", "post_canopy_ht", "post_canopy_density", "post_mortality_ba_sev", "post_mortality_ba_mod", "post_mortality_vol_sev", "post_mortality_vol_mod" }; DataTable dtSrcPotFire = null; DataTable dtTgtFfe = null; OLE.OleDbConnection oleConxSrc = null; OLE.OleDbDataAdapter oleAdaptSrc = null; OLE.OleDbConnection oleConxTgt2 = null; OLE.OleDbDataAdapter oleAdaptTgt2 = null; OLE.OleDbCommandBuilder oleCmdBTgt2 = null; try { oleConxSrc = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, string.Format(this.strPathFvsDbFile, strRxId))); oleConxSrc.Open(); oleAdaptSrc = new System.Data.OleDb.OleDbDataAdapter(string.Format(strSqlSrcPotFire, strRxId), oleConxSrc); dtSrcPotFire = new DataTable("FVSPOTFIRE"); oleAdaptSrc.Fill(dtSrcPotFire); oleConxTgt2 = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, this.strPathMasterDbFile)); oleConxTgt2.Open(); oleAdaptTgt2 = new System.Data.OleDb.OleDbDataAdapter(strSqlTgtFfe, oleConxTgt2); dtTgtFfe = new DataTable("FFE"); oleAdaptTgt2.Fill(dtTgtFfe); oleCmdBTgt2 = new System.Data.OleDb.OleDbCommandBuilder(oleAdaptTgt2); if (dtTgtFfe.Rows.Count > 0) { DataRow[] drs = dtTgtFfe.Select(string.Format(cStrRxFilter, strRxId)); foreach (DataRow dr in drs) { dr.Delete(); } oleAdaptTgt2.Update(dtTgtFfe); dtTgtFfe.AcceptChanges(); } if (dtSrcPotFire.Rows.Count > 1) { dtTgtFfe.BeginLoadData(); for (short i = 0; i < dtSrcPotFire.Rows.Count; i++) { dtTgtFfe.LoadDataRow(dtSrcPotFire.Rows[i].ItemArray, false); dtTgtFfe.Rows[i]["rx"] = strRxId; intRet++; } dtTgtFfe.EndLoadData(); } oleAdaptTgt2.Update(dtTgtFfe); dtTgtFfe.AcceptChanges(); } catch (Exception e) { throw e; } finally { if (oleConxTgt2.State == ConnectionState.Open) { oleConxTgt2.Close(); } oleConxTgt2 = null; } return(0); }
//TODO: change back to Private public int DbRecsFinalizeFvsTree(string strRxFilePath) { int intRet = 0; int intExe = 0; string strRxId = this.UtilHelperGetRxString(strRxFilePath); string strSqlSrc = "SELECT * FROM FVS_CUTLIST"; string strSqlTgt = "SELECT * FROM FVS_TREE"; //N,N,T string[] strSqlSrcFilterFvsCutlist = new string[27] { "Id", "StandID", "getRxId", "Year", "PrdLen", "TreeId", "Species", "TreeVal", "SSCD", "PtIndex", "TPA", "MortPA", "DBH", "DG", "Ht", "HtG", "PctCr", "CrWidth", "MistCD", "BAPctile", "PtBAL", "TCuFt", "MCuFt", "BdFt", "MDefect", "BDefect", "TruncHt" }; //N,T,T string[] strSqlTgtFilterFvsTree = new string[27] { "Id", "biosum_cond_id", "rx", "Year", "PrdLen", "Tid", "Species", "TreeValue", "SSCD", "PtIndex", "TPA", "MortPA", "DBH", "DG", "Ht", "HtG", "PctCr", "CrWidth", "MistCD", "BAPctile", "PtBAL", "TCuFt", "MCuFt", "BdFt", "MDefect", "BDefect", "TruncHt" }; DataTable dtTgtFvsTree = null; DataTable dtSrcFvsCutList = null; OLE.OleDbConnection oleConxTgt = null; OLE.OleDbDataAdapter oleAdaptTgt = null; OLE.OleDbCommandBuilder oleCmdBTgt = null; OLE.OleDbCommand oCmd = null; OLE.OleDbConnection oleConxSrc = null; OLE.OleDbDataAdapter oleAdaptSrc = null; try { oleConxTgt = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, this.strPathProcDbFile)); oleConxTgt.Open(); oleAdaptTgt = new System.Data.OleDb.OleDbDataAdapter(strSqlTgt, oleConxTgt); dtTgtFvsTree = new DataTable("FVS_Tree"); oleCmdBTgt = new System.Data.OleDb.OleDbCommandBuilder(oleAdaptTgt); oleAdaptTgt.Fill(dtTgtFvsTree); if (dtTgtFvsTree.Rows.Count > 0) { DataRow[] drDel = dtTgtFvsTree.Select(string.Format(cStrRxFilter, strRxId)); if (drDel.Length > 0) { foreach (DataRow dr in drDel) { dr.Delete(); //delete old Records base on RX ID } oleAdaptTgt.Update(dtTgtFvsTree); dtTgtFvsTree.AcceptChanges(); } } oleConxSrc = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, string.Format(this.strPathFvsDbFile, strRxId))); oleConxSrc.Open(); oleAdaptSrc = new System.Data.OleDb.OleDbDataAdapter(strSqlSrc, oleConxSrc); dtSrcFvsCutList = new DataTable("FVS_CUTLIST"); oleAdaptSrc.Fill(dtSrcFvsCutList); if (dtSrcFvsCutList.Rows.Count > 1) { dtTgtFvsTree.BeginLoadData(); foreach (DataRow dr in dtSrcFvsCutList.Rows) { dtTgtFvsTree.LoadDataRow(dr.ItemArray, false); DataRow drCur = dtTgtFvsTree.Rows[intRet]; drCur.BeginEdit(); drCur["rx"] = strRxId; drCur["Tid"] = dr["TreeId"].ToString().Trim(); //Data Quality Issue drCur.EndEdit(); intRet++; } dtTgtFvsTree.EndLoadData(); dtTgtFvsTree.AcceptChanges(); string[] strArySql = this.DbHelperStrSqlInserts(dtTgtFvsTree); //See Documentation oCmd = oleConxTgt.CreateCommand(); for (short x = 0; x < strArySql.Length; x++) { oCmd.CommandText = strArySql[x].Trim(); oCmd.CommandType = CommandType.Text; intExe = oCmd.ExecuteNonQuery(); } } } catch (OLE.OleDbException oe) { throw oe; } catch (Exception e) { throw e; } finally { if (oleConxSrc.State == ConnectionState.Open) { oleConxSrc.Close(); } if (oleConxTgt.State == ConnectionState.Open) { oleConxTgt.Close(); } oleConxSrc = null; oleConxTgt = null; } return(intExe); }
/// <summary> /// 更新整个DataTable /// </summary> /// <param name="table">要更新的DataTable</param> /// <param name="tableName">更新的表名</param> /// <returns></returns> public int UpdateDataTable(DataTable table, string tableName) { try { Open(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from " + tableName; OleDbDataAdapter da = new OleDbDataAdapter(cmd); OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da); da.UpdateCommand = cmdBuilder.GetUpdateCommand(); int val = da.Update(table); da.Dispose(); return val; } catch (OleDbException e) { this.Close(); throw e; } }
/// <summary>Saves the passed System description to the passed System ID if it exists. If it doesn't exist, creates a new row.</summary> /// <param name="pID">ID of the System to update (if it exists).</param> /// <param name="pDescription">Description to set.</param> /// <returns>ID of the modified/created row.</returns> public int Save(int pSystemID, int pSubSystemID, string pDescription) { int iID = (pSubSystemID > 0) ? pSubSystemID : 1; try { using(OleDbDataAdapter oDA = new OleDbDataAdapter(String.Format("select * from SubSystems where system_id = {0}", pSystemID), this.m_oDBConn.ConnectionString)) using(OleDbCommandBuilder oCB = new OleDbCommandBuilder(oDA)) using(OleDbConnection oConn = new OleDbConnection(this.m_oDBConn.ConnectionString)) { oConn.Open(); DataSet oDS = new DataSet("SubSystems"); oDA.Fill(oDS); if((oDS.Tables[0].Rows.Count > 0) && (pSubSystemID > 0)) { // Should only be one row returned, but go ahead and update all found. foreach(DataRow oRow in oDS.Tables[0].Rows) { if((int)oRow["subsystem_id"] == pSubSystemID) { oRow.BeginEdit(); oRow["subsystem_desc"] = pDescription; oRow["date_last_modified"] = DateTime.Now; oRow.EndEdit(); } } } else { // Get the next available SubSystem ID. iID = this.DetermineNextID(); DataRow oRow = oDS.Tables[0].NewRow(); oRow["system_id"] = pSystemID; oRow["subsystem_id"] = iID; oRow["subsystem_desc"] = pDescription; oRow["date_created"] = DateTime.Now; oRow["date_last_modified"] = DateTime.Now; oDS.Tables[0].Rows.Add(oRow); } oDA.Update(oDS); } } catch(Exception e) { throw new Exception("Unable to save new System!\nError: " + e.Message); } return iID; }
public void run() { Exception exp = null; OleDbDataAdapter oleDBda = new OleDbDataAdapter(); oleDBda.SelectCommand = new OleDbCommand("",new OleDbConnection()); base.OleDbDataAdapter_BuildUpdateCommands(ref oleDBda); // --------- get data from DB ----------------- DataSet ds = base.PrepareDBData_Update((DbDataAdapter)oleDBda); // add event handler oleDBda.RowUpdating +=new OleDbRowUpdatingEventHandler(oleDBda_RowUpdating); //insert ,delete, update drInsert = ds.Tables[0].NewRow(); drInsert.ItemArray = new object[] {9991,"Ofer","Borshtein","Insert"}; drDelete = ds.Tables[0].Rows.Find(9992); drUpdate = ds.Tables[0].Rows.Find(9993); ds.Tables[0].Rows.Add(drInsert); drDelete.Delete(); drUpdate["Title"] = "Jack the ripper"; //execute update to db, will raise events oleDBda.Update(ds); try { BeginCase("EventCounter "); Compare(EventCounter ,3); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} oleDBda.RowUpdating -=new OleDbRowUpdatingEventHandler(oleDBda_RowUpdating); //close connection if ( ((IDbDataAdapter)oleDBda).SelectCommand.Connection.State != ConnectionState.Closed ) ((IDbDataAdapter)oleDBda).SelectCommand.Connection.Close(); }
private void NoIdTeacher() { SqlHelper help = new SqlHelper(); DataTable dtClass = help.getDs("select * from Classes_Data","Classes_Data").Tables[0]; DataRow[] NoIDteacher = dtClass.Select("Teacher_ID='" + "0000000000" + "' "); daTeachers = help.adapter("select * from Teachers_Data"); dtTeachers = new System.Data.DataTable(); daTeachers.Fill(dtTeachers); daTeachers.FillSchema(dtTeachers, SchemaType.Source); List<string> teachers = new List<string> { }; DataTable Idteacher_dt = dtTeachers.Copy(); // 获取Class_Data的架构 Idteacher_dt.Clear(); for (int i = 0; i < NoIDteacher.Length; i++) { teachers.Add(NoIDteacher[i][2].ToString()); } string[] newteachers = teachers.Distinct<string>().ToArray(); teachers.Clear(); for (int i = 0; i < dtTeachers.Rows.Count;i++ ) { teachers.Add(dtTeachers.Rows[i][1].ToString()); } string[] oldteachers = teachers.Distinct<string>().ToArray(); for (int z = 0; z < newteachers.Length; z++) { for (int i = 0; i < oldteachers.Length;i++ ) { if (newteachers[z].ToString().Equals(oldteachers[i].ToString())) { newteachers[z] = ""; } } } for (int i = 0; i < newteachers.Length;i++ ) { if(newteachers[i]!=""){ DataRow dr_teacher = Idteacher_dt.NewRow(); dr_teacher[1] = newteachers[i].ToString(); dr_teacher[0] = newteachers[i].ToString(); dr_teacher[7] = 0; dr_teacher[8] = 0; dr_teacher[9] = 0; dr_teacher[10] = 0; Idteacher_dt.Rows.Add(dr_teacher); } } dtTeachers.Merge(Idteacher_dt, true); daTeachers.Update(dtTeachers); }
/// <summary> /// 关闭修改模式,并根据DataTable进行更新操作 /// </summary> /// <param name="DataTableSource">要提交的数据表</param> public override void Update(System.Data.DataTable DataTableSource) { dataManager.execNum++; _dap.Update(DataTableSource); DecideRelease(); }
/// <summary> /// Write database content. /// </summary> /// <param name="filePath">Export file path.</param> /// <param name="tables">Table definitions.</param> /// <param name="schedules">Schedules to export.</param> /// <param name="routes">Routes to export.</param> /// <param name="imageExporter">Map image exporter.</param> /// <param name="tracker">Cancel tracker (can be null).</param> private void _WriteContent(string filePath, ICollection<ITableDefinition> tables, ICollection<Schedule> schedules, ICollection<Route> routes, MapImageExporter imageExporter, ICancelTracker tracker) { OleDbConnection connection = new OleDbConnection(_GetConnectionString(filePath)); try { foreach (ITableDefinition tableDef in tables) { _CheckCancelState(tracker); TableDescription tableDescription = _structureKeeper.GetTableDescription(tableDef.Type); DataKeeper data = new DataKeeper(_listSeparator, tableDescription); ICollection<string> fields = tableDef.Fields; string tableName = tableDef.Name; // obtain dataset string selectCommand = string.Format(SQL_SELECT_COMMAND_FORMAT, tableName); OleDbCommand accessCommand = new OleDbCommand(selectCommand, connection); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(accessCommand); DataSet dataSet = new DataSet(tableName); dataAdapter.Fill(dataSet, tableName); // select table DataTable table = dataSet.Tables[tableName]; // write data to table switch (tableDef.Type) { case TableType.Schedules: _WriteSchedules(schedules, fields, data, table, tracker); break; case TableType.Routes: { _WriteRoutes(schedules, routes, fields, data, imageExporter, table, tracker); break; } case TableType.Stops: { _WriteStops(schedules, routes, fields, data, imageExporter, table, tracker); break; } case TableType.Schema: _WriteSchema(fields, table, tracker); break; default: Debug.Assert(false); // NOTE: not supported break; } _CheckCancelState(tracker); // set insert command OleDbCommand insertCommand = _CreateInsertCommand(tableName, tableDescription, fields); insertCommand.Connection = connection; dataAdapter.InsertCommand = insertCommand; // store changes dataAdapter.Update(table); } } catch (Exception) { } finally { connection.Close(); } }
private void ExportAccess() { bool IsTable = false; OleDbConnection conn = new OleDbConnection(); string connectionstring = m_pTaskData.DataSource; //判断是否为新建表 string tName = m_pTaskData.DataTableName; conn.ConnectionString = connectionstring; try { conn.Open(); } catch (System.Exception ex) { if (e_PublishLog != null) { WriteLog(ex.Message); e_PublishLog(this, new PublishLogEventArgs(this.TaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务:" + this.TaskData.TaskName + "发布Access失败,错误信息为:" + ex.Message + "\n")); } return; } System.Data.DataTable tb = conn.GetSchema("Tables"); foreach (DataRow r in tb.Rows) { if (r[3].ToString() == "TABLE") { if (r[2].ToString() == tName) { IsTable = true; break; } } } if (IsTable == false) { //需要建立新表,建立新表的时候采用ado.net新建行的方式进行数据增加 string CreateTablesql = getCreateTablesql(cGlobalParas.DatabaseType.Access, ""); OleDbCommand com = new OleDbCommand(); com.Connection = conn; com.CommandText = CreateTablesql; com.CommandType = CommandType.Text; try { int result = com.ExecuteNonQuery(); } catch (System.Data.OleDb.OleDbException ex) { if (ex.ErrorCode != -2147217900) { if (e_PublishLog != null) { WriteLog(ex.Message); e_PublishLog(this, new PublishLogEventArgs(this.TaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务:" + this.TaskData.TaskName + "发布MySql失败,错误信息为:" + ex.Message + "\n")); } throw ex; } } try { System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + tName, conn); System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds, m_pTaskData.DataTableName); for (int i = 0; i < m_pTaskData.PublishData.Rows.Count; i++) { DataRow dr = ds.Tables[0].NewRow(); for (int j = 0; j < m_pTaskData.PublishData.Columns.Count; j++) { dr[j] = m_pTaskData.PublishData.Rows[i][j].ToString(); } ds.Tables[0].Rows.Add(dr); } int m = da.Update(ds.Tables[0]); } catch (System.Exception ex) { if (e_PublishLog != null) { WriteLog(ex.Message); e_PublishLog(this, new PublishLogEventArgs(this.TaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务:" + this.TaskData.TaskName + "发布Access失败,错误信息为:" + ex.Message + "\n")); } return; } } else { try { //无需建立新表,需要采用sql语句的方式进行,但需要替换sql语句中的内容 System.Data.OleDb.OleDbCommand cm = new System.Data.OleDb.OleDbCommand(); cm.Connection = conn; cm.CommandType = CommandType.Text; //开始拼sql语句 string sql = ""; for (int i = 0; i < m_pTaskData.PublishData.Rows.Count; i++) { sql = m_pTaskData.InsertSql; for (int j = 0; j < m_pTaskData.PublishData.Columns.Count; j++) { string strPara = "{" + m_pTaskData.PublishData.Columns[j].ColumnName + "}"; //string strParaValue = m_pTaskData.PublishData.Rows[i][j].ToString(); string strParaValue = m_pTaskData.PublishData.Rows[i][j].ToString().Replace("\"", "\"\""); sql = sql.Replace(strPara, strParaValue); } cm.CommandText = sql; cm.ExecuteNonQuery(); } } catch (System.Exception ex) { if (e_PublishLog != null) { WriteLog(ex.Message); e_PublishLog(this, new PublishLogEventArgs(this.TaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务:" + this.TaskData.TaskName + "发布Access失败,错误信息为:" + ex.Message + "\n")); } return; } } conn.Close(); }