SQL Server 2019 : Node.js から利用する2020/04/23

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
var async = require('async');

var config = {
  server: '127.0.0.1',
  authentication: {
      type: 'default',
      options: {
          userName: 'cent',
          password: 'password'
      }
  },
  options: {
      database: 'SampleDB',
      encrypt: false,
      enableArithAbort: true,
      trustServerCertificate: true
  }
}

var connection = new Connection(config);

function Start(callback) {
    callback(null);
}

// SampleTable を Select
function Read(callback) {
    console.log('Reading from SampleTable...');

    request = new Request(
    'select * from SampleTable;',
    function(err, rowCount, rows) {
    if (err) {
        callback(err);
    } else {
        console.log(' \n');
        callback(null, 'Ubuntu', 'Linux');
    }
    });

    var result = "";
    request.on('row', function(columns) {
        columns.forEach(function(column) {
            if (column.value === null) {
                console.log('NULL');
            } else {
                result += column.value + " ";
            }
        });
        console.log(result);
        result = "";
    });

    connection.execSql(request);
}

// SampleTable に Insert
function Insert(first_name, last_name, callback) {
    console.log("Inserting '" + first_name + "' into SampleTable...");

    request = new Request(
        'insert into SampleTable (First_Name, Last_Name) output inserted.ID values (@First_Name, @Last_Name);',
        function(err, rowCount, rows) {
        if (err) {
            callback(err);
        } else {
            console.log(rowCount + ' row(s) inserted\n');
            callback(null, 'Redhat', 'Maipo');
        }
        });
    request.addParameter('First_Name', TYPES.NVarChar, first_name);
    request.addParameter('Last_Name', TYPES.NVarChar, last_name);

    connection.execSql(request);
}

// 特定の行を Update
function Update(first_name, last_name, callback) {
    console.log("Updating Last_Name to '" + last_name + "' for '" + first_name + "'...");

    request = new Request(
    'update SampleTable set Last_Name=@Last_Name where First_Name = @First_Name;',
    function(err, rowCount, rows) {
        if (err) {
        callback(err);
        } else {
        console.log(rowCount + ' row(s) updated\n');
        callback(null, 'Ubuntu');
        }
    });
    request.addParameter('First_Name', TYPES.NVarChar, first_name);
    request.addParameter('Last_Name', TYPES.NVarChar, last_name);

    connection.execSql(request);
}

// 特定の行を Delete
function Delete(first_name, callback) {
    console.log("Deleting '" + first_name + "' from Table...");

    request = new Request(
        'delete from SampleTable where First_Name = @First_Name;',
        function(err, rowCount, rows) {
        if (err) {
            callback(err);
        } else {
            console.log(rowCount + ' row(s) deleted\n');
            callback(null);
        }
        });
    request.addParameter('First_Name', TYPES.NVarChar, first_name);

    connection.execSql(request);
}

function Complete(err, result) {
    if (err) {
        callback(err);
    } else {
        console.log("Done!");
    }
}

connection.on('connect', function(err) {
  if (err) {
    console.log(err);
  } else {
    async.waterfall([
        Start,
        Read,
        Insert,
        Update,
        Delete,
        Read
    ], Complete)
  }
});