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