/// <summary>
/// This method generates code for single stored procedure
/// </summary>
/// <param name="sp"></param>
void CreateCodeForSpDataset(StoredProcedure sp, string class_name, SpStreamHelper helper /*StringBuilder sb2, StringBuilder sb_classes*/)
{
string proc_name = pars.sp_prefix + sp.Name + "_DataSet";
bool returns_data = false;
if (pars.use_sp_with_tables)
{
returns_data = sp.Name.StartsWith(pars.prefix_sp_with_tables);
}
if (!returns_data)
{
return;
}
helper.sb_static.AppendLine(indent + indent + "/// <summary> Wrapper for stored procedure " + sp.Name + " returning DataSet. </summary>");
helper.sb_static.Append(indent + indent + "public static DataSet " + proc_name + "(IDataProviderSp provider");
helper.sb_intf.AppendLine(indent + indent + "/// <summary> Wrapper for stored procedure " + sp.Name + " returning DataSet. </summary>");
helper.sb_intf.Append(indent + indent + "DataSet " + proc_name + "(");
helper.sb_obj.AppendLine(indent + indent + "/// <summary> Wrapper for stored procedure " + sp.Name + " returning DataSet. </summary>");
helper.sb_obj.Append(indent + indent + "public DataSet " + proc_name + "(");
var first = true;
var sbx = new StringBuilder();
foreach (SpParam sparam in sp.parameters)
{
sparam.cs_name = CreateValidCsName(sparam.Name);
if (first)
{
first = false;
}
else
{
helper.sb_intf.Append(", ");
helper.sb_obj.Append(", ");
}
helper.sb_static.AppendLine(", ");
helper.sb_static.Append(indent + indent + indent + (sparam.is_out ? "out " : "") + type_map.GetSpParamType(sparam.type, sparam.is_out) + " " + sparam.cs_name);
helper.sb_intf.Append((sparam.is_out ? "out " : "") + type_map.GetSpParamType(sparam.type, sparam.is_out) + " " + sparam.cs_name);
helper.sb_obj.Append((sparam.is_out ? "out " : "") + type_map.GetSpParamType(sparam.type, sparam.is_out) + " " + sparam.cs_name);
sbx.Append(", " + (sparam.is_out ? "out " : "") + sparam.cs_name);
}
helper.sb_static.AppendLine();
helper.sb_static.AppendLine(indent + indent + ") {");
helper.sb_intf.AppendLine(");");
helper.sb_obj.AppendLine(") { ");
helper.sb_obj.AppendLine(indent + indent + indent + "return " + class_name + "." + proc_name + "(provider" + sbx.ToString() + ");");
helper.sb_obj.AppendLine(indent + indent + "}");
helper.sb_static.AppendLine(indent + indent + indent + "var cmd = provider.CreateCommand();");
helper.sb_static.AppendLine(indent + indent + indent + "cmd.CommandText = provider.DatabasePrefix + provider.SchemaPrefix + \"." + sp.Name + "\";");
helper.sb_static.AppendLine(indent + indent + indent + "cmd.CommandType = CommandType.StoredProcedure;");
foreach (SpParam sparam in sp.parameters)
{
if (sparam.is_out)
{
continue;
}
string s = "AddParameter(cmd, \"@{0}\", DbType.{1}, (CheckNull({2}) ? (object) DBNull.Value : (object) {2}));";
helper.sb_static.AppendLine(indent + indent + indent + string.Format(s, sparam.Name, sparam.type.ToString(), sparam.cs_name));
}
helper.sb_static.AppendLine(indent + indent + indent + "return provider.GetDataSet(cmd);");
helper.sb_static.AppendLine(indent + indent + "}");
helper.sb_static.AppendLine("");
CreateCodeForSpWrapper(sp, class_name, helper /* sb2, sb_classes*/);
}
/// <summary>
/// This method generates code for stored procedures
/// </summary>
void GenerateCodeForSps(StringBuilder sb_root)
{
if (!pars.use_sps)
{
return;
}
Log("Generating code for SPs...");
var helper = new SpStreamHelper();
var tab = Utils.GetSPs(con);
var ar = new List <string>();
foreach (DataRow r in tab.Rows)
{
ar.Add(r["sp_name"].ToString().ToLower());
}
var selected_sps = ar.ToArray();
string class_name = pars.sp_class_name;
helper.sb_static.AppendLine(indent + "/// <summary> Public static class containing stored-procedure wrappers. </summary>");
helper.sb_static.AppendLine(indent + "public static class " + class_name + " {");
helper.sb_static.Append(Utils.LoadEmbededResourceAsString("check_nullable.txt"));
helper.sb_intf.AppendLine(indent + "/// <summary> Public interface containing stored-procedure wrappers. </summary>");
helper.sb_intf.AppendLine(indent + "public partial interface I" + class_name + " {");
helper.sb_obj.AppendLine(indent + "/// <summary> Public class containing stored-procedure wrappers. </summary>");
helper.sb_obj.AppendLine(indent + "public partial class " + class_name + "Obj : I" + class_name + " {");
helper.sb_obj.AppendLine("");
helper.sb_obj.AppendLine(indent + indent + "protected IDataProviderSp provider;");
helper.sb_obj.AppendLine(indent + indent + "public " + class_name + "Obj(IDataProviderSp _provider) { this.provider = _provider; }");
helper.sb_obj.AppendLine("");
helper.sb_obj.Append(Utils.LoadEmbededResourceAsString("check_nullable.txt"));
helper.sb_obj.AppendLine("");
foreach (string sp_name in selected_sps)
{
Log("Generating stored procedure " + sp_name);
var sp = new StoredProcedure();
sp.Name = sp_name;
if (pars.use_sp_with_tables)
{
sp.StoredProcedureReturnsData = sp.Name.StartsWith(pars.prefix_sp_with_tables);
}
var con2 = new SqlConnection();
if (pars.use_username)
{
con2 = Utils.CreateConnection(pars.sql_server_name, pars.schema_name, pars.uid, pars.pwd);
}
else
{
con2 = Utils.CreateConnection(pars.sql_server_name, pars.schema_name);
}
try {
var trans = con2.BeginTransaction();
try {
sp.Load(con2, trans, pars);
} catch (Exception ex) {
System.Diagnostics.Trace.WriteLine("Error while executing stored procedure " + sp.Name + ". " + ex.ToString());
} finally {
trans.Rollback();
}
} finally {
con2.Close();
}
helper.sb_static.AppendLine("\n\n#region " + sp.Name);
helper.sb_intf.AppendLine(indent + indent + "#region " + sp.Name);
helper.sb_obj.AppendLine(indent + indent + "#region " + sp.Name);
CreateCodeForSp(sp, class_name, helper /*sb2, sb3*/);
helper.sb_static.AppendLine("\n\n#endregion");
helper.sb_intf.AppendLine(indent + indent + "#endregion");
helper.sb_obj.AppendLine(indent + indent + "#endregion");
}
helper.sb_static.AppendLine("\n\n#region result-table wrappers");
helper.sb_static.AppendLine(helper.sb_tabs.ToString()); // append classes into static object
helper.sb_static.AppendLine("\n\n#endregion");
helper.sb_static.AppendLine(indent + "}");
helper.sb_obj.AppendLine(indent + "}");
helper.sb_intf.AppendLine(indent + "}");
sb_root.AppendLine(helper.sb_static.ToString()); // append interface
sb_root.AppendLine(helper.sb_intf.ToString()); // append interface
sb_root.AppendLine(helper.sb_obj.ToString()); // append object implementing interface
}
/// <summary>
/// This method generates code for single stored procedure
/// </summary>
void CreateCodeForSp(StoredProcedure sp, string class_name, SpStreamHelper helper /*StringBuilder sb2, StringBuilder sb3 */)
{
string proc_name = pars.sp_prefix + sp.Name;
bool returns_data = false;
if (pars.use_sp_with_tables)
{
returns_data = sp.Name.StartsWith(pars.prefix_sp_with_tables);
}
helper.sb_static.AppendLine(indent + indent + "/// <summary> Wrapper for stored procedure " + sp.Name + ". </summary>");
helper.sb_static.Append(indent + indent + "public static " + (returns_data ? "DataTable" : "int") + " " + proc_name + "(IDataProviderSp provider");
helper.sb_intf.AppendLine(indent + indent + "/// <summary> Wrapper for stored procedure " + sp.Name + ". </summary>");
helper.sb_intf.Append(indent + indent + (returns_data ? "DataTable" : "int") + " " + proc_name + "(");
helper.sb_obj.AppendLine(indent + indent + "/// <summary> Wrapper for stored procedure " + sp.Name + ". </summary>");
helper.sb_obj.Append(indent + indent + "public " + (returns_data ? "DataTable" : "int") + " " + proc_name + "(");
var first = true;
var sbx = new StringBuilder();
foreach (SpParam sparam in sp.parameters)
{
sparam.cs_name = CreateValidCsName(sparam.Name);
if (first)
{
first = false;
}
else
{
helper.sb_intf.Append(", ");
helper.sb_obj.Append(", ");
}
helper.sb_static.AppendLine(", ");
helper.sb_static.Append(indent + indent + indent + (sparam.is_out ? "out " : "") + type_map.GetSpParamType(sparam.type, sparam.is_out) + " " + sparam.cs_name);
helper.sb_intf.Append((sparam.is_out ? "out " : "") + type_map.GetSpParamType(sparam.type, sparam.is_out) + " " + sparam.cs_name);
helper.sb_obj.Append((sparam.is_out ? "out " : "") + type_map.GetSpParamType(sparam.type, sparam.is_out) + " " + sparam.cs_name);
sbx.Append(", " + (sparam.is_out ? "out " : "") + sparam.cs_name);
}
helper.sb_static.AppendLine();
helper.sb_static.AppendLine(indent + indent + ") {");
helper.sb_intf.AppendLine(");");
helper.sb_obj.AppendLine(") { ");
helper.sb_obj.AppendLine(indent + indent + indent + "return " + class_name + "." + sp.Name + "(provider" + sbx.ToString() + ");");
helper.sb_obj.AppendLine(indent + indent + "}");
helper.sb_static.AppendLine(indent + indent + indent + "var cmd = provider.CreateCommand();");
helper.sb_static.AppendLine(indent + indent + indent + "cmd.CommandText = provider.DatabasePrefix + provider.SchemaPrefix + \"." + sp.Name + "\";");
helper.sb_static.AppendLine(indent + indent + indent + "cmd.CommandType = CommandType.StoredProcedure;");
foreach (SpParam sparam in sp.parameters)
{
if (sparam.is_out)
{
string s = "var par_{0} = AddParameterOut(cmd, \"@{0}\", DbType.{1}, {2}, {3}, {4}, {5});";
helper.sb_static.AppendLine(indent + indent + indent + string.Format(s,
sparam.Name,
sparam.type.ToString(),
"DBNull.Value",
sparam.orig_parameter.Precision,
sparam.orig_parameter.Scale,
sparam.orig_parameter.Size));
}
else
{
string s = "AddParameter(cmd, \"@{0}\", DbType.{1}, (CheckNull({2}) ? (object) DBNull.Value : (object) {2}));";
helper.sb_static.AppendLine(indent + indent + indent + string.Format(s, sparam.Name, sparam.type.ToString(), sparam.cs_name));
}
}
if (returns_data)
{
string s = Utils.LoadEmbededResourceAsString("sp_returning_data.txt");
s = s.Replace("#$#SP_NAME#$#", sp.Name);
helper.sb_static.Append(s);
}
else
{
helper.sb_static.AppendLine(indent + indent + indent + "int sp_result_value = provider.ExecuteNonQuery(cmd);");
// set output parameters
foreach (SpParam sparam in sp.parameters)
{
if (!sparam.is_out)
{
continue;
}
string s = "{1} = ({2}) par_{0}.Value;";
helper.sb_static.AppendLine(indent + indent + indent + string.Format(s, sparam.Name, sparam.cs_name, type_map.GetSpParamType(sparam.type, true)));
}
helper.sb_static.AppendLine(indent + indent + indent + "return sp_result_value;");
}
helper.sb_static.AppendLine(indent + indent + "}");
helper.sb_static.AppendLine("");
CreateCodeForSpDataset(sp, class_name, helper /*sb2, sb3*/);
}
/// <summary> This method creates data-wrapper for the dataset that is returned by the stored procedure. </summary>
/// <param name="sp"> Stored procedure metadata </param>
void CreateCodeForSpWrapper(StoredProcedure sp, string class_name_sp, SpStreamHelper helper /* StringBuilder sb_intf, StringBuilder sb_classes*/)
{
if (sp.ReturnedData == null)
{
return;
}
for (var i = 0; i < sp.ReturnedData.Tables.Count; i++)
{
var tab = sp.ReturnedData.Tables[i];
var sb_columns = new StringBuilder();
string class_name_sub = pars.class_prefix + Utils.PascalCasing(sp.Name) +
"_" + i;
string base_class = " : " + (pars.base_class_for_tables != null && pars.base_class_for_tables != "" ? pars.base_class_for_tables : "object");
string db_object_type = "table";
helper.sb_tabs.AppendLine(indent + "/// <summary> Wrapper for " + db_object_type + " " + sp.Name + ". </summary>");
helper.sb_tabs.AppendLine(indent + "public partial class " + class_name_sub + base_class + " {");
foreach (DataColumn field in tab.Columns)
{
var field_name = (pars.make_names_lowercase ? field.ColumnName.ToLower() : field.ColumnName);
helper.sb_tabs.AppendLine();
helper.sb_tabs.AppendLine(indent + indent + "/// <summary> The value of the corresponding field in the result of " + sp.Name + " " + db_object_type + ". </summary>");
helper.sb_tabs.Append(indent + indent + "[" + Consts.FieldAttr + "(\"" + field_name + "\"");
helper.sb_tabs.Append(", ReadOnly=true");
helper.sb_tabs.AppendLine(")]");
string member_name = CreateValidCsName(pars.member_prefix + field_name);
string member_type = type_map.GetDescCs(field.DataType);
helper.sb_tabs.Append(indent + indent + "public " + member_type + " " + member_name);
helper.sb_tabs.AppendLine(" { get; set; }");
sb_columns.AppendLine(indent + indent + indent + "///<summary>Name of column inside this table</summary>");
sb_columns.AppendFormat(indent + indent + indent + "public const string {0} = @\"{0}\";", member_name);
sb_columns.AppendLine();
}
helper.sb_tabs.AppendLine("");
helper.sb_tabs.AppendLine(indent + indent + "#region Columns Struct");
helper.sb_tabs.AppendLine(indent + indent + "///<summary>Columns for this table</summary>");
helper.sb_tabs.AppendLine(indent + indent + "public struct Columns {");
helper.sb_tabs.Append(sb_columns.ToString());
helper.sb_tabs.AppendLine(indent + indent + "}");
helper.sb_tabs.AppendLine(indent + indent + "#endregion");
helper.sb_tabs.AppendLine(indent + "}");
}
string class_name = pars.class_prefix + Utils.PascalCasing(sp.Name);
//if (!single_table) {
helper.sb_tabs.AppendLine(indent + "/// <summary> Wrapper for " + sp.Name + ". </summary>");
helper.sb_tabs.AppendLine(indent + "public partial class " + class_name + " {");
for (var j = 0; j < sp.ReturnedData.Tables.Count; j++)
{
helper.sb_tabs.AppendLine(indent + indent + "///<summary>Member</summary>");
var str = "public IList<{0}_{1}> Table_{1};";
str = string.Format(str, class_name, j);
helper.sb_tabs.AppendLine(indent + indent + str);
}
helper.sb_tabs.AppendLine(indent + "}");
string proc_name_ds = pars.sp_prefix + sp.Name + "_DataSet";
string proc_name = pars.sp_prefix + sp.Name + "_DataObject";
helper.sb_static.AppendLine(indent + indent + "/// <summary> Wrapper for stored procedure " + sp.Name + " returning data object. </summary>");
helper.sb_static.Append(indent + indent + "public static " + pars.sp_class_name + "." + class_name + " " + proc_name + "(IDataProviderSp provider");
helper.sb_intf.AppendLine(indent + indent + "/// <summary> Wrapper for stored procedure " + sp.Name + " returning data object. </summary>");
helper.sb_intf.Append(indent + indent + pars.sp_class_name + "." + class_name + " " + proc_name + "(");
helper.sb_obj.AppendLine(indent + indent + "/// <summary> Wrapper for stored procedure " + sp.Name + " returning data object. </summary>");
helper.sb_obj.Append(indent + indent + "public " + pars.sp_class_name + "." + class_name + " " + proc_name + "(");
var first = true;
var sbx = new StringBuilder();
foreach (var sparam in sp.parameters)
{
sparam.cs_name = CreateValidCsName(sparam.Name);
if (first)
{
first = false;
}
else
{
helper.sb_intf.Append(", ");
helper.sb_obj.Append(", ");
}
helper.sb_static.AppendLine(",");
helper.sb_static.Append(indent + indent + indent + (sparam.is_out ? "out " : "") + type_map.GetSpParamType(sparam.type, sparam.is_out) + " " + sparam.cs_name);
helper.sb_intf.Append((sparam.is_out ? "out " : "") + type_map.GetSpParamType(sparam.type, sparam.is_out) + " " + sparam.cs_name);
helper.sb_obj.Append((sparam.is_out ? "out " : "") + type_map.GetSpParamType(sparam.type, sparam.is_out) + " " + sparam.cs_name);
sbx.Append(", " + (sparam.is_out ? "out " : "") + sparam.cs_name);
}
helper.sb_static.AppendLine();
helper.sb_static.AppendLine(indent + indent + ") {");
helper.sb_intf.AppendLine(");");
helper.sb_obj.AppendLine(") {");
helper.sb_obj.AppendLine(indent + indent + indent + "return " + class_name_sp + "." + proc_name + "(provider" + sbx.ToString() + ");");
helper.sb_obj.AppendLine(indent + indent + "}");
helper.sb_static.Append(indent + indent + indent + "var ds = " + proc_name_ds + "(provider");
if (sp.parameters.Length > 0)
{
helper.sb_static.Append(", " + string.Join(", ", sp.parameters.Select(x => x.cs_name).ToArray()));
}
helper.sb_static.AppendLine(");");
helper.sb_static.AppendLine(indent + indent + indent + "var res = new " + class_name + "();");
for (var i = 0; i < sp.ReturnedData.Tables.Count; i++)
{
DataTable tab = sp.ReturnedData.Tables[i];
var class_name_sub = class_name + "_" + i;
helper.sb_static.AppendLine(indent + indent + indent + "var tab_" + i + " = new List<" + class_name_sub + ">();");
helper.sb_static.AppendLine(indent + indent + indent + "foreach (DataRow r" + i + " in ds.Tables[" + i + "].Rows) {");
helper.sb_static.AppendLine(indent + indent + indent + indent + "var obj = new " + class_name_sub + "();");
helper.sb_static.AppendLine(indent + indent + indent + indent + "obj.FillObjectFromDataRow(r" + i + ", " + this.pars.sp_data_wrapper_autotrim.ToString().ToLower() + ");");
helper.sb_static.AppendLine(indent + indent + indent + indent + "tab_" + i + ".Add(obj);");
helper.sb_static.AppendLine(indent + indent + indent + "}");
helper.sb_static.AppendLine(indent + indent + indent + "res.Table_" + i + " = tab_" + i + ";");
}
helper.sb_static.AppendLine(indent + indent + indent + "return res;");
helper.sb_static.AppendLine(indent + indent + "}");
helper.sb_static.AppendLine();
}