when connect to db2 database it balzor form hangs and stuck ?

Question

I working on blazor web application working on two database

sql server and db2

i face issue when connecting to db2 to get some data

data retrieved slowly and some times hang and stuck

so i need to make best idle case to handle connection issue to db2

are this retrieving data using data reader is best practice or what

I using auto complete control on blazer that return all data

after that second function return details of selection

so are theses two api writing good code and best practice

also connection issues handling correct or there are missing

so please help me ?

Provider=IBMDA400.DataSource.1;Password=xxxx;Persist Security Info=True;User ID=xxx;Data Source=xxxxxx
[HttpGet]
        [Route("GetAllEmployee/{searchText}")]
        public IActionResult GetAllEmployee(string searchText)
        {
            var connection = new DBCredentials().GetConnectionString(_configuration["DBConnectionAppIds:jex"]);
          
         OleDbConnection con = new OleDbConnection(connection);
            string query = "";

            query = "SELECT cast(EMP.YAAN8 as varchar(20)) as EmployeeID,EMP.YAALPH AS EmployeeName FROM CRPDTA.F060116 EMP WHERE cast(EMP.YAAN8 as varchar(20)) LIKE '%" + searchText + "%' WITH UR";

           

            try
            {
                ICollection<object> ApplicationsDataValues = new List<object>();
                using (var command = con.CreateCommand())
                {
                    command.CommandText = query;
                    command.CommandType = CommandType.Text;
                   

                    command.Connection.Open();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ApplicationsDataValues.Add(new
                            {
                                EmployeeID = reader.GetFieldValue<string>(0)
                              
                             
                            });
                        }
                    }
                }
              
                return StatusCode(200, ApplicationsDataValues); // Get all users   
            }
            catch (Exception e)
            {
                return StatusCode(500, e);
            }
        }
 [HttpGet]
        [Route("GetEmployeeDetails/{searchId}")]
        public object GetEmployeeDetails(string searchId)
        {
            var connection = new DBCredentials().GetConnectionString(_configuration["DBConnectionAppIds:jex"]);
            
            
            
          
            query = "SELECT  EMP.YAAN8 AS EmployeeID,EMP.YAALPH AS EmployeeName,ltrim(rtrim(Department.MCMCU)) AS DepartementCode,EMP.YAJBCD AS JobCode,Department.MCDL01 AS DepartementName FROM CRPDTA.F0006 Department,CRPDTA.F060120 DepartmentMainLocation,CRPCTL.F0005 UDC,CRPDTA.F060116 EMP WHERE EMP.YAAN8 =" + searchId + " AND Department.MCMCU = DepartmentMainLocation.YAORMCU AND DepartmentMainLocation.YAAN8 = EMP.YAAN8 AND LTRIM(UDC.DRKY) = EMP.YAJBCD AND (LTRIM(UDC.DRSY) = '06') AND (LTRIM(UDC.DRRT) = 'G') FETCH FIRST 1 ROWS ONLY WITH UR";



            try
            {
                object ApplicationsDataValues = new object();
                using (var command = con.CreateCommand())
                {
                    command.CommandText = query;
                    command.CommandType = CommandType.Text;
                    
                    command.Connection.Open();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            
                            var result = new ObjectResult(new
                            {
                                //EmployeeID = reader.GetFieldValue<decimal>(0),
                                EmployeeID = reader.GetFieldValue<decimal>(0),
                                EmployeeName = reader.GetFieldValue<string>(1),
                                DepartementCode = reader.GetFieldValue<string>(2).ToString(),
                                JobCode = reader.GetFieldValue<string>(3),
                                DepartementName = reader.GetFieldValue<string>(4).ToString()
                            });
                            ApplicationsDataValues = result;
                        }
                        
                    }
                }
           
               
                return ApplicationsDataValues; // Get all users   
            }
            catch (Exception e)
            {
                return StatusCode(500, e);
            }
        }

Answer 1

you should be using parameters rather than building the query string dynamically in both cases, as your code allows sql injection.

using the DataReader is the fastest. you should use async queries to not tie up the request processing thread.

typically with an autocomplete and slow response, you need to do a holdoff on new requests if a pending request has not completed. You may need a different autocomplete component if it does not handle this correctly.