public virtual string Usings(CodeGenerationContext ctx)
{
return(@"using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
");
}
public virtual string MakeExecuteNonQueryWithConn(CodeGenerationContext ctx)
{
StringBuilder code = new StringBuilder();
// ExecuteScalar() with connection
code.AppendLine("public virtual int ExecuteNonQuery(" + ctx.MethodSignature + "SqlConnection conn){");
code.AppendLine("SqlCommand cmd = conn.CreateCommand();");
code.AppendLine("loadCommandText(cmd);");
code.Append(MakeParamLoadingCode(ctx));
code.AppendLine("return cmd.ExecuteNonQuery();");
code.AppendLine("}");
// close ExecuteScalar()
return(code.ToString());
}
public virtual string MakeGetOneWithoutConn(CodeGenerationContext ctx)
{
char[] spaceComma = new char[] { ',', ' ' };
StringBuilder code = new StringBuilder();
// GetOne without connection
code.AppendLine("public virtual " + ctx.ResultClassName + " GetOne(" + ctx.MethodSignature.Trim(spaceComma) + "){");
code.AppendLine("using (SqlConnection conn = new SqlConnection(QfRuntimeConnection.GetConnectionString()))");
code.AppendLine("{");
code.AppendLine("conn.Open();");
code.AppendLine("return GetOne(" + ctx.CallingArgs + ");");
code.AppendLine("}");
code.AppendLine("}");
return(code.ToString());
}
public virtual string MakeExecuteScalarWithoutConn(CodeGenerationContext ctx)
{
char[] spaceComma = new char[] { ',', ' ' };
StringBuilder code = new StringBuilder();
//ExecuteScalar without connection
code.AppendLine("public virtual " + ctx.ResultFields[0].TypeCs + " ExecuteScalar(" + ctx.MethodSignature.Trim(spaceComma) + "){");
code.AppendLine("using (IDbConnection conn = QfRuntimeConnection.GetConnection())");
code.AppendLine("{");
code.AppendLine("conn.Open();");
code.AppendLine("return ExecuteScalar(" + ctx.CallingArgs + ");");
code.AppendLine("}");
code.AppendLine("}");
return(code.ToString());
}
public virtual string MakeExecuteNonQueryWithoutConn(CodeGenerationContext ctx)
{
char[] spaceComma = new char[] { ',', ' ' };
StringBuilder code = new StringBuilder();
//ExecuteScalar without connection
code.AppendLine("public virtual int ExecuteNonQuery(" + ctx.MethodSignature.Trim(spaceComma) + "){");
code.AppendLine("using (SqlConnection conn = new SqlConnection(QfRuntimeConnection.GetConnectionString()))");
code.AppendLine("{");
code.AppendLine("conn.Open();");
code.AppendLine("return ExecuteNonQuery(" + ctx.CallingArgs + ");");
code.AppendLine("}");
code.AppendLine("}");
return(code.ToString());
}
public virtual string MakeExecuteWithoutConn(CodeGenerationContext ctx)
{
StringBuilder code = new StringBuilder();
char[] spaceComma = new char[] { ',', ' ' };
// Execute method, without connection
code.AppendLine("public virtual List<" + ctx.ResultClassName + "> Execute(" + ctx.MethodSignature.Trim(spaceComma) + "){");
code.AppendLine("using (IDbConnection conn = QfRuntimeConnection.GetConnection())");
code.AppendLine("{");
code.AppendLine("conn.Open();");
code.AppendLine("return Execute(" + ctx.CallingArgs + ").ToList();");
code.AppendLine("}");
code.AppendLine("}");
return(code.ToString());
}
public virtual string MakeGetOneWithConn(CodeGenerationContext ctx)
{
StringBuilder code = new StringBuilder();
// GetOne() with connection
code.AppendLine("public virtual " + ctx.ResultClassName + " GetOne(" + ctx.MethodSignature + "SqlConnection conn)");
code.AppendLine("{");
code.AppendLine("var all = Execute(" + ctx.CallingArgs + ");");
code.AppendLine("using (IEnumerator<" + ctx.ResultClassName + "> iter = all.GetEnumerator())");
code.AppendLine("{");
code.AppendLine("iter.MoveNext();");
code.AppendLine("return iter.Current;");
code.AppendLine("}");
code.AppendLine("}"); // close GetOne() method
return(code.ToString());
}
public virtual string MakeLoadCommandTextMethod(CodeGenerationContext ctx)
{
StringBuilder code = new StringBuilder();
// private load command text
code.AppendLine("private void loadCommandText(SqlCommand cmd){");
code.AppendLine("Stream strm = typeof(" + ctx.ResultClassName + ").Assembly.GetManifestResourceStream(\"" + ctx.NameAndPathForManifestStream + "\");");
code.AppendLine("string queryText = new StreamReader(strm).ReadToEnd();");
code.AppendLine("#if DEBUG");
code.AppendLine("//Comments inverted at runtime in debug, pre-build in release");
code.AppendLine("queryText = queryText.Replace(\"--designTime\", \"/*designTime\");");
code.AppendLine("queryText = queryText.Replace(\"--endDesignTime\", \"endDesignTime*/\");");
code.AppendLine("#endif");
code.AppendLine("cmd.CommandText = queryText;");
code.AppendLine("}"); // close method;
return(code.ToString());
}
public Conductor(Document queryDoc)
{
ctx = new CodeGenerationContext(queryDoc);
_tiny = TinyIoCContainer.Current;
// Test this! If I can get source control exclusions working, team members won't get the generated file.
if (!File.Exists(ctx.GeneratedClassFullFilename))
{
File.Create(ctx.GeneratedClassFullFilename);
}
if (GetItemByFilename(queryDoc.ProjectItem.Collection, ctx.GeneratedClassFullFilename) != null)
{
queryDoc.ProjectItem.Collection.AddFromFile(ctx.GeneratedClassFullFilename);
}
// copy namespace of generated partial class from user partial class
}
public virtual string MakeExecuteNonQueryWithConn(CodeGenerationContext ctx)
{
StringBuilder code = new StringBuilder();
// ExecuteScalar() with connection
code.AppendLine("public virtual int ExecuteNonQuery(" + ctx.MethodSignature + "IDbConnection conn){");
code.AppendLine("IDbCommand cmd = conn.CreateCommand();");
code.AppendLine("cmd.CommandText = getCommandText();");
foreach (var qp in ctx.Query.QueryParams)
{
code.AppendLine("AddAParameter(cmd, \"" + qp.DbType + "\", \"" + qp.DbName + "\", " + qp.CSName + ", " + qp.Length + ");");
}
code.AppendLine("return cmd.ExecuteNonQuery();");
code.AppendLine("}");
// close ExecuteScalar()
return(code.ToString());
}
public virtual string MakeExecuteWithConn(CodeGenerationContext ctx)
{
StringBuilder code = new StringBuilder();
// Execute method with connection
code.AppendLine("public virtual IEnumerable<" + ctx.ResultClassName + "> Execute(" + ctx.MethodSignature + "SqlConnection conn){");
code.AppendLine("SqlCommand cmd = conn.CreateCommand();");
code.AppendLine("loadCommandText(cmd);");
code.Append(MakeParamLoadingCode(ctx));
code.AppendLine("using (var reader = cmd.ExecuteReader())");
code.AppendLine("{");
code.AppendLine("while (reader.Read())");
code.AppendLine("{");
code.AppendLine("yield return Create(reader);");
code.AppendLine("}");
code.AppendLine("}");
code.AppendLine("}"); //close Execute() method
return(code.ToString());
}
public string MakeInterface(CodeGenerationContext ctx)
{
char[] spaceComma = new char[] { ',', ' ' };
StringBuilder code = new StringBuilder();
code.AppendLine("public interface I" + ctx.BaseName + "{" + Environment.NewLine);
if (ctx.ResultFields != null && ctx.ResultFields.Count > 0)
{
code.AppendLine("List<" + ctx.ResultClassName + "> Execute(" + ctx.MethodSignature.Trim(spaceComma) + ");");
code.AppendLine("IEnumerable<" + ctx.ResultClassName + "> Execute(" + ctx.MethodSignature + "SqlConnection conn);");
code.AppendLine("" + ctx.ResultClassName + " GetOne(" + ctx.MethodSignature.Trim(spaceComma) + ");");
code.AppendLine("" + ctx.ResultClassName + " GetOne(" + ctx.MethodSignature + "SqlConnection conn);");
code.AppendLine("" + ctx.ResultFields[0].DataType + " ExecuteScalar(" + ctx.MethodSignature.Trim(spaceComma) + ");");
code.AppendLine("" + ctx.ResultFields[0].DataType + " ExecuteScalar(" + ctx.MethodSignature + "SqlConnection conn);");
code.AppendLine("" + ctx.ResultClassName + " Create(IDataRecord record);");
}
code.AppendLine("int ExecuteNonQuery(" + ctx.MethodSignature.Trim(spaceComma) + ");");
code.AppendLine("int ExecuteNonQuery(" + ctx.MethodSignature + "SqlConnection conn);");
code.AppendLine("}"); // close interface;
return(code.ToString());
}
public virtual string MakeExecuteWithConn(CodeGenerationContext ctx)
{
StringBuilder code = new StringBuilder();
// Execute method with connection
code.AppendLine("public virtual IEnumerable<" + ctx.ResultClassName + "> Execute(" + ctx.MethodSignature + "IDbConnection conn){");
code.AppendLine("IDbCommand cmd = conn.CreateCommand();");
code.AppendLine("cmd.CommandText = getCommandText();");
foreach (var qp in ctx.Query.QueryParams)
{
code.AppendLine("AddAParameter(cmd, \"" + qp.DbType + "\", \"" + qp.DbName + "\", " + qp.CSName + ", " + qp.Length + ");");
}
code.AppendLine("using (var reader = cmd.ExecuteReader())");
code.AppendLine("{");
code.AppendLine("while (reader.Read())");
code.AppendLine("{");
code.AppendLine("yield return Create(reader);");
code.AppendLine("}");
code.AppendLine("}");
code.AppendLine("}"); //close Execute() method
return(code.ToString());
}
public virtual string MakeCreateMethod(CodeGenerationContext ctx)
{
StringBuilder code = new StringBuilder();
// Create() method
code.AppendLine("public virtual " + ctx.ResultClassName + " Create(IDataRecord record)");
code.AppendLine("{");
code.AppendLine("var returnVal = new " + ctx.ResultClassName + "();");
int j = 0;
foreach (var col in ctx.QueryFields)
{
code.AppendLine("if(record[" + j + "] != null && record[" + j + "] != DBNull.Value)");
code.AppendLine("returnVal." + col.ColumnName + " = (" + col.CSType + ")record[" + j++ + "];");
}
// call OnLoad method in user's half of partial class
code.AppendLine("returnVal.OnLoad();");
code.AppendLine("return returnVal;");
code.AppendLine("}"); // close method;
return(code.ToString());
}
public string MakeSelfTestMethod(CodeGenerationContext ctx)
{
char[] spaceComma = new char[] { ',', ' ' };
StringBuilder code = new StringBuilder();
code.AppendLine("[Fact]");
code.AppendLine("public void SelfTest()");
code.AppendLine("{");
code.AppendLine("var errors = new List<string>();");
code.AppendLine("var queryText = getCommandText();");
code.AppendLine("// we'll be getting a runtime version with the comments section closed. To run without parameters, open it.");
code.AppendLine("queryText = queryText.Replace(\"/*designTime\", \"-- designTime\");");
code.AppendLine("queryText = queryText.Replace(\"endDesignTime*/\", \"-- endDesignTime\");");
code.AppendLine("var schema = new ADOHelper().GetFields(new QfRuntimeConnection(), queryText);");
for (int i = 0; i < ctx.ResultFields.Count; i++)
{
var col = ctx.ResultFields[i];
code.AppendLine("if (schema[" + i.ToString() + "].DataTypeName != \"" + col.TypeDb + "\")");
code.AppendLine("errors.Add(string.Format(\"Col " + i.ToString() + " (ColName) DB datatype has changed! Was " + col.TypeDb + ". Now {1}\", schema[" + i.ToString() + "].DataTypeName));");
}
code.AppendLine("Assert.Empty(errors);");
code.AppendLine("}");
return(code.ToString());
}
public DesignTimeConnectionString(CodeGenerationContext ctx)
{
_ctx = ctx;
}
public virtual string StartClass(CodeGenerationContext ctx)
{
return(string.Format("public partial class {0} {{" + nl, ctx.ResultClassName));
}
public void ProcessOneQuery(Document queryDoc)
{
_tiny = TinyIoCContainer.Current;
ctx = new CodeGenerationContext(queryDoc);
// Test this! If I can get source control exclusions working, team members won't get the generated file.
if (!File.Exists(ctx.GeneratedClassFullFilename))
{
File.Create(ctx.GeneratedClassFullFilename);
}
if (GetItemByFilename(queryDoc.ProjectItem.Collection, ctx.GeneratedClassFullFilename) != null)
{
queryDoc.ProjectItem.Collection.AddFromFile(ctx.GeneratedClassFullFilename);
}
// copy namespace of generated partial class from user partial class
// backward compatible...
var textDoc = ((TextDocument)ctx.QueryDoc.Object());
textDoc.ReplacePattern("--designTime", "-- designTime");
textDoc.ReplacePattern("--endDesignTime", "-- endDesignTime");
try
{
if (!ctx.DesignTimeConnectionString.IsPresent)
{
_vsOutputWindow.Write(@"QueryFirst would like to help you, but you need to tell it where your DB is.
You can specify the design time connection string in your app or web.config or directly in the query file.
Add these lines to your app or web.config. providerName should be one of System.Data.SqlClient, Npgsql MySql.Data.MySqlClient.
<connectionStrings>
<add name=""QfDefaultConnection"" connectionString=""Data Source = localhost; Initial Catalog = NORTHWND; Integrated Security = SSPI; "" providerName=""System.Data.SqlClient"" />
</ connectionStrings >
or put --QfDefaultConnection=myConnectionString somewhere in your query file.
");
return; // nothing to be done
}
if (!ctx.DesignTimeConnectionString.IsProviderValid)
{
_vsOutputWindow.Write(string.Format(
@"No Implementation of IProvider for providerName {0}.
The query {1} may not run and the wrapper has not been regenerated.",
ctx.DesignTimeConnectionString.v.ProviderName, ctx.BaseName
));
}
// Use QueryFirst within QueryFirst !
// ToDo, to make this work with Postgres, store as ConnectionStringSettings with provider name.
QfRuntimeConnection.CurrentConnectionString = ctx.DesignTimeConnectionString.v.ConnectionString;
var makeSelfTest = ctx.ProjectConfig?.AppSettings["QfMakeSelfTest"] != null && bool.Parse(ctx.ProjectConfig.AppSettings["QfMakeSelfTest"].Value);
var matchInsert = Regex.Match(ctx.Query.Text, "^insert\\s+into\\s+(?<tableName>\\w+)\\.\\.\\.", RegexOptions.IgnoreCase | RegexOptions.Multiline);
var matchUpdate = Regex.Match(ctx.Query.Text, "^update\\s+(?<tableName>\\w+)\\.\\.\\.", RegexOptions.IgnoreCase | RegexOptions.Multiline);
if (matchInsert.Success)
{
var statement = new ScaffoldInsert().ExecuteScalar(matchInsert.Groups["tableName"].Value);
var ep = textDoc.CreateEditPoint();
ep.ReplaceText(ctx.Query.Text.Length, statement, 0);
//ctx.QueryDoc.Save();
}
else if (matchUpdate.Success)
{
var statement = new ScaffoldUpdate().ExecuteScalar(matchUpdate.Groups["tableName"].Value);
var ep = textDoc.CreateEditPoint();
ep.ReplaceText(ctx.Query.Text.Length, statement, 0);
//ctx.QueryDoc.Save();
}
else
{
// Execute query
try
{
// also called in the bowels of schema fetching, for Postgres, because no notion of declarations.
try
{
var undeclared = ctx.Provider.FindUndeclaredParameters(ctx.Query.Text);
var newParamDeclarations = ctx.Provider.ConstructParameterDeclarations(undeclared);
if (!string.IsNullOrEmpty(newParamDeclarations))
{
ctx.Query.ReplacePattern("-- endDesignTime", newParamDeclarations + "-- endDesignTime");
}
}
catch (SqlException ex)
{
if (ex.Message.Contains("sp_describe_undeclared_parameters"))
{
_vsOutputWindow.Write("Unable to find undeclared parameters. You will have to do this yourself.\n");
}
else
{
throw;
}
}
ctx.ResultFields = ctx.Hlpr.GetFields(ctx.DesignTimeConnectionString.v, ctx.Query.Text);
}
catch (Exception ex)
{
StringBuilder bldr = new StringBuilder();
bldr.AppendLine("Error running query.");
bldr.AppendLine();
bldr.AppendLine("/*The last attempt to run this query failed with the following error. This class is no longer synced with the query");
bldr.AppendLine("You can compile the class by deleting this error information, but it will likely generate runtime errors.");
bldr.AppendLine("-----------------------------------------------------------");
bldr.AppendLine(ex.Message);
bldr.AppendLine("-----------------------------------------------------------");
bldr.AppendLine(ex.StackTrace);
bldr.AppendLine("*/");
File.AppendAllText(ctx.GeneratedClassFullFilename, bldr.ToString());
throw;
}
ctx.QueryHasRun = true;
StringBuilder Code = new StringBuilder();
var wrapper = _tiny.Resolve <IWrapperClassMaker>();
var results = _tiny.Resolve <IResultClassMaker>();
Code.Append(wrapper.StartNamespace(ctx));
Code.Append(wrapper.Usings(ctx));
if (makeSelfTest)
{
Code.Append(wrapper.SelfTestUsings(ctx));
}
if (ctx.ResultFields != null && ctx.ResultFields.Count > 0)
{
Code.Append(results.Usings());
}
Code.Append(wrapper.MakeInterface(ctx));
Code.Append(wrapper.StartClass(ctx));
Code.Append(wrapper.MakeExecuteNonQueryWithoutConn(ctx));
Code.Append(wrapper.MakeExecuteNonQueryWithConn(ctx));
Code.Append(wrapper.MakeGetCommandTextMethod(ctx));
Code.Append(ctx.Provider.MakeAddAParameter(ctx));
if (makeSelfTest)
{
Code.Append(wrapper.MakeSelfTestMethod(ctx));
}
if (ctx.ResultFields != null && ctx.ResultFields.Count > 0)
{
Code.Append(wrapper.MakeExecuteWithoutConn(ctx));
Code.Append(wrapper.MakeExecuteWithConn(ctx));
Code.Append(wrapper.MakeGetOneWithoutConn(ctx));
Code.Append(wrapper.MakeGetOneWithConn(ctx));
Code.Append(wrapper.MakeExecuteScalarWithoutConn(ctx));
Code.Append(wrapper.MakeExecuteScalarWithConn(ctx));
Code.Append(wrapper.MakeCreateMethod(ctx));
Code.Append(wrapper.MakeOtherMethods(ctx));
Code.Append(wrapper.CloseClass(ctx));
Code.Append(results.StartClass(ctx));
foreach (var fld in ctx.ResultFields)
{
Code.Append(results.MakeProperty(fld));
}
}
Code.Append(results.CloseClass()); // closes wrapper class if no results !
Code.Append(wrapper.CloseNamespace(ctx));
//File.WriteAllText(ctx.GeneratedClassFullFilename, Code.ToString());
ctx.PutCodeHere.WriteAndFormat(Code.ToString());
var partialClassFile = GetItemByFilename(ctx.QueryDoc.ProjectItem.ProjectItems, ctx.CurrDir + ctx.BaseName + "Results.cs");
new BackwardCompatibility().InjectPOCOFactory(ctx, partialClassFile);
_vsOutputWindow.Write(Environment.NewLine + "QueryFirst generated wrapper class for " + ctx.BaseName + ".sql");
}
}
catch (Exception ex)
{
_vsOutputWindow.Write(ex.TellMeEverything());
}
}
public virtual string MakeOtherMethods(CodeGenerationContext ctx)
{
return("");
}
public virtual string CloseClass(CodeGenerationContext ctx)
{
return("}" + Environment.NewLine);
}
public virtual string StartClass(CodeGenerationContext ctx)
{
return("public class " + ctx.BaseName + "{" + Environment.NewLine);
}
