public List<Schema> GetUserSchemaForm(string logonName)
{
List<Schema> schemas = new List<Schema>();
SqlConnection conn = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = " + System.Web.HttpContext.Current.Server.MapPath(privilegesDatabase));
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.[Schema] WHERE logonName=@logonNameParam", conn);
cmd.Parameters.Add("@logonNameParam", SqlDbType.VarChar, 35);
cmd.Parameters["@logonNameParam"].Value = String.IsNullOrEmpty(logonName) ? "UNKNOWN" : logonName;
var reader = cmd.ExecuteReader();
List<string> columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
columns.Sort();
// read each record
while (reader.Read()) {
Schema theSchema = new Schema();
foreach (string column in columns) {
Type myType = typeof(Schema);
PropertyInfo myPropInfo = myType.GetProperty(column);
if (column == "approved") {
DateTime dtResult;
DateTime.TryParse(reader[column] != null ? reader[column].ToString() : String.Empty, out dtResult);
if(dtResult != null && dtResult != DateTime.MinValue) myPropInfo.SetValue(theSchema, dtResult, null);
} else if (column.ToLower() == "logonname") {
myPropInfo.SetValue(theSchema, reader[column] != null && !String.IsNullOrEmpty(reader[column].ToString()) ? reader[column].ToString() : String.Empty, null);
} else {
int intResult;
Int32.TryParse(reader[column] != null ? reader[column].ToString() : "0", out intResult);
myPropInfo.SetValue(theSchema, intResult, null);
}
}
schemas.Add(theSchema);
}
cmd.Dispose();
reader.Close();
conn.Dispose();
return schemas;
}
public void SubmitForm(object sender, CommandEventArgs e)
{
if (e.CommandName == btnSubmitForm.CommandName) {
Schema userPrestoredSchema = FormMod.GetUserSchemaForm(CurrentUser).FirstOrDefault();
Schema userSchema = new Schema();
userSchema.logonName = CurrentUser;
userSchema.schemaID = userPrestoredSchema != null ? userPrestoredSchema.schemaID ?? -1 : -1;
userSchema.spm1 = radios1a.Checked ? 1 : 0;
userSchema.spm2 = radios2a.Checked ? 1 : 0;
userSchema.spm3 = radios3a.Checked ? 1 : 0;
userSchema.spm4 = radios4a.Checked ? 1 : 0;
userSchema.spm5 = radios5a.Checked ? 1 : 0;
userSchema.spm6 = radios6a.Checked ? 1 : 0;
userSchema.spm7 = radios7a.Checked ? 1 : 0;
userSchema.spm8 = radios8a.Checked ? 1 : 0;
userSchema.spm9 = radios9a.Checked ? 1 : 0;
userSchema.spm10 = radios10a.Checked ? 1 : 0;
userSchema.spm11 = radios11a.Checked ? 1 : 0;
userSchema.spm12 = radios12a.Checked ? 1 : 0;
userSchema.spm13 = radios13a.Checked ? 1 : 0;
userSchema.spm14 = radios14a.Checked ? 1 : 0;
userSchema.spm15 = radios15a.Checked ? 1 : 0;
userSchema.spm16 = radios16a.Checked ? 1 : 0;
userSchema.spm17 = radios17a.Checked ? 1 : 0;
userSchema.spm18 = radios18a.Checked ? 1 : 0;
userSchema.spm19 = radios19a.Checked ? 1 : 0;
userSchema.spm20 = radios20a.Checked ? 1 : 0;
userSchema.spm21 = radios21a.Checked ? 1 : 0;
userSchema.spm22 = radios22a.Checked ? 1 : 0;
userSchema.spm23 = radios23a.Checked ? 1 : 0;
userSchema.spm24 = radios24a.Checked ? 1 : 0;
userSchema.spm25 = radios25a.Checked ? 1 : 0;
userSchema.spm26 = radios26a.Checked ? 1 : 0;
userSchema.spm27 = radios27a.Checked ? 1 : 0;
userSchema.spm28 = radios28a.Checked ? 1 : 0;
userSchema.spm29 = radios29a.Checked ? 1 : 0;
userSchema.spm30 = radios30a.Checked ? 1 : 0;
userSchema.spm31 = radios31a.Checked ? 1 : 0;
userSchema.spm32 = radios32a.Checked ? 1 : 0;
userSchema.spm33 = radios33a.Checked ? 1 : 0;
userSchema.spm34 = radios34a.Checked ? 1 : 0;
userSchema.spm35 = radios35a.Checked ? 1 : 0;
userSchema.spm36 = radios36a.Checked ? 1 : 0;
userSchema.spm37 = radios37a.Checked ? 1 : 0;
userSchema.spm38 = radios38a.Checked ? 1 : 0;
userSchema.spm39 = radios39a.Checked ? 1 : 0;
userSchema.spm40 = radios40a.Checked ? 1 : 0;
userSchema.spm41 = radios41a.Checked ? 1 : 0;
userSchema.spm42 = radios42a.Checked ? 1 : 0;
userSchema.spm43 = radios43a.Checked ? 1 : 0;
userSchema.spm44 = radios44a.Checked ? 1 : 0;
userSchema.spm45 = radios45a.Checked ? 1 : 0;
userSchema.spm46 = radios46a.Checked ? 1 : 0;
userSchema.spm47 = radios47a.Checked ? 1 : 0;
userSchema.spm48 = radios48a.Checked ? 1 : 0;
userSchema.spm49 = radios49a.Checked ? 1 : 0;
userSchema.spm50 = radios50a.Checked ? 1 : 0;
userSchema.spm51 = radios51a.Checked ? 1 : 0;
userSchema.spm52 = radios52a.Checked ? 1 : 0;
userSchema.spm53 = radios53a.Checked ? 1 : 0;
userSchema.spm54 = radios54a.Checked ? 1 : 0;
userSchema.spm55 = radios55a.Checked ? 1 : 0;
userSchema.spm56 = radios56a.Checked ? 1 : 0;
userSchema.spm57 = radios57a.Checked ? 1 : 0;
userSchema.spm58 = radios58a.Checked ? 1 : 0;
userSchema.spm59 = radios59a.Checked ? 1 : 0;
userSchema.spm60 = radios60a.Checked ? 1 : 0;
bool runStatus = FormMod.PutUserSchemaForm(userSchema);
string __activeFocus = String.Empty;
responsebox.InnerText = JsonConvert.SerializeObject(new ReplyObject(runStatus, __activeFocus, runStatus ?
"Egenerklæring ble sendt inn og registrert." :
"Noe feil skjedde, prøv igjen!"));
}
}
public bool PutUserSchemaForm(Schema userSchema)
{
Schema existingForm = this.GetUserSchemaForm(userSchema.logonName).Where(schema => schema.schemaID == userSchema.schemaID).FirstOrDefault();
SqlConnection conn = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = " + System.Web.HttpContext.Current.Server.MapPath(privilegesDatabase));
conn.Open();
int rowsUpdated = 0;
if (existingForm != null) {
SqlCommand cmd = new SqlCommand("UPDATE dbo.[Schema] SET logonName=@logonName, spm1=@spm1, spm2=@spm2, spm3=@spm3, spm4=@spm4, spm5=@spm5, spm6=@spm6, spm7=@spm7, spm8=@spm8, spm9=@spm9, spm10=@spm10, spm11=@spm11, spm12=@spm12, spm13=@spm13, spm14=@spm14, spm15=@spm15, spm16=@spm16, spm17=@spm17, spm18=@spm18, spm19=@spm19, spm20=@spm20, spm21=@spm21, spm22=@spm22, spm23=@spm23, spm24=@spm24, spm25=@spm25, spm26=@spm26, spm27=@spm27, spm28=@spm28, spm29=@spm29, spm30=@spm30, spm31=@spm31, spm32=@spm32, spm33=@spm33, spm34=@spm34, spm35=@spm35, spm36=@spm36, spm37=@spm37, spm38=@spm38, spm39=@spm39, spm40=@spm40, spm41=@spm41, spm42=@spm42, spm43=@spm43, spm44=@spm44, spm45=@spm45, spm46=@spm46, spm47=@spm47, spm48=@spm48, spm49=@spm49, spm50=@spm50, spm51=@spm51, spm52=@spm52, spm53=@spm53, spm54=@spm54, spm55=@spm55, spm56=@spm56, spm57=@spm57, spm58=@spm58, spm59=@spm59, spm60=@spm60 WHERE schemaID=@schemaID", conn);
foreach (PropertyInfo property in typeof(Schema).GetProperties()) {
if (userSchema.GetType().GetProperty(property.Name).GetValue(userSchema, null).GetType() == "".GetType()) {
cmd.Parameters.Add("@" + property.Name, SqlDbType.VarChar, 35);
}
else if (userSchema.GetType().GetProperty(property.Name).GetValue(userSchema, null).GetType() == DateTime.Now.GetType()) {
cmd.Parameters.Add("@" + property.Name, SqlDbType.DateTime);
}
else {
cmd.Parameters.Add("@" + property.Name, SqlDbType.Int);
}
if (property.Name == "approved") {
DateTime dtTime = ((DateTime)userSchema.GetType().GetProperty(property.Name).GetValue(userSchema, null));
if (dtTime == null || dtTime == DateTime.MinValue) cmd.Parameters["@" + property.Name].Value = DBNull.Value;
else cmd.Parameters["@" + property.Name].Value = dtTime.ToString("MM/dd/yyyy");
}
else {
cmd.Parameters["@" + property.Name].Value = userSchema.GetType().GetProperty(property.Name).GetValue(userSchema, null);
}
}
rowsUpdated = cmd.ExecuteNonQuery();
cmd.Dispose();
} else {
SqlCommand cmd = new SqlCommand("INSERT INTO dbo.[Schema] (logonName,spm1,spm2,spm3,spm4,spm5,spm6,spm7,spm8,spm9,spm10,spm11,spm12,spm13,spm14,spm15,spm16,spm17,spm18,spm19,spm20,spm21,spm22,spm23,spm24,spm25,spm26,spm27,spm28,spm29,spm30,spm31,spm32,spm33,spm34,spm35,spm36,spm37,spm38,spm39,spm40,spm41,spm42,spm43,spm44,spm45,spm46,spm47,spm48,spm49,spm50,spm51,spm52,spm53,spm54,spm55,spm56,spm57,spm58,spm59,spm60) values(@logonName,@spm1,@spm2,@spm3,@spm4,@spm5,@spm6,@spm7,@spm8,@spm9,@spm10,@spm11,@spm12,@spm13,@spm14,@spm15,@spm16,@spm17,@spm18,@spm19,@spm20,@spm21,@spm22,@spm23,@spm24,@spm25,@spm26,@spm27,@spm28,@spm29,@spm30,@spm31,@spm32,@spm33,@spm34,@spm35,@spm36,@spm37,@spm38,@spm39,@spm40,@spm41,@spm42,@spm43,@spm44,@spm45,@spm46,@spm47,@spm48,@spm49,@spm50,@spm51,@spm52,@spm53,@spm54,@spm55,@spm56,@spm57,@spm58,@spm59,@spm60)", conn);
foreach (PropertyInfo property in typeof(Schema).GetProperties()) {
if(property.Name != "schemaID") {
if (userSchema.GetType().GetProperty(property.Name).GetValue(userSchema, null).GetType() == "".GetType()) {
cmd.Parameters.Add("@" + property.Name, SqlDbType.VarChar, 35);
}
else if (userSchema.GetType().GetProperty(property.Name).GetValue(userSchema, null).GetType() == DateTime.Now.GetType()) {
cmd.Parameters.Add("@" + property.Name, SqlDbType.DateTime);
}
else {
cmd.Parameters.Add("@" + property.Name, SqlDbType.Int);
}
if (property.Name == "approved") {
DateTime dtTime = ((DateTime)userSchema.GetType().GetProperty(property.Name).GetValue(userSchema, null));
if (dtTime == null || dtTime == DateTime.MinValue) cmd.Parameters["@" + property.Name].Value = DBNull.Value;
else cmd.Parameters["@" + property.Name].Value = dtTime.ToString("MM/dd/yyyy");
}
else {
cmd.Parameters["@" + property.Name].Value = userSchema.GetType().GetProperty(property.Name).GetValue(userSchema, null);
}
}
}
string query = cmd.CommandText;
foreach (SqlParameter p in cmd.Parameters) {
query = query.Replace(p.ParameterName, p.Value.ToString());
}
rowsUpdated = cmd.ExecuteNonQuery();
cmd.Dispose();
}
conn.Dispose();
return rowsUpdated != 0;
}