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;
}