public static bool EditFeature(string labelid,string title,string content)
{
StringBuilder sb = new StringBuilder();
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
OracleParameter[] parms = new OracleParameter[]{
new OracleParameter(":LABELID",OracleType.VarChar),
new OracleParameter(":TITLE",OracleType.VarChar),
new OracleParameter(":CONTENT",OracleType.VarChar)
};
parms[0].Value = labelid;
parms[1].Value = title;
parms[2].Value = content;
comm.CommandText = SQL_UPDATE_LABEL;
comm.CommandType = CommandType.Text;
foreach (OracleParameter parm in parms)
{
comm.Parameters.Add(parm);
}
comm.ExecuteNonQuery();
conn.Close();
return true;
}
}
protected void Page_Load(object sender, EventArgs e)
{
string guid = string.Empty;
guid = Request["q"];
int ww = Convert.ToInt32(Request["w"]);
int wh = Convert.ToInt32(Request["h"]);
if (guid == null || guid == string.Empty)
{
this.map.InnerHtml = "<h1 style='font-size:24px;text-align:center;'>输入参数无效</h1>";
Response.End();
}
else
{
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
comm.CommandText = string.Format(SQL_SELECT, guid);
OracleDataReader dr = comm.ExecuteReader();
if (dr.Read())
{
string innerHTML = dr["DATA"].ToString();
this.map.InnerHtml = innerHTML;
this.map.Style.Value = "display:block;width:" + ww + "px;height:" + wh + "px;";
}
else
{
this.map.InnerHtml = "<h1 style='font-size:24px;text-align:center;'>该分享不存在</h1>";
}
}
}
}
public static string GetFeature(string p_key, int p_size, int p_page)
{
StringBuilder sb = new StringBuilder();
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
comm.CommandText = string.Format(SQL_SEARCH_STATION_TOTAL, p_size, p_key);
object total = comm.ExecuteScalar();
comm.CommandText = string.Format(SQL_SEARCH_STATION, p_key, p_page * p_size, (p_page - 1) * p_size);
OracleDataReader rdr = comm.ExecuteReader();
string str = Reader2JSON.ToJSON(rdr);
sb.Append("{\"total\":");
sb.Append(total);
sb.Append(",\"page\":");
sb.Append(p_page);
sb.Append(",\"content\":");
sb.Append(str);
sb.Append("}");
conn.Close();
}
return sb.ToString();
}
/// <summary>
/// 填充combox
/// </summary>
/// <param name="cb"></param>
/// <param name="sql"></param>
private void FillComb(ComboBox cb,string sql)
{
try
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
cb.Items.Clear();
cb.Items.Add("");
while (dr.Read())
{
cb.Items.Add(dr[0].ToString());
}
conn.Close();
dr.Close();
}
}
catch (OracleException ox)
{
MessageBox.Show(ox.Message.ToString());
return;
}
}
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 void createMember(string firstname, string lastname, string address, string city, string username,
string password, string email, string picture) //insert member
{
String oradb = "Data Source= cp3dbinstance.c4pxnpz4ojk8.us-east-1.rds.amazonaws.com:1521/cp3db;User Id=sw1;Password=CampBand4;";
OracleConnection conn = new OracleConnection(oradb);
try
{
String strSQL = "INSERT INTO MEMBERS VALUES(Members_seq.nextval, '" + firstname + "', '" + lastname + "', '" + address + "', '" + city + "', '" + username + "', '" + password + "', '" + picture + "', '" + email + "', '" + DateTime.Now.ToString("dd-MMM-yyyy") + "' )";
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Welcome to BandCamp!");
registered = true;
}
catch (OracleException ex)
{
registered = false;
MessageBox.Show("Something went wrong when accessing the database.");
MessageBox.Show("Registration was unsuccessful");
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void createOracleConnection()
{
try
{
if (dbType == DbType.Oracle)
{
if (conOra == null)
{
conOra = new System.Data.OracleClient.OracleConnection(url);
}
if (cmdOra == null)
{
cmdOra = conOra.CreateCommand();
}
if (conOra.State != ConnectionState.Open)
{
conOra.Open();
}
}
}
catch
{
try
{
conOra.Close();
}
catch { }
throw new StaConnectException();
}
}
public static string GetFeature(string p_key, int p_size, int p_page, double p_left, double p_bottom, double p_right,double p_top )
{
StringBuilder sb = new StringBuilder();
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
comm.CommandText = string.Format(SQL_RECTANGLE_KEYWORD_TOTAL, p_size,p_left,p_right,p_bottom,p_top,p_key);
object total = comm.ExecuteScalar();
comm.CommandText = string.Format(SQL_RECTANGLE_KEYWORD, p_key,p_left, p_right,p_bottom,p_top,p_page * p_size,(p_page-1)*p_size);
OracleDataReader rdr = comm.ExecuteReader();
string str = Reader2JSON.ToJSON(rdr);
sb.Append("{total:");
sb.Append(total);
sb.Append(",page:");
sb.Append(p_page);
sb.Append(",content:");
sb.Append(str);
sb.Append("}");
conn.Close();
}
return sb.ToString();
}
private DataTable ConnectAndQuery(string devicename)
{
devicename = string.IsNullOrEmpty(devicename) ? "V-0516" : devicename;
string connectionString = GetOracleConnectionString();
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection())
{
try
{
connection.ConnectionString = connectionString;
try { connection.Close(); }
catch (Exception ex)
{ }
connection.Open();
OracleCommand command = connection.CreateCommand();
string sql = "SELECT * FROM v_conduit_test where separator_id='" + devicename + "' and conduit_name like '%" + ddlConduit.SelectedValue + "%' and rownum<100";
command.CommandText = sql;
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(ds);
connection.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
connection.Close();
}
return(null);
}
}
/// <summary>
/// 将生成的材料信息更新到数据库中
/// </summary>
/// <param name="sql"></param>
/// <param name="excelinfo"></param>
public static void UpdateExcelInfo(string sql,byte[] excelinfo)
{
try
{
byte[] file = excelinfo;
using (OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
OracleParameter op = new OracleParameter("dfd", OracleType.Blob);
op.Value = file;
if (file.Length == 0)
{
MessageBox.Show("插入信息表不能为空！", "WARNNING", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
else
{
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
}
}
conn.Close();
}
}
catch (IOException ee)
{
MessageBox.Show(ee.Message.ToString());
return;
}
}
/// <summary>
/// 测试 调用 Oracle 返回结果集的函数.
/// </summary>
private void CallFuncWithTable(OracleConnection conn)
{
// 创建一个 Command.
OracleCommand testCommand = conn.CreateCommand();
// 定义需要执行的SQL语句.
testCommand.CommandText = "pkg_HelloWorld.getHelloWorld";
// 定义好，本次执行的类型，是存储过程.
testCommand.CommandType = CommandType.StoredProcedure;
// 定义好，我这个参数，是 游标 + 返回值.
OracleParameter para = new OracleParameter("c", OracleType.Cursor);
para.Direction = ParameterDirection.ReturnValue;
testCommand.Parameters.Add(para);
// 执行SQL命令，结果存储到Reader中.
OracleDataReader testReader = testCommand.ExecuteReader();
// 处理检索出来的每一条数据.
while (testReader.Read())
{
// 将检索出来的数据，输出到屏幕上.
Console.WriteLine("调用函数:{0}; 返回:{1} - {2}",
testCommand.CommandText, testReader[0], testReader[1]
);
}
// 关闭Reader.
testReader.Close();
}
public static void FillTreeView(TreeNode node, string sql)
{
try
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
node.Nodes.Add(dr[0].ToString());
}
conn.Close();
dr.Close();
}
}
catch (OracleException ox)
{
MessageBox.Show(ox.Message.ToString());
return;
}
}
public static bool AddFeature(string usrid,string usrname,string password,int privilege)
{
StringBuilder sb = new StringBuilder();
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
OracleParameter[] parms = new OracleParameter[]{
new OracleParameter(":USRID",OracleType.VarChar),
new OracleParameter(":USRNAME",OracleType.VarChar),
new OracleParameter(":PASSWORD",OracleType.VarChar),
new OracleParameter(":PRIVILEGE",OracleType.UInt32)
};
parms[0].Value = usrid;
parms[1].Value = usrname;
parms[2].Value = password;
parms[3].Value = privilege;
comm.CommandText = SQL_INSERT_USER;
comm.CommandType = CommandType.Text;
foreach (OracleParameter parm in parms)
{
comm.Parameters.Add(parm);
}
comm.ExecuteNonQuery();
conn.Close();
return true;
}
}
int MSOracleClient()
{
System.Data.OracleClient.OracleConnectionStringBuilder connBuilder = new System.Data.OracleClient.OracleConnectionStringBuilder();
connBuilder.DataSource = txtDataSource.Text.Trim();
connBuilder.UserID = txtUserId.Text.Trim();
connBuilder.Password = txtPwd.Text.Trim();
connBuilder.LoadBalanceTimeout = 60;
connBuilder.MinPoolSize = 0;
connBuilder.MaxPoolSize = 50;
int rows = 0;
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connBuilder.ConnectionString))
{
//System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connBuilder.ConnectionString);
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = txtSql.Text.Trim();
cmd.CommandTimeout = 300;
//cmd.ResetCommandTimeout();
conn.Open();
using (System.Data.OracleClient.OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
rows++;
}
}
return(rows);
}
}
public void ProcessRequest(HttpContext context)
{
string htmltext = string.Empty;
string guid = string.Empty;
int ww;
int wh;
htmltext = context.Request["HTML"];
ww = Convert.ToInt32(context.Request["ww"]);
wh = Convert.ToInt32(context.Request["wh"]);
guid = Guid.NewGuid().ToString();
using (OracleConnection conn = new OracleConnection(context.Cache["ORASTR"].ToString()))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
OracleParameter[] parms = new OracleParameter[]{
new OracleParameter(":GUID",OracleType.VarChar),
new OracleParameter(":DATA",OracleType.NClob),
};
parms[0].Value = guid;
parms[1].Value = htmltext;
comm.CommandText = SQL_INSERT;
comm.CommandType = CommandType.Text;
foreach (OracleParameter parm in parms)
{
comm.Parameters.Add(parm);
}
comm.ExecuteNonQuery();
string url = context.Request.Url.AbsoluteUri.Substring(0, context.Request.Url.AbsoluteUri.IndexOf("SVR/ShareMap.ashx"));
context.Response.Write("window.open(\'"+url+"Delegate.aspx?q="+guid+"&w="+ww+"&h="+wh+"\')");
}
}
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;
}
}
public static void GetFlowStatus(ComboBox cbox, string sql)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
try
{
//OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
OracleDataReader datareader = cmd.ExecuteReader();
while (datareader.Read())
{
cbox.Items.Add(datareader[0].ToString());
}
datareader.Close();
//conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
//return;
}
finally { conn.Close(); }
}
private void btnAlter_Click(object sender, EventArgs e)
{
bool check = true;
if (this.comboxSL.Text == "")
{ check = false; MessageBox.Show("税率没有选择"); }
else if (this.ComboxJSFS.Text == "")
{ check = false; MessageBox.Show("结算方式没有选择"); }
else if (this.txtGHDW.Text == "")
{ check = false; MessageBox.Show("单位没有选择"); }
else
{ check = true; }
if (check == true)
{
using (OracleConnection connection = new OracleConnection(StrCon))
{
try
{
connection.Open();
OracleCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Jt_C_XSJSD_update";
cmd.Parameters.Add("LS_XSJSDid", OracleType.VarChar).Value = this.txtJSDH.Tag.ToString();
cmd.Parameters.Add("ls_GHDWID", OracleType.VarChar).Value = this.txtGHDW.Tag.ToString();
cmd.Parameters.Add("ls_JSR", OracleType.VarChar).Value = this.txtJSR.Text.ToString().Trim();
cmd.Parameters.Add("ls_JSFSID", OracleType.VarChar).Value = GetJSFSID(this.ComboxJSFS.Text.ToString().Trim());
cmd.Parameters.Add("ls_SL", OracleType.VarChar).Value = this.comboxSL.Text.ToString().Trim();
cmd.Parameters.Add("ls_ZT", OracleType.VarChar).Value = "02";
cmd.Parameters.Add("ls_BZ", OracleType.VarChar).Value = this.txtBZ.Text.ToString().Trim();
cmd.Parameters.Add("DescErr", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
cmd.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
xpServerCollectionSource1.FixedFilterString = "[XSJSDID] = \'" + this.txtJSDH.Tag.ToString() + "\'";
unitOfWork1.DropIdentityMap();
xpServerCollectionSource1.Reload();
gridView1.BestFitColumns();
MessageBox.Show(cmd.Parameters["DescErr"].Value.ToString() + cmd.Parameters["Message"].Value.ToString());
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
this.btnDWselect.Enabled = false;
this.comboxSL.Enabled = false;
this.ComboxJSFS.Enabled = false;
this.txtJSR.ReadOnly = true;
this.txtBZ.ReadOnly = true;
this.txtZT.Text = "存盘";
this.txtZT.Tag = "02";
}
}
}
}
//Author: Aleksandar Zoric
public void checkLogin()
{
string username = userNameTxt.Text;
string password = passwordTxt.Text;
string connectionString = GetConnectionString("sw4", "sw4");
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = connectionString;
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleCommand command1 = connection.CreateCommand();
string usernameCmd = "SELECT studentNumber FROM Students WHERE StudentNumber = '" + username + "'";
string passwordCmd = "SELECT studentPassword FROM Students WHERE StudentNumber = '" + username + "'";
command.CommandText = usernameCmd;
command1.CommandText = passwordCmd;
OracleDataReader usernameReader = command.ExecuteReader();
OracleDataReader passwordReader = command1.ExecuteReader();
if(usernameReader.HasRows && passwordReader.HasRows)
{
OracleCommand command2 = connection.CreateCommand();
string studentIDcmd = "SELECT studentID FROM Students WHERE StudentNumber = '" + username + "' AND studentPassword = '******'";
command2.CommandText = studentIDcmd;
string extractStudentID = command2.ExecuteScalar().ToString();
if(extractStudentID != null)
{
studentIDasString = extractStudentID;
}
//open profile form
}
else
{
MessageBox.Show("Invalid Login. Try Again");
}
}
}
private void btnConfirm_Click(object sender, EventArgs e)
{
if (selection.SelectedCount == 0)
{
MessageBox.Show("没有选择单据");
}
else
{
using (OracleConnection connection = new OracleConnection(StrCon))
{
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleTransaction transaction;
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = transaction;
try
{
for (int i = 0; i < selection.SelectedCount; ++i)
{
int RowIndex = selection.GetSelectedRowIndex(i);
int RowHandle = gridView1.GetRowHandle(RowIndex);
string strXSJSDID = gridView1.GetRowCellValue(RowHandle, colXSJSDID).ToString();
command.CommandText = "insert into Temp_Save_Id (tempid,id) Values (TEMP_SAVE_ID_SEQ.nextval,'" + strXSJSDID + "')";
command.ExecuteNonQuery();
}
selection.ClearSelection();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "JC_C_XSTSD_XD";
command.Parameters.Add("LS_XSTSDid", OracleType.VarChar).Value = this.btnConfirm.Tag.ToString();
command.Parameters.Add("DescErr", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
command.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
transaction.Commit();
string mess = command.Parameters["Message"].Value.ToString();
string alarm = command.Parameters["DescErr"].Value.ToString();
MessageBox.Show(mess + alarm);
}
catch (OracleException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
this.DialogResult = DialogResult.OK;
this.Close();
}
}
}
}
public void SyncBankAccountInfo()
{
var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
var bankAccountUpdateTime = DateTime.MinValue;
if (!string.IsNullOrEmpty(config.AppSettings.Settings["BankAccountUpdateTime"].Value))
{
bankAccountUpdateTime = DateTime.Parse(config.AppSettings.Settings["BankAccountUpdateTime"].Value);
}
var bankAccounts = new List<BankAccountDTO>();
var connectionString = ConfigurationManager.ConnectionStrings["OracleNC"].ToString();
var conn = new OracleConnection(connectionString); //进行连接
try
{
conn.Open(); //打开指定的连接
var com = conn.CreateCommand();
com.CommandText = "select * from v_jdxt_yhzh t where t.TS > '" +
bankAccountUpdateTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
;
var odr = com.ExecuteReader();
while (odr.Read()) //读取数据，如果返回为false的话，就说明到记录集的尾部了
{
var bankAccount = new BankAccountDTO
{
CustCode = odr["CUSTCODE"].ToString(), //odr.GetOracleString(0).ToString(),
Address = odr["CUSTNAME"].ToString(), //odr.GetOracleString(1).ToString(),
Name = odr["CUSTNAME"].ToString(),
UpdateDate = DateTime.Parse(odr["TS"].ToString()),
};
if (bankAccount.UpdateDate.CompareTo(bankAccountUpdateTime) > 0)
{
bankAccountUpdateTime = bankAccount.UpdateDate;
}
if (odr.GetOracleString(2).IsNull || odr.GetOracleString(3).IsNull)
{
continue;
}
bankAccount.Branch = odr["BANKDOCNAME"].ToString(); // odr.GetOracleString(2).ToString();
bankAccount.Account = odr["ACCOUNTCODE"].ToString(); // odr.GetOracleString(3).ToString();
bankAccounts.Add(bankAccount);
}
odr.Close(); //关闭reader.这是一定要写的
}
catch
{
//如果发生异常，则提示出错
}
finally
{
conn.Close(); //关闭打开的连接
}
_supplierAppService.SyncBankAccountInfo(bankAccounts);
config.AppSettings.Settings["BankAccountUpdateTime"].Value =
bankAccountUpdateTime.ToString("yyyy-MM-dd HH:mm:ss");
config.Save(ConfigurationSaveMode.Modified);
ConfigurationManager.RefreshSection("appSettings");
}
private void btnAlter_Click(object sender, EventArgs e)
{
if (MessageBox.Show("确定要进行修改操作", "提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
bool checktxtghdw = false;
if (this.txtGHDW.Text == "")
{ checktxtghdw = false; MessageBox.Show("单位没有选择"); }
else
{
checktxtghdw = true;
}
if (checktxtghdw == true)
{
OracleConnection connection = new OracleConnection(StrCon);
try
{
connection.Open();
OracleCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "JT_C_XSTSD_update";
cmd.Parameters.Add("LS_XSTSDid", OracleType.VarChar).Value = this.txtTSDH.Tag.ToString();
cmd.Parameters.Add("LS_KHID", OracleType.VarChar).Value = this.txtGHDW.Tag.ToString();
cmd.Parameters.Add("LS_KHMC", OracleType.VarChar).Value = this.txtGHDW.Text.ToString();
cmd.Parameters.Add("LS_SH", OracleType.VarChar).Value = this.txtSH.Text.ToString();
cmd.Parameters.Add("LS_DZ", OracleType.VarChar).Value = this.txtDZ.Text.ToString();
cmd.Parameters.Add("LS_KHYH", OracleType.VarChar).Value = this.txtKHYH.Text.ToString();
cmd.Parameters.Add("LS_ZH", OracleType.VarChar).Value = this.txtZH.Text.ToString().Trim();
cmd.Parameters.Add("LS_DH", OracleType.VarChar).Value = this.txtDH.Text.ToString().Trim();
cmd.Parameters.Add("LS_KXNRID", OracleType.VarChar).Value = this.txtKXNR.Tag.ToString().Trim();
cmd.Parameters.Add("LS_TSPJMCID", OracleType.VarChar).Value = this.txtTSPZ.Tag.ToString().Trim();
cmd.Parameters.Add("LS_SPFYQKID", OracleType.VarChar).Value = this.txtSPYF.Tag.ToString().Trim();
cmd.Parameters.Add("LS_XSHTID", OracleType.VarChar).Value = this.txtHTH.Text.ToString();
cmd.Parameters.Add("LS_ZDR", OracleType.VarChar, 255).Value = this.txtZDR.Tag.ToString();
cmd.Parameters.Add("ls_BZ", OracleType.VarChar, 255).Value = this.txtBZ.Text.ToString();
cmd.Parameters.Add("DescErr", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
cmd.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
unitOfWork1.DropIdentityMap();
xpServerCollectionSource1.Reload();
MessageBox.Show(cmd.Parameters["DescErr"].Value.ToString() + cmd.Parameters["Message"].Value.ToString());
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
}
}
}
public static string GetReturnValue(string str, string sql)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand command = conn.CreateCommand();
command.CommandText = sql + str;
string value = command.ExecuteOracleScalar().ToString();
conn.Close();
return value;
}
private void btnSave_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtRoleName.Text.Trim()))
{
MessageBox.Show("请输入角色名称");
txtRoleName.Focus();
}
else
{
using (OracleConnection connection = new OracleConnection(StrCon))
{
connection.Open();
OracleCommand cmd = connection.CreateCommand();
OracleTransaction transaction;
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = transaction;
try
{
cmd.CommandText = "insert into sys_role (role_id,role_name,super_id,description) values (seq_sys_role_role_id.nextval,'" + txtRoleName.Text.Trim() + "','0','" + txtRoleDirs.Text.Trim() + "')";
cmd.ExecuteNonQuery();
for (int i = 0; i < treeView2.Nodes.Count;i++ )
{
if (treeView2.Nodes[i].Checked == true)
{
cmd.CommandText = "insert into SYS_ROLE_MODULE (ID,role_id,module_id) values (seq_SYS_ROLE_MODULE_id.nextval,seq_sys_role_role_id.currval,'" + treeView2.Nodes[i].Tag.ToString() + "')";
cmd.ExecuteNonQuery();
for (int j = 0; j < treeView2.Nodes[i].Nodes.Count;j++ )
{
if (treeView2.Nodes[i].Nodes[j].Checked == true)
{
cmd.CommandText = "insert into SYS_ROLE_MODULE_ACTION t" +
" (t.id, t.roleid, t.modeleid, t.actioncode)" +
"values" +
" (seq_action.nextval,seq_sys_role_role_id.currval, '" + treeView2.Nodes[i].Tag.ToString() + "', '" + treeView2.Nodes[i].Nodes[j].Tag.ToString() + "')";
cmd.ExecuteNonQuery();
}
}
}
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
this.DialogResult = DialogResult.OK;
this.Close();
}
}
}
}
public void SetUp ()
{
connection = new OracleConnection (connection_string);
connection.Open ();
using (command = connection.CreateCommand ()) {
// create the tables
command.CommandText =
"create table utf8test (id number(10), text nvarchar2(64), text2 nvarchar2(64) )";
command.ExecuteNonQuery ();
}
}
private DataTable GetConduitDeviceData(string devicename, string wellname, string startDate, string endDate)
{
if (string.IsNullOrEmpty(devicename))
{
devicename = "%";
}
if (string.IsNullOrEmpty(wellname))
{
wellname = "F315";
}
string connectionString = GetOracleConnectionString();
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection())
{
try
{
connection.ConnectionString = connectionString;
try { connection.Close(); }
catch (Exception ex)
{ }
connection.Open();
//Console.WriteLine("State: {0}", connection.State);
//Console.WriteLine("ConnectionString: {0}",
// connection.ConnectionString);
OracleCommand command = connection.CreateCommand();
string sql = "select count(*) total, extract(month from start_Date) AS monthname,extract(year from start_Date)as yearname from v_Conduit_test where separator_id like '" + devicename + "' and conduit_name = '" + wellname + "' and validity_Status = 'valid test' ";
/*if (!includeAbondoned)
* {
* sql += "and vc.actual_status not like '%ABAN_%' ";
* }*/
if (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"]))
{
sql += "and start_date >= '" + startDate + "' and end_date <= '" + endDate + "' ";
}
sql += " group by extract(month from start_date),extract(year from start_Date) order by 3";
//sql += "order by 2";
command.CommandText = sql;
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(ds);
connection.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
connection.Close();
}
return(null);
}
}
public void SetUp ()
{
connection = new OracleConnection (connection_string);
connection.Open ();
using (command = connection.CreateCommand ()) {
// create the tables
command.CommandText =
"create table lob_test (id number(10), lobo blob)";
command.ExecuteNonQuery ();
}
}
private DataTable GetWellTestData(string devicename, string wellname, string startDate, string endDate)
{
if (string.IsNullOrEmpty(devicename))
{
devicename = "%";
}
if (string.IsNullOrEmpty(wellname))
{
wellname = "F315";
}
string connectionString = GetOracleConnectionString();
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection())
{
try
{
connection.ConnectionString = connectionString;
try { connection.Close(); }
catch (Exception ex)
{ }
connection.Open();
//Console.WriteLine("State: {0}", connection.State);
//Console.WriteLine("ConnectionString: {0}",
// connection.ConnectionString);
OracleCommand command = connection.CreateCommand();
string sql = "select vct.separator_id,NVL(vct.water,0) as notNullWater,NVL(vct.oil,0) as notNullOil,NVL(vct.bsw,0) as notNullBSW,NVL(vct.gas_out,0) as notNullGasOut,vct.* from v_conduit_test vct inner join v_conduit vc on VCT.CONDUIT_NAME=VC.CONDUIT_NAME where VCT.SEPARATOR_ID IS NOT NULL and vct.separator_id like '" + devicename + "' and vct.conduit_name = '" + wellname + "' and vct.validity_status='valid test' ";
//if (!includeAbondoned)
//{
// sql += "and vc.actual_status not like '%ABAN_%' ";
//}
if (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"]))
{
sql += "and vct.start_date >= '" + startDate + "' and vct.end_date <= '" + endDate + "' ";
}
sql += " order by start_date";
command.CommandText = sql;
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(ds);
connection.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
connection.Close();
}
return(null);
}
}
private DataSet GetDs()
{
MaterAttDS ds = new MaterAttDS();
string strSql = "select * from materialattachment_view " + User.pipepart + "";
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = strSql;
OracleDataAdapter oda = new OracleDataAdapter();
oda.SelectCommand = cmd;
oda.Fill(ds, "materialattachment");
conn.Close();
return ds;
}
private DataSet GetDs()
{
SpoolDataSet ds = new SpoolDataSet();
string strSql = "select * from SPOOLSTATISTICS_TAB";
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = strSql;
OracleDataAdapter oda = new OracleDataAdapter();
oda.SelectCommand = cmd;
oda.Fill(ds,"SPOOLSTATISTICS_TAB");
conn.Close();
return ds;
}
private DataSet GetDs()
{
SPLPIPEDataSet ds = new SPLPIPEDataSet();
string strSql = "select * from SPL_VIEW "+User.pipematerial+"";
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = strSql;
OracleDataAdapter oda = new OracleDataAdapter();
oda.SelectCommand = cmd;
oda.Fill(ds, "SPLPIPE_TAB");
conn.Close();
return ds;
}
public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection();
OracleCommand cmd = null;
try
{
BeginCase("CreateCommand");
cmd = con.CreateCommand();
Compare(cmd!=null, true);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
}
private DataSet GetDs()
{
TrayMaterialDeliveryTimeDs ds = new TrayMaterialDeliveryTimeDs();
string strSql = @"select t.*, t.rowid from tray_material_deliverytime_tab t";
string whereSql = User.traymatrialplan;
strSql += whereSql;
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = strSql;
OracleDataAdapter oda = new OracleDataAdapter();
oda.SelectCommand = cmd;
oda.Fill(ds, "TRAY_MATERIAL_DELIVERYTIME_TAB");
conn.Close();
return ds;
}
private DataSet GetDs()
{
TrayPrePlanDs ds = new TrayPrePlanDs();
string strSql = @"select t.*, t.rowid from tray_preplan_tab t";
string whereSql = User.traypreplan;
strSql += whereSql;
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = strSql;
OracleDataAdapter oda = new OracleDataAdapter();
oda.SelectCommand = cmd;
oda.Fill(ds, "TRAY_PREPLAN_TAB");
conn.Close();
return ds;
}
protected override void GatherKeyColumns(
DBSqlParserTable table
)
{
// Called after the table and column information is completed to
// identify which columns in the select-list are key columns for
// their table.
OracleCommand cmd = null;
OracleDataReader rdr = null;
try {
try {
cmd = _connection.CreateCommand();
cmd.Transaction = _connection.Transaction; // must set the transaction context to be the same as the command, or we'll throw when we execute.
string schemaName = CatalogCase(table.SchemaName);
string tableName = CatalogCase(table.TableName);
string synonymSchemaName = schemaName;
string synonymTableName = tableName;
// First, we have to "dereference" a synonym, if it was specified, because
// synonyms don't have catalog items stored for them, they're for the table
// or view that the synonym references.
cmd.CommandText = GetSynonymQueryStatement(schemaName, tableName);
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
synonymSchemaName = rdr.GetString(0);
synonymTableName = rdr.GetString(1);
}
rdr.Dispose();
// Now we have the real schema name and table name, go and derive the key
// columns
cmd.CommandText = GetConstraintQueryStatement(synonymSchemaName, synonymTableName);
rdr = cmd.ExecuteReader();
ArrayList constraintColumnNames = new ArrayList();
bool isUniqueConstraint;
if (true == (_moreConstraints = rdr.Read()))
{
while (GetConstraint(rdr, out isUniqueConstraint, constraintColumnNames))
{
bool foundAllColumns = true;
int constraintColumnCount = constraintColumnNames.Count;
DBSqlParserColumn[] constraintColumn = new DBSqlParserColumn[constraintColumnCount];
for (int j = 0; j < constraintColumnCount; ++j)
{
DBSqlParserColumn column = FindConstraintColumn(
schemaName,
tableName,
(string)constraintColumnNames[j]
);
if (null == column)
{
foundAllColumns = false;
break;
}
constraintColumn[j] = column;
}
if (foundAllColumns)
{
for (int j = 0; j < constraintColumnCount; ++j)
{
constraintColumn[j].SetAsKey(isUniqueConstraint);
}
break;
}
}
}
}
finally
{
if (null != cmd)
{
cmd.Dispose();
cmd = null;
}
if (null != rdr)
{
rdr.Dispose();
rdr = null;
}
}
}
catch { // Prevent exception filters from running in our space
throw;
}
}
protected override DbCommand CreateDbCommand()
{
return(new OracleCommand(con.CreateCommand(), this));
}
private DataTable ConnectAndQuery(string devicename, bool includeAbondoned, string startDate, string endDate)
{
if (String.IsNullOrEmpty(devicename))
{
devicename = "V-0516";
}
string connectionString = GetOracleConnectionString();
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection())
{
try
{
connection.ConnectionString = connectionString;
try { connection.Close(); }
catch (Exception ex)
{ }
connection.Open();
//Console.WriteLine("State: {0}", connection.State);
//Console.WriteLine("ConnectionString: {0}",
// connection.ConnectionString);
OracleCommand command = connection.CreateCommand();
string sqlDateFilter = (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"])) ? " and t.start_date >= '" + startDate + "' and t.end_date <= '" + endDate + "'" : "";
string sql = "select " +
"vct.separator_id, vct.conduit_name," +
"count(*) TotalTests, " +
"CASE vc.actual_status " +
"WHEN 'ABAN_01' THEN 0 WHEN 'ABAN_02' THEN 0 WHEN 'ABAN_03' THEN 0 WHEN 'ABAN_04' THEN 0 ELSE 1 END iconduitstatus," +
"sum(case when VALIDITY_STATUS='valid test' then 1 else 0 end) Valid, " +
"sum(case when VALIDITY_STATUS='invalid test' then 1 else 0 end) InValid," +
"sum(case when VALIDITY_STATUS='valid test' then 0 when VALIDITY_STATUS= 'invalid test' then 0 else 1 end) NotValidated, " +
"(select NVL(min(water),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") minwater," +
"(select NVL(max(water),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxwater," +
"(select NVL(min(oil),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") minoil," +
"(select NVL(max(oil),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxoil," +
"(select NVL(min(bsw),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") minbsw," +
"(select NVL(max(bsw),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxbsw," +
"(select NVL(min(gas_out),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") mingasout," +
"(select NVL(max(gas_out),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxgasout " +
"FROM V_CONDUIT_TEST VCT INNER JOIN V_CONDUIT VC ON VCT.CONDUIT_NAME=VC.CONDUIT_NAME " +
"WHERE VCT.SEPARATOR_ID IS NOT NULL and vct.separator_id='" + devicename + "' ";
if (!includeAbondoned)
{
sql += "and vc.actual_status not like '%ABAN_%' ";
}
if (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"]))
{
sql += "and vct.start_date >= '" + startDate + "' and vct.end_date <= '" + endDate + "' ";
}
sql += "group by VCT.conduit_name, vct.separator_id, vc.actual_status order by 2";
command.CommandText = sql;
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(ds);
connection.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
connection.Close();
}
return(null);
}
}
public void SetUp ()
{
if (connection_string == null)
return;
connection = new OracleConnection (connection_string);
connection.Open ();
using (command = connection.CreateCommand ()) {
// create the tables
command.CommandText =
"create table oratest (id number(10), text varchar2(64),"
+ " text2 varchar2(64) )";
command.ExecuteNonQuery ();
command.CommandText =
"create table culture_test (id number(10), value1 float,"
+ " value2 number(20,10), value3 number (20,10))";
command.ExecuteNonQuery ();
command.CommandText =
"create table oratypes_test (id NUMBER(10), value1 VARCHAR2(100),"
+ " value2 DATE)";
command.ExecuteNonQuery ();
command.CommandText =
"create or replace procedure params_pos_test (param1 in number,"
+ "param2 in number,param3 in number,result out number) as"
+ " begin result:=param3; end;";
command.ExecuteNonQuery ();
}
}
