views:

180

answers:

3

Hey Everyone,

I'm trying to write some code using pure SQL using ASP.NET MVC.

I assume I should be building a model, and sticking to the MVC pattern.

Any suggestions for good practice would be highly appreciated, and examples very useful too. For example I'm not sure if I should be splitting this code off from my main repository's, and if I should, where should I put it?

Also I will be attempting to return data from 2 tables in this query.

The kind of query I would like to use is like this.

See top answer from this page

http://stackoverflow.com/questions/852994/how-to-implement-high-performance-tree-view-in-mssql-2005

Also

string sqlGetQuestionAnswers = "SELECT TOP (10) * FROM tblquestion ORDER BY NEWID()";//

using (SqlDataAdapter dapQuestions = new SqlDataAdapter(sqlGetQuestionAnswers, ConfigurationManager.ConnectionStrings["SiteConnectionString"].ToString()))
        {
            DataSet dsQuestions = new DataSet();
            dapQuestions.Fill(dsQuestions);

            if (dsQuestions.Tables[0].Rows.Count > 0)
            {
               work with data;
        }

            else
            {
                Error;
            }
        }
A: 

This seems like the "bloody knuckle" approach - you're really not using any of the 3.5 features that solve problems like this on your behalf.

That said, I would suggest that you build business objects in your model folder, and let your business objects handle their persistence using your SQL. Don't put the SQL in your controller, and definitely not in your view. Maintain a clear separation between these layers, and your life will be much easier.

GalacticCowboy
Thanks, I would agree that I don't want to throw away any of the MVC stuff if I don't have too, it's just there is some SQL that I'm not sure how to build in Linq, and I'd like to know how to get this done for the future anyway. What I'm a little confused about is, should I just be putting the data into a dataSet, or do I have to build a custom object. Sorry if I'm going wrong, I'm a beginner at this.
optician
+1  A: 

Leave it in your repository. The purpose of a repository is to abstract away your domain operations - if every function uses a different datasource and different methods of accessing the data (sql, file IO, http), so be it - the repository's clients won't know the difference.

Obviously the more cohesive you make the repository though, the easier it will be to maintain. However, this code definitely belongs there.

womp
Cool, good to know the model stuff is starting to make sense to me.Do you think you should split repository's up into groups of items that work with different db objects, or one big file?
optician
Hey, I think you answered kind of my question on another page, http://stackoverflow.com/questions/1236585/asp-net-mvc-c-bringing-in-data-from-multiple-tables-queries-into-a-view/1236676#1236676 I actually know all the stuff you mention there, it's just the first paragraph I'm not quite sure of the syntax to achieve the model there, or what to search for in SO/Google for tutorials, as everyone is focusing on linq it seems.
optician
The code you posted in your question seems pretty good - are you having troubles doing it that way? That's pretty much the "raw sql" equivalent of what I was talking about. So when you create your ViewModel class, you would fetch the data you need, probably in the constructor, and assign your model's properties from the data that you get back. This would basically replace your "work with data" line ;)
womp
ok good stuff, the bit I'm a bit stuck on is how to create the ViewModel class, and assign the properties. Am I write in seeing that this seems like more work than filling a dataSet? Totally fine if it is, as I want to do this the best way. Therefore does the example above (with title) show the correct way to do with, and I just have to bring everything back correctly set as properties?On the off chance am I also correct that doing it the way you describe would end up with a "strongly typed dataset" ? where as a dataSet can be filled with any data?
optician
+1  A: 

Given you want a SQL to MODEL approach this might work for you.

I'm using a LinqToSQL data context here;

I have a table of Articles that contains let's say 10 fields but all I want is the title so I create a class;

public class Art
{
  string title { get; set; }
}

Then I have my data context object

static ArticlesDataContext dc = new 
ArticlesDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

Then I can fill my, albeit simple, model;

var arts = dc.ExecuteQuery<Art>(@"Select * from articles");

Does this help or am I off base?

griegs
Hmmm, I'm not sure, I'm looking to use the query from this question here, so it seems a bit more complicated.. I'll add this to the main part of this question as well.http://stackoverflow.com/questions/852994/how-to-implement-high-performance-tree-view-in-mssql-2005
optician
The only reason I'm not sure about this, is it feels like it will be a lot of work vs using a dataSet as I have before.. However this may be becuase it is safer code.It could also be becuase I have no idea what I am talking about!
optician
Not sure whether you can use a dataset here as i've never tried it. i imagine you can but have no code to back that up. given the example you posted in the question this seemed a reasonable way to achieve what you wanted. but if you get more and more complex this solution may fall apart yeah. selecting into a model hierarchy will be more difficult and you may need to parse the result set and create your object structure. slow and boring i know. i might be wrong though.
griegs