ASP.NET MVC without entity framework and simple sql queries

Hello,

I am familiar with ASP.NET Web pages as I used it for my last project and wrote classes to separate logic from html. I am now building an Intranet system in ASP.NET MVC  as a step up from web pages. I have spent some time learning MVC and Entity Framework and I understand how it all works together and have put into practice with fairly straight forward data from the database.

I am dealing with 5 databases (Commercial CMS, Acccounting, Ecommerce website etc..) and I am pretty much stuck on how to span joins over multiple databases. To add to the problem, the tables do not have foreign keys for Entity Framework.

e.g.
Database A > Item
Database B > Basket
Database C > Transaction, Customer

I have read some example on hacking around to do this:
https://rachel53461.wordpress.com/2011/05/22/tricking-ef-to-span-multiple-databases/

I do not feel comfortable doing this and think it will cause a lot of hassle as I will be dealing with many other legacy stored proecedures / functions etc...

So I believe Entity Framework is not going to work for my project and I need to know how I can run sql queries and how to structure it within MVC.

I have a page where I need to display list of products and customer basket based on user input and this is what I have put together so far:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Intranet.Models
{
    public class TestModel
    {
	public string connectionString = ".\\SQLEXPRESS; Initial-Catalog=YourDBName; Integrated-Security=true";
        public ViewModel GetBasket(int id)
        {
            var model = new ViewModel();
            using (SqlConnection connection = new SqlConnection(connectionString))
            using (SqlCommand command = new SqlCommand("", connection))
            {
                command.CommandText = "select * from Basket where Customer=@Id";
                command.Parameters.AddWithValue("@Id",id);
                SqlDataReader reader = command.ExecuteReader();
                model.Id = id;
                model.Name = reader["Name"].ToString();
            }
            return model;
        }        
    }
}

//My ViewModel
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Intranet.Models
{
    public class ViewModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

Open in new window


This will only work for 1 basket item so how to pass IEnumerable<dynamic> or List?
CEL_ITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Lokesh B RDeveloperCommented:
Hi,

Use ActionResult as return type and List<ViewModel>

public ViewModel GetBasket(int id)
        {
             List<ViewModel> list  = new List<ViewModel>();
            using (SqlConnection connection = new SqlConnection(connectionString))
            using (SqlCommand command = new SqlCommand("", connection))
            {
                command.CommandText = "select * from Basket where Customer=@Id";
                command.Parameters.AddWithValue("@Id",id);
                SqlDataReader reader = command.ExecuteReader();

               if(reader.HasRows)
              {
                   While(reader.Read())
                    {

                              var model = new ViewModel();
                                      model.Id = id;
                                     model.Name = reader["Name"].ToString();

                             list.Add(model);

                      }
                }
            }
            return View(list);
        }  

Open in new window

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
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.