How to write pure SQL in asp.net mvc?




I've created a controller class as below:



```
public class CelebrityController : Controller
{
    private MyDatabase db = new MyDatabase();

    public ActionResult Index()
    {
        var query = db.tableCelebrity.Include(t => t.tableMovie);
        return View(query.ToList());
    }
}

```

Now I want to do the same thing, but using a pure SQL query in the `Index` method.
I am trying to replace code as below:



```
public class CelebrityController : Controller
    {
        private MyDatabase db = new MyDatabase();

        public ActionResult Index()
        {
            IEnumerable<tableCelebrity> results = db.ExecuteQuery<tableCelebrity>
            (@"SELECT c1.celebid as CelebrityID, t1.movieName as MovieName
                FROM tableCelebrity as c1, tableMovie as t1
                WHERE c1.celebid = t1.celebid");
        }
    }

```

But this gives me an error at the line `db.ExecuteQuery<tableCelebrity>`:



> 
> Error 'MyProject.Models.MyDatabase' does not contain a definition for
>  'ExecuteQuery' and no extension method 'ExecuteQuery' accepting a
>  first argument of type 'MyProject.Models.MyDatabase' could be found
> 
> 
> 


Is it possible with ASP.NET MVC?? If yes, then what changes I have to perform in the method?


Please help me to do that.



```
I am created Details method as below

public ActionResult ShortDetails(int? id, string tagname)
    {
        tblCelebrity tblcelebrity = db.tblCelebrities.SqlQuery<tblCelebrity>("SELECT * FROM dbo.tblCelebrity WHERE Celebrity_ID =" + id);

        if (tblcelebrity == null)
        {
            return HttpNotFound();
        }
        return View(tblcelebrity);
    }

```

And Details View accepting type as



```
@model MyProject.Models.tblCelebrity

```

I also created tblCelebrity class in my project


But `tblCelebrity tblcelebrity = db.tblCelebrities.SqlQuery("SELECT * FROM dbo.tblCelebrity WHERE Celebrity\_ID =" + id);
this line gives error as
The non-generic method 'System.Data.Entity.DbSet.SqlQuery(string, params object[])' cannot be used with type arguments
You want something similar to this :



```
using (var context = new BloggingContext())
{
    var blogs = context.Blogs.SqlQuery("SELECT * FROM dbo.Blogs").ToList();
}

```

You can find tutorial for that here :
<http://msdn.microsoft.com/en-us/data/jj592907.aspx>
Using [reflection](/questions/tagged/reflection "show questions tagged 'reflection'") and [expression-trees](/questions/tagged/expression-trees "show questions tagged 'expression-trees'") you can provide the parameters and then call `OrderBy` function, Instead of returning `Expression<Func<Task, T>>` and then calling `OrderBy`.


Note that `OrderBy` is an extension method and has implemented in both `System.Linq.Enumarable` and `System.Linq.Queryable` classes. The first one is for [linq-to-objects](/questions/tagged/linq-to-objects "show questions tagged 'linq-to-objects'") and the latter is for [linq-to-entities](/questions/tagged/linq-to-entities "show questions tagged 'linq-to-entities'"). [entity-framework](/questions/tagged/entity-framework "show questions tagged 'entity-framework'") needs the expression tree of the query in order to translate it to SQL commands. So we use the `Queryable` implementation.


It can be done by an extension method(explanations added as comments):



```
public static IOrderedQueryable<TSource> OrderBy<TSource>(
       this IQueryable<TSource> query, string propertyName)
{
    var entityType = typeof(TSource);

    //Create x=>x.PropName
    var propertyInfo = entityType.GetProperty(propertyName);
    ParameterExpression arg = Expression.Parameter(entityType, "x");
    MemberExpression property = Expression.Property(arg, propertyName);
    var selector = Expression.Lambda(property, new ParameterExpression[] { arg });

    //Get System.Linq.Queryable.OrderBy() method.
    var enumarableType = typeof(System.Linq.Queryable);
    var method = enumarableType.GetMethods()
         .Where(m => m.Name == "OrderBy" && m.IsGenericMethodDefinition)
         .Where(m =>
         {
            var parameters = m.GetParameters().ToList();
            //Put more restriction here to ensure selecting the right overload                
            return parameters.Count == 2;//overload that has 2 parameters
         }).Single();
    //The linq's OrderBy<TSource, TKey> has two generic types, which provided here
    MethodInfo genericMethod = method
         .MakeGenericMethod(entityType, propertyInfo.PropertyType);

    /*Call query.OrderBy(selector), with query and selector: x=> x.PropName
      Note that we pass the selector as Expression to the method and we don't compile it.
      By doing so EF can extract "order by" columns and generate SQL for it.*/
    var newQuery = (IOrderedQueryable<TSource>)genericMethod
         .Invoke(genericMethod, new object[] { query, selector });
    return newQuery;
}

```

Now you can call this overload of `OrderBy` like any other overload of it.  

For example:



```
var cheapestItems = _context.Items.OrderBy("Money").Take(10).ToList();

```

Which translates to:



```
SELECT TOP (10)  {coulmn names} FROM  [dbo].[Items] AS [Extent1] 
       ORDER BY [Extent1].[Money] ASC

```

This approach can be used to define all overloads of `OrderBy` and `OrderByDescending` methods to have `string` property selector. Change this line of code (sql query) into:



```
OleDbCommand cmd = new OleDbCommand(Stirng.Format("INSERT INTO CHILDREN2 (Emp_ID , Child_Name, Age) VALUES ({0}, {1}, {2})", Convert.ToInt32(CaptionText.Text),txtName.Text, txtAge), clsData.con);
```

Do you have a WHERE clause too? I removed it from your query, becasue it didnt belonge on that place where you put it.  

If you will use a Where clause then create an sql query this was:



```
"INSERT INTO TableName (Field1, Field2) VALUES (@paramField1, @paramField2) WHERE Field3 = @paramField3"
```

where params are parameters passed to the fields, and WHERE is a conditional clause, and has to be stated on the end (after specifying parameters). Dapper just wraps raw ADO.NET; raw ADO.NET doesn't offer a facility for this, therefore dapper does not. What you want is `SqlBulkCopy`. You could *also* use a table-valued-parameter, but this really feels like a `SqlBulkCopy` job.


In a pinch, you can *use* dapper here - `Execute` will unroll an `IEnumerable<T>` into a series of commands about `T` - but it will be lots of commands; and unless you explicitly enable async-pipelining, it will suffer from latency per-command (the pipelined mode avoids this, but it will still be n commands). But `SqlBulkCopy` will be *much* more efficient.


If the input data is an `IEnumerable<T>`, you might want to use `ObjectReader` from FastMember; for example:



```
IEnumerable<SomeType> data = ...
using(var bcp = new SqlBulkCopy(connection))
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description"))
{
    bcp.DestinationTableName = "SomeTable";
    bcp.WriteToServer(reader);
}

``` First you need to clear the call in the database. `$mysqli->query`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

sent request before parameter binding, etc.


Replace as follows:



```
$stmt = $mysqli->prepare('SELECT reply\_id FROM replies WHERE reply\_topic = ? ORDER BY reply\_date ASC');
$stmt->bind_param('i',$param)
$stmt->execute();

        

        
        
        
 (adsbygoogle = window.adsbygoogle || []).push({});
 
        

    
```

 `$mysqli->prepare`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

returns a new object for which you want to bind values ​​and execute them. It avoids [SQL injection](https://translate.googleusercontent.com/translate_c?depth=1&pto=aue&rurl=translate.google.com&sl=ru&sp=nmt4&tl=en&u=) !


None of this is discounted. `mysqli_`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

is exactly what you should be using.



  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

Since all you need is an array of results, you can use the following code to do this - no custom functions are required.



```
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
        $set[] = $row['reply\_id'];
}

        

        
        
        
 (adsbygoogle = window.adsbygoogle || []).push({});
 
        

    
```

For each row, `while`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

each field is stored in an array with the column name as the key. Thus, `$row['reply_id']`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

contains the value of a column `reply_id`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

in the database. Above, I have stored this value in a named array `$set`
  




 (adsbygoogle = window.adsbygoogle || []).push({});
 
  

and when it iterates over the next row of the result, it also stores the next value for that array.


You can manipulate the result before or after saving it to a new array as you see fit.


Hope it helps. Turns out If I tweked each method 



```
foreach (var dataColumn in readFields.Select(column => new DataColumn(columntypeof(string)) { AllowDBNull = true, DefaultValue = string.Empty }))
{
    csvData.Columns.Add(dataColumn);
}

```

and in the second Method I changed it to use an index rather than column name



```
for (var count = 0; count < table.Columns.Count; count++)
{
    sqlBulkCopy.ColumnMappings.Add(count, count);
}

``` Did you bind the listBox1, using DataSource property from DataTable?  

If so, you have to 1st get the item and value from listBox item and add it some other place (in your case to listBox2):



```
for(int i = 0; i < listBox1.SelectedItems.Count; i++)
{
     DataRowView view = listBox1.SelectedItem as DataRowView;
     string item = view["ColumnNameOfItem"].ToString(); //can be some name
     int value = Convert.ToInt32(view["ColumnNameOfValue"]);  //can be some id (number)
     listBox2.Items.Add(item); // it you want to add item (you can add value as well);
}
```

ColumnNameOfItem (or OfValue) are the names of fields in your dataTable (and same are in dataBase as well). Insteads of names you can use indexes (0 and 1, but without quotation marks).



Hope it helps.  

bye You can provide an optional parameter to the scaffolding command to update only the table you target.



```
Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DirectoryNameOfYourModels -Tables employee -f

```

If you are using .net core cli then use.



```
dotnet ef dbcontext scaffold "Server=(localdb)\mssqllocaldb;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o DirectoryNameOfYourModels -t employee -f

``` You could try using the in line view like this:


Select table1.col1, table1.col2, table1…., table2.col1, table2.col2, table2….  

 from  

(SELECT *  

FROM dbo.Shaft\_Scales  

WHERE dbo.Shaft\_Scales.ID = (SELECT max(ID) from dbo.Shaft\_Scales ID) as table1,  

(SELECT *  

FROM OtherTable  

WHERE dbo.OtherTable.ID = (SELECT max(ID) from dbo.OtherTable ID) as table2


—————- another approach from meandyou 


This works on [at least some] SQL processors


SELECT T1.* , T2.*  

FROM table1 T1, table2 T2  

WHERE …


Do we have to discuss why not to use “SELECT * ” in your SQL?<pre>


Try this


SELECT * FROM T1 as a, T2 as b WHERE a.ID = (SELECT max(ID) from T1)  

and b.ID = (SELECT max(ID) from T2);</pre> For a long time HtmlAgilityPack [didn't had the ability to directly query an attribute value](http://htmlagilitypack.codeplex.com/Thread/View.aspx?ThreadId=1720). You had to loop over the list of meta nodes. Here's one way - 



```
var doc = new HtmlDocument();
doc.LoadHtml(htmlString);

var list = doc.DocumentNode.SelectNodes("//meta"); 
foreach (var node in list)
{
    string content = node.GetAttributeValue("content", "");
}

```

But looks like in there is an [experimental xpath release](http://htmlagilitypack.codeplex.com/releases/view/46681) that will let you do that. 



```
doc.Document.SelectNodes("//meta/@content") 

```

will return a list of HtmlAttribute objects. `out` parameters in SQL Server are fun. You have to repeat yourself *everywhere*.


Specifically, here, you've forgotten to mention it in the parameter list when calling the stored proc. `dbo.InsertPhones @model, @out` should be `dbo.InsertPhones @model, @out out`. There may also be a way to omit the parameter list from this string and instruct SQL Server that you're giving it a stored procedure name, not arbitrary query text, but I'm not sure how you would do that specifically here (with an `SqlCommand` object, it would be via the `CommandType` property).


If you're using an `out` parameter, the `select` at the end of the stored procedure is unnecessary, but I assume that was an attempt at a fix.