Read SQL Server table row by row already in string type

Question

I have a question: is it possible to select data from a table in string (without using ToString method) rows?

Without using SqlDataReader and with good performance.

For example read all table and put data to List of string type.1 element=1row

SqlConnection conn = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password=");
conn.Open();

SqlCommand command = new SqlCommand("Select id from [table1] where name=@zip", conn);
command.Parameters.AddWithValue("@zip","india");

// don't use reader
using (SqlDataReader reader = command.ExecuteReader())
{
    if (reader.Read())
    {
        //List<string> = String.Format(row properties...)
    }
}

Sorry for bad explanation

What's wrong with the code you provided? What exactly is your question here? Also, SQL means "structured query language" not "SQL Server". — rory.ap
– rory.ap, Commented Jun 1, 2018 at 15:09
Are you looking for an ORM like Dapper? Avoiding SqlDataReader entirely isn't possible, as it's the common ground for everything in .NET that reads data from SQL Server, but there are certainly plenty of libraries that abstract away from it. — Jeroen Mostert
– Jeroen Mostert, Commented Jun 1, 2018 at 15:14
What do you mean by "don't use reader"? The reader is how you access the different rows. You could use ExecuteScalar, but then you'd only get the value of the first column in the first row... — Corak
– Corak, Commented Jun 1, 2018 at 15:14
One guy said me that it's possible not to use a Reader(Entity and dapper also) to take data from table row by row. Reader problem is it have to parse types. But we want to get the string type of data already needed from the database. Ideal variant is string separated by commas. — Vlad
– Vlad, Commented Jun 1, 2018 at 15:30
It is without a doubt not possible to read data in a row-by-row fashion without using SqlDataReader, either directly or indirectly -- in fact, libraries typically benchmark against reading from a raw SqlDataReader precisely for this reason, because you can't get any faster (unless you want to implement the protocol from scratch). You can have SQL Server 2017 concatenate strings for you (STRING_AGG) but in earlier versions string concatenation is a much more difficult affair, so that's not really worth it. To optimize concatenation in .NET, use StringBuilder. — Jeroen Mostert
– Jeroen Mostert, Commented Jun 1, 2018 at 15:42

Tim Schmelter · Accepted Answer · 2018-06-01 15:10:13Z

4

Sure, you can cast it:

SELECT id = CAST(id  AS varchar(20)) FROM ...;

Now you don't need to convert it at client side and can use:

var idList = new List<string>();
using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        idList.Add(reader.GetString(0));
    }
}

For what it's worth, if you want a one-liner in future use this extension:

public static class DbExtensions
{
    public static List<T> ToList<T>(this IDataReader reader, int columnOrdinal = 0)
    {
        var list = new List<T>();
        while (reader.Read())
            list.Add((T) reader[columnOrdinal]);
        return list;
    }
}

Now you can use this code:

idList = reader.ToList<string>();

answered Jun 1, 2018 at 15:10

Tim Schmelter

462k79 gold badges720 silver badges981 bronze badges

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

6 Comments

Vlad Over a year ago

Thank you a lot for your answer. This give us more perfomance then parse it to string in c#. I've got one more question.Is it possible cast row values to varchar(like you did) and then separate them by commas?

Corak Over a year ago

@Vlad - I wonder about your use case... wouldn't it be better to have a class representing one row of the table.

Vlad Over a year ago

We use mapper that working with string values seperated by commas(like csv file). So we don't want to create objects for perfomance. This mapper also map csv files so we want to apply it to databases as well

Corak Over a year ago

@Vlad - Putting everything into a string line and then separating it again doesn't sound that performant. Someone has to basically .ToString() the values. With CAST as varchar the work is just "outsourced" to the SQL Server. So if every nanosecond counts, I'd skip any automated mapper and write a method by hand that creates and fills the specific instance per row of the reader.

Corak Over a year ago

If your App-Server and DB-Server can have a shared file system access, then maybe storing the query result as CSV and reading it back with your mapper would serve your purpose. see stackoverflow.com/a/21286975/1336590

|

Collectives™ on Stack Overflow

Read SQL Server table row by row already in string type

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related