How to use raw normal sql in ASP.NET MVC without linq?

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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?