Convert SQL to c# SQL query

Question

Old SQL:

SELECT 
    [FileName], [FilePath] 
FROM 
    dbo.[tb_CrawlData] cr  
WHERE 
    cr.Content LIKE '%' + (SELECT content 
                           FROM [tb_CrawlData]
                           WHERE Content LIKE '%test1%') + '%' 
GROUP BY 
    cr.FileName, [FilePath] 
ORDER BY 
    cr.FileName

Old C# SQL query:

Sqlquery = "SELECT [FileName], [FilePath]"
                + " FROM [tb_CrawlData] cr "
                + " WHERE cr.Content like '%' + (" + Sqlquery.Substring(Sqlquery.IndexOf(" SELECT") + 1) + ") + '%' ";     
        Sqlquery += " GROUP BY cr.FileName,[FilePath]"
                  + " ORDER BY cr.FileName ";

New SQL:

select 
    [FileName], [FilePath]
from 
    dbo.[tb_CrawlData] cr
where exists (select 1
              from [tb_CrawlData] cd
              where cd.Content like '%data%'
                and cr.Content like '%' + cd.Content + '%')
group by 
    cr.FileName, [FilePath]
order by 
    count(*) desc, cr.FileName

New C# SQL query:

The new sql, I am not so sure how to modify for c#.

sorry, you have a working pattern for it in your post - where is the exact problem? do you know how to create strings in c#?`if yes, you can create your sql query. but read about parameterized queries, your sample is very dangerous - its vulnerable to sql injection. — nabuchodonossor
– nabuchodonossor, Commented Feb 11, 2017 at 7:12
This is complicated case meaning subquery exists inside a query involving 2 SQL Table. You'd better to write basic query first.. — Kay Lee
– Kay Lee, Commented Feb 11, 2017 at 7:36

Debabrata · Accepted Answer · 2018-09-09 14:22:38Z

1

We have to use the SqlCommand class.

string sql = "select 
    [FileName], [FilePath]
from 
    dbo.[tb_CrawlData] cr
where exists (select 1
              from [tb_CrawlData] cd
              where cd.Content like '%data%'
                and cr.Content like '%' + cd.Content + '%')
group by 
    cr.FileName, [FilePath]
order by 
    count(*) desc, cr.FileName"
string connectionString = "Server=.\PDATA_SQLEXPRESS;Database=;User Id=sa;Password=2BeChanged!;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(sql, connection);
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
}

answered Sep 9, 2018 at 14:22

Debabrata

1201 silver badge10 bronze badges

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

Comments

bbsimonbb · Accepted Answer · 2017-02-13 13:04:27Z

0

Use QueryFirst. You can run your SQL directly in your C# application.

disclaimer : which I wrote :-)

edited Feb 13, 2017 at 13:04

answered Feb 13, 2017 at 8:41

bbsimonbb

29.3k18 gold badges92 silver badges129 bronze badges

Collectives™ on Stack Overflow

Convert SQL to c# SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related