internal static DataTable ExecuteTable(string sql, OracleTransaction trans) { OracleCommand cmd = new OracleCommand(); OracleDataAdapter adapter = new OracleDataAdapter(); adapter.SelectCommand = cmd; DataTable dt = new DataTable(); try { cmd.Connection = trans.Connection; cmd.Transaction = trans; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 240; adapter.Fill(dt); adapter.Dispose(); } catch (Exception ex) { dt = new DataTable(); } return dt; }
public void CheckSMSStatus(Object stateInfo) { try { var _url = ConfigurationManager.AppSettings["ServiceURL"]; var _user = ConfigurationManager.AppSettings["wsUser"]; var _pass = ConfigurationManager.AppSettings["wsPwd"]; var connstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; var ws = new LG.SMS.Service(); ws.Url = _url; var sql = " SELECT moseq " + ",shortcode " + ",cell_no " + ",msgbody " + ",msgtype " + ",mttotalseq " + ",mtseqref " + ",cpid" + " FROM tb_mt_hist " + " WHERE send_flag ='N' AND ROWNUM <=100"; var oradbConnection = new OracleConnection(connstring); var sqlcmd = new OracleCommand(sql, oradbConnection); var _dataAdapter = new OracleDataAdapter(); _dataAdapter.SelectCommand = sqlcmd; var _dataTable = new DataTable(); _dataAdapter.Fill(_dataTable); StringBuilder rtbLog = new StringBuilder(); sql = string.Empty; int count = (_dataTable.Rows == null ? 0 : _dataTable.Rows.Count); //Task[] tasks = new Task[] { }; //if (count > 0) Array.Resize(ref tasks, count); int index = 0; for (index = 0; index < count; index++ ) { int idtm = index; //Application.DoEvents(); // Dim _mt_send_datetime = Format(Date.Now, "MMddyyyyHHmmss") //tasks[idtm] = Task.Factory.StartNew(() => { DataRow _row = _dataTable.Rows[idtm]; System.Console.WriteLine("<==============================MT sending==============================>"); System.Console.WriteLine("MT started : " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + " to " + (_row["cell_no"] == null ? string.Empty : _row["cell_no"].ToString())); var _result = ws.SendMT((_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()), (_row["shortcode"] == null ? string.Empty : _row["shortcode"].ToString()), (_row["cell_no"] == null ? string.Empty : _row["cell_no"].ToString()), (_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()), (_row["msgtype"] == null ? string.Empty : _row["msgtype"].ToString()), (_row["mttotalseq"] == null ? string.Empty : _row["mttotalseq"].ToString()), (_row["mtseqref"] == null ? string.Empty : _row["mtseqref"].ToString()), (_row["cpid"] == null ? string.Empty : _row["cpid"].ToString()), "1", _user, _pass); //int _result = 200; byte attemp = 1; int mo_cnt = 0; do { var _mt_sent_datetime = DateTime.Now.ToString("MMddyyyyHHmmss"); if (attemp <= 3) { if (_result == 200) { sql = " UPDATE tb_mt_hist SET send_flag ='Y', " + " result ='" + _result.ToString() + "'," + " send_time ='" + _mt_sent_datetime + "', " + " finish_time ='" + _mt_sent_datetime + "' " + " WHERE moseq ='" + (_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()) + "'"; System.Console.WriteLine("Success :" + (_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()) + "=>" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")); break; } else { sql = " UPDATE tb_mt_hist SET result ='" + _result.ToString() + "', " + " send_time ='" + _mt_sent_datetime + "'" + " WHERE moseq ='" + (_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()) + "'"; System.Console.WriteLine("Fail : " + (_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()) + "=>" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")); attemp++; } } else { sql = " UPDATE tb_mt_hist SET send_flag ='Y', " + " result ='" + _result.ToString() + "'," + " send_time ='" + _mt_sent_datetime + "', " + " finish_time ='" + _mt_sent_datetime + "' " + " WHERE moseq ='" + (_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()) + "'"; System.Console.WriteLine("Fail : " + (_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()) + "=>" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")); break; } mo_cnt++; } while (attemp == 5); mo_cnt++; System.Console.WriteLine("...done, cnt : " + mo_cnt.ToString()); var oradbConnectionSub = new OracleConnection(connstring); oradbConnectionSub.Open(); using (var sqlcmdsub = new OracleCommand(sql, oradbConnectionSub)) { sqlcmdsub.CommandType = CommandType.Text; sqlcmdsub.ExecuteNonQuery(); } oradbConnectionSub.Close(); oradbConnectionSub.Dispose(); }//); } //Task.WaitAll(tasks); sqlcmd.Dispose(); _dataAdapter.Dispose(); _dataTable.Dispose(); oradbConnection.Close(); oradbConnection.Dispose(); } catch (Exception ex) { System.Console.WriteLine("<==========================MT sending errors===============================>"); System.Console.WriteLine(ex.Message + ", " + DateTime.Now.ToString("yyyyMMdd HH:mm:ss")); } string stt = smsindex.ToString(); if (smsindex % 10 == 1) stt += "ST"; else if (smsindex % 10 == 2) stt += "ND"; else if (smsindex % 10 == 3) stt += "RD"; else stt += "TH"; System.Console.WriteLine("---------------------------------------------------------------------------"); System.Console.WriteLine("---------------------- END SMS CONSOLE APPLICATION AT " + stt.PadRight(21, '-')); System.Console.WriteLine("---------------------------------------------------------------------------"); System.Console.WriteLine("\n\n"); smsindex++; //reset index if (smsindex > 1000000) smsindex = 0; System.Console.Read(); }
private void PopulateDataViews() { OracleConnection cnn = new OracleConnection("Server=STUDREC;User ID=XCRI;Password=XCRI;"); cnn.Open(); OracleCommand cmdMasterCourses = new OracleCommand("SELECT * FROM UNITE.IS_XCRI_MASTER_COURSE",cnn); OracleDataAdapter daMasterCourses = new OracleDataAdapter(cmdMasterCourses); OracleCommand cmdCourseOfferings = new OracleCommand("SELECT * FROM UNITE.IS_XCRI_COURSEOFFERING",cnn); OracleDataAdapter daCourseOfferings = new OracleDataAdapter(cmdCourseOfferings); OracleCommand cmdQualifications = new OracleCommand("SELECT * FROM UNITE.IS_XCRI_QUALIFICATION",cnn); OracleDataAdapter daQualifications = new OracleDataAdapter(cmdQualifications); OracleCommand cmdCredits = new OracleCommand("SELECT * FROM UNITE.IS_XCRI_CREDITS",cnn); OracleDataAdapter daCredits = new OracleDataAdapter(cmdCredits); OracleCommand cmdVenues = new OracleCommand("SELECT * FROM UNITE.IS_XCRI_COURSEVENUES",cnn); OracleDataAdapter daVenues = new OracleDataAdapter(cmdVenues); DataTable dtMasterCourses = new DataTable(); daMasterCourses.Fill(dtMasterCourses); daMasterCourses.Dispose(); DataTable dtCourseOfferings = new DataTable(); daCourseOfferings.Fill(dtCourseOfferings); daCourseOfferings.Dispose(); DataTable dtQualifications = new DataTable(); daQualifications.Fill(dtQualifications); daQualifications.Dispose(); DataTable dtCredits = new DataTable(); daCredits.Fill(dtCredits); daCredits.Dispose(); DataTable dtVenue = new DataTable(); daVenues.Fill(dtVenue); daVenues.Dispose(); _dvAllMasterCourses = new DataView(dtMasterCourses); _dvAllCourseOfferings = new DataView(dtCourseOfferings); _dvAllQualifications = new DataView(dtQualifications); _dvAllCredits = new DataView(dtCredits); _dvAllCourseVenues = new DataView(dtVenue); dtMasterCourses.Dispose(); dtCourseOfferings.Dispose(); dtQualifications.Dispose(); dtCredits.Dispose(); cnn.Close(); }
public static DataTable ExecuteTable(string sql) { OracleCommand cmd = new OracleCommand(); OracleDataAdapter adapter = new OracleDataAdapter(); adapter.SelectCommand = cmd; DataTable dt = new DataTable(); try { OracleConnection conn = GetConnection(); cmd.Connection = conn; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 240; adapter.Fill(dt); adapter.Dispose(); cmd.Dispose(); conn.Close(); } catch (Exception ex) { dt = new DataTable(); } return dt; }
public static DataTable ExecuteTable(string sql, OracleParameter[] param) { OracleCommand cmd = new OracleCommand(); OracleDataAdapter adapter = new OracleDataAdapter(); adapter.SelectCommand = cmd; DataTable dt = new DataTable(); try { OracleConnection conn = GetConnection(); cmd.Connection = conn; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 240; if (param != null) { foreach (OracleParameter p in param) { if (p != null) { cmd.Parameters.Add(p); } } } adapter.Fill(dt); adapter.Dispose(); cmd.Dispose(); conn.Close(); } catch (Exception ex) { dt = new DataTable(); } return dt; }
private string GetBody() { string ret = ""; OracleConnection zConn = ConnectServer(); if (zConn == null) { _error += "\näÁèÊÒÁÒöà¢éÒÊÙèÃкºä´é"; } else { string zTable = "SELECT column_name AS \"COLUMN_NAME\", data_type AS \"TYPE_NAME\" FROM user_tab_columns WHERE table_name = '" + _gendata.Table + "'"; OracleCommand zCmd = new OracleCommand(zTable, zConn); OracleDataAdapter zAdp = new OracleDataAdapter(); zAdp.SelectCommand = zCmd; DataTable zDt = new DataTable(); zAdp.Fill(zDt); zConn.Close(); zAdp.Dispose(); if (zDt.Rows.Count > 0) { ret = GetVariable(zDt); ret += GetQueryString(zDt); ret += GetInternalMethod(zDt); } } return ret; }
/**/ /// <summary> /// 与数据库建立连接 /// </summary> /// <param name="connectString">数据库连接字符串</param> /// <returns>返回类型</returns> public override bool Connect(string connectString) { bool isConnect = false; if (m_Conn == null || m_Conn.State != ConnectionState.Open) { m_Conn = new OracleConnection(connectString); m_Conn.Open(); isConnect = true; } return isConnect; } /**/ /// <summary> /// 执行数据库查询语句 /// </summary> /// <param name="strSQL">结构化数据库查询语句</param> /// <returns>返回类型</returns> public override DataTable ExecuteSQL(string strSQL) { DataTable dt = new DataTable(); OracleCommand command = null; OracleDataAdapter da = null; try { command = new OracleCommand(strSQL, m_Conn); da = new OracleDataAdapter(); da.SelectCommand = command; da.Fill(dt); } catch (Exception e) { #if DEBUG System.Console.WriteLine(e.StackTrace.ToString()); #endif } finally { command.Dispose(); da.Dispose(); } return dt; }
//提交删除命令 public override string DeleteBySql(string strsql) { try { this.CheckConn(); OracleCommand cmdDel = new OracleCommand(); OracleDataAdapter daDel = new OracleDataAdapter(); int delrownum; cmdDel.Connection = MyConn; cmdDel.CommandText = strsql; daDel.DeleteCommand = cmdDel; delrownum = daDel.DeleteCommand.ExecuteNonQuery(); cmdDel.Dispose(); daDel.Dispose(); return ("ok"); } catch (Exception ex) { return (ex.Message); } }
//批量更新 public static bool MultiUpdateData(DataTable data, string Columns, string tableName) { //string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString(); string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString(); using (OracleConnection connection = new OracleConnection(connectionString)) { string SQLString = string.Format("select {0} from {1} where rownum=0", Columns, tableName); using (OracleCommand cmd = new OracleCommand(SQLString, connection)) { try { connection.Open(); OracleDataAdapter myDataAdapter = new OracleDataAdapter(); myDataAdapter.SelectCommand = new OracleCommand(SQLString, connection); OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter); custCB.ConflictOption = ConflictOption.OverwriteChanges; custCB.SetAllValues = true; foreach (DataRow dr in data.Rows) { if (dr.RowState == DataRowState.Unchanged) { dr.SetModified(); } } myDataAdapter.Update(data); data.AcceptChanges(); myDataAdapter.Dispose(); return true; } catch (System.Data.OracleClient.OracleException E) { connection.Close(); return false; } } } }
//批量插入 public static bool MultiInsertData(DataSet ds, string Columns, string tableName) { //string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString(); string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString(); using (OracleConnection connection = new OracleConnection(connectionString)) { string SQLString = string.Format("select {0} from {1} where rownum=0", Columns, tableName); using (OracleCommand cmd = new OracleCommand(SQLString, connection)) { try { connection.Open(); OracleDataAdapter myDataAdapter = new OracleDataAdapter(); myDataAdapter.SelectCommand = new OracleCommand(SQLString, connection); myDataAdapter.UpdateBatchSize = 0; OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter); DataTable dt = ds.Tables[0].Copy(); DataTable dtTemp = dt.Clone(); int times = 0; for (int count = 0; count < dt.Rows.Count; times++) { for (int i = 0; i < 400 && 400 * times + i < dt.Rows.Count; i++, count++) { dtTemp.Rows.Add(dt.Rows[count].ItemArray); } myDataAdapter.Update(dtTemp); dtTemp.Rows.Clear(); } dt.Dispose(); dtTemp.Dispose(); myDataAdapter.Dispose(); return true; } catch (System.Data.OracleClient.OracleException E) { connection.Close(); return false; } } } }
public bool TestConn(string tableName) { bool checkdt = true; OracleDataAdapter adapter = null ; try { conn = new OracleConnection(strconn); conn.Open(); //获取表结构,构造new_dt com = conn.CreateCommand(); com.CommandText = "select count(*) from "+tableName ; com.CommandType = CommandType.Text; //partnum runnum operator date time lotnumber quantity pumppress pumptime runtime finaltemp rundescrip archived datafile runaverage voltlog errors customdata gastypes /* * lotnumber: * */ DataTable dt = new DataTable(); adapter = new OracleDataAdapter(com); adapter.Fill(dt); //if (dt.Columns.Count == 6) if (dt.Columns.Count == 1) { /* string colnames = "V_LOT,V_STIME,V_ETIME,V_ETIME,V_EQU,V_VAR"; for (int i = 0; i < 6; i++) { if (!colnames.Contains(dt.Columns[i].ColumnName.Trim())) { checkdt = false; break; } } */ } else { checkdt = false; } } catch (Exception ex) { //暂不处理,抛给上层做处理 checkdt = false; throw (ex); } finally { conn.Close(); adapter.Dispose(); com.Dispose(); conn.Dispose(); } return checkdt; }
/// <summary> /// 功能:把dt表中的数据全部追加到Oracle数据中指定的表 /// 注意:要保证dt表的列与数据库中指定表默认检索的列的次序相同 /// </summary> /// <param name="strconn">链接字符串</param> /// <param name="dt">要追加的数据</param> /// <param name="tb_name">数据库中的表名</param> public void insertDB(DataTable dt, string tb_name) { try { conn = new OracleConnection(strconn); conn.Open(); //获取表结构,构造new_dt com = conn.CreateCommand(); com.CommandText = "select * from " + tb_name + " where 0=1"; ; com.CommandType = CommandType.Text; da = new OracleDataAdapter(com); DataTable new_dt = new DataTable(); da.Fill(new_dt); //复制dt的数据到new_dt中 DataRow new_dr = new_dt.NewRow(); foreach (DataRow dr in dt.Rows) { for (int i = 0; i < dt.Columns.Count; ++i) { new_dr[i] = dr[i]; } new_dt.Rows.Add(new_dr); } //自动构造InsertCommand命令 ocb = new OracleCommandBuilder(da); da.InsertCommand = ocb.GetInsertCommand(); da.UpdateBatchSize = 5000; da.Update(new_dt); } catch (Exception ex) { //暂不处理,抛给上层做处理 throw (ex); } finally { conn.Close(); ocb.Dispose(); da.Dispose(); com.Dispose(); conn.Dispose(); } }
public static DataTable GetOficioTramitacaoByDOC_ID( decimal pDOC_ID, ConnectionInfo pInfo ) { //string lQuery = ""; DataTable lTable = new DataTable(); //lQuery = DocumentoTramitacaoQD.qTramitacaoOficio.Trim(); //lQuery += String.Format(" AND DOCMOV.{0} = >>{0} ORDER BY DOCMOV.PTC_ID, DOCTRAM.DOCTRAM_ID", DocumentoMovimentoQD._DOC_ID.Name); //lQuery += " ORDER BY DOCMOV.PTC_ID, DOCTRAM.DOCTRAM_ID"; //SelectCommand lSelect = new SelectCommand(lQuery); OracleCommand cmd = new OracleCommand(); using (OracleConnection cn = new OracleConnection(pInfo.ConnectionString)) { cmd.Connection = cn; cmd.CommandText = "ConsultaProcesso"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("pDOC_ID", OracleType.Int32).Value = pDOC_ID; cmd.Parameters.Add("p_ref", OracleType.Cursor).Direction = ParameterDirection.Output; //cmd.Parameters.Add("pDOC_ID", OracleDbType.Decimal).Value = pDOC_ID; //cmd.Parameters.Add("p_ref", OracleDbType.RefCursor).Direction = ParameterDirection.Output; //cmd.Parameters.Add("p_ref", ParameterDirection.Output); cn.Open(); //OracleDataReader dr = cmd.ExecuteReader(); //if (dr.HasRows) //{ DataSet ds = new DataSet(); OracleDataAdapter Adapter = new OracleDataAdapter(cmd); Adapter.Fill(ds); lTable = ds.Tables[0]; Adapter.Dispose(); //} //lSelect.Fields.Add(DocumentoMovimentoQD._DOC_ID.Name, pDOC_ID, (ItemType)DocumentoMovimentoQD._DOC_ID.DBType); //lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); cmd.Dispose(); cn.Dispose(); cn.Close(); } return lTable; //OracleString rowId; //using (OracleConnection cn = new OracleConnection(pInfo.ConnectionString)) //{ // cn.Open(); // OracleDataReader dr; // //OracleConnection cn = new OracleConnection(pInfo.ConnectionString); // OracleCommand cmd = new OracleCommand(); // cmd.Connection = cn; // cmd.CommandText = lQuery2; // //lRows = (Int32)cmd.ExecuteScalar(); // //if (lRows > 0) // //{ // DataTable Dt = new DataTable(); // OracleDataAdapter Adapter = new OracleDataAdapter(lQuery, pInfo.ConnectionString); // //Adapter.SelectCommand = cmd; // lTable.Clear(); // Dt.Clear(); // Adapter.Fill(Dt); // // Adapter.Fill(lTable); // lTable = Dt; }
/// <summary> /// ִ�в�ѯ��������ѯ��������� DataTable �С� /// </summary> /// <param name="dt">�����������ݱ�</param> /// <param name="sql">sql���</param> /// <param name="OracleParams">��������</param> /// <param name="connName">ָ��ʹ�õ����ݿ�����</param> /// <param name="timeOut">�ȴ�����ִ�е�ʱ�䣨����Ϊ��λ��,Ĭ��ֵΪ 30 �롣</param> public static void QueryDt(DataTable dt, string sql, OracleParameter[] OracleParams, string connName, int timeOut) { OracleConnection conn = null; OracleCommand cmd = new OracleCommand(); OracleDataAdapter adapter = new OracleDataAdapter(); try { //ȡ�����ݿ����� conn = GetConnection(connName); cmd.Connection = conn; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; if (OracleParams != null) { foreach (OracleParameter sp in OracleParams) { cmd.Parameters.Add(sp); } } //�ȴ�����ִ�е�ʱ�䣨����Ϊ��λ��,Ĭ��ֵΪ 30 �롣 if (timeOut <= 30) { cmd.CommandTimeout = 30; } else { cmd.CommandTimeout = timeOut; } adapter.SelectCommand = cmd; conn.Open(); adapter.Fill(dt); } catch (Exception ex) { throw ex; } finally { if (conn != null) { conn.Close(); } cmd.Dispose(); adapter.Dispose(); } }
public void run() { Exception exp = null; DataSet ds = new DataSet(); OracleDataAdapter da = new OracleDataAdapter(cmdSelect); da.DeleteCommand = cmdDelete; da.UpdateCommand = cmdUpdate; da.InsertCommand = new OracleCommand("",con); cmdInsert = da.InsertCommand; da.Fill(ds); try { BeginCase("Dispose - check DataAdapter select command"); da.Dispose(); Compare(da.SelectCommand == null, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check DataAdapter insert command"); Compare(da.InsertCommand == null, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check DataAdapter Delete Command"); Compare(da.DeleteCommand == null, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check DataAdapter update command"); Compare(da.UpdateCommand == null, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check select command object"); Compare(cmdSelect != null, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check delete command object"); Compare(cmdDelete != null, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check insert command object"); Compare(cmdInsert != null, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check Update command object"); Compare(cmdUpdate != null, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check cmdSelect.connection object"); Compare(cmdSelect.Connection != null ,true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check cmdDelete.connection object"); Compare(cmdDelete.Connection != null ,true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check cmdUpdate.connection object"); Compare(cmdUpdate.Connection != null ,true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check cmdInsert.connection object"); Compare(cmdInsert.Connection != null ,true); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} try { BeginCase("Dispose - check command connection state"); da.Dispose(); Compare(cmdSelect.Connection.State ,ConnectionState.Open); } catch(Exception ex){exp = ex;} finally{EndCase(exp);exp = null;} }
//提交更新命令 public override string UpdateBySql(string strsql) { try { this.CheckConn(); OracleCommand cmdUpdate = new OracleCommand(); OracleDataAdapter daUpdate = new OracleDataAdapter(); cmdUpdate.Connection = MyConn; cmdUpdate.CommandText = strsql; daUpdate.UpdateCommand = cmdUpdate; daUpdate.UpdateCommand.ExecuteNonQuery(); cmdUpdate.Dispose(); daUpdate.Dispose(); return ("ok"); } catch (Exception ex) { return (ex.Message); } }
public static DataTable GetOracleDatatable(string sql, OracleParameter[] param) { DataTable dt = new DataTable(); try { OracleConnection conn = new OracleConnection(DPISConnectionString); conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 240; OracleDataAdapter da = new OracleDataAdapter(); da.SelectCommand = cmd; if (param != null) { foreach (OracleParameter p in param) { if (p != null) { cmd.Parameters.Add(p); } } } da.Fill(dt); da.Dispose(); } catch (Exception ex) { dt = new DataTable(); } return dt; }
public override string FillDataset(string strsql, string TableName, DataSet DS) { try { string errstr; errstr = CheckConn(); if (errstr != "ok") { return (errstr); } OracleCommand Cmd = MyConn.CreateCommand();//sql查询命令对象 Cmd.CommandText = strsql;//sql命令付给MyOraCmd对象 OracleDataAdapter dabuff = new OracleDataAdapter();//建立适配器 if (DS.Tables.Contains(TableName) == false)//判断数据集中是否有相同名称表 { DS.Tables.Add(new DataTable(TableName)); } else { //清除数据集中存在表的行数 DS.Tables[TableName].Rows.Clear(); } dabuff.SelectCommand = Cmd; DS.Tables[TableName].BeginLoadData(); dabuff.Fill(DS.Tables[TableName]); DS.Tables[TableName].EndLoadData(); Cmd.Dispose(); dabuff.Dispose(); return ("ok"); } catch (Exception ex) { return (ex.Message); } }
public void Dispose () { OracleDataAdapter da = new OracleDataAdapter (); da.DeleteCommand = new OracleCommand (); da.InsertCommand = new OracleCommand (); da.SelectCommand = new OracleCommand (); da.UpdateCommand = new OracleCommand (); da.Dispose (); Assert.IsNull (da.DeleteCommand, "#1"); Assert.IsNull (da.InsertCommand, "#2"); Assert.IsNull (da.SelectCommand, "#3"); Assert.IsNotNull (da.TableMappings, "#4"); Assert.AreEqual (0, da.TableMappings.Count, "#5"); Assert.IsNull (da.UpdateCommand, "#6"); }
public AutoCompleteStringCollection AutoComplete(TextBox textbox, string strQuery, string column) { AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection(); try { string oradb = "Data Source= cp3dbinstance.c4pxnpz4ojk8.us-east-1.rds.amazonaws.com:1521/cp3db;User Id=sw1;Password=CampBand4;"; // Create an instance of an Oracle Command to be executed OracleConnection connect = new OracleConnection(oradb); //strQuery = "SELECT * FROM Staff WHERE forename LIKE '%" + textbox.Text.Trim() + "%'"; //MessageBox.Show(strQuery); OracleCommand cmd = new OracleCommand(strQuery, connect); // Specify that the command type is a string cmd.CommandType = CommandType.Text; // Check if the connection is alive if (connect.State != ConnectionState.Open) connect.Open(); // Create a data adapter to contain the results of the query OracleDataAdapter orada = new OracleDataAdapter(cmd); // Create a data table to store the data DataTable dt = new DataTable(); // Fill up data into the data table orada.Fill(dt); // Count the rows in the data table int RowCount = dt.Rows.Count; orada.Dispose(); cmd.Dispose(); if (RowCount > 0) { int i = 0; while (i < RowCount) { namesCollection.Add(dt.Rows[i][column.ToUpper()].ToString().Trim()); i++; } } connect.Close(); } catch (OracleException e) { MessageBox.Show(e.Message); } catch (AccessViolationException ex) { MessageBox.Show(ex.Message); } return namesCollection; }
public static DataTable ExecListCmd(string sqlz, OracleConnection zConn, OracleTransaction zTrans) { OracleCommand zCommand = new OracleCommand(); OracleDataAdapter zAdapt = new OracleDataAdapter(); zAdapt.SelectCommand = zCommand; DataTable zDt = new DataTable(); if (zTrans == null && zConn == null) { using (zConn = new OracleConnection(ConnectionString)) { BuildzCommand(zCommand, zConn, zTrans, CommandType.Text, sqlz, null); zAdapt.Fill(zDt); } } else { if (zTrans != null && zConn == null) zConn = zTrans.Connection; BuildzCommand(zCommand, zConn, zTrans, CommandType.Text, sqlz, null); zAdapt.Fill(zDt); } zAdapt.Dispose(); return zDt; }