protected override ExeResEdm UpdateDtToDB(DataTable dtInfos, string strComFields = "*")
{
ExeResEdm dBResEdm = new ExeResEdm();
string strTableName = dtInfos.TableName;
try
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = GetColumnsNameSql(strTableName, strComFields);
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(cmd);
adapter.UpdateCommand = new System.Data.OracleClient.OracleCommandBuilder(adapter).GetUpdateCommand();
adapter.Update(dtInfos.GetChanges());
dtInfos.AcceptChanges();
}
}
catch (Exception ex)
{
dBResEdm.Module = "UpdateDtToDB方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
return(dBResEdm);
}
protected override ExeResEdm SqlCMD(string sql, Func <DbCommand, object> fun, params DbParameter[] pms)
{
ExeResEdm dBResEdm = new ExeResEdm();
try
{
pms = ParameterPrepare(pms);
using (System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(connstr))
{
using (System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand(sql, con))
{
con.Open();
if (pms != null && pms.Length > 0)
{
cmd.Parameters.AddRange((pms));
}
var res = fun(cmd);
dBResEdm.ExeModel = res;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "SqlCMD方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
protected void btnConfirm_OTP_Click(object sender, EventArgs e)
{
try
{
String match_OTP = enter_OTP.Value;
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionStringGST))
{
connection.Open();
string queryString = "SELECT * FROM VP_OTP WHERE EMAIL = :email AND OTP = :match_OTP AND VALIDITY=:YES";
System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(queryString, connection);
command.Parameters.AddWithValue("email", Session["Otp_email"]);
command.Parameters.AddWithValue("match_OTP", match_OTP);
command.Parameters.AddWithValue("YES", "YES");
using (System.Data.OracleClient.OracleDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
HttpContext.Current.Response.Redirect("ResetPassword.aspx", false);
}
else
{
lblError.Text = "Check Your OTP!";
}
}
}
}
catch (Exception ex)
{
}
}
protected override ExeResEdm SqlCMD_DT(string cmdText, CommandType commandType, Func <DbDataAdapter, int> fun, params DbParameter[] parameters)
{
ExeResEdm dBResEdm = new ExeResEdm();
try
{
parameters = ParameterPrepare(parameters);
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = cmdText;
cmd.CommandType = commandType;
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange((parameters));
}
var da = new System.Data.OracleClient.OracleDataAdapter(cmd);
var res = fun(da);
dBResEdm.ExeNum = res;
}
}
catch (Exception ex)
{
dBResEdm.Module = "SqlCMD_DT 方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
return(dBResEdm);
}
/// <summary>
/// 打开连接
/// </summary>
public void ConnectionOpen()
{
if (_conn.State == ConnectionState.Closed)
{
_conn.Open();
}
}
public clsOracle()
{
string p_conn_db = "Data Source=127.0.0.1/ORCL;User ID=C##BSN_SISPK;PASSWORD=Bismillah;";
connOracle = new System.Data.OracleClient.OracleConnection(p_conn_db);
connOracle.Open();
}
protected override ExeResEdm ExecuteNonQueryFromSqlContianer(List <SqlContianer> ltSqls)
{
ExeResEdm dBResEdm = new ExeResEdm();
string curSQL = "";
try
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleTransaction oraOprate = conn.BeginTransaction();
try
{
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = oraOprate;
foreach (SqlContianer objOraSqlCon in ltSqls)
{
cmd.CommandText = objOraSqlCon.strSqlTxt;
curSQL = objOraSqlCon.strSqlTxt;
cmd.Parameters.Clear();
cmd.Parameters.AddRange(objOraSqlCon.ltOraParams.ToArray());
int intRes = cmd.ExecuteNonQuery();
dBResEdm.ExeNum += intRes;
if (objOraSqlCon.intExpectNums >= 0)
{
if (intRes != objOraSqlCon.intExpectNums)
{
throw new Exception("Update records not match the expect nums");
}
}
else if (objOraSqlCon.intExpectNums != Int16.MinValue)
{
if (intRes != 0 && intRes != objOraSqlCon.intExpectNums * -1)
{
throw new Exception("Update records not match the expect nums");
}
}
}
oraOprate.Commit();
}
catch (Exception ex)
{
oraOprate.Rollback();
dBResEdm.Module = "ExecuteNonQueryFromSqlContianer方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "ExecuteNonQueryFromSqlContianer方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
return(dBResEdm);
}
static void Main(string[] args)
{
/*
* Traditional TNS Definition
* ORA11G =
* (DESCRIPTION =
* (ADDRESS = (PROTOCOL = TCP)(HOST = ora11g-win.windsor.com)(PORT = 1521))
* (CONNECT_DATA =
* (SERVER = DEDICATED)
* (SID = ora11g)
* )
* )
*
* .Net Connection String information:
* "user id=NV_NSITE_DEV;password=memorial;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11g-win.windsor.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ora11g)))"
*
* Easy Connect Naming version (standard):
* "User Id=NV_NSITE_DEV;password=memorial;Data Source=host[port - default 1521][/servicename]"
*
* Easy Connect Naming version (dedicated server):
* "User Id=NV_NSITE_DEV;password=memorial;Data Source=ora11g-win.windsor.com/ora11g:dedicated"
*
*/
string connectionString = "user id=NV_NSITE_DEV;password=memorial;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11g-win.windsor.com)(PORT=1521))(CONNECT_DATA=(SERVICER=DEDICATED)(SID=ora11g)))";
//string connectionString = "Data Source=NV_NSITE_DEV/[email protected]:1521/ora11g";
Console.WriteLine("Connecting with System.Data.OracleClient");
#pragma warning disable 618
using (var connection = new System.Data.OracleClient.OracleConnection(connectionString))
#pragma warning restore 618
{
try
{
connection.Open();
Console.WriteLine("Connection Successful using System.Data.OracleClient (deprecated)");
connection.Close();
}
catch (OracleException ex)
{
Console.WriteLine(ex.ToString());
}
}
Console.WriteLine("Connecting with Oracle.DataAccess.Client.OracleConnection");
using (OracleConnection connection = new OracleConnection(connectionString))
{
try
{
connection.Open();
Console.WriteLine("Connection Successful using Oracle.DataAccess");
connection.Close();
}
catch (OracleException ex)
{
Console.WriteLine(ex.ToString());
}
}
}
public int GenerateNewId(string keyName, string connection, int defaultValue = 1)
{
int value = 0;
Guid guid = Guid.NewGuid();
System.Diagnostics.Trace.WriteLine(connection);
//TODO 不知道为什么 数据库连接不对
connection = "User ID=oahr;Password=oahr;Data Source=oahr";
using (var sqlConnection = new System.Data.OracleClient.OracleConnection(connection))
{
if (sqlConnection.State != ConnectionState.Open)
{
sqlConnection.Open();
}
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = CommandType.Text;
for (int i = 0; i < 3; i++)
{
command.CommandText =
(string.Format("select MaxRecID from Sys_MaxRecId where TableName ='{0}'", keyName));
value = int.Parse((command.ExecuteScalar() ?? "0").ToString());
if (value <= 0)
{
value = defaultValue;
command.CommandText =
string.Format(
"insert into Sys_MaxRecId(TableName,MaxRecID,Remark) values('{0}', {1}, '{2}')",
keyName, defaultValue, guid);
command.ExecuteNonQuery();
}
else
{
value += 1;
command.CommandText =
(string.Format(
"update Sys_MaxRecId set MaxRecID = MaxRecID + 1, Remark = '{0}' where TableName = '{1}'",
guid, keyName));
command.ExecuteNonQuery();
}
command.CommandText =
(string.Format("select Remark from Sys_MaxRecId where TableName ='{0}'",
keyName));
if ((command.ExecuteScalar() ?? Guid.Empty).ToString() == guid.ToString())
{
break;
}
}
return(value);
}
}
}
private string GetDDLForTable(string tableName, string owner)
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(strCon1))
{
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = string.Format("select SYS.DBMS_METADATA.GET_DDL('TABLE','{0}','{1}') from dual", tableName, owner);
conn.Open();
object result = cmd.ExecuteScalar();
return(result.ToString());
}
}
private void bOK_Click(object sender, EventArgs e)
{
string strServer, strUser, strPassword;
string Return_Value;
System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection();
strServer = textBoxServer.Text;
strUser = textBoxLog.Text;
strPassword = textBoxPass.Text;
Return_Value = INIExists(inifile);
if (!String.IsNullOrEmpty(Return_Value))
{
MessageBox.Show(Return_Value);
return;
}
string connStr = "Data Source=" + strServer + ";User Id=" + strUser + ";Password="******";Integrated Security=no;";
con.ConnectionString = connStr;
try
{
con.Open();
}
catch (Exception ex)
{
con.Close();
MessageBox.Show("数据库连接失败！" + ex.Message, "仪器数据接收", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
strServer = EncryptDES(strServer, "zlsofthis");
strUser = EncryptDES(strUser, "zlsofthis");
strPassword = EncryptDES(strPassword, "zlsofthis");
long OpStation = WritePrivateProfileString("EQUIPMENT", "DBSERVER", strServer, System.AppDomain.CurrentDomain.BaseDirectory.ToString() + inifile);
if (OpStation == 0)
{
MessageBox.Show("未找到段落【EQUIPMENT】或关键字【DBSERVER】!");
}
OpStation = WritePrivateProfileString("EQUIPMENT", "LOGID", strUser, System.AppDomain.CurrentDomain.BaseDirectory.ToString() + inifile);
if (OpStation == 0)
{
MessageBox.Show("未找到段落【EQUIPMENT】或关键字【LOGID】!");
}
OpStation = WritePrivateProfileString("EQUIPMENT", "LOGPASS", strPassword, System.AppDomain.CurrentDomain.BaseDirectory.ToString() + inifile);
if (OpStation == 0)
{
MessageBox.Show("未找到段落【EQUIPMENT】或关键字【LOGPASS】!");
}
this.DialogResult = DialogResult.OK;
}
public int GenerateNewId(string keyName, string connection, int defaultValue = 1)
{
int value = 0;
Guid guid = Guid.NewGuid();
System.Diagnostics.Trace.WriteLine(connection);
//TODO 不知道为什么 数据库连接不对
connection = "User ID=oahr;Password=oahr;Data Source=oahr";
using (var sqlConnection = new System.Data.OracleClient.OracleConnection(connection))
{
if (sqlConnection.State != ConnectionState.Open)
sqlConnection.Open();
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = CommandType.Text;
for (int i = 0; i < 3; i++)
{
command.CommandText =
(string.Format("select MaxRecID from Sys_MaxRecId where TableName ='{0}'", keyName));
value = int.Parse((command.ExecuteScalar() ?? "0").ToString());
if (value <= 0)
{
value = defaultValue;
command.CommandText =
string.Format(
"insert into Sys_MaxRecId(TableName,MaxRecID,Remark) values('{0}', {1}, '{2}')",
keyName, defaultValue, guid);
command.ExecuteNonQuery();
}
else
{
value += 1;
command.CommandText =
(string.Format(
"update Sys_MaxRecId set MaxRecID = MaxRecID + 1, Remark = '{0}' where TableName = '{1}'",
guid, keyName));
command.ExecuteNonQuery();
}
command.CommandText =
(string.Format("select Remark from Sys_MaxRecId where TableName ='{0}'",
keyName));
if ((command.ExecuteScalar() ?? Guid.Empty).ToString() == guid.ToString())
{
break;
}
}
return value;
}
}
}
//Value which you want to pass
public void btnGet_OTP_Click(object sender, EventArgs e)
{
try
{
// to save the Email_id and OTP in database
String email = send_otp.Value;
Session["Otp_email"] = email;
string strNewOtp = GenerateOtp().ToString();
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionStringGST))
{
connection.Open();
string queryString = "update VP_OTP SET VALIDITY =:NO WHERE EMAIL = :email AND VALIDITY= :YES ";
System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(queryString, connection);
command.Parameters.AddWithValue("email", email);
command.Parameters.AddWithValue("YES", "YES");
command.Parameters.AddWithValue("NO", "NO");
int rowsUpdated = command.ExecuteNonQuery();
string QueryString = "insert into VP_OTP (EMAIL,OTP, VALIDITY) values (:email,:OTP,:VALIDITY)";
System.Data.OracleClient.OracleCommand Command = new System.Data.OracleClient.OracleCommand(QueryString, connection);
Command.Parameters.AddWithValue("email", email);
Command.Parameters.AddWithValue("OTP", strNewOtp);
Command.Parameters.AddWithValue("VALIDITY", "YES");
int RowsUpdated = Command.ExecuteNonQuery();
HttpContext.Current.Response.Redirect("OTP.aspx", false);
}
// to send the random OTP in email
// MailMessage msg = new MailMessage();
// msg.From = new MailAddress("*****@*****.**");
// msg.To.Add(email);
// msg.Body = "Dear " + email + ", <br/><br/> Your OTP is " + strNewOtp + " for Vendor Registration for RCF. <br/> Kindly ignore this email if already submitted. <br/><br/>This is system generated mail.<br/>Please do not reply.<br/><br/>Regards,<br/>RCF Ltd.";
//Response.Write(msg.Body);
// msg.IsBodyHtml = true;
// msg.Subject = "OTP for Verification for Grievance Monitoring System";
/// SmtpClient smt = new SmtpClient("webmail.rcfltd.com");
// smt.Port = 25;
// smt.Credentials = new NetworkCredential("*****@*****.**", "asdfgh@123");
//smt.EnableSsl = true;
// smt.Send(msg);
//string script = "<script>alert('Mail Sent Successfully');self.close();</script>";
//this.ClientScript.RegisterClientScriptBlock(this.GetType(), "sendMail", script);
}
catch (Exception ex)
{
}
}
//Connection to Oracle database
public System.Data.DataTable OracOLEds(string SqlString, string constring, int rowlimit)
{
DataTable dt = new DataTable();
if (rowlimit > 0)
{
try
{
using (System.Data.OracleClient.OracleConnection OracConnect = new System.Data.OracleClient.OracleConnection(constring))
{
OracConnect.Open();
System.Data.OracleClient.OracleCommand OraCmd = new System.Data.OracleClient.OracleCommand(SqlString, OracConnect);
System.Data.OracleClient.OracleDataReader Oraxreader = OraCmd.ExecuteReader();
DataSet ds = new DataSet();
ds.Load(Oraxreader, LoadOption.OverwriteChanges, "Result");
var ddt = ds.Tables[0].AsEnumerable().Take(rowlimit);
dt = ddt.CopyToDataTable <DataRow>();
Oraxreader.Close();
OracConnect.Close();
}
}
catch (Exception exOrac)
{
var ex1 = exOrac.Message;
}
}
else
{
try
{
using (System.Data.OracleClient.OracleConnection OracConnect = new System.Data.OracleClient.OracleConnection(constring))
{
OracConnect.Open();
System.Data.OracleClient.OracleCommand OraCmd = new System.Data.OracleClient.OracleCommand(SqlString, OracConnect);
System.Data.OracleClient.OracleDataReader Oraxreader = OraCmd.ExecuteReader();
DataSet ds = new DataSet();
ds.Load(Oraxreader, LoadOption.OverwriteChanges, "Result");
dt = ds.Tables[0];
Oraxreader.Close();
OracConnect.Close();
}
}
catch (Exception exOrac)
{
var ex1 = exOrac.Message;
}
}
return(dt);
}
public void Login_Load(object sender, EventArgs e)
{
System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection();
con.ConnectionString = OracleHelper.GetConnectionstring();
try
{
con.Open();
}
catch (Exception ex)
{
con.Close();
return;
}
this.DialogResult = DialogResult.OK;
}
protected void GrdViewDocs_ItemCommand(object sender, GridCommandEventArgs e)
{
if (e.CommandName == "Select")
{
GridDataItem dataitem = e.Item as GridDataItem;
//DataTable dtcon = CM_Main.SelectJob("CASE11", dataitem["REQUEST_ID"].Text, "", "", "", "", System.DateTime.Now);
//grdRequest.DataSource = dtcon;
//grdRequest.DataBind();
// int idx = dataitem["REQUEST_ID"].Text;
// Label lbl = dataitem["ID"].Text;
System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(ConfigurationManager.ConnectionStrings["CGConnectionString"].ToString());
System.Data.OracleClient.OracleCommand myCommand = new System.Data.OracleClient.OracleCommand("SELECT * FROM wf_cm_docs_jobwise_blob WHERE ID = '" + dataitem["ID"].Text + "'", conn);
conn.Open();
System.Data.OracleClient.OracleDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default);
try
{
while (myReader.Read())
{
System.Data.OracleClient.OracleLob myLob = myReader.GetOracleLob(myReader.GetOrdinal("DOCUMENT"));
if (!myLob.IsNull)
{
string FN = myReader.GetString(myReader.GetOrdinal("DOCUMENT_NAME"));
//Use buffer to transfer data
byte[] b = new byte[myLob.Length];
//Read data from database
myLob.Read(b, 0, (int)myLob.Length);
Response.AddHeader("content-disposition", "attachment;filename=" + FN);
Response.ContentType = "application/octectstream";
Response.BinaryWrite(b);
Response.End();
}
}
}
finally
{
myReader.Close();
conn.Close();
}
}
}
public Form1()
{
InitializeComponent();
oracleConnection = oracleConnection1;
oracleConnection.Open();
tb_email.Location = new Point(400 - tb_email.Width / 2, 270);
tb_password.Location = new Point(400 - tb_password.Width / 2, 302);
tb_email.BringToFront();
lb_join.Text = "";
lb_join.Enabled = false;
this.SetStyle(ControlStyles.DoubleBuffer | ControlStyles.AllPaintingInWmPaint | ControlStyles.UserPaint, true);
Invalidate();
}
protected void btnSignUp_Click(object sender, EventArgs e)
{
try
{
String username = user_name.Value;
String email = user_email.Value;
String password = user_password.Value;
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionStringGST))
{
connection.Open();
string queryString = "SELECT * FROM VP_LOGIN WHERE EMAIL_ID = :useremail";
System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(queryString, connection);
command.Parameters.AddWithValue("useremail", email);
using (System.Data.OracleClient.OracleDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
lblError_email.Text = "Email already exist!";
}
else
{
string QueryString = "insert into VP_LOGIN (NAME,EMAIL_ID,PASSWORD) values (:username,:email,:password)";
System.Data.OracleClient.OracleCommand Command = new System.Data.OracleClient.OracleCommand(QueryString, connection);
Command.Parameters.AddWithValue("username", username);
Command.Parameters.AddWithValue("email", email);
Command.Parameters.AddWithValue("password", password);
int rowsUpdated = Command.ExecuteNonQuery();
if (rowsUpdated == 0)
{
Response.Write("Record not inserted");
}
else
{
HttpContext.Current.Response.Redirect("Login.aspx", false);
}
}
}
}
}
catch (Exception ex)
{
lblError.Text = "Something Went Wrong!";
}
}
protected void btnResetPassword_Click(object sender, EventArgs e)
{
try
{
String newpassword = New_password.Value;
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionStringGST))
{
connection.Open();
string queryString = "update VP_LOGIN SET PASSWORD =:New_password WHERE EMAIL_ID = :email";
System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(queryString, connection);
command.Parameters.AddWithValue("email", Session["Otp_email"]);
command.Parameters.AddWithValue("New_password", newpassword);
int rowsUpdated = command.ExecuteNonQuery();
Response.Redirect("Login.aspx");
}
}
catch (Exception ex)
{
Response.Write("pm");
}
}
protected void DataList2_SelectedIndexChanged(object sender, EventArgs e)
{
int idx = DataList2.SelectedIndex;
Label lbl = (Label)DataList2.Items[idx].FindControl("Label2");
System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(ConfigurationManager.ConnectionStrings["CGConnectionString"].ToString());
System.Data.OracleClient.OracleCommand myCommand = new System.Data.OracleClient.OracleCommand("SELECT * FROM wf_it_images WHERE ID = '" + lbl.Text + "'", conn);
conn.Open();
System.Data.OracleClient.OracleDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default);
try
{
while (myReader.Read())
{
System.Data.OracleClient.OracleLob myLob = myReader.GetOracleLob(myReader.GetOrdinal("IMAGE"));
if (!myLob.IsNull)
{
string FN = myReader.GetString(myReader.GetOrdinal("IMAGE_NAME"));
//Use buffer to transfer data
byte[] b = new byte[myLob.Length];
//Read data from database
myLob.Read(b, 0, (int)myLob.Length);
Response.AddHeader("content-disposition", "attachment;filename=" + FN);
Response.ContentType = "application/octectstream";
Response.BinaryWrite(b);
Response.End();
}
}
}
finally
{
myReader.Close();
conn.Close();
}
}
protected void btnLogin_Click(object sender, EventArgs e)
{
try
{
String useremail = txtemail.Value;
Session["Login_email"] = useremail;
String password = txtpassword.Value;
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionStringGST))
{
connection.Open();
string queryString = "SELECT * FROM VP_LOGIN WHERE EMAIL_ID = :useremail AND PASSWORD = :password";
System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(queryString, connection);
command.Parameters.AddWithValue("useremail", useremail);
command.Parameters.AddWithValue("password", password);
using (System.Data.OracleClient.OracleDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
/*Session["username"] = Convert.ToString(reader["EMAIL_ID"]);
* Session["name"] = Convert.ToString(reader["NAME"]);
* Session["contact"] = Convert.ToString(reader["CONTACT"]);*/
HttpContext.Current.Response.Redirect("VendorRegistration.aspx", false);
}
else
{
lblError.Text = "Check Your User Name and Password!";
}
}
}
}
catch (Exception ex)
{
lblError.Text = "Something Went Wrong!";
}
}
/// <summary>
/// 测试数据库连接
/// </summary>
/// <returns></returns>
private bool TestDBConnect()
{
if (string.IsNullOrEmpty(txtSelfConnStr.Text.Trim()))
{
MessageBoxEx.Show("请先输入数据库连接字符串", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
try
{
OracleDapperDber dber = new OracleDapperDber(txtSelfConnStr.Text.Trim());
using (System.Data.OracleClient.OracleConnection conn = dber.CreateConnection() as System.Data.OracleClient.OracleConnection)
{
conn.Open();
}
return(true);
}
catch (Exception ex)
{
MessageBoxEx.Show("数据库连接失败，" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
}
/// <summary>
/// ²âÊÔÊý¾Ý¿âÁ¬½Ó
/// </summary>
/// <returns></returns>
private bool TestDBConnect()
{
if (string.IsNullOrEmpty(txtSelfConnStr.Text.Trim()))
{
MessageBoxEx.Show("ÇëÏÈÊäÈëÊý¾Ý¿âÁ¬½Ó×Ö·û´®", "Ìáʾ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
try
{
OracleDapperDber dber = new OracleDapperDber(txtSelfConnStr.Text.Trim());
using (System.Data.OracleClient.OracleConnection conn = dber.CreateConnection() as System.Data.OracleClient.OracleConnection)
{
conn.Open();
}
return(true);
}
catch (Exception ex)
{
MessageBoxEx.Show("Êý¾Ý¿âÁ¬½Óʧ°Ü£¬" + ex.Message, "Ìáʾ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
}
//total marks
//procedure
private void button1_Click(object sender, EventArgs e)
{
try
{
String oradb = "Data Source=DESKTOP-K6AG53J;Persist Security Info=True;User ID=system;Password=student;";
System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(oradb);
//conn.Open();
System.Data.OracleClient.OracleCommand comm = new System.Data.OracleClient.OracleCommand();
comm.Connection = conn;
comm.CommandText = "tot_marks";
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add("s_id", System.Data.OracleClient.OracleType.Number).Value = s1;
comm.Parameters.Add("co_id", System.Data.OracleClient.OracleType.Number).Value = comboBox1.Text;
comm.Parameters.Add("cout", System.Data.OracleClient.OracleType.Number).Direction = ParameterDirection.Output;
conn.Open();
comm.ExecuteNonQuery();
textBox1.Text = (comm.Parameters["cout"].Value).ToString();
conn.Close();
}
catch (IndexOutOfRangeException ex)
{
MessageBox.Show("Invalid Details!");
}
}
/// <summary>
/// 执行连接
/// </summary>
/// <returns></returns>
public string DoConn()
{
if (this.No == "local")
{
return("本地连接不需要测试是否连接成功.");
}
if (this.DBSrcType == Sys.DBSrcType.Localhost)
{
throw new Exception("@在该系统中只能有一个本地连接.");
}
string dsn = "";
if (this.DBSrcType == Sys.DBSrcType.SQLServer)
{
try
{
dsn = "Password="******";Persist Security Info=True;User ID=" + this.UserID + ";Initial Catalog=" + this.DBName + ";Data Source=" + this.IP + ";Timeout=999;MultipleActiveResultSets=true";
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = dsn;
conn.Open();
conn.Close();
//删除应用.
try
{
BP.DA.DBAccess.RunSQL("Exec sp_droplinkedsrvlogin " + this.No + ",Null ");
BP.DA.DBAccess.RunSQL("Exec sp_dropserver " + this.No);
}
catch
{
}
//创建应用.
string sql = "";
sql += "sp_addlinkedserver @server='" + this.No + "', @srvproduct='', @provider='SQLOLEDB', @datasrc='" + this.IP + "'";
BP.DA.DBAccess.RunSQL(sql);
//执行登录.
sql = "";
sql += " EXEC sp_addlinkedsrvlogin '" + this.No + "','false', NULL, '" + this.UserID + "', '" + this.Password + "'";
BP.DA.DBAccess.RunSQL(sql);
return("恭喜您，该(" + this.Name + ")连接配置成功。");
}
catch (Exception ex)
{
return(ex.Message);
}
}
if (this.DBSrcType == Sys.DBSrcType.Oracle)
{
try
{
dsn = "user id=" + this.UserID + ";data source=" + this.DBName + ";password="******";Max Pool Size=200";
System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection();
conn.ConnectionString = dsn;
conn.Open();
conn.Close();
return("恭喜您，该(" + this.Name + ")连接配置成功。");
}
catch (Exception ex)
{
return(ex.Message);
}
}
if (this.DBSrcType == Sys.DBSrcType.MySQL)
{
try
{
dsn = "Data Source=" + this.IP + ";Persist Security info=True;Initial Catalog=" + this.DBName + ";User ID=" + this.UserID + ";Password="******";";
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection();
conn.ConnectionString = dsn;
conn.Open();
conn.Close();
return("恭喜您，该(" + this.Name + ")连接配置成功。");
}
catch (Exception ex)
{
return(ex.Message);
}
}
// if (this.DBSrcType == Sys.DBSrcType.Infomax)
//{
// try
// {
////Host=10.0.2.36;Service=8001;Server=niosserver; Database=ccflow; UId=npmuser; Password=npmoptr2012;Database locale=en_US.819;Client Locale=en_US.CP1252
// dsn = "Data Source="+this.IP+";Persist Security info=True;Initial Catalog="+this.DBName+";User ID="+this.UserID+";Password="******";";
// MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection();
// conn.ConnectionString = dsn;
// conn.Open();
// conn.Close();
// return "恭喜您，该(" + this.Name + ")连接配置成功。";
// }
// catch (Exception ex)
// {
// return ex.Message;
// }
//}
return("没有涉及到的连接测试类型...");
}
string Run(string db,string user,string password,string sql)
{
string oradb = String.Format("Data Source={0};User Id={1};Password={2}",db,user,password);
string result="";
if(sql=="") return "";
using(System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(oradb))
{
try
{
conn.Open();
using(System.Data.OracleClient.OracleCommand cmd=conn.CreateCommand())
{
//cmd.BindByName=true;
cmd.CommandText=sql.Replace("\r\n","\n");
System.Data.OracleClient.OracleDataReader dr=cmd.ExecuteReader();
string[] fields=new string[dr.FieldCount];
if(dr.Read())
{
for(int i=0;i<dr.FieldCount;i++)
{
fields[i]=dr.GetValue(i).ToString();
}
}
dr.Close();
result=string.Join(",",fields);
}
return result;
}
catch(Exception e)
{
return "Error: "+e.Message;
}
}
}
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();
}
}
static void Main(string[] args)
{
Console.WriteLine("1. Test dotNetCore.Data.OracleClient with native OCI client");
Console.WriteLine("2. Test Oracle.ManagedDataAccess.Client");
Console.WriteLine("Other - exit");
var key = Console.ReadKey().KeyChar;
if (key == '1')
{
Console.WriteLine("Test dotNetCore.Data.OracleClient with Oci client.");
Console.WriteLine("create OracleConnection object...");
//https://stackoverflow.com/questions/9218847/how-do-i-handle-database-connections-with-dapper-in-net
using (System.Data.Common.DbConnection connection = new System.Data.OracleClient.OracleConnection("Data Source = RHEL5; User ID = TEST; Password = Passw0rd1 "))
{
Console.WriteLine("Open connection...");
connection.Open();
Console.WriteLine("Create command...");
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT table_name, tablespace_name, num_rows FROM user_tables WHERE tablespace_name is not null and num_rows > 0 order by num_rows desc";
Console.WriteLine("Execute reader...");
using (var reader = command.ExecuteReader())
{
Console.WriteLine("*** User tables (sample): ***");
while (reader.Read())
{
string tableName = reader.GetString(reader.GetOrdinal("TABLE_NAME"));
string tablespace_name = reader.GetString(reader.GetOrdinal("TABLESPACE_NAME"));
int rows = reader.GetInt32(reader.GetOrdinal("NUM_ROWS"));
Console.WriteLine(tableName + " in tablespace " + tablespace_name + " has " + rows.ToString() + " rows.");
}
;
}
Console.WriteLine("End reader...");
Console.WriteLine();
Console.WriteLine("*** Test NLS_LANG settings ***");
command.CommandText = "select 'some text in English language' as a, '储物组合带门/抽屉, 白色 卡维肯, 因维肯 白蜡木贴面' as b, 'ľščťžýáííéô§úä' as c from dual";
var reader1 = command.ExecuteReader();
reader1.Read();
string sEnglish = reader1.GetString(0);
string sChinese = reader1.GetString(1);
string sSlovak = reader1.GetString(2);
Console.WriteLine("English from db: " + sEnglish);
Console.WriteLine("Chinese from db: " + sChinese);
Console.WriteLine("Slovak from db: " + sSlovak);
Console.WriteLine("Chinese from the code: 储物组合带门 / 抽屉, 白色 卡维肯, 因维肯 白蜡木贴面");
Console.WriteLine("Slovak from the code: ľščťžýáííéô§úä");
}
}
Console.WriteLine("Done Oci Client");
Console.WriteLine("Press key to exit");
key = Console.ReadKey().KeyChar;
}
else if (key == '2')
{
Console.WriteLine("Test Oracle.ManagedDataAccess.Client.");
Console.WriteLine("create OracleConnection object...");
//1. Full TNS string in connect string
using (System.Data.Common.DbConnection connection = new OracleConnection("Data Source = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.103.67)(PORT = 1521)) )(CONNECT_DATA = (SID = ORCL))); User ID = TEST; Password = Passw0rd1 "))
//2 Withouth TNS , with service name = " select value from v$parameter where name='service_names' "
// using (System.Data.Common.DbConnection connection = new OracleConnection("Data Source = 10.100.103.67:1521/orcl.test.sk; User ID = TEST; Password = Passw0rd1 "))
// With alias from TNS
// using (OracleConnection connection = new OracleConnection(" User ID = TEST; Password = Passw0rd1; Data Source = RHEL5;"))
{
Console.WriteLine("Open connection...");
connection.Open();
Console.WriteLine("Create command...");
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT table_name, tablespace_name, num_rows FROM user_tables WHERE tablespace_name is not null and num_rows > 0 order by num_rows desc";
Console.WriteLine("Execute reader...");
using (var reader = command.ExecuteReader())
{
Console.WriteLine("*** User tables (sample): ***");
while (reader.Read())
{
string tableName = reader.GetString(reader.GetOrdinal("TABLE_NAME"));
string tablespace_name = reader.GetString(reader.GetOrdinal("TABLESPACE_NAME"));
int rows = reader.GetInt32(reader.GetOrdinal("NUM_ROWS"));
Console.WriteLine(tableName + " in tablespace " + tablespace_name + " has " + rows.ToString() + " rows.");
}
;
}
Console.WriteLine("End reader...");
Console.WriteLine();
Console.WriteLine("*** Test NLS_LANG settings ***");
command.CommandText = "select 'some text in English language' as a, '储物组合带门/抽屉, 白色 卡维肯, 因维肯 白蜡木贴面' as b, 'ľščťžýáííéô§úä' as c from dual";
var reader1 = command.ExecuteReader();
reader1.Read();
string sEnglish = reader1.GetString(0);
string sChinese = reader1.GetString(1);
string sSlovak = reader1.GetString(2);
Console.WriteLine("English from db: " + sEnglish);
Console.WriteLine("Chinese from db: " + sChinese);
Console.WriteLine("Slovak from db: " + sSlovak);
Console.WriteLine("Chinese from the code: 储物组合带门 / 抽屉, 白色 卡维肯, 因维肯 白蜡木贴面");
Console.WriteLine("Slovak from the code: ľščťžýáííéô§úä");
}
}
Console.WriteLine("Done Oracle.ManagedDataAccess.Client");
Console.WriteLine("Press key to exit");
key = Console.ReadKey().KeyChar;
}
}
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);
}
protected void btnFormSubmit_Click(object sender, EventArgs e)
{
try
{
String selectp = Request.Form.Get("title");
String Fname = fname.Value;
String Mname = mname.Value;
String Lname = lname.Value;
String Username = selectp + " " + Fname + " " + Mname + " " + Lname;
String House_no = house_no.Value;
String Street = street.Value;
String City_code = city_code.Value;
String City = city.Value;
String State = state.Value;
String PIN = pin.Value;
String Country = country.Value;
String Address = House_no + ", " + Street + ", " + City_code + ", " + City + ", " + State + ", " + PIN + ", " + Country;
String Contact_person = contact_person.Value;
String Telephone_no = telephone_no.Value;
String Mobile_no = mobile_no.Value;
String Fax = fax.Value;
//String Email = email_id.Value;
String Pan = pan_no.Value;
String Gst_no = gst_no.Value;
String Industry = industry.Value;
String Micro = micro.Value;
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionStringGST))
{
connection.Open();
string queryString = "insert into VP_REGISTRATION (NAME, ADDRESS, CONTACT_PERSON,EMAIL,FAX, GST_NO, INDUSTRY, MICRO_OR_SSI_STATUS, MOBILE_NO, PAN_NO, TELEPHONE_NO_EXT) values (:username , :Address, :C_person, :Email, :Fax, :Gst, :Industry, :Micro, :Mob_no, :Pan_no, :Telephone_no)";
System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(queryString, connection);
command.Parameters.AddWithValue("username", Username);
command.Parameters.AddWithValue("Address", Address);
command.Parameters.AddWithValue("C_person", Contact_person);
command.Parameters.AddWithValue("Email", Session["Login_email"]);
command.Parameters.AddWithValue("Fax", Fax);
command.Parameters.AddWithValue("Gst", Gst_no);
command.Parameters.AddWithValue("Industry", Industry);
command.Parameters.AddWithValue("Micro", Micro);
command.Parameters.AddWithValue("Mob_no", Mobile_no);
command.Parameters.AddWithValue("Pan_no", Pan);
command.Parameters.AddWithValue("Telephone_no", Telephone_no);
int rowsUpdated = command.ExecuteNonQuery();
if (rowsUpdated == 0)
{
Response.Write("Record not inserted");
}
else
{
HttpContext.Current.Response.Redirect("Login.aspx", false);
}
}
}
catch (Exception ex)
{
lblError.Text = "Something Went Wrong!";
}
}
protected override ExeResEdm UpdateDsToDB(DataSet dsTables, Dictionary <string, string> dicDtMainFields = null)
{
ExeResEdm dBResEdm = new ExeResEdm();
int n = 0;
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;
foreach (DataTable dtTemp in dsTables.Tables)
{
string strComFields = "*";
if (dicDtMainFields != null && dicDtMainFields.Count > 0 && dicDtMainFields.ContainsKey(dtTemp.TableName))
{
strComFields = !string.IsNullOrEmpty(dicDtMainFields[dtTemp.TableName]) ? dicDtMainFields[dtTemp.TableName]: strComFields;
}
cmd.CommandText = GetColumnsNameSql(dtTemp.TableName, strComFields);
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(cmd);
var dtChanges = dtTemp.GetChanges();
adapter.FillSchema(dtChanges, SchemaType.Mapped); //new added
if (dtChanges != null) //是添加或更新
{
adapter.UpdateCommand = new System.Data.OracleClient.OracleCommandBuilder(adapter).GetUpdateCommand();
n += adapter.Update(dtChanges);
dtTemp.AcceptChanges();
}
else //是删除
{
adapter.DeleteCommand = new System.Data.OracleClient.OracleCommandBuilder(adapter).GetDeleteCommand();
for (int i = dtTemp.Rows.Count - 1; i >= 0; i--)
{
dtTemp.Rows[i].Delete();
}
n += adapter.Update(dtTemp);
}
}
dsTables.AcceptChanges();
tsOprate.Commit();
}
catch (Exception ex)
{
tsOprate.Rollback();
dBResEdm.Module = "UpdateDsToDB方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "UpdateDsToDB方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
dBResEdm.ExeNum = n;
return(dBResEdm);
}
public void Open()
{
_con.Open();
}
