/// <summary>
/// 获取指定表的字段信息
/// </summary>
/// <param name="serverAddress">服务器地址</param>
/// <param name="dbName">要获取信息的数据库名称</param>
/// <param name="tabName">要获取信息的表名称</param>
/// <param name="userName">数据库登录用户名</param>
/// <param name="passWord">密码</param>
/// <returns></returns>
public static List <StObjField> GetTableFieldInfo(string serverAddress, string dbName, string tabName, string userName, string passWord)
{
List <StObjField> res = new List <StObjField>();
SqlDataReader dr = null;
try
{
con = new SqlConnection(GetLineStr(serverAddress, dbName, userName, passWord));
con.Open();
string sql = GetDBFieldInfoSQL(false, tabName);
SqlCommand cmd = new SqlCommand(sql, con);
dr = cmd.ExecuteReader();
StObjField obj = null;
object o = null;
string[] isS = { "u", "v" };
string[] isI = { "u" };
string[] isU = { "u" };
string[] isD = { "u" };
StObjectType stobjtype = default(StObjectType);
while (dr.Read())
{
stobjtype = default(StObjectType);
obj = new StObjField();
obj.ObjName = dr["objname"].ToString().Replace("(", "_").Replace(")", "_");
obj.ColID = Convert.ToInt32(dr["colid"]);
obj.ColName = dr["name"].ToString();
obj.ColTypeName = dr["typename"].ToString();
o = dr["defaultvalue"];
obj.DefaultValue = o is DBNull ? null : o.ToString();
o = dr["describe"];
obj.Describe = o is DBNull ? null : o.ToString().Replace("\\r\\n", "").Replace("\r\n", "");
obj.IsComputed = Convert.ToBoolean(dr["iscomputed"]);
obj.IsIdentity = Convert.ToBoolean(dr["isidentity"]);
obj.IsNull = Convert.ToBoolean(dr["isnullable"]);
obj.IsOutParam = Convert.ToBoolean(dr["isoutparam"]);
obj.IsPrimaryKey = Convert.ToBoolean(dr["isprimarykey"]);
obj.MaxLength = Convert.ToInt32(dr["length"]);
obj.ObjID = Convert.ToInt32(dr["objid"]);
o = dr["prec"];
obj.OldLength = o is DBNull ? -1 : Convert.ToInt32(o);
o = dr["parentTabID"];
obj.PrientTableID = o is DBNull ? -1 : Convert.ToInt32(o);
o = dr["parentTabName"];
obj.PrientTableName = o is DBNull ? null : o.ToString();
o = dr["scale"];
obj.Scale = o is DBNull ? -1 : Convert.ToInt32(o);
if (Enum.TryParse <StObjectType>(dr["scale"] + "", false, out stobjtype))
{
obj.ObjType = stobjtype;
}
string objtype = obj.ObjType.ToString().ToLower();
obj.IsObjDelete = isD.Contains(objtype);
obj.IsObjInsert = isI.Contains(objtype);
obj.IsObjSelect = isS.Contains(objtype);
obj.IsObjUpdate = isU.Contains(objtype);
obj.IsColDelete = obj.IsObjDelete;
obj.IsColInsert = obj.IsObjInsert && !obj.IsPrimaryKey && !obj.IsIdentity;
obj.IsColSelect = obj.IsObjSelect;
obj.IsColUpdate = obj.IsObjUpdate && !obj.IsPrimaryKey && !obj.IsIdentity && !obj.IsComputed;
res.Add(obj);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if (con != null)
{
con.Close();
}
}
return(res);
}
/// <summary>
/// 获取指定数据库的所有表的字段信息
/// </summary>
/// <param name="serverAddress">服务器地址</param>
/// <param name="dbName">要获取信息的数据库名称</param>
/// <param name="userName">数据库登录用户名</param>
/// <param name="passWord">密码</param>
/// <returns></returns>
public static Dictionary <string, StTable> GetAllTableFieldInfo2(string serverAddress, string dbName, string userName, string passWord, List <string> types, string reStr = "_", params string[] replyStr)
{
Dictionary <string, StTable> res = new Dictionary <string, StTable>();
SqlCommand cmd = null;
try
{
string sql = GetLineStr(serverAddress, dbName, userName, passWord);
con = new SqlConnection(sql);
con.Open();
sql = GetDBFieldInfoSQL2(true, "", types);
cmd = new SqlCommand(sql, con);
DataSet ds = new DataSet();
using (DbDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
}
StTable obj = null;
object o = null;
bool e = replyStr != null && replyStr.Length > 0;
string[] isS = { "u", "v" };
string[] isI = { "u" };
string[] isU = { "u" };
string[] isD = { "u" };
StObjectType stobjtype = default(StObjectType);
StField field = new StField();
DataTable dt1 = ds.Tables[0];
foreach (DataRow dr in dt1.Rows)
{
stobjtype = default(StObjectType);
field = new StField();
string tabName = dr["objname"].ToString();
if (!res.ContainsKey(tabName))
{
obj = new StTable()
{
Fields = new List <StField>()
};
obj.Name = dr["objname"].ToString();
if (e)
{
foreach (var item in replyStr)
{
obj.Name = obj.Name.Replace(item, reStr);
}
}
o = dr["objdescribe"];
obj.Describe = o is DBNull ? null : o.ToString();
obj.ID = Convert.ToInt32(dr["objid"]);
string objtype = obj.Type.ToString().ToLower();
if (Enum.TryParse <StObjectType>(dr["scale"] + "", false, out stobjtype))
{
obj.Type = stobjtype;
}
obj.IsDelete = isD.Contains(objtype);
obj.IsInsert = isI.Contains(objtype);
obj.IsSelect = isS.Contains(objtype);
obj.IsUpdate = isU.Contains(objtype);
}
field.ColID = Convert.ToInt32(dr["colid"]);
field.ColName = dr["name"].ToString();
field.ColTypeName = dr["typename"].ToString();
o = dr["defaultvalue"];
field.DefaultValue = o is DBNull ? null : o.ToString();
o = dr["describe"];
field.Describe = o is DBNull ? null : o.ToString().Replace("\r\n", "");
field.IsComputed = Convert.ToBoolean(dr["iscomputed"]);
field.IsIdentity = Convert.ToBoolean(dr["isidentity"]);
field.IsNull = Convert.ToBoolean(dr["isnullable"]);
field.IsOutParam = Convert.ToBoolean(dr["isoutparam"]);
field.IsPrimaryKey = Convert.ToBoolean(dr["isprimarykey"]);
field.MaxLength = Convert.ToInt32(dr["length"]);
o = dr["prec"];
field.OldLength = o is DBNull ? -1 : Convert.ToInt32(o);
o = dr["parentTabID"];
field.PrientTableID = o is DBNull ? -1 : Convert.ToInt32(o);
o = dr["parentTabName"];
field.PrientTableName = o is DBNull ? null : o.ToString();
o = dr["scale"];
if (!(o is DBNull))
{
field.Scale = Convert.ToInt32(o);
}
o = dr["identity_seed"];//种子
if (!(o is DBNull))
{
field.IDENTITY_SEED = Convert.ToInt32(o);
}
o = dr["identity_increase"];//增量
if (!(o is DBNull))
{
field.IDENTITY_INCREASE = Convert.ToInt32(o);
}
o = dr["unique_text"];//增量
if (!(o is DBNull))
{
field.UniqueText = o is DBNull ? null : o.ToString();
}
field.IsColDelete = obj.IsDelete;
field.IsColInsert = obj.IsInsert && !field.IsPrimaryKey && !field.IsIdentity;
field.IsColSelect = obj.IsSelect;
field.IsColUpdate = obj.IsUpdate && !field.IsPrimaryKey && !field.IsIdentity && !field.IsComputed;
if (!res.ContainsKey(obj.Name))
{
res.Add(obj.Name, obj);
}
res[obj.Name].Fields.Add(field);
}
#region Check约束
if (ds.Tables.Count > 1)
{
dt1 = ds.Tables[1];
StObject sto = null;
foreach (DataRow dr in dt1.Rows)
{
sto = new StObject();
sto.Type = StObjectType.C;
o = dr["colid"];
sto.ColID = o is DBNull ? -1 : Convert.ToInt32(o.ToString());
o = dr["definition"];
sto.CreateText = o is DBNull ? "" : o.ToString();
o = dr["name"];
sto.Name = o is DBNull ? "" : o.ToString();
o = dr["tabid"];
sto.ObjID = Convert.ToInt32(o.ToString());
o = dr["tabname"];
sto.ObjName = o is DBNull ? null : o.ToString();
StField sf = res[sto.ObjName].Fields.Where(i => i.ColID == sto.ColID).FirstOrDefault();
if (sf != null)
{
//该约束是针对列的
sf.UniqueText = sto.CreateText;
}
if (res[sto.ObjName].StObjects == null)
{
res[sto.ObjName].StObjects = new List <StObject>();
}
res[sto.ObjName].StObjects.Add(sto);
}
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cmd != null)
{
cmd.Clone();
}
if (con != null)
{
con.Close();
}
}
return(res);
}
/// <summary>
/// 获取指定数据库的所有表的字段信息
/// </summary>
/// <param name="serverAddress">服务器地址</param>
/// <param name="dbName">要获取信息的数据库名称</param>
/// <param name="userName">数据库登录用户名</param>
/// <param name="passWord">密码</param>
/// <returns></returns>
public static Dictionary <string, List <StObjField> > GetAllTableFieldInfo(string serverAddress, string dbName, string userName, string passWord, List <string> types, DateTime?sTime, string reStr = "_", params string[] replyStr)
{
Dictionary <string, List <StObjField> > res = new Dictionary <string, List <StObjField> >(StringComparer.OrdinalIgnoreCase);
SqlDataReader dr = null;
try
{
string sql = GetLineStr(serverAddress, dbName, userName, passWord);
con = new SqlConnection(sql);
con.Open();
sql = GetDBFieldInfoSQL(true, "", types, sTime);
SqlCommand cmd = new SqlCommand(sql, con);
dr = cmd.ExecuteReader();
StObjField obj = null;
object o = null;
bool e = replyStr != null && replyStr.Length > 0;
string[] isS = { "u", "v" };
string[] isI = { "u" };
string[] isU = { "u" };
string[] isD = { "u" };
StObjectType stobjtype = default(StObjectType);
while (dr.Read())
{
stobjtype = default(StObjectType);
obj = new StObjField();
obj.ObjName = dr["objname"].ToString();
if (e)
{
foreach (var item in replyStr)
{
obj.ObjName = obj.ObjName.Replace(item, reStr);
}
}
if (obj.ObjName == "vi_AccessIO")
{
int a = 0;
}
obj.ColID = Convert.ToInt32(dr["colid"]);
o = dr["objdescribe"];
obj.ObjDescribe = o is DBNull ? null : o.ToString();
obj.ColName = dr["name"].ToString();
obj.CreTime = Convert.ToDateTime(dr["creTime"]);
obj.UpdTime = Convert.ToDateTime(dr["updTime"]);
obj.ColTypeName = dr["typename"].ToString();
o = dr["defaultvalue"];
obj.DefaultValue = o is DBNull ? null : o.ToString();
o = dr["defaultid"];
if (!(o is DBNull))
{
obj.DefaultID = Convert.ToInt32(o);
}
o = dr["describe"];
obj.Describe = o is DBNull ? null : o.ToString().Replace("\r\n", "");
obj.IsComputed = Convert.ToBoolean(dr["iscomputed"]);
o = dr["computed_text"];
obj.ComputedText = o is DBNull ? null : o.ToString();
obj.IsIdentity = Convert.ToBoolean(dr["isidentity"]);
obj.IsNull = Convert.ToBoolean(dr["isnullable"]);
obj.IsOutParam = Convert.ToBoolean(dr["isoutparam"]);
obj.IsPrimaryKey = Convert.ToBoolean(dr["isprimarykey"]);
obj.MaxLength = Convert.ToInt32(dr["length"]);
obj.ObjID = Convert.ToInt32(dr["objid"]);
o = dr["prec"];
obj.OldLength = o is DBNull ? -1 : Convert.ToInt32(o);
o = dr["parentTabID"];
obj.PrientTableID = o is DBNull ? -1 : Convert.ToInt32(o);
o = dr["parentTabName"];
obj.PrientTableName = o is DBNull ? null : o.ToString();
o = dr["parColId"];
obj.ParentColID = o is DBNull ? -1 : Convert.ToInt32(o);
o = dr["parColName"];
obj.ParentColName = o is DBNull ? null : o.ToString();
o = dr["scale"];
if (!(o is DBNull))
{
obj.Scale = Convert.ToInt32(o);
}
if (Enum.TryParse <StObjectType>(dr["objtype"] + "", false, out stobjtype))
{
obj.ObjType = stobjtype;
}
obj.IsUnique = dr["isunion"] + "" == "1";
o = dr["identity_seed"];//种子
if (!(o is DBNull))
{
obj.IDENTITY_SEED = Convert.ToInt32(o);
}
o = dr["identity_increase"];//增量
if (!(o is DBNull))
{
obj.IDENTITY_INCREASE = Convert.ToInt32(o);
}
obj.IsUnique = dr["ischeck"] + "" == "1";
o = dr["check_text"];//增量
if (!(o is DBNull))
{
obj.CheckText = o is DBNull ? null : o.ToString();
}
string objtype = obj.ObjType.ToString().ToLower();
obj.IsObjDelete = isD.Contains(objtype);
obj.IsObjInsert = isI.Contains(objtype);
obj.IsObjSelect = isS.Contains(objtype);
obj.IsObjUpdate = isU.Contains(objtype);
obj.IsColDelete = obj.IsObjDelete;
obj.IsColInsert = obj.IsObjInsert && !obj.IsPrimaryKey && !obj.IsIdentity;
obj.IsColSelect = obj.IsObjSelect;
obj.IsColUpdate = obj.IsObjUpdate && !obj.IsPrimaryKey && !obj.IsIdentity && !obj.IsComputed;
if (!res.ContainsKey(obj.ObjName))
{
res.Add(obj.ObjName, new List <StObjField>());
}
res[obj.ObjName].Add(obj);
if (GetDBTableInfohander != null)
{
GetDBTableInfohander(res.Count);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if (con != null)
{
con.Close();
}
}
return(res);
}
