0

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

Vlad
  • 193
  • 2
  • 14
  • 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 Jun 01 '18 at 15:09
  • 1
    Are you looking for an ORM like [Dapper](https://github.com/StackExchange/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 Jun 01 '18 at 15:14
  • 1
    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 Jun 01 '18 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 Jun 01 '18 at 15:30
  • 1
    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 Jun 01 '18 at 15:42
  • Also, there's `bcp` and/or the Data Import-Export Wizard in Management Studio for optimized transfer from SQL Server to files, although the options for formatting the output are limited, and if you need any kind of post-processing you're probably still better off with carefully written .NET code. – Jeroen Mostert Jun 01 '18 at 15:45

1 Answers1

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>();
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • 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? – Vlad Jun 01 '18 at 15:21
  • @Vlad - I wonder about your use case... wouldn't it be better to have a class representing one row of the table. – Corak Jun 01 '18 at 15:23
  • 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 – Vlad Jun 01 '18 at 15:25
  • @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 Jun 01 '18 at 15:36
  • 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 https://stackoverflow.com/a/21286975/1336590 – Corak Jun 01 '18 at 15:41
  • I'm in bus now. But if you want to separate them with comma use: String.Join(",", idList) – Tim Schmelter Jun 01 '18 at 15:44