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); }