CODEWITHSUNDEEP
c#ado.net
user3263515
user3263515

Reputation: 67

returning json values with azure functions preview 2

I am interested in using azure functions for my new api preview 2 .net core so I setup a small function to query my database and return me the results. 

public static class Function1
    {
        private readonly KPContext _context;
        private static SqlConnection connection = new SqlConnection();
        [FunctionName("Function1")]
        public static Object Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)]HttpRequest req, TraceWriter log)
        {
            List<Employees> datastore = new List<Employees>();
            Employees employees = new Employees();
            string connstring = Environment.GetEnvironmentVariable("ConnectionStrings:SQlConnectionString");

            var conn = new SqlConnection(connstring);

            log.Info("C# HTTP trigger function processed a request.");

            conn.Open();

            var query = "select Employee_ID, Employee_LName, Employee_FName, CASE WHEN Username IS NULL THEN Employee_FName + '.' + Employee_LName ELSE Username end as Username from Employees";
            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataReader reader = cmd.ExecuteReader();

            while(reader.Read())
            {
                employees.Employee_ID = (int)reader[0];
                employees.Employee_LName = (string)reader[1] != null ? (string)reader[1] : "";
                employees.Employee_FName = (string)reader[2] != null ? (string)reader[2] : "";
                employees.UserName = (string)reader[3] != null ? (string)reader[3] : "";
                datastore.Add(employees);
            }
            return datastore;
        }
    }
}

so this works as intended but at the end it only gives me one person as all the results instead of giving me each individual result

so when I debug the code at first the code is doing what I expect going through each row then adding them to my datastore then all the sudden for really no good explanation one record ends up duplicating its self over every other record inside the list even though it keeps increment the datastore count until the end and I have the same record many times instead of all my employees. I take this same exact code out of azure functions and into an web api .net core 2 and it runs as expected.

enter image description here

Upvotes: 0

Views: 243

Answers (1)

Nkosi
Nkosi

Reputation: 247008

You keep adding the same Employees instance over and over in the while loop.

Create a new instance within in the loop and insert that.

while(reader.Read()) {
    Employees employees = new Employees() 
    {
        Employee_ID = (int)reader[0],
        Employee_LName = (string)reader[1] != null ? (string)reader[1] : "",
        Employee_FName = (string)reader[2] != null ? (string)reader[2] : "",
        UserName = (string)reader[3] != null ? (string)reader[3] : ""
    }
    datastore.Add(employees);
}

Upvotes: 2

Related Questions