//--------------------------------------------------------------------------------------------------------------------
protected void DoStoredProc(Program p, string fqstoredprocedure)
{
Helper h = new Helper();
StoredProcedureParameterInfo sppi = new StoredProcedureParameterInfo();
StoredProcedureResultsetInfo sprsi = new StoredProcedureResultsetInfo();
var schemastoredprocedure = h.SplitSchemaFromTable(fqstoredprocedure);
string thedatabase = h.GetCsharpClassName(null, null, p._databaseName);
string csharpnamespace = p._namespace + "." + p._storedProcsSubDirectory;
string csharpstoredproc = h.GetCsharpClassName(p._prefixObjectsWithSchema, schemastoredprocedure.Item1, schemastoredprocedure.Item2);
string csharpfile = p._directory + @"\" + p._storedProcsSubDirectory + @"\" + csharpstoredproc + ".cs";
string thefactoryclass = "StoredProcedureFactory";
string csharpfactoryfile = p._directory + @"\" + p._storedProcsSubDirectory + @"\" + thefactoryclass + ".cs";
// do each stored proc in a separate file, but same factory class
h.MessageVerbose("[{0}]", csharpfile);
// check for dud parameters - cursor, table - ignore the sp if so
if (sppi.HasDudParameterStoredProcedure(fqstoredprocedure))
{
h.MessageVerbose("[{0}] Ignoring stored procedure because it has dud parameters.", fqstoredprocedure);
return;
}
// get sp parameters
List <string> parameters = new List <string>();
parameters.Add("connˡ");
var spparameters = sppi.GetStoredProcedureParameterInfo(fqstoredprocedure);
if (spparameters != null)
{
foreach (var spparameter in spparameters)
{
parameters.Add(spparameter.Name);
}
}
parameters.Add("tranˡ");
// the results sets
var rsi = sprsi.GetResultsetInfo(fqstoredprocedure);
// write out the stored proc wrapper to its file
using (StreamWriter sw = new StreamWriter(csharpfile, false, UTF8Encoding.UTF8))
{
int tab = 0;
// header
h.WriteCodeGenHeader(sw);
h.WriteUsing(sw, p._namespace);
// namespace
using (NamespaceBlock nsb = new NamespaceBlock(sw, tab++, csharpnamespace))
{
using (ClassBlock cb = new ClassBlock(
sw,
tab++,
thefactoryclass,
"acr.StoredProcedureFactoryBase< " + "ns." + p._databaseSubDirectory + "." + thedatabase + "DatabaseSingletonHelper, " + "ns." + p._databaseSubDirectory + "." + thedatabase + "Database >"))
{
// execute block
h.MessageVerbose("[{0}].[{1}]", csharpnamespace, csharpstoredproc);
using (StoredProcedureFactoryExecuteBlock mb = new StoredProcedureFactoryExecuteBlock(
p,
sw,
tab,
fqstoredprocedure,
csharpstoredproc,
parameters,
sppi,
rsi))
{}
} // end class
} // end namespace
} // eof
// write out the classes for stored proc result sets, if any
int i = 0;
foreach (var rs in rsi.Resultsets)
{
i++;
string therecordsetclass = csharpstoredproc + h.IdentifierSeparator + "rs" + i;
string csharprecordsetfile = p._directory + @"\" + p._storedProcsSubDirectory + @"\" + csharpstoredproc + ".rs" + i + ".cs";
h.MessageVerbose("[{0}]", csharprecordsetfile);
using (StreamWriter sw = new StreamWriter(csharprecordsetfile, false, UTF8Encoding.UTF8))
{
int tab = 0;
// header
h.WriteCodeGenHeader(sw);
h.WriteUsing(sw, p._namespace);
// namespace
using (NamespaceBlock nsb = new NamespaceBlock(sw, tab++, csharpnamespace))
{
using (ClassBlock cb = new ClassBlock(sw, tab++, therecordsetclass, "acr.RowBase"))
{
h.MessageVerbose("[{0}].[{1}] row", csharpnamespace, therecordsetclass);
using (StoredProcedureRowConstructorBlock mb = new StoredProcedureRowConstructorBlock(sw, tab, therecordsetclass, rs.Columns))
{}
} // end class
} // end namespace
} // eof
}
} // end do sp
public StoredProcedureFactoryExecuteBlock(Program p,
StreamWriter sw,
int tabs,
string fqstoredprocedure,
string csharpstoredproc,
List <string> parameters,
StoredProcedureParameterInfo sppi,
ResultsetInfo rsi)
: base(sw, tabs)
{
Helper h = new Helper();
var schemastoredprocedure = h.SplitSchemaFromTable(fqstoredprocedure);
h.Write(sw, tabs, "public int " + csharpstoredproc);
h.Write(sw, tabs, "(");
// function interface
int pos = 1;
foreach (string parameter in parameters)
{
if (parameter == "tranˡ")
{
continue;
}
string csharpname = h.GetCsharpColumnName(parameter, "");
string csharptype = "";
if (parameter == "connˡ")
{
csharptype = "sds.SqlConnection";
}
else
if (parameter == "tranˡ")
{
csharptype = "sds.SqlTransaction";
}
else
{
var pi = sppi.GetStoredProcedureParameterInfo(fqstoredprocedure, parameter);
if (pi.IsTableType)
{
Tuple <string, string> schematabletype = h.SplitSchemaFromTable(pi.Type);
string csharptabletype = h.GetCsharpClassName(p._prefixObjectsWithSchema, schematabletype.Item1, schematabletype.Item2);
csharptype = "scg.List<" + csharptabletype + h.IdentifierSeparator + "tt>";
}
else
{
csharptype = h.GetCsharpColumnTypeForStoredProcedure(pi.Type);
}
}
string inout = "";
if (parameter == "connˡ")
{
inout = "";
}
else
if (parameter == "tranˡ")
{
inout = "";
}
else
{
var pi = sppi.GetStoredProcedureParameterInfo(fqstoredprocedure, parameter);
if (pi.IsOutput)
{
inout = "ref ";
}
}
h.Write(sw, tabs + 1, inout + csharptype + " " + csharpname + ", ");
pos++;
}
// do resultsets or dataset
if (rsi.ErrorMessage.Length > 0)
{
h.Write(sw, tabs + 1, "out sd.DataSet dsˡ, ");
}
else
if (rsi.Resultsets.Count > 0)
{
int i = 0;
foreach (Resultset rs in rsi.Resultsets)
{
i++;
string ignoreRecordset = "";
if (rsi.IgnoredResultsets.Contains(i))
{
ignoreRecordset = "//";
}
string therecordsetclass = csharpstoredproc + h.IdentifierSeparator + "rs" + i;
h.Write(sw, tabs + 1, ignoreRecordset + "out scg.List<" + therecordsetclass + "> rsˡ" + i + ", ");
}
}
// tran - last parameter
h.Write(sw, tabs + 1, "sds.SqlTransaction tranˡ = null");
h.Write(sw, tabs, ")");
h.Write(sw, tabs, "{");
h.Write(sw, tabs + 1, "const string schemaˡ = \"" + schemastoredprocedure.Item1 + "\" ; ");
h.Write(sw, tabs + 1, "const string spˡ = \"" + schemastoredprocedure.Item2 + "\" ; ");
h.Write(sw, tabs + 1, " ");
// add the parameters
h.Write(sw, tabs + 1, "scg.List<sds.SqlParameter> parametersˡ = new scg.List<sds.SqlParameter>() ;");
foreach (string parameter in parameters)
{
if (parameter == "connˡ" || parameter == "tranˡ" || parameter == "rcˡ")
{
continue;
}
var pi = sppi.GetStoredProcedureParameterInfo(fqstoredprocedure, parameter);
string csharpname = h.GetCsharpColumnName(parameter, "");
string sqlparameter = "paramˡ" + csharpname;
if (pi.IsTableType) // table type
{
Tuple <string, string> schematabletype = h.SplitSchemaFromTable(pi.Type);
string csharptabletype = h.GetCsharpClassName(p._prefixObjectsWithSchema, schematabletype.Item1, schematabletype.Item2);
h.Write(sw, tabs + 1, "sds.SqlParameter "
+ sqlparameter
+ " = base.AddParameterTableTypeˡ( parametersˡ, \"@"
+ parameter
+ "\", this." + csharpstoredproc + h.IdentifierSeparator + csharpname + "ˡ( " + csharpname + " ), "
+ "\""
+ pi.Type
+ "\""
+ " ) ; ");
}
else
{
try // normal sql type
{
SqlDbType sqldbtype = h.GetSqlDbTypeForStoredProcedure(pi.Type);
h.Write(sw, tabs + 1, "sds.SqlParameter "
+ sqlparameter
+ " = base.AddParameterˡ( parametersˡ, \"@"
+ parameter
+ "\", "
+ csharpname
+ ", sd.SqlDbType."
+ sqldbtype.ToString()
+ ", "
+ (!pi.IsOutput).ToString().ToLower()
+ ", "
+ (pi.MaxLength == null ? "null" : pi.MaxLength.ToString())
+ ", "
+ (pi.Precision == null ? "null" : pi.Precision.ToString())
+ ", "
+ (pi.Scale == null ? "null" : pi.Scale.ToString())
+ " ) ; ");
}
catch (Exception) // try udt type
{
string udtType = h.GetSqlDbTypeForStoredProcedureUdt(pi.Type);
h.Write(sw, tabs + 1, "sds.SqlParameter "
+ sqlparameter
+ " = base.AddParameterUdtˡ( parametersˡ, \"@"
+ parameter
+ "\", "
+ csharpname
+ ", "
+ "\""
+ udtType
+ "\""
+ ", "
+ (!pi.IsOutput).ToString().ToLower()
+ ", "
+ (pi.MaxLength == null ? "null" : pi.MaxLength.ToString())
+ " ) ; ");
}
}
}
// add return value parameter
h.Write(sw, tabs + 1, "sds.SqlParameter paramˡrcˡ = base.AddParameterReturnValueˡ( parametersˡ, \"@rcˡ\" ) ; ");
// the actual execute
h.Write(sw, tabs + 1, " ");
if (rsi.ErrorMessage.Length > 0)
{
h.Write(sw, tabs + 1, "dsˡ = base.Executeˡ( connˡ, tranˡ, schemaˡ, spˡ, parametersˡ ) ;");
}
else
{
h.Write(sw, tabs + 1, "sd.DataSet dsˡ = base.Executeˡ( connˡ, tranˡ, schemaˡ, spˡ, parametersˡ ) ;");
}
// the returned values
h.Write(sw, tabs + 1, " ");
foreach (string parameter in parameters)
{
if (parameter == "connˡ" || parameter == "tranˡ" || parameter == "rcˡ")
{
continue;
}
var pi = sppi.GetStoredProcedureParameterInfo(fqstoredprocedure, parameter);
if (!pi.IsOutput)
{
continue;
}
string csharpname = h.GetCsharpColumnName(parameter, "");
string sqlparameter = "paramˡ" + csharpname;
string csharptype = h.GetCsharpColumnTypeForStoredProcedure(pi.Type);
h.Write(sw, tabs + 1, csharpname + " = base.GetParameterValueˡ<" + csharptype + ">( " + sqlparameter + " ) ;");
}
// the recordsets if any
if (rsi.Resultsets.Count > 0)
{
int i = 0;
int j = 0;
foreach (Resultset rs in rsi.Resultsets)
{
i++;
string ignoreRecordset = "";
if (rsi.IgnoredResultsets.Contains(i))
{
ignoreRecordset = "//";
}
else
{
j++;
}
string therecordsetclass = csharpstoredproc + h.IdentifierSeparator + "rs" + i;
h.Write(sw, tabs + 1, ignoreRecordset + "rsˡ" + i + " = base.ToRecordsetˡ<" + therecordsetclass + ">( dsˡ, " + j + " ) ;");
}
h.Write(sw, tabs + 1, " ");
}
// finish up - add return value parameter
h.Write(sw, tabs + 1, "return base.GetParameterValueˡ<int>( paramˡrcˡ ) ;");
h.Write(sw, tabs, "}");
// table type paramater helper function
foreach (string parameter in parameters)
{
if (parameter == "connˡ" || parameter == "tranˡ" || parameter == "rcˡ")
{
continue;
}
var pi = sppi.GetStoredProcedureParameterInfo(fqstoredprocedure, parameter);
if (!pi.IsTableType)
{
continue; // table types only
}
string csharpname = h.GetCsharpColumnName(parameter, "");
Tuple <string, string> schematabletype = h.SplitSchemaFromTable(pi.Type);
string csharptabletype = h.GetCsharpClassName(p._prefixObjectsWithSchema, schematabletype.Item1, schematabletype.Item2);
h.Write(sw, tabs, " ");
h.Write(sw, tabs, "protected object " + csharpstoredproc + h.IdentifierSeparator + csharpname + "ˡ( scg.List<" + csharptabletype + h.IdentifierSeparator + "tt> list )");
h.Write(sw, tabs, "{");
h.Write(sw, tabs + 1, "if ( list == null ) return null ;");
h.Write(sw, tabs + 1, "if ( list.Count == 0 ) return null ;");
h.Write(sw, tabs + 1, "return new ns.storedProcedure." + csharptabletype + h.IdentifierSeparator + "ttlist( list ) ;");
h.Write(sw, tabs, "}");
}
} // end constructor
protected void Init()
{
Helper h = new Helper();
Settings settings = new Settings();
_codegenFile = _args[0];
h.MessageVerbose("Config file [{0}]", _codegenFile);
// load the xml configuration file
_codegen.Load(_codegenFile);
// basic codegen info
_namespace = _codegen.SelectSingleNode("/CodeGen/Namespace").InnerText;
h.MessageVerbose("Namespace [{0}]", _namespace);
_resourceNamespace = _codegen.SelectSingleNode("/CodeGen/ResourceNamespace").InnerText;
h.MessageVerbose("ResourceNamespace [{0}]", _resourceNamespace);
_directory = _codegen.SelectSingleNode("/CodeGen/Directory").InnerText;
h.MessageVerbose("Directory [{0}]", _directory);
_connectionString = _codegen.SelectSingleNode("/CodeGen/ConnectionString").InnerText;
h.MessageVerbose("ConnectionString [{0}]", _connectionString);
_concurrencyColumn = _codegen.SelectSingleNode("/CodeGen/ConcurrencyColumn").InnerText;
h.MessageVerbose("ConcurrencyColumn [{0}]", _concurrencyColumn);
string prefixes = _codegen.SelectSingleNode("/CodeGen/PrefixObjectsWithSchema").InnerText;
h.MessageVerbose("PrefixObjectsWithSchema [{0}]", prefixes);
_prefixObjectsWithSchema = new List <string>(prefixes.ToUpper().Split(','));
// items related to auto codegened unit test
_unitTestNamespace = _codegen.SelectSingleNode("/CodeGen/UnitTest/Namespace").InnerText;
h.MessageVerbose("Unit test namespace [{0}]", _unitTestNamespace);
_unitTestDirectory = _codegen.SelectSingleNode("/CodeGen/UnitTest/Directory").InnerText;
h.MessageVerbose("Unit test directory [{0}]", _unitTestDirectory);
_unitTestTableNamespace = _codegen.SelectSingleNode("/CodeGen/UnitTest/TableNamespace").InnerText;
h.MessageVerbose("Unit test table namespace [{0}]", _unitTestTableNamespace);
_unitTestTableNamespacePrefix = _codegen.SelectSingleNode("/CodeGen/UnitTest/TableNamespacePrefix").InnerText;
h.MessageVerbose("Unit test table namespace prefix [{0}]", _unitTestTableNamespacePrefix);
_threads = settings.Threads;
h.MessageVerbose("Threads [{0}]", _threads);
_tableSubDirectory = _codegen.SelectSingleNode("/CodeGen/Tables/@SubDirectory").Value;
h.MessageVerbose("Table subdirectory [{0}]", _tableSubDirectory);
_viewSubDirectory = _codegen.SelectSingleNode("/CodeGen/Views/@SubDirectory").Value;
h.MessageVerbose("View subdirectory [{0}]", _viewSubDirectory);
_querySubDirectory = _codegen.SelectSingleNode("/CodeGen/Queries/@SubDirectory").Value;
h.MessageVerbose("Query subdirectory [{0}]", _querySubDirectory);
_queries = _codegen.SelectNodes("/CodeGen/Queries/Query");
// connect to the database so we can do some code jen !
_connection = new SqlConnection(_connectionString);
_connection.Open();
// get all the database metadata that we may need !
_di.CreateDatabaseInfo(_connection);
_databaseName = _di.GetDatabaseName();
h.MessageVerbose("DatabaseName [{0}]", _databaseName);
DataSetHelper dsh = new DataSetHelper(_di.GetDatabaseInfo());
h.MessageVerbose("Database information:\n\n{0}", dsh.ToString());
ColumnInfo ci = new ColumnInfo();
ci.CreateColumnInfo(_connection, _di.Tables.Get(), _di.Views.Get(), _directory, _querySubDirectory, _queries);
h.MessageVerbose("Table, view and query column information:\n\n{0}", ci.ToString());
StoredProcedureParameterInfo sppi = new StoredProcedureParameterInfo();
sppi.CreateStoredProcedureInfo(_di);
StoredProcedureResultsetInfo sprsi = new StoredProcedureResultsetInfo();
sprsi.CreateStoredProcedureInfo(_connection, _di, _codegen, sppi);
h.MessageVerbose("Stored procedure resultset information:\n\n{0}", sprsi.ToString());
}
//--------------------------------------------------------------------------------------------------------------------
public void CreateStoredProcedureInfo(SqlConnection conn, DatabaseInfo di, XmlDocument xmlconfig, StoredProcedureParameterInfo sppi)
{
Helper h = new Helper();
// already done ?
if (__dictionary.Count > 0)
{
return;
}
foreach (string storedprocedure in di.StoredProcedures.Get())
{
__maxWidthStoredProcedure = Math.Max(__maxWidthStoredProcedure, storedprocedure.Length);
StringBuilder bob = new StringBuilder();
var parameters = sppi.GetStoredProcedureParameterInfo(storedprocedure);
if (parameters != null)
{
int i = 1;
foreach (var parameter in parameters)
{
bob.AppendLine(string.Format("declare @p{0} {1}", i++, this.GetTsqLocalVariableType(parameter.Type)));
}
}
bob.AppendLine();
bob.AppendLine("set fmtonly on");
bob.Append(string.Format("exec [{0}]", storedprocedure.Replace(".", "].[")));
if (parameters != null)
{
int i = 1;
foreach (var parameter in parameters)
{
bob.Append(string.Format(", @p{0}", i++));
}
}
bob.AppendLine();
bob.AppendLine("set fmtonly off");
string sql = bob.ToString();
sql = sql.Replace("],", "] ");
h.MessageVerbose(sql);
DataSet ds = new DataSet();
string errorMessage = this.GetMetaDataSets(conn, sql, ds);
if (errorMessage.Length > 0)
{
h.MessageVerbose("ERRORMESSAGE:" + errorMessage);
}
ResultsetInfo rsi = new ResultsetInfo(errorMessage, ds);
__dictionary.Add(storedprocedure, rsi);
foreach (DataTable dt in rsi.DataSet.Tables)
{
Resultset rs = new Resultset();
foreach (DataColumn dc in dt.Columns)
{
string column = dc.ColumnName;
string type = dc.DataType.ToString();
rs.Columns.Add(new Tuple <string, string>(column, type));
__maxWidthColumn = Math.Max(__maxWidthColumn, column.Length);
__maxWidthType = Math.Max(__maxWidthType, type.Length);
}
rsi.Resultsets.Add(rs);
}
// recordsets to be ignoreed
string xpath = string.Format("/CodeGen/StoredProcs/StoredProc[ @Name='{0}' ]/@IgnoreRecordsets", storedprocedure);
var node = xmlconfig.SelectSingleNode(xpath);
if (node != null)
{
string items = node.Value.Trim();
if (items.Length > 0)
{
rsi.IgnoredResultsets.AddRange(items.Split(',').ToList().ConvertAll <int>(i => int.Parse(i))); // convert string list to int list
}
}
} // end foreach sp
}
