Orchard Widget for front end DB Access

Question

I'm new to Orchard development, C# and MVC, so forgive me if this is actually simple...

I used Orchard CMS in my profession so I understand the widgets, layer zones, content parts etc... however, I have tasked my self with improving one of the processes that is quite common, and I decided the best way would to be to create a module for this purpose.

The basic outline of the process using widgets would be as follows:

1. Customer navigates to a page and is presented with three sections, Submit and View

2. The "submit" section is a widget which is a form (most likely a custom form) which posts to a controller, and submits that data to a DB, I think I have this figured out.. but to make sure I am going to use something like the following to do this:

       [HttpPost]
   public ActionResult Index(string fName, string lName) {
       // var post values
       string fName = Request.Form["fName"];
       string lName = Request.Form["lName"];
   
       System.Data.SqlClient.SqlConnection sqlConnection1 =
           new System.Data.SqlClient.SqlConnection(@"[CONNECTION STRING]");
   
       System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
       cmd.CommandType = System.Data.CommandType.Text;
       cmd.CommandText = "INSERT INTO Persons (FirstName, LastName) VALUES ('" + fName + "','" + lName +"')";
       cmd.Connection = sqlConnection1;
   
       sqlConnection1.Open();
       cmd.ExecuteNonQuery();
       sqlConnection1.Close();
   
       return View();
   }
   

However, I am not sure this is the correct way to go about it, and I'm sure there is some better way I can do this, tutorials have recommended I use contentParts and contentPart records to submit data to the db however, this is only useful when submitting data from the admin side, this has to be able to be submitted from the front end by an end-user

3. The second section is a table which would get a list of records from the database and display them to the user, however, for this I have no idea how to go about this at all, could anyone point me to a tutorial, example source code, or even code snippets where this is achieved?

It is worth noting, I have gone through the documentation for creating modules and widgets on Orchard's site, however, they are all updating the DB via the backend...

• Odatia

Answer 1

Though your solution seems to work, I would implement it completely different.

First of all, you are connecting to the orchard db so you can just use Orchard's mechanisms to access the database. Assuming you created the table with a migration, you can access it with the IRepository interface.

Note that your port model must be in the /Models directory!:

/Models/Port.cs:

public class Port {
    public virtual int Id { get; set; }
    public virtual string MBN { get; set; }
    public virtual string Partner { get; set; }
}

Migrations.cs:

public int Create() {
    SchemaBuilder.CreateTable("Port",
        table => table
            .Column<int>("Id", column => column.PrimaryKey().Identity())
            .Column<string>("MDN", column => column.NotNull().WithDefault(""))
            .Column<string>("Partner", column => column.NotNull().WithDefault(""))
        );
    return 1;
}

/Controllers/PortingController.cs:

[Themed]
public class PortingController : Controller

    private readonly IRepository<Port> _repository;

    public PortingController(IRepository<Port> repository) {
        _repository = repository;
    }

    [HttpGet]
    public ActionResult Index() {
        // query the table
        var ports = _repository.Table.ToList();
        return View(ports);
    }

    [HttpPost]
    public ActionResult AddRequest(port item) {
        if (ModelState.IsValid) {
            _repository.Create(item);
        }

        return RedirectToAction("Index");
    }

}

And then in your Views/Porting/Index.cshtml:

@using Porting.Models
@model IEnumerable<Port>

@* display the models.. *@

@* display a form for creating a new one *@

@using (Html.BeginFormAntiForgeryPost(Url.Action("CreateRequest", "Port", new { area = "YourAreaName" }))) {
    // the form
}

For reference see this post

Answer 2

For anybody stumbling across this post looking for help, I did the two listed things in the following way:

Controller:

 [Themed]
    public class PortingController : Controller
    {
        // GET Porting/Index
        public ActionResult Index()
        {
            List<port> ports = new List<port>();
            string constr = "Data Source=127.0.0.1;Port=3307;Database=orchard;User Id=root;Password=usbw ";
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                string query = "SELECT * FROM icc_porting_icc_activesoftswitch_ports";
                using (MySqlCommand cmd = new MySqlCommand(query)) 
                {
                    cmd.Connection = con;
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader()) {
                        while (sdr.Read()) 
                        {
                            ports.Add(new port {
                                Id = Convert.ToInt32(sdr["Id"]),
                                MBN = sdr["MBN"].ToString(),
                                Partner = sdr["Partner"].ToString()
                            });
                        }
                    }
                }
                con.Close();
            }
            return View(ports);
        }

        // POST AddRequest
        [HttpPost]
        public ActionResult AddRequest(FormCollection forms)
        {
            // init vars
            string mbn = forms["PortingRequestForm.mbn.Value"];
            string partner = forms["PortingRequestForm.Partner.Value"];

            // db con string
            string connString = "Data Source=127.0.0.1;Port=3307;Database=orchard;User Id=root;Password=usbw ";
            MySqlConnection conn = new MySqlConnection(connString);
            conn.Open();
            MySqlCommand comm = conn.CreateCommand();
            comm.CommandText = "INSERT INTO icc_porting_icc_activesoftswitch_ports(mbn, partner) VALUES(?mbn, ?partner)";
            comm.Parameters.AddWithValue("?mbn", mbn);
            comm.Parameters.AddWithValue("?partner", partner);
            comm.ExecuteNonQuery();
            conn.Close();

            //string endContent = mbn + " " + partner;
            return RedirectToAction("Index");
        }
    }
}

And then the model is just simply declaring some basic properties

To add to the DB I created a custom form and used jQuery to change the forms action so instead of using orchard's custom form controller it posted to the following: ~/{moduleName}/{Controller}/{Action}

Then just use standard razor mark up to get variables from the index controller example:

@using Porting.Models
@model IEnumerable<port>
    <table class="demo">
        <thead>
            <tr>
                <th>ID</th>
                <th>MBN</th>
                <th>Status</th>
            </tr>
        </thead>
        <tbody>
            @foreach (port Port in Model) {
                <tr>
                    <td>@Port.Id</td>
                    <td>@Port.MBN</td>
                    <td>@Port.Partner</td>
                </tr>
            }
        <tbody>
</table>

It isn't the best and I know there are probably a million ways I can improve this so if anyone wouldn't mind helping please send me a private message or even just comment here?

Thanks

• Odatia