/// <summary>
/// 执行SQL，返回DataTable
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public DataTable ExecuteTable(string sql)
{
OracleConnection myConnection = null;
OracleCommand myORACCommand = null;
DataTable datatable = null;
try
{
if (executor != null && executor.IsExecutoring)
{
myConnection = executor.Connection;
myORACCommand = myConnection.CreateCommand();
myORACCommand.Transaction = executor.Transaction;
}
else
{
myConnection = new OracleConnection(connectionString);
myConnection.Open();
myORACCommand = myConnection.CreateCommand();
}
datatable = new DataTable();
myORACCommand.CommandText = sql;
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(myORACCommand);
da.Fill(ds);
if (ds.Tables.Count != 0)
{
datatable = ds.Tables[0];
}
if (executor == null || !executor.IsExecutoring)
{
myORACCommand.Cancel();
myConnection.Close();
}
datatable.TableName = "table";
return(datatable);
}
catch (Exception ex)
{
///抛出类型转换错误
//SystemError.CreateErrorLog(ex.Message);
myORACCommand.Cancel();
myConnection.Close();
throw new Exception(ex.Message, ex);
}
}
/// <summary
/// 执行SQL，返回Void
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public void ExecuteNonQuery(string sql)
{
OracleConnection myConnection = null;
OracleCommand myORACCommand = null;
if (executor != null && executor.IsExecutoring)
{
myConnection = executor.Connection;
myORACCommand = myConnection.CreateCommand();
myORACCommand.Transaction = executor.Transaction;
}
else
{
myConnection = new OracleConnection(connectionString);
myConnection.Open();
myORACCommand = myConnection.CreateCommand();
}
myORACCommand.CommandText = sql;
myORACCommand.ExecuteNonQuery();
myORACCommand.Cancel();
if (executor == null || !executor.IsExecutoring)
{
myConnection.Close();
}
}
// 입력
public int thrm_add(params object[] val)
{
int check = 1;
try
{
if (_DB.GetConnection() == true)
{
OracleCommand comm = new OracleCommand();
comm.Connection = _DB.Connection;
//CD_GRPCD, CD_CODE, CD_SEQ, CD_CODNMS, CD_USE, CD_SDATE, CD_EDATE
comm.CommandText = @"INSERT INTO tieas_cd_hwy values(:empno, :resno, :name, :cname, '', '', '', :ename, :datasys1, :datasys2)";
comm.Parameters.Add("empno", val[0]);
comm.Parameters.Add("resno", val[1]);
comm.Parameters.Add("name", val[2]);
comm.Parameters.Add("cname", val[3]);
comm.Parameters.Add("ename", val[4]);
comm.Parameters.Add("datasys1", Convert.ToDateTime(val[5]).ToString("yyyyMMdd"));
comm.Parameters.Add("datasys2", Convert.ToDateTime(val[6]).ToString("yyyyMMdd"));
comm.Prepare();
var a = comm.ExecuteNonQuery();
Console.WriteLine(a + " row insert");
comm.Cancel();
comm.Dispose();
check = 0;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(check);
}
internal override void ExecuteNonQuery()
{
if (string.IsNullOrEmpty(CommandText))
{
throw new ArgumentNullException("CommandText");
}
AdapterState = AdapterState.Executing;
CheckConnectionStr();
using (OracleConnection oracleCn = new OracleConnection(base.ConnectionStr))
{
oracleCn.Open();
OracleCommand oracleCmd = new OracleCommand(CommandText, oracleCn);
oracleCmd.CommandType = CommandType;
if (Parameters != null)
{
oracleCmd.Parameters.AddRange(Parameters.ToArray());
}
oracleCmd.ExecuteNonQuery();
oracleCmd.Cancel();
oracleCn.Close();
oracleCmd.Dispose();
oracleCn.Dispose();
}
AdapterState = AdapterState.Waiting;
}
private void Update_btn_Click(object sender, RoutedEventArgs e)
{
sql = "select count(*) from player_character where player_id=:playerid";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("playerid", OracleDbType.NChar).Value = Player_Id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow == 0)
{
String msg = "此player_id未被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
else
{
sql = "update player_character set nickname = :nickname ," +
" player_level= :player_level , spec_id = :spec_id , " +
"weapon_id = :weapon_id , armor_id = :armor_id " +
"where player_id = :player_id";
this.AUD(sql, 1);
}
}
// 입력
public int thrm_car_add(String empno, String car_com, String car_region, DateTime car_yyyymm_f, DateTime car_yyyymm_t, String car_pos, String car_dept, String car_job, String car_reason)
{
int check = 1;
try
{
if (_DB.GetConnection() == true)
{
OracleCommand comm = new OracleCommand();
comm.Connection = _DB.Connection;
comm.CommandText = @"INSERT INTO THRM_CAR_HWY values(:empno, :resno, :name, :cname, :ename, :datasys1, :datasys2, :datasys3, :car_reason)";
comm.Parameters.Add("empno", empno);
comm.Parameters.Add("resno", car_com);
comm.Parameters.Add("name", car_region);
comm.Parameters.Add("cname", car_yyyymm_f.ToString("yyyyMM"));
comm.Parameters.Add("ename", car_yyyymm_t.ToString("yyyyMM"));
comm.Parameters.Add("datasys1", car_pos);
comm.Parameters.Add("datasys2", car_dept);
comm.Parameters.Add("datasys3", car_job);
comm.Parameters.Add("car_reason", car_reason);
comm.Prepare();
var a = comm.ExecuteNonQuery();
Console.WriteLine(a + " row insert");
comm.Cancel();
comm.Dispose();
check = 0;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(check);
}
private void Add_btn_Click(object sender, RoutedEventArgs e)
{
String sql = "select count(*) from armor where armor_id=:armor_id";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("armor_id", OracleDbType.NChar).Value = armor_id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow > 0)
{
String msg = "armor_id已被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
sql = "insert into armor(armor_id,armor_name,pd_weighted,md_weighted,speed_weighted,dodge_weighted,hp_plus,mp_plus,durability)" +
"values(:armor_id,:armor_name,:pd_weighted,:md_weighted,:speed_weighted,:dodge_weighted,:hp_plus,:mp_plus,:durability)";
this.AUD(sql, 0);
add_btn.IsEnabled = false;
update_btn.IsEnabled = true;
delete_btn.IsEnabled = true;
}
private void Add_btn_Click(object sender, RoutedEventArgs e)
{
String sql = "select count(*) from player_info where player_id=:playerid";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("playerid", OracleDbType.NChar).Value = Player_Id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow > 0)
{
String msg = "player_id已被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
else
{
sql = "insert into player_info(player_id,password,email,tel)" +
"values(:player_id,:password,:email,:tel)";
this.AUD(sql, 0);
add_btn.IsEnabled = false;
update_btn.IsEnabled = true;
delete_btn.IsEnabled = true;
}
}
/** Sterge un Angajat
* Va fi marcat cu statusul inactiv **/
private void buttonDeleteObject_Click(object sender, EventArgs e)
{
int id_angajat = -1;
foreach (DataGridViewRow row in dataGridAngajati.SelectedRows)
{
id_angajat = int.Parse(row.Cells[0].Value.ToString());
}
if (id_angajat == -1)
{
MessageBox.Show("[Eroare] Va rugam sa selectati un angajat!");
}
else
{
try
{
OracleCommand command = MyOracleConnection.con.CreateCommand();
command.CommandText = "userDBA.ADMIN_PACKAGE.PROC_DELETE_ANGAJAT";
// asigneaza valori parametrilor
command.Parameters.Add(new OracleParameter("id_angajat", OracleDbType.Int32)).Value = id_angajat;
// executa funtia
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
command.Cancel();
afisareAngajati();
}
catch (Exception exc)
{
MessageBox.Show("[Log - Exception] " + exc);
}
}
}
/// <summary>
/// 执行查询,返回查询结果的第一行第一列
/// </summary>
/// <typeparam name="T">返回值类型</typeparam>
/// <param name="sql">SQL查询语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>查询结果的第一行第一列</returns>
public override T ExecuteScalar <T>(string sql, KdtParameterCollection parameters)
{
try
{
T value = default(T);
// 执行SQL命令
using (OracleCommand cmd = new OracleCommand(ReplaceSqlText(ReplaceSqlText(sql, parameters), parameters), _oracleCn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToSqlParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
value = cmd.ExecuteScalar().Convert <T>();
cmd.Cancel();
cmd.Dispose();
}
return(value);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行查询,返回查询结果的第一行第一列错误,原因为:{0}", ex.Message));
}
}
private void Add_btn_Click(object sender, RoutedEventArgs e)
{
String sql = "select count(*) from weapon where weapon_id=:weapon_id";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("weapon_id", OracleDbType.NChar).Value = weapon_id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow > 0)
{
String msg = "weapon_id已被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
sql = "insert into weapon(weapon_id,weapon_type_id,weapon_name,atk)" +
"values(:weapon_id,:weapon_type_id,:weapon_name,:atk)";
this.AUD(sql, 0);
add_btn.IsEnabled = false;
update_btn.IsEnabled = true;
delete_btn.IsEnabled = true;
}
/// <summary>
/// 执行非查询SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>受影响的记录数</returns>
public override int ExecuteNoQuery(string sql, KdtParameterCollection parameters)
{
try
{
int effected = 0;
// 执行SQL命令
using (OracleCommand cmd = new OracleCommand(ReplaceSqlText(sql, parameters), _oracleCn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToSqlParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
effected = cmd.ExecuteNonQuery();
cmd.Cancel();
cmd.Dispose();
}
return(effected);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行非查询SQL语句错误,原因为:{0}", ex.Message));
}
}
private void Update_btn_Click(object sender, RoutedEventArgs e)
{
sql = "select count(*) from player_level where player_level=:player_level";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("player_level", OracleDbType.NChar).Value = player_level_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow == 0)
{
String msg = "此armor_id未被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
else
{
sql = "update player_level set hp_base = :hp_base ," +
" mp_base= :mp_base , pda_base = :pda_base , " +
"mda_base = :mda_base , pde_base = :pde_base, mde_base = :mde_base " +
"where player_level = :player_level";
this.AUD(sql, 1);
}
}
// 입력
public int thrm_add(params object[] val)
{
int check = 1;
try
{
if (_DB.GetConnection() == true)
{
OracleCommand comm = new OracleCommand();
comm.Connection = _DB.Connection;
comm.CommandText = @"INSERT INTO tieas_cdg_hwy values(:empno, :resno, :name, :cname, :ename, :datasys1)";
comm.Parameters.Add("empno", val[0]);
comm.Parameters.Add("resno", val[1]);
comm.Parameters.Add("name", val[2]);
comm.Parameters.Add("cname", val[3]);
comm.Parameters.Add("ename", val[4]);
comm.Parameters.Add("datasys1", val[5]);
comm.Prepare();
var a = comm.ExecuteNonQuery();
Console.WriteLine(a + " row insert");
comm.Cancel();
comm.Dispose();
check = 0;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(check);
}
private void Update_btn_Click(object sender, RoutedEventArgs e)
{
sql = "select count(*) from player_info where player_id=:playerid";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("playerid", OracleDbType.NChar).Value = Player_Id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow == 0)
{
String msg = "此player_id未被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
else
{
sql = "update player_info set password = :password ," +
" email= :email , tel = :tel " +
"where player_id = :player_id";
this.AUD(sql, 1);
}
}
private void Update_btn_Click(object sender, RoutedEventArgs e)
{
sql = "select count(*) from armor where armor_id=:armor_id";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("armor_id", OracleDbType.NChar).Value = armor_id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow == 0)
{
String msg = "此armor_id未被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
else
{
sql = "update armor set armor_name = :armor_name ," +
" pd_weighted= :pd_weighted , md_weighted = :md_weighted , " +
"speed_weighted = :speed_weighted , dodge_weighted = :dodge_weighted, " +
"hp_plus = :hp_plus , mp_plus = :mp_plus ," + "durability = :durability " +
"where armor_id = :armor_id";
this.AUD(sql, 1);
}
}
// 입력
public int thrm_add(String empno, String car_com, String car_region, DateTime car_yyyymm_f, String car_yyyymm_t)
{
int check = 1;
try
{
if (_DB.GetConnection() == true)
{
OracleCommand comm = new OracleCommand();
comm.Connection = _DB.Connection;
comm.CommandText = @"INSERT INTO thrm_lic_hwy values(:empno, :resno, :name, :cname, :ename)";
comm.Parameters.Add("empno", empno);
comm.Parameters.Add("resno", car_com);
comm.Parameters.Add("name", car_region);
comm.Parameters.Add("cname", car_yyyymm_f.ToString("yyyyMMdd"));
comm.Parameters.Add("ename", car_yyyymm_t);
comm.Prepare();
var a = comm.ExecuteNonQuery();
Console.WriteLine(a + " row insert");
comm.Cancel();
comm.Dispose();
check = 0;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(check);
}
private void Delete_btn_Click(object sender, RoutedEventArgs e)
{
String sql = "select count(*) from armor where armor_id=:armor_id";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("armor_id", OracleDbType.NChar).Value = armor_id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow == 0)
{
String msg = "此armor_id未被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
else
{
sql = "delete from armor " +
"where armor_id = :armor_id";
this.AUD(sql, 2);
this.resetAll();
}
}
private void Add_btn_Click(object sender, RoutedEventArgs e)
{
String sql = "select count(*) from specialization where spec_id=:spec_id";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("spec_id", OracleDbType.NChar).Value = spec_id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow > 0)
{
String msg = "specialization_Id已被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
sql = "insert into specialization(spec_id,spec_name,hp_weighted,mp_weighted,phy_damage_weighted,magic_damage_weighted,phy_defense_weighted,magic_defense_weighted,weapon_class_id) " +
"values( :spec_id, :spec_name, :hp_weighted, :mp_weighted, :phy_damage_weighted, :magic_damage_weighted, :phy_defense_weighted, :magic_defense_weighted, :weapon_class_id)";
this.AUD(sql, 0);
add_btn.IsEnabled = false;
update_btn.IsEnabled = true;
delete_btn.IsEnabled = true;
}
private void Add_btn_Click(object sender, RoutedEventArgs e)
{
sql = "select count(*) from player_character where player_id=:playerid";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("playerid", OracleDbType.NChar).Value = Player_Id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow > 0)
{
String msg = "player_id已被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
sql = "insert into player_character(player_id,nickname,player_level,spec_id,weapon_id,armor_id)" +
"values(:player_id,:nickname,:player_level,:spec_id,:weapon_id,:armor_id)";
this.AUD(sql, 0);
add_btn.IsEnabled = false;
update_btn.IsEnabled = true;
delete_btn.IsEnabled = true;
}
private void Update_btn_Click(object sender, RoutedEventArgs e)
{
sql = "select count(*) from specialization where spec_id=:spec_id";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("spec_id", OracleDbType.NChar).Value = spec_id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow == 0)
{
String msg = "此specialization_Id未被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
else
{
sql = "update specialization set spec_name = :spec_name ," +
" hp_weighted= :hp_weighted , mp_weighted = :mp_weighted , " + " phy_damage_weighted = :phy_damage_weighted, magic_damage_weighted = :magic_damage_weighted, phy_defense_weighted = :phy_defense_weighted,magic_defense_weighted = :magic_defense_weighted,weapon_class_id = :weapon_class_id " +
" where spec_id = :spec_id ";
this.AUD(sql, 1);
}
}
private void Search_btn_Click(object sender, RoutedEventArgs e)
{
sql = "select count(*) from armor where armor_id=:armor_id";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("armor_id", OracleDbType.NChar).Value = armor_id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow == 0)
{
String msg = "此armor_id未被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
else
{
sql = "select * from armor where armor_id=:armor_id";
cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("armor_id", OracleDbType.NChar).Value = armor_id_txbx.Text.ToString();
OracleDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
myDataGrid.ItemsSource = dt.DefaultView;
dr.Close();
MessageBox.Show("search ok");
}
}
private void Add_btn_Click(object sender, RoutedEventArgs e)
{
String sql = "select count(*) from player_level where player_level=:player_level";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("player_level", OracleDbType.NChar).Value = player_level_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow > 0)
{
String msg = "armor_id已被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
sql = "insert into player_level(player_level,hp_base,mp_base,pda_base,mda_base,pde_base,mde_base)" +
"values(:player_level,:hp_base,:mp_base,:pda_base,:mda_base,:pde_base,:mde_base)";
this.AUD(sql, 0);
add_btn.IsEnabled = false;
update_btn.IsEnabled = true;
delete_btn.IsEnabled = true;
}
private void Update_btn_Click(object sender, RoutedEventArgs e)
{
sql = "select count(*) from weapon where weapon_id=:weapon_id";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("weapon_id", OracleDbType.NChar).Value = weapon_id_txbx.Text.ToString();
int datarow = Convert.ToInt32(cmd.ExecuteScalar());
if (datarow == 0)
{
String msg = "此weapon_id未被使用";
MessageBox.Show(msg);
cmd.Cancel();
return;
}
else
{
sql = "update weapon set weapon_type_id = :weapon_type_id ," +
" weapon_name= :weapon_name , atk = :atk " +
"where weapon_id = :weapon_id";
this.AUD(sql, 1);
}
}
// 입력
public int thrm_fam_add(String fam_empno, String fam_rel, String fam_name, DateTime fam_bth, String fam_ltg, String datasys1, String datasys2, String datasys3)
{
int check = 1;
try
{
if (_DB.GetConnection() == true)
{
OracleCommand comm = new OracleCommand();
comm.Connection = _DB.Connection;
comm.CommandText = @"INSERT INTO THRM_FAM_HWY values(:empno, :resno, :name, :cname, :ename, :datasys1, :datasys2, :datasys3)";
comm.Parameters.Add("empno", fam_empno);
comm.Parameters.Add("resno", fam_rel);
comm.Parameters.Add("name", fam_name);
comm.Parameters.Add("cname", fam_bth.ToString("yyyyMMdd"));
comm.Parameters.Add("ename", fam_ltg);
comm.Parameters.Add("datasys1", datasys1);
comm.Parameters.Add("datasys2", datasys2);
comm.Parameters.Add("datasys3", datasys3);
comm.Prepare();
var a = comm.ExecuteNonQuery();
Console.WriteLine(a + " row insert");
comm.Cancel();
comm.Dispose();
check = 0;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(check);
}
/** Editare Angajat **/
private void buttonEditareAng_Click(object sender, EventArgs e)
{
int buffer = 3079;
int salariu = int.Parse(textBoxSalariu.Text);
int idDepartament = comboBoxDepartament.SelectedIndex;
if (salariu <= 0)
{
MessageBox.Show("[Log] Salariu nu poate fi 0 sau o valoare negativa!");
}
else
{
if (idDepartament == -1)
{
MessageBox.Show("[Eroare] Va rugam sa selectati un departament.");
}
else
{
string departamentName = comboBoxDepartament.Items[idDepartament].ToString();
idDepartament = departamentMapping.FirstOrDefault(x => x.Value == departamentName).Key;
OracleCommand command = MyOracleConnection.con.CreateCommand();
try
{
command.CommandText = "userDBA.ADMIN_PACKAGE.FUNC_EDITEAZA_ANGAJAT";
command.Parameters.Add("flag", OracleDbType.Int32, buffer);
command.Parameters["flag"].Direction = ParameterDirection.ReturnValue;
// asigneaza valori parametrilor
command.Parameters.Add(new OracleParameter("id_angajat", OracleDbType.Int32)).Value = angID;
command.Parameters.Add(new OracleParameter("salariu_angajat", OracleDbType.Int32)).Value = salariu;
command.Parameters.Add(new OracleParameter("id_dept", OracleDbType.Int32)).Value = idDepartament;
// executa funtia
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
string flag_insert = Convert.ToString(command.Parameters["flag"].Value);
// verifica ce returneaza functia pl/sql
switch (flag_insert)
{
case "0":
{
MessageBox.Show("[Log] Editare angajat cu succes!");
break;
}
}
command.Cancel();
}
catch (Exception exc)
{
MessageBox.Show("[Log - Exception] " + exc);
}
}
}
}
private void Disconnect()
{
if (_con != null && _con.State == ConnectionState.Open)
{
_cmd.Cancel();
_con.Close();
}
}
public override void ExecuteNoQuery(string sql, DBOParameterCollection dbp)
{
if (_s == DBStatus.Close)
{
DoOpen();
}
OracleCommand cmd;
DataSetStd ds = new DataSetStd();
var sqlarr = ToSQLArray(sql);
foreach (var s in sqlarr)
{
if (s.Trim() == "")
{
continue;
}
using (cmd = new OracleCommand(s, conn))
{
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
try
{
//如果事務開啟，則使用事務的方式
if (this._s == DBStatus.Begin_Trans)
{
cmd.Transaction = this.tran;
}
cmd.CommandText = s;
//如果有參數
if (dbp != null)
{
FillParametersToCommand(cmd, dbp, s);
}
if (_s == DBStatus.Begin_Trans)
{
cmd.Transaction = this.tran;
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Cancel();
cmd = null;
}
}
}
}
/// <summary>
/// 执行SQL，返回DataTable
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="startRow">起始行</param>
/// <param name="endRow">终止行</param>
/// <returns></returns>
public DataTable ExecuteTable(string sql, int startRow, int endRow)
{
OracleConnection myConnection = null;
OracleCommand myORACCommand = null;
try
{
myConnection = new OracleConnection(connectionString);
myConnection.Open();
myORACCommand = myConnection.CreateCommand();
myORACCommand.CommandText = sql;
OracleDataReader dataReader = myORACCommand.ExecuteReader();
DataTable datatable = new DataTable();
//动态添加表的数据列
for (int i = 0; i < dataReader.FieldCount; i++)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = dataReader.GetFieldType(i);
myDataColumn.ColumnName = dataReader.GetName(i);
datatable.Columns.Add(myDataColumn);
}
int rowIndex = 0;
///添加表的数据
while (dataReader.Read())
{
rowIndex++;
if (rowIndex < startRow || rowIndex > endRow)
{
continue;
}
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < dataReader.FieldCount; i++)
{
myDataRow[i] = dataReader[i];
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
///关闭数据读取器
dataReader.Close();
myORACCommand.Cancel();
myConnection.Close();
datatable.TableName = "table";
return(datatable);
}
catch (Exception ex)
{
///抛出类型转换错误
//SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
}
// <Snippet1>
public void CreateOracleCommand(string queryString, string connectionString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand command = new OracleCommand(queryString, connection);
command.Connection.Open();
command.ExecuteReader();
command.Cancel();
}
}
public void DoWork()
{
using (OracleConnection conn = new OracleConnection(GlobalUtils.TopSecret.OracleCS))
using (OracleCommand command = new OracleCommand())
{
try
{
conn.Open();
//conn.StatisticsEnabled = true;
command.Connection = conn;
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
return;
}
try
{
using (OracleTransaction sqlTran = conn.BeginTransaction())
{
command.Transaction = sqlTran;
OracleDataReader reader;
List<string> commands = GetCommands(com);
foreach (string c in commands)
{
command.CommandText = c;
using (reader = command.ExecuteReader())
{
ShowResultSet(reader, command);
while (reader.NextResult())
ShowResultSet(reader, command);
}
}
//string out_string;
//int status = 0;
//command.CommandText = "BEGIN DBMS_OUTPUT.GET_LINE (:out_string, :status); END;";
//command.CommandType = CommandType.Text;
//command.Parameters.Clear();
//command.Parameters.Add("out_string", OracleDbType.Varchar2, 32000);
//command.Parameters.Add("status", OracleDbType.Double);
//command.Parameters[0].Direction = System.Data.ParameterDirection.Output;
//command.Parameters[1].Direction = System.Data.ParameterDirection.Output;
//command.ExecuteNonQuery();
//out_string = command.Parameters[0].Value.ToString();
//status = int.Parse(command.Parameters[1].Value.ToString());
//if (!string.IsNullOrEmpty(out_string))
//{
// Console.WriteLine(System.Web.HttpUtility.HtmlEncode(out_string));
//}
//var stats = conn.RetrieveStatistics();
//using (TextWriter tw = new StreamWriter(path + ".stats"))
//{
// tw.WriteLine("Execution time: {0} sec, rows selected: {1}, rows affected: {2}",
// Math.Round((double)(long)stats["ExecutionTime"] / 1000, 2),
// stats["SelectRows"],
// stats["IduRows"]);
//}
}
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
if (command != null)
command.Cancel();
}
}
}