public bool Update(Modellable item)
{
String sqlString = "SELECT * FROM " + tableName + " WHERE ";
SqlCommand cmd = new SqlCommand(sqlString, con);
foreach (String key in args.Keys)
{
sqlString += key + " = @" + key + " AND ";
SqlParameter param = new SqlParameter("@" + key, args[key].Item2, args[key].Item3);
param.Value = args[key].Item1;
cmd.Parameters.Add(param);
}
if (sqlString.Substring(sqlString.Length - 4).Equals("AND "))
{
sqlString = sqlString.Substring(0, sqlString.Length - 4);
}
sqlString += ";";
cmd.CommandText = sqlString;
cmd.Prepare();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
reader.Close();
String[] keyNames = item.FieldsNotSpecifiedInPOST();
String updateSqlString = "UPDATE " + tableName + " SET ";
SqlCommand upCmd = new SqlCommand();
upCmd.Connection = con;
int cur = 1;
var properties = item.GetType().GetProperties();
foreach (var prop in properties)
{
if (!keyNames.Contains(prop.Name))
{
updateSqlString += prop.Name + "=@" + prop.Name;
if (cur != properties.Count())
{
updateSqlString += ",";
}
Tuple <SqlDbType, int> DBTypeAndSize = item.GetAssociatedDBTypeAndSize(prop.Name);
SqlParameter param = new SqlParameter("@" + prop.Name, DBTypeAndSize.Item1, DBTypeAndSize.Item2);
param.Value = (object)prop.GetValue(item, null) ?? DBNull.Value;
upCmd.Parameters.Add(param);
}
cur++;
}
updateSqlString += " WHERE ";
foreach (String key in args.Keys)
{
updateSqlString += key + " = @" + key + " AND ";
SqlParameter param = new SqlParameter("@" + key, args[key].Item2, args[key].Item3);
param.Value = args[key].Item1;
upCmd.Parameters.Add(param);
}
if (updateSqlString.Substring(updateSqlString.Length - 4).Equals("AND "))
{
updateSqlString = updateSqlString.Substring(0, updateSqlString.Length - 4);
}
updateSqlString += ";";
upCmd.CommandText = updateSqlString;
try
{
upCmd.Prepare();
upCmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
else
{
return(false);
}
}
public int Save(Modellable item, String keyToReturn)
{
string[] keyNames = item.FieldsNotSpecifiedInPOST();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
String sqlString = "INSERT INTO " + this.tableName + " (";
int cur = 1;
var properties = item.GetType().GetProperties();
foreach (var prop in properties)
{
if (!keyNames.Contains(prop.Name))
{
sqlString += prop.Name;
if (cur != properties.Count())
{
sqlString += ",";
}
}
cur++;
}
sqlString += ") OUTPUT INSERTED." + keyToReturn + " VALUES (";
cur = 1;
foreach (var prop in properties)
{
if (!keyNames.Contains(prop.Name))
{
sqlString += "@" + prop.Name;
if (cur != properties.Count())
{
sqlString += ",";
}
Tuple <SqlDbType, int> DBTypeAndSize = item.GetAssociatedDBTypeAndSize(prop.Name);
SqlParameter param = new SqlParameter("@" + prop.Name, DBTypeAndSize.Item1, DBTypeAndSize.Item2);
param.Value = (object)prop.GetValue(item, null) ?? DBNull.Value;
cmd.Parameters.Add(param);
}
cur++;
}
sqlString += ");";
cmd.CommandText = sqlString;
try
{
cmd.Prepare();
int id = (int)cmd.ExecuteScalar();
con.Close();
return(id);
}
catch (Exception ex)
{
return(-1);
}
}