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(); } }