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);
}
