/// <summary>新建字段</summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ToolStripMenuItem_fieldAdd_Click(object sender, EventArgs e)
{
TreeNode node = this.treeView1.SelectedNode;
_number++;
TreeNode fieldNode = new TreeNode("字段" + _number.ToString());
fieldNode.Name = "field" + _number.ToString();
node.Nodes.Add(fieldNode);
fieldNode.ContextMenuStrip = contextMenuStrip_field;
this.treeView1.SelectedNode = fieldNode;
LibField field = new LibField();
field.Name = fieldNode.Name;
field.DisplayName = "";
field.AliasName = string.Empty;
DataTableStruct tableStruct = GetDTStruct(node.Name);
if (tableStruct.Fields == null)
{
tableStruct.Fields = new LibCollection <LibField>();
}
tableStruct.Fields.Add(field);
TreeNodeSelected(fieldNode);
}
/// <summary>
/// 选入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, System.EventArgs e)
{
LibRelateField relateField = null;
string text = string.Empty;
string tablenm = string.Empty;
string fldnm = string.Empty;
string[] array;
foreach (var item in this.listBox1.SelectedItems)
{
this.listBox2.Items.Add(item);
array = item.ToString().Split(SysConstManage.Point);
tablenm = array[0];
LibDataTableStruct tableStruct = _tableStructs.FirstOrDefault(i => i.Name == tablenm);
LibField field = tableStruct.Fields.FindFirst("Name", array[1]);
relateField = new LibRelateField();
relateField.ID = Guid.NewGuid().ToString();
relateField.FieldNm = field.Name;
relateField.DisplayNm = field.DisplayName;
relateField.AliasName = field.AliasName;
relateField.FieldType = field.FieldType;
relateField.FromTableIndex = tableStruct.TableIndex;
if (_relateFields.FirstOrDefault(i => i.FromTableIndex == tableStruct.TableIndex && i.FieldNm == field.Name) == null)
{
_relateFields.Add(relateField);
}
RelateFieldProperty property = (RelateFieldProperty)this.splitContainer1.Panel2.Controls[relateField.ID];
if (property == null)
{
property = new RelateFieldProperty(relateField.ID);
property.Dock = DockStyle.Fill;
property.SetPropertyValue(relateField, null);
_rfieldPropertylist.Add(property);
this.splitContainer1.Panel2.Controls.Add(property);
}
}
for (int i = 0; i < this.listBox2.Items.Count; i++)
{
this.listBox1.Items.Remove(this.listBox2.Items[i]);
}
}
private void DoCreateField(string fieldnm, string displaynm, LibSysField sysfd, LibTreeNode currentNode, LibDataTableStruct currentTBStruct)
{
LibTreeNode fieldNode = new LibTreeNode();
fieldNode.NodeId = Guid.NewGuid().ToString();
fieldNode.Name = sysfd != null ? sysfd.Name : fieldnm;
fieldNode.Text = sysfd != null ? sysfd.DisplayName : displaynm;
fieldNode.NodeType = NodeType.Field;
currentNode.Nodes.Add(fieldNode);
DefFieldProperty fieldP = new DefFieldProperty(fieldNode.NodeId);
fieldP.Dock = DockStyle.Fill;
this._fieldPropertylst.Add(fieldP);
this.splitContainer1.Panel2.Controls.Add(fieldP);
LibField field = new LibField();
field.ID = fieldNode.NodeId;
field.Name = fieldNode.Name;
field.DisplayName = fieldNode.Text;
if (sysfd != null)
{
field.FieldType = sysfd.FieldType;
field.FieldLength = sysfd.FieldLength;
}
else
{
field.FieldType = LibFieldType.String;
field.FieldLength = 50;
}
field.IsActive = true;
field.AllowNull = true;
field.SysField = sysfd != null;
currentTBStruct.Fields.Add(field);
fieldP.SetPropertyValue(field, fieldNode);
}
/// <summary>获取字段实体对象</summary>
/// <param name="tableStructName">表名</param>
/// <param name="fieldName">字段名</param>
/// <returns></returns>
private LibField GetField(string tableStructName, string fieldName)
{
LibField field = null;
DataTableStruct tableStruct = GetDTStruct(tableStructName);
if (tableStruct != null)
{
foreach (LibField item in tableStruct.Fields)
{
if (string.Compare(item.Name, fieldName, true) == 0)
{
field = item;
}
}
}
#region
//foreach (DefineTable deftb in _currentDS.DefTables)
//{
// foreach (DataTableStruct tablestruct in deftb.TableStruct)
// {
// foreach (LibField item in tablestruct.Fields)
// {
// if (string.Compare(item.Name, fieldName, true) == 0)
// {
// field = item;
// isFind = true;
// break;
// }
// }
// if (isFind)
// break;
// }
// if (isFind)
// break;
//}
#endregion
return(field);
}
public Form1()
{
InitializeComponent();
//this.tabControl1.Appearance = TabAppearance.FlatButtons;
#region
DBModelOperation dbmodel = new DBModelOperation();
DataSource ds = new DataSource();
ds.DataSourceName = "datasourcename";
ds.DSID = 111111111;
LibField field = new LibField();
field.Name = "字段名称1";
field.AliasName = "字段别名1";
field.DisplayName = "字段显示名称1";
LibField field2 = new LibField();
field2.Name = "字段名称2";
field2.AliasName = "字段别名2";
field2.DisplayName = "字段显示名称2";
DataTableStruct tablestruct = new DataTableStruct();
tablestruct.Name = "数据表结构";
tablestruct.Fields = new LibCollection <LibField>();
tablestruct.Fields.Add(field);
tablestruct.Fields.Add(field2);
DefineTable deftb = new DefineTable();
deftb.TableName = "zyy";
deftb.ID = 123456;
deftb.TableStruct = new LibCollection <DataTableStruct>();
deftb.TableStruct.Add(tablestruct);
//DefineTable deftb2 = new DefineTable();
//deftb2.TableName = "Ylr";
//deftb2.ID = "22222";
//PropertyInfo[] propertis = deftb.GetType().GetProperties();
//LibXmlAttributeAttribute attr = propertis[0].GetCustomAttributes(typeof(LibXmlAttributeAttribute), true)[0] as LibXmlAttributeAttribute;
ds.DefTables = new LibCollection <DefineTable>();
ds.DefTables.Add(deftb);
//ds.DefTables.Add(deftb2);
ds.DefTables.Guid = "................";
SerializerUtils serial = new SerializerUtils();
string str = serial.XMLSerialize(ds);
//SerializerUtils.xmlserialzaition(ds, string.Format(@"{0}\Models\DataSource\{1}", Environment.CurrentDirectory, "text.xml"));
//XMLOperation xml = new XMLOperation(string.Format(@"{0}\Models\DataSource\{1}", Environment.CurrentDirectory, "text.xml"));
//ILibXMLNodeRead noderead = xml.NodeRead("/DataSource/DefTables/DefineTable");
//while (!noderead.EOF)
//{
// string test= noderead.InnerText;
// string TableName = noderead.Attributions["TableName"];
// noderead.ReadNext();
//}
//if (true) {
// ExceptionManager.ThrowError("sdfsd");
//}
//else {
// int a = 0;
// a = 88;
//}
#endregion
//DAL.DBHelp.DataAccess.testsql();
//DAL.DBHelp.DataAccess.testsql();
}
public bool IsUpdated(LibField f)
{
return(updateAttr[(int)f]);
}
private void DoGetSQL(StringBuilder builder, string tableNm, LibDataSource ds, WhereObject where, string[] sumaryfields, bool page = false, bool IsJoinRelateTable = true, bool IsJoinFromSourceField = true, bool IsRpt = false)
{
List <LibDataTableStruct> list = new List <LibDataTableStruct>();
StringBuilder joinstr = new StringBuilder();
StringBuilder joinfield = null;
StringBuilder allfields = new StringBuilder();
Dictionary <string, string> tablealiasmdic = new Dictionary <string, string>();
foreach (LibDefineTable item in ds.DefTables)
{
if (item.TableStruct == null)
{
continue;
}
list.AddRange(item.TableStruct.ToArray());
}
var tb = list.FirstOrDefault(i => i.Name == tableNm && i.Ignore);
char tbaliasnm = LibSysUtils.ToCharByTableIndex(tb.TableIndex);
#region 组织要查询表的字段
if (builder.Length == ResFactory.ResManager.SQLSelect.Length)
{
foreach (LibField f in tb.Fields)
{
if (!f.IsActive)
{
continue;
}
if (allfields.Length > 0)
{
allfields.Append(SysConstManage.Comma);
}
allfields.AppendFormat(" {0}.{1}", tbaliasnm, f.Name);
}
#region 日志列
if (allfields.Length > 0)
{
allfields.Append(SysConstManage.Comma);
}
allfields.AppendFormat(" {0}.{1}", tbaliasnm, SysConstManage.Sdp_LogId);
#endregion
}
#endregion
if (tb != null)
{
if (IsJoinRelateTable)
{
var relatetbs = list.Where(i => i.JoinTableIndex == tb.TableIndex && i.TableIndex != tb.TableIndex && i.Ignore).ToList();
List <int> tbindexs = null;
//char tbaliasnm2 = tbaliasnm;
while (relatetbs != null && relatetbs.Count > 0)
{
tbindexs = new List <int>();
foreach (var jointb in relatetbs)
{
tbindexs.Add(jointb.TableIndex);
#region 组织joinstr语句
joinstr.AppendFormat(" {0} {1} {2} {3} ",
ResFactory.ResManager.SQLLeftJoin,
jointb.Name,
LibSysUtils.ToCharByTableIndex(jointb.TableIndex),
//(char)(jointb.TableIndex+65),
ResFactory.ResManager.SQLOn);
joinfield = new StringBuilder();
foreach (var relatfield in jointb.JoinFields)
{
if (joinfield.Length > 0)
{
joinfield.Append(ResFactory.ResManager.SQLAnd);;
}
LibField libfd = jointb.Fields.FindFirst("Name", relatfield);
//if (string.IsNullOrEmpty(libfd.RelatePrimarykey))
joinfield.AppendFormat(" {0}.{1}={2}.{3} ",
LibSysUtils.ToCharByTableIndex(jointb.JoinTableIndex),
string.IsNullOrEmpty(libfd.RelatePrimarykey) ? relatfield : libfd.RelatePrimarykey,
LibSysUtils.ToCharByTableIndex(jointb.TableIndex),
relatfield);
//else
}
joinstr.Append(joinfield.ToString());
joinstr.AppendLine();
#endregion
#region 取关联表的字段
if (builder.Length == ResFactory.ResManager.SQLSelect.Length)
{
foreach (LibField f2 in jointb.Fields)
{
if (!f2.IsActive || jointb.JoinFields.Contains(f2.Name))
{
continue;
}
if (allfields.ToString().ToUpper().Contains(string.Format(".{0}", f2.Name.ToUpper())) && string.IsNullOrEmpty(f2.AliasName))
{
allfields.AppendFormat("{0}{1}.{2} as {3}", SysConstManage.Comma, LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name, string.Format("{0}{2}{1}", LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name, SysConstManage.Underline));
}
else
{
if (!string.IsNullOrEmpty(f2.AliasName))
{
allfields.AppendFormat("{0}{1}.{2} as {3}", SysConstManage.Comma, LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name, f2.AliasName);
}
else
{
allfields.AppendFormat("{0}{1}.{2}", SysConstManage.Comma, LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name);
}
}
}
}
#endregion
}
relatetbs = list.Where(i => tbindexs.Contains(i.JoinTableIndex) && i.TableIndex != i.JoinTableIndex && i.Ignore).ToList();
//tbaliasnm2=
}
}
#region 字段上的来源表。
var relatefields = tb.Fields.ToArray().Where(i => i.SourceField != null);
List <string> vals = new List <string>();
foreach (LibField f in relatefields)
{
foreach (LibFromSourceField fromfield in f.SourceField)
{
string key = string.Format("{0}{1}{2}", f.Name, fromfield.FromDataSource, fromfield.FromTableIndex);
string tbnm = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(fromfield.FromTableIndex));
if (!tablealiasmdic.ContainsKey(key))
{
//vals.Add(tbnm);
int cout = vals.Where(i => i == tbnm).Count();
if (cout > 0)
{
tbnm = string.Format("{0}{1}", tbnm, cout);
//tablealiasmdic.Add(key, string.Format("{0}", tbnm));
}
vals.Add(tbnm);
tablealiasmdic.Add(key, tbnm);
//tablealiasmdic.Add(key,string.Format( tbnm);
//tablealiasmdic[tbnm]++;
//tbnm = string.Format("{0}{1}", tbnm, tablealiasmdic[tbnm]+1);
}
else
{
////int cout = vals.Where(i => i == tbnm).Count();
tbnm = tablealiasmdic[key];
//tablealiasmdic.Add(tbnm, 0);
}
#region 组织joinstr语句
joinstr.AppendFormat(" {0} {1} {2} {3} ",
ResFactory.ResManager.SQLLeftJoin,
fromfield.FromStructTableNm,
tbnm,
//(char)(jointb.TableIndex+65),
ResFactory.ResManager.SQLOn);
joinfield = new StringBuilder();
if (joinfield.Length > 0)
{
joinfield.Append(ResFactory.ResManager.SQLAnd);
}
joinfield.AppendFormat(" {0}.{1}={2}.{3} ",
tbaliasnm,
f.Name,
tbnm,
fromfield.FromFieldNm);
#region 处理JoinOnCondition
if (!string.IsNullOrEmpty(fromfield.JoinOnCondition))
{
if (where == null)
{
where = new WhereObject();
}
DoJoinOnCondition(fromfield.JoinOnCondition, where);
}
#endregion
joinstr.Append(joinfield.ToString());
joinstr.AppendLine();
if (IsJoinFromSourceField)
{
#region 取来源表所在数据源的 与来源表关联的表。
LibDataSource fromSouceDS = ModelManager.GetDataSource(fromfield.FromDataSource);
if (fromSouceDS != null)
{
List <LibDataTableStruct> list2 = new List <LibDataTableStruct>();
foreach (LibDefineTable def in fromSouceDS.DefTables)
{
if (def.TableStruct == null)
{
continue;
}
list2.AddRange(def.TableStruct.ToArray());
}
var relatetbs = list2.Where(i => i.JoinTableIndex == fromfield.FromTableIndex && i.TableIndex != fromfield.FromTableIndex && i.Ignore).ToList();
List <int> tbindexs2 = null;
while (relatetbs != null && relatetbs.Count > 0)
{
tbindexs2 = new List <int>();
foreach (var jointb in relatetbs)
{
string key2 = string.Format("{0}{1}{2}", f.Name, fromfield.FromDataSource, jointb.TableIndex);
tbindexs2.Add(jointb.TableIndex);
string tbnm2 = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(jointb.TableIndex));
if (!tablealiasmdic.ContainsKey(key2))
{
//vals.Add(tbnm2);
int cout = vals.Where(i => i == tbnm2).Count();
if (cout > 0)
{
tbnm2 = string.Format("{0}{1}", tbnm2, cout);
//tablealiasmdic.Add(key, string.Format("{0}", tbnm));
}
tablealiasmdic.Add(key, tbnm2);
//tablealiasmdic.Add(key2, tbnm2);
//tablealiasmdic[tbnm2]++;
//tbnm2 = string.Format("{0}{1}", tbnm2, tablealiasmdic[tbnm2]+1);
}
else
{
tbnm2 = tablealiasmdic[key2];
//tablealiasmdic.Add(tbnm2, 0);
}
joinstr.AppendFormat(" {0} {1} {2} {3} ",
ResFactory.ResManager.SQLLeftJoin,
jointb.Name,
tbnm2,
//(char)(jointb.TableIndex+65),
ResFactory.ResManager.SQLOn);
joinfield = new StringBuilder();
foreach (var relatfield in jointb.JoinFields)
{
if (joinfield.Length > 0)
{
joinfield.Append(ResFactory.ResManager.SQLAnd);;
}
LibField libfd = jointb.Fields.FindFirst("Name", relatfield);
joinfield.AppendFormat(" {0}.{1}={2}.{3} ",
string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(jointb.JoinTableIndex)),
string.IsNullOrEmpty(libfd.RelatePrimarykey) ? relatfield : libfd.RelatePrimarykey,
string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(jointb.TableIndex)),
relatfield);
}
joinstr.Append(joinfield.ToString());
joinstr.AppendLine();
}
relatetbs = list2.Where(i => tbindexs2.Contains(i.JoinTableIndex) && i.TableIndex != i.JoinTableIndex && i.Ignore).ToList();
//tbaliasnm2=
}
}
#endregion
}
if (builder.Length == ResFactory.ResManager.SQLSelect.Length || IsRpt)
{
if (fromfield.RelateFieldNm != null)
{
foreach (LibRelateField relatef in fromfield.RelateFieldNm)
{
if (!IsJoinFromSourceField && relatef.FromTableIndex != fromfield.FromTableIndex)
{
continue;
}
key = string.Format("{0}{1}{2}", f.Name, fromfield.FromDataSource, relatef.FromTableIndex);
//tbnm = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(relatef.FromTableIndex));
//if (IsJoinFromSourceField && relatef.FromTableIndex != fromfield.FromTableIndex)
//{
tbnm = tablealiasmdic[key];
//}
if (allfields.Length > 0)
{
allfields.Append(SysConstManage.Comma);
}
if (!string.IsNullOrEmpty(relatef.AliasName))
{
allfields.AppendFormat("{0}.{1} as {2}",
//SysConstManage.Comma,
tbnm,
relatef.FieldNm,
relatef.AliasName);
}
else
{
allfields.AppendFormat("{0}.{1}",
//SysConstManage.Comma,
tbnm,
relatef.FieldNm);
}
}
}
}
#endregion
}
}
#endregion
if (builder.Length == ResFactory.ResManager.SQLSelect.Length || IsRpt)
{
if (builder.Length != ResFactory.ResManager.SQLSelect.Length)
{
builder.Append(SysConstManage.Comma);
}
builder.Append(allfields.ToString());
}
if (page) //分页
{
StringBuilder orderstr = new StringBuilder();
foreach (string key in tb.PrimaryKey)
{
if (orderstr.Length > 0)
{
orderstr.Append(SysConstManage.Comma);
}
orderstr.AppendFormat("{0}.{1}", LibSysUtils.ToCharByTableIndex(tb.TableIndex), key);
}
builder.AppendFormat(",ROW_NUMBER()OVER(order by {0}) as rownumber ", orderstr.ToString());
}
//if (sumaryfields != null) //汇总字段
//{
// foreach (string field in sumaryfields)
// {
// builder.AppendFormat(",SUM({0})OVER() as {1}{2}", field,SysConstManage.sdp_summaryprefix,field .Substring(field.IndexOf(".")+1));
// }
//}
builder.AppendFormat(" {0}", ResFactory.ResManager.SQLFrom);
builder.AppendFormat(" {0} {1}", tableNm, tbaliasnm);
builder.Append(joinstr.ToString());
}
}
private void SetFieldNode(LibField field, TreeNode node)
{
node.Name = field.Name;
node.Text = field.DisplayName;
}
