/// <summary>
/// 取得所有表构架
/// </summary>
/// <returns></returns>
public override XTable[] GetTables()
{
List <XTable> list = null;
try
{
DataTable dt = GetSchema("Tables", null);
//一次性把所有的表说明查出来
DataSet ds = Query(DescriptionSql);
DataTable DescriptionTable = ds == null || ds.Tables == null || ds.Tables.Count < 1 ? null : ds.Tables[0];
list = new List <XTable>();
if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
if (dr["TABLE_NAME"].ToString() != "dtproperties" &&
dr["TABLE_NAME"].ToString() != "sysconstraints" &&
dr["TABLE_NAME"].ToString() != "syssegments" &&
(dr["TABLE_TYPE"].ToString() == "BASE TABLE" || dr["TABLE_TYPE"].ToString() == "VIEW"))
{
XTable xt = new XTable();
xt.ID = list.Count + 1;
xt.Name = dr["TABLE_NAME"].ToString();
DataRow[] drs = DescriptionTable == null ? null : DescriptionTable.Select("n='" + xt.Name + "'");
xt.Readme = drs == null || drs.Length < 1 ? "" : drs[0][1].ToString();
xt.IsView = dr["TABLE_TYPE"].ToString() == "VIEW";
xt.Fields = GetFields(xt);
list.Add(xt);
}
}
}
}
catch (Exception ex)
{
throw new Exception("取得所有表构架出错！", ex);
}
return(list == null ? null : list.ToArray());
}
/// <summary>
/// 取得指定表的所有主键构架
/// </summary>
/// <param name="xt"></param>
/// <returns></returns>
protected List <String> GetPrimaryKeys(XTable xt)
{
if (PrimaryKeys == null)
{
return(null);
}
try
{
DataRow[] drs = PrimaryKeys.Select("TABLE_NAME='" + xt.Name + @"'");
if (drs == null || drs.Length < 1)
{
return(null);
}
List <String> list = new List <string>();
foreach (DataRow dr in drs)
{
list.Add(dr["COLUMN_NAME"] == DBNull.Value ? "" : dr["COLUMN_NAME"].ToString());
}
return(list);
}
catch { return(null); }
}
/// <summary>
/// 取得指定表的所有列构架
/// </summary>
/// <param name="xt"></param>
/// <returns></returns>
protected virtual XField[] GetFields(XTable xt)
{
DataTable dt = GetSchema("Columns", new String[] { null, null, xt.Name });
List <XField> list = new List <XField>();
DataRow[] drs = dt.Select("", "ORDINAL_POSITION");
List <String> pks = GetPrimaryKeys(xt);
List <Dictionary <String, String> > fks = GetForeignKeys(xt);
foreach (DataRow dr in drs)
{
XField xf = new XField();
xf.ID = Int32.Parse(dr["ORDINAL_POSITION"].ToString());
xf.Name = dr["COLUMN_NAME"].ToString();
xf.FieldType = FieldTypeToClassType(dr["DATA_TYPE"].ToString());
xf.Identity = dr["DATA_TYPE"].ToString() == "3" && (dr["COLUMN_FLAGS"].ToString() == "16" || dr["COLUMN_FLAGS"].ToString() == "90");
xf.PrimaryKey = pks != null && pks.Contains(xf.Name);
xf.ForeignKey = false;
xf.ForeignTableName = "";
xf.ForeignTablePrimaryName = "";
if (fks != null)
{
foreach (Dictionary <String, String> dic in fks)
{
if (dic["FK_COLUMN_NAME"] == xf.Name)
{
xf.ForeignKey = true;
xf.ForeignTableName = dic["PK_TABLE_NAME"];
xf.ForeignTablePrimaryName = dic["PK_COLUMN_NAME"];
break;
}
}
}
if (xf.FieldType == "Int32" || xf.FieldType == "Double")
{
xf.Length = dr["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Int32.Parse(dr["NUMERIC_PRECISION"].ToString());
xf.NumOfByte = 0;
xf.Digit = dr["NUMERIC_SCALE"] == DBNull.Value ? 0 : Int32.Parse(dr["NUMERIC_SCALE"].ToString());
}
else if (xf.FieldType == "DateTime")
{
xf.Length = dr["DATETIME_PRECISION"] == DBNull.Value ? 0 : Int32.Parse(dr["DATETIME_PRECISION"].ToString());
xf.NumOfByte = 0;
xf.Digit = 0;
}
else
{
if (dr["DATA_TYPE"].ToString() == "130" && dr["COLUMN_FLAGS"].ToString() == "234") //备注类型
{
xf.Length = Int32.MaxValue;
xf.NumOfByte = Int32.MaxValue;
}
else
{
xf.Length = dr["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value ? 0 : Int32.Parse(dr["CHARACTER_MAXIMUM_LENGTH"].ToString());
xf.NumOfByte = dr["CHARACTER_OCTET_LENGTH"] == DBNull.Value ? 0 : Int32.Parse(dr["CHARACTER_OCTET_LENGTH"].ToString());
}
xf.Digit = 0;
}
try
{
xf.Nullable = Boolean.Parse(dr["IS_NULLABLE"].ToString());
}
catch
{
xf.Nullable = dr["IS_NULLABLE"].ToString() == "YES";
}
try
{
xf.Default = dr["COLUMN_HASDEFAULT"].ToString() == "False" ? "" : dr["COLUMN_DEFAULT"].ToString();
}
catch
{
xf.Default = dr["COLUMN_DEFAULT"].ToString();
}
try
{
xf.Readme = dr["DESCRIPTION"] == DBNull.Value ? "" : dr["DESCRIPTION"].ToString();
}
catch
{
xf.Readme = "";
}
list.Add(xf);
}
return(list.ToArray());
}
//private DataTable allfields;
/// <summary>
/// 取得指定表的所有列构架
/// </summary>
/// <param name="xt"></param>
/// <returns></returns>
protected override XField[] GetFields(XTable xt)
{
//if (allfields == null) allfields = Query(SchemaSql).Tables[0];
DataTable allfields = Query(String.Format(SchemaSql, xt.Name)).Tables[0];
if (allfields == null)
{
return(base.GetFields(xt));
}
List <XField> list = new List <XField>();
//DataRow[] drs = allfields.Select("表名='" + xt.Name + "'", "字段序号");
//if (drs == null || drs.Length < 1) return base.GetFields(xt);
if (allfields.Rows == null || allfields.Rows.Count < 1)
{
return(base.GetFields(xt));
}
List <String> pks = GetPrimaryKeys(xt);
List <Dictionary <String, String> > fks = GetForeignKeys(xt);
foreach (DataRow dr in allfields.Rows)
{
XField xf = new XField();
xf.ID = Int32.Parse(dr["字段序号"].ToString());
xf.Name = dr["字段名"].ToString();
xf.FieldType = FieldTypeToClassType(dr["类型"].ToString());
xf.Identity = Boolean.Parse(dr["标识"].ToString());
//xf.PrimaryKey = pks != null && pks.Contains(xf.Name);
xf.PrimaryKey = Boolean.Parse(dr["主键"].ToString());
xf.ForeignKey = false;
xf.ForeignTableName = "";
xf.ForeignTablePrimaryName = "";
if (fks != null)
{
foreach (Dictionary <String, String> dic in fks)
{
if (dic["FK_COLUMN_NAME"] == xf.Name)
{
xf.ForeignKey = true;
xf.ForeignTableName = dic["PK_TABLE_NAME"];
xf.ForeignTablePrimaryName = dic["PK_COLUMN_NAME"];
break;
}
}
}
xf.Length = Int32.Parse(dr["长度"].ToString());
xf.NumOfByte = Int32.Parse(dr["占用字节数"].ToString());
xf.Digit = Int32.Parse(dr["小数位数"].ToString());
xf.Nullable = Boolean.Parse(dr["允许空"].ToString());
xf.Default = dr["默认值"].ToString();
xf.Readme = dr["字段说明"].ToString();
list.Add(xf);
}
return(list.ToArray());
}