public void ShouldMapDBTypesToDotNetTypes() { var columnDetail = new ColumnDetail("Id", "DATE", 10, 8, 0, 10, true); Assert.AreEqual(typeof(DateTime), columnDetail.MappedType); Assert.AreEqual("DATE", columnDetail.DataType); Assert.AreEqual("Id", columnDetail.ColumnName); }
public void AddColumn(string name, Type type, pnColumnOption opt) { ColumnDetail cd = new ColumnDetail(); cd.fName = name; cd.fOption = opt; cd.fType = type; fColumns.Add(cd); }
public static List <ColumnDetail> GetDdlStatementColumns(string str) { var columnDetails = new List <ColumnDetail>(); try { var pFrom = str.IndexOf("(", StringComparison.Ordinal) + 1; var pTo = str.LastIndexOf(")", StringComparison.Ordinal); string result = null; if (pFrom >= 0 && str.Length > pFrom) { result = str.Substring(pFrom, pTo - pFrom); } var ddlColumns = result.Split("," + Environment.NewLine, StringSplitOptions.RemoveEmptyEntries); var columns = ddlColumns.Select(ddlColumn => ddlColumn.Trim()).ToList(); foreach (var column in columns) { pTo = column.IndexOf(" ", StringComparison.Ordinal); var columnName = column.Substring(0, pTo).Trim(); var columnDataType = column.Substring(pTo).Trim(); var columnDetail = new ColumnDetail { DataType = columnDataType, Name = columnName }; columnDetails.Add(columnDetail); } } catch (Exception e) { Logger.LogError(e, $"Error extracting column details for ddl statement: {str}, please check log for more details."); } return(columnDetails); }
public void LoadDataFromTabDelimitedFile(string fileName) { Dictionary <ColumnDetail, int> detailToIndex = new Dictionary <ColumnDetail, int>(); ColumnDetail[] indexToDetail = null; this.OutcomeNameToOutcomeIndex = new Dictionary <string, int>(); var outcomeIndexToOutcomeName = new List <string>(); List <Dictionary <int, bool> > children = new List <Dictionary <int, bool> >(); this.Allergens = AllergensInFile; using (var reader = new StreamReader(fileName)) { // Read the header string str = reader.ReadLine(); string[] columns = str.Split('\t'); indexToDetail = new ColumnDetail[columns.Length]; ColumnDetail detail; for (int i = 0; i < columns.Length; i++) { string col = columns[i]; string[] parts = col.Split('_'); if (parts.Length == 1) { short outcomeIndex = (short)this.OutcomeNameToOutcomeIndex.Count; // Take this as an outcome detail = new ColumnDetail { test = -1, allergen = -1, year = -1, outcome = outcomeIndex }; this.OutcomeNameToOutcomeIndex[parts[0]] = outcomeIndex; outcomeIndexToOutcomeName.Add(parts[0]); } else { int testX = Tests.FindIndex(t => t == parts[0]); int allergenX = AllergensInFile.FindIndex(a => a == parts[1]); int yearX = Years.FindIndex(y => y == parts[2]); if (testX < 0 || allergenX < 0 || yearX < 0) { throw new FileFormatException("Header not as expected"); } detail = new ColumnDetail { test = (short)testX, allergen = (short)allergenX, year = (short)yearX, outcome = -1 }; } detailToIndex[detail] = i; indexToDetail[i] = detail; } // Now read in each child while ((str = reader.ReadLine()) != null) { Dictionary <int, bool> child = new Dictionary <int, bool>(); columns = str.Split('\t'); for (int i = 0; i < columns.Length; i++) { int yesno; if (int.TryParse(columns[i], out yesno)) { child[i] = yesno > 0; } } children.Add(child); } } // Now fill in the data structure. int numTests = Tests.Count; int numAllergens = Allergens.Count; int numYears = Years.Count; int numChildren = children.Count; this.OutcomeIndexToOutcomeName = outcomeIndexToOutcomeName.ToArray(); int numOutcomes = this.OutcomeIndexToOutcomeName.Length; int childX = 0; this.SkinTestData = Util.ArrayInit(numYears, y => Util.ArrayInit(numChildren, n => Util.ArrayInit(numAllergens, a => (int?)null))); this.IgeTestData = Util.ArrayInit(numYears, y => Util.ArrayInit(numChildren, n => Util.ArrayInit(numAllergens, a => (int?)null))); this.Outcomes = Util.ArrayInit(numOutcomes, y => Util.ArrayInit(numChildren, n => (int?)null)); this.IndicesToIncludedChildren = Util.ArrayInit(numChildren, i => i); foreach (var child in children) { foreach (var kvp in child) { var detail = indexToDetail[kvp.Key]; if (detail.outcome >= 0) { this.Outcomes[detail.outcome][childX] = kvp.Value ? 1 : 0; } else if (detail.test == 0) { this.SkinTestData[detail.year][childX][detail.allergen] = kvp.Value ? 1 : 0; } else { this.IgeTestData[detail.year][childX][detail.allergen] = kvp.Value ? 1 : 0; } } childX++; } setStatisticsFromData(); }
private void QueryColumns(string tbl) { columnDetails = new List <ColumnDetail>(); string query = "SELECT isc.TABLE_NAME, isc.COLUMN_NAME, isc.ORDINAL_POSITION, isc.DATA_TYPE, " + "isc.CHARACTER_MAXIMUM_LENGTH, isc.NUMERIC_PRECISION, isc.NUMERIC_SCALE, isc.DATETIME_PRECISION, c.is_nullable, c.is_identity " + "FROM INFORMATION_SCHEMA.COLUMNS isc inner join sys.all_columns c on isc.COLUMN_NAME = c.name " + "inner join sys.all_objects o on c.object_id = o.object_id " + "WHERE o.name = '" + tbl.Trim() + "' and isc.TABLE_NAME = '" + tbl.Trim() + "' and (o.type = 'U' or o.type='V') "; SqlConnection cnn; SqlCommand command; SqlDataReader dataReader; string connectionString = @"Data Source=" + ServerTextBox.Text.Trim() + ";Initial Catalog=" + databaseTextbox.Text.Trim() + ";User ID=" + usernameTextbox.Text.Trim() + ";Password="******"NULL") { cd.TABLE_NAME = Convert.ToString(dataReader.GetValue(0)); } } catch (Exception ex) { } try { if (Convert.ToString(dataReader.GetValue(1)) != "NULL") { cd.COLUMN_NAME = Convert.ToString(dataReader.GetValue(1)); } } catch (Exception ex) { } try { if (Convert.ToString(dataReader.GetValue(2)) != "NULL") { cd.ORDINAL_POSITION = Convert.ToString(dataReader.GetValue(2)); } } catch (Exception ex) { } try { if (Convert.ToString(dataReader.GetValue(3)) != "NULL") { cd.DATA_TYPE = Convert.ToString(dataReader.GetValue(3)); } } catch (Exception ex) { } try { if (Convert.ToString(dataReader.GetValue(4)) != "NULL") { cd.CHARACTER_MAXIMUM_LENGTH = Convert.ToInt32(dataReader.GetValue(4)); } } catch (Exception ex) { } try { if (Convert.ToString(dataReader.GetValue(5)) != "NULL") { cd.CHARACTER_MAXIMUM_LENGTH = Convert.ToInt32(dataReader.GetValue(5)); } } catch (Exception ex) { } try { if (Convert.ToString(dataReader.GetValue(6)) != "NULL") { cd.NUMERIC_SCALE = Convert.ToInt32(dataReader.GetValue(6)); } } catch (Exception ex) { } try { if (Convert.ToString(dataReader.GetValue(7)) != "NULL") { cd.DATETIME_PRECISION = Convert.ToInt32(dataReader.GetValue(7)); } } catch (Exception ex) { } cd.is_nullable = Convert.ToBoolean(dataReader.GetValue(8)); cd.is_identity = Convert.ToBoolean(dataReader.GetValue(9)); columnDetails.Add(cd); } dataReader.Close(); command.Dispose(); cnn.Close(); generateClass(tbl); }