execute sql statement in asp.net mvc3 (C#)

Question

How to execute sql statement in asp.net mvc3 (C#)? I am using Entity Data Model for my asp.net mvc application

I need to execute a sql query ("select * from users where EmailAddress like '%@gmail.com'").

Use an SqlConnection to execute the SqlCommand. Ideally this should be done in the Model, which in turn is called by an action in the Controller. — Alex R.
– Alex R., Commented Jun 9, 2011 at 10:49
Yes I have that entity, but i need this to achieve a particular scenario where the admin can filter the users based on custom query(like advanced search). When the admin uses advanced search, i can construct a query and trying to execute and get the results — Prasad
– Prasad, Commented Jun 9, 2011 at 11:21

Ladislav Mrnka · Accepted Answer · 2011-06-09 11:24:08Z

5

Is your User entity mapped? In such case yo can use

var users = from u in context.Users
            where u.EmailAddress.EndsWith("@gmail.com")
            select u;

If you don't have User table mapped but you have User class with parameterless constructor and public settable properties with same names as columns in result set you can use:

var users = context.ExecuteStoreQuery<User>("select * from users where EmailAddress like '%@gmail.com'");

in case of ObjectContext API or:

var users = context.Database.SqlQuery<User>("select * from users where EmailAddress like '%@gmail.com'");

If you don't have entity you can execute it as any other ADO.NET command by creating SqlConnection, SqlCommand and calling ExecuteReader on the command.

answered Jun 9, 2011 at 11:24

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Prasad Over a year ago

Can we join multiple tables with ExecuteStoreQuery?

Ladislav Mrnka Over a year ago

You can excute any SQL query with that method, so you can join tables as well.

aaronmallen Over a year ago

Can you execute a query this way on multiple tables?

Collectives™ on Stack Overflow

execute sql statement in asp.net mvc3 (C#)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related