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