# Miscellaneous > Ask an Expert >  can you please answe the query?

## meghana007

1.	Scenario: You have two tables Users and Products, with a key table called UserProducts. The Users table has for columns int ID and varchar(50) Name. The Products table has for columns ID and Name as well. ID on both tables are primary keys. The User Products table has int UserID and int ProductID, which link the Users and Products table. Write a select statement that joins the two tables returning Users.ID, Users.Name, Products.ID and Products.Name. The query should return all records in the Users table, and any related records from the Products table, if they exist.

----------


## meghana007

1.	You have discovered a bug in a multi-threaded windows application method that serves lookup data. The method fills a static list object full of simple data objects. When only one thread hits the method the method executes rapidly. The SQL stored procedure that is returning the results is optimized and runs quickly. You can test it using a T-SQL call and it runs in less than a millisecond. When one thread accesses the method the code returns just as quickly. But when more than one thread hits the method at the same time load up of the static list object becomes slower in an exponential fashion, driving system resource usage up and taking much longer than expected. After the list runs then the usage drops again, since the method is no longer called until the application is recycled. 

private static List<DataObject> _list = null;


        public static List<DataObject> GetList()
        {
            if (_list == null)
            {
                _list = new List<DataObject>();
                SqlDataReader reader = null;
                SqlConnection connection = 
new SqlConnection(ConnectionString);
                SqlCommand command = null;               


                // Set up a new command
                command = new SqlCommand("GetList", connection);
                command.CommandType = CommandType.StoredProcedure;
                reader = 
		command.ExecuteReader(CommandBehavior.CloseConnect  ion);

                while (reader.Read())
                {
                    DataObject instance = new DataObject();
                    instance.ID = reader["ID"] != DBNull.Value &&
 reader["ID"] != null ? (int)reader["ID"] : 0;
                    instance.Name = reader["Name"] != DBNull.Value && 
				reader["Name"] != null ? (string)reader["Name"] : 
				string.Empty;
                    _list.Add(instance);
                }

            }
        }
First, give an explanation of what is wrong and why the issue stated above is happening. Then write the code and give a detailed explanation of how you would solve this issue?

----------


## meghana007

2.	You have two factory classes that both return a generic list of a data object class. Currently you are using a switch statement to call a particular factory depending on some input. Code below the switch depends on getting the list of data objects from different sources, but processing them the same. 

            List<Product> products = null;
            switch (i)
            {
                case 1:
                    products = ProductFactory.GetProducts();
                    break;
                case 2:
                    products = ItemFactory.GetItems();
                    break;
                default:
                    break;
            }

The problem is that if you wanted to add more factories from different sources you would have to rewrite the code for the current builder class, i.e. add more statements to the switch. Since an unknown number of factory calls could be made to get the data objects from different sources (one factory returns from Oracle, one from SQL one from SAP, etc) the code needs to accommodate any new factory sources without having to recode.

Describe how you might accomplish this, write code to show your solution. Be as verbose as needed.

----------


## Pandu

Select Users.ID, Users.Name, Products.ID, Products.Name from Users LEFT OUTER JOIN Products on Users. Id = Products.ID Group by Users.ID

----------


## Island1

Even if these weren't obviously school assignment / examp prep questions, they should still be listed separately...

Good Luck.

Bill

----------

