public static DataTable GetSchema(string tableName, string columnNameParameter)
{
CustomBoolean.Clear();
DataTable table = GetDatatableSchema();
ATWS atws = new ATWS();
atws.Credentials = new NetworkCredential("*****@*****.**", "12345");
ModelCreator.autotask.Field[] fields = atws.GetFieldInfo(tableName);
foreach (ModelCreator.autotask.Field field in fields)
{
DataRow row = table.NewRow();
row["Name"] = field.Name;
row["FieldName"] = field.Name;
row["Type"] = GetType(field.Type);
row["DbType"] = GetType(field.Type);
row["Length"] = field.Length;
row["Nullable"] = field.IsRequired;
row["Table"] = tableName;
row["Schema"] = "";
row["BusinessName"] = field.Name;
row["Description"] = field.Description;
table.Rows.Add(row);
}
return(table);
}
public static dynamic GetTSObject(CustomBoolean dynObject)
{
if (dynObject is null)
{
return(null);
}
return(dynObject.teklaObject);
}
public static DataTable GetSchema(string tableName)
{
CustomBoolean.Clear();
DataTable table = GetDatatableSchema();
foreach (DataRow dr in GetColumns(tableName).Rows)
{
string newTableName = dr[1].ToString().Trim();
string columnsName = dr[0].ToString().Trim();
string schema = dr[2].ToString().Trim();
int columnsMaxLength = dr[5].ToString().Trim().Equals(string.Empty) ? 0 : Int32.Parse(dr[5].ToString().Trim());
bool columnsNullable = dr[7].ToString().Trim().ToUpper().StartsWith("Y") ? true : false;
string columnsType = GetSqlBaseType(columnsName, dr[4].ToString(), columnsMaxLength, columnsNullable).Trim();
string columnsDBType = dr[4].ToString().Trim();
columnsNullable = dr[7].ToString().Trim().ToUpper().Equals("Y") ? true : false;
string businessName = dr[9].ToString().Trim();
string description = string.Empty;
if (!businessName.Equals(string.Empty))
{
if (businessName.Contains("-"))
{
string[] array = businessName.Split('-');
businessName = array[0].Trim();
description = array[1].Trim();
}
}
DataRow row = table.NewRow();
//row["Name"] = businessName.Equals(string.Empty) ? columnsName : string.Empty;
row["Name"] = columnsName;
row["FieldName"] = columnsName;
row["Type"] = columnsType;
row["DbType"] = columnsDBType;
row["Length"] = columnsMaxLength;
row["Nullable"] = columnsNullable;
row["Table"] = newTableName;
row["Schema"] = schema;
row["BusinessName"] = businessName;
row["Description"] = description;
table.Rows.Add(row);
}
return(table);
}
public static string GetSqlBaseType(string name, string tstrSqlType, int lenght, bool IsNullable)
{
string _Type = string.Empty;
bool isNumeric = false;
switch (tstrSqlType)
{
case "BIGINT":
{
_Type = "Int64";
isNumeric = true;
} break;
case "SMALLINT":
{
_Type = "Int16";
isNumeric = true;
} break;
case "tinyint":
{
_Type = "byte";
} break;
case "INTEGER":
{
_Type = "int";
isNumeric = true;
} break;
case "bit":
{
_Type = "bool";
} break;
case "DECIMAL":
{
if (lenght == 1)
{
_Type = "bool";
CustomBoolean.Add(name);
}
else
{
_Type = "decimal";
isNumeric = true;
}
} break;
case "NUMBER":
{
_Type = "decimal";
isNumeric = true;
} break;
case "money":
case "smallmoney":
{
_Type = "decimal";
isNumeric = true;
} break;
case "DOUBLE":
{
_Type = "double";
isNumeric = true;
} break;
case "FLOAT":
{
if (lenght == 8)
{
_Type = "double";
}
else
{
_Type = "float";
}
isNumeric = true;
} break;
case "REAL":
{
_Type = "float";
isNumeric = true;
} break;
case "DATE":
case "DATETIME":
case "SMALLDATETIME":
case "TIME":
{
_Type = "DateTime?";
} break;
case "CHAR":
{
_Type = "string";
} break;
case "sql_variant":
{
_Type = "object";
} break;
case "VARCHAR":
case "TEXT":
case "NCHAR":
case "NVARCHAR":
case "NTEXT":
{
_Type = "string";
} break;
case "GRAPHIC":
case "VARGRAPHIC":
case "BINARY":
case "VARBINARY":
case "LONGVAR":
case "LONGGRAPHICS":
case "LONGBINARY":
case "CHAR>254":
case "VARCHAR>254":
{
//_Type = "byte[]";
_Type = "string";
} break;
case "TIMESTMP":
case "TIMESTAMP":
{
_Type = "DateTime?";
} break;
default:
{
_Type = "unknown";
} break;
}
if ((!Session.NullLogic) && (IsNullable))
{
if (isNumeric)
{
_Type = string.Concat(_Type, "?");
}
}
return(_Type);
}
public bool BuildTemplate(DataTable dt, Provider provider)
{
string tableName = dt.Rows[0]["Table"].ToString().Trim();
KeyColumn key = new KeyColumn();
key.Name = dt.Rows[0]["Name"].ToString();
key.NetType = dt.Rows[0]["Type"].ToString();
key.Sequence = 1;
key.Type = dt.Rows[0]["Type"].ToString();
key.Size = int.Parse(dt.Rows[0]["Length"].ToString());
Dictionary <string, KeyColumn> keys = new Dictionary <string, KeyColumn>();
keys.Add(key.Name, key);
Session.Keys = keys;
string keyType = "int";
string isAutomaticKey = "false";
string typeKey = string.Empty;
if (Session.Keys.Count == 0)
{
return(false);
}
string privateField = string.Empty;
if (Session.Framework == Framework.net20)
{
privateField = GetPrivateField(dt);
}
string publicField = GetPublicField(dt);
string mapping = GetMapping(dt);
string parameters = string.Empty; // GetParameters(dt, tableName);
string parametersList = string.Empty;
string parametersListInsert = string.Empty;
string parametersListUpdate = string.Empty;
string parametersListDelete = string.Empty;
string parametersListClone = string.Empty;
string declarationParametersKeys = string.Empty;
string parameterKeys = string.Empty;
string listParameterKeys = string.Empty;
string isNullKeys = string.Empty;
string parametersKeysWithOutType = string.Empty;
string queryInsert = string.Empty;
string queryUpdate = string.Empty;
string queryDelete = string.Empty;
string fieldBoolean = string.Empty;
string queryByUpdVer = string.Empty;
string structureField = string.Empty;
string isSingleKey = "true";
if (Session.Keys.Count > 1)
{
isSingleKey = "false";
}
string template = string.Empty;
template = ManageTemplate.GetTemplate("CrudTemplateAutoTask.ico");
//parametersListInsert = GetParametersList(dt, tableName, false);
//parametersListUpdate = GetParametersList(dt, tableName, false);
//parametersListDelete = GetParametersList(dt, tableName, true);
GetParametersListAll(dt, ref parametersListInsert, ref parametersListDelete, ref parametersListClone, ref parameters, provider);
parametersListUpdate = parametersListInsert;
SetAllKeys(ref declarationParametersKeys, ref listParameterKeys, ref parametersKeysWithOutType, ref isNullKeys, ref parameterKeys);
structureField = GetStructure(dt);
fieldBoolean = CustomBoolean.GetList();
//declarationParametersKeys = SetDeclarationKeys();
//parameterKeys = GetParametersDictionaryList(Session.Keys, tableName);
//listParameterKeys = SetParametersKeys();
//isNullKeys = SetNullKeysCondtition();
template = template.Replace("#Version#", Session.Version);
template = template.Replace("#Date#", System.DateTime.Now.ToString("dd/MM/yyyy"));
template = template.Replace("#Owner#", Session.Owner);
template = template.Replace("***NameSpace***", Session.NamespaceDataLayer);
template = template.Replace("***ClassName***", tableName);
template = template.Replace("***StructureField***", structureField);
template = template.Replace("***PrivateField***", privateField);
template = template.Replace("***PublicField***", publicField);
template = template.Replace("***MappingDataReader***", mapping);
template = template.Replace("***MappingDataTable***", mapping);
template = template.Replace("***Parameters***", parameters);
template = template.Replace("***KetType***", keyType);
template = template.Replace("***isAutomaticKey***", isAutomaticKey);
foreach (KeyValuePair <string, KeyColumn> kvp in Session.Keys)
{
template = template.Replace("***TableKey***", kvp.Key);
if (!kvp.Value.NetType.Equals("String"))
{
template = template.Replace("***TableKeyType***", string.Concat(kvp.Value.NetType, ".Parse"));
}
else
{
template = template.Replace("***TableKeyType***", string.Empty);
}
break;
}
template = template.Replace("***DeclarationParameterKeys***", declarationParametersKeys);
template = template.Replace("***ParameterKeys***", parameterKeys);
template = template.Replace("***ListParameterKeys***", listParameterKeys);
template = template.Replace("***ParametersListInsert***", parametersListInsert);
template = template.Replace("***ParametersListUpdate***", parametersListUpdate);
template = template.Replace("***ParametersListDelete***", parametersListDelete);
template = template.Replace("***ParametersListClone***", parametersListClone);
template = template.Replace("***Collection***", Session.GetCollectionFunction);
//template = template.Replace("#GetCollectionSql#", GetViewSql(dt, tableName, true));
//template = template.Replace("#GetCollectionSqlWithOutOrder#", GetViewSql(dt, tableName, false));
template = template.Replace("#GetCollectionByKeys#", "GetCollectionByKeys");
template = template.Replace("***FieldBoolean***", fieldBoolean);
template = template.Replace("***IsSingleKey***", isSingleKey);
template = template.Replace("***TypeKey***", keyType);
if (Session.SuperClass)
{
string minorField = string.Empty;
string tableNameRelationship = string.Empty;
DataTable dtRelationship = GetDatatableSqlBase.GetRelationship(tableName);
foreach (DataRow drRelationship in dtRelationship.Rows)
{
if (!tableNameRelationship.Equals(drRelationship["REFDTBNAME"].ToString()))
{
tableNameRelationship = drRelationship["REFDTBNAME"].ToString();
DataTable dtMinor = GetDatatableSqlBase.GetSchema(drRelationship["REFDTBNAME"].ToString());
minorField = string.Concat(minorField, GetPublicFieldForSuperClass(dtMinor, tableNameRelationship));
}
tableNameRelationship = drRelationship["REFDTBNAME"].ToString();
}
template = template.Replace("***SuperClass***", minorField);
template = template.Replace("***DeclareRelationship***", GetDeclareRelationship(tableName));
template = template.Replace("***DeclareIndex***", GetDeclareIndex(tableName));
}
else
{
template = template.Replace("***SuperClass***", string.Empty);
template = template.Replace("***DeclareRelationship***", string.Empty);
template = template.Replace("***DeclareIndex***", string.Empty);
}
//GetSql(dt, tableName, ref queryInsert, ref queryUpdate, ref queryDelete);
//template = template.Replace("#GetByCodeSql#", GetByCodeSql(dt, tableName));
//template = template.Replace("#GetByCodeSqlUpdVer#", GetByCodeSqlUpdVer(dt, tableName));
template = template.Replace("***SWITCHCUSTOMFIELDS***", GetSwitchCustomFields(dt));
template = template.Replace("***FIELDENUM***", GetFieldEnum(dt));
template = template.Replace("***InsertSql***", queryInsert);
template = template.Replace("#UpdateSql#", queryUpdate);
template = template.Replace("#DeleteSql#", queryDelete);
template = template.Replace("***Insert***", string.Format(Session.InsertFunction, tableName));
template = template.Replace("#InsertKey#", "InsertKey");
template = template.Replace("***Update***", string.Format(Session.UpdateFunction, tableName));
template = template.Replace("***Delete***", string.Format(Session.DeleteFunction, tableName));
template = template.Replace("***Read***", string.Format(Session.ReadFunction, tableName));
template = template.Replace("***GetCollection***", string.Format(Session.GetCollectionFunction, tableName));
template = template.Replace("***GetByCode***", string.Format(Session.GetByCodeFunction, tableName));
template = template.Replace("***IsUnknown***", Session.IsUnknown);
template = template.Replace("***IsUnknownCondtition***", isNullKeys);
template = template.Replace("***CheckExist***", string.Empty);
bool isValid = false;
if (!(ManageTemplate.WriteTemplate(Session.Folder, template, string.Concat(tableName, ""), null)))
{
return(false);
}
//{
// #region Class Plus
// //if (Session.CreateCustomClass)
// //{
// // template = ManageTemplate.GetTemplate("CrudTemplatePlusSqlBase.ico");
// // template = template.Replace("#Version#", Session.Version);
// // template = template.Replace("#Date#", System.DateTime.Now.ToString("dd/MM/yyyy"));
// // template = template.Replace("***NameSpace***", Session.Namespace);
// // template = template.Replace("***ClassName***", tableName);
// // template = template.Replace("***Collection***", Session.GetCollectionFunction);
// // template = template.Replace("***Insert***", string.Format(Session.InsertFunction, tableName));
// // template = template.Replace("#InsertKey#", "InsertKey");
// // template = template.Replace("***Update***", string.Format(Session.UpdateFunction, tableName));
// // template = template.Replace("***Delete***", string.Format(Session.DeleteFunction, tableName));
// // template = template.Replace("***Read***", string.Format(Session.ReadFunction, tableName));
// // template = template.Replace("***GetCollection***", string.Format(Session.GetCollectionFunction, tableName));
// // template = template.Replace("#GetCollectionByKeys#", "GetCollectionByKeys");
// // template = template.Replace("***GetByCode***", string.Format(Session.GetByCodeFunction, tableName));
// // template = template.Replace("***IsUnknown***", Session.IsUnknown);
// // template = template.Replace("***DeclarationParameterKeys***", declarationParametersKeys);
// // template = template.Replace("***DeclarationParameterKeysWithOutType***", parametersKeysWithOutType);
// // if (!ManageTemplate.WriteTemplate(Session.Folder, template, tableName, null)) return false;
// //}
// //template = ManageTemplate.GetTemplate("Interface.ico");
// //template = template.Replace("#Version#", Session.Version);
// //template = template.Replace("#Date#", System.DateTime.Now.ToString("dd/MM/yyyy"));
// //template = template.Replace("***NameSpace***", Session.Namespace);
// //template = template.Replace("***ClassName***", tableName);
// //if (ManageTemplate.WriteTemplate(Session.Folder, template, string.Concat("I", tableName), null)) return true;
// //else return false;
// #endregion
// return false;
//}
return(true);
}
public static DataTable GetSchema(string tableName, string columnNameParameter)
{
CustomBoolean.Clear();
DataTable table = GetDatatableSchema();
OleDbConnection connection = new OleDbConnection(Session.ConnectionString);
connection.Open();
DataTable columns = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);
connection.Close();
foreach (DataRow dr in columns.Rows)
{
if (dr["TABLE_NAME"].ToString().Equals(tableName))
{
string newTableName = dr["TABLE_NAME"].ToString().Trim();
string columnsName = dr["COLUMN_NAME"].ToString().Trim();
bool isOk = true;
if (!columnNameParameter.Equals(string.Empty))
{
isOk = false;
if (columnNameParameter.Equals(columnsName))
{
isOk = true;
}
}
if (isOk)
{
string schema = dr["TABLE_SCHEMA"].ToString().Trim();
int columnsMaxLength = dr["CHARACTER_MAXIMUM_LENGTH"].ToString().Trim().Equals(string.Empty) ? 0 : Int32.Parse(dr["CHARACTER_MAXIMUM_LENGTH"].ToString().Trim());
bool columnsNullable = dr["IS_NULLABLE"].ToString().Trim().ToUpper().StartsWith("True") ? true : false;
string columnsType = GetAccessType(columnsName, dr["DATA_TYPE"].ToString(), columnsMaxLength, columnsNullable).Trim();
string columnsDBType = GetAccessType(columnsName, dr["DATA_TYPE"].ToString(), columnsMaxLength, columnsNullable).Trim();
string businessName = dr["COLUMN_NAME"].ToString().Trim();
string description = string.Empty;
if (!businessName.Equals(string.Empty))
{
if (businessName.Contains("-"))
{
string[] array = businessName.Split('-');
businessName = array[0].Trim();
description = array[1].Trim();
}
}
DataRow row = table.NewRow();
row["Name"] = columnsName;
row["FieldName"] = columnsName;
row["Type"] = columnsType;
row["DbType"] = columnsDBType;
row["Length"] = columnsMaxLength;
row["Nullable"] = columnsNullable;
row["Table"] = newTableName;
row["Schema"] = schema;
row["BusinessName"] = businessName;
row["Description"] = description;
table.Rows.Add(row);
}
}
}
return(table);
}
public static DataTable GetSchema(string tableName, string columnNameParameter)
{
CustomBoolean.Clear();
DataTable table = GetDatatableSchema();
string query = string.Format("select * from information_schema.columns where table_name = '{0}' order by ordinal_position", tableName);
DataTable columns = SqlHelperPostGres.ExecuteDataTable(Session.ConnectionString, CommandType.Text, query, null);
foreach (DataRow dr in columns.Rows)
{
if (dr["table_name"].ToString().Equals(tableName))
{
string newTableName = dr["table_name"].ToString().Trim();
string columnsName = dr["column_name"].ToString().Trim();
bool isOk = true;
if (!columnNameParameter.Equals(string.Empty))
{
isOk = false;
if (columnNameParameter.Equals(columnsName))
{
isOk = true;
}
}
if (isOk)
{
string schema = dr["table_schema"].ToString().Trim();
int columnsMaxLength = dr["character_octet_length"].ToString().Trim().Equals(string.Empty) ? 0 : Int32.Parse(dr["character_octet_length"].ToString().Trim());
bool columnsNullable = dr["is_nullable"].ToString().Trim().ToUpper().StartsWith("True") ? true : false;
string columnsType = GetType(columnsName, dr["udt_name"].ToString().Trim(), columnsMaxLength, columnsNullable).Trim();
string columnsDBType = GetType(columnsName, dr["udt_name"].ToString().Trim(), columnsMaxLength, columnsNullable).Trim();
string businessName = dr["column_name"].ToString().Trim();
string description = string.Empty;
if (!businessName.Equals(string.Empty))
{
if (businessName.Contains("-"))
{
string[] array = businessName.Split('-');
businessName = array[0].Trim();
description = array[1].Trim();
}
}
DataRow row = table.NewRow();
row["Name"] = columnsName;
row["FieldName"] = columnsName;
row["Type"] = columnsType;
row["DbType"] = columnsDBType;
row["Length"] = columnsMaxLength;
row["Nullable"] = columnsNullable;
row["Table"] = newTableName;
row["Schema"] = schema;
row["BusinessName"] = businessName;
row["Description"] = description;
table.Rows.Add(row);
}
}
}
return(table);
}
