Read data from SqlDataReader

Question

I have a SQL Server 2008 database and I am working on it in the backend  I am working on asp net C   SqlDataReader rdr   cmd ExecuteReader      while  rdr Read                           how do I read strings here             I know that the reader has values  My SQL command is to select just 1 column from a table  The column contains strings ONLY  I want to read the strings  rows  in the reader one by one  How do I do this

User · Answer

I know this is kind of old but if you are reading the contents of a SqlDataReader into a class  then this will be very handy  the column names of reader and class should be same  public static List lt T gt  Fill lt T gt  this SqlDataReader reader  where T   new                         List lt T gt  res   new List lt T gt                 while  reader Read                                  T t   new T                    for  int inc   0  inc  lt  reader FieldCount  inc                                          Type type   t GetType                        string name   reader GetName inc                       PropertyInfo prop   type GetProperty name                       if  prop    null                                                if  name    prop Name                                                        var value   reader GetValue inc                               if  value    DBNull Value                                                                 prop SetValue t  Convert ChangeType value  prop PropertyType   null                                                               prop SetValue t  value  null                                                                                      res Add t                             reader Close                 return res

User · Answer

For a single result   if  reader Read          Response Write reader 0  ToString         Response Write reader 1  ToString         For multiple results   while  reader Read          Response Write reader 0  ToString         Response Write reader 1  ToString

User · Answer

You have to read database columnhere  You could have a look on following code snippet                   string connectionString   ConfigurationManager ConnectionStrings  NameOfYourSqlConnectionString   ConnectionString                  using  var  connection   new SqlConnection connectionString                                          connection Open                         using  SqlCommand command   new SqlCommand  SELECT SomeColumnName FROM TableName    connection                                                  SqlDataReader sqlDataReader   command ExecuteReader                            if  sqlDataReader HasRows                                                        while  sqlDataReader Read                                                                  string YourFirstDataBaseTableColumn   sqlDataReader  SomeColumn   ToString       Remember Type Casting is required here it has to be according to database column data type                                 string YourSecondDataBaseTableColumn   sqlDataReader  SomeColumn   ToString                                    string YourThridDataBaseTableColumn   sqlDataReader  SomeColumn   ToString                                                                                     sqlDataReader Close                                               connection Close

User · Answer

string col1Value   rdr  ColumnOneName   ToString      or  string col1Value   rdr 0  ToString      These are objects  so you need to either cast them or  ToString

User · Answer

Put the name of the column begin returned from the database where  ColumnName  is   If it is a string  you can use  ToString     If it is another type  you need to convert it using System Convert   SqlDataReader rdr   cmd ExecuteReader    while  rdr Read          string column   rdr  ColumnName   ToString        int columnValue   Convert ToInt32 rdr  ColumnName

User · Answer

In the simplest terms  if your query returns column name and it holds a string   while  rdr Read          string yourString   rdr getString  column name

User · Answer

I would argue against using SqlDataReader here  ADO NET has lots of edge cases and complications  and in my experience most manually written ADO NET code is broken in at least one way  usually subtle and contextual    Tools exist to avoid this  For example  in the case here you want to read a column of strings  Dapper makes that completely painless   var region          some filter var vals   connection Query lt string gt        select Name from Table where Region  region      query     new   region      parameters   AsList      Dapper here is dealing with all the parameterization  execution  and row processing - and a lot of other grungy details of ADO NET  The  lt string gt  can be replaced with  lt SomeType gt  to materialize entire rows into objects

User · Answer

Actually  I figured it out myself that I could do this   while  rdr read          string str   rdr GetValue   ToString   Trim

User · Answer

I have a helper function like     public static string GetString object o                if  o    DBNull Value              return              return o ToString            then I use it to extract the string    tbUserName Text   GetString reader  UserName

User · Answer

I usually read data by data reader this way  just added a small example   string connectionString    Data Source DESKTOP-2EV7CF4 Initial Catalog TestDB User ID sa Password tintin11    string queryString    Select   from EMP    using  SqlConnection connection   new SqlConnection connectionString   using  SqlCommand command   new SqlCommand queryString  connection                                 connection Open                     using  SqlDataReader reader   command ExecuteReader                                          if  reader HasRows                                                while  reader Read                                                          Console WriteLine String Format   0    1    reader 0   reader 1                                                                         reader Close

User · Answer

using SqlDataReader rdr   cmd ExecuteReader          while  rdr Read                  var myString   rdr GetString 0     The 0 stands for  the 0 th column   so the first column of the result             Do somthing with this rows string  for example to put them in to a list         listDeclaredElsewhere Add myString

User · Answer

while rdr Read         string col rdr  colName   ToString        it wil work

User · Answer

Thought to share my helper method for those who can use it   public static class Sql       public static T Read lt T gt  DbDataReader DataReader  string FieldName                int FieldIndex          try   FieldIndex   DataReader GetOrdinal FieldName             catch   return default T              if  DataReader IsDBNull FieldIndex                         return default T                     else                       object readData   DataReader GetValue FieldIndex               if  readData is T                                return  T readData                            else                               try                                       return  T Convert ChangeType readData  typeof T                                      catch  InvalidCastException                                        return default T                                                       Usage   cmd CommandText     SELECT DISTINCT  SoftwareCode00    MachineID                       FROM  CM S01   dbo   INSTALLED SOFTWARE DATA    using  SqlDataReader data   cmd ExecuteReader          while  data Read                  usedBy Add              Sql Read lt String gt  data   SoftwareCode00                 Sql Read lt Int32 gt  data   MachineID               The helper method casts to any value you like  if it can t cast or the database value is NULL  the result will be null

[c#] Read data from SqlDataReader

Examples related to c#

Examples related to asp.net

Examples related to sql-server-2008

Examples related to sqldatareader