private static void AddTrace(StringBuilder sb, ControllerInfo controller, ProcInfo proc, bool skipId = false)
{
string fmt = ""; int pi = 0;
foreach (var param in proc.parameters)
{
if (!(param.name.ToLower() == "@id" && skipId))
{
fmt += param.name + "={" + (pi).ToString() + "}";
pi += 1;
if (pi < (proc.parameters.Count))
{
fmt += ", ";
}
}
}
fmt += ", return={" + (pi).ToString() + "}\",";
pi = 0;
foreach (var param in proc.parameters)
{
if (!(param.name.ToLower() == "@id" && skipId))
{
if (pi > 0)
{
fmt += ",";
}
if (param.name.ToLower() == "@username")
{
fmt += "User.Identity.Name";
}
else
{
if (!proc.name.ToLower().EndsWith("get"))
{
string memberName = "";
if (IsMember(controller, param.name.Substring(1), out memberName) && !(param.name.ToLower() == "@id"))
{
fmt += "res." + memberName;
}
else
{
fmt += param.name.Substring(1);
}
}
else
{
fmt += param.name.Substring(1);
}
}
pi++;
}
}
if (pi > 0)
{
fmt += ", ";
}
fmt += " RetVal.Value ";
fmt += ")";
sb.Append("\tlogger.Info(\"" + proc.name + ":" + fmt + ";\n");
}
private static void AddAdoParams(StringBuilder sb, ControllerInfo controller, ProcInfo proc, String varName, bool skipId = false)
{
bool hasMemberNamedId = false;
foreach (var col in controller.columns)
{
if (col.name.ToLower() == "id")
{
hasMemberNamedId = true;
}
}
foreach (var param in proc.parameters)
{
if (!(param.name.ToLower() == "@id" && skipId))
{
var resourceFieldName = "";
if (varName != null && varName.Length > 0)
{
foreach (var col in controller.columns)
{
if (col.name.ToLower() == param.name.Substring(1).ToLower())
{
resourceFieldName = varName + "." + col.name;
}
}
}
var paramType = param.sqlType;
if (!(varName.ToLower() == "id" && skipId))
{
if (param.name.ToLower() == "@username")
{
sb.AppendFormat("\tcom.Parameters.Add(\"{0}\", SqlDbType.{1}, {2}).Value = User.Identity.Name;\n",
param.name.Substring(1), GetTypeName(param.sqlType), param.maxLen);
}
else if (param.name.ToLower() == "@id" && (!hasMemberNamedId || proc.name.EndsWith("put")))
{
sb.AppendFormat("\tcom.Parameters.Add(\"{0}\", SqlDbType.{1}).Value = {2};\n",
param.name.Substring(1), GetTypeName(param.sqlType), param.name.Substring(1));
}
else if (paramType.ToLower().EndsWith("char"))
{
sb.AppendFormat("\tcom.Parameters.Add(\"{0}\", SqlDbType.{1}, {2}).Value = {3};\n",
param.name.Substring(1), GetTypeName(param.sqlType), param.maxLen,
(resourceFieldName.Length > 0) ? resourceFieldName : param.name.Substring(1));
}
else
{
sb.AppendFormat("\tcom.Parameters.Add(\"{0}\", SqlDbType.{1}).Value = {2};\n", param.name.Substring(1), GetTypeName(param.sqlType),
(resourceFieldName.Length > 0) ? resourceFieldName : param.name.Substring(1));
}
if (param.isOutput)
{
sb.AppendFormat("\tcom.Parameters[\"{0}\"].Direction = ParameterDirection.Output; \n",
(resourceFieldName.Length > 0) ? resourceFieldName : param.name.Substring(1));
}
}
}
}
}
static void ConstructEntity(SqlConnection con, ControllerInfo controller, ProcInfo procGet)
{
string vars = ""; bool firstVar = true;
foreach (var p in procGet.parameters)
{
if (!firstVar)
{
vars += ", ";
}
vars += p.name + "=null";
firstVar = false;
}
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
string query = string.Format(@"
SET FMTONLY ON;
EXEC dbo.api_{0} {1}
SET FMTONLY OFF;
" , procGet.name, vars);
cmd.CommandText = query;
DataTable table = new DataTable();
// con.Open();
try
{
SqlDataAdapter da = null;
using (da = new SqlDataAdapter(cmd))
{
da.Fill(table);
}
foreach (DataColumn col in table.Columns)
{
ColumnInfo newColumn = new ColumnInfo
{
name = col.ColumnName,
sqlType = col.DataType.Name,
maxLen = col.MaxLength
};
controller.columns.Add(newColumn);
}
}
catch (Exception ex)
{
Console.WriteLine("ConstructEntity: ", ex.Message);
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
}
}
private static bool AddNewId(StringBuilder sb, ControllerInfo controller, ProcInfo proc)
{
foreach (var param in proc.parameters)
{
if (param.name.ToLower() == "@newid")
{
sb.AppendFormat("\t {0} {1} = null;\n", param.csType, param.name.Substring(1));
return(true);
}
}
return(false);
}
private static bool IsMember(ControllerInfo controller, string varName, out string memberName)
{
foreach (var col in controller.columns)
{
if (col.name.ToLower() == varName.ToLower())
{
memberName = col.name;
return(true);
}
}
memberName = "";
return(false);
}
private static ProcInfo GetProcInfo(ControllerInfo controller, string procName)
{
procName = SkipAPI(procName);
foreach (var proc in controller.procs)
{
if (proc.name == procName)
{
return(proc);
}
}
ProcInfo newProc = new ProcInfo {
name = procName
};
controller.procs.Add(newProc);
return(newProc);
}
static void ConstructEntity(SqlConnection con, ControllerInfo controller, string nameEntityGet)
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
string query = string.Format(@"
SET FMTONLY ON;
EXEC dbo.api_{0}
SET FMTONLY OFF;
" , nameEntityGet);
cmd.CommandText = query;
DataTable table = new DataTable();
// con.Open();
try
{
SqlDataAdapter da = null;
using (da = new SqlDataAdapter(cmd))
{
da.Fill(table);
}
foreach (DataColumn col in table.Columns)
{
ColumnInfo newColumn = new ColumnInfo {
name = col.ColumnName,
sqlType = col.DataType.Name,
maxLen = col.MaxLength
};
controller.columns.Add(newColumn);
}
}
catch (Exception ex)
{
Console.WriteLine("ConstructEntity: ", ex.Message);
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
}
}
private static ControllerInfo GetControllerInfo(Model model, string procName)
{
procName = SkipAPI(procName);
if (procName.EndsWith("delete"))
{
procName = procName.Substring(0, procName.Length - 6);
}
else if (procName.EndsWith("get"))
{
procName = procName.Substring(0, procName.Length - 3);
}
else if (procName.EndsWith("put"))
{
procName = procName.Substring(0, procName.Length - 3);
}
else if (procName.EndsWith("post"))
{
procName = procName.Substring(0, procName.Length - 4);
}
else
{
return(null);
}
if (procName.EndsWith("_"))
{
procName = procName.Substring(0, procName.Length - 1);
}
foreach (var controller in model.controllers)
{
if (controller.name == procName)
{
return(controller);
}
}
ControllerInfo newController = new ControllerInfo {
name = procName
};
model.controllers.Add(newController);
return(newController);
}
private static void AddParameters(bool first, StringBuilder sb, ControllerInfo controller, ProcInfo proc, bool skipMembers, bool skipId = false)
{
// Add parameters not in resource
foreach (var param in proc.parameters)
{
var paramName = param.name.Substring(1);
if (skipMembers)
{
foreach (var col in controller.columns)
{
if (col.name.ToLower() == param.name.Substring(1).ToLower() &&
col.name.ToLower() != "id")
{
paramName = "";
}
}
}
if (paramName.Length > 0)
{
if (paramName.ToLower() != "username")
{
if (!(skipId && paramName.ToLower() == "id") &&
!(proc.name.ToLower().EndsWith("post") && paramName.ToLower() == "newid"))
{
if (!first)
{
sb.Append(", ");
}
if (proc.name.ToLower().EndsWith("get") && paramName.ToLower() == "id")
{
sb.AppendFormat(" {0} {1} ", param.csType, paramName);
}
else
{
sb.AppendFormat(" {0} {1} = null", param.csType, paramName);
}
first = false;
}
}
}
}
}
static public Model ConstructModel(string conString)
{
Model model = new Model();
SqlConnection con = null;
SqlCommand cmd;
try
{
con = new SqlConnection(conString);
} catch (Exception ex)
{
logger.Fatal(ex);
return(null);
}
cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
string query = @"SELECT
ProcedureName = ir.ROUTINE_NAME,
ParameterName = COALESCE(ip.PARAMETER_NAME, '<no params>'),
SqlType = ip.DATA_TYPE, Precision = ip.NUMERIC_PRECISION, Scale = ip.NUMERIC_SCALE, MaxLen = ip.CHARACTER_MAXIMUM_LENGTH,
DataType = COALESCE(UPPER(ip.DATA_TYPE) + CASE
WHEN ip.DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN
'(' + CAST(ip.NUMERIC_PRECISION AS VARCHAR)
+ ', ' + CAST(ip.NUMERIC_SCALE AS VARCHAR) + ')'
WHEN RIGHT(ip.DATA_TYPE, 4) = 'CHAR' THEN
'(' + CAST(ip.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
ELSE '' END + CASE ip.PARAMETER_MODE
WHEN 'INOUT' THEN ' OUTPUT' ELSE ' ' END, '-'),
ParameterMode = ip.PARAMETER_MODE
FROM
INFORMATION_SCHEMA.ROUTINES ir
LEFT OUTER JOIN
INFORMATION_SCHEMA.PARAMETERS ip
ON ir.ROUTINE_NAME = ip.SPECIFIC_NAME
WHERE
ir.ROUTINE_NAME LIKE 'API%'
AND ir.ROUTINE_TYPE = 'PROCEDURE'
AND COALESCE(OBJECTPROPERTY
(
OBJECT_ID(ip.SPECIFIC_NAME),
'IsMsShipped'
), 0) = 0
ORDER BY
ir.ROUTINE_NAME,
ip.ORDINAL_POSITION" ;
cmd.CommandText = query;
DataTable table = new DataTable();
con.Open();
try
{
SqlDataAdapter da = null;
using (da = new SqlDataAdapter(cmd))
{
da.Fill(table);
}
foreach (DataRow row in table.Rows)
{
string procName = row["ProcedureName"].ToString();
ControllerInfo controller = GetControllerInfo(model, procName);
if (controller != null)
{
ProcInfo proc = GetProcInfo(controller, procName);
string parameterName = row["ParameterName"].ToString();
if (parameterName != "<no params>")
{
proc.parameters.Add(new ParameterInfo
{
name = parameterName,
precision = (DBNull.Value == row["Precision"]) ? 0 : int.Parse(row["Precision"].ToString()),
scale = (DBNull.Value == row["Scale"]) ? 0 : int.Parse(row["Scale"].ToString()),
maxLen = (DBNull.Value == row["Maxlen"]) ? 0 : int.Parse(row["MaxLen"].ToString()),
sqlType = row["SqlType"].ToString(),
isOutput = (row["ParameterMode"].ToString() != "IN")
});
}
}
}
foreach (ControllerInfo controller in model.controllers)
{
foreach (ProcInfo proc in controller.procs)
{
if ((proc.name == controller.name + "get") || (proc.name == controller.name + "_get"))
{
if (controller.columns.Count == 0)
{
ConstructEntity(con, controller, proc);
}
}
foreach (var par in proc.parameters)
{
par.csType = TypeMapper.GetCSTypeName(par.sqlType);
}
GetXmlComments(con, proc);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
con.Close();
}
return(model);
}
