public IActionResult RawQueryComplex(int id) { IList <PersonAddressView> list = new List <PersonAddressView>(); using (var conn = _context.Database.GetDbConnection()) { conn.Open(); using (var command = conn.CreateCommand()) { var sql = "SELECT P.PersonID,P.FirstName,P.LastName,P.Email,P.Mobile,P.AddressID,A.AddressLine,A.City,A.Pin FROM People P,PAddresss A" + "WHERE P.AddressID = A.AddressID"; //var sql = "SELECT P.PersonID, P.FirstName, P.LastName, P.Email, P.Mobile, P.AddressID, A.AddressLine, A.City, A.Pin FROM People P left join PAddress A on P.AddressID = A.AddressID"; command.CommandText = sql; using (var reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { var record = new PersonAddressView() { PersonID = reader.GetInt32(0), FirstName = reader.GetString(1), LastName = reader.GetString(2), Email = reader.GetString(3), Mobile = reader.GetInt64(4), AddressID = reader.GetInt32(5), AddressLine = reader.GetString(6), City = reader.GetString(7), Pin = reader.GetInt32(8) }; list.Add(record); } } } // reader } // command conn.Close(); } // connection return(View(list)); }
public IActionResult RawQueryComplex(int id) { IList <PersonAddressView> list = new List <PersonAddressView>(); using (var conn = _context.Database.GetDbConnection()) { conn.Open(); using (var command = conn.CreateCommand()) { var sql = "SELECT P.AutoId, P.FirstName, P.LastName, P.Age, P.Active, P.AddressId, A.AddressLine, A.City, A.Pin FROM People P, Addresses A " + " WHERE P.AddressId = A.AddressId"; command.CommandText = sql; using (var reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { var record = new PersonAddressView() { Active = reader.GetBoolean(4), AddressId = reader.GetInt32(5), AddressLine = reader.GetString(6), Age = reader.GetInt32(3), AutoId = reader.GetInt32(0), City = reader.GetString(7), FirstName = reader.GetString(1), LastName = reader.GetString(2), Pin = reader.GetString(8) }; list.Add(record); } } } // reader } // command conn.Close(); } // connection return(View(list)); }