none
Return New[] from database RRS feed

  • Question

  • public static PropertySuggestion[] PropertySuggestions = new[]
            {
            //Code to access DB where the SqlDataReaders name is reader

                      new PropertySuggestion { PropId = "reader.GetInt(0)", DisplayName = "reader.GetString(1)" }

            }

    Aboe code doesn't work, that's my way of explaining what I want to do. How can I return all the PropertySuggestions from the query?

    Friday, October 4, 2019 9:33 AM

Answers

  • Hello,

    The following example reads from SQL-Server and works by changing the data provider to say OleDb to work for MS-Access.

    The class we are returning

    public class StateItem
    {
        public int Identifier { get; set; }
        public string Name { get; set; }
        public string Abbreviation { get; set; }
    
    }

    In the class below, there is a method that is overloaded, first method returns all records as a list (could be an array) while the second returns one return.

    using System.Collections.Generic;
    using System.Data.SqlClient;
    
    namespace MinimalApproachExample
    {
        class Example
        {
            public List<StateItem> GetStateInformation()
            {
                var stateInformation = new List<StateItem>();
                var selectStatement = "SELECT id,StateName,StateAbbrev FROM StateLookup";
    
                using (var cn = new SqlConnection { ConnectionString = "TODO" })
                {
                    using (var cmd = new SqlCommand { Connection = cn, CommandText = selectStatement })
                    {
                        cn.Open();
    
                        var reader = cmd.ExecuteReader();
    
                        while (reader.Read())
                        {
                            stateInformation.Add(new StateItem
                            {
                                Identifier = reader.GetInt32(0),
                                Name = reader.GetString(1),
                                Abbreviation = reader.GetString(2)
                            });
                        }
                    }
                }
    
                return stateInformation;
            }
            public StateItem GetStateInformation(int identifier)
            {
                var item = new StateItem();
                var selectStatement = "SELECT id,StateName,StateAbbrev FROM StateLookup WHERE id = @Id";
    
                using (var cn = new SqlConnection { ConnectionString = "" })
                {
                    using (var cmd = new SqlCommand { Connection = cn, CommandText = selectStatement })
                    {
                        cn.Open();
    
                        cmd.Parameters.AddWithValue("@id", identifier);
    
                        var reader = cmd.ExecuteReader();
                        if (!reader.HasRows) return item;
    
                        reader.Read();
                        item = new StateItem
                        {
                            Identifier = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Abbreviation = reader.GetString(2)
                        };
                    }
                }
    
                return item;
            }
        }
    
        public class StateItem
        {
            public int Identifier { get; set; }
            public string Name { get; set; }
            public string Abbreviation { get; set; }
    
        }
    }
    

    First method here returns an array rather than a list.

    public StateItem[] GetStateInformation()
    {
        var stateInformation = new List<StateItem>();
        var selectStatement = "SELECT id,StateName,StateAbbrev FROM StateLookup";
    
        using (var cn = new SqlConnection { ConnectionString = "TODO" })
        {
            using (var cmd = new SqlCommand { Connection = cn, CommandText = selectStatement })
            {
                cn.Open();
    
                var reader = cmd.ExecuteReader();
    
                while (reader.Read())
                {
                    stateInformation.Add(new StateItem
                    {
                        Identifier = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        Abbreviation = reader.GetString(2)
                    });
                }
            }
        }
    
        return stateInformation.ToArray();
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, October 4, 2019 10:04 AM
    Moderator