public bool InsertMember(string number) { try { DS.Clear(); adapter.SelectCommand = new OracleCommand("select * from member where branch_id ='" + branch_id + "'", conn); adapter.Fill(DS, "Member"); DataRow newRow = DS.Tables["Member"].NewRow(); newRow["branch_id"] = branch_id; newRow["phone_number"] = number; newRow["create_at"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); DS.Tables["Member"].Rows.Add(newRow); adapter.Update(DS, "Member"); DS.AcceptChanges(); adapter.SelectCommand = new OracleCommand("select * from member where phone_number='" + number + "'", conn); DS.Clear(); adapter.Fill(DS, "Member"); member_id = DS.Tables["Member"].Rows[0]["member_id"].ToString(); return(true); } catch (Exception e) { Console.WriteLine(e.Message + "이거다"); } return(false); }
private void button2_Click(object sender, EventArgs e) { try { dataAdapter.Update(dataSet.Tables[0]); } catch (OracleException ex) { if (ex.Number == 1400) { MessageBox.Show("Wartość w następnej kolumnie nie może byc pusta"); } if (ex.Number == 2291) { MessageBox.Show("Wartość klucza w jednej z kolumn jest nieprawidłowa"); } if (ex.Number == 2290) { MessageBox.Show("Wartość w kolumnie: " + ex.Message.ToString() + " nie jest prawidłowa"); } if (ex.Number == 1) { MessageBox.Show("Wartość klucza musi być unikatowa"); } } }
private void button_zapisz_Click(object sender, EventArgs e) { dataGridView1.ReadOnly = true; data_adapter.Update(wybor_pracownicy.Tables[0]); data_adapter.Update(wybor_sprzet.Tables[0]); data_adapter.Update(wybor_mieszkania.Tables[0]); }
public static void Main() { string str = "data source=topcredu;User ID=scott;Password=tiger"; OracleConnection Conn = new OracleConnection(str); Conn.Open(); // 갱신 및 기타 작업을 위한 Adapter를 하나 만든다. OracleDataAdapter adapter = new OracleDataAdapter("select * from emp", Conn); DataSet ds = new DataSet("myemp"); adapter.Fill(ds, "사원"); //가지고 온 자료를 화면에 뿌리자... foreach (DataRow r in ds.Tables["사원"].Rows) { Console.WriteLine("Empno : {0}, Ename : {1}, Sal : {2}", r["empno"], r["ename"], r["sal"]); } //데이터를 변경 하자... ds.Tables["사원"].Rows[0]["ename"] = "홍길동"; /*Update Method를 호출 하기 위해 CommandBuilder를 만들자. * 하나의 테이블을 갱신하는 간단한 경우 라면 변경용 Query를 사용 할 필요가 없다. * 단지 자동적으로 적절핚 SQL을 만들고 그것을 관련된 DataAdapter에 연관 시키는 * CommandBuilder 개체를 사용하면 된다. * CommandBuilder에 DataAdapter를 인자로 * 주었는데 이렇게 하면 CommandBuilder가 생성될때 생성자에 의해 적절핚 * SQL 명령문이 만들어지며 그것이 DataAdapter에 연결 된다. */ OracleCommandBuilder thisBuilder = new OracleCommandBuilder(adapter); /* Update 메소드는 DataTable의 행들 중 변경된 데이터를 데이터베이스에 * 적용한다. Rows 컬렉션안의 DataRow 개체에는 RowState 라는 속성이 있는데 * 이 속성은 그 행의 삭제, 추가, 수정 여부 상태를 의미한다. */ adapter.Update(ds, "사원"); //변경 후 DataTable 자료 출력 Console.WriteLine(); foreach (DataRow r in ds.Tables["사원"].Rows) { Console.WriteLine("Empno : {0}, Ename : {1}, Sal : {2}", r["empno"], r["ename"], r["sal"]); } DataRow row = ds.Tables["사원"].NewRow(); row["empno"] = 8788; row["ename"] = "87길동"; row["sal"] = 7777; ds.Tables["사원"].Rows.Add(row); adapter.Update(ds, "사원"); //다시 DB에서 데이터 원본을 추출, 테이블의 내용이 바뀐 것을 확인하자. adapter = new OracleDataAdapter("select * from emp", Conn); adapter.Fill(ds, "사원"); //추가 후 자료 출력 Console.WriteLine(); foreach (DataRow r in ds.Tables["사원"].Rows) { Console.WriteLine("Empno : {0}, Ename : {1}, Sal : {2}", r["empno"], r["ename"], r["sal"]); } Console.WriteLine(" 총 {0} 건 입니다.", ds.Tables["사원"].Rows.Count); }
static void Main(string[] args) { string str = "data source=topcredu;User ID=scott;Password=tiger"; OracleConnection Conn = new OracleConnection(str); Conn.Open(); OracleDataAdapter adapter = new OracleDataAdapter("select * from emp", Conn); DataSet ds = new DataSet("myemp"); adapter.Fill(ds, "사원"); foreach (DataRow r in ds.Tables["사원"].Rows) { Console.WriteLine("Empno : {0}, Ename : {1}, Sal : {2}", r["empno"], r["ename"], r["sal"]); } ds.Tables["사원"].Rows[0]["ename"] = "홍길동"; OracleCommandBuilder thisBuilder = new OracleCommandBuilder(adapter); adapter.Update(ds, "사원"); Console.WriteLine(); foreach (DataRow r in ds.Tables["사원"].Rows) { Console.WriteLine("Empno : {0}, Ename : {1}, Sal : {2}", r["empno"], r["ename"], r["sal"]); } DataRow row = ds.Tables["사원"].NewRow(); row["empno"] = 5868; row["ename"] = "5868길동"; row["sal"] = 5868; ds.Tables["사원"].Rows.Add(row); adapter.Update(ds, "사원"); adapter = new OracleDataAdapter("select * from emp", Conn); adapter.Fill(ds, "사원"); Console.WriteLine(); foreach (DataRow r in ds.Tables["사원"].Rows) { Console.WriteLine("Empno : {0}, Ename : {1}, Sal : {2}", r["empno"], r["ename"], r["sal"]); } Console.WriteLine("총 {0} 건 입니다.", ds.Tables["사원"].Rows.Count); }
static void Main(string[] args) { string str = @"Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.27)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = topcredu) ) ) ;User Id=scott;Password=tiger;"; //string str = "data source=topcredu;UserID = scott; Password = tiger"; OracleConnection Conn = new OracleConnection(str); Conn.Open(); OracleDataAdapter adapter = new OracleDataAdapter("select * from emp", Conn); DataSet ds = new DataSet("myemp"); adapter.Fill(ds, "사원"); foreach (DataRow r in ds.Tables["사원"].Rows) { Console.WriteLine("Empno : {0}, Ename : {1}, Sal : {2}", r["empno"], r["ename"], r["sal"]); } ds.Tables["사원"].Rows[0]["ename"] = "홍길동"; OracleCommandBuilder thisBuilder = new OracleCommandBuilder(adapter); adapter.Update(ds, "사원"); Console.WriteLine(); foreach (DataRow r in ds.Tables["사원"].Rows) { Console.WriteLine("Empno : {0}, Ename : {1}, Sal : {2}", r["empno"], r["ename"], r["sal"]); } DataRow row = ds.Tables["사원"].NewRow(); row["empno"] = 9766; row["ename"] = "89길동"; row["sal"] = 1717; ds.Tables["사원"].Rows.Add(row); adapter.Update(ds, "사원"); adapter = new OracleDataAdapter("select * from emp", Conn); adapter.Fill(ds, "사원"); Console.WriteLine(); foreach (DataRow r in ds.Tables["사원"].Rows) { Console.WriteLine("Empno : {0}, Ename : {1}, Sal : {2}", r["empno"], r["ename"], r["sal"]); } Console.WriteLine("총 {0}건입니다. ", ds.Tables["사원"].Rows.Count); }
public int EditDBFrmDataTable(string TableStrcutSQL, DataTable dt) { if (dt == null || dt.Rows.Count <= 0) { return(0); } try { Conn = OracleConnManager.GetCon(); OracleCommand sqlCommand = new OracleCommand(TableStrcutSQL, Conn); OracleDataAdapter sqlAdapater = new OracleDataAdapter(sqlCommand); //取个结构 OracleCommandBuilder sqlBuilder = new OracleCommandBuilder(sqlAdapater); //必须有 DataTable dt1 = dt.GetChanges(DataRowState.Deleted); if (dt1 == null || dt1.Rows.Count <= 0) { } else { sqlAdapater.Fill(dt1); sqlAdapater.Update(dt1); } DataTable dt2 = dt.GetChanges(DataRowState.Modified); if (dt2 == null || dt2.Rows.Count <= 0) { } else { sqlAdapater.Fill(dt2); sqlAdapater.Update(dt2); } DataTable dt3 = dt.GetChanges(DataRowState.Added); if (null == dt3 || dt3.Rows.Count <= 0) { } else { sqlAdapater.Fill(dt3); sqlAdapater.Update(dt3); } return(0); } finally { CloseConnection(); } }
void MedStoreLoad() { try { conn.Open(); string sqlquery = "SELECT * FROM MED_STORE"; //string sqlquery = "SELECT * FROM MED_INFO"; OracleCommand cmd = new OracleCommand(sqlquery, conn); OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; dt = new DataTable(); // dt.Columns["MED_NAME"].ColumnName = "Name"; oda.Fill(dt); BindingSource bsource = new BindingSource(); bsource.DataSource = dt; dataGridView1.DataSource = bsource; oda.Update(dt); dt.Columns[0].ColumnName = "ID"; dt.Columns[1].ColumnName = "Name"; dt.Columns[2].ColumnName = "Quantity"; dt.Columns[3].ColumnName = "Damage Qty"; dt.Columns[4].ColumnName = "Reorder Level"; dt.AcceptChanges(); conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
void stuffload() { try { conn.Open(); string sqlquery = "SELECT EMP_ID,EMP_NAME,MOBILE,JOIN_DATE,POSITION,SALARY FROM EMPLOYE_INFO"; //string sqlquery = "SELECT * FROM MED_INFO"; OracleCommand cmd = new OracleCommand(sqlquery, conn); OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; dt = new DataTable(); // dt.Columns["MED_NAME"].ColumnName = "Name"; oda.Fill(dt); BindingSource bsource = new BindingSource(); bsource.DataSource = dt; dataGridView1.DataSource = bsource; oda.Update(dt); dt.Columns[0].ColumnName = "ID"; dt.Columns[1].ColumnName = "Name"; dt.Columns[2].ColumnName = "Mobile"; dt.Columns[3].ColumnName = "Joining Date"; dt.Columns[4].ColumnName = "Position"; dt.Columns[4].ColumnName = "Salary"; dt.AcceptChanges(); conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void AddNewBroker(Broker brokerToAdd) { // disconnected version string sqlStatement = "INSERT INTO brokers(broker_id, first_name, last_name) VALUES (:broker_id, :first_name, :last_name)"; IDbConnection connection = new OracleConnection(connectionString); OracleCommand command = new OracleCommand(sqlStatement, (OracleConnection)connection); command.BindByName = true; IDbDataParameter param = new OracleParameter(":first_name", OracleDbType.Varchar2, 25); param.Value = brokerToAdd.firstName; command.Parameters.Add(param); param = new OracleParameter(":last_name", OracleDbType.Varchar2, 25); param.Value = brokerToAdd.lastName; command.Parameters.Add(param); param = new OracleParameter(":broker_id", OracleDbType.Int16, 50); param.Value = brokerToAdd.id; command.Parameters.Add(param); IDbDataAdapter da = new OracleDataAdapter(); da.InsertCommand = command; DataRow newRow = dataSet.Tables[0].NewRow(); newRow["broker_id"] = brokerToAdd.id; newRow["first_name"] = brokerToAdd.firstName; newRow["last_name"] = brokerToAdd.lastName; dataSet.Tables[0].Rows.Add(newRow); // it's not clear from this code but we could make many changes to // the dataset before submitting back to the database. connection.Open(); da.Update(dataSet); connection.Close(); }
/// <summary> /// 更新数据库 /// </summary> /// <param name="vConnection">数据库连接</param> /// <param name="vTableName">表名</param> /// <param name="vUpdateDataSource">用于更新的DataTable</param> public void ApplyOperation(OracleConnection vConnection, string vTableName, DataTable vUpdateDataSource) { string strSql = "Select * from " + vTableName; OracleCommand pDBCommand = null; OracleDataAdapter pDataAdapter = null; OracleCommandBuilder pCommandBuilder = null; DataTable pDataTable; try { pDBCommand = new OracleCommand(strSql, vConnection); pDataAdapter = new OracleDataAdapter(pDBCommand); pCommandBuilder = new OracleCommandBuilder(pDataAdapter); pDataAdapter.Update(vUpdateDataSource); } catch (Exception ex) { //ModDeclare.g_ErrorHandler.DisplayInformation("ClsDatabaseOperation中ApplyOperation出错:" + ex.Message, false,null,null); MessageBox.Show("ClsDatabaseOperation中ApplyOperation出错:" + ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); } finally { pDBCommand = null; pDataAdapter = null; pCommandBuilder = null; pDataTable = null; } }
public int DataTableToDB(DataTable dt) { using (OracleConnection selectConnection = new OracleConnection(this.ConnectionString)) { try { selectConnection.Open(); using (OracleDataAdapter dataAdapter = new OracleDataAdapter("select * from " + dt.TableName + " where 1=0", selectConnection)) { DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); foreach (DataRow row in (InternalDataCollectionBase)dt.Rows) { dataTable.ImportRow(row); } OracleCommandBuilder oracleCommandBuilder = new OracleCommandBuilder(dataAdapter); return(dataAdapter.Update(dataTable)); } } catch (OracleException ex) { throw ex; } } }
public bool insertOracle(DataTable dataTable, string sql) // Oracle sql 查询的是表头 { string ConnStr = connectionString; using (OracleConnection conn = new OracleConnection(ConnStr)) { try { OracleCommand cmd = new OracleCommand(sql, conn); OracleDataAdapter adapter = new OracleDataAdapter(cmd); OracleCommandBuilder cb = new OracleCommandBuilder(adapter); DataTable dsNew = new DataTable(); int count = adapter.Fill(dsNew); for (int i = 0; i < dataTable.Rows.Count; i++) { DataRow dr = dsNew.NewRow(); for (int j = 0; j < dataTable.Columns.Count; j++) { dr[dsNew.Columns[j].ColumnName] = dataTable.Rows[i][j]; } dsNew.Rows.Add(dr); } count = adapter.Update(dsNew); adapter.UpdateBatchSize = 5000; //adapter.Update(dataTable); return(true); } catch (Exception e) { //LogHelper.WriteErrLog("insertOracle", ex.Message); logger.Info(e.Message); return(false); } } }
public override int SaveTable(DataTable dataTable, string sql) { Console.WriteLine("Saving " + dataTable.TableName); DataSet myDataSet = new DataSet(); myDataSet.Tables.Add(dataTable.TableName); OracleConnection myAccessConn = new OracleConnection(ConnectionString); OracleCommand myAccessCommand = new OracleCommand(sql, myAccessConn); OracleDataAdapter myDataAdapter = new OracleDataAdapter(myAccessCommand); OracleCommandBuilder cb = new OracleCommandBuilder(myDataAdapter); // myDataAdapter.InsertCommand = cb.GetInsertCommand(); this.lastSqlCommand = sql; SqlCommands.Add(sql); myAccessConn.Open(); int recordCount = 0; try { // call Fill method only to make things work. (we ignore myDataSet) myDataAdapter.Fill(myDataSet, dataTable.TableName); recordCount = myDataAdapter.Update(dataTable); } finally { myAccessConn.Close(); } return(recordCount); }
void MedStoreLoad() { try { conn.Open(); string sqlquery = "SELECT * FROM MED_STORE"; //string sqlquery = "SELECT * FROM MED_INFO"; OracleCommand cmd = new OracleCommand(sqlquery, conn); OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; dt = new DataTable(); // dt.Columns["MED_NAME"].ColumnName = "Name"; oda.Fill(dt); BindingSource bsource = new BindingSource(); bsource.DataSource = dt; dataGridView1.DataSource = bsource; oda.Update(dt); dt.Columns[0].ColumnName = "ID"; dt.Columns[1].ColumnName = "Name"; dt.Columns[2].ColumnName = "Quantity"; dt.Columns[3].ColumnName = "Damage Qty"; dt.Columns[4].ColumnName = "Reorder Level"; dt.AcceptChanges(); conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
/// <summary> /// 保存 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void barButtonItem3_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { if (!gridView1.PostEditor()) { return; } if (!gridView1.UpdateCurrentRow()) { return; } if (!saveCheck()) { return; } try { ti01Adapter.Update(dt_ti01); MessageBox.Show("保存成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } catch (Exception ee) { MessageBox.Show(ee.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } }
private void save_button_Click(object sender, EventArgs e) { if (dBManager.GetConnection() == true) { using (OracleCommand cmd = new OracleCommand()) { OracleTransaction tran = dBManager.Connection.BeginTransaction(); cmd.Transaction = tran; cmd.Connection = dBManager.Connection; empno = P_info.Empno; cmd.CommandText = "select * from thrm_car_ljm where car_empno = " + empno; adapter.SelectCommand = cmd; try { OracleCommandBuilder cb = new OracleCommandBuilder(adapter); adapter.Update(ds, "Info"); dataGridView5.DataSource = ds.Tables["Info"]; tran.Commit(); } catch (Exception ex) { MessageBox.Show(ex.Message); tran.Rollback(); } } } }
protected override ExeResEdm UpdateDtToDB(DataTable dtInfos, string strComFields = "*") { ExeResEdm dBResEdm = new ExeResEdm(); string strTableName = dtInfos.TableName; try { using (OracleConnection conn = new OracleConnection(connstr)) { conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = GetColumnsNameSql(strTableName, strComFields); OracleDataAdapter adapter = new OracleDataAdapter(cmd); adapter.UpdateCommand = new OracleCommandBuilder(adapter).GetUpdateCommand(); adapter.Update(dtInfos.GetChanges()); dtInfos.AcceptChanges(); } } catch (Exception ex) { dBResEdm.Module = "UpdateDtToDB方法"; dBResEdm.ExBody = ex; dBResEdm.ErrCode = 1; return(dBResEdm); } return(dBResEdm); }
private void btnEditType_Click(object sender, EventArgs e) { OracleCommandBuilder orb = new OracleCommandBuilder(adapter); adapter.Update(table); populate(); }
void MgfLoad() { try { conn.Open(); // string sqlquery = "SELECT MED_ID,MED_NAME,MED_STG,MED_MGF,MED_BATCH,MED_GROUP,MED_TYPE,COST_PRICE,SELL_PRICE,NOTES FROM MED_INFO"; string sqlquery = "SELECT * FROM MFG_INFO"; OracleCommand cmd = new OracleCommand(sqlquery, conn); OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; dt = new DataTable(); // dt.Columns["MED_NAME"].ColumnName = "Name"; oda.Fill(dt); BindingSource bsource = new BindingSource(); bsource.DataSource = dt; dataGridView1.DataSource = bsource; oda.Update(dt); dt.Columns[0].ColumnName = "ID"; dt.Columns[1].ColumnName = "Name"; dt.Columns[2].ColumnName = "Address"; dt.Columns[3].ColumnName = "Mobile"; dt.AcceptChanges(); conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
void MgfLoad() { try { conn.Open(); // string sqlquery = "SELECT MED_ID,MED_NAME,MED_STG,MED_MGF,MED_BATCH,MED_GROUP,MED_TYPE,COST_PRICE,SELL_PRICE,NOTES FROM MED_INFO"; string sqlquery = "SELECT * FROM MFG_INFO"; OracleCommand cmd = new OracleCommand(sqlquery, conn); OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; dt = new DataTable(); // dt.Columns["MED_NAME"].ColumnName = "Name"; oda.Fill(dt); BindingSource bsource = new BindingSource(); bsource.DataSource = dt; dataGridView1.DataSource = bsource; oda.Update(dt); dt.Columns[0].ColumnName = "ID"; dt.Columns[1].ColumnName = "Name"; dt.Columns[2].ColumnName = "Address"; dt.Columns[3].ColumnName = "Mobile"; dt.AcceptChanges(); conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
protected void cekguncelle_Click(object sender, EventArgs e) { conn = new OracleConnection(dbyol); string sorgu = "Update CK set FIRMA=:fir,VERTAR=:vertar,VADETAR=:vadetar,BANKA=:bank,CEKNO=:cekno,TUTAR=:tut,ACIK=:acik,CARINO=:carino,CEKTUR=:cektur,DURUMU=:durum Where CEKID=:id "; OracleDataAdapter da = new OracleDataAdapter("Select *from CK", conn); da.UpdateCommand = new OracleCommand(sorgu, conn); da.UpdateCommand.Parameters.Add("@fir", firma.Text); da.UpdateCommand.Parameters.Add("@vertar", cekvertar.Text); da.UpdateCommand.Parameters.Add("@vadetar", vadetar.Text); da.UpdateCommand.Parameters.Add("@bank", banka.Text); da.UpdateCommand.Parameters.Add("@cekno", cekno.Text); da.UpdateCommand.Parameters.Add("@tut", tutar.Text); da.UpdateCommand.Parameters.Add("@acik", aciklama.Text); da.UpdateCommand.Parameters.Add("@carino", carino.Text); da.UpdateCommand.Parameters.Add("@cektur", cekturu.Text); da.UpdateCommand.Parameters.Add("@durum", durum.Text); conn.Open(); cmd = new OracleCommand("Delete From CK Where CEKID = :id", conn); cmd.Parameters.AddWithValue(":id", ASPxGridView1.GetSelectedFieldValues("CEKID")[0].ToString()); cmd.ExecuteNonQuery(); DataSet ds = new DataSet(); OracleCommandBuilder orac = new OracleCommandBuilder(da); da.Fill(ds, "CK"); DataTable dt = ds.Tables["CK"]; DataRow crow = dt.NewRow(); crow["CEKID"] = ASPxGridView1.GetSelectedFieldValues("CEKID")[0].ToString(); crow["FIRMA"] = firma.Text; crow["VERTAR"] = cekvertar.Text; crow["VADETAR"] = vadetar.Text; crow["BANKA"] = banka.Text; crow["CEKNO"] = cekno.Text; crow["TUTAR"] = tutar.Text; crow["ACIK"] = aciklama.Text; crow["CARINO"] = carino.Text; crow["CEKTUR"] = cekturu.Text; crow["DURUMU"] = durum.Text; ds.Tables["CK"].Rows.Add(crow); da.Update(ds, "CK"); conn.Close(); Response.Redirect("CekBilgileri.aspx"); }
private void ConfirmNewManager_Click(object sender, EventArgs e) { connection CN2 = new connection(); CN2.thisConnection.Open(); OracleDataAdapter thisAdapter = new OracleDataAdapter("SELECT * FROM userinfo", CN2.thisConnection); OracleCommandBuilder thisBuilder = new OracleCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "userinfo"); DataRow thisRow = thisDataSet.Tables["userinfo"].NewRow(); try { thisRow["id"] = NewManagerUsername.Text; thisRow["password"] = NewManagerPass.Text; thisDataSet.Tables["userinfo"].Rows.Add(thisRow); thisAdapter.Update(thisDataSet, "userinfo"); MessageBox.Show("New Manager Added"); } catch (Exception ex) { MessageBox.Show(ex.Message); } CN2.thisConnection.Close(); AdminDashboard nxtform = new AdminDashboard(); nxtform.Show(); this.Hide(); }
private void button2_Click(object sender, EventArgs e) { if (conn.State == ConnectionState.Open) { conn.Close(); } try { conn.Open(); string sql2 = "SELECT id_transaksi FROM h_transaksi WHERE id_kamar = '" + comboBox1.Text + "' ORDER BY tgl_checkin DESC"; OracleCommand cmd2 = new OracleCommand(sql2, conn); string kode = cmd2.ExecuteScalar().ToString(); OracleCommand cmd = new OracleCommand("select * from d_transaksi where id_transaksi = '" + kode + "'", conn); OracleDataAdapter od = new OracleDataAdapter(cmd); builder = new OracleCommandBuilder(od); od.InsertCommand = builder.GetInsertCommand(); od.Update(datatablessource); updatedata(kode); conn.Close(); } catch (Exception ex) { conn.Close(); MessageBox.Show(ex.Message); } }
public static void Main() { string str = "data source=topcredu;User id=scott;Password=tiger"; OracleConnection Conn = new OracleConnection(str); OracleDataAdapter adapter = new OracleDataAdapter("select * from emp", Conn); OracleCommandBuilder builder = new OracleCommandBuilder(adapter); DataSet ds = new DataSet(); adapter.Fill(ds, "사원"); Console.WriteLine("Data Count : {0}", ds.Tables["사원"].Rows.Count); DataColumn[] keys = new DataColumn[1]; keys[0] = ds.Tables["사원"].Columns["empno"]; ds.Tables["사원"].PrimaryKey = keys; DataRow findrow = ds.Tables["사원"].Rows.Find(9999); if (findrow != null) { findrow.Delete(); adapter.Update(ds, "사원"); foreach (DataRow row in ds.Tables["사원"].Rows) { foreach (DataColumn col in ds.Tables["사원"].Columns) { Console.Write("{0, -10}", row[col.ColumnName]); } } Console.WriteLine(); } }
private void ConfirmNewBook_Click(object sender, EventArgs e) { connection CN2 = new connection(); CN2.thisConnection.Open(); OracleDataAdapter thisAdapter = new OracleDataAdapter("SELECT * FROM bookinfo", CN2.thisConnection); OracleCommandBuilder thisBuilder = new OracleCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "bookinfo"); DataRow thisRow = thisDataSet.Tables["bookinfo"].NewRow(); try { thisRow["isbn"] = IsbnOfTheBook.Text; thisRow["book_name"] = NameOfTheBook.Text; thisRow["writer_name"] = NameOfTheWriter.Text; thisRow["book_category"] = CategoryOfTheBook.Text; thisRow["entry_date"] = DateOfEntry.Text; thisRow["year_published"] = YearOfPublication.Text; thisRow["book_qty"] = NumberOfBook.Text; thisDataSet.Tables["bookinfo"].Rows.Add(thisRow); thisAdapter.Update(thisDataSet, "bookinfo"); MessageBox.Show("A New Book Added"); } catch (Exception ex) { MessageBox.Show(ex.Message); } CN2.thisConnection.Close(); ManagerDashboard nxtForm = new ManagerDashboard(); nxtForm.Show(); this.Hide(); }
public static int Main(String[] args) { const string CONNECTION_STRING = "Data Source=Oracle8i;Integrated Security=yes"; const string SELECT_ALL = "SELECT * FROM Scott.Emp"; //Create DataAdapter. OracleDataAdapter rAdapter = new OracleDataAdapter(SELECT_ALL, CONNECTION_STRING); //Create and fill DataSet (Select only first 5 rows.). DataSet rDataSet = new DataSet(); rAdapter.Fill(rDataSet, 0, 5, "Table"); //Modify DataSet. DataTable rTable = rDataSet.Tables["Table"]; rTable.Rows[0][1] = "DYZY"; //Add handlers. rAdapter.RowUpdating += new OracleRowUpdatingEventHandler(OnRowUpdating); rAdapter.RowUpdated += new OracleRowUpdatedEventHandler(OnRowUpdated); //Update--this operation fires two events (RowUpdating and RowUpdated) for each changed row. rAdapter.Update(rDataSet, "Table"); //Remove handlers. rAdapter.RowUpdating -= new OracleRowUpdatingEventHandler(OnRowUpdating); rAdapter.RowUpdated -= new OracleRowUpdatedEventHandler(OnRowUpdated); return(0); }
public void InsertTrial() { try { OracleCommand oracleCommand = conn.CreateCommand(); oracleCommand.CommandText = "insert into student(ID, name, dept_name, tot_cred) values (:ID,:name,:dept_name,:tot_cred)"; OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(); // oracleDataAdapter.InsertCommand.CommandType = CommandType.Text; oracleDataAdapter.InsertCommand = oracleCommand; OracleParameter[] oracleParameters = { new OracleParameter(":ID", OracleDbType.Varchar2, 5, "77777"), new OracleParameter(":name", OracleDbType.Varchar2, 20, "mingkai"), new OracleParameter(":dept_name", OracleDbType.Varchar2, 20, "Comp.Sci."), new OracleParameter(":tot_cred", OracleDbType.Varchar2, 4, "777"), }; oracleDataAdapter.InsertCommand.Parameters.AddRange(oracleParameters); oracleDataAdapter.InsertCommand.Connection = conn; int k = oracleDataAdapter.InsertCommand.ExecuteNonQuery(); DataTable dataTable = new DataTable(); oracleDataAdapter.Update(dataTable); dataTable.AcceptChanges(); MessageBox.Show(k.ToString()); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } }
private void button3_Click(object sender, EventArgs e) { List <string> list_Of_Prisons_name = new List <string>(); string constr = "Data Source=orcl;User Id=hr;Password=hr;"; string prison = "select prison_name from prisons"; OracleDataAdapter adapter = new OracleDataAdapter(prison, constr); DataSet dataSet = new DataSet(); adapter.Fill(dataSet); for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++) { list_Of_Prisons_name.Add(dataSet.Tables[0].Rows[i]["prison_name"].ToString()); } string update_NO_Policemen = @"update PRISONS set N0_Of_Policemen =(select count(*) from POLICEMEN where prison_name=:name) where prison_name=:name"; for (int i = 0; i < list_Of_Prisons_name.Count; i++) { adapter = new OracleDataAdapter(update_NO_Policemen, constr); adapter.SelectCommand.Parameters.Add("name", list_Of_Prisons_name[i]); dataSet = new DataSet(); adapter.Fill(dataSet); } builderr = new OracleCommandBuilder(adapter); adapter.Update(dataSett.Tables[0]); }
/// <summary> /// 先存入DataTable中,再插入,实际不快,为什么 /// </summary> /// <param name="comm"></param> private void BatchInsert(OracleCommand comm) { Msg += "初始化前:" + DateTime.Now.ToString("HH:mm:ss fff") + "\n"; DataTable dt = new DataTable(); int count = GetTimes(); dt.TableName = "AAATest"; dt.Columns.Add("EID", typeof(String)); dt.Columns.Add("FROMID", typeof(String)); dt.Columns.Add("TOID", typeof(String)); dt.Columns.Add("LL", typeof(Int32)); dt.Columns.Add("YW", typeof(Int32)); dt.Columns.Add("LXSJ", typeof(Int32)); Msg += "初始化中:" + DateTime.Now.ToString("HH:mm:ss fff") + "\n"; OracleDataAdapter da = new OracleDataAdapter(comm); OracleCommandBuilder ocb = new OracleCommandBuilder(da); da.SelectCommand.CommandText = "SELECT * FROM AAA"; da.InsertCommand = ocb.GetInsertCommand(); Msg += "初始化后:" + DateTime.Now.ToString("HH:mm:ss fff") + "\n"; for (int i = 0; i < count; i++) { dt.Rows.Add(new Object[] { DateTime.Now.ToString("HH:mm:ss fff"), "1", "1", 1, 1, 1 }); } Msg += "提交前:" + DateTime.Now.ToString("HH:mm:ss fff") + "\n"; da.Update(dt); Msg += "提交后:" + DateTime.Now.ToString("HH:mm:ss fff") + "\n"; }
private void GetAdjustmentsForTeacher() { try { using OracleConnection connection = new OracleConnection(OracleDataBaseConnection.data); OracleParameter teacherId = new OracleParameter { ParameterName = "in_teacher_id", Direction = ParameterDirection.Input, OracleDbType = OracleDbType.Varchar2, Value = _teacher.UserId }; connection.Open(); using (OracleCommand command = new OracleCommand("select s.student_name, s.course || '-'|| s.num_group course_group, a.subject, a.status, TO_CHAR(a.filing_date, 'DD.MM.YYYY') filing_date from adjustments a " + "inner join student_info s on a.user_id = s.user_id " + "where a.teacher_id = :in_teacher_id and a.status in ('accept/decline', 'accept/accept')", connection)) { command.Parameters.Add(teacherId); OracleDataAdapter oda = new OracleDataAdapter(command); DataTable dt = new DataTable("adjustments"); oda.Fill(dt); dg_Adjustments.ItemsSource = dt.DefaultView; oda.Update(dt); } connection.Close(); } catch (Exception e) { MessageBox.Show(e.Message); throw; } }
/// <summary> /// 批量插入数据 /// </summary> /// <param name="dt">需要插入的数据源表</param> /// <returns></returns> public static void InsertAllList(DataSet ds, string tableName) { using (OracleConnection connection = new OracleConnection(connectionString)) { string SQLString = string.Format("select * from {0} where rownum=0", tableName); using (OracleCommand cmd = new OracleCommand(SQLString, connection)) { OracleDataAdapter myDataAdapter = new OracleDataAdapter(); myDataAdapter.SelectCommand = new OracleCommand(SQLString, connection); myDataAdapter.UpdateBatchSize = 0; OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter); DataTable dt = ds.Tables[0].Copy(); DataTable dtTemp = dt.Clone(); int times = 0; for (int count = 0; count < dt.Rows.Count; times++) { for (int i = 0; i < 500 && 500 * times + i < dt.Rows.Count; i++, count++) { dtTemp.Rows.Add(dt.Rows[count].ItemArray); } myDataAdapter.Update(dtTemp); dtTemp.Rows.Clear(); } dt.Dispose(); dtTemp.Dispose(); myDataAdapter.Dispose(); } } }
private void frmShowLeagueTable_Load(object sender, EventArgs e) { //Connect to db OracleConnection conn = new OracleConnection(DBConnect.oradb); //Define SQL Query String strSQL = "select name, won, lost, drawn, scoresfor, scoresagainst, points from teams order by points desc"; //Define Oracle command OracleCommand cmd = new OracleCommand(strSQL, conn); conn.Open(); try { OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; DataTable dbdataset = new DataTable(); oda.Fill(dbdataset); BindingSource bSource = new BindingSource(); bSource.DataSource = dbdataset; leagueTable.DataSource = bSource; oda.Update(dbdataset); setRowNumber(leagueTable); leagueTable.AllowUserToAddRows = false; } catch (Exception ex) { MessageBox.Show(ex.Message); } conn.Close(); }
private void GetAdjustments() { try { using (OracleConnection connection = new OracleConnection(OracleDataBaseConnection.data)) { OracleParameter faculty = new OracleParameter { ParameterName = "in_faculty", Direction = ParameterDirection.Input, OracleDbType = OracleDbType.Varchar2, Value = _deanery.Faculty }; connection.Open(); using (OracleCommand command = new OracleCommand("select s.student_name,s.faculty,a.subject,a.status, TO_CHAR(a.filing_date, 'DD.MM.YYYY') filing_date " + "from adjustments a inner join student_info s on a.user_id = s.user_id and s.faculty = :in_faculty", connection)) { //TODO FIX command.Parameters.Add(faculty); command.ExecuteNonQuery(); OracleDataAdapter oda = new OracleDataAdapter(command); DataTable dt = new DataTable("adjustments"); oda.Fill(dt); dg_Adjustments.ItemsSource = dt.DefaultView; oda.Update(dt); } connection.Close(); } } catch (Exception e) { MessageBox.Show(e.Message); } }
private void MetroWindow_Loaded(object sender, RoutedEventArgs e) { OracleConnection Oracon = new OracleConnection(oradb); try { //abrir conexao com o banco de dados Oracon.Open(); OracleCommand createCommand = new OracleCommand(SQL_SELECT_ALL, Oracon); createCommand.ExecuteNonQuery(); OracleDataAdapter adapter = new OracleDataAdapter(createCommand); DataTable dt = new DataTable("viatura"); adapter.Fill(dt); dgvConteudo.ItemsSource = dt.DefaultView; dgvConteudo.Columns[0].Header = "ID"; dgvConteudo.Columns[1].Header = "Fabricante Modelo"; dgvConteudo.Columns[2].Header = "Placa"; dgvConteudo.Columns[3].Header = "Chassi"; dgvConteudo.Columns[4].Header = "Status"; //Ocultar colunas dgvConteudo.Columns[4].Visibility = Visibility.Hidden; adapter.Update(dt); //Fechar conexao com o banco de dados Oracon.Close(); } catch (Exception ex) { System.Windows.MessageBox.Show(ex.Message); } }
void MedLoad() { try { conn.Open(); string sqlquery = "SELECT MED_ID,MED_NAME,MED_STG,MED_MGF,MED_BATCH,MED_GROUP,MED_TYPE,COST_PRICE,SELL_PRICE,NOTES FROM MED_INFO"; //string sqlquery = "SELECT * FROM MED_INFO"; OracleCommand cmd = new OracleCommand(sqlquery, conn); OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; dt = new DataTable(); // dt.Columns["MED_NAME"].ColumnName = "Name"; oda.Fill(dt); BindingSource bsource = new BindingSource(); bsource.DataSource = dt; dataGridView1.DataSource = bsource; oda.Update(dt); dt.Columns[0].ColumnName = "ID"; dt.Columns[1].ColumnName = "Name"; dt.Columns[2].ColumnName = "Strenght"; dt.Columns[3].ColumnName = "Manufacturer"; dt.Columns[4].ColumnName = "Batch No"; dt.Columns[5].ColumnName = "Group"; dt.Columns[6].ColumnName = "Type"; dt.Columns[7].ColumnName = "Cost Price"; dt.Columns[8].ColumnName = "Sell Price"; dt.Columns[9].ColumnName = "Note"; dt.AcceptChanges(); conn.Close(); } catch(Exception ex) { MessageBox.Show(ex.Message); } }
public static bool SelectPlayer(Player player) { OracleConnection cn = null; OracleCommand cmd = null; OracleDataReader rdr = null; OracleDataAdapter adp = null; OracleCommandBuilder bldr = null; DataTable tbl = null; DataRow row = null; string sql; Pick currentPick = GlobalCollections.Instance.OnTheClock; int totalPicks = 0; bool saved = false; try { cn = createConnectionSDR(); sql = "select count(*) from draftorder"; cmd = new OracleCommand(sql, cn); rdr = cmd.ExecuteReader(); if (rdr.HasRows) { rdr.Read(); totalPicks = Convert.ToInt16(rdr[0]); } rdr.Close(); rdr.Dispose(); if (currentPick.OverallPick > 0 && currentPick.OverallPick <= totalPicks) { sql = "select * from draftplayers where playerid = " + player.PlayerId; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count > 0) { row = tbl.Rows[0]; if (row["pick"] == DBNull.Value || row["pick"].ToString() == "0") { row["pick"] = currentPick.OverallPick; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); saved = true; } } //disable all the team finish/ranks pages for NFL if (ConfigurationManager.AppSettings["DraftType"].ToUpper() == "NFL") { sql = "update drafttidbits set enabled = 0 where referencetype = 2 and referenceid = " + currentPick.Team.ID + " and tidbitorder >= 10 and tidbitorder < 40"; cmd = new OracleCommand(sql, cn); cmd.ExecuteNonQuery(); cmd.Dispose(); GlobalCollections.Instance.LoadTeams(); } } } finally { if (cmd != null) cmd.Dispose(); if (adp != null) adp.Dispose(); if (bldr != null) bldr.Dispose(); if (tbl != null) tbl.Dispose(); if (cn != null) cn.Close(); cn.Dispose(); } return saved; }
public DataTable ExecuteDataTable(string sql) { try { DataTable dt = new DataTable(); OracleDataAdapter da = new OracleDataAdapter(sql, conn); da.Fill(dt); da.Update(dt); return dt; } catch (Exception ex) { MessageBox.Show("Query gagal dijalankan, pastikan definisi data di dalam Oracle 11g sudah sesuai dengan upstream.\nPesan kesalahan : \n" + ex.ToString(), "Galat", MessageBoxButtons.OK, MessageBoxIcon.Error); } return null; }
private void MetroWindow_Loaded(object sender, RoutedEventArgs e) { OracleConnection Oracon = new OracleConnection(oradb); try { //abrir conexao com o banco de dados Oracon.Open(); OracleCommand createCommand = new OracleCommand(SQL_SELECT_ALL, Oracon); createCommand.ExecuteNonQuery(); OracleDataAdapter adapter = new OracleDataAdapter(createCommand); DataTable dt = new DataTable("agente"); adapter.Fill(dt); dgvConteudo.ItemsSource = dt.DefaultView; dgvConteudo.Columns[0].Header = "ID"; dgvConteudo.Columns[1].Header = "Nome Completo"; dgvConteudo.Columns[2].Header = "Sexo"; dgvConteudo.Columns[3].Header = "Data de Nascimento"; dgvConteudo.Columns[4].Header = "RG"; dgvConteudo.Columns[5].Header = "CPF"; dgvConteudo.Columns[6].Header = "Tipo Sanguíneo"; dgvConteudo.Columns[7].Header = "Raça"; dgvConteudo.Columns[8].Header = "Estado Civil"; dgvConteudo.Columns[9].Header = "CEP"; dgvConteudo.Columns[10].Header = "Logradouro"; dgvConteudo.Columns[11].Header = "Numero"; dgvConteudo.Columns[12].Header = "Complemento"; dgvConteudo.Columns[13].Header = "Bairro"; dgvConteudo.Columns[14].Header = "Cidade"; dgvConteudo.Columns[15].Header = "UF"; dgvConteudo.Columns[16].Header = "Foto Agente"; dgvConteudo.Columns[17].Header = "Impressão Digital"; dgvConteudo.Columns[18].Header = "Cargo"; dgvConteudo.Columns[19].Header = "STATUS"; //Escondendo colunas dgvConteudo.Columns[16].Visibility = Visibility.Hidden; dgvConteudo.Columns[17].Visibility = Visibility.Hidden; dgvConteudo.Columns[19].Visibility = Visibility.Hidden; adapter.Update(dt); Oracon.Close(); } catch (Exception ex) { System.Windows.MessageBox.Show(ex.Message); } //Variaveis de Foto e Digital nao podem ser nulas destinationPathFoto = "pack://application:,,,/IMAGES/User_Profile.png"; destinationPathFinger = "pack://application:,,,/IMAGES/Finger_Print.png"; // Variaveis para o padrão namefoto = ""; namefinger = ""; fingerpath = ""; imagepath = ""; checkFinger = false; checkFoto = false; Armamento = false; fotoAgentesource = ""; digitalsource = ""; alterFinger = false; alterPhoto = false; }
public int Update(OracleDataAdapter oracleAdapter, DataRow[] dataRow) { int tickCount = Environment.TickCount; int retVal; try { SetupOracleDataAdapter(oracleAdapter); retVal = oracleAdapter.Update(dataRow); } catch( Exception ex ) { _errorLog.WriteExceptionLog(ex, "OracleDbAgent:"); throw ex; } finally { _counter.CounterName = "Update"; _counter.RawValue = Environment.TickCount - tickCount; } return retVal; }
/// <summary> /// Executes the respective command for each inserted, updated, or deleted row in the DataSet. /// </summary> /// <remarks> /// e.g.: /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); /// </remarks> /// <param name="insertCommand">A valid transact-SQL statement or stored procedure to insert new records into the data source</param> /// <param name="deleteCommand">A valid transact-SQL statement or stored procedure to delete records from the data source</param> /// <param name="updateCommand">A valid transact-SQL statement or stored procedure used to update records in the data source</param> /// <param name="dataSet">The DataSet used to update the data source</param> /// <param name="tableName">The DataTable used to update the data source.</param> public static void UpdateDataset(OracleCommand insertCommand, OracleCommand deleteCommand, OracleCommand updateCommand, DataSet dataSet, string tableName) { if (insertCommand == null) throw new ArgumentNullException("insertCommand"); if (deleteCommand == null) throw new ArgumentNullException("deleteCommand"); if (updateCommand == null) throw new ArgumentNullException("updateCommand"); if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName"); // Create a OracleDataAdapter, and dispose of it after we are done using (OracleDataAdapter dataAdapter = new OracleDataAdapter()) { // Set the data adapter commands dataAdapter.UpdateCommand = updateCommand; dataAdapter.InsertCommand = insertCommand; dataAdapter.DeleteCommand = deleteCommand; // Update the dataset changes in the data source dataAdapter.Update(dataSet, tableName); // Commit all the changes made to the DataSet dataSet.AcceptChanges(); } }
private static int UpdateByOracleDataAdapter() { try { OracleDataAdapter adapter = new OracleDataAdapter(BANKING_STATUS_SKELETON_SELECT, connString); adapter.InsertCommand = BuildOracleInsertCommand(); adapter.InsertCommand.Connection = new OracleConnection(connString); DataTable dt = new DataTable(); adapter.Fill(dt); BuildUpdateDataTable(dt); return adapter.Update(dt); } catch { throw; } }
public bool UpdateTable(DataTable table, string tableName) { try { TableHelper.SetDefaultColumnValues(table); var con = CONNECTION.OpenCon(); var adapter = new OracleDataAdapter(string.Format(@"SELECT * FROM {0}", tableName), con); var cmd = new OracleCommandBuilder(adapter); adapter.Update(table); cmd.Dispose(); adapter.Dispose(); CONNECTION.CloseCon(con); return true; } catch (DBConcurrencyException cex) { SLLog.WriteError(new LogData { Source = ToString(), FunctionName = "UpdateTable DBConcurrencyError!", Ex = cex, }); return false; } catch (Exception ex) { SLLog.WriteError(new LogData { Source = ToString(), FunctionName = "UpdateTable Error!", Ex = ex, }); return false; } }
public static bool AddPlayerToDraftPlayers(Int64 playerId, string firstName, string lastName, string position, Int32 schoolId) { bool saved = false; OracleConnection cn = null; OracleCommand cmd = null; OracleDataAdapter adp = null; OracleCommandBuilder bldr = null; DataTable tbl = null; string sql = ""; try { cn = createConnectionSDR(); sql = "select * from draftplayers where playerid = " + playerId; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); DataRow row; adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["playerid"] = playerId; } else { row = tbl.Rows[0]; } row["schoolid"] = schoolId; row["firstname"] = firstName; row["lastname"] = lastName; row["position"] = position; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); saved = true; } finally { if (cmd != null) cmd.Dispose(); if (cn != null) cn.Close(); cn.Dispose(); } return saved; }
public int Update(OracleDataAdapter oracleAdapter, DataSet ds, string srcTable) { int tickCount = Environment.TickCount; int retVal; try { SetupOracleDataAdapter(oracleAdapter); if ( srcTable != null ) { retVal = oracleAdapter.Update(ds, srcTable); } else { retVal = oracleAdapter.Update(ds); } } catch( Exception ex ) { _errorLog.WriteExceptionLog(ex, "OracleDbAgent:"); throw ex; } finally { _counter.CounterName = "Update"; _counter.RawValue = Environment.TickCount - tickCount; } return retVal; }
public static void ImportTeams(string file) { int teamsImported = 0; int importErrors = 0; int teamsToImport = 0; SetStatusBarMsg("Importing teams info...", "Yellow"); BackgroundWorker worker = new BackgroundWorker(); worker.WorkerReportsProgress = true; worker.DoWork += delegate(object s, DoWorkEventArgs args) { OracleConnection cn = null; OracleCommand cmd = null; OracleDataReader rdr = null; OracleDataAdapter adp = null; OracleCommandBuilder bldr = null; MySqlConnection cnMySql = null; MySqlCommand cmdMySql = null; MySqlDataReader rdrMySql = null; MySqlDataAdapter adpMySql = null; MySqlCommandBuilder bldrMySql = null; //DataTable tblPlayer = null; DataTable tbl = null; DataRow row; string sql; //long teamId; int i; int totalPicks = 0; DataSet dsTeams = new DataSet(); dsTeams.ReadXml(file); try { cn = createConnectionSDR(); teamsToImport = dsTeams.Tables["team"].Rows.Count; foreach (DataRow xmlRow in dsTeams.Tables["team"].Rows) { totalPicks = 0; if (xmlRow["teamid"].ToString().Trim() != "") { if (ConfigurationManager.AppSettings["DraftType"].ToUpper() == "NFL") { #region Picks try { sql = "select count(*) from draftorder where teamid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count > 0) { totalPicks = int.Parse(tbl.Rows[0][0].ToString()); } } finally { cmd.Dispose(); adp.Dispose(); tbl.Dispose(); } #endregion #region 6 Matrix Notes //import the 4 matrix notes for (i = 1; i <= 6; i++) { if (xmlRow["note" + i.ToString()].ToString().Trim() != "") { sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = " + i.ToString() + " and referenceid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["referencetype"] = 2; row["referenceid"] = xmlRow["teamid"]; row["tidbitorder"] = i; row["enabled"] = 1; } else { row = tbl.Rows[0]; } row["text"] = xmlRow["note" + i.ToString()].ToString(); adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); } } #endregion #region MySql team ranks/results cnMySql = createConnectionMySql(); sql = "select * from teams where id = " + xmlRow["teamid"]; cmdMySql = new MySqlCommand(sql, cnMySql); adpMySql = new MySqlDataAdapter(cmdMySql); bldrMySql = new MySqlCommandBuilder(adpMySql); tbl = new DataTable(); adpMySql.Fill(tbl); if (tbl.Rows.Count > 0) { row = tbl.Rows[0]; row["totalpicks"] = totalPicks; row["overallrecord"] = xmlRow["record"]; row["divisionresult"] = xmlRow["divresult"]; row["playoffs"] = xmlRow["playoffs"]; row["offrankppg"] = xmlRow["offrankppg"]; row["offrankypg"] = xmlRow["offrankypg"]; row["offrankturns"] = xmlRow["offrankturns"]; row["offrankrush"] = xmlRow["offrankrushyds"]; row["offrankpass"] = xmlRow["offrankpassyds"]; row["defrankppg"] = xmlRow["defrankppg"]; row["defrankypg"] = xmlRow["defrankypg"]; row["defranktakeaways"] = xmlRow["defranktakeaways"]; row["defrankrush"] = xmlRow["defrankrushing"]; row["defrankpass"] = xmlRow["defrankpassing"]; row["teamneeds"] = xmlRow["melsneeds"]; adpMySql.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmdMySql.Dispose(); adpMySql.Dispose(); bldrMySql.Dispose(); tbl.Dispose(); } #endregion } else if (ConfigurationManager.AppSettings["DraftType"].ToUpper() == "NBA") { #region 2 Matrix Notes //import the 4 matrix notes for (i = 1; i <= 2; i++) { if (xmlRow["note" + i.ToString()].ToString().Trim() != "") { sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = " + i.ToString() + " and referenceid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["referencetype"] = 2; row["referenceid"] = xmlRow["teamid"]; row["tidbitorder"] = i; } else { row = tbl.Rows[0]; } row["text"] = xmlRow["note" + i.ToString()].ToString(); row["enabled"] = 1; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); } } #endregion #region Finish sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = 21 and referenceid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["referencetype"] = 2; row["referenceid"] = xmlRow["teamid"]; row["tidbitorder"] = 21; } else { row = tbl.Rows[0]; } row["text"] = xmlRow["divresult"].ToString(); row["enabled"] = 1; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); #endregion #region Record sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = 20 and referenceid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["referencetype"] = 2; row["referenceid"] = xmlRow["teamid"]; row["tidbitorder"] = 20; } else { row = tbl.Rows[0]; } row["text"] = xmlRow["record"].ToString(); row["enabled"] = 1; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); #endregion #region Lineup sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = 30 and referenceid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["referencetype"] = 2; row["referenceid"] = xmlRow["teamid"]; row["tidbitorder"] = 30; } else { row = tbl.Rows[0]; } row["text"] = xmlRow["lineup"].ToString(); row["enabled"] = 1; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); #endregion } teamsImported++; } worker.ReportProgress(teamsImported / teamsToImport); } //foreach team } //try catch (Exception ex) { importErrors++; } finally { if (cmd != null) cmd.Dispose(); if (adp != null) adp.Dispose(); if (bldr != null) bldr.Dispose(); if (rdr != null) rdr.Dispose(); if (cn != null) cn.Close(); cn.Dispose(); //log.Close(); } }; //dowork worker.RunWorkerCompleted += delegate(object s, RunWorkerCompletedEventArgs args) { SetStatusBarMsg(teamsImported.ToString() + " of " + teamsToImport.ToString() + " teams successfully imported at " + DateTime.Now.ToLongTimeString(), "Green"); GlobalCollections.Instance.LoadTeams(); }; worker.ProgressChanged += delegate(object s, ProgressChangedEventArgs args) { SetStatusBarMsg(teamsImported.ToString() + " of " + teamsToImport.ToString() + " teams imported.", "Yellow"); }; worker.RunWorkerAsync(file); }
public static bool UpdateTidbitSDR(int tidbitTypeId, Int32 refId, int tidbitOrder, string tidbitText = "", string timecode = "", bool enabled = false, int newTidbitOrder = 0) { OracleConnection cn = null; OracleCommand cmd = null; OracleDataAdapter adp = null; OracleCommandBuilder bldr = null; DataTable tbl = null; DataRow row = null; bool updated = false; try { cn = createConnectionSDR(); string sql = "select * from drafttidbits where referencetype = " + tidbitTypeId + " and referenceid = " + refId + " and tidbitorder = " + tidbitOrder; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); } else { row = tbl.Rows[0]; } row["referencetype"] = tidbitTypeId; row["referenceid"] = refId; if (newTidbitOrder > 0) { row["tidbitorder"] = newTidbitOrder; } else { row["tidbitorder"] = tidbitOrder; } row["text"] = tidbitText; row["enabled"] = enabled; //row["timecode"] = timecode; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); updated = true; } catch (Exception ex) { } finally { if (cmd != null) cmd.Dispose(); if (adp != null) adp.Dispose(); if (bldr != null) bldr.Dispose(); if (tbl != null) tbl.Dispose(); } return updated; }
public static void ImportPlayers(string file) { int playersImported = 0; int importErrors = 0; int playersToImport = 0; int playersNotFound = 0; SetStatusBarMsg("Importing players...", "Yellow"); BackgroundWorker worker = new BackgroundWorker(); worker.WorkerReportsProgress = true; worker.DoWork += delegate(object s, DoWorkEventArgs args) { OracleConnection cn = null; OracleCommand cmd = null; OracleDataReader rdr = null; OracleDataAdapter adp = null; OracleCommandBuilder bldr = null; DataTable tblPlayer = null; DataTable tbl = null; DataRow row; //use the log when importing based on player names to find missing/duplicate players //FileInfo logFile = new FileInfo(ConfigurationManager.AppSettings["ImportLogFile"]); //if (logFile.Exists) //{ // logFile.Delete(); //} //TextWriter log = new StreamWriter(ConfigurationManager.AppSettings["ImportLogFile"]); string sql; long playerId; DataSet dsPlayers = new DataSet(); try { dsPlayers.ReadXml(file); cn = createConnectionSDR(); playersToImport = dsPlayers.Tables["player"].Rows.Count; foreach (DataRow xmlRow in dsPlayers.Tables["player"].Rows) { //sql = "select player_id from espnews.news_players where upper(last_name) = :lastname and upper(first_name) = :firstname"; //cmd = new OracleCommand(sql, cn); //cmd.Parameters.Add(":lastname", xmlRow["lastname"].ToString().ToUpper()); //cmd.Parameters.Add(":firstname", xmlRow["firstname"].ToString().ToUpper()); //rdr = cmd.ExecuteReader(); //tblPlayer = new DataTable(); //tblPlayer.Load(rdr); //rdr.Close(); //rdr.Dispose(); //if (tblPlayer.Rows.Count > 0) //{ // if (tblPlayer.Rows.Count > 1) // { // //write to the report file so we can see what player's ids are questionable // log.WriteLine(DateTime.Now + " --- Found multiple records for " + xmlRow["firstname"].ToString() + " " + xmlRow["lastname"].ToString() + ". Used PLAYERID " + tblPlayer.Rows[0]["player_id"].ToString()); // } // playerId = Convert.ToInt32(tblPlayer.Rows[0]["player_id"].ToString()); //} //else //{ // playerId = 0; //} //tblPlayer.Dispose(); //cmd.Dispose(); if (xmlRow["playerid"].ToString().Trim() != "") { sql = "select * from espnews.draftplayers where playerid = " + xmlRow["playerid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["playerid"] = xmlRow["playerid"]; } else { row = tbl.Rows[0]; if (row["lastname"].ToString() != xmlRow["lastname"].ToString()) { string message = "Duplicate player id for " + xmlRow["firstname"].ToString() + " " + xmlRow["lastname"].ToString(); MessageBoxResult result = System.Windows.MessageBox.Show(message, "Duplicate Player ID", MessageBoxButton.OK, MessageBoxImage.Exclamation); //if (result == MessageBoxResult.Yes) //{ // Application.Current.Shutdown(); //} } } row["firstname"] = xmlRow["firstname"].ToString(); row["lastname"] = xmlRow["lastname"].ToString(); Int16 age; if (dsPlayers.Tables["player"].Columns["age"] != null) { if (Int16.TryParse(xmlRow["age"].ToString(), out age)) { row["age"] = age; } } row["position"] = xmlRow["position"].ToString(); if (dsPlayers.Tables["player"].Columns["positionfull"] != null) { row["positionfull"] = xmlRow["positionfull"].ToString(); } if (xmlRow["school"].ToString() != "") { if (Char.IsNumber(xmlRow["school"].ToString().ToCharArray()[0])) { row["schoolid"] = xmlRow["school"]; } } if (xmlRow["kiperrank"].ToString() != "") { row["kiperrank"] = Convert.ToInt16(xmlRow["kiperrank"].ToString()); } else { row["kiperrank"] = DBNull.Value; } if (xmlRow["mcshayrank"].ToString() != "") { row["mcshayrank"] = Convert.ToInt16(xmlRow["mcshayrank"].ToString()); } else { row["mcshayrank"] = DBNull.Value; } row["height"] = xmlRow["height"].ToString(); if (xmlRow["weight"].ToString().Trim() != "") { row["weight"] = xmlRow["weight"].ToString() + " LBS"; } else { row["weight"] = ""; } row["class"] = xmlRow["class"].ToString(); adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); DataTable tblTids = null; try { int noteCount; if (ConfigurationManager.AppSettings["DraftType"].ToString().ToUpper() == "NBA") { noteCount = 2; } else { noteCount = 4; } for (int i = 1; i <= noteCount; i++) { if (xmlRow.Table.Columns["matrixnote" + i.ToString()] != null) { if (xmlRow["matrixnote" + i.ToString()].ToString().Trim() != "") { sql = "select * from espnews.drafttidbits where referenceid = " + xmlRow["playerid"] + " and referencetype = 1 and tidbitorder = " + i; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tblTids = new DataTable(); DataRow rowTids = null; adp.Fill(tblTids); if (tblTids.Rows.Count == 0) { rowTids = tblTids.Rows.Add(); rowTids["referenceid"] = xmlRow["playerid"]; rowTids["referencetype"] = 1; rowTids["tidbitorder"] = i; rowTids["enabled"] = 1; } else { rowTids = tblTids.Rows[0]; } rowTids["text"] = xmlRow["matrixnote" + i.ToString()].ToString(); adp.Update(tblTids.GetChanges()); tblTids.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tblTids.Dispose(); } } } } finally { if (tblTids != null) tblTids.Dispose(); } playersImported++; worker.ReportProgress(playersImported / playersToImport); } //playerid > 0 else { playersNotFound++; // //write to report file with this player, not found // log.WriteLine(DateTime.Now + " --- Not found: " + xmlRow["firstname"].ToString() + " " + xmlRow["lastname"].ToString()); } } //foreach player } //try catch (Exception ex) { importErrors++; } finally { if (cmd != null) cmd.Dispose(); if (adp != null) adp.Dispose(); if (bldr != null) bldr.Dispose(); if (rdr != null) rdr.Dispose(); if (cn != null) cn.Close(); cn.Dispose(); //log.Close(); } }; //dowork worker.RunWorkerCompleted += delegate(object s, RunWorkerCompletedEventArgs args) { SetStatusBarMsg(playersImported.ToString() + " of " + playersToImport.ToString() + " players successfully imported at " + DateTime.Now.ToLongTimeString() + ". Players not found by ID: " + playersNotFound.ToString() + ". Errors importing " + importErrors.ToString() + ".", "Green"); GlobalCollections.Instance.LoadPlayers(); }; worker.ProgressChanged += delegate(object s, ProgressChangedEventArgs args) { SetStatusBarMsg(playersImported.ToString() + " of " + playersToImport.ToString() + " players imported.", "Yellow"); }; worker.RunWorkerAsync(file); }
public static bool SavePlayer(Player player) { OracleConnection cn = null; OracleCommand cmd = null; OracleDataAdapter adp = null; OracleCommandBuilder bldr = null; DataTable tbl = null; DataRow row = null; bool saved = false; try { cn = createConnectionSDR(); String sql = "select * from draftplayers where playerid = " + player.PlayerId; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["playerid"] = player.PlayerId; } else { row = tbl.Rows[0]; } row["firstname"] = player.FirstName; row["lastname"] = player.LastName; int oldRank = 0; if (row["kiperrank"] != DBNull.Value) { oldRank = Convert.ToInt16(row["kiperrank"]); } if (oldRank != player.KiperRank) { updateKiperRanks(player, oldRank); } if (player.KiperRank == 0) { row["kiperrank"] = DBNull.Value; } else { row["kiperrank"] = player.KiperRank; } oldRank = 0; if (row["mcshayrank"] != DBNull.Value) { oldRank = Convert.ToInt16(row["mcshayrank"]); } if (oldRank != player.McShayRank) { updateMcShayRanks(player, oldRank); } if (player.McShayRank == 0) { row["mcshayrank"] = DBNull.Value; } else { row["mcshayrank"] = player.McShayRank; } if (player.School != null) { row["schoolid"] = player.School.ID; } else { row["schoolid"] = DBNull.Value; } row["position"] = player.Position; row["positionfull"] = player.PositionFull; row["height"] = player.Height; row["weight"] = player.Weight; row["class"] = player.Class; //update the trade tidbit (NBA) UpdateTidbitSDR(1, player.PlayerId, 999, player.TradeTidbit, null, true); if (player.Tidbits != null) { foreach (Tidbit tidbit in player.Tidbits) { UpdateTidbitSDR(tidbit.ReferenceType, tidbit.ReferenceID, tidbit.TidbitOrder, tidbit.TidbitText, null, tidbit.Enabled); } } adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); saved = true; } finally { if (cmd != null) cmd.Dispose(); if (adp != null) adp.Dispose(); if (bldr != null) bldr.Dispose(); if (tbl != null) tbl.Dispose(); if (cn != null) cn.Close(); cn.Dispose(); } return saved; }