Populate Dropdown From Data Access Layer

Question

0.00/5 (No votes)

See more:

, +

Hi, I am wondering what is the best way to populate a UI dropdown from a sql database table.

Is it best to have a specific method in the data access layer for each and every drop down in the UI and use a data reader?

Do I then call this method from the middle layer and then call this from the UI form?

How do I pass the returned data up from the data access layer to the UI?

Thank you.

Posted 13-Nov-14 22:43pm

DHicks19

Add a Solution

2 solutions

Solution 2

I think these articles will give you and idea how to update the UI based on database values:

Silverlight Application with Management Entity Framework and WCF Service

And,

MVVM Silverlight Application with Entity Framework and WCF Service[^]

After reading these articles and experimenting with the source code, please feel free to let me know if you have any other questions.

Posted 19-Dec-14 9:48am

Roger C Moore

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

IpsitaMishra · Accepted Answer · 2014-11-13T22:54:00

Solution 1

It depends upon your requirement.You can create a single stored procedure for getting all the drop down data in your application.And a single service method that call the sp and return the data .You can create a common class called DropDownList.Which may look like this

C#

Public Class DropDownList
{
public int DataVlaue{get;set;}
Public string DataText{get;set;}
}

Posted 13-Nov-14 22:54pm

IpsitaMishra

v2

Comments

DHicks19 14-Nov-14 5:30am

so the best way to do this is to create a method in data access layer called "GetColours", this then calls a stored procedure on sql server.

I then create a dataset in this method and return this to the business logic layer method which is then called from the ON load form event of the UI?

IpsitaMishra 14-Nov-14 5:35am

yes.

DHicks19 14-Nov-14 6:56am

My code stops executing at da.fill and goes no further, no errors. Can you see the problem?

using (Conn)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand("GetColours", Conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds, "Colour");

return ds;

}

Never mind, fixed it. There was an issue with the connected.
What is the best way to handle exception for this method?

IpsitaMishra 14-Nov-14 7:08am

see the following link

http://programcall.com/7/aspnet/binding-dropdownlist-with-dataset-values-in-aspnet.aspx

IpsitaMishra 14-Nov-14 7:13am

Here inline query is used .You can use sp like this
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spName";

IpsitaMishra 14-Nov-14 7:15am

try
{
//your logic
} catch (SqlException exSqlType)
{
Log("Error message:" + exSqlType.Message, exSqlType);
}
catch (Exception exGen)
{
Log("Error message:" + exGen.Message, exGen);
}

DHicks19 14-Nov-14 7:20am

Thank you for your help, it is working.

One more question, so I have exception handling in the data acess layer methods and it logs the errors in an error table and emails admin.

However, do I also have exception handling in the UI form Load that calls the dataset method? If so how would this look? Do I just display a label saying an error has occured loading colours etc?

Will my exception handling still execute in the data access layer and log the error ?

thanks for your help

IpsitaMishra 14-Nov-14 7:25am

You can use the same error logging method to log the error in code behind file.