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