/// <summary>
/// Analyzes the table.
/// </summary>
/// <param name="conn">The connection.</param>
/// <exception cref="System.Exception">Failed to parse the reference string for the field ` + f.Name + '.</exception>
public void AnalyzeTable(SqlConnection conn)
{
this.fields.Clear();
this.columns.Clear();
this.primaryKeys.Clear();
this.rowCount = 0;
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "EXEC sp_help @objname = @table";
SqlParameter tableName = new SqlParameter("@table", SqlDbType.VarChar, 776);
tableName.Value = this.TableName;
cmd.Parameters.Add(tableName);
cmd.Prepare();
using (SqlDataReader results = cmd.ExecuteReader()) {
results.NextResult();
while (results.Read()) {
Field f = new Field((string)results["Column_name"]);
Column c = new Column((string)results["Column_name"]);
switch ((string)results["Type"]) {
case "int":
f.Type = Field.FieldType.Number;
f.DataType = SqlDbType.Int;
f.AddValidator(new Validators.NumValidator());
c.Type = Column.ColumnType.Int;
break;
case "numeric":
f.Type = Field.FieldType.Number;
f.DataType = SqlDbType.Decimal;
f.Precision = byte.Parse(((string)results["Prec"]).Trim());
f.Scale = byte.Parse(((string)results["Scale"]).Trim());
f.AddValidator(new Validators.DecValidator());
c.Type = Column.ColumnType.Numeric;
break;
case "text":
f.Type = Field.FieldType.TextArea;
f.DataType = SqlDbType.Text;
c.Type = Column.ColumnType.Text;
break;
case "date":
f.Type = Field.FieldType.Date;
f.DataType = SqlDbType.Date;
f.AddValidator(new Validators.DateValidator());
c.Type = Column.ColumnType.Date;
break;
case "datetime":
f.Type = Field.FieldType.DateTime;
f.DataType = SqlDbType.DateTime;
c.Type = Column.ColumnType.DateTime;
break;
case "varbinary":
f.Type = Field.FieldType.File;
f.DataType = SqlDbType.VarBinary;
c.Type = Column.ColumnType.File;
break;
case "varchar":
default:
f.Type = Field.FieldType.Text;
f.DataType = SqlDbType.VarChar;
c.Type = Column.ColumnType.Text;
break;
}
f.MaxLength = (int)results["Length"];
f.Required = ((string)results["Nullable"] == "no");
if (f.Required) f.AddValidator(new Validators.NotNullValidator());
this.fields.Add((string)results["Column_name"], f);
this.columns.Add((string)results["Column_name"], c);
}
results.NextResult();
while (results.Read()) {
if (this.fields.ContainsKey((string)results["Identity"])) {
this.fields[(string)results["Identity"]].ReadOnly = true;
}
}
results.NextResult();
results.NextResult();
results.NextResult();
results.NextResult();
while (results.Read()) {
if ((string)results["constraint_type"] == "FOREIGN KEY") {
Field f = this.fields[(string)results["constraint_keys"]];
f.Type = Field.FieldType.Select;
if (results.Read()) {
Regex r = new Regex(@"\.(\w+) \((\w+)\)$");
Match m = r.Match((string)results["constraint_keys"]);
if (m.Groups.Count == 3) {
f.ForeignTable = m.Groups[1].Captures[0].ToString();
f.ForeignKey = m.Groups[2].Captures[0].ToString();
f.FetchOptions(conn);
} else {
throw new Exception("Failed to parse the reference string for the field `" + f.Name + "'.");
}
} else {
throw new Exception("Failed to parse the foreign key for the field `" + f.Name + "'.");
}
} else if ((string)results["constraint_type"] == "PRIMARY KEY" || (string)results["constraint_type"] == "PRIMARY KEY (clustered)") {
string[] keys = ((string)results["constraint_keys"]).Split(',');
foreach (string key in keys) {
string trimmed = key.Trim();
if (this.fields.ContainsKey(trimmed)) {
this.fields[trimmed].Required = true;
}
if (this.columns.ContainsKey(trimmed)) {
this.columns[trimmed].PrimaryKey = true;
}
this.primaryKeys.Add(trimmed);
foreach (string col in this.columns.Keys) {
if (this.columns[col].Type == Column.ColumnType.File) {
this.columns[col].AddPrimaryKey(this.columns[trimmed]);
}
}
}
}
}
}
}
/// <summary>
/// Adds the primary key.
/// </summary>
/// <param name="col">The column.</param>
/// <exception cref="System.InvalidOperationException"></exception>
public void AddPrimaryKey(Column col)
{
if (col.PrimaryKey) {
this.primaryKeys.Add(col.Name, col);
} else {
throw new InvalidOperationException();
}
}