在C＃数据库连接中选择一些行

I used database connection for connect to database and I selected some of rows in database table, like this code:

我使用数据库连接连接到数据库,我选择了数据库表中的一些行,如下代码:

OleDbConnection objConnection = new OleDbConnection("server=localhost;database=sample;Data Source=|DataDirectory|\\sample.accdb");

objDataAdapter = new OleDbDataAdapter("SELECT description, category, account, price FROM SampleTable WHERE Select_ID = 12", objConnection);

now this code select 16 rows from SampleTable where Select_ID is 12 after that i need rows 3,5,7 how can i select this rows?

现在这段代码从SampleTable中选择16行,其中Select_ID为12,之后我需要行3,5,7如何选择这行?

4 个解决方案

#1

DataTable dt = new DataTable(); // get your data into this datatable

        DataRow[] dr;
        dr = dt.Select("WHERE Select_ID = 12");
        if (dr.Length > 0)
        {
            //do something
        }

#2

You can use the ROW_NUMBER function. If you use it in the query you have, you will select by ROW_NUMBER of ALL rows. If I read correctly, you want rows 3,5,7 of the result set, so you would have to use a subquery:

您可以使用ROW_NUMBER函数。如果您在查询中使用它,您将按ROW_NUMBER选择所有行。如果我读得正确,你需要结果集的3,5,7行,所以你必须使用子查询:

SELECT * FROM (
    SELECT description, category, account, price FROM SampleTable WHERE Select_ID = 12
) WHERE ROW_NUMBER() IN (3,5,7)

In the inner query, you are getting the 16-row result. The outer query now uses that result as a table/data source and selects the rows you need.

在内部查询中,您将获得16行结果。外部查询现在将该结果用作表/数据源并选择所需的行。

#3

if order by column "id", then

如果按列“id”排序,那么

SELECT t.description, t.category, t.account, t.price FROM (
 SELECT ROW_NUMBER() OVER (ORDER BY id) as RowN, description, category, account, price FROM SampleTable WHERE Select_ID = 12
) as t WHERE RowN in (3,5,7)

else (ORDER BY id) change to your column

else(ORDER BY id)更改为您的列

#4

If your DataTable already contains those 16 rows but you want only row 3,5,7:

如果您的DataTable已包含这16行但您只想要行3,5,7:

int[] indexes = { 2, 4, 6 };
dataTable1 = dataTable1.AsEnumerable()
    .Where((row, index) => indexes.Contains(index))
    .CopyToDataTable();

If you use a database that supports ranking functions you could use ROW_NUMBER. If you use SQL-Server >=2005 this works:

如果您使用支持排名功能的数据库,则可以使用ROW_NUMBER。如果使用SQL-Server> = 2005,则可以:

string sql = @"
WITH CTE AS
(
    SELECT description, category, account, price,
           rn = ROW_NUMBER() OVER (ORDER BY Select_ID, description, category, account, price)
    FROM SampleTable 
    WHERE Select_ID = 12
)
SELECT description, category, account, price
FROM CTE
WHERE rn IN ( 3, 5, 7 );"

#1

DataTable dt = new DataTable(); // get your data into this datatable

        DataRow[] dr;
        dr = dt.Select("WHERE Select_ID = 12");
        if (dr.Length > 0)
        {
            //do something
        }

#2