/// <summary>
/// 获取所有关系
/// </summary>
/// <param name="chileName">null则查询所有表</param>
/// <returns></returns>
public List <TableRelationAttribute> GetRelation(DataBaseOperate oper, DBInfo info, IEnumerable <string> childNames)
{
List <TableRelationAttribute> lst = new List <TableRelationAttribute>();
using (BatchAction ba = oper.StarBatchAction())
{
OleDbConnection conn = oper.Connection as OleDbConnection;
oper.ConnectDataBase();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
foreach (DataRow dr in dt.Rows)
{
TableRelationAttribute tinfo = new TableRelationAttribute();
tinfo.CreateName();
tinfo.SourceTable = dr["FK_TABLE_NAME"] as string;
tinfo.SourceName = dr["FK_COLUMN_NAME"] as string;
tinfo.TargetTable = dr["PK_TABLE_NAME"] as string;
tinfo.TargetName = dr["PK_COLUMN_NAME"] as string;
tinfo.IsParent = true;
lst.Add(tinfo);
tinfo = new TableRelationAttribute();
tinfo.CreateName();
tinfo.SourceTable = dr["PK_TABLE_NAME"] as string;
tinfo.SourceName = dr["PK_COLUMN_NAME"] as string;
tinfo.TargetTable = dr["FK_TABLE_NAME"] as string;
tinfo.TargetName = dr["FK_COLUMN_NAME"] as string;
tinfo.IsParent = false;
lst.Add(tinfo);
}
}
return(lst);
}
private void btnTest_Click(object sender, EventArgs e)
{
try
{
if (!FillInfo())
{
return;
}
DBInfo db = _info.CreateDBInfo();
using (DataBaseOperate oper = db.CreateOperate())
{
oper.ConnectDataBase();
MessageBox.Show("测试成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("连接错误:" + ex.ToString(), "连接数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//private static string _sqlTables = "SELECT [name],[xtype] FROM [sysobjects] Where [xtype] in ('U','V') and [name] not in('dtproperties','sysdiagrams') ORDER BY [xtype],[crdate] desc";
/// <summary>
/// 获取所有用户表
/// </summary>
/// <returns></returns>
public virtual List <DBTableInfo> GetAllTableName(DataBaseOperate oper, DBInfo info)
{
ParamList lstParam = new ParamList();
//DataBaseOperate oper = info.DefaultOperate;
List <DBTableInfo> lstName = new List <DBTableInfo>();
using (BatchAction ba = oper.StarBatchAction())
{
OleDbConnection conn = oper.Connection as OleDbConnection;
oper.ConnectDataBase();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in dt.Rows)
{
DBTableInfo tableInfo = new DBTableInfo();
if (row.IsNull("TABLE_NAME"))
{
continue;
}
tableInfo.Name = row["TABLE_NAME"].ToString();
tableInfo.IsView = false;
lstName.Add(tableInfo);
}
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "VIEW" });
foreach (DataRow row in dt.Rows)
{
DBTableInfo tableInfo = new DBTableInfo();
if (row.IsNull("TABLE_NAME"))
{
continue;
}
tableInfo.Name = row["TABLE_NAME"].ToString();
tableInfo.IsView = true;
lstName.Add(tableInfo);
}
}
return(lstName);
}
/// <summary>
/// 获取表信息
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="tableNames"></param>
/// <returns></returns>
public List <DBTableInfo> GetTablesInfo(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
Dictionary <string, DBTableInfo> dicTables = new Dictionary <string, DBTableInfo>();
List <DBTableInfo> lst = new List <DBTableInfo>();
using (BatchAction ba = oper.StarBatchAction())
{
OleDbConnection conn = oper.Connection as OleDbConnection;
oper.ConnectDataBase();
DataTable dtpk = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null); //主键
Dictionary <string, bool> dicPk = new Dictionary <string, bool>();
foreach (DataRow row in dtpk.Rows)
{
string key = row["TABLE_NAME"].ToString() + ":" + row["COLUMN_NAME"].ToString();
dicPk[key.ToLower()] = true;
}
foreach (string tableName in tableNames)
{
DBTableInfo tableinfo = new DBTableInfo();
tableinfo.Params = new List <EntityParam>();
tableinfo.RelationItems = new List <TableRelationAttribute>();
tableinfo.IsView = false;
DataTable dtStr = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, tableName, "TABLE" });
if (dtStr.Rows.Count <= 0)
{
dtStr = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, tableName, "VIEW" });
tableinfo.IsView = true;
}
if (dtStr.Rows.Count <= 0)
{
continue;
}
tableinfo.Name = dtStr.Rows[0]["TABLE_NAME"] as string;
tableinfo.Description = dtStr.Rows[0]["DESCRIPTION"] as string;
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
//获取表结构
DataTable dtData = null;
string sql = "select * from [" + tableName + "]";
using (OleDbCommand comm = new OleDbCommand(sql, conn))
{
using (OleDbDataReader dr = comm.ExecuteReader(CommandBehavior.KeyInfo))
{
dtData = dr.GetSchemaTable();
}
}
DataView dv = dt.DefaultView;
dv.Sort = "ORDINAL_POSITION asc";
foreach (DataRowView row in dv)
{
FillParam(tableinfo, row.Row, dicPk, dtData);
}
lst.Add(tableinfo);
}
}
foreach (DBTableInfo table in lst)
{
dicTables[table.Name] = table;
}
List <TableRelationAttribute> lstRelation = GetRelation(oper, info, tableNames);
FillRelation(dicTables, lstRelation);
return(lst);
}