public static SqlScript DropScript(this iTable obj)
{
return(new SqlScript()
{
sql = $"DROP TABLE {obj.TableName()}"
});
}
public iImage NewImage(iTable header, string imagePath)
{
iImage column = new iImage();
column.SetImage(imagePath);
header.Columns.Add(column);
return(column);
}
public iColumn NewColumn(iTable header)
{
iColumn column = new iColumn();
column.Text = "New Column";
header.Columns.Add(column);
return(column);
}
public iTable NewTable(ArrayList parent)
{
//var header = (T)Activator.CreateInstance(typeof(T));
var tbl = new iTable();
parent.Add(tbl);
return(tbl);
}
public bool RemoveImage(iTable header, iImage col)
{
var colCount = header.Columns.Count;
header.Columns.Remove(col);
if (header.Columns.Count == colCount - 1)
{
return(true);
}
return(false);
}
/// <summary>
/// Set the table object by another smilar object
/// </summary>
/// <param name="obj"></param>
/// <param name="cloneobj"></param>
public static void SetByObj(this iTable obj, object cloneobj)
{
PropertyInfo[] pros = obj.GetType().GetProperties();
foreach (PropertyInfo p in cloneobj.GetType().GetProperties())
{
var v = from c in pros
where c.Name == p.Name && c.PropertyType == p.PropertyType
select c;
if (v.Count() > 0)
{
p.SetValue(obj, p.GetValue(cloneobj, null), null);
}
}
}
public bool RemoveTable(iTable table, object obj)
{
if (obj.GetType().Equals(typeof(ArrayList)))
{
ArrayList lst = obj as ArrayList;
var lstCount = lst.Count;
lst.Remove(table);
if (lst.Count == (lstCount - 1))
{
return(true);
}
}
return(false);
}
private static void updatesql(Type type, string paramchar, iTable obj, ref string set, ref string where, ref List <SqlParam> dbparams)
{
foreach (var item in type.GetPrimaryKeys())
{
if (string.IsNullOrEmpty(where))
{
where = $"{item.Name}={paramchar}{item.Name}";
}
else
{
where = $"{where} AND {item.Name}={paramchar}{item.Name}";
}
if (dbparams.Where(x => x.name == item.Name).Count() == 0)
{
dbparams.Add(new SqlParam(item.Name, item.GetValue(obj)));
}
}
foreach (var item in type.GetNonPrimaryKeys())
{
if (string.IsNullOrEmpty(set))
{
set = $"{item.Name}={paramchar}{item.Name}";
}
else
{
set = $"{set},{item.Name}={paramchar}{item.Name}";
}
if (item.GetValue(obj) == null)
{
dbparams.Add(new SqlParam(item.Name, DBNull.Value));
}
else
{
if (dbparams.Where(x => x.name == item.Name).Count() == 0)
{
if (item.PropertyType.IsEnum)
{
dbparams.Add(new SqlParam(item.Name, item.GetValue(obj).ToString()));
}
else
{
dbparams.Add(new SqlParam(item.Name, item.GetValue(obj)));
}
}
}
}
}
public static List <SqlScript> InsertSelectScript(this iTable obj, string paramchar, string randomidcharacter)
{
Type type = obj.GetType();
SqlScript sqlscript = new SqlScript();
string into = "";
string value = "";
string set = "";
string where = "";
updatesql(type, paramchar, obj, ref set, ref where, ref sqlscript.dbparameters);
insertsql(type, paramchar, randomidcharacter, obj, ref into, ref value, ref sqlscript.dbparameters);
if (type.Name.IndexOf('`') > 0)
{
sqlscript.sql = $@"
begin tran
UPDATE {type.Name.Remove(type.Name.IndexOf('`'))} SET {set} WHERE {where}
if @@rowcount = 0
begin
INSERT INTO {type.Name.Remove(type.Name.IndexOf('`'))} ({into}) VALUES ({value})
end
commit tran";
}
else
{
sqlscript.sql = $@"
begin tran
UPDATE {type.Name.Replace("`1", "")} SET {set} WHERE {where}
if @@rowcount = 0
begin
INSERT INTO {type.Name.Replace("`1", "")} ({into}) VALUES ({value})
end
commit tran";
}
return(new List <SqlScript>()
{
sqlscript
});
}
/// <summary>
/// Insert Data
/// </summary>
/// <param name="obj"></param>
/// <param name="paramchar"></param>
/// <param name="randomidcharacter"></param>
/// <returns></returns>
public static List <SqlScript> InsertScript(this iTable obj, string paramchar, string randomidcharacter, eInsertUpdateType inserttype = eInsertUpdateType.Normal)
{
Type type = obj.GetType();
SqlScript sqlscript = new SqlScript();
string into = "";
string value = "";
insertsql(type, paramchar, randomidcharacter, obj, ref into, ref value, ref sqlscript.dbparameters);
string insert = "INSERT";
switch (inserttype)
{
case eInsertUpdateType.Normal:
break;
case eInsertUpdateType.Replace:
insert += " OR REPLACE ";
break;
case eInsertUpdateType.Ignore:
insert += " OR IGNORE ";
break;
default:
break;
}
if (type.Name.IndexOf('`') > 0)
{
sqlscript.sql = $"{insert} INTO { type.Name.Remove(type.Name.IndexOf('`'))} ({into}) VALUES ({value})";
}
else
{
sqlscript.sql = $"{insert} INTO {type.Name.Replace("`1", "")} ({into}) VALUES ({value})";
}
// sqlscript.sql = $"INSERT INTO {type.Name.Replace("`1", "")} ({into}) VALUES ({value})";
// sqlscript.sql = $"INSERT INTO { type.Name.Remove(type.Name.IndexOf('`'))} ({into}) VALUES ({value})";
return(new List <SqlScript>()
{
sqlscript
});
}
/// <summary>
/// Update Data By PK
/// </summary>
/// <param name="obj"></param>
/// <param name="paramchar"></param>
/// <returns></returns>
public static List <SqlScript> UpdateByPKScript(this iTable obj, string paramchar)
{
Type type = obj.GetType();
SqlScript sqlscript = new SqlScript();
string set = "";
string where = "";
updatesql(type, paramchar, obj, ref set, ref where, ref sqlscript.dbparameters);
if (type.Name.IndexOf('`') > 0)
{
sqlscript.sql = $"UPDATE {type.Name.Remove(type.Name.IndexOf('`'))} SET {set} WHERE {where}";
}
else
{
sqlscript.sql = $"UPDATE {type.Name.Replace("`1", "")} SET {set} WHERE {where}";
}
return(new List <SqlScript>()
{
sqlscript
});
}
/// <summary>
/// Table 物件 --> SQL Script for select all
/// </summary>
/// <param name="obj"></param>
/// <param name="paramchar"></param>
/// <returns></returns>
public static SqlScript SelectAllScript(this iTable obj, string paramchar)
{
SqlScript dbsqlscript = new SqlScript();
string wheresql = "";
foreach (var item in typeof(object).GetProperties())
{
object provalue = item.GetValue(obj);
if (provalue == null)
{
continue;
}
wheresql = $"{item.Name}={paramchar}{item.Name}";
dbsqlscript.dbparameters.Add(new SqlParam(item.Name, provalue));
}
dbsqlscript.sql = $@"
SELECT *
FROM {obj.TableName()}
{wheresql}";
return(dbsqlscript);
}
/// <summary>
/// Table 物件 --> SQL Script for select by PK
/// </summary>
/// <param name="obj"></param>
/// <param name="paramchar"></param>
/// <returns></returns>
public static SqlScript SelectAllWhereByPKScript(this iTable obj, string paramchar)
{
SqlScript dbsqlscript = new SqlScript();
string PKWhere = "";
foreach (var item in obj.GetType().GetPrimaryKeys())
{
if (string.IsNullOrEmpty(PKWhere))
{
PKWhere = $"{item.Name}={paramchar}{item.Name}";
}
else
{
PKWhere = $"{PKWhere} AND {item.Name}={paramchar}{item.Name}";
}
dbsqlscript.dbparameters.Add(new SqlParam(name: item.Name, value: item.GetValue(obj)));
}
dbsqlscript.sql = $@"
SELECT *
FROM {obj.TableName()}
WHERE {PKWhere}";
return(dbsqlscript);
}
public void RemoveTableColumn(iTable header, iTable col)
{
header.Columns.Remove(col);
}
public void NewTableColumn(iTable header)
{
iTable column = new iTable();
header.Columns.Add(column);
}
void menuItem_Click(object sender, EventArgs e)
{
try
{
TreeNode selectedNod = treeView1.SelectedNode;
var tag = selectedNod.Tag;
ToolStripItem menuItem = (ToolStripItem)sender;
if (menuItem.Name == "AddTable")
{
if (tag != null)
{
if (tag.GetType().Equals(typeof(ArrayList)))
{
var parent = (ArrayList)tag;
var newtable = inv.NewTable(parent);
var newnod = selectedNod.Nodes.Add("Table");
newnod.Tag = newtable;
treeView1.SelectedNode = newnod;
newnod.EnsureVisible();
treeView1_NodeMouseClick(treeView1, new TreeNodeMouseClickEventArgs(newnod, MouseButtons.Left, 1, 0, 0));
return;
}
if (tag.GetType().Equals(typeof(iTable)))
{
var parent = (iTable)tag;
var newtable = inv.NewTable(parent.Columns);
var newnod = selectedNod.Nodes.Add("Table");
newnod.Tag = newtable;
treeView1.SelectedNode = newnod;
newnod.EnsureVisible();
treeView1_NodeMouseClick(treeView1, new TreeNodeMouseClickEventArgs(newnod, MouseButtons.Left, 1, 0, 0));
return;
}
}
}
if (menuItem.Name == "AddCellColumn")
{
iTable header = treeView1.SelectedNode.Tag as iTable;
if (header != null)
{
var col = inv.NewColumn(header);
var newnod = selectedNod.Nodes.Add(col.Text);
newnod.Tag = col;
treeView1.SelectedNode = newnod;
newnod.EnsureVisible();
treeView1_NodeMouseClick(treeView1, new TreeNodeMouseClickEventArgs(newnod, MouseButtons.Left, 1, 0, 0));
//LoadTree();
}
}
if (menuItem.Name == "AddCellImage")
{
iTable header = treeView1.SelectedNode.Tag as iTable;
if (header != null)
{
string filename = GetImageFile();
if (filename == "")
{
return;
}
var col = inv.NewImage(header, filename);
var newnod = selectedNod.Nodes.Add("Image");
newnod.Tag = col;
treeView1.SelectedNode = newnod;
newnod.EnsureVisible();
treeView1_NodeMouseClick(treeView1, new TreeNodeMouseClickEventArgs(newnod, MouseButtons.Left, 1, 0, 0));
//LoadTree();
}
}
if (menuItem.Name == "RemoveTable")
{
var currenNod = treeView1.SelectedNode;
iTable header = currenNod.Tag as iTable;
var parentNod = currenNod.Parent;
var parent = parentNod.Tag;
if (header != null)
{
if (parent.GetType().Equals(typeof(iTable)))
{
if (inv.RemoveTable(header, ((iTable)parent).Columns) == true)
{
currenNod.Remove();
}
}
else
{
if (inv.RemoveTable(header, parent) == true)
{
currenNod.Remove();
}
}
//LoadTree();
}
}
if (menuItem.Name == "RemoveCellColumn")
{
var currenNod = treeView1.SelectedNode;
iColumn headerColumn = currenNod.Tag as iColumn;
iTable header = currenNod.Parent.Tag as iTable;
if (header != null)
{
if (headerColumn != null)
{
if (inv.RemoveColumn(header, headerColumn) == true)
{
currenNod.Remove();
var newnod = treeView1.SelectedNode;
treeView1_NodeMouseClick(treeView1, new TreeNodeMouseClickEventArgs(newnod, MouseButtons.Left, 1, 0, 0));
//LoadTree();
}
}
}
}
if (menuItem.Name == "RemoveCellImage")
{
var currenNod = treeView1.SelectedNode;
iImage headerColumn = currenNod.Tag as iImage;
iTable header = currenNod.Parent.Tag as iTable;
if (header != null)
{
if (headerColumn != null)
{
if (inv.RemoveImage(header, headerColumn) == true)
{
currenNod.Remove();
var newnod = treeView1.SelectedNode;
treeView1_NodeMouseClick(treeView1, new TreeNodeMouseClickEventArgs(newnod, MouseButtons.Left, 1, 0, 0));
//LoadTree();
}
}
}
}
if (menuItem.Name == "Cut")
{
var currenNod = treeView1.SelectedNode;
if (currenNod.Tag == null)
{
CutItem = null; return;
}
;
var parrentNod = currenNod.Parent;
iTable parentTable = parrentNod.Tag as iTable;
if (parentTable == null)
{
return;
}
var ind = parentTable.Columns.IndexOf(currenNod.Tag);
CutItem = new CutObject()
{
CutNode = currenNod, CutObjIndex = ind, CutParentobj = parentTable
};
}
if (menuItem.Name == "Paste")
{
var currenNod = treeView1.SelectedNode;
if (currenNod.Tag == null)
{
throw new Exception("selected item is null");
}
if (CutItem == null)
{
throw new Exception("Cut item not found");
}
var parentNod = treeView1.SelectedNode.Parent;
var parentItem = parentNod.Tag;
if (parentItem == null)
{
throw new Exception("Parent is not found");
}
//if (parentNod.Text == "Data") throw new Exception("Data section cannot be pasted");
//if (!CutItem.CutObj.GetType().Equals(currenNod.Tag.GetType())) throw new Exception("Cut item and paste item do not match");
var CutObj = CutItem.CutParentobj.Columns[CutItem.CutObjIndex];
if (CutObj.GetType().Equals(typeof(iColumn)) || CutObj.GetType().Equals(typeof(iTable)))
{
if (CutObj.GetType().Equals(typeof(iTable)) && (parentNod.Text == "Detail Header" || parentNod.Text == "Detail Footer" || parentNod.Text == "Data"))
{
throw new Exception("Table cannot be paste here");
}
//{
var parenttbl = parentItem as iTable;
if (parenttbl == null)
{
throw new Exception("Parent is not a table");
}
CutItem.CutParentobj.Columns.RemoveAt(CutItem.CutObjIndex);
var arrayIndex = parenttbl.Columns.IndexOf(currenNod.Tag);
parenttbl.Columns.Insert(arrayIndex, CutObj);
var nodIndex = selectedNod.Index;
string text = CutObj.GetType().Equals(typeof(iColumn)) ? ((iColumn)CutObj).Text : "Table";
var insertedNod = parentNod.Nodes.Insert(nodIndex, text);
insertedNod.Tag = CutObj;
CutItem.CutNode.Parent.Nodes.Remove(CutItem.CutNode);
CutItem = null;
//}
}
//if (currenNod.Tag.GetType().Equals(typeof(iTable)))
//{
// CutObject = currenNod.Tag as iTable;
//}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static string TableName(this iTable obj)
{
return(new string(obj.GetType().Name.Where(p => char.IsLetter(p) || p == '_').ToArray()));
}
private static void insertsql(Type type, string paramchar, string randomidcharacter, iTable obj, ref string into, ref string value, ref List <SqlParam> dbparam)
{
foreach (var item in type.GetProperties())
{
if (item.IsRandomChar(obj, randomidcharacter))
{
into = string.IsNullOrEmpty(into) ? $"{item.Name}" : $"{into},{item.Name}";
value = string.IsNullOrEmpty(value) ? serialvalue : $"{value},{serialvalue}";
}
else if (item.IsCreateUpdateDate())
{
into = string.IsNullOrEmpty(into) ? $"{item.Name}" : $"{into},{item.Name}";
value = string.IsNullOrEmpty(value) ? datevalue : $"{value},{datevalue}";
}
else if (item.IsColumn(obj))
{
into = string.IsNullOrEmpty(into) ? $"{item.Name}" : $"{into},{item.Name}";
value = string.IsNullOrEmpty(value) ? $"{paramchar}{item.Name}" : $"{value},{paramchar}{item.Name}";
if (dbparam.Where(x => x.name == item.Name).Count() == 0)
{
if (item.PropertyType.IsEnum)
{
dbparam.Add(new SqlParam(item.Name, item.GetValue(obj).ToString()));
}
else
{
dbparam.Add(new SqlParam(item.Name, item.GetValue(obj)));
}
}
}
}
}
public static bool IsRandomChar(this PropertyInfo item, iTable obj, string randomidcharacter)
{
return((item.GetCustomAttribute <DBColumnAttribute>() != null &&
item.GetCustomAttribute <DBColumnAttribute>().IsSerialKey) ||
(item.GetValue(obj) != null && item.GetValue(obj).ToString() == randomidcharacter));
}
public static bool IsColumn(this PropertyInfo item, iTable obj)
{
return(item.GetCustomAttribute <DBColumnAttribute>() != null &&
item.GetCustomAttribute <DBColumnAttribute>().IsColumn &&
item.GetValue(obj) != null);
}