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(); }