How to get results from database in asp.net mvc 3 using ADO.NET

Question

I wrote model class which returns a List then i pass it to view, but when the page is requested the view call the model many times and then crash the page, here's my code please help me I am new to asp.net MVC

Model

public List<string> DoWork()
    {
        List<string> results = new List<string>();
        using (SqlConnection con = new SqlConnection(@"Connection String Here"))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand(@"SELECT Column1 FROM Table1", con))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        results.Add(rdr.GetString(0));
                    }
                }
            }
        }
        return results;
    }

Controller

 public ActionResult Index()
    {
        MyData data = new MyData();
        return View(data);
    }

View

<ul>
@for (int i = 0; i <@Model.DoWork().Count; i++)
{
<li>
  @Model.DoWork()[i]
</li>
}
</ul>

Alex Peta · Accepted Answer · 2012-05-15 12:14:06Z

1

The idea is that the DB layer and View layer should be separated. This being said, you should get your DB entities in your controller action method, not in the view, and use a ViewModel pattern to spit it to the view.In your case, you have a simple view model, IEnumerable, but it can be any entity.

public ActionResult Index()
    {
        MyData data = new MyData();
        IEnumerable<string> thelist = data.DoWork();
        return View(thelist);
    }

and in yout View, use the IEnumerable as your model, and loop though it , not calling the DB to fetch your data:

@model IEnumerable<string>
......
@foreach(string s in Model)
{
  <li>@s<li>
}

edited May 15, 2012 at 12:14

answered May 15, 2012 at 12:08

Alex Peta

1,4051 gold badge15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jelo Melo Over a year ago

the way i wrote the code, is it a good approach, your comments, or how can i wrote it more better

Alex Peta Over a year ago

@JeloMelo best practices can be found on asp.net/mvc. It's recomended for scalable applications Repository pattern, and DI(dependency injection) and IoC(inversion of control).

Gabriele Petrioli · Accepted Answer · 2012-05-15 11:34:12Z

0

You are calling the DoWork method each time you want to use a result...

Store the result in a variable, and use that, instead.

<ul>
@{ List<string> works = Model.DoWork(); }
@for (int i = 0; i < works.Count; i++)
{
<li>
  @works[i]
</li>
}
</ul>

answered May 15, 2012 at 11:34

Gabriele Petrioli

197k35 gold badges275 silver badges331 bronze badges

1 Comment

Jelo Melo Over a year ago

the way i wrote the code, is it a good approach, your comments, or how can i wrote it more better.

Collectives™ on Stack Overflow

How to get results from database in asp.net mvc 3 using ADO.NET

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related