public void run()
{
OracleDataAdapter oleDBda = new OracleDataAdapter();
oleDBda.SelectCommand = new OracleCommand("",new OracleConnection());
base.DataAdapter_MissingMappingAction((DbDataAdapter)oleDBda);
}
protected void showDonVi()
{
try
{
string user = "******";
string dataSource = showSiteName();
if (dataSource.ToUpper() == "BACGIANG")
user = "******";
string txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={1}; Unicode=True", dataSource, user);
if (dataSource.ToUpper() == "NGHEAN")
txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={2}; Unicode=True", dataSource, "CMIS01PC1", "PC1CMIS01");
if (dataSource.ToUpper() == "NINHBINH")
txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={2}; Unicode=True", dataSource, "CMIS01PC1", "pc1cmis01");
OracleConnection ora_con = new OracleConnection(txt_cmd);
txt_cmd = "select MA_DVIQLY, TEN_DVIQLY from cmis01.d_dvi_qly where CAP_DVI=3 order by MA_DVIQLY";
OracleDataAdapter ora_da = new OracleDataAdapter(txt_cmd, ora_con);
DataSet ds = new DataSet();
ora_con.Open();
ora_da.Fill(ds);
ora_con.Close();
ddDonViQL.DataSource = ds;
ddDonViQL.DataBind();
}
catch (Exception ex) { lbError.Text = ex.Message; }
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
string strConn = "Data Source=orcl;User Id=chatroom;Password=chatroom;Integrated Security=no;";
OracleConnection oleDbConnection = new OracleConnection(strConn);
oleDbConnection.Open();
string strSql;
if (this.name.Text.ToString() == "")
{
strSql = "select * from scott.ChatRoomLog order by id desc";
}
else
{
strSql = "select * from scott.chatroomlog where name='" + this.name.Text.ToString() + "' order by id";
}
System.Data.OracleClient.OracleCommand oleDbCommand = new System.Data.OracleClient.OracleCommand();
oleDbCommand.CommandText = strSql;
oleDbCommand.Connection = oleDbConnection;
System.Data.OracleClient.OracleDataAdapter oleDbDataAdapter = new System.Data.OracleClient.OracleDataAdapter(oleDbCommand);
DataTable table = new DataTable();
oleDbDataAdapter.Fill(table);
oleDbConnection.Close();
this.GridView1.DataSource = table.DefaultView;
this.GridView1.DataBind();
}
catch
{
}
}
public Member AuthenticateUser(String username, String password)
{
conn.Open();
try
{
cmd = conn.CreateCommand();
string sql = "Select * from members where username = '******' AND account_password = '******'" ;
cmd = new OracleCommand(sql);
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
da = new OracleDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "ss");
if (ds.Tables["ss"].Rows[0].ItemArray[0].Equals(username) && ds.Tables["ss"].Rows[0].ItemArray[2].Equals(password))
{
member = createMember(ds);
}
return member;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return null;
}
finally
{
conn.Close();
}
}
/// <summary>
/// ���������ļ��������õ����ݿ�����
/// �ʹ�������������������Ӧ���ݿ�����������
/// </summary>
/// <returns></returns>
public static IDbDataAdapter CreateDataAdapter(IDbCommand cmd)
{
IDbDataAdapter adapter = null;
switch (AdoHelper.DbType)
{
case DatabaseType.SQLSERVER:
adapter = new SqlDataAdapter((SqlCommand)cmd);
break;
case DatabaseType.ORACLE:
adapter = new OracleDataAdapter((OracleCommand)cmd);
break;
case DatabaseType.MYSQL:
adapter = new MySqlDataAdapter((MySqlCommand)cmd);
break;
case DatabaseType.ACCESS:
adapter = new OleDbDataAdapter((OleDbCommand)cmd);
break;
case DatabaseType.SQLITE:
adapter = new SQLiteDataAdapter((SQLiteCommand)cmd);
break;
default: throw new Exception("���ݿ�����Ŀǰ��֧�֣�");
}
return adapter;
}
protected DataSet cmis_search(string ma_khang, string so_cto, out string txt_err)
{
DataSet ds = new DataSet();
try
{
string txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={1}; Unicode=True", "THAINGUYEN", "CMIS01PC1");
OracleConnection ora_con = new OracleConnection(txt_cmd);
txt_cmd = @"select TEN_KHANG, DCHI_KHANG, DCHI_TTOAN, KY, THANG, NAM, NGAY_DKY, NGAY_CKY, LOAI_HDON, TONG_TIEN, DIEN_TTHU, NGAY_PHANH
from cmis01.hdn_hdon
where MA_KHANG=:ma_khang or SO_CTO=:so_cto
order by NGAY_CKY desc";
OracleParameter[] pa = new OracleParameter[2];
pa[0] = new OracleParameter("ma_khang", ma_khang.ToUpper());
pa[1] = new OracleParameter("so_cto", so_cto);
OracleDataAdapter ora_da = new OracleDataAdapter(txt_cmd, ora_con);
ora_da.SelectCommand.CommandType = CommandType.Text;
ora_da.SelectCommand.Parameters.Add(pa[0]);
ora_da.SelectCommand.Parameters.Add(pa[1]);
ora_con.Open();
ora_da.Fill(ds);
ora_con.Close();
txt_err = "";
}
catch (Exception ex) { txt_err = ex.Message; }
return ds;
}
public void Finish()
{
_output = Output ?? "Output";
if (!Directory.Exists(_output))
{
Directory.CreateDirectory(_output);
}
Console.Write("输出路径：");
Console.WriteLine(_output);
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
using (OracleCommand cmd = new OracleCommand())
{
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
cmd.Connection = conn;
foreach (string tableName in TableNames)
{
CreateFile(tableName, cmd, da);
Console.WriteLine("成功创建文件:{0}.cs", tableName);
}
Console.WriteLine("操作成功，成功生成{0}个文件", TableNames.Length);
}
}
}
}
private void frmMain_Load(object sender, EventArgs e)
{
my_rpt objRpt;
// Creating object of our report.
objRpt = new my_rpt();
String ConnStr = "SERVER=myDb;USER ID=user1;PWD=user1";
OracleConnection myConnection = new OracleConnection(ConnStr);
String Query1 = "select a.PROJECT_ID,a.PROJECT_NAME,b.GROUP_NAME from tbl_project a,tbl_project_group b where a.group_code= b.group_code";
OracleDataAdapter adapter = new OracleDataAdapter(Query1, ConnStr);
DataSet Ds = new DataSet();
// here my_dt is the name of the DataTable which we created in the designer view.
adapter.Fill(Ds, "my_dt");
if (Ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("No data Found", "CrystalReportWithOracle");
return;
}
// Setting data source of our report object
objRpt.SetDataSource(Ds);
CrystalDecisions.CrystalReports.Engine.TextObject root;
root = (CrystalDecisions.CrystalReports.Engine.TextObject)objRpt.ReportDefinition.ReportObjects["txt_header"];
root.Text = "Sample Report By Using Data Table!!";
// Binding the crystalReportViewer with our report object.
crystalReportViewer1.ReportSource = objRpt;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
OracleConnection OrclConn = new OracleConnection(DataAccess.OIDSConnStr);
OrclConn.Open();
OracleDataAdapter OrclNestAdapter = new OracleDataAdapter();
OracleCommand OrclNestCmd = OrclConn.CreateCommand();
OracleCommand OrclCmd = OrclConn.CreateCommand();
//OracleCommandBuilder myCommandBuilder = new OracleCommandBuilder(mOrclAdapter);
//mOrclAdapter.Update(MyDataSet);
//conn.Close();
//for each row and each spool , insert into single_inventory_table
for (int i = 0; i < InventoryTable.Rows.Count; i++)
{
string pipetype = InventoryTable.Rows[i]["类型"].ToString();
Int32 pipelength = Convert.ToInt32(InventoryTable.Rows[i]["长度"].ToString());
string erpcode = InventoryTable.Rows[i]["ERP编码"].ToString();
double weight = (Convert.ToDouble(InventoryTable.Rows[i]["重量"].ToString())) / (Convert.ToInt32(InventoryTable.Rows[i]["数量"].ToString()));
string batch_no = InventoryTable.Rows[i]["炉批号"].ToString();
for (int j = 0; j < Convert.ToInt32(InventoryTable.Rows[i]["数量"].ToString()); j++)
{
//insert into oracle
OrclCmd.CommandText = "INSERT INTO pipe_inventory_single_spool (PIPETYPE,PIPELENGTH,ERPCODE,WEIGHT,BATCH_NO,STATE) VALUES ('" + pipetype + "'," + pipelength + ",'" + erpcode + "'," + weight + ",'" + batch_no + "',1)";
OrclCmd.ExecuteNonQuery();
}
}
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
MessageBox.Show("更新成功!");
}
public static void BuscaDGV(DataGridView dgv, String nome)
{
OracleDataAdapter da = new OracleDataAdapter("SELECT f.funcionarioid, f.nome, c.nome cargo, f.numero, f.ativo FROM funcionarios f JOIN cargos c USING (cargoid) WHERE f.nome LIKE '" + nome + "%'", ConexaoOracle.ObterConexao());
DataTable dt = new DataTable();
da.Fill(dt);
dgv.DataSource = dt;
}
public static void BuscaDGV(DataGridView dgv, String nome)
{
OracleDataAdapter da = new OracleDataAdapter("SELECT bairroid, nome, valor FROM bairros WHERE nome LIKE '" + nome + "%'", ConexaoOracle.ObterConexao());
DataTable dt = new DataTable();
da.Fill(dt);
dgv.DataSource = dt;
}
public static bool DeckIsRefD(int Deck_id)
{
try
{
OracleConnection OraCon = new OracleConnection("Data Source=oids;User ID=plm;Password=123!feed;Unicode=True");
OraCon.Open();
OracleDataAdapter OrclPrjAdapter = new OracleDataAdapter();
OracleCommand OrclPrjCmd = OraCon.CreateCommand();
OrclPrjAdapter.SelectCommand = OrclPrjCmd;
OrclPrjCmd.CommandText = @"SELECT T.TABLE_NAME,T.COLUMN_NAME FROM use_deckid_tables_view T";
DataSet Mydata = new DataSet();
OrclPrjAdapter.Fill(Mydata);
DataSet tmpData = new DataSet();
string QueryPrjIdCmdStr = string.Empty;
for (int i = 0; i < Mydata.Tables[0].Rows.Count; i++)
{
QueryPrjIdCmdStr = @"SELECT T." + Mydata.Tables[0].Rows[i][1] + " FROM " + Mydata.Tables[0].Rows[i][0] + " T WHERE TO_CHAR(T." + Mydata.Tables[0].Rows[i][1] + ")=TO_CHAR(" + Deck_id + ")";
OrclPrjCmd.CommandText = QueryPrjIdCmdStr;
OrclPrjAdapter.Fill(tmpData);
if (tmpData.Tables[0].Rows.Count > 0)
return true;
}
return false;
}
catch (Exception err)
{
System.Console.WriteLine(err.Message);
return true;
}
}
protected void ddlCampus_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
using (OracleConnection sqlConn = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
using (OracleCommand sqlCmd = new OracleCommand())
{
sqlCmd.CommandText = "select * from TB_FACULTY where CAMPUS_ID = " + ddlCampus.SelectedValue;
sqlCmd.Connection = sqlConn;
sqlConn.Open();
OracleDataAdapter da = new OracleDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
ddlFaculty.DataSource = dt;
ddlFaculty.DataValueField = "FACULTY_ID";
ddlFaculty.DataTextField = "FACULTY_NAME";
ddlFaculty.DataBind();
sqlConn.Close();
ddlFaculty.Items.Insert(0, new ListItem("--กรุณาเลือกสำนัก / สถาบัน / คณะ--", "0"));
ddlDivision.Items.Clear();
ddlDivision.Items.Insert(0, new ListItem("--กรุณาเลือกกอง / สำนักงานเลขา / ภาควิชา--", "0"));
ddlWorkDivision.Items.Clear();
ddlWorkDivision.Items.Insert(0, new ListItem("--กรุณาเลือกงาน / ฝ่าย--", "0"));
}
}
}
catch { }
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("listar_flujo_aprobado");
comando.Connection = ora2;
comando.Connection = ora2;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tblFlujo.DataSource = dt;
tblFlujo.DataBind();
ora2.Close();
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
}
}
public static DbDataAdapter CreateDataAdapter(DbProviderType DbProviderType, IDbCommand command, IDbConnection connection)
{
switch (DbProviderType)
{
case DbProviderType.SQLSERVER:
SqlDataAdapter sqlda = new SqlDataAdapter();
sqlda.SelectCommand = (SqlCommand) command;
command.Connection = connection;
return sqlda;
case DbProviderType.ORACLE:
OracleDataAdapter orada = new OracleDataAdapter();
orada.SelectCommand = (OracleCommand) command;
command.Connection = connection;
return orada;
// case DbProviderType.MYSQL:
// MySqlDataAdapter mysqlda = new MySqlDataAdapter();
// mysqlda.SelectCommand = (MySqlCommand) command;
// command.Connection = connection;
// return mysqlda;
default:
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = (OleDbCommand) command;
command.Connection = connection;
return oleda;
}
}
public DataTable executeDataTable(IDbTransaction transaction, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
{
try
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
//create the DataAdapter & DataTable
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataTable dt = new DataTable();
//fill the DataTable using default values for DataTable names, etc.
da.Fill(dt);
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
//return the datatable
return dt;
}
catch (Exception ex)
{
throw (new Exception(string.Concat(ex.Message.ToString(), "\r\nExecuted SQL Statement:\r\n", commandText.ToString()), ex));
}
}
/// <summary>
/// ִ�в�ѯ��䣬����DataSet
/// </summary>
/// <param name="SQLString">��ѯ���</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
if (SQLString != null && SQLString.Trim() != "")
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.OracleClient.OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
return ds;
}
}
else
{
return null;
}
}
[Test] // ctor (OracleCommand)
public void Constructor2 ()
{
OracleCommand cmd = new OracleCommand ();
OracleDataAdapter da = new OracleDataAdapter (cmd);
Assert.IsTrue (da.AcceptChangesDuringFill, "#1");
#if NET_2_0
Assert.IsTrue (da.AcceptChangesDuringUpdate, "#2");
#endif
Assert.IsNull (da.Container, "#3");
Assert.IsFalse (da.ContinueUpdateOnError, "#4");
Assert.IsNull (da.DeleteCommand, "#5");
#if NET_2_0
Assert.AreEqual (LoadOption.OverwriteChanges, da.FillLoadOption, "#6");
#endif
Assert.IsNull (da.InsertCommand, "#7");
Assert.AreEqual (MissingMappingAction.Passthrough, da.MissingMappingAction, "#8");
Assert.AreEqual (MissingSchemaAction.Add, da.MissingSchemaAction, "#9");
#if NET_2_0
Assert.IsFalse (da.ReturnProviderSpecificTypes, "#10");
#endif
Assert.IsNotNull (da.SelectCommand, "#11");
Assert.AreSame (cmd, da.SelectCommand, "#12");
Assert.IsNull (da.Site, "#13");
Assert.IsNotNull (da.TableMappings, "#14");
Assert.AreEqual (0, da.TableMappings.Count, "#15");
#if NET_2_0
Assert.AreEqual (1, da.UpdateBatchSize, "#16");
#endif
Assert.IsNull (da.UpdateCommand, "#17");
}
public void run()
{
OracleDataAdapter oleDBda = new OracleDataAdapter();
oleDBda.SelectCommand = new OracleCommand("",new OracleConnection());
base.DbDataAdapter_FillError((DbDataAdapter)oleDBda);
}
private void EditStudentFrm_Load(object sender, EventArgs e)
{
//Author: Niall Stack - t00174406
string CloudDB = "Data Source=cp3dbinstance.c4pxnpz4ojk8.us-east-1.rds.amazonaws.com:1521/cp3db;User Id=sw4;Password=sw4;";
try
{
OracleConnection conn = new OracleConnection(CloudDB);
OracleCommand cmd = new OracleCommand("SELECT * FROM Students", conn);
cmd.CommandType = CommandType.Text;
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ss");
studGrd.DataSource = ds.Tables["ss"];
conn.Close();
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
}
public DataSet ReadDataToDataSet(OracleConnection conn, String SQL, string diff)
{
OracleDataAdapter adapter = new OracleDataAdapter(SQL, conn);
DataSet testDataSet = new DataSet();
adapter.Fill(testDataSet);
return testDataSet;
}
private void button2_Click(object sender, EventArgs e)
{
oracle.LinkToOracle("orcl", "hui", "oracle");
OracleCommand cmd = oracle.getConnection().CreateCommand();
if (radioButton1.Checked)
{
OracleDataAdapter sda = new OracleDataAdapter("select * from department", oracle.getConnection());
DataTable query = new DataTable();
query = (DataTable)dataGridView1.DataSource;
OracleCommandBuilder sb = new OracleCommandBuilder(sda);
sda.Update(query);
query.Clear();
sda.Fill(query);
}
if (radioButton2.Checked)
{
OracleDataAdapter sda = new OracleDataAdapter("select * from teacher", oracle.getConnection());
DataTable query = new DataTable();
query = (DataTable)dataGridView1.DataSource;
OracleCommandBuilder sb = new OracleCommandBuilder(sda);
sda.Update(query);
query.Clear();
sda.Fill(query);
}
oracle.LinkClose();
cmd.Dispose();
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public override DataSet ExecuteDataSet(string SQLString, params IDataParameter[] cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (OracleException e)
{
//LogManage.OutputErrLog(e, new Object[] { SQLString, cmdParms });
throw;
}
finally
{
cmd.Dispose();
connection.Close();
}
return ds;
}
}
}
private void stdnt_payment_info_btn_Click(object sender, EventArgs e)
{
dbConnect conObj = new dbConnect();
OracleConnection conn = conObj.connFunc();
OracleParameter parOut = new OracleParameter();
parOut.ParameterName = "res_cur";
parOut.OracleType = OracleType.Cursor;
parOut.Size = 32;
parOut.Direction = System.Data.ParameterDirection.Output;
OracleCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "student_payment_procedure";
cmd.Parameters.Add(parOut);
conn.Open();
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
DataSet myset = new DataSet("res_cur");
adapter.Fill(myset);
stdnt_payment_dataGridView.DataSource = myset;
stdnt_payment_dataGridView.DataSource = myset.Tables[0];
conn.Close();
}
public DataSet GetDateSet(string sqlstring)
{
OracleDataAdapter sda = new OracleDataAdapter(sqlstring, conn);
DataSet ds = new DataSet();
sda.Fill(ds, "ds");
return ds;
}
public static DataTable getTable(string strSql)
{
if (MsgBox != null) MsgBox.Text = strSql + System.Environment.NewLine + MsgBox.Text;
DataTable dt = new DataTable();
try
{
Connect();
OracleCommand orcCmd = myConnection.CreateCommand();//触发条件表
orcCmd.CommandText = strSql;//根据公司ID查询触发条件表
OracleDataAdapter orcDap = new OracleDataAdapter(orcCmd);//主表
orcDap.Fill(dt);
}
catch (Exception ex)
{
Console.Write(ex.Message);
if (MsgBox != null) MsgBox.Text = "执行sql异常：" + ex.ToString() + System.Environment.NewLine + MsgBox.Text;
throw ex;
}
finally
{
close();
}
return dt;
}
/// <summary>
/// execute a query£¬return DataSet
/// </summary>
/// <param name="SQLString"></param>
/// <returns>DataSet</returns>
public static DataSet Query(string connectionString, string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
return ds;
}
}
public static void BuscaDGV(DataGridView dgv)
{
OracleDataAdapter da = new OracleDataAdapter("select p.pedidoid, c.nome, f.nome, p.valortotal, p.valorpago, p.formapagamento, p.data, p.desconto, p.status, p.cancelado, p.entregaid from funcionarios f join pedidos p using (funcionarioid) join clientes c using (clienteid) order by pedidoid; ", ConexaoOracle.ObterConexao());
DataTable dt = new DataTable();
da.Fill(dt);
dgv.DataSource = dt;
}
/// <summary>
/// execute a query£¬return DataSet
/// </summary>
/// <param name="SQLString"></param>
/// <returns>DataSet</returns>
public static DataSet Query(string connectionString, CommandType cmdType, string SQLString, params OracleParameter[] cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, null, cmdType, SQLString, cmdParms);
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
DataSet ds = new DataSet();
//try
//{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
//}
//catch (System.Data.OracleClient.OracleException ex)
//{
// throw new Exception(ex.Message);
//}
//finally
//{
// if (connection.State != ConnectionState.Closed)
// {
// connection.Close();
// }
//}
return ds;
}
}
}
//Filling the booking details into the return cars datagridview
public void bookingsDetails(DataGridView grd_Bookings)
{
var tomorrow = DateTime.Today.AddDays(1).ToString("dd-MMM-yyyy");
var dueDate = DateTime.Today.AddDays(-5).ToString("dd-MMM-yyyy");
string query_String = string.Format("SELECT DISTINCT B.Customer_Id ,Forename, Surname ,B.Car_Reg ,Cost ,Customer_Type,Return_Date FROM Cars CA, " +
"Customers C JOIN Bookings B ON C.Customer_Id = B.Customer_Id " +
"WHERE Car_Status = 'U' AND CA.Car_Reg = B.Car_Reg AND Return_Date BETWEEN '{0}' AND '{1}'", dueDate, tomorrow );
// MessageBox.Show(query_String);
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_Adapter = new OracleDataAdapter(cmd);
data_Set = new DataSet();
data_Adapter.Fill(data_Set, "Bookings");
grd_Bookings.DataSource = data_Set.Tables["Bookings"];
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
data_reader.Read();
connection.Close();
}
catch (Exception ex) {MessageBox.Show("" + ex);}
}
private void button1_Click( object sender, EventArgs e )
{
try
{
OracleConnection conn = ConnectionOracle.creaConnection();
string str ="";
if (radioButton1.Checked)
{
str = "select * from book where bname='" + textBox1.Text + "'";
}
if (radioButton2.Checked)
{
str = "select * from book where auther = '" + textBox1.Text + "'";
}
if (radioButton3.Checked)
{
str = "select * from book where pkey='"+textBox1.Text+"' or pkey1='"+textBox1.Text+"' or pkey2='"+textBox1.Text+"'";
}
OracleCommand cmd = conn.CreateCommand();
OracleDataAdapter da = new OracleDataAdapter(str,conn);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource =
ds.Tables[0];
conn.Open();
}
catch (Exception e1)
{
MessageBox.Show(e1.Message);
throw;
}
}
public void CargarQuerys()
{
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_devueltas2");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando3;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tablasTareasDevueltas.DataSource = dt;
tablasTareasDevueltas.DataBind();
if (tablasTareasDevueltas.Rows.Count == 0)
{
// Response.Write("<script>('No hay tareas pendientes')</script>");
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>");
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_y_flujo");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_correo", OracleType.VarChar).Value = txtCorreoEncargado_2.Text;
comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando3;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tablaTareas.DataSource = dt;
tablaTareas.DataBind();
if (tablaTareas.Rows.Count == 0)
{
Response.Write("<script>('No hay tareas pendientes')</script>");
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>");
}
}
/// <summary>
/// 根据sql语句，指定其中若干条记录生成数据表
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <param name="StartRecord">从其开始的从零开始的记录号</param>
/// <param name="MaxRecord">要检索的最大记录数</param>
/// <returns>数据表，如果sql语句执行异常，返回null</returns>
public DataTable GetDtBySql(string sqlstr, int StartRecord, int MaxRecord)
{
DataAdapter adapter = new DataAdapter(sqlstr, mConn);
DataSet ds = new DataSet();
try
{
adapter.Fill(ds, StartRecord, MaxRecord, "srcTable");
return(ds.Tables["srcTable"]);
}
catch (Exception e)
{
WriteLogInfo("error on " + this.GetType().Name + " GetDtBySql:" + sqlstr + "\r\n" + e.Message);
return(null);
}
}
/// <summary>
/// 根据sql语句生成数据表
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <returns>数据表，如果sql语句执行异常，返回null</returns>
public DataTable GetDtBySql(string sqlstr)
{
DataAdapter adapter = new DataAdapter(sqlstr, mConn);
DataTable dt = new DataTable();
try
{
adapter.Fill(dt);
return(dt);
}
catch (Exception e)
{
WriteLogInfo("error on " + this.GetType().Name + " GetDtBySql:" + sqlstr + "\r\n" + e.Message);
return(null);
}
}
public DataSet GetData(string strSql, out System.Data.OracleClient.OracleDataAdapter adapter)
{
/// 增加 2007.7.27
/// -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
try
{
/// -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/// 增加 20090903
/// ===================================================================================================================
if (this._cmd == null)
{
adapter = null;
return(null);
}
if (this._conn == null || this._conn.State == ConnectionState.Closed)
{
this.OpenConnection();
}
/// ===================================================================================================================
DataSet ds = new DataSet();
_cmd.CommandText = strSql;
adapter = new OracleDataAdapter();
adapter.SelectCommand = _cmd;
adapter.Fill(ds);
return(ds);
/// 增加 2007.7.27
/// -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
}
catch (Exception ex)
{
log.ErrorFormat("oracle数据错误:{0}", ex.Message);
adapter = null;
return(null);
}
finally
{
this.CloseConnection();
}
/// -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
}
public DataSet GetDataSet1(string sql, ref System.Data.OracleClient.OracleDataAdapter sda)
{
try
{
DataSet ds = new DataSet();
this.Open();
IDbCommand com = DBF.getDBCommand();
com.CommandText = sql;
com.Connection = DBF.getDBConnection();
sda = (System.Data.OracleClient.OracleDataAdapter)DBF.getDataAdapter(com);
sda.Fill(ds);
this.Close();
return(ds);
}
catch (Exception ex)
{
MessageBox.Show("Error:" + sql + ex.Message);
return(null);
}
}
public int UpdateDataTable1(string sql, System.Data.DataTable dt, System.Data.OracleClient.OracleDataAdapter sda)
{
int i = 0;
try
{
System.Data.OracleClient.OracleCommandBuilder builder = new System.Data.OracleClient.OracleCommandBuilder(sda);
sda.UpdateCommand = builder.GetUpdateCommand();
if (dt.GetChanges() != null)
{
i = sda.Update(dt);
dt = GetDataTable(sql);
}
}
catch (Exception ex)
{
MessageBox.Show("Error:" + ex.Message.ToString());
return(-1);
}
return(i);
}
public Sdt ExecuteProcedure(String procedureName)
{
Sdt dt = new Sdt();
if (dbType == DbType.Oracle)
{
cmdOra.CommandText = procedureName;
cmdOra.CommandType = CommandType.StoredProcedure;
System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(cmdOra);
da.Fill(dt);
dt.SecoundConstructor();
}
if (dbType == DbType.MySQL)
{
cmd.CommandText = procedureName;
cmd.CommandType = CommandType.StoredProcedure;
MySqlDataAdapter da = new MySqlDataAdapter((MySqlCommand)cmd);
da.Fill(dt);
dt.SecoundConstructor();
}
return(dt);
}
protected void btnGuardarFlujo_Click(object sender, EventArgs e)
{
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("INSERT_flujo", ora2);
comando2.CommandType = System.Data.CommandType.StoredProcedure;
comando2.Parameters.Add("P_DESCRIPCION", OracleType.VarChar).Value = txtNombreFlujo.Text;
comando2.Parameters.Add("P_ESTADO", OracleType.VarChar).Value = "Pendiente de Aprobación";
comando2.ExecuteNonQuery();
ora2.Close();
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Flujo Agregado');</script>");
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("listar_flujo_aprobado");
comando.Connection = ora2;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tblFlujo.DataSource = dt;
tblFlujo.DataBind();
ora2.Close();
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
}
public static System.Data.DataTable ExecuteDataTable(ref System.Data.OracleClient.OracleCommand command, System.Data.OracleClient.OracleConnection connection, System.Data.CommandType commandType, string commandText)
{
System.Data.OracleClient.OracleDataAdapter oracleDataAdapter = new System.Data.OracleClient.OracleDataAdapter();
System.Data.DataSet dataSet = new System.Data.DataSet();
System.Data.DataTable dataTable = new System.Data.DataTable();
try
{
prepareCommand(ref command, connection, commandType, commandText);
oracleDataAdapter.SelectCommand = command;
oracleDataAdapter.Fill(dataSet);
if (dataSet != null && dataSet.Tables.Count > 0)
{
dataTable = dataSet.Tables[0];
}
}
catch (Exception ex)
{
// Handle the exception in UI level only
throw ex;
}
return(dataTable);
}
private OracleDataAdapter(OracleDataAdapter from) : base(from)
{
this._updateBatchSize = 1;
GC.SuppressFinalize(this);
}
//private Transaction CreateTransaction()
//{
// Transaction tr=mConn.BeginTransaction();
// tr.Commit();
//}
#endregion
#endregion
#region 公开方法：存贮过程相关
#region CallProc
public bool CallProc(IStoredProc objProc, Transaction tr)
{
#region 创建命令
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = objProc.GetProcName();
dbCommand.Transaction = tr;
#endregion
#region 参数赋值
#if DT_ORACLE
DbParameter[] dbparams = this.DeriveParameters(objProc.GetProcName(), false);
#elif DT_MSSQL
DbParameter[] dbparams = this.DeriveParameters(objProc.GetProcName(), true);
#elif DT_OLEDB
DbParameter[] dbparams = new DbParameter[0];
this.WriteLogInfo(this.GetType().Name + "OLEDB方式不支持取存贮过程参数");
return(false);
#endif
Hashtable htParam = objProc.GetFields();
foreach (DbParameter dbparam in dbparams)
{
if (dbparam.Direction == ParameterDirection.Input || dbparam.Direction == ParameterDirection.InputOutput)
{
dbparam.Value = htParam[dbparam.ParameterName.ToUpper()];
}
else
{
dbparam.Value = DBNull.Value;
}
dbCommand.Parameters.Add(dbparam);
}
#endregion
#region 执行
DataSet mDataSet = new DataSet();
using (DataAdapter mDataAdapter = new DataAdapter(dbCommand))
{
try
{
mDataAdapter.Fill(mDataSet);
}
catch (Exception e)
{
WriteLogInfo("error on " + this.GetType().Name + " " + objProc.GetProcName() + ".excute:\r\n" + e.Message);
return(false);
}
}
#endregion
#region 读取参数
foreach (DbParameter dbparam in dbparams)
{
if (dbparam.Direction == ParameterDirection.Output || dbparam.Direction == ParameterDirection.InputOutput)
{
htParam[dbparam.ParameterName.ToUpper()] = dbparam.Value;
}
}
if (objProc.GetCursorFieldList() != "")
{
string[] cursorFields = objProc.GetCursorFieldList().Split(new char[] { ',' });
if (mDataSet.Tables.Count != cursorFields.Length)
{
WriteLogInfo("error on " + this.GetType().Name + " " + objProc.GetProcName() + ".excute:\r\n 返回的游标数量与参数不匹配："
+ "\r\n\t游标参数:" + objProc.GetCursorFieldList()
+ "\r\n\t游标返回数:" + mDataSet.Tables.Count);
return(false);
}
int idx = 0;
foreach (string cursorField in cursorFields)
{
htParam[cursorField.ToUpper()] = mDataSet.Tables[idx++];
}
}
#endregion
return(true);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
lblidddl.Visible = false;
txtCorreo.Text = Session["Value"].ToString();
Array enumList1 = Enum.GetValues(typeof(tiposTarea));
foreach (tiposTarea getTipoTarea in enumList1)
{
ddlTipoTarea.Items.Add(new ListItem(getTipoTarea.ToString(), ((int)getTipoTarea).ToString()));
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_devueltas");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando3;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tablasTareasDevueltas.DataSource = dt;
tablasTareasDevueltas.DataBind();
if (tablasTareasDevueltas.Rows.Count == 0)
{
// Response.Write("<script>('No hay tareas pendientes')</script>");
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>");
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("listar_flujo_aprobado");
comando.Connection = ora2;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
System.Data.OracleClient.OracleDataReader rdr = comando.ExecuteReader();
while (rdr.Read())
{
ddlFlujo.Items.Add(rdr.GetString(0).ToString());
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>");
}
}
}
