public ComDataReader ExecuteReader()
{
ComDataReader dr = new ComDataReader();
dr.DataReader = _cmd.ExecuteReader();
return(dr);
}
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)
{
}
}
/// <summary>
/// 返回指定Sql语句的DbDataReader
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>DBDataReader对象</returns>
public virtual DbDataReader GetDataReader(string sqlstr)
{
//程序不能返回抽象类，故用分数据访问分工来生成DataReader对象
if (databaseType == "Oracle")
{
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
System.Data.OracleClient.OracleDataReader oraDr = cmd.ExecuteReader();
return(oraDr);
}
else if (databaseType == "SqlServer")
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataReader sqlDr = cmd.ExecuteReader();
return(sqlDr);
}
else if (databaseType == "Sybase")
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
System.Data.OleDb.OleDbDataReader oleDr = cmd.ExecuteReader();
return(oleDr);
}
else
{
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader odbcDr = cmd.ExecuteReader();
return(odbcDr);
}
}
/// <summary>
/// 执行sql
/// </summary>
/// <param name="strSql"></param>
/// <param name="strDataSet"></param>
/// <param name="strXSLFileName"></param>
/// <param name="SettingXml"></param>
/// <returns></returns>
public int ExecQuery(string strSql, ref string strDataSet, string strXSLFileName, string SettingXml)
{
System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(strConn);
System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand();
command.Connection = con;
command.CommandType = System.Data.CommandType.Text;
command.Parameters.Clear();
command.CommandText = strSql + "";
try
{
System.Data.OracleClient.OracleDataReader TempReader1;
TempReader1 = command.ExecuteReader();
XmlDocument doc = new XmlDocument();
XmlNode root;
XmlElement node, row;
doc.AppendChild(doc.CreateXmlDeclaration("1.0", "GB2312", ""));
if (strXSLFileName != null && strXSLFileName != "")
{
string PI = "type='text/xsl' href='" + strXSLFileName + "'";
System.Xml.XmlProcessingInstruction xmlProcessingInstruction = doc.CreateProcessingInstruction("xml-stylesheet", PI);
doc.AppendChild(xmlProcessingInstruction);
}
string Header = doc.OuterXml + "\n<DataSet>\n" + SettingXml;
doc = new XmlDocument();
root = doc.CreateElement("Table");
doc.AppendChild(root);
while (TempReader1.Read())
{
row = doc.CreateElement("Row");
for (int i = 0; i < TempReader1.FieldCount; i++)
{
node = doc.CreateElement("Column");
node.SetAttribute("Name", TempReader1.GetName(i).ToString());
node.InnerText = TempReader1[i].ToString() + "";
row.AppendChild(node);
}
root.AppendChild(row);
}
strDataSet = Header + "\n" + doc.OuterXml + "\n</DataSet>";
TempReader1.Close();
}
catch (Exception ex)
{
this.Err = "执行语句产生错误!" + ex.Message;
this.ErrorException = ex.InnerException + "+ " + ex.Source;
this.ErrCode = strSql;
this.WriteErr();
return(-1);
}
WriteDebug("执行查询sql语句！" + strSql);
return(0);
}
//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);
}
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();
}
}
}
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!";
}
}
public void InsertCLOB(string SQLStatement, string str)
{
if (SQLStatement.Length > 0)
{
if (connOracle.State.ToString().Equals("Open"))
{
byte[] newvalue = System.Text.Encoding.Unicode.GetBytes(str);
sqlCommandOracle =
new System.Data.OracleClient.OracleCommand(SQLStatement, connOracle);
rstOracle = sqlCommandOracle.ExecuteReader();
rstOracle.Read();
txn = connOracle.BeginTransaction();
clob = rstOracle.GetOracleLob(0);
clob.Write(newvalue, 0, newvalue.Length);
txn.Commit();
}
}
}
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();
}
}
// https://social.msdn.microsoft.com/Forums/en-US/dc1b053d-f0d5-48f8-ad82-fb6d96d27f80/how-to-insert-or-update-blob-data-for-sqlserver-based-on-buffersize?forum=adodotnetdataproviders
public void WriteLargeFileChunckedOracle(string path)
{
byte[] buffer = new byte[1024];
using (System.Data.OracleClient.OracleCommand cmd = null)
{
using (System.Data.OracleClient.OracleDataReader dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
// this code segment works well for Oracle database
System.Data.OracleClient.OracleLob lob = dataReader.GetOracleLob(1);
using (System.IO.Stream fileStream = System.IO.File.OpenRead(path))
{
lob.SetLength(0);
int readBytes = 0;
while ((readBytes = fileStream.Read(buffer, 0, buffer.Length)) > 0)
{
lob.Write(buffer, 0, readBytes);
} // Whend
} // End Using fileStream
} // End Using dataReader
} // End Using cmd
} // End Sub WriteLargeFileChunckedOracle
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!";
}
}