static string ConvertToCompilable(string name, bool capitalize)
{
if (capitalize)
{
var sb = new StringBuilder(name);
for (int i = 0; i < sb.Length; i++)
{
if (char.IsLetter(sb[i]))
{
sb[i] = sb[i].ToUpper();
break;
}
}
name = sb.ToString();
}
return(CSharpTools.ToValidIdentifier(name));
}
public IEnumerable <ExplorerItem> GetItemsAndCode(string nameSpace, string typeName)
{
typeName = ConvertToCompilable(typeName, false);
var connectionString = _cxInfo.DatabaseInfo.CustomCxString;
var provider = ProviderHelper.GetProvider(ProviderName).GetDataProvider(connectionString);
using (var db = new DataConnection(provider, connectionString))
{
db.CommandTimeout = CommandTimeout;
_dataProvider = db.DataProvider;
_sqlBuilder = _dataProvider.CreateSqlBuilder();
var options = new GetSchemaOptions();
var includeSchemas = (string)_cxInfo.DriverData.Element("includeSchemas");
if (includeSchemas != null)
{
options.IncludedSchemas = includeSchemas.Split(',', ';');
}
var excludeSchemas = (string)_cxInfo.DriverData.Element("excludeSchemas");
if (excludeSchemas != null)
{
options.ExcludedSchemas = excludeSchemas.Split(',', ';');
}
var includeCatalogs = (string)_cxInfo.DriverData.Element("includeCatalogs");
if (includeCatalogs != null)
{
options.IncludedCatalogs = includeCatalogs.Split(',', ';');
}
var excludeCatalogs = (string)_cxInfo.DriverData.Element("excludeCatalogs");
if (excludeCatalogs != null)
{
options.ExcludedCatalogs = excludeCatalogs.Split(',', ';');
}
options.GetProcedures = (string)_cxInfo.DriverData.Element("excludeRoutines") != "true";
_schema = _dataProvider.GetSchemaProvider().GetSchema(db, options);
ConvertSchema(typeName);
}
Code
.AppendLine("using System;")
.AppendLine("using System.Collections;")
.AppendLine("using System.Collections.Generic;")
.AppendLine("using System.Data;")
.AppendLine("using System.Reflection;")
.AppendLine("using System.Linq;")
.AppendLine("using LinqToDB;")
.AppendLine("using LinqToDB.Common;")
.AppendLine("using LinqToDB.Data;")
.AppendLine("using LinqToDB.Mapping;")
.AppendLine("using System.Net.NetworkInformation;")
;
if (_schema.Procedures.Any(_ => _.IsAggregateFunction))
{
Code
.AppendLine("using System.Linq.Expressions;")
;
}
if (_schema.ProviderSpecificTypeNamespace.NotNullNorWhiteSpace())
{
Code.AppendLine($"using {_schema.ProviderSpecificTypeNamespace};");
}
var providerInfo = ProviderHelper.GetProvider(ProviderName);
References.AddRange(providerInfo.GetAssemblyLocation(connectionString));
if (providerInfo.Provider.AdditionalNamespaces != null)
{
foreach (var ns in providerInfo.Provider.AdditionalNamespaces)
{
Code.AppendLine($"using {ns};");
}
}
Code
.AppendLine($"namespace {nameSpace}")
.AppendLine("{")
.AppendLine($" public class {typeName} : LinqToDB.LINQPad.LINQPadDataConnection")
.AppendLine(" {")
.AppendLine($" public {typeName}(string provider, string connectionString)")
.AppendLine(" : base(provider, connectionString)")
.AppendLine(" {")
.AppendLine($" CommandTimeout = {CommandTimeout};")
.AppendLine(" }")
.AppendLine($" public {typeName}()")
.AppendLine($" : base({CSharpTools.ToStringLiteral(ProviderName)}, {CSharpTools.ToStringLiteral(connectionString)})")
.AppendLine(" {")
.AppendLine($" CommandTimeout = {CommandTimeout};")
.AppendLine(" }")
;
if (ProviderName == LinqToDB.ProviderName.PostgreSQL)
{
PreprocessPostgreSQLSchema();
}
var schemas =
(
from t in
(
from t in _schema.Tables
select new { t.IsDefaultSchema, t.SchemaName, Table = t, Procedure = (ProcedureSchema)null }
)
.Union
(
from p in _schema.Procedures
select new { p.IsDefaultSchema, p.SchemaName, Table = (TableSchema)null, Procedure = p }
)
group t by new { t.IsDefaultSchema, t.SchemaName } into gr
orderby !gr.Key.IsDefaultSchema, gr.Key.SchemaName
select new
{
gr.Key,
Tables = gr.Where(t => t.Table != null).Select(t => t.Table).ToList(),
Procedures = gr.Where(t => t.Procedure != null).Select(t => t.Procedure).ToList(),
}
)
.ToList();
foreach (var s in schemas)
{
var items = new List <ExplorerItem>();
if (s.Tables.Any(t => !t.IsView && !t.IsProcedureResult))
{
items.Add(GetTables("Tables", ExplorerIcon.Table, s.Tables.Where(t => !t.IsView && !t.IsProcedureResult)));
}
if (s.Tables.Any(t => t.IsView))
{
items.Add(GetTables("Views", ExplorerIcon.View, s.Tables.Where(t => t.IsView)));
}
if (!_cxInfo.DynamicSchemaOptions.ExcludeRoutines && s.Procedures.Any(p => p.IsLoaded && !p.IsFunction))
{
items.Add(GetProcedures(
"Stored Procs",
ExplorerIcon.StoredProc,
s.Procedures.Where(p => p.IsLoaded && !p.IsFunction).ToList()));
}
if (s.Procedures.Any(p => p.IsLoaded && p.IsTableFunction))
{
items.Add(GetProcedures(
"Table Functions",
ExplorerIcon.TableFunction,
s.Procedures.Where(p => p.IsLoaded && p.IsTableFunction).ToList()));
}
if (s.Procedures.Any(p => p.IsFunction && !p.IsTableFunction))
{
items.Add(GetProcedures(
"Scalar Functions",
ExplorerIcon.ScalarFunction,
s.Procedures.Where(p => p.IsFunction && !p.IsTableFunction).ToList()));
}
if (schemas.Count == 1)
{
foreach (var item in items)
{
yield return(item);
}
}
else
{
yield return(new ExplorerItem(
s.Key.SchemaName.IsNullOrEmpty() ? s.Key.IsDefaultSchema ? "(default)" : "empty" : s.Key.SchemaName,
ExplorerItemKind.Schema,
ExplorerIcon.Schema)
{
Children = items
});
}
}
Code
.AppendLine(" }")
.AppendLine(_classCode.ToString())
.AppendLine("}")
;
#if DEBUG
Debug.WriteLine(Code.ToString());
#endif
}
void CodeTable(StringBuilder classCode, TableSchema table, bool addTableAttribute)
{
classCode.AppendLine();
table.TypeName = GetName(_existingMemberNames, table.TypeName);
if (addTableAttribute)
{
classCode.Append($" [Table(Name={CSharpTools.ToStringLiteral(table.TableName)}");
if (table.SchemaName.NotNullNorEmpty())
{
classCode.Append($", Schema={CSharpTools.ToStringLiteral(table.SchemaName)}");
}
classCode.AppendLine(")]");
}
classCode
.AppendLine($" public class {table.TypeName}")
.AppendLine(" {")
;
foreach (var c in table.Columns)
{
classCode
.Append($" [Column({CSharpTools.ToStringLiteral(c.ColumnName)}), ")
.Append(c.IsNullable ? "Nullable" : "NotNull");
if (c.IsPrimaryKey)
{
classCode.Append($", PrimaryKey({c.PrimaryKeyOrder})");
}
if (c.IsIdentity)
{
classCode.Append(", Identity");
}
classCode.AppendLine("]");
var memberType = UseProviderSpecificTypes ? (c.ProviderSpecificType ?? c.MemberType) : c.MemberType;
classCode.AppendLine($" public {memberType} {c.MemberName} {{ get; set; }}");
}
foreach (var key in table.ForeignKeys)
{
classCode
.Append(" [Association(")
.Append($"ThisKey={CSharpTools.ToStringLiteral((key.ThisColumns.Select(c => c.MemberName)).Join(", "))}")
.Append($", OtherKey={CSharpTools.ToStringLiteral((key.OtherColumns.Select(c => c.MemberName)).Join(", "))}")
.Append($", CanBeNull={(key.CanBeNull ? "true" : "false")}")
;
if (key.BackReference != null)
{
if (key.KeyName.NotNullNorEmpty())
{
classCode.Append($", KeyName={CSharpTools.ToStringLiteral(key.KeyName)}");
}
if (key.BackReference.KeyName.NotNullNorEmpty())
{
classCode.Append($", BackReferenceName={CSharpTools.ToStringLiteral(key.BackReference.MemberName)}");
}
}
else
{
classCode.Append(", IsBackReference=true");
}
classCode.AppendLine(")]");
var typeName = key.AssociationType == AssociationType.OneToMany
? $"List<{key.OtherTable.TypeName}>"
: key.OtherTable.TypeName;
classCode.AppendLine($" public {typeName} {key.MemberName} {{ get; set; }}");
}
classCode.AppendLine(" }");
}
void CodeProcedure(StringBuilder code, ProcedureSchema p, string sprocSqlName)
{
code.AppendLine();
var spName = CSharpTools.ToStringLiteral(sprocSqlName);
if (p.IsTableFunction)
{
code.Append($" [Sql.TableFunction(Name={CSharpTools.ToStringLiteral(p.ProcedureName)}");
if (p.SchemaName != null)
{
code.Append($", Schema={CSharpTools.ToStringLiteral(p.SchemaName)})");
}
code
.AppendLine("]")
.Append($" public ITable<{p.ResultTable?.TypeName}>")
;
}
else if (p.IsAggregateFunction)
{
var inputs = p.Parameters.Where(pr => !pr.IsResult).ToArray();
p.Parameters.RemoveAll(parameter => !parameter.IsResult);
p.Parameters.Add(new ParameterSchema()
{
ParameterType = "IEnumerable<TSource>",
ParameterName = "src"
});
foreach (var input in inputs.Where(pr => !pr.IsResult))
{
p.Parameters.Add(new ParameterSchema()
{
ParameterType = $"Expression<Func<TSource, {input.ParameterType}>>",
ParameterName = $"{input.ParameterName}"
});
}
p.MemberName += "<TSource>";
code
.Append($" [Sql.Function(Name={spName}, ServerSideOnly=true, IsAggregate = true")
.Append(inputs.Length > 0 ? $", ArgIndices = new[] {{ {string.Join(", ", Enumerable.Range(0, inputs.Length))} }}" : string.Empty)
.AppendLine(")]")
.Append($" public static {p.Parameters.Single(pr => pr.IsResult).ParameterType}");
}
else if (p.IsFunction)
{
code
.AppendLine($" [Sql.Function(Name={spName}, ServerSideOnly=true)]")
.Append($" public static {p.Parameters.Single(pr => pr.IsResult).ParameterType}");
}
else
{
var type = p.ResultTable == null ? "int" : $"IEnumerable<{p.ResultTable.TypeName}>";
code.Append($" public {type}");
}
code
.Append($" {p.MemberName}(")
.Append(p.Parameters
.Where(pr => !pr.IsResult)
.Select(pr => $"{(pr.IsOut ? pr.IsIn ? "ref " : "out " : "")}{pr.ParameterType} {pr.ParameterName}")
.Join(", "))
.AppendLine(")")
.AppendLine(" {")
;
if (p.IsTableFunction)
{
code
.Append($" return this.GetTable<{p.ResultTable?.TypeName}>(this, (MethodInfo)MethodBase.GetCurrentMethod()")
.AppendLine(p.Parameters.Count == 0 ? ");" : ",")
;
for (var i = 0; i < p.Parameters.Count; i++)
{
code.AppendLine($" {p.Parameters[i].ParameterName}{(i + 1 == p.Parameters.Count ? ");" : ",")}");
}
}
else if (p.IsFunction)
{
code.AppendLine(" throw new InvalidOperationException();");
}
else
{
var inputParameters = p.Parameters.Where(pp => pp.IsIn).ToList();
var outputParameters = p.Parameters.Where(pp => pp.IsOut).ToList();
var inOrOutputParameters = p.Parameters.Where(pp => pp.IsIn || pp.IsOut).ToList();
spName += inOrOutputParameters.Count == 0 ? ");" : ",";
var retName = "__ret__";
var retNo = 0;
while (p.Parameters.Any(pp => pp.ParameterName == retName))
{
retName = "__ret__" + ++retNo;
}
var hasOut = outputParameters.Any(pr => pr.IsOut);
var prefix = hasOut ? $"var {retName} =" : "return";
if (p.ResultTable == null)
{
code.Append($" {prefix} this.ExecuteProc({spName}");
}
else
{
var hashSet = new HashSet <string>();
var hasDuplicate = false;
foreach (var column in p.ResultTable.Columns)
{
if (hashSet.Contains(column.ColumnName))
{
hasDuplicate = true;
break;
}
hashSet.Add(column.ColumnName);
}
if (hasDuplicate)
{
code.AppendLine(" var ms = this.MappingSchema;");
code.AppendLine($" {prefix} this.QueryProc(dataReader =>");
code.AppendLine($" new {p.ResultTable.TypeName}");
code.AppendLine(" {");
var n = 0;
foreach (var c in p.ResultTable.Columns)
{
code.AppendLine($" {c.MemberName} = Converter.ChangeTypeTo<{c.MemberType}>(dataReader.GetValue({n++}), ms),");
}
code.AppendLine(" },");
code.AppendLine($" {spName}");
}
else
{
code.AppendLine($" {prefix} this.QueryProc<{p.ResultTable.TypeName}>({spName}");
}
}
for (var i = 0; i < inOrOutputParameters.Count; i++)
{
var pr = inOrOutputParameters[i];
var str = string.Format(
!pr.IsIn && pr.IsOut
? " new DataParameter({0}, null, DataType.{2})"
: " new DataParameter({0}, {1}, DataType.{2})",
CSharpTools.ToStringLiteral(pr.SchemaName),
pr.ParameterName,
pr.DataType);
if (pr.IsOut)
{
str += " { Direction = " + (pr.IsIn ? "ParameterDirection.InputOutput" : "ParameterDirection.Output");
if (pr.Size != null && pr.Size.Value != 0)
{
str += ", Size = " + pr.Size.Value;
}
str += " }";
}
// we need to call ToList(), because otherwise output parameters will not be updated
// with values. See https://msdn.microsoft.com/en-us/library/ms971497#gazoutas_topic6
str += i + 1 == inOrOutputParameters.Count ? (outputParameters.Count > 0 && p.ResultTable != null ? ").ToList();" : ");") : ",";
code.AppendLine(str);
}
if (hasOut)
{
code.AppendLine();
foreach (var pr in p.Parameters.Where(_ => _.IsOut))
{
var str = $" {pr.ParameterName} = Converter.ChangeTypeTo<{pr.ParameterType}>(((IDbDataParameter)this.Command.Parameters[\"{pr.SchemaName}\"]).Value);";
code.AppendLine(str);
}
code
.AppendLine()
.AppendLine($" return {retName};")
;
}
}
code.AppendLine(" }");
}
