public static void DeleteData(object obj)
{
string tableName = GetTableName(obj, false);
List <string> fieldNames = new List <string>();
List <object> values = new List <object>();
string whereStr = "";
SqlParameter primaryKey = null;
if (tableName != "")
{
foreach (PropertyInfo prop in obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
object objValue = prop.GetValue(obj);
SqlFieldName sField = GetPropertySqlFieldName(prop);
if (sField != null)
{
if (sField.isPrimaryKey)
{
whereStr = " WHERE " + sField.Name + " = @primaryKey";
primaryKey = new SqlParameter("@primaryKey", prop.GetValue(obj));
}
else
{
fieldNames.Add(sField.Name);
values.Add(GetCsvConversion(prop, obj));
}
}
}
}
else
{
throw new Exception("No table name found for '" + obj.GetType().Name + "'.");
}
if (primaryKey == null)
{
throw new Exception("No primary key found for '" + obj.GetType().Name + "'.");
}
if (whereStr != "")
{
SqlConnection dataConn = new SqlConnection(GetConnectionString());
SqlCommand cmd = new SqlCommand("DELETE FROM " + tableName + " " + whereStr, dataConn);
cmd.Parameters.Add(primaryKey);
dataConn.Open();
SqlDataReader dRead = cmd.ExecuteReader();
dataConn.Close();
}
else
{
throw new Exception("No WHERE clause was available for DELETE.");
}
}
public static T GetRecord <T>(object id) where T : DataItem, new()
{
SqlConnection dataConn = new SqlConnection(GetConnectionString());
string tableName = GetTableName <T>(true);
if (tableName == "")
{
throw new Exception("No SqlTableName attribute is set for object '" + typeof(T).Name + "'.");
}
string whereStr = "";
foreach (PropertyInfo prop in typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
SqlFieldName sField = GetPropertySqlFieldName(prop);
if (sField != null)
{
if (sField.isPrimaryKey)
{
whereStr = " WHERE " + sField.Name + " = @id";
}
}
}
if (whereStr == "")
{
throw new Exception("Couldn't find a primary key for object with SqlTableName '" + tableName + "'.");
}
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM " + tableName + " " + whereStr, dataConn);
dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@id", id));
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
if (dt.Rows.Count == 1)
{
List <T> lItems = new List <T>();
foreach (DataRow dRow in dt.Rows)
{
lItems.Add(FromDataRow <T>(dRow));
}
return(lItems[0]);
}
else
{
return(default(T));
}
}
public DataItem(object Id)
{
var property = DataFunctions.GetPrimaryKey(this);
if (property != null)
{
SqlFieldName sField = DataFunctions.GetPropertySqlFieldName(property);
//DataFunctions.GetRecord<>
object item = typeof(DataFunctions)
.GetMethod("GetRecord")
.MakeGenericMethod(this.GetType())
.Invoke(null, new object[] { Id });
if (item == null)
{
throw new Exception("Item cannot be found.");
}
DataFunctions.ShallowCopy(item, this);
this._newRecord = false;
}
// look for relationships
var relationships = DataFunctions.GetRelationshipDataLists(this.GetType());
foreach (var r in relationships)
{
DataFunctions.TypeRelationship tR = DataFunctions.GetRelationship(r.PropertyType, this.GetType(), r.PropertyType.GetGenericArguments()[0]);
if (tR.ForeignRelationship.Relationship == SqlRelatedTable.DataRelationship.MANY_TO_ONE && tR.LocalRelationship.Relationship == SqlRelatedTable.DataRelationship.ONE_TO_MANY)
{
// we found the related property
SqlFieldName fieldName = DataFunctions.GetPropertySqlFieldName(tR.ForeignProperty);
// construct our generic type
List <object> retList = DataFunctions.GetData(r.PropertyType.GetGenericArguments()[0],
0,
0,
"WHERE " + fieldName.Name + " = @fieldid", "",
new List <SqlParameter>()
{
new SqlParameter("@fieldid", tR.LocalProperty.GetValue(this))
});
var converted = DataFunctions.ConvertList(retList, r.PropertyType);
r.SetValue(this, converted);
break;
}
}
}
private static List <string> GetDataFields <T>() where T : DataItem
{
List <string> fields = new List <string>();
foreach (PropertyInfo prop in typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
SqlFieldName sField = GetPropertySqlFieldName(prop);
if (sField != null)
{
fields.Add(sField.Name);
}
}
return(fields);
}
internal static PropertyInfo GetPrimaryKey <T>()
{
foreach (PropertyInfo prop in typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
SqlFieldName sField = GetPropertySqlFieldName(prop);
if (sField != null)
{
if (sField.isPrimaryKey)
{
return(prop);
}
}
}
return(null);
}
internal static PropertyInfo GetAutoNumber(object obj)
{
foreach (PropertyInfo prop in obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
SqlFieldName sField = GetPropertySqlFieldName(prop);
if (sField != null)
{
if (sField.isAutoNumber)
{
return(prop);
}
}
}
return(null);
}
private static object FromDataRow(Type ofType, DataRow dRow)
{
DataItem obj = Activator.CreateInstance(ofType) as DataItem;
obj._newRecord = false;
foreach (PropertyInfo prop in ofType.GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
SqlFieldName sField = GetPropertySqlFieldName(prop);
if (sField != null)
{
if (dRow.Table.Columns.Contains(sField.Name))
{
if (dRow[sField.Name].GetType() == typeof(DBNull))
{
prop.SetValue(obj, null);
}
else
{
if (dRow[sField.Name].GetType() == typeof(string))
{
if (prop.PropertyType == typeof(Guid))
{
prop.SetValue(obj, Guid.Parse(dRow[sField.Name].ToString()));
}
else
{
prop.SetValue(obj, dRow[sField.Name]);
}
}
else
{
prop.SetValue(obj, dRow[sField.Name]);
}
}
}
}
}
obj.DataFilled();
return(obj);
}
public static T FromDataRow <T>(DataRow dRow) where T : DataItem, new()
{
T obj = new T();
obj._newRecord = false;
foreach (PropertyInfo prop in typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
SqlFieldName sField = GetPropertySqlFieldName(prop);
if (sField != null)
{
if (dRow.Table.Columns.Contains(sField.Name))
{
if (dRow[sField.Name].GetType() == typeof(DBNull))
{
prop.SetValue(obj, null);
}
else
{
if (dRow[sField.Name].GetType() == typeof(string))
{
if (prop.PropertyType == typeof(Guid))
{
prop.SetValue(obj, Guid.Parse(dRow[sField.Name].ToString()));
}
else
{
SetCsvConversion(prop, obj, dRow[sField.Name]);
}
}
else
{
prop.SetValue(obj, dRow[sField.Name]);
}
}
}
}
}
obj.DataFilled();
return(obj);
}
public static object InsertData(object obj)
{
string tableName = GetTableName(obj, false);
List <string> fieldNames = new List <string>();
List <object> values = new List <object>();
string autoNumberField = "id"; // default autonumber field
PropertyInfo autoNumberProperty = null;
if (tableName != "")
{
foreach (PropertyInfo prop in obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
object objValue = prop.GetValue(obj);
SqlFieldName sField = GetPropertySqlFieldName(prop);
if (sField != null)
{
if (!sField.isAutoNumber && !sField.isReadOnly)
{
fieldNames.Add(sField.Name);
values.Add(GetCsvConversion(prop, obj));
}
if (sField.isAutoNumber)
{
autoNumberField = sField.Name;
autoNumberProperty = prop;
}
}
}
}
if (fieldNames.Count > 0)
{
string fieldStr = "";
string valueStr = "";
SqlConnection dataConn = new SqlConnection(GetConnectionString());
SqlCommand cmd = new SqlCommand("INSERT INTO " + tableName + " ", dataConn);
bool hasValues = false;
for (var i = 0; i < fieldNames.Count; i++)
{
if (values[i] != null)
{
bool skipValue = false;
if (values[i].GetType() == typeof(DateTime))
{
if ((DateTime)values[i] == DateTime.MinValue)
{
// TODO: If MinValue, assume null. Not fantastic, but will do for now.
skipValue = true;
}
}
if (!skipValue)
{
hasValues = true;
fieldStr += fieldNames[i] + ", ";
valueStr += "@" + fieldNames[i] + ", ";
cmd.Parameters.Add(new SqlParameter("@" + fieldNames[i], values[i]));
}
}
}
if (hasValues)
{
fieldStr = "(" + fieldStr.Substring(0, fieldStr.Length - 2) + ")";
valueStr = "(" + valueStr.Substring(0, valueStr.Length - 2) + ")";
cmd.CommandText += fieldStr + " VALUES " + valueStr + "; SELECT IDENT_CURRENT('" + tableName + "');";
dataConn.Open();
object retVal;
try
{
retVal = cmd.ExecuteScalar();
}
catch (Exception ex)
{
Exception outEx = new Exception("Error executing statement: " + cmd.CommandText, ex);
throw outEx;
}
dataConn.Close();
if (autoNumberProperty != null)
{
if (autoNumberProperty.PropertyType == typeof(Guid))
{
Guid retGuid = Guid.Parse(retVal.ToString());
autoNumberProperty.SetValue(obj, retGuid);
return(retGuid);
}
else
{
long outId = Convert.ToInt64(retVal);
autoNumberProperty.SetValue(obj, Convert.ChangeType(outId, autoNumberProperty.PropertyType));
return(outId);
}
}
}
}
return(-1);
}
public static void UpdateData(object obj)
{
string tableName = GetTableName(obj, false);
List <string> fieldNames = new List <string>();
List <object> values = new List <object>();
string whereStr = "";
SqlParameter whereValue = null;
if (tableName != "")
{
foreach (PropertyInfo prop in obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
object objValue = prop.GetValue(obj);
SqlFieldName sField = GetPropertySqlFieldName(prop);
if (sField != null)
{
if (sField.isPrimaryKey)
{
// TODO: This only really works if we assume an integer value - should add to parameters instead
whereStr = " WHERE " + sField.Name + " = @insert_id__";
whereValue = new SqlParameter("@insert_id__", prop.GetValue(obj));
}
else if (sField.isReadOnly == false)
{
fieldNames.Add(sField.Name);
values.Add(GetCsvConversion(prop, obj));
}
}
}
}
if (fieldNames.Count > 0)
{
string valueStr = "";
SqlConnection dataConn = new SqlConnection(GetConnectionString());
SqlCommand cmd = new SqlCommand("UPDATE " + tableName + " SET ", dataConn);
bool hasValues = false;
for (int i = 0; i < fieldNames.Count; i++)
{
if (values[i] != null)
{
bool skipValue = false;
if (values[i] is DateTime)
{
// nulls get converted to DateTime.MinValue when reading, which then isn't compatible with
// SQL when putting back - so don't update this field.
DateTime dTime = (DateTime)values[i];
if (dTime == DateTime.MinValue)
{
skipValue = true;
}
}
if (!skipValue)
{
hasValues = true;
valueStr += fieldNames[i] + " = @" + fieldNames[i] + ", ";
cmd.Parameters.Add(new SqlParameter("@" + fieldNames[i], values[i]));
}
}
}
if (hasValues)
{
if (whereValue != null)
{
cmd.Parameters.Add(whereValue);
}
valueStr = valueStr.Substring(0, valueStr.Length - 2);
cmd.CommandText += valueStr + whereStr;
dataConn.Open();
SqlDataReader dRead = cmd.ExecuteReader();
dataConn.Close();
}
}
}
public static List <object> GetData(Type ofType, int numRecords, int pageNumber, string whereClause = "", string overrideTableName = "", List <SqlParameter> sqlParameters = null, string orderBy = "ORDER BY ??")
{
object tempObj = Activator.CreateInstance(ofType);
SqlConnection dataConn = new SqlConnection(GetConnectionString());
string tableName = "";
if (overrideTableName != "")
{
tableName = overrideTableName;
}
else
{
tableName = GetTableName(tempObj, true);
if (tableName == "")
{
throw new Exception("No SqlTableName attribute is set for object '" + ofType.Name + "'.");
}
}
if (whereClause != "")
{
whereClause = " " + whereClause;
}
if (orderBy == "ORDER BY ??")
{
// we need to find the primary key field
PropertyInfo pInfo = GetPrimaryKey(tempObj);
if (pInfo != null)
{
SqlFieldName sField = GetPropertySqlFieldName(pInfo);
orderBy = "ORDER BY " + sField.Name;
}
else
{
throw new Exception("Cannot find primary key to complete ORDER BY statement.");
}
}
SqlDataAdapter dataAdapter = new SqlDataAdapter();
if (numRecords == 0)
{
dataAdapter = new SqlDataAdapter("SELECT * FROM " + tableName + whereClause + " " + orderBy, dataConn);
}
else if (pageNumber > 0)
{
dataAdapter = new SqlDataAdapter("SELECT * FROM " + tableName + whereClause + " " + orderBy + " OFFSET " + (numRecords * pageNumber) + " ROWS FETCH NEXT " + numRecords + " ROWS ONLY", dataConn);
}
else
{
dataAdapter = new SqlDataAdapter("SELECT TOP (" + numRecords + ") * FROM " + tableName + whereClause + " " + orderBy, dataConn);
}
if (sqlParameters != null)
{
foreach (var param in sqlParameters)
{
dataAdapter.SelectCommand.Parameters.Add(param);
}
}
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
List <object> lItems = new List <object>();
foreach (DataRow dRow in dt.Rows)
{
lItems.Add(FromDataRow(ofType, dRow));
}
return(lItems);
//JsonSerializerSettings s = new JsonSerializerSettings();
//s.ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver();
//return JsonConvert.SerializeObject(dt, s);
}
public static List <T> GetData <T>(int numRecords, int pageNumber, string whereClause = "", string overrideTableName = "", List <SqlParameter> sqlParameters = null, string orderBy = "ORDER BY ??", bool distinct = false, Dictionary <string, string> selectOverrides = null) where T : DataItem, new()
{
if (selectOverrides == null)
{
selectOverrides = new Dictionary <string, string>();
}
SqlConnection dataConn = new SqlConnection(GetConnectionString());
string tableName = "";
if (overrideTableName != "")
{
tableName = overrideTableName;
}
else
{
tableName = GetTableName <T>(true);
if (tableName == "")
{
throw new Exception("No SqlTableName attribute is set for object '" + typeof(T).Name + "'.");
}
}
if (whereClause != "")
{
whereClause = " " + whereClause;
}
if (orderBy == "ORDER BY ??")
{
// we need to find the primary key field
PropertyInfo pInfo = GetPrimaryKey <T>();
if (pInfo != null)
{
SqlFieldName sField = GetPropertySqlFieldName(pInfo);
orderBy = "ORDER BY " + sField.Name;
}
else
{
throw new Exception("Cannot find primary key to complete ORDER BY statement.");
}
}
SqlDataAdapter dataAdapter = new SqlDataAdapter();
string selectDirective;
if (distinct)
{
selectDirective = "SELECT DISTINCT ";
}
else
{
selectDirective = "SELECT ";
}
string fields = "";
foreach (string fieldName in GetDataFields <T>())
{
if (selectOverrides.ContainsKey(fieldName))
{
if (!string.IsNullOrEmpty(selectOverrides[fieldName]))
{
fields += selectOverrides[fieldName] + ", ";
}
}
else
{
fields += tableName + "." + fieldName + ", ";
}
}
if (fields.Length > 1)
{
fields = fields.Substring(0, fields.Length - 2);
}
else
{
fields = "*";
}
if (numRecords == 0)
{
dataAdapter = new SqlDataAdapter(selectDirective + fields + " FROM " + tableName + whereClause + " " + orderBy, dataConn);
}
else if (pageNumber > 0)
{
dataAdapter = new SqlDataAdapter(selectDirective + fields + " FROM " + tableName + whereClause + " " + orderBy + " OFFSET " + (numRecords * pageNumber) + " ROWS FETCH NEXT " + numRecords + " ROWS ONLY", dataConn);
}
else
{
dataAdapter = new SqlDataAdapter(selectDirective + "TOP (" + numRecords + ") " + fields + " FROM " + tableName + whereClause + " " + orderBy, dataConn);
}
if (sqlParameters != null)
{
foreach (var param in sqlParameters)
{
dataAdapter.SelectCommand.Parameters.Add(param);
}
}
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
List <T> lItems = new List <T>();
foreach (DataRow dRow in dt.Rows)
{
lItems.Add(FromDataRow <T>(dRow));
}
return(lItems);
//JsonSerializerSettings s = new JsonSerializerSettings();
//s.ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver();
//return JsonConvert.SerializeObject(dt, s);
}
