public string CreateInsertSQL()
{
Type type = typeof(T);
string tableName = type.Name;
if ((type.GetCustomAttributes(typeof(TableAttribute), false).Length) > 0)
{
TableAttribute db = (TableAttribute)type.GetCustomAttributes(typeof(TableAttribute), false)[0];
if (db.Name.Length > 0)
{
tableName = db.Name;
}
}
StringBuilder sb = new StringBuilder();
StringBuilder sbVals = new StringBuilder();
sb.Append("INSERT INTO [" + _sqlSchema + "].[" + tableName + "] (");
sbVals.Append(") VALUES (");
int amnt = props.Length + addedParameters.Count;
int cnt = 0;
string primaryKey = FindPrimaryKey();
foreach (PropertyInfo param in props)
{
if (param.PropertyType.Namespace != "System.Collections.Generic")
{
string colName = param.Name;
DBAttribute db = DataHelper.FindAttribute <DBAttribute>(param);
if (db != null && db.ColumnName != null && db.ColumnName.Length > 0)
{
colName = db.ColumnName;
}
addSqlParams(primaryKey, colName, amnt, cnt, param.PropertyType.BaseType.Name, ref sb, ref sbVals);
}
cnt++;
}
foreach (KeyValuePair <string, object> kvp in addedParameters)
{
addSqlParams(primaryKey, kvp.Key, amnt, cnt, "", ref sb, ref sbVals);
cnt++;
}
sbVals.Append(" )");
sbVals.AppendFormat(" SELECT * FROM [{2}].[{0}] WHERE {1} = SCOPE_IDENTITY()", tableName, primaryKey, _sqlSchema);
return(sb.ToString() + sbVals.ToString());
}
public string CreateSelectByIDSQL()
{
Type type = typeof(T);
string tableName = type.Name;
if ((type.GetCustomAttributes(typeof(TableAttribute), false).Length) > 0)
{
TableAttribute db = (TableAttribute)type.GetCustomAttributes(typeof(TableAttribute), false)[0];
if (db.Name.Length > 0)
{
tableName = db.Name;
}
}
StringBuilder sb = new StringBuilder();
StringBuilder sbVals = new StringBuilder();
sb.Append("SELECT ");
int amnt = props.Length;
int cnt = 0;
string primaryKey = FindPrimaryKey();
foreach (PropertyInfo param in props)
{
if (param.PropertyType.Namespace != "System.Collections.Generic")
{
string colName = param.Name;
DBAttribute db = DataHelper.FindAttribute <DBAttribute>(param);
if (db != null && db.ColumnName != null && db.ColumnName.Length > 0)
{
colName = db.ColumnName;
}
addSqlParams(string.Empty, colName, amnt, cnt, param.PropertyType.BaseType.Name, ref sb, ref sbVals);
}
cnt++;
}
sb.Append(" FROM [" + _sqlSchema + "].[" + tableName + "] WHERE " + primaryKey + " = @" + primaryKey);
return(sb.ToString());
}
public string CreateUpdateWConcurrencySQL(T original)
{
Type type = typeof(T);
StringBuilder sb = new StringBuilder();
sb.Append(CreateUpdateSQL() + " ");
// int amnt = props.Length;
int cnt = 0;
string primaryKey = FindPrimaryKey();
foreach (PropertyInfo param in props)
{
if (param.PropertyType.Namespace != "System.Collections.Generic")
{
if (primaryKey != param.Name && param.PropertyType.BaseType.Name != "DataObj" && !param.Name.StartsWith("FK_"))
{
bool isBigTextOrimage = false;
if (param.GetCustomAttributes(typeof(DBAttribute), false).Length > 0)
{
DBAttribute db = (DBAttribute)param.GetCustomAttributes(typeof(DBAttribute), false)[0];
if (db != null)
{
isBigTextOrimage = db.IsBigTextOrImage;
}
}
if (isBigTextOrimage)
{
sb.Append(" AND (Cast([" + param.Name + "] AS Varchar(max)) = Cast(@original_" + param.Name + " AS Varchar(max)) OR @original_" + param.Name + "_isNull =1 AND @original_" + param.Name + " IS null)");
}
else if (param.GetType().Name == "String" || param.GetType().Name == "Boolean" || param.GetType().Name == "DateTime" || param.GetType().Name == "Int32")
{
sb.Append(" AND ([" + param.Name + "] = @original_" + param.Name + " OR @original_" + param.Name + "_isNull =1 AND @original_" + param.Name + " IS null)");
}
}
}
cnt++;
}
return(sb.ToString());
}
public string CreateSelectJoinByParamsSQL(string[] paramList, Dictionary <string, bool> orderByList, string joinTable)
{
Type type = typeof(T);
string tableName = type.Name;
if ((type.GetCustomAttributes(typeof(TableAttribute), false).Length) > 0)
{
TableAttribute db = (TableAttribute)type.GetCustomAttributes(typeof(TableAttribute), false)[0];
if (db.Name.Length > 0)
{
tableName = db.Name;
}
}
StringBuilder sb = new StringBuilder();
StringBuilder sbVals = new StringBuilder();
sb.Append("SELECT ");
int amnt = props.Length;
int cnt = 0;
string primaryKey = FindPrimaryKey();
foreach (PropertyInfo param in props)
{
if (param.PropertyType.Namespace != "System.Collections.Generic")
{
string colName = param.Name;
DBAttribute db = DataHelper.FindAttribute <DBAttribute>(param);
if (db != null && db.ColumnName != null && db.ColumnName.Length > 0)
{
colName = db.ColumnName;
}
addSqlParamsWithJoin("a", string.Empty, colName, amnt, cnt, param.PropertyType.BaseType.Name, ref sb, ref sbVals);
}
cnt++;
}
sb.AppendFormat(" FROM [{3}].[{0}] a LEFT JOIN [{3}].[{1}] b ON a.[{2}] = b.[{2}] ", tableName, joinTable, primaryKey, _sqlSchema);
if (paramList.Length > 0)
{
sb.Append("WHERE ");
}
amnt = paramList.Length;
cnt = 0;
foreach (string param in paramList)
{
if (cnt < amnt - 1)
{
sb.Append("b.[" + param + "] = @" + param + " AND ");
}
else
{
sb.Append("b.[" + param + "] = @" + param);
}
cnt++;
}
if (orderByList != null)
{
if (orderByList.Count > 0)
{
sb.Append(" ORDER BY ");
amnt = orderByList.Count;
cnt = 0;
foreach (KeyValuePair <string, bool> param in orderByList)
{
string ascDesc = " ASC";
if (!param.Value)
{
ascDesc = " DESC";
}
if (cnt < amnt - 1)
{
sb.Append("[" + param.Key + "]" + ascDesc + ", ");
}
else
{
sb.Append("[" + param.Key + "]" + ascDesc);
}
cnt++;
}
}
}
else
{
sb.AppendFormat(" ORDER BY [{0}] ASC", tableName + "ID");
}
return(sb.ToString());
}
public string CreateSelectByParamsSQL(Dictionary <string, object> whereVals, Dictionary <string, bool> orderByList)
{
Type type = typeof(T);
string tableName = type.Name;
if ((type.GetCustomAttributes(typeof(TableAttribute), false).Length) > 0)
{
TableAttribute db = (TableAttribute)type.GetCustomAttributes(typeof(TableAttribute), false)[0];
if (db.Name.Length > 0)
{
tableName = db.Name;
}
}
StringBuilder sb = new StringBuilder();
StringBuilder sbVals = new StringBuilder();
sb.Append("SELECT ");
int amnt = props.Length;
int cnt = 0;
// string primaryKey = FindPrimaryKey();
foreach (PropertyInfo param in props)
{
if (param.PropertyType.Namespace != "System.Collections.Generic")
{
string colName = param.Name;
DBAttribute db = DataHelper.FindAttribute <DBAttribute>(param);
if (db != null && db.ColumnName != null && db.ColumnName.Length > 0)
{
colName = db.ColumnName;
}
addSqlParams(string.Empty, colName, amnt, cnt, param.PropertyType.BaseType.Name, ref sb, ref sbVals);
}
cnt++;
}
sb.Append(" FROM [" + _sqlSchema + "].[" + tableName + "] ");
if (whereVals != null)
{
if (whereVals.Count > 0)
{
sb.Append("WHERE ");
}
amnt = whereVals.Count;
cnt = 0;
foreach (var param in whereVals)
{
string and = " AND ";
if (cnt == (amnt - 1))
{
and = "";
}
if (param.Value == DBNull.Value)
{
sb.AppendFormat("ISNull({0}, -1) = ISNull(@{0}, -1) {1}", param.Key, and);
}
else
{
sb.AppendFormat("{0} = @{0} {1}", param.Key, and);
}
cnt++;
}
}
if (orderByList != null)
{
if (orderByList.Count > 0)
{
sb.Append(" ORDER BY ");
amnt = orderByList.Count;
cnt = 0;
foreach (KeyValuePair <string, bool> param in orderByList)
{
string ascDesc = " ASC";
if (!param.Value)
{
ascDesc = " DESC";
}
if (cnt < amnt - 1)
{
sb.Append("[" + param.Key + "]" + ascDesc + ", ");
}
else
{
sb.Append("[" + param.Key + "]" + ascDesc);
}
cnt++;
}
}
}
return(sb.ToString());
}
public SqlCommand CreateObjectParams(SqlCommand command, T obj, T original, bool includePrimary)
{
// Type type = typeof(T);
string primaryKey = FindPrimaryKey();
foreach (PropertyInfo param in props)
{
ValidationAttribute[] vals = (ValidationAttribute[])param.GetCustomAttributes(typeof(ValidationAttribute), false);
if (param.PropertyType.Namespace != "System.Collections.Generic")
{
if (param.PropertyType.BaseType.Name != _baseTypeName)
{
if ((primaryKey != param.Name && !includePrimary || includePrimary) && !param.Name.StartsWith("FK_"))
{
string typeName = param.PropertyType.Name;
if (Nullable.GetUnderlyingType(param.PropertyType) != null)
{
typeName = Nullable.GetUnderlyingType(param.PropertyType).Name;
}
SqlDbType dbType = DataHelper.FindParamType(typeName);
int maxChars = 150;
if (vals.Length > 0)
{
maxChars = vals[0].MaxChars;
}
SqlParameter myParam;
if (param.PropertyType.Name == "String")
{
myParam = command.Parameters.Add("@" + param.Name, dbType, maxChars);
}
else
{
myParam = command.Parameters.Add("@" + param.Name, dbType);
}
object paramValue = param.GetValue(obj, null);
myParam.Value = GetCleansedValue(paramValue);
bool isBigTextOrimage = false;
if (param.GetCustomAttributes(typeof(DBAttribute), false).Length > 0)
{
DBAttribute db = (DBAttribute)param.GetCustomAttributes(typeof(DBAttribute), false)[0];
if (db != null)
{
isBigTextOrimage = db.IsBigTextOrImage;
}
}
if (primaryKey != param.Name && (isBigTextOrimage || param.PropertyType.Name == "String" || param.GetType().Name == "Boolean" || param.GetType().Name == "DateTime" || param.GetType().Name == "Int32"))
{
SqlParameter concurrParam;
if (param.PropertyType.Name == "String")
{
concurrParam = command.Parameters.Add("@original_" + param.Name, dbType, maxChars);
}
else
{
concurrParam = command.Parameters.Add("@original_" + param.Name, dbType);
}
object concurrValue = param.GetValue(original, null);
concurrParam.Value = GetCleansedValue(concurrValue);
SqlParameter concurrIsNullParam;
concurrIsNullParam = command.Parameters.Add("@original_" + param.Name + "_IsNull", SqlDbType.Bit);
concurrIsNullParam.Value = (concurrParam.Value == DBNull.Value ? 1 : 0);
}
}
}
}
}
return(command);
}
