How to update table in ASP.NET MVC 5 Razor Database

asp.net asp.net-mvc-5 c# entity-framework-6 razor

Question

I'm new to ASP.NET MVC 5 Razor and I need some help how can I upload rows in my database.

I also use EF6 existing database for my model.

This is what I'm trying to do:

@if (Request.QueryString["GetUser"] != null && Request.QueryString["Update"] == "Open")
                            {
                                ViewBag.Notification = "Do you want to sync " + @Request.QueryString["GetUser"].ToString() + " to ActiveDirectory2?";
                            }
                            @if (Request.QueryString["GetUser"] != null && Request.QueryString["Update"] == "Update")
                            {
                                using(GWActiveDirectory.Models.GW_UTAEntities db = new GWActiveDirectory.Models.GW_UTAEntities())
                                {
                                    var query = "UPDATE ActiveDirectory2"
                                              + " SET SamAccountName = '" + DataStorage.Rows[0][0].ToString() 
                                              + "', EmployeeNumber = '" + DataStorage.Rows[0][9].ToString()
                                              + "', GivenName = '" + DataStorage.Rows[0][4].ToString() 
                                              + "', Surname = '" + DataStorage.Rows[0][1].ToString() 
                                              + "', EmailAddress = '" + DataStorage.Rows[0][5].ToString() 
                                              + "', Enable = '" + DataStorage.Rows[0][3].ToString() 
                                              + "', Guid = '" + DataStorage.Rows[0][2].ToString()
                                              + "', SID = '" + DataStorage.Rows[0][6].ToString() 
                                              + "', DateCreated = '" + DataStorage.Rows[0][7].ToString() 
                                              + "', DateModified = '" + DataStorage.Rows[0][8] + "'"
                                              + " WHERE SamAccountName = @SamAccountName";
                                    db.ActiveDirectory2.SqlQuery(query,Request.QueryString["GetUser"])
                                    db.SaveChangesAsync();
                                    Response.Redirect("?SearchString=" + ViewBag.searchString + "&GetUser=" + Request.QueryString["GetUser"]);
                                }  

                            }

But the problem is when I try to run my program the table ActiveDirectory2 didn't update.

1
0
9/27/2014 5:15:26 AM

Accepted Answer

SqlQuery is for querying. For updates you need to use ExecuteCommand. However you seem to want to use EF in which case you don't stitch SQL queries manually but you fetch the entity from the database you want to update, you set the properties and you call SaveChanges. Also note that in your code snippet the SaveChanges call does not make sense because it is a no-op. Because you bypass EF when updating the database the context does not track any entities so SaveChanges does nothing.

Finally the way you create your command is susceptible to a SQL injection attack. If you use EF the right way EF will build the command securely.

3
9/27/2014 5:45:05 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow