CODEWITHSUNDEEP
c#sql
eagle eagle
eagle eagle

Reputation: 35

Convert SQL to c# SQL query

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#.

Upvotes: 0

Views: 936

Answers (2)

Debabrata
Debabrata

Reputation: 122

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

Upvotes: 1

bbsimonbb
bbsimonbb

Reputation: 28982

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

disclaimer : which I wrote :-)

Upvotes: 0

Related Questions