CODEWITHSUNDEEP
asp.net-mvc
VictorC
VictorC

Reputation: 326

asp mvc plain sql possible?

Is there a way to use simple sql queries on ASP MVC without using LINQ thing?

any link is welcome :)

Upvotes: 1

Views: 446

Answers (6)

khaled saleh
khaled saleh

Reputation: 518

try this solution:

var results = DynamicCall.DynamicListFromSql(_entities, "select * from users", null).ToList();


    public static IEnumerable<dynamic> DynamicListFromSql(this DbContext db, string Sql, Dictionary<string, object> Params)
    {
        using (var cmd = db.Database.Connection.CreateCommand())
        {
            cmd.CommandText = Sql;
            if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }

            using (var dataReader = cmd.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    var row = new ExpandoObject() as IDictionary<string, object>;
                    for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
                    {
                        row.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
                    }
                    yield return row;
                }
            }
        }
    }

Upvotes: 0

Amirhosein Ghasemi
Amirhosein Ghasemi

Reputation: 26

If you are using Entity Framework you can use SqlQuery like this:

using(var db = new DbContext())
{
var result = db.Database.SqlQuery<your object>("Your Query",params));
}

Your object : your expected result Type (object or class Type) and

Your query and params : sql command with params you should pass to query or not

Upvotes: 0

geekyhybrid
geekyhybrid

Reputation: 25

Building off of @user102220's answer:

Set up a data access layer (just a separate class or series of classes), then call these from your controller. Apply ADO.NET as necessary.

Upvotes: 0

blparker
blparker

Reputation: 343

Sure, you can embed plain ADO.NET objects within your controller's action methods or in a custom business logic library. A bit of an example. WARNING: DEMONSTRATION CODE ONLY. DO NOT ATTEMPT TO USE IN PRODUCTION SCENARIO.

public ActionResult Index()
{
    using(SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CNTax"].ConnectionString))
    {
        using(SqlCommand command = conn.CreateCommand())
        {
            command.CommandText = "select * from Names";
            command.CommandType = CommandType.Text;

            conn.Open();

            var table = new DataTable();
            table.load(command.ExecuteReader());

            return View("Index", table);
        }
    }
}

A simple code snippet to select all names from the database and return the index view with the model set as a DataTable.

Upvotes: 3

Joel Martinez
Joel Martinez

Reputation: 47759

Of course, you can always drop down to use regular ol' ADO.NET :-)

The Data Access Application Block is also commonly used to simplify the execution of raw sql and stored procedures.

Upvotes: 8

7wp
7wp

Reputation: 12674

You sure can. And it is done the same way as you normally would in an ASP.NET application just like the other answers here have indicated.

....HOWEVER, I prefer to use a tool to generate my data access layer. Some of the top choices right now are nHibernate, and LLBLGen Pro, or even Microsoft's Entity Framework

I personally would go with nHibernate or LLBLGen Pro, depending on if you want to have your data access layer driven by "domain driven design" (nHibernate) or "data driven design" (LLBLGen Pro)

Upvotes: 1

Related Questions