protected void con_btn_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(TextBox_ad_pay.Text))
{
LabelMessage.Text = "Advance Payment is empty";
TextBox_ad_pay.Focus();
}
else
{
int advanced = Convert.ToInt32(TextBox_ad_pay.Text);
int due = Convert.ToInt32( Session["total_price"]) - advanced;
string ConnectionString = @"DATA SOURCE=127.0.0.1:1521/XE;PERSIST SECURITY INFO=True;USER ID=TAHMID; Password=anik";
string cmdQuery = "insert into CLIENT(C_ID, C_NAME, C_PHONE, C_EMAIL, C_ADDRESS, C_GUEST, C_SERVICE_ID, C_F_PACK_ID, C_D_PACK_ID, C_R_TIME, C_R_DATE) values (client_seq.NEXTVAL, '" + Session["B_c_name"] + "', '" + Session["B_c_phone"] + "', '" + Session["B_c_email"] + "', '" + Session["B_c_address"] + "', '" + Session["gest"] + "', '" + Session["B_c_package"] + "', '" + Session["f_package"] + "', '" + Session["d_package"] + "', '" + Session["r_date"] + "', '" + Session["r_time"] + "' )";
OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(a);
DataSet ds = new DataSet();
a.Fill(ds, "client");
string cmdquery2 = "Select C_ID from CLIENT where C_NAME = '" + Session["B_c_name"] + "' and C_PHONE = '" + Session["B_c_phone"] + "' ";
OracleDataAdapter b = new OracleDataAdapter(cmdquery2, ConnectionString);
OracleCommandBuilder builder2 = new OracleCommandBuilder(b);
DataSet ds2 = new DataSet();
b.Fill(ds2, "client_id");
int cid = Convert.ToInt32(ds2.Tables["client_id"].Rows[0]["C_ID"]);
string cmdQuery3 = "insert into COST(CO_ID, CO_C_ID, CO_DEC, CO_F_PR, CO_F_TOTAL, CO_TOTAL, CO_ADVANCED, CO_DUE) values (cost_seq.NEXTVAL, '" + cid + "','" + Session["Decuration_price"] + "', '" + Session["FOOD_price"] + "' ,'" + Session["total_f_price"] + "', '" + Session["total_price"] + "', '" + advanced + "', '" + due + "')";
OracleDataAdapter c = new OracleDataAdapter(cmdQuery3, ConnectionString);
OracleCommandBuilder builder3 = new OracleCommandBuilder(c);
DataSet ds3 = new DataSet();
c.Fill(ds3, "COST");
Response.Redirect("booking.aspx");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string ab = Session["B_c_package"].ToString();
string cmdQuery = "Select * from Decuration_Package where dec_pack_service = '"+ ab +"' ";
OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(a);
DataSet ds = new DataSet();
a.Fill(ds, "Decuration_Package");
foreach (DataRow r in ds.Tables["Decuration_Package"].Rows)
{
ListItem i = new ListItem();
i.Text = r["DEC_PACK_NAME"].ToString();
i.Value = r["DEC_PACK_ID"].ToString();
Session["Decuration_price"]= r["DEC_PACK_PRICE"].ToString();
DropDownList_decuration.Items.Add(i);
}
string cmdQuery2 = "Select * from FOOD_PACKAGE where f_pack_service = '" + ab + "' ";
OracleDataAdapter b = new OracleDataAdapter(cmdQuery2, ConnectionString);
OracleCommandBuilder builder2 = new OracleCommandBuilder(b);
DataSet ds2 = new DataSet();
b.Fill(ds2, "FOOD_PACKAGEes");
foreach (DataRow r in ds2.Tables["FOOD_PACKAGEes"].Rows)
{
ListItem i = new ListItem();
i.Text = r["f_pack_name"].ToString();
i.Value = r["f_pack_id"].ToString();
Session["FOOD_price"] = r["f_pack_price"].ToString();
DropDownList_food_package.Items.Add(i);
}
}
}
protected void GridView_stuff_list_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Del_Btn")
{
int index = Convert.ToInt32(e.CommandArgument);
GridViewRow selectedRow = GridView_stuff_list.Rows[index];
TableCell SID = selectedRow.Cells[0];
Session["SID"] = SID.Text;
string ConnectionString = @"DATA SOURCE=127.0.0.1:1521/XE;PERSIST SECURITY INFO=True;USER ID=TAHMID; Password=anik";
string cmdQuery = "delete from STUFF where STUFF_ID = '" +Session["SID"]+"'";
OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(a);
DataSet ds = new DataSet();
a.Fill(ds, "STUFF_delete");
Response.Redirect("stuff_list.aspx");
}
else if (e.CommandName == "edit_Btn")
{
int index = Convert.ToInt32(e.CommandArgument);
GridViewRow selectedRow = GridView_stuff_list.Rows[index];
TableCell SID = selectedRow.Cells[0];
Session["U_SID"] = SID.Text;
Response.Redirect("edit_stuff.aspx");
}
}
private void btn_hyouji_Click(object sender, EventArgs e)
{
try
{
dt.Rows.Clear();
dt.Columns.Clear();
dt.Clear();
TssSystemLibrary tssdb = new TssSystemLibrary();
string connStr = tssdb.GetConnectionString();
OracleConnection conn = new OracleConnection(connStr);
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
string sql = "select * from " + cb_table_name.Text;
if (tb_sql.Text.Length >= 1)
{
sql = sql + " " + tb_sql.Text;
}
cmd.CommandText = sql;
da = new OracleDataAdapter(cmd);
cb = new OracleCommandBuilder(da);
das = new DataSet();
da.Fill(dt);
dgv_table.DataSource = null;
dgv_table.DataSource = dt;
}
catch
{
dgv_table.DataSource = null;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string cmdQuery = "Select * from DESIGNATION";
OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(a);
DataSet ds = new DataSet();
try
{
a.Fill(ds, "DESIGNATION");
foreach (DataRow r in ds.Tables["DESIGNATION"].Rows)
{
ListItem i = new ListItem();
i.Text = r["D_NAME"].ToString();
i.Value = r["D_ID"].ToString();
Stuff_position_DropDownList.Items.Add(i);
}
show_ts(1);
}
catch(Exception er)
{
Response.Write("<script language= 'javascript'>alart('Connection Problem');</script>");
}
finally { }
}
}
protected void Page_Load(object sender, EventArgs e)
{
//DataTable dt = new DataTable();
//dt.Columns.Add("STUFF_NAME");
//dt.Columns.Add("STUFF_GENDER");
//dt.Columns.Add("STUFF_PHONE");
//dt.Columns.Add("STUFF_DOB");
//dt.Columns.Add("STUFF_NID");
//dt.Columns.Add("STUFF_DESIGNATION");
//dt.Columns.Add("STUFF_Salary");
//DataRow dr;
string ConnectionString = @"DATA SOURCE=127.0.0.1:1521/XE;PERSIST SECURITY INFO=True;USER ID=TAHMID; Password=anik";
string cmdQuery = "Select STUFF.STUFF_ID as SID, STUFF.STUFF_NAME as name, STUFF.STUFF_GENDER as gen, STUFF.STUFF_PHONE as phone, STUFF.STUFF_DOB as dob, STUFF.STUFF_NID as nid, DESIGNATION.D_NAME as d_name, DESIGNATION.SALARY as salary, TIME_SHIFT.TS as ts from STUFF, DESIGNATION, TIME_SHIFT where STUFF.STUFF_DESIGNATION = DESIGNATION.D_ID and STUFF.STUFF_TS = TIME_SHIFT.TS_ID ";
OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(a);
DataSet ds = new DataSet();
a.Fill(ds, "STUFF_details");
GridView_stuff_list.DataSource = ds;
GridView_stuff_list.DataBind();
//foreach (DataRow r in ds.Tables["STUFF"].Rows)
//{
// dr = dt.NewRow();
// dr["STUFF_NAME"] = r["STUFF_NAME"].ToString();
// dr["STUFF_GENDER"] = r["STUFF_GENDER"].ToString();
// dr["STUFF_PHONE"] = r["STUFF_PHONE"].ToString();
// dr["STUFF_DOB"] = r["STUFF_DOB"].ToString();
// dr["STUFF_NID"] = r["STUFF_NID"].ToString();
// string i;
// i = r["STUFF_DESIGNATION"].ToString();
// dt.Rows.Add(dr);
// dt.AcceptChanges();
//}
}
private void SaveToDatabase(string name, string D_salary)
{
int salary = Convert.ToInt32(D_salary);
string ConnectionString = @"DATA SOURCE=127.0.0.1:1521/XE;PERSIST SECURITY INFO=True;USER ID=TAHMID; Password=anik";
string cmdQuery = "insert into designation (D_ID, D_NAME, SALARY) values(D_seq.NEXTVAL,'" + name + "', '" + salary + "')";
OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(a);
DataSet ds = new DataSet();
a.Fill(ds, "designation");
}
public static string UnquoteIdentifierIfQuoted(this string identifier)
{
if (!string.IsNullOrWhiteSpace(identifier))
{
var cb = new OracleCommandBuilder();
if (identifier.StartsWith(cb.QuotePrefix) && identifier.EndsWith(cb.QuoteSuffix))
{
return cb.UnquoteIdentifier(identifier);
}
}
return identifier;
}
private void SaveToDatabase(string user_name, string password)
{
string ConnectionString = @"DATA SOURCE=127.0.0.1:1521/XE;PERSIST SECURITY INFO=True;USER ID=TAHMID; Password=anik";
string cmdQuery = "insert into USER_TABLE (USER_ID, USER_NAME, PASSWORD) values(user_seq.NEXTVAL,'" + user_name + "', '" + password + "')";
OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(a);
DataSet ds = new DataSet();
a.Fill(ds, "USER_TABLE");
//OracleDataAdapter a = new OracleDataAdapter(cmdstr, ConnectionString);
//OracleCommandBuilder builder = new OracleCommandBuilder(a);
}
public bool saveData(DataTable dt)
{
DataRow[] d = dt.Select("[address_Line1] = ''");
foreach (DataRow dr in d)
{
dr.Delete();
}
cmdBuilder = new OracleCommandBuilder(adapter);
int ret = adapter.Update(dt);
return(true);
}
private void button2_Click(object sender, EventArgs e)
{
try
{
OracleCommandBuilder builder = new OracleCommandBuilder(dA);
dA.Update(ds.Tables[0]);
MessageBox.Show("Updated Successfully");
}
catch
{
MessageBox.Show("Invalid");
}
}
private void Add_Click(object sender, EventArgs e)
{
try
{
builder = new OracleCommandBuilder(adapter);
adapter.Update(ds.Tables[0]);
MessageBox.Show("Updated!");
}
catch
{
MessageBox.Show("Update failed!");
}
}
// update table
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
try
{
OracleCommandBuilder ocb = new OracleCommandBuilder(oda);
oda.Update(ds, "table");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
builder1 = new OracleCommandBuilder(adapter1);
adapter1.Update(ds.Tables[0]);
MessageBox.Show("Reservation is done successfully :)");
}
catch
{
MessageBox.Show("You already reserved this conference :)");
}
}
public override void UpdateDB(string sql, DataTable dt)
{
try
{
OracleDataAdapter da = new OracleDataAdapter(sql, (OracleConnection)_DBConnection);
OracleCommandBuilder cb = new OracleCommandBuilder(da);
da.Update(dt);
}
catch (Exception ex)
{
throw ex;
}
}
private void просмотрВсехКнигToolStripMenuItem_Click(object sender, EventArgs e)
{
using (OracleConnection oc = new OracleConnection("DATA SOURCE=DESKTOP-UGCSCSS:1521/PDB_BD;" //источник данных имя_компа:порт/имя_пдб
+ "PASSWORD=PDB_BD;" //пароль пользователя
+ "USER ID=PDB_BD;")) //имя пользователя
{
OracleDataAdapter od = new OracleDataAdapter("select * from BOOK", oc); //команда на выполнение
OracleCommandBuilder ob = new OracleCommandBuilder(od); //выполнение команды
DataTable dt = new DataTable(); //виртуальная таблица для заполнения
od.Fill(dt); //заполняем витуальную таблицу результатом запроса
dataGridView1.DataSource = dt; //записываем таблицу в датагрид
}
}
private void button_update_Click(object sender, EventArgs e)
{
try
{
cmdbl = new OracleCommandBuilder(oda);
oda.Update(dt);
MessageBox.Show("updated", "Succesfullu saved!", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Error\n", ex.Message, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
static void Main()
{
string str = "data source=topcredu; User id = scott; password=tiger";
OracleConnection Conn = new OracleConnection(str);
//OleDbConnection Conn = new OleDbConnection(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"] = 8788;
row["ename"] = "87길동";
row["sal"] = 7777;
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["enmpno"], r["ename"], r["sal"]);
}
Console.WriteLine(" 총 {0} 건 입니다.", ds.Tables["사원"].Rows.Count);
}
private void metroButton1_Click(object sender, EventArgs e)
{
Connection sv = new Connection();
sv.thisConnection.Open();
OracleDataAdapter thisAdapter = new OracleDataAdapter("SELECT * FROM users", sv.thisConnection);
OracleCommandBuilder thisBuilder = new OracleCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "data");
DataRow thisRow = thisDataSet.Tables["data"].NewRow();
try
{
OracleCommand seqCommand = new OracleCommand();
seqCommand.Connection = sv.thisConnection;
seqCommand.CommandText = "select seq_person.nextval from users";
OracleDataReader thisReader = seqCommand.ExecuteReader();
thisReader = seqCommand.ExecuteReader();
int nextSeq = 0;
if (thisReader.Read())
{
nextSeq = Convert.ToInt32(thisReader["NEXTVAL"]);
}
thisRow["EmployeeID"] = nextSeq;
thisRow["Username"] = metroTextBox4.Text;
thisRow["Type"] = metroComboBox1.Text;
thisRow["Name"] = metroTextBox6.Text;
thisRow["Join"] = DateTime.Now.ToString("dd-MM-yyyy");
thisRow["Password"] = metroTextBox1.Text;
thisRow["PIN"] = metroTextBox5.Text;
thisDataSet.Tables["data"].Rows.Add(thisRow);
thisAdapter.Update(thisDataSet, "data");
MessageBox.Show("Submitted");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
MessageBox.Show(metroComboBox1.Text);
}
sv.thisConnection.Close();
Form1 l = new Form1();
l.Show();
this.Hide();
}
public static int ImportTableInto(DataTable impTb, string intoTb, string select, int clear)
{
int count = 0;
DataTable target = null;
//导入前是否先清空
if (clear == 1)
{
DBAccess.RunSQL("delete from " + intoTb);
}
try
{
target = DBAccess.RunSQLReturnTable(select);
}
catch (Exception ex) //select查询出错，可能是缺少列
{
throw new Exception("源表格式有误，请核对！" + ex.Message + " :" + select);
}
object conn = DBAccess.GetAppCenterDBConn;
SqlDataAdapter sqlada = null;
OracleDataAdapter oraada = null;
DBType dbt = DBAccess.AppCenterDBType;
if (dbt == DBType.MSSQL)
{
sqlada = new SqlDataAdapter(select, (SqlConnection)DBAccess.GetAppCenterDBConn);
SqlCommandBuilder bl = new SqlCommandBuilder(sqlada);
sqlada.InsertCommand = bl.GetInsertCommand();
count = ImportTable(impTb, target, sqlada);
}
else if (dbt == DBType.Oracle)
{
oraada = new OracleDataAdapter(select, (OracleConnection)DBAccess.GetAppCenterDBConn);
OracleCommandBuilder bl = new OracleCommandBuilder(oraada);
oraada.InsertCommand = bl.GetInsertCommand();
count = ImportTable(impTb, target, oraada);
}
else
{
throw new Exception("未获取数据库连接！ ");
}
target.Dispose();
return(count);
}
private void delBtn_Click(object sender, EventArgs e)
{
//disconnected 3 delete
string cmdstr = "delete from courses where course_id = :cid";
adapter = new OracleDataAdapter(cmdstr, ordb);
adapter.SelectCommand.Parameters.Add("cid", textBox4.Text);
builder = new OracleCommandBuilder(adapter);
adapter.Fill(ds);
adapter.Update(ds.Tables[0]);
refreshDGV2();
}
protected void saveBtn_Click(object sender, EventArgs e)
{
int recipeID = (int)Session["recipeID"];
OracleConnection conn = null;
OracleDataAdapter adapter;
OracleCommandBuilder commandBuilder;
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
conn = new OracleConnection(connectionString);
adapter = new OracleDataAdapter("select ingredient_name, quantity, unit_of_measure from ingredients where recipe_id = " + recipeID, conn);
commandBuilder = new OracleCommandBuilder(adapter);
adapter.Update(((DataSet)ViewState["IngredientDataSet"]).Tables["Ingredients"]);
}
public void display()
{
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
OracleConnection connection = new OracleConnection(constring);
OracleDataAdapter adapter = new OracleDataAdapter("select * from Employees", connection);
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "Employees");
dg.DataContext = ds.Tables[0];
}
protected void add_stuff_btn_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(stuff_name.Text))
{
LabelMessage.Text = "Stuff Name is empty";
stuff_name.Focus();
}
else if (string.IsNullOrEmpty(stuff_phone.Text))
{
LabelMessage.Text = "Stuff Phone is empty";
stuff_phone.Focus();
}
else if (string.IsNullOrEmpty(stuff_address.Text))
{
LabelMessage.Text = "Stuff Address is empty";
stuff_address.Focus();
}
else if (string.IsNullOrEmpty(stuff_dob.Text))
{
LabelMessage.Text = "Date of Birth is empty";
stuff_dob.Focus();
}
else if (string.IsNullOrEmpty(stuff_nid.Text))
{
LabelMessage.Text = "National Id is empty";
stuff_nid.Focus();
}
else
{
try
{
int D_id = Convert.ToInt32(Request.Form[Stuff_position_DropDownList.UniqueID]);
int ts_id = Convert.ToInt32(Request.Form[DropDownList_ts.UniqueID]);
Session["D_Id"] = D_id;
Session["ts_id"] = ts_id;
string cmdQuery = "insert into STUFF(STUFF_ID, STUFF_NAME, STUFF_GENDER, STUFF_PHONE, STUFF_ADDRESS, STUFF_DOB, STUFF_NID, STUFF_DESIGNATION, STUFF_TS) values(STF_seq.NEXTVAL, '" + stuff_name.Text + "', '" + stuff_gender.SelectedItem.Value + "' ,'" + stuff_phone.Text + "', '" + stuff_address.Text + "', '" + stuff_dob.Text + "', '" + stuff_nid.Text + "', '" + Session["D_Id"] + "' , '"+Session["ts_id"]+"') ";
OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(a);
DataSet ds = new DataSet();
a.Fill(ds, "STUFF");
LabelMessage.Text = "Data saved successfully";
}
catch (Exception er)
{
LabelMessage.Text = " Please, Insert during office hours. ";
}
}
}
public void PrepareInsert(string tableName)
{
string sql = "SELECT * FROM " + tableName;
dataAdapter = new OracleDataAdapter(sql, connection);
OracleCommandBuilder cb = new OracleCommandBuilder(dataAdapter);
dataSet = new DataSet();
dataAdapter.SelectCommand.Transaction = transaction;
dataAdapter.Fill(dataSet);
}
private void buttonAnuleazaModificariConsultatie_Click(object sender, EventArgs e)
{
button1.Visible = false;
try
{
OracleCommandBuilder command = new OracleCommandBuilder(dataAdapterConsultatii);
dataAdapterConsultatii.Update(dataSetConsultatii.Tables["Consultatii"]);
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString());
}
getDiagnosticsForPatients(CNP);
}
public override Params ExecuteStoredProcedureWithTransaction(object trnTransaction, string strProcedureName, params object[] cmdParams)
{
Params params2;
Params @params = new OracleParams();
OracleConnection objConnection = (OracleConnection)base.objConnection;
OracleTransaction tx = (OracleTransaction)trnTransaction;
try
{
OracleCommand command = new OracleCommand(strProcedureName, objConnection, tx)
{
CommandType = CommandType.StoredProcedure
};
if (objConnection.State == ConnectionState.Closed)
{
objConnection.Open();
}
OracleCommandBuilder.DeriveParameters(command);
for (int i = 0; i < command.Parameters.Count; i++)
{
if (command.Parameters[i].Direction == ParameterDirection.Input)
{
if (cmdParams.Length <= i)
{
throw new Exception("Parameter Count does not match.");
}
command.Parameters[i].Value = cmdParams[i];
}
else
{
command.Parameters[i].Value = DBNull.Value;
}
}
command.ExecuteNonQuery();
foreach (OracleParameter parameter in command.Parameters)
{
if ((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Output))
{
@params.Add(parameter);
}
}
params2 = @params;
}
catch (Exception exception)
{
throw exception;
}
return(params2);
}
private void button1_Click(object sender, EventArgs e)
{
string s2 = "", s3 = "", s4 = "";
s2 = textBox1.Text.ToString();
s3 = textBox3.Text.ToString();
s4 = textBox5.Text.ToString();
if ((s2 == "") || (s3 == "") || (s4 == ""))
{
MessageBox.Show("ошибка ввода данных");
}
else
{
using (OracleConnection oc = new OracleConnection("DATA SOURCE=DESKTOP-UGCSCSS:1521/PDB_BD;" //источник данных имя_компа:порт/имя_пдб
+ "PASSWORD=PDB_BD;" //пароль пользователя
+ "USER ID=PDB_BD;")) //имя пользователя
{
OracleDataAdapter od = new OracleDataAdapter("select * from users ", oc); //команда на выполнение
OracleCommandBuilder ob = new OracleCommandBuilder(od); //выполнение команды
DataTable dt = new DataTable(); //виртуальная таблица для заполнения
od.Fill(dt); //заполняем витуальную таблицу результатом запроса
dataGridView1.DataSource = dt; //записываем таблицу в датагрид
int max = 0;
int znach = 0;
try
{
for (int i = 0; i <= 50; i++)
{
znach = Convert.ToInt32(dataGridView1[0, i].Value.ToString());
if (max < znach)
{
max = znach;
}
}
}
catch { }
max++;
oc.Open();
int d = 0;
OracleCommand com = new OracleCommand("BEGIN new_user('" + max.ToString() + "' , '" + s2.ToString() + "' ,'" + s3.ToString() + "','" + s4.ToString() + "'); END; ", oc);
com.ExecuteNonQuery();
}
MessageBox.Show("регистрирование произошло успешно");
}
}
public bool UpdateTable(DataTable table, string tableName, bool setInsertOn = true, bool setModifyOn = true, string additionalMessage = "")
{
try
{
TableHelper.SetDefaultColumnValues(table, setInsertOn, setModifyOn);
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!",
AdditionalMessage = $"Table: {tableName}{Environment.NewLine}AdditionalMessage: {additionalMessage}",
Ex = cex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateTable Error!", cex);
}
return(false);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable Error!",
AdditionalMessage = $"Table: {tableName}{Environment.NewLine}AdditionalMessage: {additionalMessage}",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateTable Error!", ex);
}
return(false);
}
}
public int Insert(Hashtable hashTable, string SourceTable)
{
OracleConnection oracleConn = dbConectorObj.GetConnection;
OpenAppConnection();
try
{
OracleCommand oracleComd = new OracleCommand("SELECT * FROM " + SourceTable + " WHERE NOT(1=1)", oracleConn);
OracleDataAdapter ODP = new OracleDataAdapter(oracleComd);
DataSet DS = new DataSet();
ODP.Fill(DS, SourceTable);
DataRow drAddrow = DS.Tables[0].NewRow();
foreach (object OBJ in hashTable.Keys)
{
string colName = Convert.ToString(OBJ);
drAddrow[colName] = hashTable[OBJ];
}
DS.Tables[0].Rows.Add(drAddrow);
OracleCommandBuilder ocmd = new OracleCommandBuilder(ODP);
ODP.InsertCommand = ocmd.GetInsertCommand();
return(ODP.Update(DS, SourceTable));
}
catch (OracleException ex)
{
if (Trans != null)
{
Trans.Rollback();
Trans = null;
}
throw ex;
}
catch (Exception Ex)
{
if (Trans != null)
{
Trans.Rollback();
Trans = null;
}
throw Ex;
}
finally
{
if (Trans == null)
{
CloseAppConnection();
}
}
}
public void privateinfo()
{
conn = new OracleConnection(connstr);
conn.Open();
string select = "select * from rentalx where STUDENT_ID='" + user_lbl.Text + "' ";
OracleDataAdapter oda = new OracleDataAdapter(select, conn);
OracleCommandBuilder commandBuilder = new OracleCommandBuilder(oda);
DataSet ds = new DataSet();
oda.Fill(ds);
dataGridView1.ReadOnly = true;
dataGridView1.DataSource = ds.Tables[0];
}
public static void ListParms()
{
OracleConnection conn = new OracleConnection("SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.3.1.11)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MDMSPMIS)));User Id=MDMS;Password=MDMS;");
OracleCommand cmd = new OracleCommand("PKG_WEBSERVICE_MDMS.Get_DiemDo", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
OracleCommandBuilder.DeriveParameters(cmd);
foreach (OracleParameter p in cmd.Parameters)
{
string acb = p.ParameterName + " " + p.OracleType + " " + p.Value + " " + p.Size;
var dir = p.Direction;
var dirf = p.Offset;
}
}
/// <summary>
/// 更新角色数据
/// </summary>
/// <param name="_savedt"></param>
/// <returns></returns>
public static bool Update_RoleData(DataTable _savedt)
{
string cmdStr = "SELECT * FROM QX_JSDYB";
using (OracleConnection cn = OracleHelper.OpenConnection())
{
OracleTransaction tx = cn.BeginTransaction();
OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, cn);
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
adapter.Update(_savedt);
tx.Commit();
cn.Close();
}
return(true);
}
private void Home_btn_Click(object sender, EventArgs e)
{
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
adapter.Update(dataset.Tables[0]);
refToEmployee.Show();
string constr = "Data Source=orcl;User Id=scott;Password=tiger;";
string cmdstr = @"select EVENT_ID,EVENT_TITLE,EVENT_DURATION,EVENT_DATE,EVENT_BUDGET,EVENT_CAPACITY,EVENT_PRIVACY
from event";
adapter1 = new OracleDataAdapter(cmdstr, constr);
refToEmployee.dataset.Clear();
adapter.Fill(refToEmployee.dataset);
this.Hide();
}
public void SelectModalidades()
{
connection.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = "select id_modalidade, nome, pont_maxima, tipo_pont, g.descricao, comprovante from modalidade m inner join grupo g on (m.id_grupo = g.id_grupo) order by id_modalidade";
OracleDataAdapter adapter = new OracleDataAdapter(cmd.CommandText, connection);
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
DataTable data = new DataTable();
adapter.Fill(data);
dtGvModalidade.DataSource = data;
connection.Close();
}
void IDatabase.DeriveParameters()
{
try
{
OracleCommandBuilder.DeriveParameters(this.sqlDataAdapter.SelectCommand);
}
catch (AtomusException exception)
{
throw exception;
}
catch (Exception exception)
{
throw new AtomusException(exception);
}
}
public void UpdateContentOfTable(string tableName, DataTable content)
{
using (var c = _connection.CreateCommand())
{
c.CommandText = string.Format("select * from {0}", tableName);
using (var adapter = new OracleDataAdapter(c))
{
using (var builder = new OracleCommandBuilder(adapter))
{
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.Update(content);
}
}
}
}
public void initAdapter(string statement)
{
try
{
Table = new DataTable();
this.statement = statement;
Adapter = new OracleDataAdapter(statement, App.connection);
Builder = new OracleCommandBuilder(Adapter);
Adapter.Fill(Table);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void DB_Open()
{
try
{
string connectionString = "User Id=LPK; Password=1234; Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.142.16)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = xe)) ); ";
string commandString = "select * from 회원";
DBAdapter = new OracleDataAdapter(commandString, connectionString);
myCommandBuilder = new OracleCommandBuilder(DBAdapter);
DS = new DataSet();
}
catch (DataException DE)
{
MessageBox.Show(DE.Message);
}
}
public static string UnquoteIdentifierIfQuoted(this OracleCommandBuilder commandBuilder, string identifier)
{
if (commandBuilder == null)
{
throw new ArgumentNullException("commandBuilder");
}
if (!string.IsNullOrWhiteSpace(identifier))
{
if (identifier.StartsWith(commandBuilder.QuotePrefix) && identifier.EndsWith(commandBuilder.QuoteSuffix))
{
return(commandBuilder.UnquoteIdentifier(identifier));
}
}
return(identifier);
}
private void Data_Load()
{
openconnection();
string strquerry = "Select * From Salary";
OracleDataAdapter sqlDataAdapter = new OracleDataAdapter(strquerry, con);
OracleCommandBuilder sqlCommandBuilder = new OracleCommandBuilder(sqlDataAdapter);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;
accountmanage_dataGridView.DataSource = bindingSource;
closeconnection();
}
private void Data_Load()
{
openconnection();
string strquerry = "Select id,name,nationalid,Designation,contact,address,experience From employee where active = 1";
OracleDataAdapter sqlDataAdapter = new OracleDataAdapter(strquerry, con);
OracleCommandBuilder sqlCommandBuilder = new OracleCommandBuilder(sqlDataAdapter);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;
employee_dataGridView.DataSource = bindingSource;
closeconnection();
}
protected void add_stuff_btn_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(stuff_name.Text))
{
LabelMessage.Text = "Stuff Name is empty";
stuff_name.Focus();
}
else if (string.IsNullOrEmpty(stuff_phone.Text))
{
LabelMessage.Text = "Stuff Phone is empty";
stuff_phone.Focus();
}
else if (string.IsNullOrEmpty(stuff_address.Text))
{
LabelMessage.Text = "Stuff Address is empty";
stuff_address.Focus();
}
else if (string.IsNullOrEmpty(stuff_dob.Text))
{
LabelMessage.Text = "Date of Birth is empty";
stuff_dob.Focus();
}
else if (string.IsNullOrEmpty(stuff_nid.Text))
{
LabelMessage.Text = "National Id is empty";
stuff_nid.Focus();
}
else
{
try
{
string ConnectionString = @"DATA SOURCE=127.0.0.1:1521/XE;PERSIST SECURITY INFO=True;USER ID=TAHMID; Password=anik";
string cmdQuery = "UPDATE STUFF SET STUFF_NAME= '" + stuff_name.Text + "' , STUFF_GENDER = '" + stuff_gender.SelectedItem.Value + "', STUFF_PHONE = '" + stuff_phone.Text + "', STUFF_ADDRESS= '" + stuff_address.Text + "', STUFF_DOB = '" + stuff_dob.Text + "', STUFF_NID = '" + stuff_nid.Text + "', STUFF_DESIGNATION = '" + 3 + "', STUFF_TS = '" + 2 + "' where STUFF_ID = '" + Session["U_SID"] + "'";
OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(a);
DataSet ds = new DataSet();
a.Fill(ds, "STUFF_update");
Response.Redirect("stuff_list.aspx");
}
catch (Exception er)
{
LabelMessage.Text = " Please, Edit during office hours. ";
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string cmdQuery = "Select * from service_type";
OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(a);
DataSet ds = new DataSet();
a.Fill(ds, "service_type");
foreach (DataRow r in ds.Tables["service_type"].Rows)
{
ListItem i = new ListItem();
i.Text = r["service_name"].ToString();
i.Value = r["service_id"].ToString();
DropDownList_package.Items.Add(i);
}
}
}
protected void sign_in_btn_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(this.id.Text) && string.IsNullOrEmpty(this.password.Text))
{
this.id_err_lbl.Text = "Please Enter Your Id.";
this.pass_err_lbl.Text = "Please Enter Your Paasword.";
}
else if (string.IsNullOrEmpty(this.id.Text))
{
this.id_err_lbl.Text = "Please Enter Your Id.";
}
else if (string.IsNullOrEmpty(this.password.Text))
{
this.pass_err_lbl.Text = "Please Enter Your Paasword.";
}
else
{
string admin_id = this.id.Text;
string admin_pass = this.password.Text;
string ConnectionString = @"DATA SOURCE=127.0.0.1:1521/XE;PERSIST SECURITY INFO=True;USER ID=TAHMID; Password=anik";
string cmdQuery = "Select USER_NAME from USER_TABLE where USER_NAME = '" + admin_id + "' and PASSWORD = '******' ";
OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(a);
DataSet ds = new DataSet();
a.Fill(ds,"login");
if (ds.Tables["login"].Rows[0]["USER_NAME"].ToString() == admin_id)
{
Session["user"] = admin_id;
Response.Redirect("admin.aspx");
}
else
{
this.id_err_lbl.Text = "User and Password Combination doesn't match!";
}
}
}
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 string QuoteSchemaAndTable(this string sqlQuery, string schemaName, string tableName)
{
var cb = new OracleCommandBuilder();
return string.Format(sqlQuery, cb.QuoteIdentifier(schemaName), cb.QuoteIdentifier(tableName));
}
private static OracleDataAdapter BuildOracleDataAdapter()
{
try
{
OracleConnection conn = new OracleConnection(connectionString);
OracleDataAdapter adapter = null;
if (statusInfo.Source == BankReportType.PAYMUL)
{
adapter = new OracleDataAdapter(SelectScript4Insert(), conn);
}
else
{
string sql = SelectScript4Update();
if (string.IsNullOrEmpty(sql) || !Regex.IsMatch(sql, "select", RegexOptions.IgnoreCase))
{
throw new Exception("Invalid Query statement.");
}
adapter = new OracleDataAdapter(SelectScript4Update(), conn);
}
OracleCommandBuilder cmdBuilder = new OracleCommandBuilder(adapter);
return adapter;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// ����selectedCommandText�����OracleDataAdapter�����Զ�����
/// InsertCommand��DeleteCommand��UpdateCommand
/// </summary>
/// <param name="selectedCommandText">selectedCommandText</param>
/// <returns>The SqlDataAdapter</returns>
public override IDbDataAdapter GetDataAdapter(string selectCommandText, IDbConnection connection)
{
OracleDataAdapter adapter = new OracleDataAdapter(selectCommandText,(OracleConnection)connection);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
using(OracleCommandBuilder builder = new OracleCommandBuilder(adapter))
{
adapter.InsertCommand = (OracleCommand)((ICloneable)builder.GetInsertCommand()).Clone();
adapter.DeleteCommand = (OracleCommand)((ICloneable)builder.GetDeleteCommand()).Clone();
adapter.UpdateCommand = (OracleCommand)((ICloneable)builder.GetUpdateCommand()).Clone();
}
return adapter;
}
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 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;
}
/// <summary>
/// 将设定开始行的DataTable数据集导入到数据库
/// </summary>
/// <param name="dtImport">数据集</param>
/// <param name="strSql">执行SQL</param>
/// <param name="intStartRow">读取数据集的起始行</param>
public void WriteDataTableToDB(DataTable dtImport, string strSql, int intStartRow)
{
OracleDataAdapter myAdp = GetDataAdapter(strSql);
DataTable dtWrite = new DataTable();
myAdp.Fill(dtWrite);
OracleCommandBuilder comBuilder = new OracleCommandBuilder(myAdp);//这一句很关键，必不可少。
if (dtWrite.Columns.Count != dtImport.Columns.Count)
{
throw new Exception("对不起，导入文件的列数不符合导入规范，请检查！");
}
for (int i = intStartRow; i < dtImport.Rows.Count; i++)
{
dtWrite.Rows.Add(dtImport.Rows[i].ItemArray);
}
OpenConn();
myAdp.Update(dtWrite);
}
/// <summary>
/// 以Adapter方式导入Datatable映射的列到数据库表
/// </summary>
/// <param name="dataTable">要导入的dataTable</param>
/// <param name="tableName">目标表名</param>
/// <param name="columnMapping">列映射关系(第1列为源列,第2列为目标列)</param>
/// <returns>成功返回success，否则为返回错误消息</returns>
public string ImportDatatableToDB(DataTable dataTable, string tableName, DataRow[] columnMapping)
{
string field = "";
foreach (DataRow row in columnMapping)
{
field = field + "," + row[1];
dataTable.Columns[row[0].ToString()].ColumnName = row[1].ToString();//映射列名
}
if (field.StartsWith(","))
field = field.Substring(1);
OracleDataAdapter adapter = GetDataAdapter(string.Format("select {0} from {1} where 1=2", field, tableName));
OracleCommandBuilder cmb = new OracleCommandBuilder(adapter);
OpenConn();
adapter.Update(dataTable);
return "success";
}
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;
}
}
private void show_ts(int a)
{
string cmdQuery = "Select * from time_shift";
OracleDataAdapter b = new OracleDataAdapter(cmdQuery, ConnectionString);
OracleCommandBuilder builder = new OracleCommandBuilder(b);
DataSet ds2 = new DataSet();
b.Fill(ds2, "time_shift");
foreach (DataRow r in ds2.Tables["time_shift"].Rows)
{
ListItem i = new ListItem();
i.Text = r["ts"].ToString();
i.Value = r["ts_id"].ToString();
DropDownList_ts.Items.Add(i);
}
}
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 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);
}
private void Assign_Click(object sender, EventArgs e)
{
//ProjectManage_Clear_Click(sender, e);
checktable = 2; //for assign table
openconnection();
string strquerry = "Select EmployeeId,ProjectId,WorkTime,AssignDate From AssignOfProject Where WorkStatus=1"; //Order By Employee_Id ASC korte hobe
//string strquerry = "Select * From Project Where Status = 1";
OracleDataAdapter sqlDataAdapter = new OracleDataAdapter(strquerry, con);
OracleCommandBuilder sqlCommandBuilder = new OracleCommandBuilder(sqlDataAdapter);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;
projectmanageassign_dataGridView.DataSource = bindingSource;
closeconnection();
}
private void ProjectData_Load()
{
checktable = 1; //for project table
openconnection();
string strquerry = "Select * From Project";
//string strquerry = "Select * From Project Status = 1";
OracleDataAdapter sqlDataAdapter = new OracleDataAdapter(strquerry, con);
OracleCommandBuilder sqlCommandBuilder = new OracleCommandBuilder(sqlDataAdapter);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;
projectmanageassign_dataGridView.DataSource = bindingSource;
closeconnection();
}