C#访问oracle数据库的问题

[Fanyunfeng]

发现一个很奇怪的问题，功能很简单就是从数据库中按主键取一条记录，
使用MS提供的System.Data.OracleClient作为数据提供者，使用DbParameter设置
SQL中的参数（方式1），这条SQL要执行600ms。如果使用字符串拼出整条的SQL直
接执行（方式2），则马上返回结果。

我更改了驱动，使用Oracle提供的数据提供者，则上面的两种方式的执行时间没有
什么太大的差异。

        public void SetDbType(int type)
        {
            this.type = type;
        }

        public IDataParameter Parameter(string name, DbType type, Object value)
        {
            DbParameter par = Factory().CreateParameter();

            par.DbType = type;
            par.Direction = ParameterDirection.Input;
            par.ParameterName = name;
            par.Value = value;

            return par;
        }

        public DbProviderFactory Factory()
        {
            switch (type)
            {
                case 0:
                    return System.Data.OracleClient.OracleClientFactory.Instance;
                case 1:
                    return Oracle.DataAccess.Client.OracleClientFactory.Instance;
                default:
                    return null;
            }
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            begin = DateTime.Now;
            SetDbType(0);
            LOGGER.Info("call sql by parameter...");

            DbConnection connection = Factory().CreateConnection();

            connection.ConnectionString = connectionString;
            LOGGER.Debug(String.Format("get connection."));

            connection.Open();

            LOGGER.Debug(String.Format("oppend."));

            int point = -1;

            int promotedPoint = 0;
            int perchasedPoint = 0;
            int presentedPoint = 0;
            int bound = 0;

            DbDataAdapter adapter = null;
            DbCommand command = null;
            DataTable table = null;

            try
            {
                DataRow row = null;

                table = new DataTable();
                string sql = "select BIND_FLAG,PROMOTE_SMS_POINT,BUY_SM_POINT,PRESENT_SM_NUM"+
                    " from im_mobile_maps where IM_ID=:imid";

                command = Factory().CreateCommand();

                command.Connection = connection;
                command.CommandText = sql;

                command.Parameters.Add(Parameter(":imid", DbType.String, imid));

                adapter = Factory().CreateDataAdapter();
                adapter.SelectCommand = command;


                int rows = adapter.Fill(table);

                if (rows == 0)
                {
                    return;
                }

                row = table.Rows[0];

                bound = (row["BIND_FLAG"] == DBNull.Value) ? 0 : (Decimal.ToInt32((Decimal)row["BIND_FLAG"]));
                promotedPoint = Decimal.ToInt32((Decimal)row["PROMOTE_SMS_POINT"]);
                perchasedPoint = Decimal.ToInt32((Decimal)row["BUY_SM_POINT"]);
                presentedPoint = Decimal.ToInt32((Decimal)row["PRESENT_SM_NUM"]);

                if (bound == 0)
                {
                    if (LOGGER.IsDebugEnabled)
                        LOGGER.Debug(String.Format("GetCurrentSmPoint2 {0} is unbound.", imid));

                    return;
                }

                point = promotedPoint + perchasedPoint + presentedPoint;
                LOGGER.Debug(String.Format("return value:{0}", point));

                return;
            }
            catch (Exception exception)
            {
                LOGGER.Debug("error.", exception);
            }
            finally
            {
                if (adapter != null) adapter.Dispose();
                if (command != null) command.Dispose();
                if (table != null) table.Dispose();

                LOGGER.Info("call sql by parameter end.\n");
            }
        }

[平生我自如]

顶一下