//-------------------------------------------------------------------------------------------------------------------- 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 }