private bool IsExcludeColumn(DBTableColumnInfo colInfo)
{
return(colInfo.IsIdentity ||
colInfo.IsAutogeneratedGuid ||
colInfo.DataType.ToUpperInvariant() == "TIMESTAMP" ||
colInfo.DataType.ToUpperInvariant() == "DATETIME" && colInfo.ColumnName.ToUpperInvariant() == "CREATIONDATE"); //autogenerated by convention
}
private DBTableInfo GetTableInformation(string tableName, int schemaId, SqlConnection connection)
{
string sql = $"Select * from INFORMATION_SCHEMA.COLUMNS c JOIN sys.schemas s ON c.TABLE_SCHEMA = s.name where TABLE_NAME='{tableName}' AND s.schema_id={schemaId}";
var dt = ExecuteDataTable(sql, connection);
var sqlTableInfo = new DBTableInfo();
List <DBTableColumnInfo> colList = new List <DBTableColumnInfo>();
foreach (DataRow dr in dt.Rows)
{
if (string.IsNullOrEmpty(sqlTableInfo.Schema))
{
string tableSchema = dr.Field <string>(InformationSchemaCols.TABLE_SCHEMA).Trim();
if (tableSchema != string.Empty)
{
sqlTableInfo.Schema = tableSchema;
}
}
string colName = dr.Field <string>(InformationSchemaCols.COLUMN_NAME);
var colinfo = new DBTableColumnInfo
{
ColumnName = colName,
DataType = dr.Field <string>(InformationSchemaCols.DATA_TYPE)
};
if (!string.IsNullOrWhiteSpace(dr[InformationSchemaCols.CHARACTER_MAXIMUM_LENGTH]?.ToString()))
{
colinfo.CharacterMaximumLength = int.Parse(dr[InformationSchemaCols.CHARACTER_MAXIMUM_LENGTH].ToString());
}
if (!string.IsNullOrWhiteSpace(dr[InformationSchemaCols.NUMERIC_PRECISION]?.ToString()))
{
colinfo.NumericPrecision = int.Parse(dr[InformationSchemaCols.NUMERIC_PRECISION].ToString());
}
if (!string.IsNullOrWhiteSpace(dr[InformationSchemaCols.NUMERIC_PRECISION_RADIX]?.ToString()))
{
colinfo.NumericPrecisionRadix = int.Parse(dr[InformationSchemaCols.NUMERIC_PRECISION_RADIX].ToString());
}
if (!string.IsNullOrWhiteSpace(dr[InformationSchemaCols.NUMERIC_SCALE]?.ToString()))
{
colinfo.NumericScale = int.Parse(dr[InformationSchemaCols.NUMERIC_SCALE].ToString());
}
colinfo.IsIdentity = IsIdentityColumn(colName, tableName, sqlTableInfo.Schema);
colinfo.IsAutogeneratedGuid = IsAutogeneratedGuidColumn(dr);
colinfo.IsPrimaryKey = IsPrimaryColumn(colName, tableName, sqlTableInfo.Schema);
colinfo.IsForeignKey = IsForeignKeyColumn(colName, tableName, sqlTableInfo.Schema);
colinfo.Exclude = IsExcludeColumn(colinfo);
colList.Add(colinfo);
}
sqlTableInfo.TableName = tableName;
sqlTableInfo.Columns = colList;
return(sqlTableInfo);
}
private DBTableInfo GetTableInformation(string tableName, SqlConnection connection)
{
string sql = "Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tableName + "'";
var dt = ExecuteDataTable(sql, connection);
var sqlTableInfo = new DBTableInfo();
List <DBTableColumnInfo> colList = new List <DBTableColumnInfo>();
foreach (DataRow dr in dt.Rows)
{
var colinfo = new DBTableColumnInfo();
colinfo.ColumnName = dr["COLUMN_NAME"].ToString();
colinfo.DataType = dr["DATA_TYPE"].ToString();
if (dr["CHARACTER_MAXIMUM_LENGTH"] != null && dr["CHARACTER_MAXIMUM_LENGTH"].ToString().Trim() != "")
{
colinfo.CharacterMaximumLength = int.Parse(dr["CHARACTER_MAXIMUM_LENGTH"].ToString());
}
if (dr["NUMERIC_PRECISION"] != null && dr["NUMERIC_PRECISION"].ToString().Trim() != "")
{
colinfo.NumericPrecision = int.Parse(dr["NUMERIC_PRECISION"].ToString());
}
if (dr["NUMERIC_PRECISION_RADIX"] != null && dr["NUMERIC_PRECISION_RADIX"].ToString().Trim() != "")
{
colinfo.NumericPrecisionRadix = int.Parse(dr["NUMERIC_PRECISION_RADIX"].ToString());
}
if (dr["NUMERIC_SCALE"] != null && dr["NUMERIC_SCALE"].ToString().Trim() != "")
{
colinfo.NumericScale = int.Parse(dr["NUMERIC_SCALE"].ToString());
}
if (dr["TABLE_SCHEMA"] != null && dr["TABLE_SCHEMA"].ToString().Trim() != "")
{
colinfo.Schema = dr["TABLE_SCHEMA"].ToString();
}
colinfo.IsIdentity = IsDinityColumn(dr["COLUMN_NAME"].ToString(), tableName);
colinfo.IsPrimaryKey = IsPrimaryColumn(dr["COLUMN_NAME"].ToString(), tableName);
colinfo.Exclude = IsExcludeColumn(colinfo);
colList.Add(colinfo);
}
sqlTableInfo.TableName = tableName;
sqlTableInfo.Columns = colList;
return(sqlTableInfo);
}
protected string GenerateWhereStatement(List <DBTableColumnInfo> whereConditionFields)
{
var sb = new StringBuilder();
sb.Append("\tWHERE ");
for (int i = 0; i < whereConditionFields.Count; i++)
{
if (i != 0)
{
sb.Append(Environment.NewLine + "\t\t AND ");
}
DBTableColumnInfo colInf = whereConditionFields[i];
sb.Append(Wrap(colInf.ColumnName) + "=" + PrefixWhereParameter + colInf.ColumnName);
}
return(sb.ToString());
}
private string GetColumnDispalyName(DBTableColumnInfo colInfo)
{
string diaplayName = colInfo.ColumnName;
if (colInfo.Exclude)
{
if (colInfo.IsIdentity)
{
diaplayName += " (IDENTITY)";
}
if (colInfo.DataType.ToUpperInvariant() == "TIMESTAMP")
{
diaplayName += " (TIMESTAMP)";
}
}
return(diaplayName);
}
private TreeViewNode CreateTableNode(DBTableInfo sqlTableInfo, TreeViewNode parent)
{
string tableDisplayName = sqlTableInfo.Schema == "dbo"
? sqlTableInfo.TableName
: sqlTableInfo.Schema + "." + sqlTableInfo.TableName;
TreeViewNode tblNode = new TreeViewNode(tableDisplayName, parent);
tblNode.Tag = sqlTableInfo;
TreeViewNode insertSp = new TreeViewNode(Constants.insertTreeNodeText, tblNode);
AddColumnNodes(insertSp, sqlTableInfo.Columns.Where(x => !x.Exclude), true);
tblNode.Children.Add(insertSp);
TreeViewNode deleteSp = new TreeViewNode(Constants.deleteTreeNodeText, tblNode);
TreeViewNode whereDelCondition = new TreeViewNode(Constants.whereConditionTreeNodeText, deleteSp);
deleteSp.Children.Add(whereDelCondition);
tblNode.Children.Add(deleteSp);
AddColumnNodes(whereDelCondition, sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToList(), true);
var updateCols = sqlTableInfo.Columns.Where(x => !x.Exclude).ToArray();
var updateWhereCols = sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToArray();
if (updateCols.Any() && updateCols.All(x => !updateWhereCols.Contains(x)))
{
TreeViewNode updateSp = new TreeViewNode(Constants.updateTreeNodeText, tblNode);
AddColumnNodes(updateSp, updateCols, true);
tblNode.Children.Add(updateSp);
TreeViewNode whereUpdateCondition = new TreeViewNode(Constants.whereConditionTreeNodeText, updateSp);
updateSp.Children.Add(whereUpdateCondition);
AddColumnNodes(whereUpdateCondition, updateWhereCols, true);
}
AddColumnNodes(deleteSp, new List <DBTableColumnInfo>(), true);
var selectColumns = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
TreeViewNode selectSp = new TreeViewNode(Constants.selectTreeNodeText, tblNode);
sqlTableInfo.Columns.CopyTo(selectColumns, 0);
Array.ForEach(selectColumns, x =>
{
x.Exclude = false;
});
AddColumnNodes(selectSp, selectColumns, true);
tblNode.Children.Add(selectSp);
var selectOneColumns = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
var selectOneWhereCols = sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToArray();
sqlTableInfo.Columns.CopyTo(selectOneColumns, 0);
Array.ForEach(selectColumns, x =>
{
x.Exclude = false;
});
if (selectOneColumns.Any() &&
selectOneWhereCols.All(x => selectOneColumns.Any(y => x.ColumnName != y.ColumnName)))
{
TreeViewNode selectOneSp = new TreeViewNode(Constants.selectOneTreeNodeText, tblNode);
AddColumnNodes(selectOneSp, selectOneColumns, true);
tblNode.Children.Add(selectOneSp);
TreeViewNode whereSelectOneCondition = new TreeViewNode(Constants.whereConditionTreeNodeText,
selectOneSp);
selectOneSp.Children.Add(whereSelectOneCondition);
AddColumnNodes(whereSelectOneCondition, selectOneWhereCols, true);
}
var selectByColumns = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
var selectByWhereCols = sqlTableInfo.Columns.Where(x => x.IsForeignKey && !x.IsPrimaryKey).ToArray();
sqlTableInfo.Columns.CopyTo(selectByColumns, 0);
Array.ForEach(selectByColumns, x =>
{
x.Exclude = false;
});
if (selectByColumns.Any() &&
selectByWhereCols.Any())
{
TreeViewNode selectBySp = new TreeViewNode(Constants.selectByTreeNodeText, tblNode);
AddColumnNodes(selectBySp, selectByColumns, true);
tblNode.Children.Add(selectBySp);
TreeViewNode whereSelectByCondition = new TreeViewNode(Constants.whereConditionTreeNodeText,
selectBySp);
selectBySp.Children.Add(whereSelectByCondition);
AddColumnNodes(whereSelectByCondition, selectByWhereCols, true);
}
return(tblNode);
}