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>
/// <param name="conn">连接字符串</param>
/// <param name="cte">连接类型</param>
/// <param name="dt">连接超时时间</param>
/// <returns>返回创建的连接</returns>
private ConnStruct CreateConnection(string conn, ConnTypeEnum cte)
{
DbConnection db = null;
if (cte == ConnTypeEnum.Odbc)
{
db = new System.Data.Odbc.OdbcConnection(conn);//ODBC数据源连接
}
else if (cte == ConnTypeEnum.OleDb)
{
db = new System.Data.OleDb.OleDbConnection(conn);//OLE DB数据连接,Access数据库
}
else if (cte == ConnTypeEnum.SqlClient)
{
db = new System.Data.SqlClient.SqlConnection(conn);//SqlServer数据库连接
}
else if (cte == ConnTypeEnum.MySqlClient)
{
db = new MySql.Data.MySqlClient.MySqlConnection(conn);//MySqlServer数据库连接
}
else if (cte == ConnTypeEnum.OracleClient)
{
db = new System.Data.OracleClient.OracleConnection(conn);//Oracle数据库连接
}
else if (cte == ConnTypeEnum.MsModelClient)
{
db = new System.Data.EntityClient.EntityConnection(conn);//Microsoft数据库连接
}
ConnStruct cs = new ConnStruct(db, cte, DateTime.Now);
cs.Open();
return(cs);
}
public static IDbConnection CreateSqlConnection(DatabaseType dbType, string strKey)
{
IDbConnection connection = null;
string strConn = ConfigurationManager.ConnectionStrings[strKey].ConnectionString;
switch (dbType)
{
case DatabaseType.SqlServer:
connection = new System.Data.SqlClient.SqlConnection(strConn);
break;
case DatabaseType.MySql:
//connection = new MySql.Data.MySqlClient.MySqlConnection(strConn);
//break;
case DatabaseType.Oracle:
//connection = new Oracle.DataAccess.Client.OracleConnection(strConn);
connection = new System.Data.OracleClient.OracleConnection(strConn);
break;
case DatabaseType.DB2:
connection = new System.Data.OleDb.OleDbConnection(strConn);
break;
}
return(connection);
}
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>
/// 工厂模式 根据配置创建数据库连接
/// </summary>
/// <returns></returns>
public static IDbConnection CreateSqlCon()
{
IDbConnection connection = null;
string conStr = PubConstant.ConnectionString;
switch (PubConstant.DbType.ToLower())
{
case "mssql":
connection = new System.Data.SqlClient.SqlConnection(conStr);
break;
case "mysql":
connection = new MySql.Data.MySqlClient.MySqlConnection(conStr);
break;
case "oracle":
connection = new System.Data.OracleClient.OracleConnection(conStr);
break;
case "sqlce":
connection = new System.Data.SqlServerCe.SqlCeConnection(conStr);
break;
case "sqlite":
connection = new System.Data.SQLite.SQLiteConnection(conStr);
break;
}
return(connection);
}
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 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);
}
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();
}
static System.Data.IDbConnection GetNewTempConnection()
{
string connectString = Neusoft.FrameWork.Management.Connection.DataSouceString;
if (connectString.ToLower().Trim().IndexOf("pooling=true") > 0)
{
connectString = connectString.Replace("True", "False");
connectString = connectString.Replace("true", "False");
}
System.Data.IDbConnection conn = null;
switch (Neusoft.FrameWork.Management.Connection.DBType)
{
case Neusoft.FrameWork.Management.Connection.EnumDBType.ORACLE:
conn = new System.Data.OracleClient.OracleConnection(connectString);
break;
case Neusoft.FrameWork.Management.Connection.EnumDBType.DB2:
conn = new IBM.Data.DB2.DB2Connection(Neusoft.FrameWork.Management.Connection.DataSouceString);
break;
case Neusoft.FrameWork.Management.Connection.EnumDBType.SQLSERVER:
conn = new System.Data.SqlClient.SqlConnection(Neusoft.FrameWork.Management.Connection.DataSouceString);
break;
default:
conn = new System.Data.OleDb.OleDbConnection(Neusoft.FrameWork.Management.Connection.DataSouceString);
break;
}
return(conn);
}
/// <summary>
/// 测试连接
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void m_btnTest_Click(object sender, EventArgs e)
{
DbConnection dbConn = null;
string strDbType = m_cboType.Text;
if (m_blnStringEqualsIgnoreCase(strDbType, "sql"))
{
dbConn = new System.Data.SqlClient.SqlConnection();
}
else if (m_blnStringEqualsIgnoreCase(strDbType, "oracle"))
{
dbConn = new System.Data.OracleClient.OracleConnection();
}
dbConn.ConnectionString = m_strCreateConnStr();
try
{
dbConn.Open();
MessageBox.Show("数据库连接成功!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show("数据库连接成失败,请检查数据库连接配置!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (dbConn != null)
{
if (dbConn.State == ConnectionState.Open)
{
dbConn.Close();
}
dbConn = null;
}
}
}
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 Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
oracleConnection = new System.Data.OracleClient.OracleConnection();
}
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
oracleConnection = new System.Data.OracleClient.OracleConnection();
}
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);
}
}
}
} // End Constructor
public cOracle(string strConnectionString)
{
//this.m_DatabaseConfiguration = dbcDBconfig;
this.m_dbtDBtype = DataBaseEngine_t.Oracle;
this.m_providerFactory = this.GetFactory();
this.m_dictScriptTemplates = GetSQLtemplates();
this.m_dblDBversion = 11.0;
this.m_ConnectionString = new System.Data.OracleClient.OracleConnectionStringBuilder(strConnectionString);
this.m_SqlConnection = new System.Data.OracleClient.OracleConnection(strConnectionString);
} // End Constructor 2
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;
}
/// <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);
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <param name="dsn">连接字符串</param>
/// <returns></returns>
private System.Data.Common.DbConnection GetConnection(string dsn)
{
System.Data.Common.DbConnection conn = null;
var dbType = this.DBSrcType;
if (dbType == Sys.DBSrcType.Localhost)
{
switch (SystemConfig.AppCenterDBType)
{
case DBType.MSSQL:
dbType = Sys.DBSrcType.SQLServer;
break;
case DBType.Oracle:
dbType = Sys.DBSrcType.Oracle;
break;
case DBType.MySQL:
dbType = Sys.DBSrcType.MySQL;
break;
case DBType.Informix:
dbType = Sys.DBSrcType.Infomax;
break;
default:
throw new Exception("没有涉及到的连接测试类型...");
}
}
switch (dbType)
{
case Sys.DBSrcType.SQLServer:
conn = new System.Data.SqlClient.SqlConnection(dsn);
break;
case Sys.DBSrcType.Oracle:
conn = new System.Data.OracleClient.OracleConnection(dsn);
break;
case Sys.DBSrcType.MySQL:
conn = new MySql.Data.MySqlClient.MySqlConnection(dsn);
break;
case Sys.DBSrcType.Infomax:
conn = new System.Data.OleDb.OleDbConnection(dsn);
break;
}
return(conn);
}
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)
{
}
}
/// <summary>
/// 创建连接队象和设置数据库类型
/// </summary>
/// <param name="connstringName"></param>
/// <returns></returns>
public static IDbConnection CreateDbConnection(string connstringName)
{
DatabaseType dbType = DatabaseType.SqlServer;
IDbConnection connection;
ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings[connstringName];
string connectionString = connectionStringSettings.ConnectionString;
string providerName = connectionStringSettings.ProviderName;
if (string.IsNullOrEmpty(providerName))
{
throw new Exception(connstringName + "连接字符串未定义 ProviderName");
}
else if (providerName == "System.Data.SqlClient")
{
dbType = DatabaseType.SqlServer;
connection = new System.Data.SqlClient.SqlConnection(connectionString);
}
//else if (providerName == "Oracle.DataAccess.Client" )
//{
// dbType = dbType.Oracle;
// connection = new Oracle.DataAccess.Client.OracleConnection(strConn);
//}
else if (providerName == "System.Data.OracleClient")
{
dbType = DatabaseType.Oracle;
connection = new System.Data.OracleClient.OracleConnection(connectionString);
}
else if (providerName == "MySql.Data.MySqlClient")
{
dbType = DatabaseType.MySql;
connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
}
else if (providerName == "System.Data.OleDb")
{
dbType = DatabaseType.Access;
connection = new System.Data.OleDb.OleDbConnection(connectionString);
}
else if (providerName == "System.Data.SQLite")//未测试
{
dbType = DatabaseType.SQLite;
connection = new System.Data.OleDb.OleDbConnection(connectionString);
}
else
{
throw new Exception(connstringName + "连接字符串未识别 ProviderName");
}
DBType = dbType;
return(connection);
}
//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 Run(string db,string user,string password,string sql)
{
this.db=db;
this.user=user;
this.password=password;
while(sql.Trim().EndsWith(";"))
{
sql=sql.Trim().Substring(0,sql.Trim().Length-1);
}
tbSql.Text=sql;
Text=Text+" - "+db;
conn=new System.Data.OracleClient.OracleConnection(String.Format("Data Source={0};User Id={1};Password={2}",db,user,password));
Show();
btRun.PerformClick();
}
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!";
}
}
/// <summary>
/// 根据数据库类型和配置文件字符串获取连接；主要针对Dapper使用
/// </summary>
public static IDbConnection CreateSqlConnection(DatabaseType dbType, string strKey)
{
IDbConnection connection = null;
string strConn = ConfigurationManager.ConnectionStrings[strKey].ConnectionString;
//App.config配置文件要么使用xml，要么将配置写入调用此类库的config文件中
//ExeConfigurationFileMap fileMap = new ExeConfigurationFileMap();
//fileMap.ExeConfigFilename = Application.StartupPath + @"\Unicellular.DataAccess.dll.config";
//Configuration config = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None);
//return config.ConnectionStrings.ConnectionStrings[ connectionName ].ConnectionString.ToString();
switch (dbType)
{
case DatabaseType.SqlServer:
connection = new System.Data.SqlClient.SqlConnection(strConn);
break;
case DatabaseType.MySql:
//connection = new MySql.Data.MySqlClient.MySqlConnection(strConn);
break;
case DatabaseType.Oracle:
connection = new System.Data.OracleClient.OracleConnection(strConn);
//connection = new System.Data.OracleClient.OracleConnection(strConn);
break;
case DatabaseType.DB2:
connection = new System.Data.OleDb.OleDbConnection(strConn);
break;
case DatabaseType.Sqlite:
connection = new System.Data.SQLite.SQLiteConnection(strConn);
break;
case DatabaseType.Postgresql:
//connection = new System.Data.SQLite.SQLiteConnection( strConn );
break;
}
if (connection == null)
{
throw new Exception("数据库链接为空！");
}
return(connection);
}
private static IDbConnection GetConnection(string connectionString, string dbType)
{
IDbConnection conn = null;
dbType = dbType.ToUpper();
switch (dbType)
{
case "SQLSERVER":
case "SYSTEM.DATA.SQLCLIENT": conn = new System.Data.SqlClient.SqlConnection(connectionString); break;
case "ORACLE": conn = new System.Data.OracleClient.OracleConnection(connectionString); break;
case "ACCESS": conn = new System.Data.OleDb.OleDbConnection(connectionString); break;
case "DB2": conn = new System.Data.Odbc.OdbcConnection(connectionString); break;
default: conn = new System.Data.OleDb.OleDbConnection(connectionString); break;
}
return(conn);
}
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();
}
}
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();
}
}
private static IDbConnection GetConnection(string connectionString, string dbType)
{
IDbConnection conn = null;
dbType = dbType.ToUpper();
switch (dbType)
{
case "SQLSERVER":
case "SYSTEM.DATA.SQLCLIENT": conn = new System.Data.SqlClient.SqlConnection(connectionString); break;
case "ORACLE": conn = new System.Data.OracleClient.OracleConnection(connectionString); break;
case "ACCESS": conn = new System.Data.OleDb.OleDbConnection(connectionString); break;
case "DB2": conn = new System.Data.Odbc.OdbcConnection(connectionString); break;
default: conn = new System.Data.OleDb.OleDbConnection(connectionString); break;
}
return conn;
}
