calling sp in controller

montoyam

This not so much a datatables question other than it is in a asp.net mvc project that has datatables in it. I am having the user import a text file into a datatable: https://editor.datatables.net/examples/extensions/import

After it is imported and they have reviewed the data, I need the user to click a button to call a stored procedure. The stored procedure is parsing the data and putting into another table, which is the datasource for another datatable. But the stored procedure itself does not return any data. From what I am researching, since datatables uses MVC i need to put that call in a controller.

    public class ParseImportDataController: ApiController
    {
        [HttpGet]
        [HttpPost]
        public IHttpActionResult cleanAndImport()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;
            string AsOfCookie = request.Cookies.Get("AsOfDate").Value;

            string strCon = settings.DbConnection;
            SqlConnection DbConnection = new SqlConnection(strCon);
            DbConnection.Open();

            SqlCommand command = new SqlCommand("sp_ImportFTE", DbConnection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@EffectiveDate", AsOfCookie));
            command.ExecuteNonQuery();
            DbConnection.Close();
            return Ok(1); //no idea what to return
        }
    }

I can't find out how I have the button click call this code in the controller. Any help would be greatly appreciated.

I have no idea if the code in the controller is correct, but I figure that will be the next struggle.

montoyam

Can I use Buttons to add a custom button that will call the cleanAndImport function in the controller?

montoyam

I just realized that I had this as a new discussion, not a question. I have re-posted it as a question.

allan

Can I use Buttons to add a custom button that will call the cleanAndImport function in the controller?

You can - but I'm not certain that is the way forward here. How are you going to give the data to this controller that it needs to import? Are you going to upload it directly to this controller, or has it already been uploaded somewhere and this just needs to be called to process it?

Could you list the interactions the user will be taking so I can understand the aim a little bit more?

Thanks,
Allan

montoyam

yes, step one is done. They upload a csv file into a 'raw data' SQL table. I used this code to accomplish that: https://editor.datatables.net/examples/extensions/import.

After they make sure the data came in fine, I want them to push a button that will run a stored procedure. That stored procedure will take the data from the SQL table in step one and append it to another SQL table. I just can't figure out how to run the stored procedure. I put the call (code in my original post) into a controller, not sure if that is where it belongs. if it can go there, how do i run it? I see how you can create a custom button that has a function. But what is the line of code I need in the function that says "go run cleanAndImport()"?

allan

Okay - perfect - thanks for the clarifications. In that case, a custom button is the way to go, and you'd have it call an end point on the server (via Ajax) that will do your cleanAndImport() function - e.g. you might use this for the button:

action: () => {
  $.ajax({
    url: '/api/cleanAndImport'
  });
}

you might want success handlers, and to send data as well, that really depends upon your requirements, but that's the key part from the client-side. The other part you need is the controller / end point on the server, which will be implemented in whatever server-side environment you are using.

Allan

montoyam

that was it!!! awesome. From my controller code posted above I had to add:

[Route("api/cleanAndImport")]

then your code worked perfectly.