private List <BmSQLDataRow> getData(BmSQLTableDataType table) { List <BmSQLDataRow> lsData = new List <BmSQLDataRow>(); SqlDataReader sqlReader = null; try { string sql = "SELECT "; for (int i = 0; i < table.Columns.Count; i++) { sql += " " + table.Columns[i].ColName + " "; if (i < table.Columns.Count - 1) { sql += " , "; } } sql += " FROM[" + table.TableName + "]"; SqlCommand sqlCMD = new SqlCommand(sql, m_sqlCnn.sqlCNN); sqlReader = sqlCMD.ExecuteReader(CommandBehavior.Default); while (sqlReader.Read()) { BmSQLDataRow row = new BmSQLDataRow(); for (int i = 0; i < sqlReader.FieldCount; i++) { row.ColValues.Add(sqlReader.GetValue(i).ToString()); row.ColTypes.Add(sqlReader.GetFieldType(i)); row.ColNames.Add(table.Columns[i].ColName); } lsData.Add(row); } } catch (Exception ex) { Debug.WriteLine(ex.ToString()); } finally { if (sqlReader != null && !sqlReader.IsClosed) { sqlReader.Close(); } } return(lsData); }
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("============================"); } }