環境Environmental ASP.NET MVC4(C#)
Thank you for your help.
Objective → Achieve a drop-down list of 3 integrations with knockoutjs
Based on the official sample, create mock data for 3 linkage (parent, child, grandchild)
I have confirmed that it can be achieved.
However, data for 3 integrations are originally extracted from DB.
I'm getting it from IList now.
The problem is that the SQL extraction results are structured the same as the mock data (← both will be discussed later).
I looked it up on various websites, but I didn't know the result of the difficulty, so I asked a question.
How do I create an array with the same structure as the mock data from the SQL extraction results?
An image with a child array in the parent array and a code and name array in the child.
I would appreciate it if you could give me an easy-to-understand explanation in this area.
And I'm in a great hurry (sweat)
Please let me know.
Below are the SQL extraction contents and mock data structure.
(The value is rewritten as an example.)
<SQL Extraction>
Mother/Son Code Son Name
2019, Division 1,001, Sub-Name of Division 1
2019, Division 1,002, Division 1 Sub-Name 2
2019, Department 2,002, Department 2 sub-
2018, Division 1,001, Sub-Name of Division 1
Sub-names of 2018, Division A, 001, Division A
2018, Division A, 002, Sub-Name of Division A 2
<Mock Data>
varmoq=
[
{
"Parents"
[
{
"Child":
[
{
"Grandchild Code": "001",
"Grandchild Name": "Sub-Name of Division 1"
},
{
"Grandchild Code": "002",
"Grandchild Name": "Subname 2 of Division 1"
}
],
"Child Value": "Department 1"
},
{
"Child":
[
{
"Grandchild Code": "001",
"Grandchild Name": "Sub-Name of Division 2"
}
],
"Child Value": "Department 2"
}
],
"Parent value"
},
{
"Parents"
[
{
"Child":
[
{
"Grandchild Code": "001",
"Grandchild Name": "Sub-Name of Division 1"
}
],
"Child Value": "Department 1"
},
{
"Child":
[
{
"Grandchild Code": "001",
"Grandchild Name": "Sub-Name of Division B"
},
{
"Grandchild Code": "002",
"Grandchild Name": "Sub-Name of Division B"
}
],
Child Value: Department B
}
],
Parent value: 2018
}
];
Because mock data is a complex JSON format, we don't know which library converts SQL extraction results to the desired structure.(Probably nonexistent)
You can convert to JSON using a standard library such as DataContractJsonSerializer.
Please check if the sample code below is helpful.
To run the sample code below, you must add System.Runtime.Serialization in the reference configuration and System.Data.SQLite.Core in NuGet.
using System;
using System.Collections.General;
using System.Data.SQLite;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
namespace ConsoleApp1
{
class program
{
const string MOC_SQL=@"
select2019 Parent, 'Department 1' Child, '001' Son Code, 'Sub-Name of Division 1' Son name union all
select2019 parent, 'Department 1' child, '002' grandchild code, 'Department 1' sub-name 2' grandchild name union all
select2019 Parent, 'Department 2' Child, '002' Son Code, 'Sub-Name of Division 2' Son name union all
select 2018 Parent, 'Department 1' Child, '001' Son Code, 'Sub-Name of Division 1' Son name union all
select 2018 Parent, 'Department A' Child, '001' Son Code, 'Sub-Name of Department A' Son Union all
select2018 Parent, 'Department A' Child, '002' Son Code, 'Department A Sub-Name 2' Son Name;
static void Main (string[]args)
{
// DB structure reading
var list = LoadMockList();
using(var stream=new MemoryStream())
{
// JSON serialization
varserializer = new DataContractJsonSerializer (typeof(MockList)));
serializer.WriteObject(stream, list);
stream.Position=0;
using(var reader=new StreamReader(stream))
{
// Output
Console.Write (reader.ReadToEnd());
}
}
}
/// <summary>
/// Create a JSON for a Mock from the Database
/// </summary>
/// <returns></returns>
private static MockList LoadMockList()
{
var list = new MockList();
// This is a sample code using todo SQLite, so please replace it with the DB connection you need.
varscsb=new SQLiteConnectionStringBuilder {DataSource=":memory:"};
using(varcn=new SQLiteConnection(scsb.ToString()))
{
cn.Open();
using(varcmd=newSQLiteCommand(cn))
{
// Get value in SQL
cmd.CommandText=MOC_SQL;
var reader = cmd.ExecuteReader();
while(reader.Read())
{
// Read and list structures
list.Merge(reader);
}
}
}
return list;
}
}
public class MockList:List<MockParent>
{
/// <summary>
/// Add/Retrieve elements from parent values and add child elements
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public void Merge (SQLiteDataReader)
{
MockParent parent;
if(Exists(m=>m.Value==reader["parent".ToString())))
{
parent=Find(m=>m.Value==reader["parent"].ToString());
parent.Merge(reader);
}
else
{
parent = new MockParent(reader);
Add (parent);
}
}
}
DataContract
public class MockParent
{
[DataMember(Name="parent", Order=1)]
public List<MockChild>Children {get;set;}
[DataMember(Name="parent value", Order=2)]
public string value {get;set;}
public MockParent()
{
Children = new List <MockChild >();
}
public MockParent (SQLiteDataReader reader): this()
{
Children.Add (new MockChild (reader));
Value=reader ["parent".ToString();
}
/// <summary>
/// Add/Retrieve elements from child values and add grandson elements
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public void Merge (SQLiteDataReader)
{
MockChild child;
if(Children.Exists(m=>m.Value==reader["child".ToString())))
{
child=Children.Find(m=>m.Value==reader["child"].ToString());
child.Grandsons.Add (new MockGrandson (reader));
}
else
{
child = new MockChild(reader);
Children.Add(child);
}
}
}
DataContract
public class MockChild
{
[DataMember(Name="child", Order=1)]
public List <MockGrandson>Grandsons {get;set;}
[DataMember(Name="child value", Order=2)]
public string value {get;set;}
public MockChild()
{
Grandsons = new List <MockGrandson >();
}
public MockChild (SQLiteDataReader reader): this()
{
Grandsons.Add (new MockGrandson (reader));
Value=reader ["child"].ToString();
}
}
DataContract
public class MockGrandson
{
[DataMember(Name="Grandchild Code")]
public string code {get;set;}
[DataMember(Name="Grandchild")]
public string value {get;set;}
public MockGranson (SQLiteDataReader reader)
{
Code=reader ["grandchild code".ToString();
Value=reader ["Grandchild"].ToString();
}
}
}
DataContractJsonSerializer cannot shape JSON data, but it can output the same structure as the <mock data> presented.
The value also matches the grandchild code in line 3 of SQL and except for "Department A" and "Department B" in line 5 and 6.
Dear Payaneco,
I apologize for the delay in my reply.
Based on the source sample you provided, I will think about various things here too
I was able to do what I wanted.
The biggest point is that you have to build your own three-tier list.
As long as we don't do this or we don't have this original material, we can't create JSON no matter what kind of conversion library we have.
To put it the other way around, JSON can be created using any library if it has original material.
Payaneco taught me that I have to create a three-tier object myself.
There are many ways to create JSON data, but I chose Json.NET, which was easy to understand.
The following is a fairly slow, old-fashioned implementation, but now we're able to do it.
(Rewrite variable names, etc.)
It's a rough flow, but
Lick the list from DB one after another and
Create a three-tier object using the basic method of writing to add each list after the break.
In JsonConvert.SerializeObject, put it in a string variable and use the view side (cshtml) javascript to
JSON.parse to set the variable for knockoutjs.
public class grandchild
{
public string grandson {get;set;}
public string grandname {get;set;}
public string grandson Disp {get;set;}
}
public class child
{
public string child {get;set;}
public List <grandchild>grandchild List {get;set;}
}
// List of parents and grandchildren
public class parent-child list
{
public string parent {get;set;}
public List <Child >Child List {get;set;}
}
public void CreateJson()
{
// Data Retrieval
vardao = new sample dao("Connected";
var results=dao.sampleInfoList();
if(results==null)
{
error handling
}
// Create json for parents, children, and grandchildren
varkey parent = string.Empty;// break key:parent
varkey child = string.Empty; // Breakkey: child
List<parent-child >JsonObj=new List<parent-child >();
child list child;
List of grandchildren;
foreach (var result in results)
{
if (key parent!=result.parent)
{
Parent-child grandson JsonObjtemp=new Parent-child grandson();
JsonObjtemp.parent = result.parent;
JsonObj.Add (JsonObjtemp);
JsonObj[JsonObj.Count-1].childList = newList<child>();
key parent = result.parent;
};
if(key child!=result.child)
{
list child = new child();
list child.child = result.child;
list child.Grandchild List=new List<Grandchild>();
JsonObj[JsonObj.Count-1]. child List.Add (list child);
key child = result.child;
};
list grandson = new grandson();
list grandson = result. grandchild;
list grandchildren.grandchildren=result.grandchildren;
list grandson.grandchild Disp=result.grandchild+"+result.grandchild;
List child.Grandchild List.Add (list grandchild);
}
ViewModel. Parent-child List=JsonConvert.SerializeObject(JsonObj);//←ViewModel. Parent-child List is string.
return;
}
view side (cshtml)
<script type="text/javascript">
variant=function(){
varself = this;
self. Parent-child grandson = ko.observable(JSON.parse('@Html.Raw(Model. Parent-child grandchild list)));
...
...
</script>
Thank you very much for teaching me so much.
Now I'd like to close the question.
© 2024 OneMinuteCode. All rights reserved.