the data as a string array at once

[patrick]

I want to receive the data as a string array at once in Oledbcommand.

I will not use this Datatable, DataSet method.
I will not use this OleDbDataReader method.

        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\data\myDB.mdb";
        string strSQL = "SELECT LastName From User";
        OleDbConnection conn = new OleDbConnection(strConn);
        OleDbCommand comm = new OleDbComand(strSQL, conn);
        try
        {
              conn.Open(); 

              OleDbDataReader reader = comm.ExcuteReader();

              while(reader.Read() == true)
              {
                   Console.WriteLine(reader["LastName"] + " " + reader["Age"]);
              }

              reader.Close();

              conn.Close();
        }
        catch (Exception e)
        {
            Console.WriteLine(" DB Open Fail ")l
        }

 

[Skydiver]

You cannot. You need to build the string yourself from the rows returned by ADO.NET.

 

[cjard]

You could maybe do it with dapper, and either select a single column as strings or interleave (unpivot) multiple string columns in a predictable, unpackable fashion..

But the question I'd have is "why" - why make life such hard work when you could just make a Person class, a ToString on it that makes a string representation, and then you could Dapper something like conn.Query<Person>("SELECT * FROM User").Select(p => p.ToString()).ToArray()

 

[jmcilhinney]

You could maybe do it with dapper, and either select a single column as strings or interleave (unpivot) multiple string columns in a predictable, unpackable fashion..

But the question I'd have is "why" - why make life such hard work when you could just make a Person class, a ToString on it that makes a string representation, and then you could Dapper something like conn.Query<Person>("SELECT * FROM User").Select(p => p.ToString()).ToArray()

It's worth noting that tools like Dapper are still just going to use ADO.NET under the hood anyway. If it's a simpler interface you want then such tools will help, but they do add another layer on top of the same data reader that you can use yourself.

 

[Skydiver]

I suspect our OP maybe used to dealing with "stringly-typed" data and has not yet been shown the power of strongly typed data.

To me it seems pointless to collect all the last names from the user table and put them into a single string. How to you determine where one surname ends and the next one starts? Our OP will likely say "the surnames should be comma delimited within the string I just forgot to state that in my post". So if it's comma delimited that means you'll like want to get the individual surnames in some kind of collection. That means allocating the big string with all N surnames, and then N smaller strings with each of the surnames. So that about 2*N the amount of memory used. So you might as well have just started off with just a list of the N surnames in the first place.

If the OP response as to why he needs a single string is "I need to check to see if a surname is (or is not) in the Users table and the easiest way to do this is to use Contains() in the string", then this is a complete failure to take advantage of a database. A database can quickly perform the search (specially if that column is indexed), and just give you the answer as a single boolean or integer. Compare that to the extra resources used with the database sending back all the surnames across the network to your app, and then you allocating memory for all the surnames, and then you burning CPU searching through that string to find a match.

 

[jmcilhinney]

I suspect our OP maybe used to dealing with "stringly-typed" data and has not yet been shown the power of strongly typed data.

To me it seems pointless to collect all the last names from the user table and put them into a single string. How to you determine where one surname ends and the next one starts? Our OP will likely say "the surnames should be comma delimited within the string I just forgot to state that in my post". So if it's comma delimited that means you'll like want to get the individual surnames in some kind of collection. That means allocating the big string with all N surnames, and then N smaller strings with each of the surnames. So that about 2*N the amount of memory used. So you might as well have just started off with just a list of the N surnames in the first place.

If the OP response as to why he needs a single string is "I need to check to see if a surname is (or is not) in the Users table and the easiest way to do this is to use Contains() in the string", then this is a complete failure to take advantage of a database. A database can quickly perform the search (specially if that column is indexed), and just give you the answer as a single boolean or integer. Compare that to the extra resources used with the database sending back all the surnames across the network to your app, and then you allocating memory for all the surnames, and then you burning CPU searching through that string to find a match.

Note that the OP did say that they wanted a string array, rather than just a string. The issue seems to be that they think that you should be able to go directly from a database to a string array. There are tools that will hide the ADO.NET code form you but it's still there, so what's the big deal about writing less than a dozen lines of code to do that part yourself. If you want to use an ORM for the functionality an ORM provides then I'm all for that, but it just seems weird to ask this specific question and demand that there be no ADO.NET involved when using ADO.NET to generate a string array is pretty simple. Maybe the OP thinks that the process could be more efficient without using ADO.NET but, if so, they are misguided.

 

[Skydiver]

I see now. @patrick and @patrickna used to be yunhyejeong. Given his past propensity for having unusual requirements, this is just par for the course.

 

[Skydiver]

The following still uses a DbDataReader, but builds a string array of last names queried from the database, so it doesn't fit our OP's requirements. On the flip side: No Dapper. No Entity Framework. Just plain old ADO.NET and LINQ-to-objects.

using Microsoft.Data.Sqlite;
using System.Data;

using var conn = new SqliteConnection("Data Source=northwind.db");
using var command = new SqliteCommand("SELECT LastName FROM Employees", conn);
await conn.OpenAsync();
using var reader = await command.ExecuteReaderAsync();
var result = reader.Cast<IDataRecord>()
                   .Select(r => r.GetString(0))
                   .ToArray();

foreach(var row in result)
    Console.WriteLine(row);

 

[jmcilhinney]

The following still uses a DbDataReader, but builds a string array of last names queried from the database, so it doesn't fit our OP's requirements. On the flip side: No Dapper. No Entity Framework. Just plain old ADO.NET and LINQ-to-objects.

using Microsoft.Data.Sqlite;
using System.Data;

using var conn = new SqliteConnection("Data Source=northwind.db");
using var command = new SqliteCommand("SELECT LastName FROM Employees", conn);
await conn.OpenAsync();
using var reader = await command.ExecuteReaderAsync();
var result = reader.Cast<IDataRecord>()
                   .Select(r => r.GetString(0))
                   .ToArray();

foreach(var row in result)
    Console.WriteLine(row);

I was never aware that you could enumerate a data reader that way. I guess I should have realised, given that I've bound one to a BindingSource before. Live and learn!

 

[cjard]

It's worth noting that tools like Dapper are still just going to use ADO.NET under the hood anyway. If it's a simpler interface you want then such tools will help, but they do add another layer on top of the same data reader that you can use yourself.

Yeah, but in everything we do there are typically good reasons why we use higher level abstractions. How much of their life does a dev spend writing assembly? How many times does one write an entire project in Notepad with Console.WriteLine debugging?

Dapper, AutoMapper, RestSharp, Blazor, EF Core, Newtonsoft et al - all tech that makes sense for most applications over finding a much lower level route.

Makes me sad that newbies spend all day getting some stringly typed, sql injection prone junk code working, writing hundreds of unnecessary lines and then copy pasting it through the entire project because it just about works. Use the higher level abstraction for clarity and speed of coding and if you then really truly need to wring every last cent of performance out of the system for a handful of well trodden code paths, hand optimize on a lower level

 

[jmcilhinney]

Yeah, but in everything you do there are typically good reasons why we use higher level abstractions.

I wasn't criticising or suggesting that they shouldn't be used. I just wanted the OP to be aware that using such tools is not an alternative to ADO.NET but rather a layer on top.