Using mongoose style schemas MSSQL in Node.js

Question

I am currently writing an API with Express.js and have seen many examples about how to create schemas/models for mongodb, but was wondering if there is any similar solution for MSSQL. For example, I have the following method in a C# controller:

public void SubmitExpenses(List<Expense> expenses)
        {
            using (cnxn)
            {
                cnxn.Open();
                for (int i = 0; i < expenses.Count; i++)
                {
                    int employeeId = expenses.ElementAt(i).employeeId;
                    string expenseDate = expenses.ElementAt(i).expenseDate;
                    int taskId = expenses.ElementAt(i).taskId;
                    int expenseTypeId = expenses.ElementAt(i).expenseTypeId;
                    int billingCategory = expenses.ElementAt(i).billingCategory;
                    string notes = expenses.ElementAt(i).notes;
                    float amount = expenses.ElementAt(i).amount;
                    string lastUpdatedDate = expenses.ElementAt(i).LastUpdatedDate;
                    int lastUpdatedBy = expenses.ElementAt(i).LastUpdatedBy;
                    string dateSubmitted = expenses.ElementAt(i).dateSubmitted;


                    //public void SubmitExpenses(int employeeId, string expenseDate, int taskId, int expenseTypeId, int billingCategory,
                    //    string notes, float amount, string lastUpdatedDate, int lastUpdatedBy, string dateSubmitted)
                    //{

                    using (SqlCommand sqlQuery = new SqlCommand("INSERT INTO Expenses " +
                        "(Employee_ID, Task_ID, Expense_Date, Expense_Type_ID, Billing_Category_ID, " +
                        "Amount, Notes, Last_Updated_By, Last_Update_Datetime, Date_Submitted, Location) " +
                        "Values (@employeeId, @taskId, @expenseDate, @expenseTypeId, @billingCategory, @amount, @notes, " +
                        "@lastUpdatedBy, @lastUpdatedDate, @dateSubmitted, @locationId)", cnxn))
                    {
                        sqlQuery.Parameters.Add(new SqlParameter("@employeeId", SqlDbType.Int) { Value = employeeId });
                        sqlQuery.Parameters.Add(new SqlParameter("@expenseDate", SqlDbType.DateTime) { Value = expenseDate });
                        sqlQuery.Parameters.Add(new SqlParameter("@taskId", SqlDbType.Int) { Value = taskId });
                        sqlQuery.Parameters.Add(new SqlParameter("@expenseTypeId", SqlDbType.Int) { Value = expenseTypeId });
                        sqlQuery.Parameters.Add(new SqlParameter("@billingCategory", SqlDbType.Int) { Value = billingCategory });
                        sqlQuery.Parameters.Add(new SqlParameter("@notes", SqlDbType.Text) { Value = notes });
                        sqlQuery.Parameters.Add(new SqlParameter("@amount", SqlDbType.Money) { Value = amount });
                        sqlQuery.Parameters.Add(new SqlParameter("@lastUpdatedDate", SqlDbType.DateTime) { Value = lastUpdatedDate });
                        sqlQuery.Parameters.Add(new SqlParameter("@lastUpdatedBy", SqlDbType.Int) { Value = lastUpdatedBy });
                        sqlQuery.Parameters.Add(new SqlParameter("@dateSubmitted", SqlDbType.DateTime) { Value = dateSubmitted });
                        sqlQuery.Parameters.Add(new SqlParameter("@locationId", SqlDbType.VarChar) { Value = "" });

                        sqlQuery.ExecuteNonQuery();
                    }
                }
            }
        }

And the Expense.cs model:

public class Expense
    {
        public int employeeId { get; set; }
        public string expenseDate { get; set; }
        public int taskId { get; set; }
        public int expenseTypeId { get; set; }
        public int billingCategory { get; set; }
        public string notes { get; set; }
        public float amount { get; set; }
        public string LastUpdatedDate { get; set; }
        public int LastUpdatedBy { get; set; }
        public string dateSubmitted { get; set; }
        public string location { get; set; }
    }

How would I go about in similar fashion in Express.js so I could take a list of JavaScript objects, break them into elements which could then be parameterized and submitted?

Would it be something like...

app.post('/route/to/api', function (req, res) {
    var sql = require('mssql');

    sql.connect("mssql://user:password@localhost/Northwind").then(function () {
        console.log('connected');



       for (var i = 0; i < req.length; i++) {
            new sql.Request()
                .input('input_param', sql.VARCHAR, req[i]['PropertyName'])
                .query('INSERT INTO Table VALUES (@input_param)')
                .then(function (recordset) {
                    console.log(recordset);
                })
                .catch(function (err) {
                    console.log(err);
                });
        }
    }).catch(function (err) {
        console.log(err);
    });
});

Any advice would be much appreciated!

RedJandal · Accepted Answer · 2016-08-23 02:45:03Z

4

Have a look at sequelize. It should give you the functionality you require.

answered Aug 23, 2016 at 2:45

RedJandal

1,21310 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

WakaChewbacca Over a year ago

Do you know if I could cherry-pick the ability to define models from it and use it with mssql or is it recommended to just use sequelize independently for all things SQL?

RedJandal Over a year ago

Sequelize is an ORM like Entity Frameworks in .NET. It writes all the SQL for CRUD in the background for you. If you want to us mssql and you do not like the SQL output from sequelize you could always write your own repository model, but why go through all that effort when sequelize will do the job.

Collectives™ on Stack Overflow

Using mongoose style schemas MSSQL in Node.js

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related