C# -Ms Access Query: Concatenating Rows through a query

Question

I am in need to Concatenating multiple rows, this need is already discussed and answered in this thread Ms Access Query: Concatenating Rows through a query

In the above thread, the specified need can be achievable only through VBA function..but I want to achieve it via C# function. Is there any possibilities to concatenate multiple rows through ms access query and that query should be executed from C#.

I want to achieve this complete functionality through MS Access query as like SQL query's For XML PATH.

Since you're using C# I was use Linq Group By e.g. stackoverflow.com/questions/614542 — Conrad Frix
– Conrad Frix, Commented Jun 14, 2016 at 14:11
Why would Access ACE/JET Db understand C# or CLR? but .net has enough tools for achieving this task. DataReader is one powerful option. — marlan
– marlan, Commented Jun 14, 2016 at 14:26
See also related question here: Create a concatenated list of child table values from an Access database without using VBA. — Gord Thompson
– Gord Thompson, Commented Jun 14, 2016 at 14:49

Quentin Roger · Accepted Answer · 2016-06-15 09:44:33Z

0

You can query your ms db with an OleDbConnection like that :

        var con = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;");

        string stbQuery = "SELECT * FROM [Table]";
        OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery, con);
        DataSet dsXLS = new DataSet();
        adp.Fill(dsXLS);

        var groups = dsXLS.Tables[0].Rows.OfType<DataRow>().GroupBy(r => r.ItemArray[0]);
        System.Data.DataTable t = new System.Data.DataTable();
        t.Columns.Add("Key");
        t.Columns.Add("Value");

        foreach (var grp in groups)
        {
            t.Rows.Add(grp.Key, grp.ToList().Select(r => r.ItemArray[1]).Aggregate((a, b) => a + "," + b));
        }

        Console.ReadLine();

Tutorial on msdn : https://msdn.microsoft.com/en-us/library/aa288452(v=vs.71).aspx

Have a great day !

edited Jun 15, 2016 at 9:44

answered Jun 14, 2016 at 15:01

Quentin Roger

6,5482 gold badges26 silver badges37 bronze badges

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

4 Comments

Kevin Morgan Over a year ago

Hi @Quentin Roger, I want to achieve this complete functionality through MS Access query as like SQL query's For XML PATH.

Quentin Roger Over a year ago

Hi @kombsh, I edited my answer to show you how you can query your xml file using OleDbConnection

Kevin Morgan Over a year ago

Hi @Quentin Roger, thanks for your answer.. but I need to query data from a table in MS Access database , not from Excel file .

Quentin Roger Over a year ago

My bad, sorry. To connect to MS Access db its the same way with an OleDbConnection.The connection string should be something like this ` var conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\emp.mdb;");`

Collectives™ on Stack Overflow

C# -Ms Access Query: Concatenating Rows through a query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related