public virtual void WriteUpdateToSQL(string tableName, T ModelToSubmit, string pKeyName, string connStr = "")
{
if (connStr == string.Empty)
{
connStr = CommonProcs.WCompanyConnStr;
}
var NotMapped = new List <String>();
var props = typeof(T).GetProperties().Where(x => Attribute.IsDefined(x, typeof(NotMappedAttribute)));
foreach (var prop in props)
{
NotMapped.Add(prop.Name);
}
props = typeof(T).GetProperties().Where(x => Attribute.IsDefined(x, typeof(DatabaseGeneratedAttribute)));
foreach (var prop in props)
{
NotMapped.Add(prop.Name);
}
NotMapped.Add(pKeyName);
string sql = "UPDATE " + tableName + " SET ";
var properties = typeof(T).GetProperties();
foreach (var prop in properties)
{
if (!NotMapped.Contains(prop.Name))
{
var value = ModelToSubmit.GetType().GetProperty(prop.Name).GetValue(ModelToSubmit);
if (value != null)
{
if (prop.PropertyType.Name == "Int32")
{
sql += prop.Name + "=" + value + ",";
}
if (prop.PropertyType.Name == "Decimal")
{
sql += prop.Name + "=" + value + ",";
}
else if (prop.PropertyType.Name == "String")
{
sql += prop.Name + "='" + FSQ(value.ToString()) + "',";
}
else if (prop.PropertyType.Name == "Boolean")
{
sql += prop.Name + "=" + ConvertToBool((bool)value) + ",";
}
else if (prop.PropertyType.Name == "DateTime")
{
sql += prop.Name + "='" + value + "',";
}
}
}
}
sql = sql.Substring(0, sql.Length - 1);
sql += " WHERE " + pKeyName + "=" + ModelToSubmit.GetType().GetProperty(pKeyName).GetValue(ModelToSubmit);
using (clsDataGetter dg = new clsDataGetter(connStr))
{
dg.RunCommand(sql);
}
}
public virtual int WriteInsertSQL(string tableName, T ModelToSubmit, string pKeyName, string connStr = "")
{
if (connStr == string.Empty)
{
connStr = CommonProcs.WCompanyConnStr;
}
int newSubmitID = -1;
var NotMapped = new List <String>();
var props = typeof(T).GetProperties().Where(x => Attribute.IsDefined(x, typeof(NotMappedAttribute)));
foreach (var prop in props)
{
NotMapped.Add(prop.Name);
}
props = typeof(T).GetProperties().Where(x => Attribute.IsDefined(x, typeof(DatabaseGeneratedAttribute)));
foreach (var prop in props)
{
NotMapped.Add(prop.Name);
}
NotMapped.Add(pKeyName);
string sql = "INSERT INTO " + tableName + "(";
var properties = typeof(T).GetProperties();
foreach (var prop in properties)
{
if (!NotMapped.Contains(prop.Name))
{
sql += prop.Name + ",";
}
}
sql = sql.Substring(0, sql.Length - 1);
sql += ") VALUES(";
foreach (var prop in properties)
{
{
if (!NotMapped.Contains(prop.Name))
{
var propertyType = prop.PropertyType;
if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
propertyType = propertyType.GetGenericArguments()[0];
}
var value = ModelToSubmit.GetType().GetProperty(prop.Name).GetValue(ModelToSubmit);
if (propertyType.Name == "Int32")
{
if (value == null)
{
value = 0;
}
sql += value + ",";
}
if (propertyType.Name == "Decimal")
{
if (value == null)
{
value = 0.0M;
}
sql += value + ",";
}
else if (propertyType.Name == "String")
{
if (value == null)
{
value = "";
}
sql += "'" + FSQ(value.ToString()) + "',";
}
else if (propertyType.Name == "Boolean")
{
if (value == null)
{
value = false;
}
sql += ConvertToBool((bool)value) + ",";
}
else if (propertyType.Name == "DateTime")
{
if (value == null)
{
value = DateTime.Now.ToShortDateString();
}
sql += "'" + value + "',";
}
}
}
}
sql = sql.Substring(0, sql.Length - 1);
sql += ")";
using (clsDataGetter dg = new clsDataGetter(connStr))
{
try
{
dg.RunCommand(sql);
}
catch (Exception ex)
{
string x = ex.Message;
}
newSubmitID = dg.GetScalarInteger("SELECT MAX(" + pKeyName + ") FROM " + tableName);
}
return(newSubmitID);
}