SQL Table to C#

Question

I have a question.

First, I need to tell you guys that I'm kind of new to programming and tried a tutorial.

Now my question is: How can I get multiple Sql tables to c#. At the moment my C# code is only reading from one table. But I need information of more tables.

At the moment I'm only getting the information from the table: "Domeinnaam" Now I also need to get the information from the tables: X & Y.

How should I do this?

My code:

Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using DNDB.Models;
using System.Data.SqlClient;

namespace DNDB.Controllers
{
public class DomeinnaamController : Controller
{
    // GET: Domeinnaam
    public ActionResult Index()
    {
        var entities = new DomeinnaambeheerEntities1();

        return View(entities.Domeinnaam.ToList());
    }
    public ActionResult CreateDomainName()
    {
        return View(); 
    }

The automatic generated page:

@model IEnumerable<DNDB.Models.Domeinnaam>

@foreach (var item in Model)
{
<tr>
    <td>
        @Html.DisplayFor(modelItem => item.IsActief)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.Naam)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.TLD)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.DatumRegistratie)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.Omschrijving)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.DatumOpzeg)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.EigenaarID)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.Opmerking)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.BeheerAccountID)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.KlantID) 
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.RegistrarID)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.BetaaldVan)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.BetaaldTot)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.AfspraakPrijs)
    </td>
    <td>
        @Html.ActionLink("Edit", "Edit", new { id = item.ID })
        @Html.ActionLink("Details", "Details", new { id = item.ID })
        @Html.ActionLink("Delete", "Delete", new { id = item.ID })
    </td>
</tr>
}

Createdomeinnaam.cshtml

@model DNDB.Models.Domeinnaam

@Styles.Render("~/Content/StyleSheet.css")
@{
ViewBag.Title = "CreateDomainName";
}
@using (Html.BeginForm())
{
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>CreateDomainName</title>
</head>
<body> 

    <label>IsActief</label>
    @Html.CheckBoxFor(m => m.IsActief) 
    <label>Naam</label>
    @Html.TextBoxFor(m => m.Naam) 
    <label>TLD</label><br />
    @Html.TextBoxFor(m => m.TLD) 
    <label>DatumRegistratie</label><br />   
    @Html.TextBoxFor(m => m.DatumRegistratie) 
    <label>Omschrijving</label><br />
    @Html.TextBoxFor(m => m.Omschrijving)   
    <label>OpzegDatum</label><br />
    @Html.TextBoxFor(m => m.DatumOpzeg)
    <label>EigenaarID</label><br />
    @Html.TextBoxFor(m => m.EigenaarID)
    <label>Opmerking</label><br />
    @Html.TextBoxFor(m => m.Opmerking)
    <label>BeheerAccountID</label><br /> 
    @Html.TextBoxFor(m => m.BeheerAccountID)
    <label>KlantID</label><br />
    @Html.TextBoxFor(m => m.KlantID)
    <label>RegistrarID</label><br />
    @Html.TextBoxFor(m => m.RegistrarID)
    <label>BetaaldVan</label><br />
    @Html.TextBoxFor(m => m.BetaaldVan)
    <label>BetaaldTot</label><br />
    @Html.TextBoxFor(m => m.BetaaldTot)
    <label>Prijsafspraak</label><br />
    @Html.TextBoxFor(m => m.AfspraakPrijs)
    <br />

</body>
</html>

<input type="submit" value="add">
}

Sorry if my question is not 100% clear. The explenation is also a bit hard for me because I do not understand everything of programming. Please tell me when you need clarification.

EDIT: A code example would be amazing.

Thanks alot guys.

If you imported those tables in Entity Framework, they should be available through your entities object. Have you tried entities.X and entities.Y? — Alexandru Pupsa, Dec 19 '16 at 09:08
Possible duplicate of [How to fill Dataset with multiple tables?](http://stackoverflow.com/questions/11345761/how-to-fill-dataset-with-multiple-tables) — huse.ckr, Dec 19 '16 at 09:09
@AlexandruPupsa Iknow that is optianal but I do not know how to add that part to my code. Where should I add the line to do it? — mrminer, Dec 19 '16 at 09:11
You want to add it in your `GET` action so for example if you have a view named Create with the attribute `GET` you'll add it in there and then in the `POST` action you'll get those values and do what you want with them i.e. store them in the database. Of course eventually you'll want to separate the application into n-tier or some other form of architecture — Izzy, Dec 19 '16 at 09:16
@Izzy A code example would be amazing! :) Because I do not know where exactly to add the line. — mrminer, Dec 19 '16 at 09:22
So you want two query to different tables and view the result? Do these tables have any relation? — Izzy, Dec 19 '16 at 09:26
@Izzy EDIT: Yes they do, all the tables I want to add are linked to "Domeinnaam". To be precize I want to read from tables Domeinnaam, X, Y and get them in my C# Database — mrminer, Dec 19 '16 at 09:29
The Y table must be present in your entities variable like entities.TableY and entities.TableX thats how entity framework is designed for. — Hameed Syed, Dec 19 '16 at 09:42
@HameedSyed I also know that, but where do I need to add the code? public class DomeinnaamController : Controller { // GET: Domeinnaam public ActionResult Index() { var entities = new DomeinnaambeheerEntities1(); return View(entities.Domeinnaam.ToList()); } public ActionResult CreateDomainName() { return View(); } — mrminer, Dec 19 '16 at 09:45
I would suggest you to just check in VS with intellisense entities.(period) must give you the remaining tables of the database if your entry point to Database from entity framework is correct . — Hameed Syed, Dec 19 '16 at 09:48
@HameedSyed As I said I'm kind of new to this, so could you explain to me how I can do that? — mrminer, Dec 19 '16 at 09:49
Why don't you create another Controller for the X table and make it similar to the one you already have? — Alexandru Pupsa, Dec 19 '16 at 09:54
@AlexandruPupsa You mean: public class Domeinnaam1Controller : Controller instead of public class DomeinnaamController : Controller with the same code as my domeinnaamcontroller? But only change the table? — mrminer, Dec 19 '16 at 09:56
Just type anywhere after the line "var entities = new DomeinnaambeheerEntities1();" -> "entities." and see what suggestions you have — Alexandru Pupsa, Dec 19 '16 at 09:57
Yes, but call it XController, because you want the same thing for X table, right? — Alexandru Pupsa, Dec 19 '16 at 09:58
@AlexandruPupsa Yes that is right but how does my automatic generated page know what table to use? — mrminer, Dec 19 '16 at 10:00

score 2 · Answer 1 · answered Dec 19 '16 at 10:04

2

If I understand correctly you want to show data from three different tables. I'm assuming you want to show the data only where it matches in these three tables. So what you want to do is write a linq query first as

var joinQuery = from d in entities.Domeinnaam
                        join x in entities.X on d.DomeinnaamId equals x.XId
                        join y in entities.Y on d.DomeinnaamId equals y.YId
                        into dom
                        select new { Domeinnaam = dom };

Please note - I do not know the exact structure of your entities so I'm assuming here. You can change it accordingly

Now you want to create a class in your Models folder and name it whatever I've named it SomeViewModel this will be our model which we will use. For now the class only includes a property of

public IEnumerable<Domeinnaam> Domeinnaam { get; set; }

Now to your comments on where to add this code. As I mentioned to get going you can add it to the view you want to return it to. For example if you've got a view named Index where you want this information displayed then you will write it in the Action named Index which is in your controller so lets assume we have a controller named SomeController this is how it will look

public class SomeController : Controller
{
    public ActionResult Index()
    {
        var viewModel = new SomeViewModel();

        var entities = new DomeinnaambeheerEntities1();

        //change the below query accordingly
        var joinQuery = from d in entities.Domeinnaam
                        join x in entities.X on d.DomeinnaamId equals x.XId
                        join y in entities.Y on d.DomeinnaamId equals y.YId
                        into dom
                        select new { Domeinnaam = dom };

        viewModel.Domeinnaam = joinQuery;

        return View(viewModel);
    }
}

The SomeViewModel class will look like

public class SomeViewModel
{
    public IEnumerable<Domeinnaam> Domeinnaam { get; set; }
}

In your view named Index you want to pass in SomeViewModel

This should give you the basic idea. If you have any questions please let me know

answered Dec 19 '16 at 10:04

Izzy

6,740
7
40
84

thanks alot for this Izzy, I got one question. How can I let my auto generated page know from what table it should pick the information ? Like this, @Html.DisplayFor(modelItem => item.Name) How can it know from wich table it needs to get the name? – mrminer Dec 19 '16 at 10:06
@mrminer You can add the appropriate properties in your viewModel class for example in `SomeViewModel` and so on. but you will need to provide the data for each property if you want it to be displayed in the view otherwise it'll throw an error – Izzy Dec 19 '16 at 10:11
But what code should I change? because I don't really understand you? Should I change something in:@model IEnumerable @foreach (var item in Model) Or in: @Html.DisplayFor(modelItem => item.IsActief) – mrminer Dec 19 '16 at 10:14
I will create a demo for you but you'll have to bare with me as I've got some meetings to attend now – Izzy Dec 19 '16 at 10:15
Sure, thanks alot Izzy. I really appriciate the help ! Nice person :) – mrminer Dec 19 '16 at 10:17
By the way Izzy, You get me wrong. You said;I'm assuming you want to show the data only where it matches in these three tables.But what I want to do is to get the information from 3 tables. and I need to chose wich information I show in my C# table. Like from Domeinname I want to show the name and the date, from table X I want to show the time and place and from table Y I want to show the costumer. I hope this clearifies my question a little bit ! – mrminer Dec 19 '16 at 11:04
@mrminer Can you provide an example of how you want your data to be displayed – Izzy Dec 19 '16 at 11:53
Do you understand what I mean? – mrminer Dec 19 '16 at 12:08
@mrminer Have a look at **[this](https://dotnetfiddle.net/ijMfTT)** I've put together. I've not applied any css styling to the final output but it display values as you wanted them to. Please note I've used hard coded values where as you'll be using values from a database. – Izzy Dec 19 '16 at 12:57
goodmorning Izzy, in your view. Is that a way to select wich table i want to get the information from? @foreach(var items in Model.Domeinnaam) Can I do the same with @foreach(var items in Model.X) ? – mrminer Dec 20 '16 at 06:56
@mrminer Yes you can. Add the property in `public IEnumerable X { get; set; }` and then access it. But please note you will have to provide the data for `X` – Izzy Dec 20 '16 at 08:32
could you explain: But please note you will have to provide the data for X Remember I'm kind of new to this – mrminer Dec 20 '16 at 08:49
@mrminer I meant in your `GET` method you will need to query your entity and assign it to the property of `X`. Just the way I did it for `Domeinnaam` – Izzy Dec 20 '16 at 08:51
Like this? // GET: Registrar public class XController : Controller { public ActionResult Index() { var entities = new DomeinnaambeheerEntities1(); return View(entities.X.ToList()); } public ActionResult CreateDomainName() { return View(); } – mrminer Dec 20 '16 at 08:57
@mrminer Yes that's how you'd do it. – Izzy Dec 20 '16 at 09:00
When I do: @foreach (var item in Model.Domeinnaam) I am getting this error: Severity Code Description Project File Line Suppression State Error CS1061 'IEnumerable' does not contain a definition for 'Domeinnaam' and no extension method 'Domeinnaam' accepting a first argument of type 'IEnumerable' could be found (are you missing a using directive or an assembly reference?) DNDB C:\Users\marijn\documents\visual studio 2015\Projects\DNDB\DNDB\Views\Domeinnaam\PartDomeinnaam.cshtml 4 Active – mrminer Dec 20 '16 at 09:01
You can see what code I have at the moment in that view. at the automatic generated page part – mrminer Dec 20 '16 at 09:03
@mrminer it's complaining because you don't have any data for `Domeinnaam` as you're only returning the data for `X`. As you can see in the demo I provided in the `foreach(var items in Model.Domeinnaam) { @Html.DisplayFor(m => items.Item2.Id) }` that is actually accessing `X`. I'm really struggling to put together what exactly you want here. – Izzy Dec 20 '16 at 09:34
Added, Can you take a look? :) – mrminer Dec 20 '16 at 09:37
@mrminer You've added a create section which is completely differet to what we've been discussing – Izzy Dec 20 '16 at 10:10
But do you understand my problem a bit better now? Hope you are stil willing to help – mrminer Dec 20 '16 at 10:14
I just have one question How can I choose what data I want to show? with this : [@model IEnumerable @foreach (var item in Model)] – mrminer Dec 20 '16 at 10:17

score 0 · Answer 2 · answered Dec 19 '16 at 09:09

You should be using Entity Framework for communicating with database. Have a look at https://msdn.microsoft.com/en-us/library/aa937723(v=vs.113).aspx you don't wan't to spend a lot of time typing all of that yourself if there's a framework that can do it for you (especially for large databases), unless there's a very specific reason why you can't use it.

SQL Table to C#

2 Answers2