Rohit Dhand

Sometimes we have to use a parameter that will form the basis of the condition in the Query. For e.g. if the parameter has a value, then we want to use it in the condition, otherwise we want to retrieve all the records. This can be achieved by: declare @vEname nvarchar(60) set @vEname = null Select * from Emp where Ename = isnull(@vEname,Ename) In the above script, if @vEname has some value, then the query will run based on that condition, otherwise it will run on the condition of "Ename = Ename" column, which will always return true and hence will return all the records. Happy Coding.!

In case we have created a table and forget to include a foreign key, we can do it by creating a new migration and then running it. The following code shows how to do it: knex.schema.table('employees', function (table) { table.bigInteger('deptno').references('id').inTable('Depts') }); The above code adds a column deptno as a foreign key in the table named "Employees", that refers a column "ID" from the Dept table. Happy Coding!!!.

Migrations are the best way of creating and maintaining database stuff. Through Knex we can easily create migrations and then finally run them against the database. After installing the "knex" package, we can use the following command: knex migrate:make setup The above command helps to create directory (migrations) and place a migration file in that folder. Now edit the contents of the file to include the following stuff: exports.up = function(knex, Promise) {     return Promise.all([     knex.schema.createTable('employees', function(table){       table.string('empname');       table.string('job');       table.timestamps();     })   ]) }; exports.down = function(knex, Promise) {     return Promise.all([     knex.schema.dropTable('employees');   ]) }; The above code has got two important concepts: a. UP b. DOWN UP is a function used when the migration is run against the Database and DOWN is the function when we wou

In case we want to update a record within a table through KNEX, we can use the following code: updateRecord(); function updateRecord(){   knex('depts')   .where("deptno","40")   .update({     dname: 'TEST DEPARTMENT'   })   .then(count=>{     console.log("record updated"+count);   }); } The above code calls a function updateRecord that updates the deptno = 40's department name to 'TEST DEPARTMENT'. Also in the Promise '.then', it prints out number of records that were updated. Happy Coding !!!

Select is the only command available in the whole SQL to retrieve data. KNEX provides an implementation of the "Select" Query. The following code illustrates: knex.select("*").from("depts") .then(function (dept) {     dept.forEach(function(value){       console.log(value.deptno);     });   }).catch(function(err) {     // All the error can be checked in this piece of code     console.log(err);   }).finally(function() {     // To close the connection pool     knex.destroy();   }); } The above code has following important observations: a. Its Selecting data from a table named "Depts". We are selecting all the columns and rows. b. Once select query has fetched rows, it will run a promise of .then c. "dept" in the .then function is a collection of all records fetched from the table. d. We can access individual columns like "value.deptno", "value.dname" within forEach loop. Happy Coding !!!

This post is all about of deleting records through KNEX ORM in Nodejs. The following code shows how to achieve this: knex("depts").where("deptno","50").del() .then(function (count) {     console.log(count); }) .finally(function () {     knex.destroy(); }); The above code deletes a record from a table "Depts" where Deptno = 50. It has a "then" Promise attached that will show how many records were deleted with this command. Also "finally" is the method that will always execute and will close the KNEX connection. Happy Coding !!!

Knex can be used to insert data into tables. The following code shows how we can do that. var knex = require ( 'knex' )({ client: 'mysql' , connection: { host : '127.0.0.1' , user : 'root' , password : '' , database : 'lovely' } }); knex ( 'depts' ). insert ({ name: 'Technology' }) . returning ( 'id' ) . then (( id ) => { console.log(id); }); In the above code, we used a node package knex to connect Nodejs to mysql. And then we are finally inserting a new record in the table named "Depts". The value of the new dname inserted is  'Technology'. Once its inserted, it will be returning an automatic incremented ID for that dept. This Id

KNEX is a wonderful ORM library that can be used with Javascript . Recently I had a chance to use it in one of my projects. Thought of sharing with all others. In order to use Knex we need to have node package installed 'knex'. Once the package is installed we can use the following code to connect to mysql through Node with Knex. var knex = require ( 'knex' )({ client: 'mysql' , connection: { host : '127.0.0.1' , user : 'root' , password : '' , database : 'test' } }); This is a very simple way to connect our node program to mysql through Knex.

In case we have a date value and we want to generate the full name of the month for that date, we can use the following code: DateTime vDt = DateTime.Now; String vMonthName = vDt.ToString("MMMM"); Happy Coding!!!.

If in a C# Program we want to generate FirstDate and LastDate of a month, and we already have month and year, the following code can be used: int vMonth = 12; DateTime vFirstDateOfMonth = new DateTime(2017, vMonth, 1); DateTime vLastDateOfMonth = vFirstDateOfMonth.AddMonths(1).AddDays(-1); Happy Coding!!!

Students who are interested to learn Nodejs in the online mode can contact me for more details. Course Prerequisite: Basic Programming Skills.

In order to make a call from our application in Nodejs, we can use TwilioAPI for doing so. We need to create an account on Twilio and specify details. Once we have done that we can use the following code: // Twilio Credentials var accountSid = 'ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ; var authToken = "your_auth_token" ; var client = require ( 'twilio' )( accountSid , authToken );   client . calls . create ({     url : "http://demo.twilio.com/docs/voice.xml" ,     to : "+1XXXXXXXXXX" ,     from : "+1XXXXXXXXX" }, function ( err , call ) {     process . stdout . write ( call . sid ); }); We need to specify  to  and  from numbers. To  is the number that is verified under Twilio account and  from  is the number that Twilio generates for you at the account setup. Also url is the property that we need to setup so as to pick up the voice information.

In order to send an SMS from our application in Nodejs, we can use TwilioAPI for doing so. We need to create an account on Twilio and specify details. Once we have done that we can use the following code to send an SMS. // Twilio Credentials var accountSid = 'ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ; var authToken = 'your_auth_token' ; //require the Twilio module and create a REST client var client = require( 'twilio' )(accountSid, authToken); client.messages.create({     to : "+15XXXXXXXX" ,     from : "+15XXXXXXX0" ,     body : "Test Message from Twilio" , }, function (err, message) {     console.log(message.sid); }); We need to specify to and from numbers. To is the number that is verified under Twilio account and from is the number that Twilio generates for you at the account setup.

Promises are a method by which we can handle Asynchronous Requests in a better way. Normally a Promise has three output conditions: a. RESOLVED b. PENDING c. REJECT Just imaging a Promise that your father made, for buying a good phone. It would have three conditions: a. RESOLVED - Your father got you a new phone. b. REJECTED - You didn't got a new phone. c. PENDING - The promise is neither rejected or fulfilled, still you are waiting for the phone function checkValue ( x ){ if ( x > 100 ){ return Promise . resolve ( x ); } else { return Promise . reject ( "does not satisfy the condition" ); } } var result = checkValue ( 20 ); console . log ( "this is executing" ); console . log ( result ); //This returns an Object of Promise which we can use. var t = function (){ //The object of Promise can be used to use Then or use Catch for the Error. result . then ( function ( fulfi

How to use  SQL Extension in Visual Studio Code for connecting to SQL Server and performing operations on that. In case you are using Visual Studio Code for development and using SQL Server at the backend, we can integrate a SQL Extension named "mssql". This extension will help in connecting, running queries, use intelli-sense and even display the output in the Visual Studio Code for the Select Queries. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-develop-use-vscode A screenshots for the query that I ran and tested. Happy Coding!

Microsoft launched Visual Studio 2017 today and is celebrating 20 years of this product. Unfortunately, the ISO images are no longer available from Microsoft. The install happens to be online. But in case you want it as an offline install, follow the link: https://docs.microsoft.com/en-us/visualstudio/install/create-an-offline-installation-of-visual-studio

In case we are installing Visual Studio Color Theme Editor on Visual Studio 2013 and its giving an error of "Required .NET Framework" not installed, then we can resolve it by going through the following link: http://joehacker.blogspot.ca/2016/03/cant-install-visual-studio-2013-color.html