private void btnFetch_Click(object sender, EventArgs e)
{
TAIGFields.PopulateGridWithData(txtConnectionString.Text, SQLStatement.Text);
string SQLQuerySchema = "sp_describe_first_result_set N'" + SQLStatement.Text.Replace("'", "''") + "'";
TaigQuerySchema.PopulateGridWithData(txtConnectionString.Text, SQLQuerySchema);
ParseIntoTheFieldsList();
ParseIntoTheFieldsGrid();
}
private void ParseIntoTheFieldsList()
{
TheFields = new List <Field>();
for (int i = 0; i < TaigQuerySchema.Rows; i++)
{
Field nf = new Field();
var nullable = TaigQuerySchema.get_item(i, "is_nullable");
if (nullable.ToUpper() == "TRUE")
{
nf.AllowNulls = true;
}
else
{
nf.AllowNulls = false;
}
nf.FieldName = TaigQuerySchema.get_item(i, "name");
nf.FieldNameConverted = nf.FieldName.Replace(" ", "_");
nf.FieldType = TaigQuerySchema.get_item(i, "system_type_name").Split("(".ToCharArray())[0].ToUpper(); // rather convoluted but effective
var isID = TaigQuerySchema.get_item(i, "is_identity_column");
if (isID.ToUpper() == "TRUE")
{
nf.IsIdentity = true;
}
else
{
nf.IsIdentity = false;
}
nf.MaxLength = int.Parse(TaigQuerySchema.get_item(i, "max_length"));
nf.Precision = int.Parse(TaigQuerySchema.get_item(i, "precision"));
nf.Scale = int.Parse(TaigQuerySchema.get_item(i, "scale"));
TheFields.Add(nf);
}
}
private void btnGenerateController_Click(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder();
string stab = "";
string tab = stab;
string CLASSNAME = "{CLASSNAMEHERE}";
string PROJNAME = "{Project}";
if (txtClassName.Text.Trim() != "")
{
CLASSNAME = txtClassName.Text;
}
if (txtProjectName.Text.Trim() != "")
{
PROJNAME = txtProjectName.Text;
}
if (chkFullController.Checked)
{
sb.Append("using System;\n" +
"using System.Collections.Generic;\n" +
"using System.Linq;\n" +
"using System.Threading.Tasks;\n" +
"using Microsoft.AspNetCore.Http;\n" +
"using Microsoft.AspNetCore.Mvc;\n\n" +
"using System.Data.Sql;\n\n" +
"using System.Data.SqlClient;\n\n");
// using System.Data.Sql;
// using System.Data.SqlClient;
sb.Append("using " + PROJNAME + ".Shared;\n\n");
sb.Append("namespace " + PROJNAME + ".Server.Controllers\n" +
"{\n\n");
stab = "\t"; //Begin indenting one level
tab = stab;
}
// LIST GETTER
sb.Append(tab + "[Route(\"api/[controller]\")]\n");
sb.Append(tab + "[ApiController]\n");
sb.Append(tab + "public class " + CLASSNAME + "sController : ControllerBase\n" +
tab + "{\n\n");
tab = stab + "\t";
sb.Append(tab + "[HttpGet]\n");
sb.Append(tab + "public IEnumerable<" + CLASSNAME + "> Get()\n");
sb.Append(tab + "{\n\n");
tab = stab + "\t\t";
sb.Append(tab + "List<" + CLASSNAME + "> My" + CLASSNAME + "s = new List<" + CLASSNAME + ">();\n\n");
if (chkDoSQL.Checked)
{
sb.Append(tab + @"// Hardcoded SQL Populator You will need to replace the " + "\n");
sb.Append(tab + @"// connection parameters with something more secure " + "\n");
sb.Append(tab + @"// most certainly. In addition the SQL selection filters and criteria" + "\n");
sb.Append(tab + @"// will likely need to be adjusted to accomodate actual use cases" + "\n\n");
// here we will attempt to format the SQL statement if its got linefeeds in it
string SQL = SQLStatement.Text.Replace("\n", " \" + \n" + tab + "\"").Replace("\r", "");
sb.Append(tab + "string sql =\"" + SQL + "\";\n\n");
sb.Append(tab + "SqlConnection cn = new SqlConnection(\"" + txtConnectionString.Text + "\");\n\n");
sb.Append(tab + "cn.Open();\n\n");
sb.Append(tab + "SqlCommand cmd = new SqlCommand(sql,cn);\n\n");
sb.Append(tab + "SqlDataReader r = cmd.ExecuteReader();\n\n");
sb.Append(tab + "while (r.Read())\n");
sb.Append(tab + "{\n");
tab = stab + "\t\t\t";
sb.Append(tab + @"// Do the read and class stuffage here" + "\n");
sb.Append(tab + CLASSNAME + " TheClass = new " + CLASSNAME + "();\n\n");
sb.Append(tab + @"// TODO: Implement the field reader" + "\n");
// Implement field stuffer here
sb.Append(tab + "Copyfields(r,TheClass);\n\n");
sb.Append(tab + "My" + CLASSNAME + "s.Add(TheClass);\n\n");
tab = stab + "\t\t";
sb.Append(tab + "}\n\n");
sb.Append(tab + "r.Close();\n");
sb.Append(tab + "cmd.Cancel();\n");
sb.Append(tab + "cmd.Dispose();\n");
sb.Append(tab + "cn.Close();\n");
sb.Append(tab + "cn.Dispose();\n");
}
else
{
sb.Append(tab + @"// Populate the list here with something" + "\n\n");
}
sb.Append(tab + "return My" + CLASSNAME + "s;\n\n");
tab = stab + "\t";
sb.Append(tab + "}\n\n");
// INSTANCE GETTER
var IDFIELD = "";
var IDFIELDTYPE = "long";
for (int r = 0; r < TaigQuerySchema.Rows; r++)
{
if (TaigQuerySchema.get_item(r, "is_identity_column").ToUpper() == "TRUE")
{
IDFIELD = TaigQuerySchema.get_item(r, "name");
//IDFIELDTYPE = "long";
break;
}
}
if (IDFIELD == "")
{
sb.Append(tab + "[HttpGet(\"{id}\")]\n");
}
else
{
sb.Append(tab + "[HttpGet(\"{" + IDFIELD + "}\")]\n");
}
sb.Append(tab + "public " + CLASSNAME + " Get(" + IDFIELDTYPE + " " + IDFIELD + ")\n");
sb.Append(tab + "{\n\n");
tab = stab + "\t\t";
sb.Append(tab + @"// Return a specific instance of " + CLASSNAME + "\n\n");
sb.Append(tab + CLASSNAME + " result = new " + CLASSNAME + "();\n\n");
var SQL2 = SQLStatement.Text.Replace("\n", " \" + \n" + tab + "\"").Replace("\r", "");
sb.Append(tab + "string sql =\"SELECT TOP 1 * from (" + SQL2 + ") SUBSELECTA WHERE SUBSELECTA." + IDFIELD + " = @FLD\";\n\n");
sb.Append(tab + "SqlConnection cn = new SqlConnection(\"" + txtConnectionString.Text + "\");\n\n");
sb.Append(tab + "cn.Open();\n\n");
sb.Append(tab + "SqlCommand cmd = new SqlCommand(sql,cn);\n\n");
sb.Append(tab + "cmd.Parameters.AddWithValue(\"@FLD\"," + IDFIELD + ");\n\n");
sb.Append(tab + "SqlDataReader r = cmd.ExecuteReader();\n\n");
sb.Append(tab + "while (r.Read())\n");
sb.Append(tab + "{\n");
tab = stab + "\t\t\t";
sb.Append(tab + "Copyfields(r,result);\n\n");
tab = stab + "\t\t";
sb.Append(tab + "}\n\n");
sb.Append(tab + "r.Close();\n");
sb.Append(tab + "cmd.Cancel();\n");
sb.Append(tab + "cmd.Dispose();\n");
sb.Append(tab + "cn.Close();\n");
sb.Append(tab + "cn.Dispose();\n\n");
sb.Append(tab + "return result;\n\n");
tab = stab + "\t";
sb.Append(tab + "}\n\n");
// POSTER
sb.Append(tab + "[HttpPost]\n");
sb.Append(tab + "public void Post([FromBody] " + CLASSNAME + " value )\n");
sb.Append(tab + "{\n\n");
sb.Append(tab + "}\n\n");
// PUTTER
sb.Append(tab + "[HttpPut(\"{id}\")]\n");
sb.Append(tab + "public void Put(long id, [FromBody] " + CLASSNAME + " value )\n");
sb.Append(tab + "{\n\n");
sb.Append(tab + "}\n\n");
// DELETER
sb.Append(tab + "[HttpDelete(\"{id}\")]\n");
sb.Append(tab + "public void Delete(long id )\n");
sb.Append(tab + "{\n\n");
sb.Append(tab + "}\n\n");
// Private stuff
sb.Append(tab + "private void Copyfields(SqlDataReader r, " + CLASSNAME + " TheClass)\n");
sb.Append(tab + "{\n\n");
tab += "\t";
string s = "";
foreach (Field f in TheFields)
{
if (f.FieldType == "VARCHAR" || f.FieldType == "CHAR" || f.FieldType == "NVARCHAR" ||
f.FieldType == "TEXT" || f.FieldType == "UNIQUEIDENTIFIER" || f.FieldType == "GUID" ||
f.FieldType == "SYSNAME")
{
s += tab + "if (!Convert.IsDBNull(r[\"" + f.FieldName + "\"]))\n";
s += tab + "{\n";
s += tab + "\tTheClass." + f.FieldNameConverted + " = r[\"" + f.FieldName + "\"] + \"\";\n";
s += tab + "}\n\n";
}
if (f.FieldType == "INT" || f.FieldType == "SMALLINT" || f.FieldType == "TINYINT")
{
s += tab + "if (!Convert.IsDBNull(r[\"" + f.FieldName + "\"]))\n";
s += tab + "{\n";
s += tab + "\tTheClass." + f.FieldNameConverted + " = Convert.ToInt32(r[\"" + f.FieldName + "\"]);\n";
s += tab + "}\n\n";
}
if (f.FieldType == "BIGINT")
{
s += tab + "if (!Convert.IsDBNull(r[\"" + f.FieldName + "\"]))\n";
s += tab + "{\n";
s += tab + "\tTheClass." + f.FieldNameConverted + " = Convert.ToInt64(r[\"" + f.FieldName + "\"]);\n";
s += tab + "}\n\n";
}
if (f.FieldType == "DECIMAL" || f.FieldType == "DOUBLE" || f.FieldType == "MONEY" || f.FieldType == "CURRENCY" || f.FieldType == "FLOAT")
{
s += tab + "if (!Convert.IsDBNull(r[\"" + f.FieldName + "\"]))\n";
s += tab + "{\n";
s += tab + "\tTheClass." + f.FieldNameConverted + " = Convert.ToDouble(r[\"" + f.FieldName + "\"]);\n";
s += tab + "}\n\n";
}
if (f.FieldType == "DATETIME" || f.FieldType == "DATE" || f.FieldType == "DATETIME2" || f.FieldType == "SMALLDATE" || f.FieldType == "SMALLDATETIME")
{
s += tab + "if (!Convert.IsDBNull(r[\"" + f.FieldName + "\"]))\n";
s += tab + "{\n";
s += tab + "\tTheClass." + f.FieldNameConverted + " = Convert.ToDateTime(r[\"" + f.FieldName + "\"]);\n";
s += tab + "}\n\n";
}
if (f.FieldType == "BOOL" || f.FieldType == "BIT")
{
s += tab + "if (!Convert.IsDBNull(r[\"" + f.FieldName + "\"]))\n";
s += tab + "{\n";
s += tab + "\tTheClass." + f.FieldNameConverted + " = Convert.ToBoolean(r[\"" + f.FieldName + "\"]);\n";
s += tab + "}\n\n";
}
}
tab = stab + "\t";
s += tab + "}\n\n";
sb.Append(s);
tab = stab;
sb.Append(tab + "}\n\n");
if (chkFullController.Checked)
{
sb.Append("}\n\n");
}
fcController.Text = sb.ToString();
}
