private CodeMemberMethod BuildFillMethod(TableViewTableTypeBase table)
{
// Accepts a DataRow and Returns a Poco of this type.
CodeMemberMethod cmmFill = new CodeMemberMethod();
cmmFill.Name = "Fill";
cmmFill.Attributes = MemberAttributes.Private;
cmmFill.ReturnType = new CodeTypeReference(table.Name);
CodeParameterDeclarationExpression cpdeDataRow = new CodeParameterDeclarationExpression();
cpdeDataRow.Name = "row";
cpdeDataRow.Type = new CodeTypeReference("System.Data.DataRow");
cpdeDataRow.Direction = FieldDirection.In;
cmmFill.Parameters.Add(cpdeDataRow);
var init_Express = new CodeSnippetExpression("new " + table.Name + "()");
var obj = new CodeVariableDeclarationStatement(new CodeTypeReference(table.Name), "obj", init_Express);
cmmFill.Statements.Add(obj);
foreach (Column c in table.Columns)
{
String DotNetTypeName = TypeConvertor.ToNetType(c.DataType.SqlDataType).ToString();
MemberGraph mGraph = new MemberGraph(c);
System.CodeDom.CodeConditionStatement ccsField = new CodeConditionStatement();
ccsField.Condition = new CodeSnippetExpression("(row[\"" + c.Name + "\"] != System.DBNull.Value)");
//if (!(mGraph.IsReadOnly))
//{
// If Field is nullable Type
if (mGraph.IsNullable)
{
if (mGraph.TypeName() == "String")
{
ccsField.TrueStatements.Add(new CodeSnippetExpression("obj." + mGraph.PropertyName() + " = row[\"" + c.Name + "\"].ToString()"));
}
else
{
ccsField.TrueStatements.Add(new CodeSnippetExpression("obj." + mGraph.PropertyName() + " = ((" + mGraph.TypeName() + ")(row[\"" + c.Name + "\"]))"));
}
}
else
{
if (mGraph.TypeName() == "String")
{
ccsField.TrueStatements.Add(new CodeSnippetExpression("obj." + mGraph.PropertyName() + " = row[\"" + c.Name + "\"].ToString()"));
}
else
{
ccsField.TrueStatements.Add(new CodeSnippetExpression("obj." + mGraph.PropertyName() + " = ((" + mGraph.TypeName() + ")(row[\"" + c.Name + "\"]))"));
}
}
cmmFill.Statements.Add(ccsField);
//}
}
cmmFill.Statements.Add(new CodeSnippetExpression("return obj"));
cmmFill.Comments.Add(new CodeCommentStatement("Returns a Hydrated POCO"));
return(cmmFill);
}
public static SqlParameter ToSqlPrimaryIDParam(this object obj, SqlParameter[] additionalParams = null)
{
Type type = obj.GetType();
var sqlProp = type.GetProperties().FirstOrDefault(x => Attribute.IsDefined(x, typeof(PrimaryID)));
if (sqlProp != null)
{
}
else
{
throw new Exception("No Primary ID Field Defined");
}
var v = new QueryParamInfo();
v.Name = sqlProp.Name;
v.Value = sqlProp.GetValue(obj);
var sqlParam = new SqlParameter(v.Name, TypeConvertor.ToSqlDbType(sqlProp.PropertyType))
{
Value = v.Value
};
return(sqlParam);
}
public object Invoke(List <object> parameters)
{
if (parameters != null && parameters.Count > ParametersInfo.Count())
{
throw new ArgumentException("Too many parameters in input");
}
if (parameters != null && parameters.Count > 0 && ParametersInfo.Count() == 0)
{
throw new ArgumentException("Too many parameters in input");
}
if (ParametersInfo == null || ParametersInfo.Count() == 0)
{
return(MethodInfo.Invoke(callingInstance, null));
}
if (parameters == null || parameters.Count == 0)
{
return(MethodInfo.Invoke(callingInstance, new object[ParametersInfo.Count()]));
}
object[] realParameters = new object[ParametersInfo.Count()];
for (int i = 0; i < parameters.Count(); i++)
{
realParameters[i] = TypeConvertor.ConvertObject(ParametersInfo[i].ParameterType, parameters[i]);
}
return(MethodInfo.Invoke(callingInstance, realParameters));
}
static void Main(string[] args)
{
var connectionStringSQLServer = ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString;
var sqlServerApi = new SqlServerApi(connectionStringSQLServer);
Console.WriteLine("\n qlServerApi(connectionStringSQLServer)");
Console.WriteLine("--------------------------------------------------------------------------------");
Console.WriteLine(connectionStringSQLServer);
var builder = new SqlConnectionStringBuilder(connectionStringSQLServer);
var dbName = builder.InitialCatalog;
Console.WriteLine("dbName:" + dbName);
var testConnection = sqlServerApi.TestConnection();
if (!testConnection)
{
Console.WriteLine("\n sqlServerApi.TestConnection() failed.");
Environment.Exit(0);
}
Console.WriteLine("\n sqlServerApi.Version()");
Console.WriteLine("--------------------------------------------------------------------------------");
Console.WriteLine(sqlServerApi.Version());
var database = sqlServerApi.Database(dbName, "%", "%Entity%", "%", "%sp_%");
Console.WriteLine("\n sqlServerApi.Database(dbName, \"%\", \"%\")");
Console.WriteLine("--------------------------------------------------------------------------------");
Console.WriteLine(database.ToString());
Console.WriteLine("\n database.Tables()");
Console.WriteLine("--------------------------------------------------------------------------------");
foreach (var item in database.Tables)
{
Console.WriteLine(item.ToString() + "\n");
}
Console.WriteLine("\n database.StoredProcedures()");
Console.WriteLine("--------------------------------------------------------------------------------");
foreach (var item in database.StoredProcedures)
{
Console.WriteLine(item.Name + " p:" + item.Parameters.Count() + " r:" + item.ResultColumns.Count());
}
Console.WriteLine("\n TypeConvertor");
Console.WriteLine("--------------------------------------------------------------------------------");
foreach (var sqlDbType in Enum.GetValues(typeof(SqlDbType)).Cast <SqlDbType>())
{
var typeMapElelment = TypeConvertor.Find(sqlDbType);
Console.WriteLine(typeMapElelment.DbType + " | " + sqlDbType.ToString() + " | " + typeMapElelment.Type.Name + " | " + typeMapElelment.CsSampleData());
}
Console.ReadKey();
}
QueryParamInfo GetParamInfo(string name, string sqlTypeAndLength)
{
var qp = new QueryParamInfo();
var m = Regex.Match(sqlTypeAndLength, @"(?'type'^\w*)\(?(?'firstNum'\d*),?(?'secondNum'\d*)");
var typeOnly = m.Groups["type"].Value;
int.TryParse(m.Groups["firstNum"].Value, out int firstNum);
int.TryParse(m.Groups["secondNum"].Value, out int secondNum);
if (secondNum != 0)
{
qp.Precision = firstNum;
qp.Scale = secondNum;
}
else if (typeOnly.ToLower() == "datetime2")
{
qp.Precision = firstNum;
}
else if (firstNum > 0)
{
qp.Length = firstNum;
}
//string normalizedType;
// we have no info for the nullability of query params, so we'll make them all nullable
//var csType = System2Alias.Map( TypeMapDB2CS(typeOnly, out normalizedType), true );
try
{
// hack
if (typeOnly == "sql_variant")
{
typeOnly = "Variant";
}
var sqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), typeOnly, true);
var csType = TypeConvertor.ToNetType(sqlDbType);
var dbType = TypeConvertor.ToDbType(sqlDbType);
qp.CSType = System2Alias.Map(csType.FullName, true); // will look up aliases of system types, and append the question mark.
qp.FullyQualifiedCSType = csType.FullName;
qp.DbType = dbType.ToString();
qp.CSNameCamel = char.ToLower(name.First()) + name.Substring(1);
qp.CSNamePascal = char.ToUpper(name.First()) + name.Substring(1);
qp.CSNamePrivate = "_" + qp.CSNameCamel;
qp.DbName = '@' + name;
return(qp);
}
catch (Exception ex)
{
throw new TypeNotMatchedException(ex.Message);
}
}
public static List <SqlParameter> ToSqlParamsList(this object obj, SqlParameter[] additionalParams = null)
{
var props = (
from p in obj.GetType().GetProperties(System.Reflection.BindingFlags.Public
| System.Reflection.BindingFlags.Instance
| System.Reflection.BindingFlags.DeclaredOnly)
let nameAttr = p.GetCustomAttributes(typeof(QueryParamNameAttribute), true)
let ignoreAttr = p.GetCustomAttributes(typeof(QueryParamIgnoreAttribute), true)
select new { Property = p, Names = nameAttr, Ignores = ignoreAttr }).ToList();
var result = new List <SqlParameter>();
foreach (var p in props)
{
if (p.Ignores != null && p.Ignores.Length > 0)
{
continue;
}
var name = p.Names.FirstOrDefault() as QueryParamNameAttribute;
var pinfo = new QueryParamInfo();
if (name != null && !String.IsNullOrWhiteSpace(name.Name))
{
pinfo.Name = name.Name.Replace("@", "");
}
else
{
pinfo.Name = p.Property.Name.Replace("@", "");
}
pinfo.Value = p.Property.GetValue(obj) ?? DBNull.Value;
var sqlParam = new SqlParameter(pinfo.Name, TypeConvertor.ToSqlDbType(p.Property.PropertyType))
{
Value = pinfo.Value
};
result.Add(sqlParam);
}
if (additionalParams != null && additionalParams.Length > 0)
{
result.AddRange(additionalParams);
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public CodeMemberMethod BuildSelectBE(TableViewTableTypeBase table)
{
CodeMemberMethod cmSelect = new CodeMemberMethod();
cmSelect.Attributes = MemberAttributes.Public;
cmSelect.ReturnType = new CodeTypeReference("System.Data.DataSet");
String cp_name = "ssp_" + table.Name;
String PocoTypeName = table.Name;
String FullPocoTypeName = PocoTypeName;
CodeParameterDeclarationExpression cpdePoco = new CodeParameterDeclarationExpression();
cpdePoco.Name = "query";
cpdePoco.Type = new CodeTypeReference(table.Name);
cpdePoco.Direction = FieldDirection.In;
cmSelect.Parameters.Add(cpdePoco);
cmSelect.Attributes = MemberAttributes.Public;
cmSelect.Name = "Select";
cmSelect.Statements.Add(new CodeSnippetExpression("this.Access.CreateProcedureCommand(\"" + cp_name + "\")"));
foreach (Column c in table.Columns)
{
MemberGraph mGraph = new MemberGraph(c);
String DotNetTypeName = TypeConvertor.ToNetType(c.DataType.SqlDataType).ToString();
System.CodeDom.CodeConditionStatement ccsField = new CodeConditionStatement();
if (mGraph.IsNullable)
{
ccsField.Condition = new CodeSnippetExpression("query." + mGraph.PropertyName() + ".HasValue");
ccsField.TrueStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\",query." + mGraph.PropertyName() + ".Value, ParameterDirection.Input)"));
ccsField.FalseStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\", null , ParameterDirection.Input)"));
}
else
{
ccsField.Condition = new CodeSnippetExpression("query." + mGraph.PropertyName() + " == null");
ccsField.TrueStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\", null , ParameterDirection.Input)"));
ccsField.FalseStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\",query." + mGraph.PropertyName() + ", ParameterDirection.Input)"));
}
cmSelect.Statements.Add(ccsField);
}
cmSelect.Statements.Add(new CodeSnippetExpression("return this.Access.ExecuteDataSet()"));
cmSelect.Comments.Add(new CodeCommentStatement("Select by Object [Implements Query By Example], returns DataSet"));
return(cmSelect);
}
private List <ParameterSQL> StoredProcedureParameters(SqlCommand command, string procedureName)
{
List <ParameterSQL> returnValue = new List <ParameterSQL>();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = procedureName;
SqlCommandBuilder.DeriveParameters(command);
foreach (SqlParameter p in command.Parameters)
{
returnValue.Add(new ParameterSQL
{
Name = p.ParameterName,
Direction = p.Direction,
Size = p.Size,
DataType = TypeConvertor.ToSqlDbType(p.DbType)
});
}
return(returnValue);
}
/// <summary>
/// Registers the converter for the specific Type
/// </summary>
/// <typeparam name="TFor">The type to register the converter for.</typeparam>
/// <param name="convertor">The converter to register</param>
public void RegisterTypeConverter <TFor>(TypeConvertor convertor)
{
_convertors[typeof(TFor)] = convertor;
}
/// <summary>
/// Registers the converter for the specific Type
/// </summary>
/// <typeparam name="TFor">The type to register the converter for.</typeparam>
/// <param name="convertor">The converter to register</param>
/// <remarks>
/// If a converter for the type is already registered, it is overwritten with the new value.
/// </remarks>
public void RegisterTypeConvertor <TFor>(TypeConvertor convertor)
{
_typeConvertors.RegisterTypeConverter <TFor>(convertor);
}
public SubSonicSqlParameter(string name, object value)
: base(name, value)
{
SqlDbType = TypeConvertor.ToSqlDbType(value.GetType(), SubSonicContext.DbOptions.SupportUnicode);
}
public SubSonicSqlParameter(string name, object value, IDbEntityProperty property)
: base(name, value, property)
{
SqlDbType = TypeConvertor.ToSqlDbType(property.DbType, SubSonicContext.DbOptions.SupportUnicode);
}
/// <summary>
/// Registers the converter for the specific Type
/// </summary>
/// <param name="typeFor">The type to register the converter for.</param>
/// <param name="convertor">The converter to register</param>
public void RegisterTypeConverter(Type typeFor, TypeConvertor convertor)
{
_convertors[typeFor] = convertor;
}
/// <summary>
/// Registers a converter for the specific type
/// </summary>
/// <param name="typeFor">The type to register the converter for.</param>
/// <param name="convertor">The converter to register</param>
/// <returns>Instance of the TypeScriptFluent that enables fluent configuration.</returns>
public TypeScriptFluent WithConvertor(Type typeFor, TypeConvertor convertor)
{
_scriptGenerator.RegisterTypeConvertor(typeFor, convertor);
return(this);
}
public static TypeScriptFluent RegisterTypeConvertor(this TypeScriptFluent fluent, Type a, TypeConvertor b)
{
var sg = fluent.ScriptGenerator;
var flags = BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance;
var typeConvertorsField = typeof(TsGenerator).GetField("_typeConvertors", flags);
var typeConvertors = (TypeConvertorCollection)typeConvertorsField?.GetValue(sg);
var convertorsField = typeof(TypeConvertorCollection).GetField("_convertors", flags);
var convertors = (Dictionary <Type, TypeConvertor>)convertorsField?.GetValue(typeConvertors);
convertors?.Add(a, b);
return(fluent);
}
protected void AddSQLParam(MySqlParameterCollection @params, string name, object val)
{
AddSQLParam(@params, name, val, TypeConvertor.ToMySqlDbType(val));
}
internal void Setup(
string applicationConfigurationFileFullName,
bool useInternalRollingFileConfiguration, string businessLogFileFullname, string technicalLogFileFullname,
bool useInternalAdoNetConfiguration, Type connectionType, string connectionStringName, string logTableName,
LogLevel businessMinimumLogLevel = LogLevel.Trace,
LogLevel technicalMinimumLogLevel = LogLevel.Trace,
Func <object, Exception, string> exceptionFormatter = null)
{
UseInternalBusinessRollingFileConfiguration = useInternalRollingFileConfiguration && !String.IsNullOrWhiteSpace(businessLogFileFullname);
UseInternalTechnicalRollingFileConfiguration = useInternalRollingFileConfiguration && !String.IsNullOrWhiteSpace(technicalLogFileFullname);
UseInternalBusinessAdoNetConfiguration = useInternalAdoNetConfiguration;
if (!UseInternalBusinessRollingFileConfiguration && !UseInternalTechnicalRollingFileConfiguration && !UseInternalBusinessAdoNetConfiguration)
{
if (!String.IsNullOrWhiteSpace(applicationConfigurationFileFullName))
{
XmlConfigurator.Configure(LogManager.GetRepository(Assembly.GetEntryAssembly()), Parselog4NetConfigFile(applicationConfigurationFileFullName));
}
else
{
XmlConfigurator.Configure(LogManager.GetRepository(Assembly.GetEntryAssembly()));
}
}
else
{
var log4NetElement = GetDefaultConfiguration(
businessLogFileFullname ?? String.Empty,
businessMinimumLogLevel,
technicalLogFileFullname ?? String.Empty,
technicalMinimumLogLevel,
connectionType ?? GetType(),
connectionStringName ?? String.Empty,
logTableName ?? String.Empty);
if (!UseInternalTechnicalRollingFileConfiguration)
{
var technicalRollingFileAppenderElement = log4NetElement.DescendantsAndSelf().First(e => e.HasAttributes && e.Attributes("name").Any() && e.Attributes("name").ElementAt(0).Value == "TechnicalRollingFileAppender");
technicalRollingFileAppenderElement.Remove();
var technicalLoggerElement = log4NetElement.DescendantsAndSelf().First(e => e.HasAttributes && e.Attributes("ref").Any() && e.Attributes("ref").ElementAt(0).Value == "TechnicalRollingFileAppender");
technicalLoggerElement.Remove();
}
if (!UseInternalBusinessRollingFileConfiguration)
{
var businessRollingFileAppenderElement = log4NetElement.DescendantsAndSelf().First(e => e.HasAttributes && e.Attributes("name").Any() && e.Attributes("name").ElementAt(0).Value == "BusinessRollingFileAppender");
businessRollingFileAppenderElement.Remove();
var businessLoggerElement = log4NetElement.DescendantsAndSelf().First(e => e.HasAttributes && e.Attributes("ref").Any() && e.Attributes("ref").ElementAt(0).Value == "BusinessRollingFileAppender");
businessLoggerElement.Remove();
}
if (!UseInternalBusinessAdoNetConfiguration)
{
var businessRollingFileAppenderElement = log4NetElement.DescendantsAndSelf().First(e => e.HasAttributes && e.Attributes("name").Any() && e.Attributes("name").ElementAt(0).Value == "BusinessAdoNetAppender");
businessRollingFileAppenderElement.Remove();
var businessLoggerElement = log4NetElement.DescendantsAndSelf().First(e => e.HasAttributes && e.Attributes("ref").Any() && e.Attributes("ref").ElementAt(0).Value == "BusinessAdoNetAppender");
businessLoggerElement.Remove();
}
XmlConfigurator.Configure(LogManager.GetRepository(Assembly.GetCallingAssembly()), log4NetElement.ToXmlElement());
}
var loggers = LogManager.GetCurrentLoggers(Assembly.GetCallingAssembly());
if (!IsBusinessLogTableCreated)
{
AdoNetAppender adoNetAppender = null;
if (loggers.Any())
{
foreach (var adoAppender in loggers.Select(
logger => logger.Logger.Repository.GetAppenders().FirstOrDefault(a => a.GetType() == typeof(AdoNetAppender)))
.Where(adoAppender => adoAppender != null).OfType <AdoNetAppender>())
{
adoNetAppender = adoAppender;
break;
}
}
if (adoNetAppender != null)
{
var connectionString = ConfigurationManager.ConnectionStrings[adoNetAppender.ConnectionStringName].ConnectionString;
var field = typeof(AdoNetAppender).GetField("m_parameters", BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Static);
if (field == null)
{
Debug.WriteLine("Can not find field name 'm_parameters' in AdoNetAppender class using reflection.");
throw new InvalidOperationException("Can not find field name 'm_parameters' in AdoNetAppender class using reflection.", new NullReferenceException("field"));
}
if (field.GetValue(adoNetAppender) is ArrayList parameters)
{
var fields = adoNetAppender.CommandText.Split(' ')[3].Split(',').Select(e => e.Replace("(", "").Replace(")", "").Replace("[", "").Replace("]", "")).ToList();
logTableName = adoNetAppender.CommandText.Split(' ')[2];
var tableFieldsList = new List <string> {
"[LOG_id] [bigint] IDENTITY(1,1) NOT NULL"
};
for (var i = 0; i < parameters.Count; i++)
{
var parameter = (AdoNetAppenderParameter)parameters[i];
var tableField = new StringBuilder();
tableField.AppendFormat("[{0}] [{1}]{2} {3}",
fields[i],
TypeConvertor.ToSqlDbType(parameter.DbType),
parameter.DbType == DbType.AnsiString || parameter.DbType == DbType.AnsiStringFixedLength ||
parameter.DbType == DbType.String || parameter.DbType == DbType.StringFixedLength ||
parameter.DbType == DbType.Xml
? String.Format("({0})", parameter.Size)
: String.Empty,
"NULL");
tableFieldsList.Add(tableField.ToString());
}
using (var connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
using (var command = connection.CreateCommand())
{
var nameParameter = command.CreateParameter();
nameParameter.DbType = DbType.String;
nameParameter.ParameterName = "name";
nameParameter.Value = logTableName.ToLower();
command.Parameters.Add(nameParameter);
command.CommandText = "IF NOT EXISTS (SELECT * FROM sys.tables WHERE LOWER(name) = @name) " + Environment.NewLine;
command.CommandText += "BEGIN " + Environment.NewLine;
command.CommandText += String.Format("CREATE TABLE {0} (", logTableName) + Environment.NewLine;
foreach (var tableField in tableFieldsList)
{
command.CommandText += tableField + ", " + Environment.NewLine;
}
command.CommandText += String.Format("CONSTRAINT [PK_dbo.{0}] PRIMARY KEY CLUSTERED ", logTableName) + Environment.NewLine;
command.CommandText += "( " + Environment.NewLine;
command.CommandText += "[LOG_id] ASC " + Environment.NewLine;
command.CommandText += ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] " + Environment.NewLine;
command.CommandText += ") ON [PRIMARY] " + Environment.NewLine;
command.CommandText += "END";
Debug.WriteLine("Creating logs table: " + command.CommandText);
command.ExecuteNonQuery();
}
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}
else
{
Console.WriteLine(@"dbCommand is null.");
}
}
}
IsBusinessLogTableCreated = true;
}
/// <summary>
/// Registers a converter for the specific type
/// </summary>
/// <typeparam name="TFor">The type to register the converter for.</typeparam>
/// <param name="convertor">The converter to register</param>
/// <returns>Instance of the TypeScriptFluent that enables fluent configuration.</returns>
public TypeScriptFluent WithConvertor <TFor>(TypeConvertor convertor)
{
_scriptGenerator.RegisterTypeConvertor <TFor>(convertor);
return(this);
}
/// <summary>
/// Registers the converter for the specific Type
/// </summary>
/// <param name="typeFor">The type to register the converter for.</param>
/// <param name="convertor">The converter to register</param>
/// <remarks>
/// If a converter for the type is already registered, it is overwritten with the new value.
/// </remarks>
public void RegisterTypeConvertor(Type typeFor, TypeConvertor convertor)
{
_typeConvertors.RegisterTypeConverter(typeFor, convertor);
}