private void getSchema(System.Data.DataTable table) { foreach (System.Data.DataRow row in table.Rows) { BmSQLTableDataType sqlTable = null; foreach (System.Data.DataColumn col in table.Columns) { if (col.ColumnName.Equals("TABLE_NAME")) { Debug.WriteLine("{0} = {1}", col.ColumnName, row[col]); sqlTable = new BmSQLTableDataType(); sqlTable.TableName = row[col].ToString(); DataTable fields = m_sqlCnn.sqlCNN.GetSchema(SqlClientMetaDataCollectionNames.Columns, new string[] { null, null, sqlTable.TableName }); try { foreach (DataRow dr in fields.Rows) { BmSQLColumnDataType sqlColumn = new BmSQLColumnDataType(); sqlColumn.ColName = dr["COLUMN_NAME"].ToString(); // sqlColumn.updateDataType(dr[7].ToString()); Debug.WriteLine(sqlColumn.ColName); //get column type string sql = "SELECT + [" + sqlColumn.ColName + "] FROM [" + sqlTable.TableName + "]"; SqlCommand sqlCMD = new SqlCommand(sql, m_sqlCnn.sqlCNN); SqlDataReader sqlReader = sqlCMD.ExecuteReader(CommandBehavior.KeyInfo); DataTable schemaDataType = sqlReader.GetSchemaTable(); //foreach (ForeignKeyConstraint fk in schemaDataType.Constraints) //{ // Debug.WriteLine("Contraints: " + fk.RelatedColumns[0] + " - " + fk.RelatedColumns[1]); //} //foreach (DataRow type in schemaDataType.Rows) if (schemaDataType.Rows.Count > 0) { DataRow type = schemaDataType.Rows[0]; foreach (DataColumn property in schemaDataType.Columns) { Debug.WriteLine(property.ColumnName + " : " + type[property].ToString()); if (property.ColumnName.Equals("DataTypeName")) { sqlColumn.updateDataType(type[property].ToString()); } else if (property.ColumnName.Equals("NumericPrecision")) { sqlColumn.NumericPrecision = Int16.Parse(type[property].ToString()); } else if (property.ColumnName.Equals("NumericScale")) { sqlColumn.NumericScale = Int16.Parse(type[property].ToString()); } else if (property.ColumnName.Equals("IsUnique")) { sqlColumn.IsUnique = Boolean.Parse(type[property].ToString()); } else if (property.ColumnName.Equals("IsKey")) { sqlColumn.IsKey = Boolean.Parse(type[property].ToString()); } else if (property.ColumnName.Equals("IsIdentity")) { sqlColumn.IsIdentity = Boolean.Parse(type[property].ToString()); } else if (property.ColumnName.Equals("IsAutoIncrement")) { sqlColumn.IsAutoIncrement = Boolean.Parse(type[property].ToString()); } else if (property.ColumnName.Equals("IsReadOnly")) { sqlColumn.IsReadOnly = Boolean.Parse(type[property].ToString()); } } } sqlReader.Close(); string sql2 = "SELECT f.name AS foreign_key_name ,OBJECT_NAME(f.parent_object_id) AS table_name " + ", COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name" + " , OBJECT_NAME (f.referenced_object_id) AS referenced_object " + " , COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name " + "FROM sys.foreign_keys AS f " + "INNER JOIN sys.foreign_key_columns AS fc " + " ON f.object_id = fc.constraint_object_id " + "WHERE f.parent_object_id = OBJECT_ID('" + sqlTable.TableName + "') AND COL_NAME(fc.referenced_object_id, fc.referenced_column_id) = '" + sqlColumn.ColName + "'; "; SqlCommand sqlCMD2 = new SqlCommand(sql2, m_sqlCnn.sqlCNN); SqlDataReader sqlReader2 = sqlCMD2.ExecuteReader(); while (sqlReader2.Read()) { string foreign_key_name = sqlReader2["foreign_key_name"].ToString(); string parent_table_name = sqlReader2["referenced_object"].ToString(); string referenced_column_name = sqlReader2["referenced_column_name"].ToString(); string constraint_column_name = sqlReader2["constraint_column_name"].ToString(); sqlColumn.IsForeignKey = true; sqlColumn.ParentTableName = parent_table_name; sqlColumn.ReferenceColName = referenced_column_name; Debug.WriteLine("Forign Key: " + foreign_key_name); Debug.WriteLine("Parent Table: " + parent_table_name + " - Column Constraint:" + constraint_column_name + " --- Ref: " + referenced_column_name); } sqlReader2.Close(); sqlTable.Columns.Add(sqlColumn); } } catch (Exception ex) { } } } if (sqlTable != null) { sqlTable.Data = getData(sqlTable); SqlSchema.Tables.Add(sqlTable); } Debug.WriteLine("============================"); } }
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); }