} // CreateTableParameter
public virtual QueryParameter CreateTableParameter(
Type oColumnInfo,
string sFieldName,
IEnumerable oValues,
Func <object, object[]> oValueToRow,
IEnumerable <Type> oCustomTypeOrder = null
)
{
var tbl = new DataTable();
if (TypeUtils.IsSimpleType(oColumnInfo))
{
AddColumn(tbl, oColumnInfo);
}
else
{
if (oCustomTypeOrder == null)
{
PropertyTraverser.Traverse(oColumnInfo, (i, oPropertyInfo) => {
object[] pkAttrList = oPropertyInfo.GetCustomAttributes(typeof(PKAttribute), false);
if (pkAttrList.Length < 1)
{
AddColumn(tbl, oPropertyInfo.PropertyType);
return;
} // if no PK configured
PKAttribute pk = (PKAttribute)pkAttrList[0];
// Primary key which is identity is not inserted into output column list
// because such field usage is intended for saving a new item but identity
// column is filled by DB.
if (!pk.WithIdentity)
{
AddColumn(tbl, oPropertyInfo.PropertyType);
}
});
}
else
{
foreach (Type t in oCustomTypeOrder)
{
AddColumn(tbl, t);
}
} // if
} // if
if (oValues != null)
{
foreach (object v in oValues)
{
tbl.Rows.Add(oValueToRow(v));
}
}
return(BuildTableParameter(sFieldName, tbl));
} // CreateTableParameter
} // SaveTo
public static ExcelWorksheet CreateSheet(ExcelPackage book)
{
var lst = new List <string>();
PropertyTraverser.Traverse <CRMData>((ignored, pi) => {
lst.Add(pi.Name);
});
return(book.CreateSheet("CRM", false, lst.ToArray()));
} // CreateSheet
public static DataTable ToTable()
{
var tbl = new DataTable();
PropertyTraverser.Traverse(typeof(NewUnmatchedPayment), (ignoredInstance, pi) => {
if (pi.PropertyType == typeof(byte[]))
{
return;
}
tbl.Columns.Add(pi.Name, pi.PropertyType);
});
return(tbl);
} // ToTable
} // enum InputRowTypes
private static DataTable ToTable(SortedDictionary <Source, McsRow> oData)
{
McsRow oTotal = new McsRow {
Source = Source.Total,
Css = "total",
};
DataTable tbl = new DataTable();
PropertyTraverser.Traverse(typeof(McsRow), (ignored, oPropInfo) => {
object[] oAttrList = oPropInfo.GetCustomAttributes(typeof(ToStringAttribute), false);
tbl.Columns.Add(oPropInfo.Name, oAttrList.Length > 0 ? typeof(string) : oPropInfo.PropertyType);
});
Source[] aryAllSources = Enum.GetValues(typeof(Source)).Cast <Source>().ToArray();
Array.Sort(aryAllSources,
(a, b) => string.Compare(a.ToString(), b.ToString(), System.StringComparison.InvariantCultureIgnoreCase)
);
for (int i = 0; i < aryAllSources.Length; i++)
{
Source nSource = (Source)aryAllSources.GetValue(i);
McsRow oRow = oData.ContainsKey(nSource) ? oData[nSource] : new McsRow {
Source = nSource,
};
ToRow(tbl, oRow);
oTotal.Add(oRow);
} // for each
ToRow(tbl, oTotal);
return(tbl);
} // ToTable
public static string GetCreateTable <T>() where T : class
{
var oFields = new List <string>();
var oConstraints = new List <string>();
string sTableName = typeof(T).Name;
PropertyTraverser.Traverse(typeof(T), (ignored, oPropInfo) => {
string sType = T2T(oPropInfo);
if (string.IsNullOrWhiteSpace(sType))
{
return;
}
List <CustomAttributeData> oKeyAttr = oPropInfo.CustomAttributes
.Where(a => a.AttributeType == typeof(FKAttribute) || a.AttributeType == typeof(PKAttribute))
.ToList();
string fullPropName = "\t\t[" + oPropInfo.Name + "] " + sType;
if (oKeyAttr.Count == 0)
{
oFields.Add(fullPropName);
return;
} // if
bool isPk = false;
if (oKeyAttr.Any(x => x.AttributeType == typeof(PKAttribute)))
{
oConstraints.Add("\t\tCONSTRAINT PK_" + sTableName + " PRIMARY KEY ([" + oPropInfo.Name + "]),");
oFields.Insert(0, fullPropName);
isPk = true;
} // if
var fk = oPropInfo.GetCustomAttribute <FKAttribute>();
if ((fk != null) && !string.IsNullOrWhiteSpace(fk.TableName))
{
oConstraints.Add(
"\t\tCONSTRAINT FK_" + sTableName + "_" + oPropInfo.Name +
" FOREIGN KEY ([" + oPropInfo.Name + "]) REFERENCES [" + fk.TableName + "] ([" + fk.FieldName + "]),"
);
} // if
if (!isPk)
{
oFields.Add(fullPropName);
}
});
oFields.Add("\t\t[TimestampCounter] ROWVERSION");
return
("SET QUOTED_IDENTIFIER ON\nGO\n\n" +
"IF OBJECT_ID('" + sTableName + "') IS NULL\nBEGIN\n" +
"\tCREATE TABLE [" + sTableName + "] (\n" +
string.Join(",\n", oFields) +
(oConstraints.Count < 1 ? "" : ",\n" + string.Join("\n", oConstraints)) +
"\n\t)\nEND\nGO\n\n");
} // GetCreateTable
} // GetCreateTable
public static string GetCreateSp <T>() where T : class
{
var oSql = new List <string>();
var oFields = new List <string>();
var oFieldNames = new List <string>();
List <string> oProcSql = new List <string>();
string sTableName = typeof(T).Name;
string sProcName = sTableName + "Save";
string sTypeName = sTableName + "List";
oSql.Add("IF OBJECT_ID('" + sProcName + "') IS NOT NULL\n\tDROP PROCEDURE " + sProcName + "\nGO\n");
oSql.Add("IF TYPE_ID('" + sTypeName + "') IS NOT NULL\n\tDROP TYPE " + sTypeName + "\nGO\n");
oSql.Add("CREATE TYPE " + sTypeName + " AS TABLE (");
PropertyTraverser.Traverse(typeof(T), (ignored, oPropInfo) => {
string sType = T2T(oPropInfo);
List <bool> oKeyAttr = oPropInfo.CustomAttributes
.Where(a => a.AttributeType == typeof(PKAttribute) && a.ConstructorArguments.Count > 0)
.Select(a => (bool)a.ConstructorArguments[0].Value)
.ToList();
if (oKeyAttr.Count > 0 && oKeyAttr[0]) // is identity
{
return;
}
if (!string.IsNullOrWhiteSpace(sType))
{
if (oPropInfo.DeclaringType == typeof(T))
{
oFields.Add("[" + oPropInfo.Name + "] " + sType);
oFieldNames.Add("[" + oPropInfo.Name + "]");
}
else
{
oSql.Add("\t[" + oPropInfo.Name + "] " + sType + ",");
oFieldNames.Insert(0, "[" + oPropInfo.Name + "] ");
} // if
} // if
});
var sFieldNames = string.Join(",\n\t\t", oFieldNames);
oProcSql.Add("CREATE PROCEDURE " + sProcName);
oProcSql.Add("@Tbl " + sTypeName + " READONLY");
oProcSql.Add("AS");
oProcSql.Add("BEGIN");
oProcSql.Add("\tSET NOCOUNT ON;\n");
oProcSql.Add("\tINSERT INTO " + sTableName + " (");
oProcSql.Add("\t\t" + sFieldNames);
oProcSql.Add("\t) SELECT");
oProcSql.Add("\t\t" + sFieldNames);
oProcSql.Add("\tFROM @Tbl\n");
oProcSql.Add("\tDECLARE @ScopeID INT = SCOPE_IDENTITY()");
oProcSql.Add("\tSELECT @ScopeID AS ScopeID");
oProcSql.Add("END");
oProcSql.Add("GO");
return
("SET QUOTED_IDENTIFIER ON\nGO\n\n" +
string.Join("\n", oSql) + "\n\t" +
string.Join(",\n\t", oFields) + "\n)\nGO\n\n" +
string.Join("\n", oProcSql) + "\n\n");
} // GetCreateSp
