Add SQL SELECT query result to List

Question

I am new to C# and not very familiar with how a connection to a MySql database should be established.

I do have a table called packages which contains, for which i created a model:

namespace CService.Models
{
    public class Package
    {   

        public int id { get; set; }
        public String name { get; set; }
        public String description { get; set; }
        public Boolean tracking { get; set; }
        public int senderCityId { get; set; }
        public int destinationCityId { get; set; }
        public int senderId { get; set; }
        public int receiverId { get; set; }

        public Package(int id, string name, string description, Boolean tracking, int senderCityId,
            int destinationCityId, int senderId, int receiverId)
        {
            this.id = id;
            this.name = name;
            this.description = description;
            this.tracking = tracking;
            this.senderCityId = senderCityId;
            this.destinationCityId = destinationCityId;
            this.senderId = senderId;
            this.receiverId = receiverId;        }
    }
}

I am trying to make a select statement and assign the result to a local list:

MySqlConnection conn = new MySqlConnection("server = localhost; user id = root; database=assignment_four; password=Mysqlpassword123");

        public List<Package> getPackages()
        {

            List<Package> packages = new List<Package>();
            conn.Open();
            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * from packages";

            MySqlDataReader reader = cmd.ExecuteReader();

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    packages.Add(new Package(
                        reader.GetInt32(reader.GetOrdinal("id")),
                        reader.GetString(reader.GetOrdinal("name")),
                        reader.GetString(reader.GetOrdinal("description")),
                        reader.GetBoolean(reader.GetOrdinal("tracking")),
                        reader.GetInt32(reader.GetOrdinal("senderCity_id")),
                        reader.GetInt32(reader.GetOrdinal("destinationCity_id")),
                        reader.GetInt32(reader.GetOrdinal("sender_id")),
                        reader.GetInt32(reader.GetOrdinal("receiver_id"))

                    ));

                }
                reader.Close();
            }
            catch (Exception exp)
            {
                throw;
            }
            finally
            {

                conn.Close();
            }

        return packages;

However, when i try to run this, as a WCF service, i get the following error:

Failed to add a service. Service metadata may not be accessible. Make sure your service is running and exposing metadata.

The service was working before adding the above code, so I assume the problem resides in my data retrieval approach.

Answer 1

First, the error details mainly indicate that we should expose service metadata in order to consume the service on the client side, but it could not solve our problem.
Secondly, we should add OperationContractAttribute to the operation method and add DataContractAttribute to the custom complex type.
I have made a demo, wish it is useful to you.
Interface and Service implement

[ServiceContract]
public interface IService1
{
    [OperationContract]
    [WebGet(RequestFormat =WebMessageFormat.Json,ResponseFormat =WebMessageFormat.Json)]
    List<Product> GetProducts(); }
[DataContract]
public class Product
{
    [DataMember]
    public int ID { get; set; }
    [DataMember]
    public string Name { get; set; }
    [DataMember]
    public int Price { get; set; }
    public Product(int id, string name, int price)
    {
        this.ID = id;
        this.Name = name;
        this.Price = price;
    } }
public class Service1 : IService1
{

    public List<Product> GetProducts()
    {
        List<Product> products = new List<Product>();
        SqlConnection connection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DataStore;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
        SqlCommand command = new SqlCommand("select * from Products", connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            products.Add(new Product(reader.GetInt32((reader.GetOrdinal("Id"))), reader.GetString(reader.GetOrdinal("Name")), reader.GetInt32(reader.GetOrdinal("Price"))));
        }
        reader.Close();
        connection.Close();

        return products;
    }
}

web.config

<system.serviceModel>
<services>
  <service name="WcfService1.Service1">
    <endpoint address="" binding="webHttpBinding" contract="WcfService1.IService1" behaviorConfiguration="rest"></endpoint>
    <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"></endpoint>
  </service>
</services>
<behaviors>
  <endpointBehaviors>
    <behavior name="rest">
      <webHttp/>
    </behavior>
  </endpointBehaviors>
  <serviceBehaviors>
    <behavior>
      <serviceMetadata httpGetEnabled="true" httpsGetEnabled="true" httpGetUrl="mex"/>
      <serviceDebug includeExceptionDetailInFaults="true"/>
    </behavior>
  </serviceBehaviors>
</behaviors>

Result. Besides, one thing must be noted is that if we want to query the localdb database(vs2017 built-in database) in IIS, we should configure the following in the IIS Application Pool which hosted the wcf service.

loadUserProfile="true" setProfileEnvironment="true" 

Feel free to let me know if there is anything I can help with.