public static List<CrudGenSPROC> ParseDataTable(DataTable dt)
{
List<CrudGenSPROC> tables = new List<CrudGenSPROC>();
foreach (DataRow dr in dt.Rows) {
CrudGenSPROC table;
if (tables.Count == 0 || tables[tables.Count - 1].TableName != dr["TableName"].ToString()) {
table = new CrudGenSPROC();
table.TableName = dr["TableName"].ToString();
tables.Add(table);
} else {
table = tables[tables.Count - 1];
}
Column column = new Column();
column.Name = dr["ColumnName"].ToString();
column.IsIdentity = ((int)dr["IsIdentity"] == 1);
column.IsPrimaryKey = ((int)dr["IsPrimaryKey"] == 1);
column.DataType = dr["DataType"].ToString();
if (dr["isComputed"].ToString() == "1")
column.IsComputed = true;
table.Columns.Add(column);
}
return tables;
}
private string GetPropName(Column c)
{
string result = (c.Name.Length > 1) ?
string.Format("{0}{1}", c.Name.Substring(0, 1).ToUpper(), c.Name.Substring(1)) :
c.Name.ToUpper();
return result;
}
private static bool DeclareColumn(StringBuilder sb, Column c, bool first, bool output)
{
if (!first) {
sb.Append(",\r\n");
} else {
first = !first;
}
sb.Append("\t@");
sb.Append(c.Name + " ");
sb.Append(c.DataType);
if (output) {
sb.Append(" OUTPUT");
}
return first;
}
/// <summary>
/// The initial value fields are set to when the constructor is invoked
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
private string GetInitialValueByType(Column c)
{
SqlDbType type = c.SqlDbTypeOfColumn ;
string result = "\"\"";
switch (type) {
case SqlDbType.Bit:
result = "false"; break;
case SqlDbType.BigInt :
case SqlDbType.Binary:
case SqlDbType.Decimal:
case SqlDbType.Float:
case SqlDbType.Int :
case SqlDbType.Money:
case SqlDbType.Real:
case SqlDbType.SmallInt:
case SqlDbType.SmallMoney:
case SqlDbType.TinyInt:
case SqlDbType.VarBinary:
result = "0";break;
case SqlDbType.Char:
case SqlDbType.NChar:
result = "' '";break;
case SqlDbType.VarChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.Text:
result = "\"\"";break;
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.SmallDateTime:
case SqlDbType.Time:
result = "new DateTime()"; break;
case SqlDbType.UniqueIdentifier:
result = "new Guid()"; break;
}
return result;
}
private string GetInputParamName(Column c)
{
//make it camel cased
string result = (c.Name.Length > 1) ?
string.Format("{0}{1}", c.Name.Substring(0, 1).ToLower(), c.Name.Substring(1)) :
c.Name.ToLower();
return result;
}
private string GetFieldName(Column c)
{
string result = (c.Name.Length > 1)?
string.Format("_{0}{1}", c.Name.Substring(0,1).ToLower(), c.Name.Substring(1)):
"_" + c.Name.ToLower();
return result;
}
private string GetFieldAsInputParameter(Column c)
{
string result = "";
result = string.Format ("{0} {1}",
c.GetASPNetDataType(), GetInputParamName(c));
return result;
}
/// <summary>
/// Builds the readAll, readById, update, and delete procedures.
/// Best practices: it uses as input only the identity column
/// because we are guaranteed that there will only be one of those in a table.
/// As far as why not to use a primary key as input, that has to do with the fact that the number
/// of primary keys can vary according to how the table is designed and I don't see
/// a need to complicate this method.
/// </summary>
private void BuildCrud_DL_ReadUpdateDelete()
{
Column identity = Column.GetIdentityColumn(_cols);
if (identity == null){
identity = new Column();
identity.Name = "NoIdentityColumn";
}
crudData.AppendFormat(" public static List<{0}> RetrieveAll(Guid userId) {{\r\n", _className);
crudData.AppendFormat(" List<{0}> result = new List<{0}>();\r\n", _className);
crudData.AppendFormat(" using (SqlCommand cmd = new SqlCommand(\"{0}\"))\r\n", CrudGenSPROC.GetSprocNameReadAll(_tableName) );
crudData.AppendLine(" {");
crudData.AppendLine(" cmd.CommandType = CommandType.StoredProcedure;");
crudData.AppendLine(" cmd.Parameters.AddWithValue(\"@userId\", userId);");
crudData.AppendLine(" try{");
crudData.AppendLine(" SqlDataReader r = DataAccess.RunCMDGetDataReader(cmd);");
crudData.AppendLine(" while (r.Read()) ");
crudData.AppendLine(" result.Add(convertReaderToObject(r));");
crudData.AppendLine(" r.Close();");
crudData.AppendLine(" } catch (Exception ex) {throw ex; }");
crudData.AppendLine(" } //close using statement ");
crudData.AppendLine(" return result;");
crudData.AppendLine(" }");
//todo get key column type and its variable name
crudData.AppendFormat(" public static {0} RetrieveById(int {1}, Guid userId){{\r\n", _className, identity.Name );
crudData.AppendFormat(" {0} result=null;\r\n", _className);
crudData.AppendFormat(" using (SqlCommand cmd = new SqlCommand(\"{0}\"))\r\n", CrudGenSPROC.GetSprocNameReadById(_tableName));
crudData.AppendLine(" {");
crudData.AppendLine(" cmd.CommandType = CommandType.StoredProcedure;");
crudData.AppendFormat(" cmd.Parameters.AddWithValue(\"@{0}\", {0});\r\n", identity.Name);
crudData.AppendLine(" cmd.Parameters.AddWithValue(\"@userId\", userId);");
crudData.AppendLine(" SqlDataReader r = DataAccess.RunCMDGetDataReader(cmd);");
crudData.AppendLine(" if (r.Read())");
crudData.AppendLine(" result = convertReaderToObject(r);");
crudData.AppendLine(" r.Close();");
crudData.AppendLine(" } //close using statement ");
crudData.AppendLine(" return result;");
crudData.AppendLine(" }");
crudData.AppendLine("");
crudData.AppendFormat(" public static Boolean Update({0} obj,Guid userId) {{\r\n", _className);
crudData.AppendLine(" bool result = false;");
crudData.AppendFormat(" using (SqlCommand cmd = new SqlCommand(\"{0}\"))\r\n", CrudGenSPROC.GetSprocNameUpdate(_tableName));
crudData.AppendLine(" {");
crudData.AppendLine(" cmd.CommandType = CommandType.StoredProcedure;");
crudData_AddSprocParams(crudData, true);
crudData.AppendLine(" cmd.Parameters.Add(\"@rowsAffected\", SqlDbType.Int);\r\n");
crudData.AppendLine(" cmd.Parameters[\"@rowsAffected\"].Direction = ParameterDirection.ReturnValue;");
crudData.AppendLine(" SqlDataReader r = DataAccess.RunCMDGetDataReader(cmd);");
crudData.AppendLine(" if (cmd.Parameters[\"@rowsAffected\"].Value.ToString() == \"1\") result = true;");
crudData.AppendLine(" r.Close();");
crudData.AppendLine(" } //close using statement ");
crudData.AppendLine(" return result;");
crudData.AppendLine(" }\r\n");
crudData.AppendFormat(" public static Boolean Delete(int {0}, Guid userId){{\r\n", identity.Name);
crudData.AppendLine(" bool result = false;");
crudData.AppendFormat(" using (SqlCommand cmd = new SqlCommand(\"{0}\")){{\r\n", CrudGenSPROC.GetSprocNameDelete(_tableName));
crudData.AppendLine(" cmd.CommandType = CommandType.StoredProcedure;");
crudData.AppendFormat(" cmd.Parameters.AddWithValue(\"@{0}\", {0});\r\n", identity.Name);
crudData.AppendLine(" cmd.Parameters.AddWithValue(\"@userId\", userId);");
crudData.AppendLine(" cmd.Parameters.Add(\"@rowsAffected\");");
crudData.AppendLine(" cmd.Parameters[\"@rowsAffected\"].Direction = ParameterDirection.ReturnValue;");
crudData.AppendLine("");
crudData.AppendLine(" SqlDataReader r = DataAccess.RunCMDGetDataReader(cmd);");
crudData.AppendLine(" if (cmd.Parameters[\"@rowsAffected\"].Value.ToString() == \"1\") result = true;");
crudData.AppendLine(" //todo set the return value to true or false based on what the stored procedure returns as ");
crudData.AppendLine(" r.Close();");
crudData.AppendLine(" } //close using statement ");
crudData.AppendLine(" return result;");
crudData.AppendLine(" }");
}
/// <summary>Create should return the datatype of the current </summary>
private void BuildCrud_CreateDL()
{
Column identityCol = Column.GetIdentityColumn(_cols);
if (identityCol == null)
{
identityCol = new Column();
identityCol.DataType = "Int"; //default return data type
identityCol.Name = "NoIdentityColumnInTable_NeedManualSetup";
}
crudData.Append("\t///<summary>returns the id of the item which was just created</summary>\r\n"
+ string.Format("\tpublic static {0} Create({1} obj{2}){{\r\n"
, identityCol.GetASPNetDataType()
, _className
, (u.UserSettings.UserIdIsParamForCRUBusinessLayer) ? ",Guid userId" : "")
+ string.Format("\t using(SqlCommand cmd=new SqlCommand(\"{0}\")){{\r\n", CrudGenSPROC.GetSprocNameCreate(_tableName))
+ "\t\t cmd.CommandType = CommandType.StoredProcedure;\r\n");
//for each column, add a parameter to the sproc
//foreach (Column c in cols) {
// result += string.Format("\t\t cmd.Parameters.AddWithValue(\"@{0}\" , obj.{1});\r\n", c.Name, GetPropName(c));
// //todo: convert the value from theinput object to database friendly value... this means need to have a ToDBFriendly method
//}
crudData_AddSprocParams(crudData, true);
crudData.AppendFormat("\t\t return DataAccess.RunCmdReturn_{0}(cmd);\r\n", identityCol.GetASPNetDataType());
crudData.AppendLine("\t } //close using statement \r\n}");
}
private string BuildCRUD(List<Column> cols)
{
StringBuilder result= new StringBuilder().Append("#region CRUD\r\n");
string create, retrieveByID,retrieveAll, update, delete;
create = retrieveByID = retrieveAll = update = delete = "";
Column identityCol = Column.GetIdentityColumn(_cols);
if (identityCol == null){
identityCol = new Column();
identityCol.DataType = "Int";
identityCol.Name = "NoIdentityColumnInTable_NeedManualSetup";
}
create = string.Format("\tpublic {0} Create(Guid userId){{\r\n", identityCol.GetASPNetDataType())
+ string.Format("\t\t return {0}Data.Create(this,userId);", _className )
+ "\t}\r\n";
retrieveAll = string.Format("\tpublic static List<{0}> RetrieveAll(Guid userId){{\r\n", _className)
+ string.Format("\t\t return {0}Data.RetrieveAll(userId);\r\n", _className)
+ "\t}\r\n";
retrieveByID = string.Format("\tpublic static {0} RetrieveById(int {1}, Guid userId){{\r\n", _className, identityCol.Name)
+ string.Format("\t\t return {0}Data.RetrieveById({1}, userId);\r\n", _className, identityCol.Name)
+ "\t}\r\n";
update = "\tpublic bool Update(Guid userId){\r\n"
+ string.Format("\t\t return {0}Data.Update(this, userId);\r\n", _className)
+ "\t}\r\n";
delete = "\tpublic bool Delete(Guid userId){\r\n"
+ string.Format("\t\t return {0}Data.Delete(this, userId);\r\n", _className)
+ "\t}\r\n";
delete = string.Format("\tpublic static bool Delete(int {0},Guid userId){{\r\n", identityCol.Name)
+ string.Format("\t\t return {0}Data.Delete({1}, userId);\r\n", _className, identityCol.Name)
+ "\t}\r\n";
result.Append(create);
result.Append(retrieveAll);
result.Append(retrieveByID);
result.Append(update);
result.Append(delete);
result.Append("#endregion //CRUD\r\n");
return result.ToString();
}
