none
Run SQL Query on Datatable RRS feed

  • Question

  • Below Query Works fine in SQL Server.

    select  col1, count(*) over ( 
                                PARTITION BY col1 ROWS UNBOUNDED PRECEDING ) as col2 from company

    I have a Datatable in C# (Existing) ....I need to Execute the Same Command  in C#.



    • Edited by ID GO Saturday, July 23, 2016 9:11 AM
    • Moved by DotNet Wang Monday, July 25, 2016 6:22 AM
    Saturday, July 23, 2016 8:09 AM

Answers

  • Try this simple approach:

    var dt = new DataTable();
    
    dt.Columns.Add( "col1", typeof( String ) );
    
    dt.Rows.Add( "a" );
    dt.Rows.Add( "b" );
    dt.Rows.Add( "a" );
    dt.Rows.Add( "b" );
    dt.Rows.Add( "a" );
    dt.Rows.Add( "c" );
    
    var query1 = dt.Rows
           .Cast<DataRow>()
           .Select( ( r, n ) => new { col1 = r["col1"].ToString(), Num = n } );
    
    var query2 = query1
           .Select( r => new { col1 = r.col1, col2 = query1.Count( r2 => r2.col1 == r.col1 && r2.Num <= r.Num ) } )
           .OrderBy( r => r.col1 );
    
    foreach( var r in query2 )
    {
           Console.WriteLine( "{0} {1}", r.col1, r.col2 );
    }
    

    If does not work, then give an example and details about the expected results.

    If the data table contains a column for primary key, then there are other solutions.


    Saturday, July 23, 2016 9:48 AM

All replies

  • Try this simple approach:

    var dt = new DataTable();
    
    dt.Columns.Add( "col1", typeof( String ) );
    
    dt.Rows.Add( "a" );
    dt.Rows.Add( "b" );
    dt.Rows.Add( "a" );
    dt.Rows.Add( "b" );
    dt.Rows.Add( "a" );
    dt.Rows.Add( "c" );
    
    var query1 = dt.Rows
           .Cast<DataRow>()
           .Select( ( r, n ) => new { col1 = r["col1"].ToString(), Num = n } );
    
    var query2 = query1
           .Select( r => new { col1 = r.col1, col2 = query1.Count( r2 => r2.col1 == r.col1 && r2.Num <= r.Num ) } )
           .OrderBy( r => r.col1 );
    
    foreach( var r in query2 )
    {
           Console.WriteLine( "{0} {1}", r.col1, r.col2 );
    }
    

    If does not work, then give an example and details about the expected results.

    If the data table contains a column for primary key, then there are other solutions.


    Saturday, July 23, 2016 9:48 AM
  • Rather than run this on a DataTable, create a stored procedure and run the query from the server and return your results. Example

    DataTable dt = new DataTable();
    using (SqlConnection cn = new SqlConnection("Connection string"))
    {
        using (SqlCommand cmd = new SqlCommand("Stored Procedure Name", cn) { CommandType = CommandType.StoredProcedure })
        {
            cn.Open();
            dt.Load(cmd.ExecuteReader());
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, July 23, 2016 9:52 AM
  • Hi ID GO,

    Thank you for posting here.

    The Visual C# forum discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools.

    Since your issue is related to ado.net dataset forum. I will move the thread to that forum for better support.

    If you have something else , please feel free to contact us.

    Best Regards,

    Hart


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. Click HERE to participate the survey.

    Monday, July 25, 2016 2:12 AM