コード例 #1
0
ファイル: DbHelperOra.cs プロジェクト: jsonchou/jsonsite
 /// <summary>
 /// 执行带一个存储过程参数的的SQL语句。
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSql(string SQLString, string content)
 {
     using (OracleConnection connection = new OracleConnection(connectionString))
     {
         OracleCommand cmd = new OracleCommand(SQLString, connection);
         System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@content", OracleDbType.NVarchar2);
         myParameter.Value = content;
         cmd.Parameters.Add(myParameter);
         try
         {
             connection.Open();
             int rows = cmd.ExecuteNonQuery();
             return(rows);
         }
         catch (System.Data.OracleClient.OracleException E)
         {
             throw new Exception(E.Message);
         }
         finally
         {
             cmd.Dispose();
             connection.Close();
         }
     }
 }
コード例 #2
0
ファイル: BetchLogic.cs プロジェクト: ycysvler/DataImport
        private string updateDbByID()
        {
            System.Data.OracleClient.OracleParameter[]
            ps = new System.Data.OracleClient.OracleParameter[7];
            System.Data.OracleClient.OracleParameter p1 = new System.Data.OracleClient.OracleParameter("I_EnTest_Work_Id", this.taskInfo.id);
            System.Data.OracleClient.OracleParameter p2 = new System.Data.OracleClient.OracleParameter("I_EnTest_Times", System.Data.OracleClient.OracleType.Int32);
            p2.Value = this.times;
            System.Data.OracleClient.OracleParameter p3 = new System.Data.OracleClient.OracleParameter("I_Fk_Time_Name", "COLUMN0");
            System.Data.OracleClient.OracleParameter p4 = new System.Data.OracleClient.OracleParameter("I_Object_Table_Name", this.dataScriptRule.DesTable);
            System.Data.OracleClient.OracleParameter p5 = new System.Data.OracleClient.OracleParameter("I_Source_Table_Name", tableName);
            System.Data.OracleClient.OracleParameter p6 = new System.Data.OracleClient.OracleParameter("O_Return_Int", System.Data.OracleClient.OracleType.Int32);
            p6.Direction = ParameterDirection.Output;
            System.Data.OracleClient.OracleParameter p7 = new System.Data.OracleClient.OracleParameter("O_Return_String", System.Data.OracleClient.OracleType.VarChar, 100);
            p7.Direction = ParameterDirection.Output;
            ps[0]        = p1;
            ps[1]        = p2;
            ps[2]        = p3;
            ps[3]        = p4;
            ps[4]        = p5;
            ps[5]        = p6;
            ps[6]        = p7;

            log.Info(string.Format("BetchLogic > updateDbByID > 更新数据:{0} -> {1}", tableName, this.dataScriptRule.DesTable));
            SendMessageEvent(string.Format("更新数据:{0} -> {1} , {2}", tableName, this.dataScriptRule.DesTable, DateTime.Now.ToString("HH:mm:ss")));

            object[] prout = OracleHelper.ExcuteProc("Process_Date_Server.Process_Temp_Into_Oragin", 2, ps);

            log.Info(string.Format("BetchLogic > updateDbByID > 更新结束:{0} : {1} {2} {3}", this.dataScriptRule.DesTable, DateTime.Now.ToString("HH:mm:ss"), prout[0], prout[1]));
            SendMessageEvent(string.Format("更新结束:{0} : {1}", this.dataScriptRule.DesTable, DateTime.Now.ToString("HH:mm:ss")));

            return(prout[1].ToString());

            SendCompleteEvent(prout[1].ToString());
            //TableDAL.DropTable(tableName);
        }
コード例 #3
0
ファイル: DbHelperOra.cs プロジェクト: jsonchou/jsonsite
 /// <summary>
 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
 /// </summary>
 /// <param name="strSQL">SQL语句</param>
 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
 {
     using (OracleConnection connection = new OracleConnection(connectionString))
     {
         OracleCommand cmd = new OracleCommand(strSQL, connection);
         System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@fs", OracleDbType.LongRaw);
         myParameter.Value = fs;
         cmd.Parameters.Add(myParameter);
         try
         {
             connection.Open();
             int rows = cmd.ExecuteNonQuery();
             return(rows);
         }
         catch (System.Data.OracleClient.OracleException E)
         {
             throw new Exception(E.Message);
         }
         finally
         {
             cmd.Dispose();
             connection.Close();
         }
     }
 }
コード例 #4
0
ファイル: Form1.cs プロジェクト: Moveofself/Test001
        public void sss1()
        {
            try
            {
                OraDbHelper _OraHelper = new OraDbHelper(null);

                _OraHelper.ConnectionString = "Data Source=KS_PRD_AY;Persist Security Info=True;User ID=FA;Password=FA;Unicode=True";

                string sSql = "";
                sSql  = " INSERT INTO FA.JOBLOG_HIS (SP_NAME, TIMESTAMP, ERRORCODE, ERRORDESC, STATUS) ";
                sSql += " VALUES('OLP_STATUS_MAIL', '20190520 172605', 'sss1',?pb, 'START');";


                System.Data.OracleClient.OracleParameter[] param = new System.Data.OracleClient.OracleParameter[]
                {
                    new System.Data.OracleClient.OracleParameter("?pb", OracleDbType.Varchar2)
                };

                param[0].Value = "TEST";

                _OraHelper.ExecuteNonQuery(sSql, CommandType.Text, param);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }
コード例 #5
0
        public void OnTableChange(Object sender, OracleNotificationEventArgs args)
        {
            //Get data Changed before.........................

            if (args.Type == OracleNotificationType.Change)
            {
                int start = 0;
                if (args.Info.ToString() == "Update")
                {
                    start = 1;
                }

                for (int i = start; i < args.Details.Rows.Count; i++)
                {
                    DataRow detailRow = args.Details.Rows[i];
                    string  rowid     = detailRow["Rowid"].ToString();

                    DataSet ds = new DataSet();
                    using (System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(NotificationNameAdapter, constr))
                    {
                        try
                        {
                            da.SelectCommand.CommandType = CommandType.StoredProcedure;
                            da.SelectCommand.Parameters.Add("MY_ID", System.Data.OracleClient.OracleType.VarChar).Value = rowid;
                            da.SelectCommand.Parameters.Add("Info", System.Data.OracleClient.OracleType.VarChar).Value  = args.Info.ToString();

                            System.Data.OracleClient.OracleParameter ret = new System.Data.OracleClient.OracleParameter("my_cursor", System.Data.OracleClient.OracleType.Cursor);
                            ret.Direction = ParameterDirection.ReturnValue;
                            da.SelectCommand.Parameters.Add(ret);
                            da.Fill(ds);
                        }
                        catch (Exception ex)
                        {
                        }
                    }

                    string data = JsonConvert.SerializeObject(ds);
                    try
                    {
                        Message messageObject = new Message();
                        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                        {
                            messageObject.message = ds.Tables[0].Rows[j]["MESSAGE"].ToString();
                            messageObject.url     = ds.Tables[0].Rows[j]["URL"].ToString();;
                            messageObject.type    = ds.Tables[0].Rows[j]["TYPE"].ToString();

                            var jsonMessage = JsonConvert.SerializeObject(messageObject);

                            //after get two paramters of notification fire it...
                            string successMessage = sendNotification(ds.Tables[0].Rows[j]["NID"].ToString(), jsonMessage);
                        }
                    }
                    catch (Exception e)
                    {
                    }
                }
            }
        }
コード例 #6
0
ファイル: Common.cs プロジェクト: yuanbeier/FZY
 public static System.Data.OracleClient.OracleParameter GetCursorParameter(string name)
 {
     System.Data.OracleClient.OracleParameter result = new System.Data.OracleClient.OracleParameter();
     result.OracleType    = System.Data.OracleClient.OracleType.Cursor;
     result.Direction     = System.Data.ParameterDirection.Output;
     result.ParameterName = name;
     result.Value         = DBNull.Value;
     return(result);
 }
コード例 #7
0
        /// <summary>
        /// 转换为数据库的对应参数类型
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public System.Data.Common.DbParameter[] ConvertToDbParameter(eDBType dbType)
        {
            if (_dicParamNameValue == null | _dicParamNameValue.Count == 0)
            {
                return(new System.Data.Common.DbParameter[0]);
            }

            System.Data.Common.DbParameter[] pras = new System.Data.Common.DbParameter[_dicParamNameValue.Count];

            switch (dbType)
            {
            case eDBType.Oracle:

                System.Data.OracleClient.OracleParameter[] pOras =
                    new System.Data.OracleClient.OracleParameter[_dicParamNameValue.Count];

                int iOra = 0;
                foreach (string name in _dicParamNameValue.Keys)
                {
                    pOras[iOra++] = new System.Data.OracleClient.OracleParameter(name, _dicParamNameValue[name]);
                }
                return(pOras);

            case eDBType.SqlServer:

                System.Data.SqlClient.SqlParameter[] pSqls =
                    new System.Data.SqlClient.SqlParameter[_dicParamNameValue.Count];

                int iSql = 0;
                foreach (string name in _dicParamNameValue.Keys)
                {
                    pSqls[iSql++] = new System.Data.SqlClient.SqlParameter(name, _dicParamNameValue[name]);
                }
                return(pSqls);

            case eDBType.Access:
                System.Data.OleDb.OleDbParameter[] oSqls =
                    new System.Data.OleDb.OleDbParameter[_dicParamNameValue.Count];

                int iOledb = 0;
                foreach (string name in _dicParamNameValue.Keys)
                {
                    oSqls[iOledb++] = new System.Data.OleDb.OleDbParameter(name, _dicParamNameValue[name]);
                }
                return(oSqls);

            case eDBType.MySql:
            case eDBType.DB2:
            case eDBType.Sybase:
            default:
                break;
            }

            return(new System.Data.Common.DbParameter[0]);
        }
コード例 #8
0
ファイル: Parameter.cs プロジェクト: howbigbobo/DailyCode
        /// <summary>
        /// 转换为数据库的对应参数类型
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public System.Data.Common.DbParameter[] ConvertToDbParameter(eDBType dbType)
        {
            if (_dicParamNameValue == null | _dicParamNameValue.Count == 0) return new System.Data.Common.DbParameter[0];

            System.Data.Common.DbParameter[] pras = new System.Data.Common.DbParameter[_dicParamNameValue.Count];

            switch (dbType)
            {
                case eDBType.Oracle:

                    System.Data.OracleClient.OracleParameter[] pOras =
                        new System.Data.OracleClient.OracleParameter[_dicParamNameValue.Count];

                    int iOra = 0;
                    foreach (string name in _dicParamNameValue.Keys)
                    {
                        pOras[iOra++] = new System.Data.OracleClient.OracleParameter(name, _dicParamNameValue[name]);
                    }
                    return pOras;
                case eDBType.SqlServer:

                    System.Data.SqlClient.SqlParameter[] pSqls =
                        new System.Data.SqlClient.SqlParameter[_dicParamNameValue.Count];

                    int iSql = 0;
                    foreach (string name in _dicParamNameValue.Keys)
                    {
                        pSqls[iSql++] = new System.Data.SqlClient.SqlParameter(name, _dicParamNameValue[name]);
                    }
                    return pSqls;
                case eDBType.Access:
                    System.Data.OleDb.OleDbParameter[] oSqls =
                      new System.Data.OleDb.OleDbParameter[_dicParamNameValue.Count];

                    int iOledb = 0;
                    foreach (string name in _dicParamNameValue.Keys)
                    {
                        oSqls[iOledb++] = new System.Data.OleDb.OleDbParameter(name, _dicParamNameValue[name]);
                    }
                    return oSqls;
                case eDBType.MySql:
                case eDBType.DB2:
                case eDBType.Sybase:
                default:
                    break;
            }

            return new System.Data.Common.DbParameter[0];
        }
コード例 #9
0
        private void DisplayDataForQuery()
        {
            dataGridView1.DataSource = null;

            if (queryBuilder.MetadataProvider != null && queryBuilder.MetadataProvider.Connected)
            {
                if (queryBuilder.MetadataProvider is OracleMetadataProvider)
                {
                    System.Data.OracleClient.OracleCommand command = (System.Data.OracleClient.OracleCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
                    command.CommandText = queryBuilder.SQL;

                    // handle the query parameters
                    if (queryBuilder.Parameters.Count > 0)
                    {
                        for (int i = 0; i < queryBuilder.Parameters.Count; i++)
                        {
                            if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
                            {
                                System.Data.OracleClient.OracleParameter parameter = new System.Data.OracleClient.OracleParameter();
                                parameter.ParameterName = queryBuilder.Parameters[i].FullName;
                                parameter.DbType        = queryBuilder.Parameters[i].DataType;
                                command.Parameters.Add(parameter);
                            }
                        }

                        using (QueryParametersForm qpf = new QueryParametersForm(command))
                        {
                            qpf.ShowDialog();
                        }
                    }

                    System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(command);
                    DataSet dataset = new DataSet();

                    try
                    {
                        adapter.Fill(dataset, "QueryResult");
                        dataGridView1.DataSource = dataset.Tables["QueryResult"];
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "SQL query error");
                    }
                } //end oracle (ms)
            }     //end check if connected
        }         //end method
コード例 #10
0
ファイル: OracleHelp2.cs プロジェクト: xinqinglhj/AddCode
        // ===========================================
        // ==用hashTable对数据库进行insert,update,del操作,注意此时只能用默认的数据库连接"connstr"==
        // ============================================

        #region 根据表名及哈稀表自动插入数据库 用法:Insert("test",ht)
        public int Insert(string TableName, Hashtable ht)
        {
            OracleParameter[]     Parms = new OracleParameter[ht.Count];
            IDictionaryEnumerator et    = ht.GetEnumerator();
            DataTable             dt    = GetTabType(TableName);

            System.Data.OracleClient.OracleType otype;
            int size = 0;
            int i    = 0;

            while (et.MoveNext()) // 作哈希表循环
            {
                GetoType(et.Key.ToString().ToUpper(), dt, out otype, out size);
                System.Data.OracleClient.OracleParameter op = MakeParam(":" + et.Key.ToString(), otype, size, et.Value.ToString());
                Parms[i] = op; // 添加SqlParameter对象
                i        = i + 1;
            }
            string str_Sql = GetInsertSqlbyHt(TableName, ht); // 获得插入sql语句
            int    val     = ExecuteNonQuery(str_Sql, Parms);

            return(val);
        }
コード例 #11
0
        internal static IDataParameter AddRefCursor(IDbCommand command)
        {
#if ODP
            Oracle.DataAccess.Client.OracleParameter parameter = new Oracle.DataAccess.Client.OracleParameter();
            parameter.ParameterName = "p_cursor";
            parameter.OracleDbType  = Oracle.DataAccess.Client.OracleDbType.RefCursor;
            parameter.Direction     = ParameterDirection.Output;

            ((Oracle.DataAccess.Client.OracleCommand)command).Parameters.Add(parameter);
            return(parameter);
#elif OracleClient
            System.Data.OracleClient.OracleParameter parameter = new System.Data.OracleClient.OracleParameter();
            parameter.ParameterName = "p_cursor";
            parameter.DbType        = (DbType)System.Data.OracleClient.OracleType.Cursor;
            parameter.Direction     = ParameterDirection.Output;

            ((System.Data.OracleClient.OracleCommand)command).Parameters.Add(parameter);
            return(parameter);
#else
            throw new NotImplementedException("AddRefCursor is only implemented for Oracle database access.");
#endif
        }
コード例 #12
0
ファイル: Class1.cs プロジェクト: ycysvler/DataImport
 private void updateDbByID()
 {
     System.Data.OracleClient.OracleParameter[]
     ps = new System.Data.OracleClient.OracleParameter[7];
     System.Data.OracleClient.OracleParameter p1 = new System.Data.OracleClient.OracleParameter("I_EnTest_Work_Id", this.taskInfo.id);
     System.Data.OracleClient.OracleParameter p2 = new System.Data.OracleClient.OracleParameter("I_EnTest_Times", System.Data.OracleClient.OracleType.Int32);
     p2.Value = this.times;
     System.Data.OracleClient.OracleParameter p3 = new System.Data.OracleClient.OracleParameter("I_Fk_Time_Name", this.dataScriptRule.DesBusinessPk);
     System.Data.OracleClient.OracleParameter p4 = new System.Data.OracleClient.OracleParameter("I_Object_Table_Name", this.dataScriptRule.DesTable);
     System.Data.OracleClient.OracleParameter p5 = new System.Data.OracleClient.OracleParameter("I_Source_Table_Name", tableName);
     System.Data.OracleClient.OracleParameter p6 = new System.Data.OracleClient.OracleParameter("O_Return_Int", System.Data.OracleClient.OracleType.Int32);
     p6.Direction = ParameterDirection.Output;
     System.Data.OracleClient.OracleParameter p7 = new System.Data.OracleClient.OracleParameter("O_Return_String", System.Data.OracleClient.OracleType.VarChar, 100);
     p7.Direction = ParameterDirection.Output;
     ps[0]        = p1;
     ps[1]        = p2;
     ps[2]        = p3;
     ps[3]        = p4;
     ps[4]        = p5;
     ps[5]        = p6;
     ps[6]        = p7;
     object[] prout = OracleHelper.ExcuteProc("Process_Date_Server.Process_Temp_Into_Oragin", 2, ps);
 }
コード例 #13
0
        /// <summary>
        /// 初始化 com.individual.helper.ComDbParameter 类的新实例
        /// </summary>
        /// <param name="DataBaseType">数据库类型</param>
        /// <param name="ParameterName">参数名称</param>
        /// <param name="DbType">参数数据类型</param>
        /// <param name="Value">参数值</param>
        /// <param name="Direction">该值指示参数是只可输入、只可输出、双向还是存储过程返回值参数,System.Data.ParameterDirection 值之一</param>
        public static DbParameter CreateDbParameter(DataBaseType DataBaseType, string ParameterName, DbParameterType DbType, object Value, ParameterDirection Direction)
        {
            if (DataBaseType == helper.DataBaseType.Access)
            {
                System.Data.OleDb.OleDbParameter param = new System.Data.OleDb.OleDbParameter(ParameterName, GetOleDbType(DbType));
                param.Direction = Direction;
                param.Value = Value;
                param.Size = 8000;
                return param;
            }
            else if (DataBaseType == helper.DataBaseType.MySql)
            {
                MySql.Data.MySqlClient.MySqlParameter param = new MySql.Data.MySqlClient.MySqlParameter(ParameterName, GetMySqlDbType(DbType));
                param.Direction = Direction;
                param.Value = Value;
                param.Size = 8000;
                return param;
            }

            else if (DataBaseType == helper.DataBaseType.Oracle)
            {
                System.Data.OracleClient.OracleParameter param = new System.Data.OracleClient.OracleParameter(ParameterName, GetOracleType(DbType));
                param.Direction = Direction;
                param.Value = Value;
                param.Size = 8000;
                return param;
            }
            else
            {
                System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter(ParameterName, GetSqlDbType(DbType));

                param.Direction = Direction;
                param.Value = Value;
                param.Size = 8000;
                return param;
            }
        }
コード例 #14
0
        public static DbParameter[] GetSelectParamArray <T>(T t, MyDBType mydbtype)
        {
            string tStr = string.Empty;

            if (t == null)
            {
                return(null);
            }
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            if (properties.Length <= 0)
            {
                return(null);
            }
            System.Collections.Generic.List <DbParameter> list = new System.Collections.Generic.List <DbParameter>();
            DbParameter[] paramArray = null;
            int           lenght     = 0;

            for (int i = 0; i < properties.Length; i++)
            {
                string name  = properties[i].Name;              //����
                object value = properties[i].GetValue(t, null); //ֵ
                if (value != null)
                {
                    if (CanAdd(name, t.GetType().Name))
                    {
                        lenght++;
                    }
                }
            }
            switch (mydbtype)
            {
            case MyDBType.Access:
                paramArray = new System.Data.OleDb.OleDbParameter[lenght];
                break;

            case MyDBType.Sql:
                paramArray = new System.Data.SqlClient.SqlParameter[lenght];
                break;

            case MyDBType.Oracle:
                paramArray = new System.Data.OracleClient.OracleParameter[lenght];
                break;
            }

            int index = 0;

            for (int i = 0; i < properties.Length; i++)
            {
                string name  = properties[i].Name;              //����
                object value = properties[i].GetValue(t, null); //ֵ
                string type  = properties[i].PropertyType.Name;
                if (value != null)
                {
                    if (CanAdd(name, t.GetType().Name))
                    {
                        switch (mydbtype)
                        {
                        case MyDBType.Access:
                            paramArray[index] = new System.Data.OleDb.OleDbParameter("@" + name, value);
                            break;

                        case MyDBType.Sql:
                            paramArray[index] = new System.Data.SqlClient.SqlParameter("@" + name, value);
                            break;

                        case MyDBType.Oracle:
                            paramArray[index] = new System.Data.OracleClient.OracleParameter(":" + name, value);
                            break;
                        }
                        index++;
                    }
                }
            }

            return(paramArray);
        }
コード例 #15
0
        protected override void PrepareParameters()
        {
            // Prepare parameters
            SQLParameter parameter;

            for (int index = 0; index < _parameterIndexes.Length; index++)
            {
                parameter = Parameters[_parameterIndexes[index]];
                System.Data.OracleClient.OracleParameter oracleParameter = (System.Data.OracleClient.OracleParameter)_command.CreateParameter();
                oracleParameter.ParameterName = String.Format(":{0}", parameter.Name);
                oracleParameter.IsNullable    = true;
                switch (parameter.Direction)
                {
                case SQLDirection.Out: oracleParameter.Direction = System.Data.ParameterDirection.Output; break;

                case SQLDirection.InOut: oracleParameter.Direction = System.Data.ParameterDirection.InputOutput; break;

                case SQLDirection.Result: oracleParameter.Direction = System.Data.ParameterDirection.ReturnValue; break;

                default: oracleParameter.Direction = System.Data.ParameterDirection.Input; break;
                }

                if (parameter.Type is SQLStringType)
                {
                    oracleParameter.OracleType = System.Data.OracleClient.OracleType.VarChar;
                    oracleParameter.Size       = ((SQLStringType)parameter.Type).Length;
                }
                else if (parameter.Type is SQLBooleanType)
                {
                    oracleParameter.OracleType = System.Data.OracleClient.OracleType.Int32;
                }
                else if (parameter.Type is SQLIntegerType)
                {
                    switch (((SQLIntegerType)parameter.Type).ByteCount)
                    {
                    case 1: oracleParameter.OracleType = System.Data.OracleClient.OracleType.Byte; break;

                    case 2: oracleParameter.OracleType = System.Data.OracleClient.OracleType.Int16; break;

                    case 8:
                        oracleParameter.OracleType = System.Data.OracleClient.OracleType.Number;
                        oracleParameter.Precision  = 20;
                        oracleParameter.Scale      = 0;
                        break;

                    default: oracleParameter.OracleType = System.Data.OracleClient.OracleType.Int32; break;
                    }
                }
                else if (parameter.Type is SQLNumericType)
                {
                    SQLNumericType type = (SQLNumericType)parameter.Type;
                    oracleParameter.OracleType = System.Data.OracleClient.OracleType.Number;
                    oracleParameter.Precision  = type.Precision;
                    oracleParameter.Scale      = type.Scale;
                }
                else if (parameter.Type is SQLBinaryType)
                {
                    oracleParameter.OracleType = System.Data.OracleClient.OracleType.Blob;
                }
                else if (parameter.Type is SQLTextType)
                {
                    oracleParameter.OracleType = System.Data.OracleClient.OracleType.Clob;
                }
                else if (parameter.Type is SQLDateTimeType)
                {
                    oracleParameter.OracleType = System.Data.OracleClient.OracleType.DateTime;
                }
                else if (parameter.Type is SQLDateType)
                {
                    oracleParameter.OracleType = System.Data.OracleClient.OracleType.DateTime;
                }
                else if (parameter.Type is SQLTimeType)
                {
                    oracleParameter.OracleType = System.Data.OracleClient.OracleType.DateTime;
                }
                else if (parameter.Type is SQLGuidType)
                {
                    oracleParameter.OracleType = System.Data.OracleClient.OracleType.Char;
                    oracleParameter.Size       = 24;
                }
                else if (parameter.Type is SQLMoneyType)
                {
                    oracleParameter.OracleType = System.Data.OracleClient.OracleType.Number;
                    oracleParameter.Precision  = 28;
                    oracleParameter.Scale      = 8;
                }
                else
                {
                    throw new ConnectionException(ConnectionException.Codes.UnknownSQLDataType, parameter.Type.GetType().Name);
                }
                _command.Parameters.Add(oracleParameter);
            }
        }
コード例 #16
0
ファイル: DbHelperOra.cs プロジェクト: coasline/STBC
 /// <summary>
 /// 执行带一个存储过程参数的的SQL语句。
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSql(string SQLString,string content)
 {
     using (OracleConnection connection = new OracleConnection(connectionString))
     {
         OracleCommand cmd = new OracleCommand(SQLString,connection);
         System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@content", OracleDbType.NVarchar2);
         myParameter.Value = content ;
         cmd.Parameters.Add(myParameter);
         try
         {
             connection.Open();
             int rows=cmd.ExecuteNonQuery();
             return rows;
         }
         catch(System.Data.OracleClient.OracleException E)
         {
             throw new Exception(E.Message);
         }
         finally
         {
             cmd.Dispose();
             connection.Close();
         }
     }
 }
コード例 #17
0
ファイル: DbHelperOra.cs プロジェクト: coasline/STBC
 /// <summary>
 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
 /// </summary>
 /// <param name="strSQL">SQL语句</param>
 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
 {
     using (OracleConnection connection = new OracleConnection(connectionString))
     {
         OracleCommand cmd = new OracleCommand(strSQL,connection);
         System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@fs", OracleDbType.LongRaw);
         myParameter.Value = fs ;
         cmd.Parameters.Add(myParameter);
         try
         {
             connection.Open();
             int rows=cmd.ExecuteNonQuery();
             return rows;
         }
         catch(System.Data.OracleClient.OracleException E)
         {
             throw new Exception(E.Message);
         }
         finally
         {
             cmd.Dispose();
             connection.Close();
         }
     }
 }
コード例 #18
0
 protected override DbParameter GetOneDbParameter(string name, object value)
 {
     System.Data.OracleClient.OracleParameter cur = new System.Data.OracleClient.OracleParameter(name, value);
     return(cur);
 }
コード例 #19
0
        private void DisplayDataForQuery()
        {
            //AppGlobals.AppMessages.DisplayAlertMessage("DisplayDataForQuery");
            dataGridView1.DataSource = null;

            if (queryBuilder.MetadataProvider != null && queryBuilder.MetadataProvider.Connected)
            {
                if (queryBuilder.MetadataProvider is MSSQLMetadataProvider)
                {
                    SqlCommand command = (SqlCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
                    command.CommandText = queryBuilder.SQL;

                    // handle the query parameters
                    if (queryBuilder.Parameters.Count > 0)
                    {
                        for (int i = 0; i < queryBuilder.Parameters.Count; i++)
                        {
                            if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
                            {
                                SqlParameter parameter = new SqlParameter();
                                parameter.ParameterName = queryBuilder.Parameters[i].FullName;
                                parameter.DbType        = queryBuilder.Parameters[i].DataType;
                                command.Parameters.Add(parameter);
                            }
                        }

                        using (QueryParametersForm qpf = new QueryParametersForm(command))
                        {
                            qpf.ShowDialog();
                        }
                    }

                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataSet        dataset = new DataSet();

                    try
                    {
                        adapter.Fill(dataset, "QueryResult");
                        dataGridView1.DataSource = dataset.Tables["QueryResult"];
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "SQL query error");
                    }
                }//end sql server

                //if (queryBuilder.MetadataProvider is MSSQLCEMetadataProvider)
                //{
                //    SqlCeCommand command = (SqlCeCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
                //    command.CommandText = queryBuilder.SQL;

                //    // handle the query parameters
                //    if (queryBuilder.Parameters.Count > 0)
                //    {
                //        for (int i = 0; i < queryBuilder.Parameters.Count; i++)
                //        {
                //            if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
                //            {
                //                SqlCeParameter parameter = new SqlCeParameter();
                //                parameter.ParameterName = queryBuilder.Parameters[i].FullName;
                //                parameter.DbType = queryBuilder.Parameters[i].DataType;
                //                command.Parameters.Add(parameter);
                //            }
                //        }

                //        using (QueryParametersForm qpf = new QueryParametersForm(command))
                //        {
                //            qpf.ShowDialog();
                //        }
                //    }

                //    SqlCeDataAdapter adapter = new SqlCeDataAdapter(command);
                //    DataSet dataset = new DataSet();

                //    try
                //    {
                //        adapter.Fill(dataset, "QueryResult");
                //        dataGridView1.DataSource = dataset.Tables["QueryResult"];
                //    }
                //    catch (Exception ex)
                //    {
                //        MessageBox.Show(ex.Message, "SQL query error");
                //    }
                //}//end sql server ce

                if (queryBuilder.MetadataProvider is OLEDBMetadataProvider)
                {
                    //AppGlobals.AppMessages.DisplayAlertMessage("for OLEDBMetadataProvider");
                    OleDbCommand command = (OleDbCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
                    command.CommandText = queryBuilder.SQL;

                    // handle the query parameters
                    if (queryBuilder.Parameters.Count > 0)
                    {
                        for (int i = 0; i < queryBuilder.Parameters.Count; i++)
                        {
                            if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
                            {
                                OleDbParameter parameter = new OleDbParameter();
                                parameter.ParameterName = queryBuilder.Parameters[i].FullName;
                                parameter.DbType        = queryBuilder.Parameters[i].DataType;
                                command.Parameters.Add(parameter);
                            }
                        }

                        using (QueryParametersForm qpf = new QueryParametersForm(command))
                        {
                            qpf.ShowDialog();
                        }
                    }

                    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                    DataSet          dataset = new DataSet();

                    try
                    {
                        adapter.Fill(dataset, "QueryResult");
                        dataGridView1.DataSource = dataset.Tables["QueryResult"];
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "SQL query error");
                    }
                    //AppGlobals.AppMessages.DisplayAlertMessage("end for OLEDBMetadataProvider");
                }//end ole schemaRoot

                if (queryBuilder.MetadataProvider is ODBCMetadataProvider)
                {
                    OdbcCommand command = (OdbcCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
                    command.CommandText = queryBuilder.SQL;

                    // handle the query parameters
                    if (queryBuilder.Parameters.Count > 0)
                    {
                        for (int i = 0; i < queryBuilder.Parameters.Count; i++)
                        {
                            if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
                            {
                                OdbcParameter parameter = new OdbcParameter();
                                parameter.ParameterName = queryBuilder.Parameters[i].FullName;
                                parameter.DbType        = queryBuilder.Parameters[i].DataType;
                                command.Parameters.Add(parameter);
                            }
                        }

                        using (QueryParametersForm qpf = new QueryParametersForm(command))
                        {
                            qpf.ShowDialog();
                        }
                    }

                    OdbcDataAdapter adapter = new OdbcDataAdapter(command);
                    DataSet         dataset = new DataSet();

                    try
                    {
                        adapter.Fill(dataset, "QueryResult");
                        dataGridView1.DataSource = dataset.Tables["QueryResult"];
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "SQL query error");
                    }
                }//end odbc


                if (queryBuilder.MetadataProvider is OracleMetadataProvider)
                {
                    System.Data.OracleClient.OracleCommand command = (System.Data.OracleClient.OracleCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
                    command.CommandText = queryBuilder.SQL;

                    // handle the query parameters
                    if (queryBuilder.Parameters.Count > 0)
                    {
                        for (int i = 0; i < queryBuilder.Parameters.Count; i++)
                        {
                            if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
                            {
                                System.Data.OracleClient.OracleParameter parameter = new System.Data.OracleClient.OracleParameter();
                                parameter.ParameterName = queryBuilder.Parameters[i].FullName;
                                parameter.DbType        = queryBuilder.Parameters[i].DataType;
                                command.Parameters.Add(parameter);
                            }
                        }

                        using (QueryParametersForm qpf = new QueryParametersForm(command))
                        {
                            qpf.ShowDialog();
                        }
                    }

                    System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(command);
                    DataSet dataset = new DataSet();

                    try
                    {
                        adapter.Fill(dataset, "QueryResult");
                        dataGridView1.DataSource = dataset.Tables["QueryResult"];
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "SQL query error");
                    }
                } //end oracle (ms)
            }     //end check if connected
        }         //end method
コード例 #20
0
ファイル: DbHelper.cs プロジェクト: pcstx/OA
        /// <summary>
        /// 生成参数
        /// </summary>
        /// <param name="ParamName"></param>
        /// <param name="DbType"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        public DbParameter MakeParam(string ParamName, DbType DbType, int Size)
        {
            System.Data.SqlClient.SqlParameter sparam;
            System.Data.OracleClient.OracleParameter oparam;
            switch (SDBType)
            {
                case "SqlServer":
                    if (Size > 0)
                        sparam = new System.Data.SqlClient.SqlParameter(ParamName, (SqlDbType)DbType, Size);
                    else
                        sparam = new System.Data.SqlClient.SqlParameter(ParamName, (SqlDbType)DbType);
                    return sparam;
                case "Oracle":

                    if (Size > 0)
                        oparam = new System.Data.OracleClient.OracleParameter(ParamName, (System.Data.OracleClient.OracleType)DbType, Size);
                    else
                        oparam = new System.Data.OracleClient.OracleParameter(ParamName, (System.Data.OracleClient.OracleType)DbType);
                    return oparam;
                default:
                    if (Size > 0)
                        sparam = new System.Data.SqlClient.SqlParameter(ParamName, (SqlDbType)DbType, Size);
                    else
                        sparam = new System.Data.SqlClient.SqlParameter(ParamName, (SqlDbType)DbType);
                    return sparam;
            }
        }
コード例 #21
0
        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("ÕýÔÚ¼ìË÷");
            Application.DoEvents();
            Neusoft.FrameWork.Management.Connection.Instance = new System.Data.OracleClient.OracleConnection(Neusoft.FrameWork.Management.Connection.Instance.ConnectionString);
            Neusoft.FrameWork.Management.Connection.Instance.Open();

            Neusoft.FrameWork.Management.DataBaseManger m = new Neusoft.FrameWork.Management.DataBaseManger();
            DataSet ds = new DataSet();

            if (m.ExecQuery("select id,name from com_sql", ref ds) == -1)
            {
                Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm();
                MessageBox.Show(m.Err);
                return;
            }
            //Neusoft.FrameWork.Management.Connection.Instance.Close();
            //Neusoft.FrameWork.Management.Connection.Instance = new Oracle.DataAccess.Client.OracleConnection(Neusoft.FrameWork.Management.Connection.Instance.ConnectionString);
            //Neusoft.FrameWork.Management.Connection.Instance.Open();

            Neusoft.FrameWork.Management.PublicTrans.BeginTransaction();
            m.SetTrans(Neusoft.FrameWork.Management.PublicTrans.Trans);

            //Neusoft.FrameWork.Management.Transaction t = new Neusoft.FrameWork.Management.Transaction(Neusoft.FrameWork.Management.Connection.Instance);
            //t.BeginTransaction();
            //m.SetTrans(t.Trans);

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                string s = "update com_sql set name1 = :a where id='" + dr[0].ToString() + "'";
                System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(s);
                command.Connection  = Neusoft.FrameWork.Management.Connection.Instance as System.Data.OracleClient.OracleConnection;
                command.CommandText = s;
                command.CommandType = System.Data.CommandType.Text;
                command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as System.Data.OracleClient.OracleTransaction;


                System.Data.OracleClient.OracleParameter param = command.Parameters.Add("a", System.Data.OracleClient.OracleType.Clob);
                param.Direction = System.Data.ParameterDirection.Input;

                // Assign Byte Array to Oracle Parameter
                param.Value = dr[1].ToString();
                // Step 5
                // Execute the Anonymous PL/SQL Block
                try
                {
                    if (command.ExecuteNonQuery() == -1)
                    {
                        Neusoft.FrameWork.Management.PublicTrans.RollBack();
                        Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm();
                        MessageBox.Show(m.Err);
                        return;
                    }
                }
                catch (Exception ex)
                {
                    Neusoft.FrameWork.Management.PublicTrans.RollBack();
                    Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm();
                    MessageBox.Show(ex.Message);
                    return;
                }


                //if (m.InputBlob(s, b)== -1)
                //{
                //    t.RollBack();
                //    Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm();
                //    MessageBox.Show(m.Err);
                //    return;
                //}
                Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm(dr[0].ToString() + "\n" + dr[1].ToString());
                Application.DoEvents();
            }
            Neusoft.FrameWork.Management.PublicTrans.Commit();

            Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm();
            MessageBox.Show("³É¹¦£¡");
        }
コード例 #22
0
        public static DbParameter[] GetParamArray <T>(T t, MyDBType mydbtype)
        {
            string tStr = string.Empty;

            if (t == null)
            {
                return(null);
            }
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            if (properties.Length <= 0)
            {
                return(null);
            }
            System.Collections.Generic.List <DbParameter> list = new System.Collections.Generic.List <DbParameter>();
            DbParameter[] paramArray = null;
            switch (mydbtype)
            {
            case MyDBType.Access:
                paramArray = new System.Data.OleDb.OleDbParameter[properties.Length];
                break;

            case MyDBType.Sql:
                paramArray = new System.Data.SqlClient.SqlParameter[properties.Length];
                break;

            case MyDBType.Oracle:
                paramArray = new System.Data.OracleClient.OracleParameter[properties.Length];
                break;
            }


            for (int i = 0; i < properties.Length; i++)
            {
                string name  = properties[i].Name;              //����
                object value = properties[i].GetValue(t, null); //ֵ
                string type  = properties[i].PropertyType.Name;

                switch (mydbtype)
                {
                case MyDBType.Access:
                    if (name.ToLower() == "LIFECYCLE".ToLower() && value.ToString() == "0")
                    {
                        paramArray[i] = new System.Data.OleDb.OleDbParameter("@" + name, DBNull.Value);
                    }
                    else
                    {
                        paramArray[i] = new System.Data.OleDb.OleDbParameter("@" + name, value);
                    }
                    break;

                case MyDBType.Sql:
                    if (name.ToLower() == "LIFECYCLE".ToLower() && value.ToString() == "0")
                    {
                        paramArray[i] = new System.Data.SqlClient.SqlParameter("@" + name, DBNull.Value);
                    }
                    else
                    {
                        paramArray[i] = new System.Data.SqlClient.SqlParameter("@" + name, value);
                    }
                    break;

                case MyDBType.Oracle:
                    if (name.ToLower() == "LIFECYCLE".ToLower() && value.ToString() == "0")
                    {
                        paramArray[i] = new System.Data.OracleClient.OracleParameter(":" + name, DBNull.Value);
                    }
                    else
                    {
                        paramArray[i] = new System.Data.OracleClient.OracleParameter(":" + name, value);
                    }
                    break;
                }
            }

            return(paramArray);
        }