Coldfusion developer learning ASP.net needs some help with data

Hello all!

Ok so I am slowly learning ASP.NET w/ c# to build web applications and am somewhat getting the hang of it. Im finding it to be much slower but more powerful in general. One thing I am having a problem with and cant find an example with anywhere is around looping through data. Most everything I find for data management in C# is around Datagrids and such. As i work in coldfusion I like the ability to tag the fields in the HTML and fully control out put. I will code a sample of what I do in CF and I would like help converting it to asp.net w/ c#. Pardon any CF code errors im just writing quick so you will get the picture of what i am looking for.

<!--- main query to get users --->

<cfquery name="getAllUsers" datasource="#request.dsn#" username="#request.dsnu#" password="#request.dsnp#">
select fname, lname, email FROM Users
where Active = 1
</cfquery>

<table>
<tr>
<td>First</td>
<td>Last</td>
<td>Email</td>
<td>Recent Order</td>
</tr>

<!--- Now I am going to loop through main query--->
<cfloop query="getAllUsers">

<!--- Now using the loop query i want to run a query of another tablke using data from this iteration of the loop and so on for each--->
<cfquery name="getOrders" datasource="#request.dsn#" username="#request.dsnu#" password="#request.dsnp#">
select top 1 orderID, ProductName, userID, orderDate FROM Orders
where userID = #getAllUsers.userID#
order by orderDate desc
</cfquery>

<!--- output the loop with info from top level query and the query just used in this loop--->
<tr>
<td>#getAllUsers.fname#</td>
<td>#getAllUsers.lname#</td>
<td>#getAllUsers.email#</td>

<!--- check if there was a recent order--->
<cfif #recordcount.getOrders EQ 0#
<td>No recent order</td>
<cfelse>
<td>#getOrders.ProductName#</td>
</cfif>
</tr>

</cfloop>
</table>

Open in new window


I think this covers my first need. I will be posting others as I run into them butr I think this is a good start since i use this approach in almost ever app i build and its ket to converting apps over.

Thanks in advance for any help you can provide.
joedfuseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
Are you learning Web Forms, or ASP.NET MVC?
0
joedfuseAuthor Commented:
I want to start with web forms. I looked at MVC but really figured web forms would be better coming from CF. Obviously im open to anything that will help me in the long run but since this is trully a learning project I really want to understand how to customize data output
0
sammySeltzerCommented:
Hi joedfuse,

The first problem right off the bat is that for someone to help convert your cold fusion to asp.net, that person must have some knowledge of both and it is not easy.

I have played around with cold fusion a long time ago.

So, here is what I can tell you:

If I remember correctly, this:

<td>#getAllUsers.lname#</td>

Open in new window


means that a value from the database based on your query will populate a fieldname called lname.

If that still remains true, the equivalence of that in asp.net is:

<td><asp:Label ID="lnameLabel" runat="server" Text='<%# Eval("lname") %>' /></td>

Open in new window


However, one of the big differences between you CFM and asp.net is that you run the query in what we call codebehind on pageload() event and then assign a variable to the fieldname, something like this:

                      protected void Page_Load(object sender, EventArgs e)
                        {
                        if (!IsPostBack) {
				myConnection.Open();
				SqlCommand cmd1 = new SqlCommand("select top 1 orderID, ProductName, userID, orderDate FROM Orders where userID = @userid order by orderDate desc ", myConnection);
				cmd1.Parameters.AddWithValue("@userid", Session["UserId"]);
				SqlDataReader dr = cmd1.ExecuteReader();
				if (dr.Read()) {
					lnameLabel.Text = dr("lname").ToString();
                                 }
                                }
                              }

Open in new window


Then your markup will be something like this:

<td><asp:Label ID="lnameLabel" runat="server" Text="" /></td>

Open in new window


The point is that it will be extremely hard to do line by line conversions because the synthases and semantics are different but you can build on this and if you run into some snags, come back with specific questions and we could help.

Good luck.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

joedfuseAuthor Commented:
Good point on someone really knowing both. I havent read through the rest of your post yet as i wanted to post below first in case that causes any changes for you

Here is an example of the CF code using regular language instead of the sample code

I want to query users to get active users

Create a user table to display 4 columns and 2 rows. One row will be the column header the second row will loop through all the results of the user query

Inside each loop iteration I want to query a second sql table to look for recent order using a primary key of the user its currently on.

Display values from each query seemless in the second row

Basically looping through a query result and creating another query using the id of the main query

Hope this helps a bit, I will review the rest fo your answer now as well.
0
joedfuseAuthor Commented:
Also, i dont need it to be line by line conversion... just accomplish the same thing and i can extrapolate what does what.

So in your answer you were on to something with the codebehind however its missing some key parts

First query the user table, then loop through each user and then query the order table for each user. (This was the query you put in your example) - Looping through query results is where i get lost. Can you do a regular while loop through the result or do you have to load the query result to a map or array first then loop through that?

Then out put the values from both query into a table in the same row, I would like to see how it gets created using an html table and not bound to a data grid or control like that. Would a repeater be used here, again this is very unclear?
0
Bob LearnedCommented:
If you understand MVC, you could see that it more closely resembles CF than Web Form does.  In my humble opinion, the MVC platform has a lot to offer, including testability, that would warrant a first look.  You can mix HTML and logic in the view, and pass data into a view, and process like this example:

Examining the Edit Methods and Edit View
http://www.asp.net/mvc/overview/getting-started/introduction/examining-the-edit-methods-and-edit-view

@model MvcMovie.Models.Movie

@{
    ViewBag.Title = "Edit";
}
<h2>Edit</h2>
@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()    
    <div class="form-horizontal">
        <h4>Movie</h4>
        <hr />
        @Html.ValidationSummary(true)
        @Html.HiddenFor(model => model.ID)

        <div class="form-group">
            @Html.LabelFor(model => model.Title, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Title)
                @Html.ValidationMessageFor(model => model.Title)
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.ReleaseDate, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.ReleaseDate)
                @Html.ValidationMessageFor(model => model.ReleaseDate)
            </div>
        </div>
        @*Genre and Price removed for brevity.*@        
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}
<div>
    @Html.ActionLink("Back to List", "Index")
</div>
@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joedfuseAuthor Commented:
Thanks Bob

I took a look at the article you linked and yeah I dont really understand. Im sure your right in its easier than webforms IF you understand it.

My problem I guess is I dont know how to translate MVC into the query and display parts. From your example I have no idea where the queries are.  are they in the @model?
0
Bob LearnedCommented:
With MVC, the queries can be done in a data layer class, then passed to the view from the controller.  The desired approach is to use a business object for the data access, and then a view model for the view, with attributes for validation and display.

Since web programmers try to keep as much of the business logic out of the view as possible, I feel that this is a good approach.  If you are just starting to learn, now is the time to make a decision for the better direction to take, and I feel that ASP.NET MVC is the future, and Web Forms is the past.

How to Use ViewModel with ASP.NET MVC
http://sampathloku.blogspot.com/2012/10/how-to-use-viewmodel-with-aspnet-mvc.html
0
joedfuseAuthor Commented:
Whats a good way to learn this without much asp.net experience? I sometimes feel that coming from php, coldfusion jumping to MVC feels much more complicated.
0
Bob LearnedCommented:
I started 2 years ago with Nerd Dinner and MVC Music Store, to see the realm of possibilities:

Nerd Dinner
http://www.nerddinner.com/

Nerd Dinner Tutorial
http://nerddinnerbook.s3.amazonaws.com/Intro.htm

MVC Music Store
https://mvcmusicstore.codeplex.com/

I learn best by doing, so if you have a web site in mind, that would give you something to play with.
0
joedfuseAuthor Commented:
This is great. I will check them out.

Yeah I have a few in mind its just the getting started that is my problem... For example I have created several full database driven web apps in coldfusion and ms sql. My normal process was to create all the code with no formatting and once i had everything working i would design a UI and wrap it with the CFML to add data. Or this process in reverse. If i had an idea for a look i would do that then build the code.

I am getting the feeling that I have to go about things differently in this environment.
0
Bob LearnedCommented:
I am at the point in my career where I am finally open to learning new things.  I recently was able to learn Java on the job, and that was challenging, so I understand about doing things a little differently.  When it comes to new development platforms, it is important to learn the "better practices" or the preferred style.  

i don't know that much about ColdFusion, but the templating and scripting language (CFML) do appear to be a different approach.  I had a class once, and the instructor said that it was important to him to learn as much as he could about a wide variety of things, like Python, .NET, Java, ...
0
joedfuseAuthor Commented:
Ok so I have spent since my last comment going through the MVC music store tutorial and I am starting to understand the differences. One thing though that the tutorial doesnt really cover is the data manipulation. It loads a sql ce db with a bunch of demo data and then calls the data as is.

How would i connect to live database and run sql queries? Would I even do this or is it done differently in MVC?

I guess im a bit confused by the entity framework opposed to creating connection strings and sql queries.
0
Bob LearnedCommented:
There is something really cool with .NET, and that is LINQ.  With the Entity Framework, you don't need to write database queries, you could write integrated queries in code.  You could also connect to stored procedures, if you prefer to have the database logic in the database.  The data layer can really be anything that you want.  It just needs to connect to the database, and get back the data in the format that you desire.  The business layer should then be responsible for adding the business logic.

Entity Framework and LINQ is a whole 'nuther subject that you would need to get your head around.

LINQ (Language-Integrated Query)
https://msdn.microsoft.com/en-us/library/bb397926.aspx

Here is a walk-through that may shine a little more light on the subject:

Getting Started with Entity Framework 6 Code First using MVC 5
https://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-an-entity-framework-data-model-for-an-asp-net-mvc-application

You need to keep this in the back of your mind--you didn't learn ColdFusion in a day, so this is going to take a while.  Your journey, though, always begins with the first step.  The direction that you choose now can dictate later events, and prevent running into dead-end streets.
0
joedfuseAuthor Commented:
Your right about not learning CF in a day, I have been doing it for many many years.

And believe me I am not expecting to be a master anytime soon. My real concern is making sure I choose the tech that I will be able to do what i need to do. Im sure most all can but it has to be paired with my ability to learn it....

Do you know of any complete app tutorials like the music store that covers more data manip and Linq and MVC?
0
Bob LearnedCommented:
I posted a link to a walk-through that I have used, that is a complete tutorial from Micro$oft.

Getting Started with ASP.NET MVC 5
http://www.asp.net/mvc/overview/getting-started/introduction/getting-started

Down the left side are the topics for learning.
0
joedfuseAuthor Commented:
ok so one more question and i will be closing this. I created a demo app during a training i was doing and am starting to see the similarities in using Razor inline code and what I was doing in CF. In the code below I am passing in model data from 3 restaurant reviews and looping a table row on them. What if i wanted to run another query on each of the results based on the Id?  An example would be if i had a relational table of addresses for each review related by ID. Call it RestaurantAddress tbl. The fields would be street, postalCode, state. I assume you could add an @{} block under the foreach loop and assign another linq query to a variable then output that in a few more columns added to the table.  

@model IEnumerable<OdeToFood.Models.RestaurantReview>

@{
    ViewBag.Title = "Index";
    var firstReview = Model.First();

}

<h2>The Latest Reviews</h2>

We are showing the latest @Model.Count() reviews


<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table>
    <tr>
        <th>

        </th>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.City)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Country)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Rating)
        </th>
        <th></th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @{
        string iName = Convert.ToString(item.Name);
        string fReview = Convert.ToString(firstReview.Name);

        if (iName == fReview)
        {
            <strong>TOP</strong>
        }

                }
            </td>
            <td>

                @Html.DisplayFor(modelItem => item.Name)


            </td>
            <td>
                @*@Html.DisplayFor(modelItem => item.City)*@
                @Html.Raw(item.City)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Country)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Rating)

            </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>
    }

</table>

<div class="float-right">
    <p>
        Virtual Path Review: <strong>@VirtualPath</strong>
    </p>
</div>

Open in new window

0
Bob LearnedCommented:
If you have composite data requirements for a single view, then you would have a single view model with multiple properties, that would pull together all the data from the different sources.

Here is an example:

public class RestaurantViewModel
{
    public RestaurantModel CurrentRestaurant { get; set; }
    public List<PublicCommentModel> PublicComments { get; set; }
    public List<ReviewModel> ProfessionalReviews { get; set; }
}

...

@model RestaurantViewModel
0
joedfuseAuthor Commented:
I have spent some time with this and I can see this being a good way to go.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.