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