public ColumnParamsCollection GetColumns(string databaseName, string tableName, bool isView)
{
this._qryExec = true;
try
{
string tblNm, usrNm;
rsDbSql.ParseSchemaAndTableName(tableName, out tblNm, out usrNm);
this.CheckConnectionReady();
ColumnParamsCollection cols = new ColumnParamsCollection(null);
string qry = string.Format("SELECT sc.name, st.name, sc.colorder, sc.length, sc.colstat, sc.isnullable, so.uid FROM {0}.sys.syscolumns sc INNER JOIN {0}.sys.sysobjects so ON sc.id = so.id AND so.id = (SELECT so2.id FROM {0}.sys.sysobjects so2 INNER JOIN {0}.sys.schemas su2 ON su2.schema_id = so2.uid AND su2.name = '{2}' WHERE so2.name = '{1}' AND so2.xtype=N'{3}') INNER JOIN {0}.dbo.systypes st ON st.xtype = sc.xtype ORDER BY sc.colorder", databaseName, tblNm, usrNm, (!isView) ? "U" : "V");
using (SqlCommand cmd = new SqlCommand(qry, this.DbConnection))
using (SqlDataReader rdr = cmd.ExecuteReader())
while (rdr.Read())
{
if (rdr.GetValue(1).ToString() == "sysname")
// I don't know exactly what this is, but the query actually returns an "extra" record for each column, with a datatype of "sysname"
continue;
ColumnParams cp = new ColumnParams(); //rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), int.Parse(rdr.GetValue(3).ToString()));
cp.ColumnName = rdr.GetValue(0).ToString();
SqlDbType colType = SqlDbType.Variant;
try { colType = (SqlDbType)Enum.Parse(typeof(SqlDbType), rdr.GetValue(1).ToString(), true); }
catch { colType = SqlDbType.Variant; }
cp.DataType = colType;
int fldSz;
if (!int.TryParse(rdr.GetValue(3).ToString(), out fldSz))
fldSz = -1;
cp.FieldSize = fldSz;
cp.IsIdentity = (rdr.GetValue(4).ToString() == "1");
cp.IsNullable = (rdr.GetValue(5).ToString() == "1");
cp.SetOrdinal(int.Parse(rdr.GetValue(2).ToString()));
string colKeyBase = string.Format("{0}.{1}.{2}.{3}", databaseName, usrNm, tblNm, rdr.GetValue(0));
string colKey = colKeyBase;
int colKeyCnt = 1;
while (cols.ContainsKey(colKey))
colKey = colKeyBase + (colKeyCnt++).ToString().PadLeft(2, '0');
cols.Add(cp, colKey);
}
return cols;
}
catch
{ throw; }
finally
{
this._qryExec = false;
}
}
private void PopulateColumns()
{
if (this._cols == null)
{
this._cols = new ColumnParamsCollection();
}
else
{
this._cols.Clear();
}
if (this.TargetDirection == DataTargetDirection.Source)
{
if (this.DataTargetType == DataTargetType.Flat_File)
{
if (string.IsNullOrEmpty(this.pan02cboFlatSrcColDelim.Text) || string.IsNullOrEmpty(this.pan02cboFlatSrcRowDelim.Text) || !File.Exists(this.pan02txtFlatSrcFileName.Text))
{
return;
}
string[] pcs = null;
switch (this.pan02drpFlatSrcFormat.SelectedIndex)
{
case 0: // Delimited
string
cDelim = string.Empty,
rDelim = string.Empty;
switch (this.pan02cboFlatSrcColDelim.Text)
{
case "{CR}{LF}": cDelim = "\r\n"; break;
case "{CR}": cDelim = "\r"; break;
case "{LF}": cDelim = "\n"; break;
case "Semicolon {;}": cDelim = "\n"; break;
case "Colon {:}": cDelim = ":"; break;
case "Comma {,}": cDelim = ","; break;
case "Tab {t}": cDelim = "\t"; break;
case "Vertical Bar {|}": cDelim = "|"; break;
default: cDelim = this.pan02lblFlatSrcColDelim.Text; break;
}
switch (this.pan02cboFlatSrcRowDelim.Text)
{
case "{CR}{LF}": rDelim = "\r\n"; break;
case "{CR}": rDelim = "\r"; break;
case "{LF}": rDelim = "\n"; break;
case "Semicolon {;}": rDelim = "\n"; break;
case "Colon {:}": rDelim = ":"; break;
case "Comma {,}": rDelim = ","; break;
case "Tab {t}": rDelim = "\t"; break;
case "Vertical Bar {|}": rDelim = "|"; break;
default: rDelim = this.pan02lblFlatSrcColDelim.Text; break;
}
string sTxtQual = this.pan02txtFlatSrcTextQual.Text;
if (sTxtQual.ToLower() == "<none>")
{
sTxtQual = string.Empty;
}
using (FileStream fs = new FileStream(this.pan02txtFlatSrcFileName.Text, FileMode.Open, FileAccess.Read))
using (RainstormStudios.IO.DelimitedTextReader rdr = new RainstormStudios.IO.DelimitedTextReader(fs, rDelim, cDelim, sTxtQual, Encoding.Default))
for (int i = 0; i < this.pan02numFlatSrc.Value + 1; i++)
{
pcs = rdr.ReadRow();
}
break;
case 1: // Fixed width
break;
case 2: // Ragged right
break;
}
// If the first row doesn't contain the column names, then
// just populate the array with generic "ColumnXXX" values.
if (!this.pan02chkFlatSrcColNameFirstRow.Checked)
{
for (int i = 0; i < pcs.Length; i++)
{
pcs[i] = "Column" + i.ToString().PadLeft(pcs.Length.ToString().Length, '0');
}
}
// Create the entries in the ColumnParams collection.
for (int i = 0; i < pcs.Length; i++)
{
this._cols.Add(new RainstormStudios.Data.ColumnParams(pcs[i], typeof(System.String), 0, i));
}
if (pcs == null)
{
return;
}
}
else
{
string sqlQry = "SELECT TOP 1 * FROM " + this.pan02drpSqlDatabaseList.Text;
using (rsDb db = rsDb.GetDbObject(AdoProviderType.Auto, this.GetConnectionString(), sqlQry))
{
using (DataSet ds = db.GetData())
{
if (ds.Tables.Count > 0)
{
foreach (DataColumn dc in ds.Tables[0].Columns)
{
this._cols.Add(new ColumnParams(dc.ColumnName, dc.DataType, dc.MaxLength, dc.Ordinal));
}
}
}
}
}
}
else
{
if (this.DataTargetType == DataTargetType.Flat_File && File.Exists(this.pan02txtFlatSrcFileName.Text))
{
}
else if (this.DataTargetType == DataTargetType.SQL_Native_Client)
{
}
}
// If we're working with a flat data type, then populate the columns in the wizard.
if (this.DataTargetType == DataTargetType.Flat_File)
{
this.pan02lstFlatSrcColPrev.BeginUpdate();
this.pan02lstFlatSrcColPrev.Items.Clear();
for (int c = 0; c < this._cols.Count; c++)
{
this.pan02lstFlatSrcColPrev.Items.Add(new ListViewItem(new string[] { this._cols[c].ColumnName, this._cols[c].DataType.ToString(), this._cols[c].FieldSize.ToString() }));
}
this.pan02lstFlatSrcColPrev.EndUpdate();
}
}
