public bool convertSQL2NonSQLVer1(List <BmSQLTableDataType> tables, TreeView tree) { tree.Nodes.Clear(); bool resutl = true; foreach (BmSQLTableDataType table in tables) { TreeNode node = new TreeNode(); node.Text = table.TableName; //BmClassBuilder MCB = new BmClassBuilder("BookStore"); //var myclass = MCB.CreateObject(new string[5] { "Id", "BookTitle", "Auther", "Category", "ISBN" }, new Type[5] { typeof(ObjectId), typeof(string), typeof(string), typeof(string), typeof(string) }); //var myObject = Activator.CreateInstance(myclass.GetType()); //myclass.GetType().GetProperty("BookTitle", BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, "dddMongoDB Basics", null); //myclass.GetType().GetProperty("ISBN", BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, "8767687689898yu", null); //myclass.GetType().GetProperty("Auther", BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, "Tanya", null); //myclass.GetType().GetProperty("Category", BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, "NoSQL DBMS", null); //var collection = mMongoDB.GetCollection("BookStore"); // collection.Save(myObject); BmClassBuilder MCB = new BmClassBuilder(table.TableName); string[] cols = new string[table.Columns.Count + 1]; Type[] types = new Type[table.Columns.Count + 1]; cols[0] = "Id"; types[0] = typeof(ObjectId); string keyName = ""; for (int i = 0; i < table.Columns.Count; i++) { cols[i + 1] = table.Columns[i].ColName; bool isForeignKey = table.Columns[i].IsForeignKey; if (isForeignKey) { types[i + 1] = typeof(BsonArray); } else { types[i + 1] = table.Columns[i].getType(); } if (table.Columns[i].IsKey) { keyName = table.Columns[i].ColName; } } var myclass = MCB.CreateObject(cols, types); var collection = m_sqlCnn.nonSQLCNN.GetCollection(table.TableName); if (!keyName.Equals("")) { collection.EnsureIndex(IndexKeys.Ascending(keyName), IndexOptions.SetUnique(true)); } bool isUpdateTree = false; foreach (BmSQLDataRow row in table.Data) { var myObject = Activator.CreateInstance(myclass.GetType()); for (int i = 0; i < row.ColValues.Count; i++) { TreeNode subNode = new TreeNode(); subNode.Text = table.Columns[i].ColName + " : " + convertDataName(table.Columns[i].DataTypeName); //check foreign Key bool isForeignKey = table.Columns[i].IsForeignKey; if (isForeignKey) { bool isParent = isParentTable(table.Columns[i].ParentTableName, tables); subNode.Text = table.Columns[i].ColName + " : BsonArray"; BsonArray array = new BsonArray(); if (isParent) { array.Add(new BsonDocument() { { "Parent_Table", table.Columns[i].ParentTableName } }); if (row.ColTypes[i] == typeof(string)) { array.Add(new BsonDocument() { { table.Columns[i].ColName, row.ColValues[i] } }); } else if (row.ColTypes[i] == typeof(decimal)) { array.Add(new BsonDocument() { { table.Columns[i].ColName, Double.Parse(row.ColValues[i]) } }); } else if (row.ColTypes[i] == typeof(bool)) { array.Add(new BsonDocument() { { table.Columns[i].ColName, Boolean.Parse(row.ColValues[i]) } }); } else if (row.ColTypes[i] == typeof(DateTime)) { array.Add(new BsonDocument() { { table.Columns[i].ColName, DateTime.Parse(row.ColValues[i]) } }); } else if (row.ColTypes[i] == typeof(int)) { array.Add(new BsonDocument() { { table.Columns[i].ColName, Int32.Parse(row.ColValues[i]) } }); } else { array.Add(new BsonDocument() { { table.Columns[i].ColName, null } }); } TreeNode subChildNode1 = new TreeNode(); subChildNode1.Text = "Parent_Table : String "; subNode.Nodes.Add(subChildNode1); TreeNode subChildNode2 = new TreeNode(); subChildNode2.Text = table.Columns[i].ColName + " : " + convertDataName(table.Columns[i].DataTypeName); subNode.Nodes.Add(subChildNode2); } else { List <BmSQLDataRow> newRows = getParentRow(table.Columns[i].ReferenceColName, row.ColValues[i], table.Columns[i].ParentTableName, tables); foreach (BmSQLDataRow newRow in newRows) { BsonArray subArray = new BsonArray(); for (int j = 0; j < newRow.ColValues.Count; j++) { if (newRow.ColTypes[j] == typeof(string)) { subArray.Add(new BsonDocument() { { newRow.ColNames[j], newRow.ColValues[j] } }); } else if (newRow.ColTypes[j] == typeof(decimal)) { subArray.Add(new BsonDocument() { { newRow.ColNames[j], Double.Parse(newRow.ColValues[j]) } }); } else if (newRow.ColTypes[j] == typeof(bool)) { subArray.Add(new BsonDocument() { { newRow.ColNames[j], Boolean.Parse(newRow.ColValues[j]) } }); } else if (newRow.ColTypes[j] == typeof(DateTime)) { subArray.Add(new BsonDocument() { { newRow.ColNames[j], DateTime.Parse(newRow.ColValues[j]) } }); } else if (newRow.ColTypes[j] == typeof(int)) { subArray.Add(new BsonDocument() { { newRow.ColNames[j], Int32.Parse(newRow.ColValues[j]) } }); } else { subArray.Add(new BsonDocument() { { newRow.ColNames[j], null } }); } TreeNode subChildNode3 = new TreeNode(); subChildNode3.Text = newRow.ColNames[j] + " : " + convertDataName(newRow.ColTypes[j]); subNode.Nodes.Add(subChildNode3); } array.Add(subArray); } } myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, array, null); } else if (row.ColTypes[i] == typeof(string)) { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, row.ColValues[i], null); } else if (row.ColTypes[i] == typeof(decimal)) { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, Double.Parse(row.ColValues[i]), null); } else if (row.ColTypes[i] == typeof(bool)) { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, Boolean.Parse(row.ColValues[i]), null); } else if (row.ColTypes[i] == typeof(DateTime)) { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, DateTime.Parse(row.ColValues[i]), null); } else if (row.ColTypes[i] == typeof(int)) { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, Int32.Parse(row.ColValues[i]), null); } else { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, null, null); } if (!isUpdateTree) { node.Nodes.Add(subNode); } } isUpdateTree = true; collection.Insert(myObject); } //TreeNode node = new TreeNode(); //node.Text = table.TableName; //foreach (BmSQLColumnDataType column in table.Columns) //{ // TreeNode subNode = new TreeNode(); // subNode.Text = column.ColName + " : " + column.DataTypeName; // if (column.IsKey) // subNode.Text += " - Primary Key"; // else if (column.IsForeignKey) // subNode.Text += " - Foreign Key : (" + column.ParentTableName + "-" + column.ReferenceColName + ")"; // node.Nodes.Add(subNode); //} //tvSQLSchema.Nodes.Add(node); tree.Nodes.Add(node); } return(resutl); }
public bool convertSQL2NonSQLVer2(List <BmSQLTableDataType> tables, TreeView tree) { tree.Nodes.Clear(); //check include tables m_treeData.Nodes.Clear(); int removeTableIndex = 0; foreach (BmSQLTableDataType table in tables) { int numForeignKey = 0; string stParentTableName = ""; string stForeignKeyName = ""; foreach (BmSQLColumnDataType col in table.Columns) { if (col.IsForeignKey) { numForeignKey++; stParentTableName = col.ParentTableName; stForeignKeyName = col.ColName; } } if (numForeignKey == 1) { bool noParent = false; foreach (BmSQLTableDataType table1 in tables) { foreach (BmSQLColumnDataType col1 in table1.Columns) { if (col1.IsForeignKey && col1.ParentTableName.Equals(table.TableName)) { noParent = true; break; } } if (noParent) { break; } } if (!noParent) { int tableIndex = 0; foreach (BmSQLTableDataType table2 in tables) { if (table2.TableName.Equals(stParentTableName)) { BmSQLColumnDataType newCol = new BmSQLColumnDataType(); newCol.DataType = BmSQLDataType.SQL_DATA_TYPE_ARRAY; newCol.ColName = table.TableName; newCol.IncludeTableName = table.TableName; newCol.IncludeForeignKeyName = stForeignKeyName; newCol.IsIncludeTable = true; tables[tableIndex].Columns.Add(newCol); tables[removeTableIndex].IsRemove = true; break; } tableIndex++; } } } removeTableIndex++; } bool resutl = true; foreach (BmSQLTableDataType table in tables) { if (table.IsRemove) { continue; } TreeNode node = new TreeNode(); node.Text = table.TableName; TreeNode nodeData = new TreeNode(); nodeData.Text = table.TableName; //BmClassBuilder MCB = new BmClassBuilder("BookStore"); //var myclass = MCB.CreateObject(new string[5] { "Id", "BookTitle", "Auther", "Category", "ISBN" }, new Type[5] { typeof(ObjectId), typeof(string), typeof(string), typeof(string), typeof(string) }); //var myObject = Activator.CreateInstance(myclass.GetType()); //myclass.GetType().GetProperty("BookTitle", BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, "dddMongoDB Basics", null); //myclass.GetType().GetProperty("ISBN", BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, "8767687689898yu", null); //myclass.GetType().GetProperty("Auther", BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, "Tanya", null); //myclass.GetType().GetProperty("Category", BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, "NoSQL DBMS", null); //var collection = mMongoDB.GetCollection("BookStore"); // collection.Save(myObject); BmClassBuilder MCB = new BmClassBuilder(table.TableName); string[] cols = new string[table.Columns.Count + 1]; Type[] types = new Type[table.Columns.Count + 1]; cols[0] = "Id"; types[0] = typeof(ObjectId); string keyName = ""; for (int i = 0; i < table.Columns.Count; i++) { cols[i + 1] = table.Columns[i].ColName; if (table.Columns[i].IsIncludeTable) { types[i + 1] = typeof(BsonArray); } else { types[i + 1] = table.Columns[i].getType(); } if (table.Columns[i].IsKey) { keyName = table.Columns[i].ColName; } } var myclass = MCB.CreateObject(cols, types); var collection = m_sqlCnn.nonSQLCNN.GetCollection(table.TableName); if (!keyName.Equals("")) { collection.EnsureIndex(IndexKeys.Ascending(keyName), IndexOptions.SetUnique(true)); } bool isUpdateTree = false; int rowIndex = 0; foreach (BmSQLDataRow row in table.Data) { try { var myObject = Activator.CreateInstance(myclass.GetType()); string primeyKeyValue = ""; TreeNode subNodeRow = new TreeNode(); subNodeRow.Text = "row " + rowIndex++; for (int i = 0; i < table.Columns.Count; i++) { TreeNode subNode = new TreeNode(); subNode.Text = table.Columns[i].ColName + " : " + convertDataName(table.Columns[i].DataTypeName); TreeNode subNodeData = new TreeNode(); if (i < table.Columns.Count - 1) { subNodeData.Text = table.Columns[i].ColName + " : " + row.ColValues[i]; } //check foreign Key if (table.Columns[i].IsKey) { primeyKeyValue = row.ColValues[i]; } if (table.Columns[i].IsIncludeTable) { subNode.Text = table.Columns[i].ColName + " : BsonArray"; subNodeData.Text = table.Columns[i].ColName; BsonArray array = new BsonArray(); List <BmSQLDataRow> newRows = getIncludedRow(table.Columns[i].IncludeForeignKeyName, primeyKeyValue, table.Columns[i].IncludeTableName, tables); foreach (BmSQLDataRow newRow in newRows) { BsonArray subArray = new BsonArray(); for (int j = 0; j < newRow.ColValues.Count; j++) { if (newRow.ColTypes[j] == typeof(string)) { subArray.Add(new BsonDocument() { { newRow.ColNames[j], newRow.ColValues[j] } }); } else if (newRow.ColTypes[j] == typeof(decimal)) { subArray.Add(new BsonDocument() { { newRow.ColNames[j], Double.Parse(newRow.ColValues[j]) } }); } else if (newRow.ColTypes[j] == typeof(bool)) { subArray.Add(new BsonDocument() { { newRow.ColNames[j], Boolean.Parse(newRow.ColValues[j]) } }); } else if (newRow.ColTypes[j] == typeof(DateTime)) { subArray.Add(new BsonDocument() { { newRow.ColNames[j], DateTime.Parse(newRow.ColValues[j]) } }); } else if (newRow.ColTypes[j] == typeof(int)) { subArray.Add(new BsonDocument() { { newRow.ColNames[j], Int32.Parse(newRow.ColValues[j]) } }); } else { subArray.Add(new BsonDocument() { { newRow.ColNames[j], "" } }); } TreeNode subChildNode3 = new TreeNode(); subChildNode3.Text = newRow.ColNames[j] + " : " + convertDataName(newRow.ColTypes[j]); subNode.Nodes.Add(subChildNode3); TreeNode subChildNodeData = new TreeNode(); subChildNodeData.Text = newRow.ColNames[j] + newRow.ColValues[j]; subNodeData.Nodes.Add(subChildNodeData); } array.Add(subArray); } myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, array, null); } else if (row.ColTypes[i] == typeof(string)) { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, row.ColValues[i], null); } else if (row.ColTypes[i] == typeof(decimal)) { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, Double.Parse(row.ColValues[i]), null); } else if (row.ColTypes[i] == typeof(bool)) { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, Boolean.Parse(row.ColValues[i]), null); } else if (row.ColTypes[i] == typeof(DateTime)) { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, DateTime.Parse(row.ColValues[i]), null); } else if (row.ColTypes[i] == typeof(int)) { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, Int32.Parse(row.ColValues[i]), null); } else { myclass.GetType().GetProperty(table.Columns[i].ColName, BindingFlags.Public | BindingFlags.Instance).SetValue(myObject, null, null); } if (!isUpdateTree) { node.Nodes.Add(subNode); } subNodeRow.Nodes.Add(subNodeData); } nodeData.Nodes.Add(subNodeRow); isUpdateTree = true; collection.Insert(myObject); } catch (Exception ex) { } } //TreeNode node = new TreeNode(); //node.Text = table.TableName; //foreach (BmSQLColumnDataType column in table.Columns) //{ // TreeNode subNode = new TreeNode(); // subNode.Text = column.ColName + " : " + column.DataTypeName; // if (column.IsKey) // subNode.Text += " - Primary Key"; // else if (column.IsForeignKey) // subNode.Text += " - Foreign Key : (" + column.ParentTableName + "-" + column.ReferenceColName + ")"; // node.Nodes.Add(subNode); //} //tvSQLSchema.Nodes.Add(node); tree.Nodes.Add(node); m_treeData.Nodes.Add(nodeData); } return(resutl); }