public ColumnData(string name, SqlDbType type, int typeLength = 100, bool allowNull = true, bool isPrimaryKey = false, bool identity = false, ForeignKey foreignKey = null, bool isUnique = false) { Name = name; Type = type; TypeLength = typeLength; IsPrimaryKey = isPrimaryKey; IsUnique = isUnique; AllowNull = allowNull; Identity = identity; ForeignKey = foreignKey; }
public TableData GetTable(string tableName) { if (this.IsPhysical == false) { throw new Exception("Cannot open database"); } List <ColumnData> columns = new List <ColumnData>(); bool isPrimary = false; bool isNull; ForeignKey foreignKey = null; DataTable tableColumnInfo; DataTable tableColumns; using (SqlConnection connection = ConnectToDataBase(this.Name, this.DataSource)) { try { string strCommandGetColumnsInfo = String.Format("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_NAME = '{0}'", tableName); SqlCommand commandGetColumnsInfo = new SqlCommand(strCommandGetColumnsInfo, connection); using (SqlDataReader readerColumnsInfo = commandGetColumnsInfo.ExecuteReader()) { tableColumnInfo = new DataTable(); tableColumnInfo.Load(readerColumnsInfo); } string strCommandGetColumns = String.Format("SELECT * FROM [{0}]", tableName); SqlCommand commandGetColumns = new SqlCommand(strCommandGetColumns, connection); using (SqlDataReader readerColumns = commandGetColumns.ExecuteReader()) { tableColumns = new DataTable(); tableColumns.Load(readerColumns); } var cols = tableColumns.Columns; for (int i = 0; i < tableColumnInfo.Rows.Count; i++) { string name = tableColumnInfo.Rows[i][0].ToString(); SqlDbType type = ConvertFromStr(tableColumnInfo.Rows[i][1].ToString()); int maxlength = (tableColumnInfo.Rows[i][2].ToString() != "") ? Int16.Parse(tableColumnInfo.Rows[i][2].ToString()) : 0; if (tableColumnInfo.Rows[i][3].ToString() == "NO") { isNull = false; } else { isNull = true; } bool identity = cols[i].AutoIncrement; bool isUnique = cols[i].Unique; string strCommandGetColumnKeys = String.Format("SELECT CONSTRAINT_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName, name); SqlCommand commandGetColumnsKeys = new SqlCommand(strCommandGetColumnKeys, connection); using (SqlDataReader readerColumnsKeys = commandGetColumnsKeys.ExecuteReader()) { while (readerColumnsKeys.Read()) { string constraintName = readerColumnsKeys[0].ToString(); string[] parts = constraintName.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries); if (parts[0] == "FK") { foreignKey = new ForeignKey(parts[2], parts[1]); } else if (parts[0] == "PK") { isPrimary = true; } } } columns.Add(new ColumnData(name, type, maxlength, isNull, isPrimary, identity, foreignKey, isUnique)); } } catch (Exception ex) { Console.WriteLine(ex.Message); } return(TableData.CreateNew(tableName, this, columns)); } }
public TableData GetTable(string tableName) { if (this.IsPhysical == false) throw new Exception("Cannot open database"); List<ColumnData> columns = new List<ColumnData>(); bool isPrimary = false; bool isNull; ForeignKey foreignKey = null; DataTable tableColumnInfo; DataTable tableColumns; using (SqlConnection connection = ConnectToDataBase(this.Name, this.DataSource)) { try { string strCommandGetColumnsInfo = String.Format("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_NAME = '{0}'", tableName); SqlCommand commandGetColumnsInfo = new SqlCommand(strCommandGetColumnsInfo, connection); using (SqlDataReader readerColumnsInfo = commandGetColumnsInfo.ExecuteReader()) { tableColumnInfo = new DataTable(); tableColumnInfo.Load(readerColumnsInfo); } string strCommandGetColumns = String.Format("SELECT * FROM [{0}]", tableName); SqlCommand commandGetColumns = new SqlCommand(strCommandGetColumns, connection); using (SqlDataReader readerColumns = commandGetColumns.ExecuteReader()) { tableColumns = new DataTable(); tableColumns.Load(readerColumns); } var cols = tableColumns.Columns; for (int i = 0; i < tableColumnInfo.Rows.Count; i++) { string name = tableColumnInfo.Rows[i][0].ToString(); SqlDbType type = ConvertFromStr(tableColumnInfo.Rows[i][1].ToString()); int maxlength = (tableColumnInfo.Rows[i][2].ToString() != "") ? Int16.Parse(tableColumnInfo.Rows[i][2].ToString()) : 0; if (tableColumnInfo.Rows[i][3].ToString() == "NO") isNull = false; else isNull = true; bool identity = cols[i].AutoIncrement; bool isUnique = cols[i].Unique; string strCommandGetColumnKeys = String.Format("SELECT CONSTRAINT_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName, name); SqlCommand commandGetColumnsKeys = new SqlCommand(strCommandGetColumnKeys, connection); using (SqlDataReader readerColumnsKeys = commandGetColumnsKeys.ExecuteReader()) { while (readerColumnsKeys.Read()) { string constraintName = readerColumnsKeys[0].ToString(); string[] parts = constraintName.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries); if (parts[0] == "FK") foreignKey = new ForeignKey(parts[2], parts[1]); else if (parts[0] == "PK") isPrimary = true; } } columns.Add(new ColumnData(name, type, maxlength, isNull, isPrimary, identity, foreignKey, isUnique)); } } catch (Exception ex) { Console.WriteLine(ex.Message); } return TableData.CreateNew(tableName, this, columns); } }