0

In one application there is a database with "one to many". Models:

 public class NewForm
{
    public int Id { get; set; }
    [Required]
    public string HeadForm { get; set; }
    [Required]
    public string DescriptionForm { get; set; }
    public virtual List<Field> Fields { get; set; }
}
 public class Field
{
    public int Id { get; set; }
    public bool Check { get; set; }
    public string HeadField { get; set; }

    public int? NewFormId { get; set; }
    public virtual NewForm NewForm { get; set; }
}

In other application (Web Api) I take the data using sqlconnection:

        [HttpGet]
    public string GetNamesByIndex(int id)
    {
        string connectionString = @"data source = (localdb)\MSSQLLocalDB; Initial Catalog = EditFormApplication.Models.NewFormContext; Integrated Security=True";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT * FROM NewForms WHERE Id = "+id+"";
            command.Connection = connection;
            var reader = command.ExecuteReader();
            if (reader.HasRows) 
            {
                while (reader.Read()) 
                {
                    string HeadForm = reader.GetString(1);
                    return HeadForm;
                }
            }
            return "false";
         }
    }

For example, here I return HeadForm from table "NewForm". But to call an element from the table "Field" I need to create connection for it separately. Is it possible to do this with one sqlconnection or to get the whole model or model structure? I need to send data from the database to the View in format JSON. Also I need to do it without entity framework.

vamp123
  • 57
  • 6
  • `Entity` is a namespace that exists in `System.Data`. Did you mean you can't use [tag:entity-framework]? – Erik Philips Feb 22 '19 at 20:29
  • I need to do it without Entity – vamp123 Feb 22 '19 at 20:32
  • 3
    How does the tables in the database look? Seems to me like you could use a single query with a join, and some good-old-fashion conditions in your while loop, or simply run two queries at once. Anyway, you have a bigger problem on your hands - this code is vulnerable to SQL injection attacks. – Zohar Peled Feb 22 '19 at 20:39
  • 2
    Also, you can execute more than one `SqlCommand` (query) against an open connection. Refer to the answer from @lumberjack4 in the following post: https://stackoverflow.com/questions/13677318/how-to-run-multiple-sql-commands-in-a-single-sql-connection – David Tansey Feb 22 '19 at 20:39
  • What is `Entity`?????? – Erik Philips Feb 22 '19 at 21:15

1 Answers1

0

The only way to do is by doing an inner join on the table and select the fields in ADO query or stored procedure. Then you'll have to manually populate your models in code by doing a group by on the basis of NewForm's Id or/and other fields. You can use LINQ's GroupBy for this as well.

UPDATE Example:

SQL will be written as,

SELECT * FROM NewForms nf
  JOIN Field f on nf.Id = f.NewFormId

And then in code, by executing DataReader on this query

var dict = new Dictionary<int, NewForms>();

while(reader.HasRows)
{
    var id = int.Parse(reader["Id"].ToString())

    if(!dict.ContainsKey(id))
    {
        var newForm = new NewForms()
        {
            // Map properties from reader
        }

        dict[newForm.Id] = newForm; 
    }

    // Create Field from the reader's columns 
    var field = new Field()
    {
        // Map properties from reader
    };

    dict[id].Field.Add(field);
}

var newForms = dict.Values.ToList();

The usage of dictionary here is to efficiently map Fields without a nested loop.

PS: Code might have compilation errors as I typed it off of my mind.

Riaz Raza
  • 382
  • 1
  • 14
  • I'll be more than happy to give you coding examples if required – Riaz Raza Feb 22 '19 at 21:02
  • 1
    This is absolutely not the only way. It might not even be the best way. – Zohar Peled Feb 22 '19 at 21:05
  • I'll be more than happy to know if there's a better way for this, but since SQL Server only return results in tabular form, there's no way to get associated data in nested objects, even Entity Framework uses this strategy while grabbing nested entities – Riaz Raza Feb 22 '19 at 21:11
  • well, I really want to look at coding examples – vamp123 Feb 22 '19 at 21:31
  • A single command can execute multiple statements and return multiple result sets - for a single parent entity it might be a better option since the code would be much simpler. – Zohar Peled Feb 22 '19 at 22:13
  • @ZoharPeled I think the desired outcome is to get a list of New Forms, by the multiple result sets approach, you'll still have to map lists of child elements by looping through the results – Riaz Raza Feb 24 '19 at 00:04
  • I disagree. Check out the select statement - `WHERE Id = "+id+""` - I think it's for a single record in the `NewForms` table and all it's related records in the other table. – Zohar Peled Feb 24 '19 at 06:32