Insert data into sql database in ASP.NET

Question

I am facing a problem getting the values from my form and passing it to the controller.

AddUser.cshtml

@model SecureMedi.Models.Users

<form asp-controller="Index" asp-action="AddUser" method="post">
    <div class="form-group">
        <label asp-for="Username">Username</label>
        <input asp-for="Username" class="form-control" />
    </div>
    <!-- / form-group -->
    <div class="form-group">
        <label asp-for="Role">Username</label>
        <input asp-for="Role" class="form-control" />
    </div>
    <!-- / form-group -->
    <button type="submit" class="btn btn-primary">Add User</button>
</form>
<!-- / form -->

UsersDAL.cs (Data access layer)

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using SecureMedi.Models;

namespace SecureMedi.DAL {
    public class UsersDAL {
        public void Insert(Users u) {
            string connectionstring = "MY_CONNECTION_STRING";
            SqlConnection conn = new SqlConnection(connectionstring);
            SqlCommand cmd = new SqlCommand(String.Format("CREATE USER {0} WITHOUT LOGIN", u.Username), conn);
            SqlCommand cmd2 = new SqlCommand(String.Format("ALTER ROLE {1} ADD MEMBER {0}", u.Username, u.Role), conn);

            try {
                conn.Open();

                using(conn) {
                    cmd.Transaction = conn.BeginTransaction();
                    cmd.ExecuteNonQuery();
                    cmd2.Transaction = cmd.Transaction;
                    cmd2.ExecuteNonQuery();
                    cmd2.Transaction.Commit();
                }
            } finally {
                if (conn != null) {
                    conn.Close();
                }
            }

        }
    }
}

Users.cs (Model)

namespace SecureMedi.Models {
    public class Users {
        public string Username {
            get;
            set;
        }
        public string Role {
            get;
            set;
        }
    }
}

HomeController.cs

using System;
using System.Collections.Generic;
using System.Diagnostics;
using Microsoft.AspNetCore.Mvc;
using nmvs_db.dal;
using nmvs_module;
using nmvs_module.util;
using SecureMedi.Models;
using SecureMedi.DAL;

namespace SecureMedi.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult AddUser(Users u)
        {
            UsersDAL ud = new UsersDAL();            
            ud.Insert(u);
            return View(u);
        }
    }
}

Here, I am facing two problems:

1) Whenever I navigate to /AddUser in my browser the AddUser method is called automatically. Instead, I would like to call AddUser method only when the form button is clicked.

2) Since AddUser is called (point 1) automatically the values I am retrieving from u.Username and u.Role is null.

For debugging purposes, if I modify my controller method like following:

public ActionResult AddUser(Users u) {
    if (u.Username == null)
        u.Username = "testuser";
    if (u.Role == null)
        u.Role = "SecureMediUsers";
    UsersDAL ud = new UsersDAL();
    ud.Insert(u);
    return View(u);
}

The only values which are passed in the DAL are the hardcoded values of Username and Role as shown above, where I want those values to be fetched from the form input values.

@StephenMuecke I have tried that but I don't see the page being loaded due to 404 HTTP request not found error. — 0xburned
– 0xburned, Commented Jan 17, 2018 at 11:02
You also need a public ActionResult AddUser() { return View(); } method for the GET call — user3559349
– user3559349, Commented Jan 17, 2018 at 11:05
Yeah, I need to make a GET call too before I post some action. By the way, how would I return to another route after the POST call is made using [HttpPost] AddUser() method? I also like you to post this as an answer so that I accept it. — 0xburned
– 0xburned, Commented Jan 17, 2018 at 11:15

score 3 · Accepted Answer · 2018-01-17 11:35:11Z

You need separate GET and POST methods. The GET will be

[HttpGet] // this attribute is optional since its the default
public ActionResult AddUser()
{
    var model = new Users();
    return View(model);
}

and method you currently have needs to be marked with the HttpPostAttribute. In addition, you should be checking if ModelState is invalid, and if so, return the view immediately so the user can correct validation errors, and if not, save and then redirect.

[HttpPost]
public ActionResult AddUser(Users u)
{
    if (!ModelState.IsValid)
    {
        return View(u);
    }
    UsersDAL ud = new UsersDAL();            
    ud.Insert(u);
    return RedirectToAction("Index", "Home"); // redirects to ../Home/Index
}

You should also consider adding validation attributes (e.g. a [Required] attribute assuming the values of the properties cannot be null), and add the validation message placeholders in your view for both client and server side validation (refer Introduction to model validation).

As as side note, your model describes as single User so the class should be public class User (not plural)

Saad Qais · Accepted Answer · 2018-01-17 11:06:34Z

0

change the asp-controller="Index" to asp-controller="Home"

answered Jan 17, 2018 at 11:06

Saad Qais

6227 silver badges8 bronze badges

1 Comment

0xburned Over a year ago

You are right in a way but that doesn't solve the asked problem.

ThatUser · Accepted Answer · 2018-01-17 12:42:41Z

I don't know if you're using temp code to get the controller working but dont forget to protect yourself from SQL injection, and rolling back of transactions incase of faliure.

SqlConnection conn = new SqlConnection(connectionstring);
SqlCommand cmd = new SqlCommand(String.Format("CREATE USER {0} WITHOUT LOGIN", u.Username), conn);
SqlCommand cmd2 = new SqlCommand(String.Format("ALTER ROLE {1} ADD MEMBER {0}", u.Username, u.Role), conn);

Example of loading an SqlCommand with SqlParameterCollection: (Not an ASP.NET project)

"nErrorMsg" is a paramater given to the encasing method:

try
  {
    nErrorMsg = nErrorMsg.Truncate(2000);

    using (SqlConnection sqlErrorLogConn = new SqlConnection(SqlConnString.ErrorLogConn))
    {
      try
      {
        sqlErrorLogConn.Open();
      }
      catch (Exception)
      {
        MessageBox.Show("There was an error whilst opening the connection with the database - (WriteToErrorLog)", "Connection Error", 0, MessageBoxIcon.Error);
        return;
      }

      using (SqlCommand sqlErrorLogCommand = sqlErrorLogConn.CreateCommand())
      {
        string sqlCommandText = string.Format("INSERT INTO [dbo].[Error_Log] (Description,LoggedAt,ComputerName) VALUES (@Description,@LoggedAt,@ComputerName);");

        sqlErrorLogCommand.CommandText = sqlCommandText;
        sqlErrorLogCommand.Prepare();

        sqlErrorLogCommand.Parameters.AddWithValue("@Description", nErrorMsg);
        sqlErrorLogCommand.Parameters.AddWithValue("@LoggedAt", DateTime.Now);
        sqlErrorLogCommand.Parameters.AddWithValue("@ComputerName", Environment.MachineName);

        sqlErrorLogCommand.ExecuteNonQuery();
      }
    }
  }
  catch (Exception ex)
  {
    MessageBox.Show("There was an error whilst communicating with the database: " + ex.ToString(), "Connection Error", 0, MessageBoxIcon.Error);
  }

Collectives™ on Stack Overflow

Insert data into sql database in ASP.NET

3 Answers 3

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Related