// DefineRowsetColumns takes two lines like the below as input:
// spid blocked waittype
// --------- ------------ ---------------
// and parses this to identify column names ("spid", "blocked", "waittype"), and the character
// width of each column. For each column in the rowset header a Column object of the appropriate
// type is instantiated and added to the Columns collection.
public virtual void DefineRowsetColumns(string HeaderLines, string ColumnNames)
{
try
{
string[] SplitStrings;
int i = 0;
int extrachar = 0; // used to handle spaces as the first chars in the line
// First add any implied rowset-level columns to the column list (these won't show up in the
// text rowset's column headers).
AddRowsetColumns();
// osql/sqlcmd may add line numbers to the output, which prevents the column names
// from lining up with the ----'s in the column header. For example, the output may
// look like this:
//
// 1> 2> 1> 2> 3> 4> 5> 6> databasename dbid
// -------------------- ------
// master 1
// AdventureWorks2000 7
//
// Detect and remove these line numbers so that the header + column names line up:
//
// databasename dbid
// -------------------- ------
// master 1
// AdventureWorks2000 7
if (ColumnNames.Substring(0, 3).Equals("1> "))
{
ColumnNames = ColumnNames.Substring(ColumnNames.IndexOf(" ") + 1);
}
// Break apart the rowset header strings to define the actual row-level column metadata.
SplitStrings = HeaderLines.Split(new char[] { ' ' });
foreach (String s in SplitStrings)
{
// See if the current "column" is actually just an empty space. If so we'll add the
// space's width to the next column in the rowset. osql & isql both insert a single
// space at the beginning of each row.
if (0 == s.Length)
{
extrachar++;
continue;
}
Column newcol = null;
// Get the new column's length and name.
int NewColumnLength = s.Length + extrachar;
int NegativeColumnLenght = 0;
string NewColumnName = ColumnNames.Substring(i);
if (NewColumnName.Length > NewColumnLength)
{
NewColumnName = NewColumnName.Substring(0, NewColumnLength);
}
NewColumnName = NewColumnName.Trim();
// See if we have metadata for this column in the KnownColumns collection.
foreach (Column c in KnownColumns)
{
if (NewColumnName == c.Name)
{
newcol = c;
// Set column length to width observed in this raw text data file (rowset).
newcol.Length = NewColumnLength;
//Description on handling VARCHAR(MAX)
//if c.SqlColumnLength is -1 (meaning we want (MAX)) then don't need to do anything because it comes from the TextRowset.xml file already
//and it gets copied into newcol.SqlColumnLength
// Use data file rowset col length for SQL col length, unless a longer length
// has been specified in rowset metadata (TextRowsets.xml)).
//Else the column definition is preserved based on TextRowset.xml
if ((c.SqlColumnLength != Column.SQL_MAX_LENGTH) && (newcol.SqlColumnLength <= newcol.Length))
{
newcol.SqlColumnLength = NewColumnLength;
}
break;
}
}
// Default to simple varchar if we didn't find the column's name in the rowset's KnownColumns list.
if (null == newcol)
{
newcol = new VarCharColumn(NewColumnName, NewColumnLength, NewColumnLength, false);
}
// Add new column to rowset Columns collection.
this.Columns.Add(newcol);
i += NewColumnLength + 1; // Move past the column (& space) in the text row containing column names.
extrachar = 0; // Reset extrachar counter.
}
}
catch (Exception e)
{
ErrorDialog ed = new ErrorDialog(e, true, this.logger);
ed.Handle();
}
return;
}