2 Replies - 3078 Views - Last Post: 28 February 2012 - 06:55 AM

#1 aklo  Icon User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 229
  • Joined: 23-January 09

Is my database model correct? + what are the standards out there

Posted 25 February 2012 - 02:37 AM

I started learning asp.net mvc3 few days ago and my

1st question is: Is this how you do a database model since placing it in views/controllers is not correct.

There are tutorials out there but they use "Entity Framework" is this better or is this standard way to use or can I do it my way?

This is in the "Models" folder in DatabaseModels.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MySql.Data.MySqlClient;
using System.Collections;

namespace News.Models
{
    public class DatabaseModels
    {
        MySqlConnection con; 
        public ArrayList getTitles(){

            con = new MySqlConnection("Server=127.0.0.1;Uid=root;Pwd=password;Database=newsdata");
            con.Open();
            ArrayList alNews = new ArrayList();
            MySqlCommand queryTitles = new MySqlCommand("SELECT * FROM newsdata",con );
            MySqlDataReader dr = queryTitles.ExecuteReader();
            while (dr.Read())
            {
                alNews.Add(dr["Title"]);
            }
            con.Close();

            return alNews;
        }
    }
}




So if the above is correct should return an ArrayList.
So I did this:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MySql.Data.MySqlClient;
using News.Models;

namespace News.Controllers
{
    public class HomeController : Controller
    {

        public ActionResult Index()
        {
            ViewBag.Message = "Welcome to ASP.NET MVC!";
            DatabaseModels dm = new DatabaseModels();
            ViewBag.Titles =  dm.getTitles();
            return View();
        }

        public ActionResult About()
        {
            ViewBag.AboutUs = "Team of developers";
            return View();
        }

        public ActionResult Forum()
        {
            return View();
        }
    }
}



meanwhile at index.cshtml

@using System.Collections;

@{
    ViewBag.Title = "Home Page";
}

<h2>@ViewBag.Message</h2>
<p>
    To learn more about ASP.NET MVC visit <a href="http://asp.net/mvc" title="ASP.NET MVC Website">http://asp.net/mvc</a>.
</p>

<table>
@{
    ArrayList al = new ArrayList();
    al = @ViewBag.Titles;    
    foreach(string newsTitle in al){
        <tr>
            <td>
                @newsTitle   
            </td>
        </tr>       
    } 
}
</table>



So is this the "correct" way of doing things assuming i'm not using entity framework?

I've search around and a lot(all) of the tutorials uses entity frame work, so it is difficult to see what are the other standards like creating database connections, retrieving of data and displaying of data using the MVC(3) way without EF.

I learnt asp.net webforms/websites so the above method is somewhat similar to having a class file with database connections and any form of manipulating data methods.

This post has been edited by aklo: 25 February 2012 - 04:11 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Is my database model correct? + what are the standards out there

#2 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: Is my database model correct? + what are the standards out there

Posted 27 February 2012 - 11:08 PM

That will work, but there's a few suggestions I'd like to give. First you should move your connection string into your web.config. This way you can easily access the connection string throughout the application without having to copy and paste the same connection string all over the place. The connection string should look like this in the web.config.

<connectionStrings>
    <add name="MySqlConString" connectionString="Server=127.0.0.1;Uid=root;Pwd=password;Database=newsdata"  providerName="MySql.Data.MySqlClient" />
  </connectionStrings>



Second, I would get away from using Arraylists(unless you're stuck using .Net 1.1). It's just not a good idea to use a container type that can allow unrelated data be stored alongside useful data. If all you are doing here is pulling a string from the database then use List<string> instead.

Third, I would recommend making use of the MySqlHelper class. It simplifies life a lot. Here's a quick example that should be close enough to what you have already that you shouldn't have a problem following it.

            // Get the connection string from the web.config file
            string conString = WebConfigurationManager.ConnectionStrings["MySqlConString"].ConnectionString;

            // Create your query
            string query = "SELECT title FROM film";

            // wrap the reader in a using block. This makes use of the fact that the
            //  reader inherits from IDisposable. Once the code leaves the using
            //  block the connection to the database will automatically close.
            using (var reader = MySqlHelper.ExecuteReader(conString, query))
            {
                // MySqlHelper takes care of opening the connection to the database
                
                List<string> Titles = new List<string>();

                while (reader.Read())
                {
                    Titles.Add(reader.GetString("title"));
                }
                GridView1.DataSource = Titles;
                GridView1.DataBind();
            }



Of course, instead of databinding a gridview you would just return the list.

This post has been edited by Nakor: 27 February 2012 - 11:11 PM

Was This Post Helpful? 1
  • +
  • -

#3 aklo  Icon User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 229
  • Joined: 23-January 09

Re: Is my database model correct? + what are the standards out there

Posted 28 February 2012 - 06:55 AM

Thanks for the tips and I will modify my code accordingly (and of course sticking with list instead of arraylist to catch any errors at compile time)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1