void BtRunClick(object sender, EventArgs e)
{
string sql=tbSql.Text;
while(sql.Trim().EndsWith(";"))
{
sql=sql.Trim().Substring(0,sql.Trim().Length-1);
}
tbSql.Text=sql;
if(sql=="") return;
numPage.Value=1;
try
{
if(conn.State!=ConnectionState.Open) conn.Open();
if(da!=null) da.Dispose();
da=new System.Data.OracleClient.OracleDataAdapter(sql,conn);
dt.Clear();
dt.Columns.Clear();
dt.DefaultView.Sort="";
da.Fill((int)((numPage.Value-1)*numRecords.Value),(int)numRecords.Value,dt);
dataGridView1.DataSource=dt;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message ,"Error");
}
}
/// <summary>
/// 更新指定的科室数据字典记录
/// </summary>
/// <param name="iDeptDataDict"></param>
/// <param name="sda"></param>
/// <returns></returns>
public override int Update1(IModel iDeptDataDict, ref System.Data.OracleClient.OracleDataAdapter sda)
{
DataSet ds = new DataSet();
sda.Fill(ds);
DataTable dt = ds.Tables[0];
MDeptDataDict deptDataDict = (MDeptDataDict)iDeptDataDict;
DataRow[] dr = dt.Select("DATA_NAME='" + deptDataDict.DATA_NAME + "' AND DATA_TYPE='" + deptDataDict.DATA_TYPE + "' AND DEPT_CODE='" + deptDataDict.DEPT_CODE + "'");
if (dr.Length > 1)
{
return(-1);
}
dr[0]["AUTHOR"] = deptDataDict.AUTHOR;
dr[0]["CREATE_DATE_TIME"] = deptDataDict.CREATE_DATE_TIME;
dr[0]["DATA"] = deptDataDict.DATA;
dr[0]["DATA_NAME"] = deptDataDict.DATA_NAME;
dr[0]["DATA_TYPE"] = deptDataDict.DATA_TYPE;
dr[0]["DEPT_CODE"] = deptDataDict.DEPT_CODE;
dr[0]["MODIFY_DATE_TIME"] = deptDataDict.MODIFY_DATE_TIME;
string strSql = "select * from " + TableName + " where DEPT_CODE='" + deptDataDict.DEPT_CODE +
"' and DATA_NAME = '" + deptDataDict.DATA_NAME +
"' and DATA_TYPE = '" + deptDataDict.DATA_TYPE + "'";
return(UpdateDataTable1(strSql, dt, sda));
}
public void OnTableChange(Object sender, OracleNotificationEventArgs args)
{
//Get data Changed before.........................
if (args.Type == OracleNotificationType.Change)
{
int start = 0;
if (args.Info.ToString() == "Update")
{
start = 1;
}
for (int i = start; i < args.Details.Rows.Count; i++)
{
DataRow detailRow = args.Details.Rows[i];
string rowid = detailRow["Rowid"].ToString();
DataSet ds = new DataSet();
using (System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(NotificationNameAdapter, constr))
{
try
{
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("MY_ID", System.Data.OracleClient.OracleType.VarChar).Value = rowid;
da.SelectCommand.Parameters.Add("Info", System.Data.OracleClient.OracleType.VarChar).Value = args.Info.ToString();
System.Data.OracleClient.OracleParameter ret = new System.Data.OracleClient.OracleParameter("my_cursor", System.Data.OracleClient.OracleType.Cursor);
ret.Direction = ParameterDirection.ReturnValue;
da.SelectCommand.Parameters.Add(ret);
da.Fill(ds);
}
catch (Exception ex)
{
}
}
string data = JsonConvert.SerializeObject(ds);
try
{
Message messageObject = new Message();
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
messageObject.message = ds.Tables[0].Rows[j]["MESSAGE"].ToString();
messageObject.url = ds.Tables[0].Rows[j]["URL"].ToString();;
messageObject.type = ds.Tables[0].Rows[j]["TYPE"].ToString();
var jsonMessage = JsonConvert.SerializeObject(messageObject);
//after get two paramters of notification fire it...
string successMessage = sendNotification(ds.Tables[0].Rows[j]["NID"].ToString(), jsonMessage);
}
}
catch (Exception e)
{
}
}
}
}
private void DisplayDataForQuery()
{
dataGridView1.DataSource = null;
if (queryBuilder.MetadataProvider != null && queryBuilder.MetadataProvider.Connected)
{
if (queryBuilder.MetadataProvider is OracleMetadataProvider)
{
System.Data.OracleClient.OracleCommand command = (System.Data.OracleClient.OracleCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder.SQL;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
System.Data.OracleClient.OracleParameter parameter = new System.Data.OracleClient.OracleParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
} //end oracle (ms)
} //end check if connected
} //end method
private TB_Dzsrcdel GetDzsrcdel(string sono, int entryid, System.Data.OracleClient.OracleCommand cmd)
{
TB_Dzsrcdel model = new TB_Dzsrcdel()
{
Fjde_sono = sono, FsoentryID = entryid
};
string sql = DbUtils.GetSqlWithObject(model, Operate.Select);
sql += "WHERE Fjde_sono=:Fjde_sono AND FsoentryID=:FsoentryID";
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("Fjde_sono", model.Fjde_sono);
cmd.Parameters.AddWithValue("FsoentryID", model.FsoentryID);
System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(cmd);
System.Data.DataTable table = new System.Data.DataTable();
da.Fill(table);
if (table.Rows.Count == 1)
{
var t = model.GetType();
var pis = t.GetProperties().ToList();
var row = table.Rows[0];
pis.ForEach(p =>
{
var value = row[p.Name];
if (value != null && DBNull.Value != value)
{
p.SetValue(model, value, null);
}
});
return(model);
}
if (table.Rows.Count > 1)
{
throw new Exception(string.Format("TB_DZsrcdel表中存在多条符合条件【Fjde_sono={0} AND FsoentryID={1}】的记录", sono, entryid));
}
return(null);
}
private void button1_Click(object sender, System.EventArgs e)
{
try
{
System.Collections.Specialized.StringCollection databases = new System.Collections.Specialized.StringCollection();
databases.Add("SEMPRA.DEV");
databases.Add("SEMPRA.STAGE");
Sempra.Login oracleLogin = new Sempra.Login(
true,
5,
"H:\\VS2003Projects\\SempraOpsDevLib\\Login",
"\\\\stdev2\\dev\\OpsDev\\XMLSchemas\\",
databases);
if (oracleLogin.Execute(oracleConnection))
{
label1.Text = "Login Successful";
oracleDataAdapter = new System.Data.OracleClient.OracleDataAdapter(
"select * from dept", oracleConnection);
DataSet dataSet = new DataSet();
oracleDataAdapter.Fill(dataSet);
gridData.BeginUpdate();
gridData.DataSource = dataSet;
gridData.DataMember = "Table";
gridData.EndUpdate();
}
else
{
label1.Text = "Login Failed";
}
}
catch (Exception error)
{
MessageBox.Show(error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// 获得查询的字段
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object[] GetFields(string sql)
{
ArrayList al = new ArrayList();
object[] param = this.GetParams(sql);
for (int j = 0; j < param.Length; j++)
{
sql = sql.Replace(param[j].ToString(), System.DateTime.Now.ToString());
}
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(sql, this.con.ConnectionString);
System.Data.DataSet ds = new System.Data.DataSet();
adapter.Fill(ds, 0, 0, "table");
if (ds == null)
{
return(al.ToArray());
}
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
al.Add(ds.Tables[0].Columns[i].Caption);
}
return(al.ToArray());
}
public DataTable SelectRemessas()
{
DataTable dt = new DataTable();
ConexaoOracle objConexao = new ConexaoOracle();
try
{
objConexao.AdicionarParametroCursor("DADOS");
System.Data.OracleClient.OracleDataAdapter adpt = objConexao.ExecutarAdapter("AAT_PKG_SERASA_PEFIN.LISTAR_REMESSAS");
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: //n" + ex.Message);
}
finally
{
objConexao.Dispose();
}
return(dt);
}
private TB_DZmanuscript GetManuscript_By_SonoAnd_EntryID(string sono, int entryid, System.Data.OracleClient.OracleCommand cmd)
{
TB_DZmanuscript model = new TB_DZmanuscript()
{
Fjde_sono = sono, FsoentryID = entryid
};
string sql = DbUtils.GetSqlWithObject(model, Operate.Select);
sql += "WHERE Fjde_sono=:Fjde_sono AND FsoentryID=:FsoentryID";
cmd.CommandText = sql;
DbUtils.GetParams(model, cmd);
System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(cmd);
System.Data.DataTable table = new System.Data.DataTable();
da.Fill(table);
if (table.Rows.Count == 1)
{
var t = model.GetType();
var pis = t.GetProperties().ToList();
var row = table.Rows[0];
model.FID = row["FID"].ToString();
return(model);
}
if (table.Rows.Count > 1)
{
throw new Exception(string.Format("{2}表中存在多条符合条件【Fjde_sono={0} AND FsoentryID={1}】的记录", sono, entryid, table.TableName));
}
throw new Exception("未找到相关的底表记录");
}
protected override ExeResEdm GetDataSets(List <SqlContianer> ltSqls)
{
ExeResEdm dBResEdm = new ExeResEdm();
DataSet ds = new DataSet();
try
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleTransaction tsOprate = conn.BeginTransaction();
try
{
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = tsOprate;
List <string> tbNames = new List <string>();
foreach (var objOraSqlCon in ltSqls)
{
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(objOraSqlCon.tableName))
{
dt.TableName = objOraSqlCon.tableName;
}
else
{
string tb = GetTableNameFromSelectSql(objOraSqlCon.strSqlTxt);
if (!string.IsNullOrEmpty(tb))
{
dt.TableName = tb;
}
}
if (tbNames.Contains(dt.TableName))
{
dt.TableName = dt.TableName + "_" + (tbNames.Count() + 1);
}
tbNames.Add(dt.TableName);
cmd.CommandText = objOraSqlCon.strSqlTxt;
cmd.Parameters.Clear();
if (objOraSqlCon.ltOraParams != null && objOraSqlCon.ltOraParams.Count > 0)
{
cmd.Parameters.AddRange(objOraSqlCon.ltOraParams.ToArray());
}
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(cmd);
adapter.Fill(dt);
ds.Tables.Add(dt);
}
tsOprate.Commit();
}
catch (Exception ex)
{
tsOprate.Rollback();
dBResEdm.Module = "GetDataSets方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "GetDataSets方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
dBResEdm.ExeModel = ds;
return(dBResEdm);
}
private void DisplayDataForQuery()
{
//AppGlobals.AppMessages.DisplayAlertMessage("DisplayDataForQuery");
dataGridView1.DataSource = null;
if (queryBuilder.MetadataProvider != null && queryBuilder.MetadataProvider.Connected)
{
if (queryBuilder.MetadataProvider is MSSQLMetadataProvider)
{
SqlCommand command = (SqlCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder.SQL;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}//end sql server
//if (queryBuilder.MetadataProvider is MSSQLCEMetadataProvider)
//{
// SqlCeCommand command = (SqlCeCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
// command.CommandText = queryBuilder.SQL;
// // handle the query parameters
// if (queryBuilder.Parameters.Count > 0)
// {
// for (int i = 0; i < queryBuilder.Parameters.Count; i++)
// {
// if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
// {
// SqlCeParameter parameter = new SqlCeParameter();
// parameter.ParameterName = queryBuilder.Parameters[i].FullName;
// parameter.DbType = queryBuilder.Parameters[i].DataType;
// command.Parameters.Add(parameter);
// }
// }
// using (QueryParametersForm qpf = new QueryParametersForm(command))
// {
// qpf.ShowDialog();
// }
// }
// SqlCeDataAdapter adapter = new SqlCeDataAdapter(command);
// DataSet dataset = new DataSet();
// try
// {
// adapter.Fill(dataset, "QueryResult");
// dataGridView1.DataSource = dataset.Tables["QueryResult"];
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message, "SQL query error");
// }
//}//end sql server ce
if (queryBuilder.MetadataProvider is OLEDBMetadataProvider)
{
//AppGlobals.AppMessages.DisplayAlertMessage("for OLEDBMetadataProvider");
OleDbCommand command = (OleDbCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder.SQL;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
OleDbParameter parameter = new OleDbParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
//AppGlobals.AppMessages.DisplayAlertMessage("end for OLEDBMetadataProvider");
}//end ole schemaRoot
if (queryBuilder.MetadataProvider is ODBCMetadataProvider)
{
OdbcCommand command = (OdbcCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder.SQL;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
OdbcParameter parameter = new OdbcParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
OdbcDataAdapter adapter = new OdbcDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}//end odbc
if (queryBuilder.MetadataProvider is OracleMetadataProvider)
{
System.Data.OracleClient.OracleCommand command = (System.Data.OracleClient.OracleCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder.SQL;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
System.Data.OracleClient.OracleParameter parameter = new System.Data.OracleClient.OracleParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
} //end oracle (ms)
} //end check if connected
} //end method
private void button1_Click(object sender, System.EventArgs e)
{
try
{
System.Collections.Specialized.StringCollection databases = new System.Collections.Specialized.StringCollection();
databases.Add( "SEMPRA.DEV" );
databases.Add( "SEMPRA.STAGE" );
Sempra.Login oracleLogin = new Sempra.Login(
true,
5,
"H:\\VS2003Projects\\SempraOpsDevLib\\Login",
"\\\\stdev2\\dev\\OpsDev\\XMLSchemas\\",
databases );
if ( oracleLogin.Execute( oracleConnection ) )
{
label1.Text = "Login Successful";
oracleDataAdapter = new System.Data.OracleClient.OracleDataAdapter(
"select * from dept", oracleConnection );
DataSet dataSet = new DataSet();
oracleDataAdapter.Fill( dataSet );
gridData.BeginUpdate();
gridData.DataSource = dataSet;
gridData.DataMember = "Table";
gridData.EndUpdate();
}
else
label1.Text = "Login Failed";
}
catch( Exception error )
{
MessageBox.Show( error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
}
}
public void RunSQl(object dbmodel)
{
DbModel model = (DbModel)dbmodel;
if (string.IsNullOrEmpty(model.Conn)) return;
string sql = this.txtsql.Text.Trim();
string conn = model.Conn;
RichTextBox ctl = null;
if (model.TextBoxName == "rtxtCtosut")
{
ctl = this.rtxtCtosut;
}
else
{
ctl = this.rtxtCtosdemo;
}
Stopwatch sw = Stopwatch.StartNew();
sw.Restart();
StringBuilder sb = new StringBuilder();
DataSet ds = new DataSet();
string dbname = null;
try
{
if (model.isOdp)
{
using (OracleConnection oraconn = new OracleConnection(conn))
{
oraconn.Open();
dbname = oraconn.ServiceName;
OracleCommand cmd = new OracleCommand(sql, oraconn);
OracleDataAdapter oda = new OracleDataAdapter(cmd);
int x = oda.Fill(ds);
sw.Stop();
}
}
else
{
using (System.Data.OracleClient.OracleConnection oraconn = new System.Data.OracleClient.OracleConnection(conn))
{
oraconn.Open();
dbname = oraconn.DataSource;
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand(sql, oraconn);
System.Data.OracleClient.OracleDataAdapter oda = new System.Data.OracleClient.OracleDataAdapter(cmd);
int x = oda.Fill(ds);
sw.Stop();
}
}
DataTable dt = ds.Tables[0];
sb.AppendFormat("#{3}, server: {4}, Return {0} rows with {1} columns, cost: {2}ms", dt.Rows.Count, dt.Columns.Count, sw.ElapsedMilliseconds, Thread.CurrentThread.ManagedThreadId, dbname);
if (dt.Rows.Count == 1)
{
sb.AppendLine();
foreach (DataColumn dc in dt.Columns)
{
sb.AppendFormat("Column {0}; Type {1}; Value {2};", dc.ColumnName.PadLeft(20), dc.DataType.ToString().PadLeft(20), dt.Rows[0][dc].ToString().PadLeft(20));
sb.AppendLine();
}
}
}
catch (Exception ex)
{
sb.AppendFormat("Error: {0}" , ex.Message);
}
sb.AppendLine();
if (this.rtxtCtosut.InvokeRequired)
{
Invoke(new SetContrlValueDelegate(SetTextboxValue), ctl, sb.ToString());
}
else
{
ctl.Text += sb.ToString();
SetState();
}
}
public int Fill(System.Data.DataSet dataSet, int startRecord, int maxRecords, string srcTable)
{
return(_da.Fill(dataSet, startRecord, maxRecords, srcTable));
}