public void ExecuteCneZcxExcel()
{
string zcxStr = ConfigurationManager.AppSettings["ZCXConnStr"].ToString();
using (var zcxConn = new OracleConnection(zcxStr))
{
using (OracleCommand cmd = new OracleCommand())
{
zcxConn.Open();
cmd.Connection = zcxConn;
cmd.CommandText = "sp_MergeZCXEnergyYieldData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
zcxConn.Close();
}
//chemical ver. 2.3 rewrite this class
using (OracleCommand cmd = new OracleCommand())
{
zcxConn.Open();
cmd.Connection = zcxConn;
cmd.CommandText = "sp_MergeZCXChemistryOutputData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
zcxConn.Close();
}
}
}
/// <summary>
/// 執行非查詢的SQL語句
/// </summary>
/// <param name="SQL">SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <returns></returns>
public int GetNonQueryBySQL(string SQL, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open(); //打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = SQL;
int num = cmd.ExecuteNonQuery();
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(num);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
/// <summary>
/// 執行SQL語句返回DataTable
/// </summary>
/// <param name="SQL">SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <returns></returns>
public DataTable GetDataTableBySQL(string SQL, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open(); //打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = SQL;
Oracle.ManagedDataAccess.Client.OracleDataAdapter adapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
dataTable.TableName = "數據集";
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(dataTable);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
/// <summary>
/// 調用存儲返回String字符串信息和DataTable數據表格（最後兩個位置必須為返回參數，一個為輸出字符串另一個為游標，位置不能顛倒）
/// <para>obj使用方法：new{ v_data=value, v_data1=value1,out_string="",out_cursor=""}</para>
/// <para>注意：obj中v_data為存儲參數名稱，value為對應的值，out_string為輸出參數不需要輸入值， out_cursor為游標不需要輸入值</para>
/// </summary>
/// <param name="storageName">存儲名稱</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <param name="obj">存儲參數對象</param>
/// <param name="dataTable">返回結果集</param>
/// <returns></returns>
public string GetStringAndDataTableByStorageName(string storageName, string DBUrl, object obj, out DataTable dataTable)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open();//打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = storageName; //存儲名稱
cmd.CommandType = CommandType.StoredProcedure;
PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); //獲取object中的字段名和值
for (int i = 0; i < properties.Length; i++)
{
if (i == (properties.Length - 2))
{ //設定輸出的類型和值
cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2, short.MaxValue).Direction = ParameterDirection.Output;
cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
}
else if (i == (properties.Length - 1))
{ //設定輸出的類型和值
cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output;
cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
}
else
{ //設定輸入的類型和值
cmd.Parameters.Add(properties[i].Name, GetOracleDbType(properties[i], obj)).Direction = ParameterDirection.Input;
cmd.Parameters[properties[i].Name].Value = properties[i].GetValue(obj, null); //賦值
}
}
DataTable dt = new DataTable();
dt.TableName = "數據集";
Oracle.ManagedDataAccess.Client.OracleDataAdapter oda = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);
oda.Fill(dt);
dataTable = dt; //返回數據結果集
string message = cmd.Parameters[properties[properties.Length - 2].Name].Value.ToString(); //獲取輸出的字符串
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(message);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString)
{
using (OracleConnection connection = new OracleConnection(conStr))
{
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
}
}
public void ImportDataTable(Cells c)
{
DataTable tableData = ImportWorkSheet(c);
using (var con = new OracleConnection(Connectionstr))
{
con.Open();
var cmdText = "INSERT INTO Cofeed VALUES( ";
for (var i = 0; i < tableData.Columns.Count; i++)
{
cmdText += ":v" + i + ",";
}
cmdText += "sysdate,null)";
var cmd = new OracleCommand(cmdText, con);
for (var j = 0; j < tableData.Rows.Count; j++)
{
cmd.Parameters.Clear();
for (var i = 0; i < tableData.Columns.Count; i++)
{
var par = new OracleParameter("v" + i, OracleDbType.NVarchar2)
{
Direction = ParameterDirection.Input,
Value = tableData.Rows[j][i].ToString()
};
cmd.Parameters.Add(par);
}
cmd.ExecuteNonQuery();
}
con.Close();
}
}
public DateTime GetMaxStartTime()
{
object o = null;
using (OracleConnection conn = new OracleConnection(Connectionstr))
{
try
{
conn.Open();
string sql = "select max(startTime ) as MaxTime from CofeedProduct";
using (OracleCommand cmd = new OracleCommand(sql, conn))
{
o = cmd.ExecuteScalar();
}
}
catch
{
// ignored
}
finally
{
conn.Close();
}
}
return(Convert.ToDateTime(o));
}
public int viewTroops()
{
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DbConnect.oradb);
String strSQL = "SELECT * FROM V_PlayerTroops";
conn.Open();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(strSQL, conn);
Oracle.ManagedDataAccess.Client.OracleDataReader dr = cmd.ExecuteReader();
//read the record in dr
dr.Read();
if (dr.IsDBNull(0))
{
playerTroops = 1;
}
else
{
playerTroops = Convert.ToInt16(dr.GetValue(0)) + 1;
}
conn.Close();
return(playerTroops);
}
public int LoginUsingPassword(string username, string password)
{
var result = 0;
var userNameParam = new OracleParameter("USERNAME", OracleDbType.Varchar2, username, ParameterDirection.Input);
var userPassParam = new OracleParameter("PASSWORD", OracleDbType.Varchar2, password, ParameterDirection.Input);
var resultPassParam = new OracleParameter("result_tab", OracleDbType.Int32, ParameterDirection.ReturnValue);
using (OracleConnection connection = new OracleConnection(_connectionString))
{
OracleCommand cmd = new OracleCommand("LOGINFUNC", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.Parameters.Add(resultPassParam);
cmd.Parameters.Add(userNameParam);
cmd.Parameters.Add(userPassParam);
connection.Open();
cmd.ExecuteNonQuery();
var res = cmd.Parameters["result_tab"].Value.ToString();
result = Convert.ToInt32(res);
connection.Close();
// context.SaveChanges();
}
return(result);
}
//string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.130.23)(PORT=8903))(CONNECT_DATA=(SERVICE_NAME = settle_primary)));User Id=settle;Password=settle;";
static void Main123(string[] args)
{
ArrayList arr = GetAttId1();
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
for (int i = 0; i < arr.Count; i++)
{
double ds = 0;
int total = 1;
for (int j = 0; j < total; j++)
{
DateTime startDate = DateTime.Now;
OracleParameter[] oracleParameters = new OracleParameter[1];
oracleParameters[0] = new OracleParameter("p_attachmentId", arr[i]);
OracleCommand cmd = new OracleCommand("SP_CHECKCINEMA_MT_BYATTID", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(oracleParameters);
cmd.CommandTimeout = 1800;
cmd.ExecuteNonQuery();
DateTime endDate = DateTime.Now;
ds += (endDate - startDate).TotalSeconds;
}
Console.WriteLine(arr[i] + "：" + ds / total);
}
conn.Close();
}
Console.ReadLine();
}
protected void RegisterButton_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
// Hash and salt the password using Bcyrpt before it gets sent to the database
var saltedPassword = BCryptHelper.HashPassword(password.Text, Global.Salt);
using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
{
// Declare the stored procedure to execute and send it the parameters it needs
OracleCommand objCmd = new OracleCommand("tickets_api.insertGuest", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };
objCmd.Parameters.Add("p_FirstName", OracleDbType.Varchar2, first_name.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_LastName", OracleDbType.Varchar2, last_name.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_Email", OracleDbType.Varchar2, email.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_Password", OracleDbType.Varchar2, saltedPassword, ParameterDirection.Input);
try
{
objConn.Open();
objCmd.ExecuteNonQuery();
}
catch (Exception)
{
labelMessage.Text = "Could not register. Try again later.";
}
objConn.Close();
}
if (string.IsNullOrEmpty(labelMessage.Text))
Response.Redirect("Login.aspx");
}
}
public void ImportDataTable(Cells c, out int total)
{
DataTable datatable = ImportWorkSheet(c);
total = datatable.Rows.Count;
using (OracleConnection conn = new OracleConnection(Connectionstr))
{
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
try
{
foreach (DataRow dr in datatable.Rows)
{
string sql1 = CreateInsertSql(dr);
using (OracleCommand cmd = new OracleCommand(sql1, conn))
{
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
conn.Close();
throw;
}
}
}
/// <summary>
/// 調用存儲返回String字符串信息（最後一個位置必須為String類型字符，位置不能顛倒）
/// <para>obj使用方法：new{ v_data=value, v_data1=value1,out_string=""}</para>
/// <para>注意：obj中v_data為存儲參數名稱，value為對應的值，out_string為輸出參數不需要輸入值</para>
/// </summary>
/// <param name="storageName"></param>
/// <param name="DBUrl"></param>
/// <param name="obj"></param>
/// <returns></returns>
public string GetStringDataByStorageName(string storageName, string DBUrl, object obj)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open();
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = storageName; //存儲名稱
cmd.CommandType = CommandType.StoredProcedure;
PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); //獲取object中的字段名和值
for (int i = 0; i < properties.Length; i++)
{
if (i == (properties.Length - 1))
{ //設定輸出的類型和值
cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2, short.MaxValue).Direction = ParameterDirection.Output;
cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
}
else
{ //設定輸入的類型和值
cmd.Parameters.Add(properties[i].Name, GetOracleDbType(properties[i], obj)).Direction = ParameterDirection.Input;
cmd.Parameters[properties[i].Name].Value = properties[i].GetValue(obj, null); //賦值
}
}
cmd.ExecuteNonQuery();
string message = cmd.Parameters[properties[properties.Length - 1].Name].Value.ToString(); //獲取返回的值
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(message);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
public void FillData(DataTable tb, string str, int language)
{
Regex reg = new Regex(@"<tr>([\w\s\S]+?)</tr>");
OracleConnection conn = new OracleConnection(connstr);
try
{
if (reg.IsMatch(str))
{
MatchCollection coll = reg.Matches(str);
for (int i = 1; i < coll.Count; i++)
{
string tr = coll[i].Value;
tr = tr.Replace("<td class=\"spec\">", "@").Replace("<td>", "@").Replace("</tr>", "");
tr = new Regex(@"</td>\s+").Replace(tr, "@");
tr = new Regex(@"<tr>\s+").Replace(tr, "");
string[] strArray = tr.Split(new char[] { '@' }, StringSplitOptions.RemoveEmptyEntries);
// datatype, dataname, unit, figure, updateDate, fetchTime
DataRow dr = tb.NewRow();
dr["id"] = strArray[0].Replace(" ", "");
string code = getCode(1, i, conn);
if (string.IsNullOrEmpty(code))
{
code = strArray[0].Replace(" ", "");
}
dr["code"] = code;
dr["datatype"] = strArray[1].Replace(" ", "");
dr["dataname"] = strArray[2].Replace(" ", "");
dr["priceDate"] = strArray[3].Replace(" ", "");
dr["unit"] = strArray[4].Replace(" ", "");
dr["figure"] = strArray[5].Replace(" ", "");
dr["updateDate"] = strArray[6].Replace(" ", "").Replace("/", "-");
//dr["fetchTime"] = (long)(DateTime.Now - new DateTime(1970, 1, 1)).TotalMilliseconds;
dr["fetchTime"] = DateTime.Now.ToString("dd-MMM-yyyy hh:mm:ss tt");
dr["Lanuage"] = language;
//if (Convert.ToDateTime (dr["updateDate"])!= DateTime.Now.Date )
//{
// continue;
//}
tb.Rows.Add(dr);
}
}
}
catch (Exception e)
{
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行sql执行增删改
/// </summary>
/// <param name="cmdText">sql语句</param>
/// <param name="oracleParameters">所传参数（必须按照存储过程参数顺序）</param>
/// <param name="strConn">链接字符串</param>
/// <returns></returns>
public static int ExecToSqlNonQuery(string cmdText)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
OracleCommand cmd = new OracleCommand(cmdText, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
return(result);
}
}
private void Deconnecter()
{
try
{
conn.Close();
// MessageBox.Show(conn.State.ToString());
}
catch (Exception se)
{
MessageBox.Show(se.Message);
}
}
public void ExecuteMax()
{
try
{
using (conn = new OracleConnection(ConfigurationManager.AppSettings["mergeData"]))
{
conn.Open();
tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxChemicalIndustryTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyGasTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyOilTable";
cmd.ExecuteNonQuery();
}
tran.Commit();
}
}
catch (Exception e)
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行存储过程没有返回值
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="outParameters">参数</param>
/// <param name="oracleParameters">所传参数（必须按照存储过程参数顺序）</param>
/// <param name="strConn">链接字符串</param>
/// <returns></returns>
public static void ExecToStoredProcedure(string cmdText, OracleParameter[] oracleParameters, string strConn)
{
using (OracleConnection conn = new OracleConnection(strConn))
{
OracleCommand cmd = new OracleCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(oracleParameters);
cmd.CommandTimeout = 1800;
conn.Open();
Console.WriteLine("3：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ffff"));
cmd.ExecuteNonQuery();
Console.WriteLine("4：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ffff"));
Console.WriteLine(cmd.Parameters["p_returnvalue"].Value.ToString());
conn.Close();
}
}
private DateTime GetMaxUnxi()
{
object o;
using (OracleConnection conn = new OracleConnection(ConfigurationManager.AppSettings["CnECon"]))
{
using (OracleCommand cmd = new OracleCommand("select max(updateDate) from " + _tablename, conn))
{
conn.Open();
o = cmd.ExecuteScalar();
conn.Close();
}
}
return(DateTime.Parse(o.ToString()));
}
public void reserver(int idclient, int idchambre, DateTime debut, DateTime fin)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = "pr_reservation";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("leclient", OracleDbType.Int32).Value = idclient;
cmd.Parameters.Add("lachambre", OracleDbType.Int32).Value = idchambre;
cmd.Parameters.Add("ledebut", OracleDbType.Date).Value = debut;
cmd.Parameters.Add("lafin", OracleDbType.Date).Value = fin;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
}
public static void ExecCinema_MT(int attid)
{
Console.WriteLine(DateTime.Now.ToString(_formatString));
//OracleConnection conn, OracleTransaction tran
string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME = orcl)));User Id=settle;Password=settle;";
OracleConnection conn = new OracleConnection(conStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
var paramArray = new[]
{
new OracleParameter("@p_attachmentId", OracleDbType.Int32)
{
Value = attid
},
//new OracleParameter("@QueryDateType", OracleDbType.Varchar2) { Value = queryDateType },
//new OracleParameter("@OrgType", OracleDbType.Varchar2) { Value = orgType },
//new OracleParameter("@TrustType", OracleDbType.NVarchar2) { Value = trustType },
// new OracleParameter("@Total", OracleDbType.Int32,ParameterDirection.Output),
//new OracleParameter("@cur",OracleDbType.RefCursor,ParameterDirection.Output)
};
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_CHECKCinema_MT_BYattID";
cmd.CommandTimeout = 90;
cmd.Parameters.AddRange(paramArray);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
tran.Commit();
}
}
catch (Exception ee)
{
tran.Rollback();
Console.WriteLine(ee.Message);
}
finally {
conn.Close();
}
Console.WriteLine(DateTime.Now.ToString(_formatString));
}
public void ExecuteInsertSql(string tableName, DataTable tableData, string minReDate, int paraCount, bool isHasHistoryValue = false)
{
using (var con = new OracleConnection(Connectionstr))
{
con.Open();
var cmd = new OracleCommand("delete " + tableName + " where RE_DATE>='" + minReDate + "'", con);
cmd.ExecuteNonQuery();
con.Close();
}
using (var con = new OracleConnection(Connectionstr))
{
con.Open();
var cmdText = "INSERT INTO " + tableName + " VALUES( ";
for (var i = 0; i < paraCount; i++)
{
cmdText += ":v" + i + ",";
}
if (isHasHistoryValue)
{
cmdText += "sysdate,null)";
}
else
{
cmdText += "sysdate)";
}
var cmd = new OracleCommand(cmdText, con);
for (var j = 0; j < tableData.Rows.Count; j++)
{
cmd.Parameters.Clear();
for (var i = 0; i < paraCount; i++)
{
var par = new OracleParameter("v" + i, OracleDbType.NVarchar2)
{
Direction = ParameterDirection.Input,
Value = tableData.Rows[j][i].ToString()
};
cmd.Parameters.Add(par);
}
cmd.ExecuteNonQuery();
}
con.Close();
}
}
/// <summary>
/// 通过EF执行sql语句获得Table
/// </summary>
/// <param name="db">数据库上下文</param>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public static DataTable SqlQueryForDataTatable(string sql)
{
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection();
conn.ConnectionString = connectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
conn.Close();
return(table);
}
///
///
/// </summary>
/// <param name="parmAppID"></param>
/// <returns></returns>
///
///
public DataTable GetMatchingReports(int?p_ApplicationID)
{
var con = ConfigurationManager.ConnectionStrings["OraReports"].ConnectionString;
Oracle.ManagedDataAccess.Client.OracleConnection connection = new Oracle.ManagedDataAccess.Client.OracleConnection(con);
try
{
OracleCommand command = new OracleCommand("ZZUAB_WAM.wfuab_Get_Application_Reports", connection);
command.CommandType = CommandType.StoredProcedure;
command.BindByName = true;
command.Parameters.Add("p_ApplicationID", p_ApplicationID).OracleDbType = OracleDbType.Varchar2;
command.Parameters.Add("p_recordSet ", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
OracleDataAdapter adapter = new OracleDataAdapter(command);
DataSet result = new DataSet();
DataTable dt = new DataTable();
connection.Open();
adapter.Fill(result);
var fill = result.Tables[0];
return(fill);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
connection.Dispose();
connection.Close();
}
}
public void ImportData(DataTable tb, string[] filters, StringBuilder sb)
{
int updateRows = 0, insertRows = 0;
using (OracleConnection conn = new OracleConnection(connstr))
{
conn.Open();
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
foreach (DataRow dr in tb.Rows)
{
string strWhere = CreateFilter(filters, dr);
string sqlExists = "select * from " + tb.TableName + strWhere;
cmd.CommandText = sqlExists;
OracleDataAdapter sda = new OracleDataAdapter(cmd);
DataTable t = new DataTable();
sda.Fill(t);
if (t != null && t.Rows.Count == 1)
{
//更新数据；
string updatesql = CreateUpdateSql(tb, dr, strWhere);
cmd.CommandText = updatesql;
cmd.ExecuteNonQuery();
updateRows++;
}
else
{
//插入数据；
string updatesql = CreateInsertSql(tb, dr);
cmd.CommandText = updatesql;
cmd.ExecuteNonQuery();
insertRows++;
}
}
}
conn.Close();
sb.Append(" update:" + updateRows + " insert:" + insertRows + "\r\n");
}
}
protected void RegisterButton_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
// Hash and salt the password using Bcyrpt before it gets sent to the database
var saltedPassword = BCryptHelper.HashPassword(password.Text, Global.Salt);
using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
{
// Declare the stored procedure to execute and send it the parameters it needs
OracleCommand objCmd = new OracleCommand("tickets_api.insertGuest", objConn)
{
BindByName = true, CommandType = CommandType.StoredProcedure
};
objCmd.Parameters.Add("p_FirstName", OracleDbType.Varchar2, first_name.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_LastName", OracleDbType.Varchar2, last_name.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_Email", OracleDbType.Varchar2, email.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_Password", OracleDbType.Varchar2, saltedPassword, ParameterDirection.Input);
try
{
objConn.Open();
objCmd.ExecuteNonQuery();
}
catch (Exception)
{
labelMessage.Text = "Could not register. Try again later.";
}
objConn.Close();
}
if (string.IsNullOrEmpty(labelMessage.Text))
{
Response.Redirect("Login.aspx");
}
}
}
// retourne un DataSet avec le contenu d'une procedure qui retourne plusieurs résultats
public DataSet GetSelectProc(string Nom_Procedure)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet dataset = new DataSet();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = Nom_Procedure;
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter curseur =
new OracleParameter("curseur", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add(curseur);
connection.Open();
cmd.ExecuteNonQuery();
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(dataset);
cmd.Dispose();
connection.Close();
return(dataset);
}
}
/// <summary>
/// 執行多條SQL語句，實現數據庫事務。
/// </summary>
/// <param name="SQLStringList">多條SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
public int GetNonQueryByManySQL(ArrayList SQLStringList, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
using (Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl))
{
conn.Open();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
Oracle.ManagedDataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int num = 0;
for (int i = 0; i < SQLStringList.Count; i++)
{
string SQL = SQLStringList[i].ToString();//獲取SQL語句
if (SQL.Trim().Length > 1)
{
cmd.CommandText = SQL;
num = cmd.ExecuteNonQuery();
}
tx.Commit(); //提交事務
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
}
return(num); //返回執行結果數量
}
catch (Oracle.ManagedDataAccess.Client.OracleException E)
{
tx.Rollback();//事務回滾
throw new Exception(E.Message);
}
}
}
public virtual async Task <object> ExecuteScalarAsync(CommandType cmdType, string cmdText,
params OracleParameter[] commandParameters)
{
using (var connection = new OracleConnection(ConnectionString))
{
using (var command = new OracleCommand(cmdText, connection))
{
try
{
command.CommandType = cmdType;
command.Parameters.AddRange(commandParameters);
connection.Open();
return(await command.ExecuteScalarAsync());
}
finally
{
connection.Close();
}
}
}
}
public virtual int ExecuteNonQuery(CommandType cmdType, string cmdText,
params OracleParameter[] commandParameters)
{
using (var connection = new OracleConnection(ConnectionString))
{
using (var command = new OracleCommand(cmdText, connection))
{
try
{
command.CommandType = cmdType;
if (commandParameters != null)
{
command.Parameters.AddRange(commandParameters);
}
connection.Open();
return(command.ExecuteNonQuery());
}
finally
{
connection.Close();
}
}
}
}
public void FillDataTable_Product(DataTable tb, string str, int language)
{
//s == "SMMPrices_路透" || s == "SMMPrices_toThomsonReuters"
Regex reg = new Regex(@"<tr>([\w\s\S]+?)</tr>");
OracleConnection conn = new OracleConnection(connstr);
try
{
if (reg.IsMatch(str))
{
MatchCollection coll = reg.Matches(str);
for (int i = 1; i < coll.Count; i++)
{
string tr = coll[i].Value;
//.Replace(@"</td>\s+", "@").
//.Replace(@"<tr>\s+", "")
tr = tr.Replace("<td class=\"spec\">", "@").Replace("<td>", "@").Replace("</tr>", "");
tr = new Regex(@"</td>\s+").Replace(tr, "@");
tr = new Regex(@"<tr>\s+").Replace(tr, "");
string[] strArray = tr.Split(new char[] { '@' }, StringSplitOptions.RemoveEmptyEntries);
// id, productName, unit, specification, grade, brand, locationOfSale, locationOfProduction, producer, lowestPrice, highestPrice, updateDate, updateDateTime
DataRow dr = tb.NewRow();
//string pname=System .Text .Encoding .UTF8.GetString (System.Text.Encoding.GetEncoding("gb2312").GetBytes());
dr["id"] = strArray[0].Replace(" ", "");
string code = getCode(0, i, conn);
if (string.IsNullOrEmpty(code))
{
code = strArray[0].Replace(" ", "");
}
dr["code"] = code;
dr["productName"] = strArray[1].Replace(" ", "");
dr["unit"] = strArray[2].Replace(" ", "");
dr["specification"] = strArray[3].Replace(" ", "");
dr["grade"] = strArray[4].Replace(" ", "");
dr["brand"] = strArray[5].Replace(" ", "");
dr["locationOfSale"] = strArray[6].Replace(" ", "");
dr["locationOfProduction"] = strArray[7].Replace(" ", "");
dr["producer"] = strArray[8].Replace(" ", "");
dr["lowestPrice"] = strArray[9].Replace(" ", "");
dr["highestPrice"] = strArray[10].Replace(" ", "");
dr["updateDate"] = strArray[11].Replace(" ", "").Replace("/", "-");
//dr["updateDateTime"] = (long)(DateTime.Now - new DateTime(1970, 1, 1)).TotalMilliseconds;
dr["FETCHTIME"] = DateTime.Now.ToString("dd-MMM-yyyy hh:mm:ss tt");
dr["Lanuage"] = language;
//if (Convert.ToDateTime(dr["updateDate"]) != DateTime.Now.Date)
//{
// continue;
//}
tb.Rows.Add(dr);
}
}
}
catch (Exception e)
{
}
finally
{
conn.Close();
}
}
