/// <summary>
/// Count by any criteria
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <param name="param"></param>
/// <returns></returns>
public static int Count <T>(string where = null, object[] param = null) where T : ModelBase, new()
{
T obj = new T();
int result = 0;
if (obj != null)
{
CinchMapping mappings = Mapper.MapQuery <T>(obj, where, param);
//make sure we have a WHERE
if (!string.IsNullOrWhiteSpace(mappings.QueryString) &&
mappings.QueryString.ToLowerInvariant().IndexOf("where") == -1)
{
mappings.QueryString = String.Format("WHERE {0}", mappings.QueryString);
}
string query = String.Format(Queries.Count, obj.TableNameFullyQualified, obj.TableName, mappings.QueryString);
using (Cinch cinch = new Cinch(query, CommandType.Text))
{
if (mappings.SqlParams != null && mappings.SqlParams.Count > 0)
{
cinch.AddParameters(mappings.SqlParams);
}
result = cinch.ExecuteScalarInt();
}
}
return(result);
}
public static CinchMapping MapProperties <T>(T obj, List <string> cols = null) where T : ModelBase
{
string cacheKey = String.Format("{0}_{1}", obj.ObjName, String.Join("-", cols.ToArray()));
CinchMapping cinchMapping = null;
if (!_cinchMappingCache.ContainsKey(cacheKey))
{
PropertyInfo[] props = obj.GetType().GetProperties();
List <string> columns = new List <string>();
List <string> valuesQueryParams = new List <string>();
List <SqlParameter> sqlParams = new List <SqlParameter>();
int i = 1;
foreach (PropertyInfo prop in props)
{
if (prop.Attributes.GetAttributeFrom <CinchIgnoreAttribute>(prop) != null ||
prop.DeclaringType == typeof(ModelBase) ||
(cols != null && cols.Contains(prop.Name)))
{
continue;
}
#if NET40
object value = prop.GetValue(obj, null);
#else
object value = prop.GetValue(obj);
#endif
if (value == null)
{
continue;
}
Type t = prop.PropertyType;
string placeholder = String.Format("val{0}", i);
columns.Add(String.Format("{0}{1}{2}", SpecialCharacters.ColumnBegin, prop.Name, SpecialCharacters.ColumnEnd));
valuesQueryParams.Add(String.Format("{0}{1}", SpecialCharacters.ParamPrefix, placeholder));
sqlParams.AddParameter(placeholder, Conversion.GetSqlDbType(t), value);
i++;
}
cinchMapping = new CinchMapping()
{
Columns = columns,
ValuesQueryParams = valuesQueryParams,
SqlParams = sqlParams
};
_cinchMappingCache.Add(cacheKey, cinchMapping);
}
else
{
cinchMapping = _cinchMappingCache[cacheKey];
}
return(cinchMapping);
}
/// <summary>
/// UPDATE
/// </summary>
/// <param name="obj">The object to be updated.</param>
/// <param name="cols">The whitelist of columns to be inserted.</param>
/// <returns>Record ID if successfull, -1 otherwise</returns>
public static int Update <T>(T obj, List <string> cols = null) where T : ModelBase
{
int result = -1;
if (obj != null)
{
CinchMapping mappings = Mapper.MapProperties(obj, cols);
string query = String.Format(Queries.Update, obj.TableNameFullyQualified, mappings.UpdateValuesQueryParamsString, obj.PrimaryKeyFullyQualified);
using (Cinch cinch = new Cinch(query, CommandType.Text))
{
cinch.AddParameters(mappings.SqlParams);
cinch.AddParameter("id", SqlDbType.Int, obj.ID);
try
{
result = cinch.ExecuteScalarInt();
}
catch
{
throw;
}
}
}
return(result);
}
/// <summary>
/// Inserts an object to the database.
/// </summary>
/// <param name="obj">The object to be inserted.</param>
/// <param name="cols">The whitelist of columns to be inserted.</param>
/// <returns>Record ID if successfull, -1 otherwise</returns>
public static int Insert <T>(T obj, List <string> cols = null) where T : ModelBase, new()
{
int result = -1;
if (obj != null)
{
CinchMapping mappings = Mapper.MapProperties(obj, cols);
string query = String.Format(Queries.Insert, obj.TableNameFullyQualified, mappings.ColumnsString, mappings.InsertValuesQueryParamsString);
using (Cinch cinch = new Cinch(null, CommandType.Text))
{
cinch.AddParameters(mappings.SqlParams);
try
{
result = cinch.ExecuteScalarInt();
}
catch
{
throw;
}
}
}
return(result);
}
public IEnumerable <T> Query <T>(string query, object[] param = null, CommandType ct = CommandType.Text) where T : new()
{
IEnumerable <T> obj = null;
CinchMapping mappings = Mapper.MapQuery(query, param);
//set query from mapper
cmd.CommandText = query;
cmd.CommandType = ct;
//do we have params?
if (mappings.SqlParams != null && mappings.SqlParams.Count > 0)
{
this.AddParameters(mappings.SqlParams);
}
try
{
//execute cmd and buffer reader
SqlDataReader rd = FillSqlReader();
while (rd.Read())
{
obj = ConvertReaderToEnumerable <T>(rd);
}
rd.Close();
return(obj);
}
catch (SqlException sqlEx)
{
if (sqlEx.Number == 50000)
{
DBException dbx = new DBException(sqlEx.Message, sqlEx);
throw dbx;
}
else
{
throw sqlEx;
}
}
catch
{
throw;
}
finally
{
Close();
}
}
public int Execute(string query, object[] param = null, CommandType ct = CommandType.Text)
{
CinchMapping mappings = Mapper.MapQuery(query, param);
//set query from mapper
cmd.CommandText = query;
cmd.CommandType = ct;
//do we have params?
if (mappings.SqlParams != null && mappings.SqlParams.Count > 0)
{
this.AddParameters(mappings.SqlParams);
}
return(this.ExecuteScalarInt());
}
public static CinchMapping MapQuery(string query, object[] param)
{
string cacheKey = String.Format("{0}_{1}", query, String.Join("-", param));
CinchMapping cinchMapping = null;
if (!_cinchMappingCache.ContainsKey(cacheKey))
{
cinchMapping = new CinchMapping()
{
QueryString = query
};
if (param != null && param.Count() > 0)
{
//where clause has params, but no param values were passed in.
if (!String.IsNullOrWhiteSpace(query) && query.IndexOf('@') > -1 && param.Count() <= 0)
{
throw new ApplicationException(String.Format("Could not execute Find for \"{0}\" because the parameters array is empty", query), new NullReferenceException());
}
//param counts don't match
else if (query.Count(c => c == '@') != param.Count())
{
throw new ApplicationException(String.Format("Could not execute Find for \"{0}\" because the number of parameters in the where clause and parameters array do not match", query), new NullReferenceException());
}
List <SqlParameter> sqlParams = BuildParamsFromString(query, param);
cinchMapping.SqlParams = sqlParams;
}
_cinchMappingCache.Add(cacheKey, cinchMapping);
}
else
{
cinchMapping = _cinchMappingCache[cacheKey];
}
return(cinchMapping);
}