data-access

database data access now just support oracle & sqlserver，other dbbase is in developing... 数据库访问集成，现在只支持oracle & sqlserver，其他数据库（可自行扩展）持续开发中...

typescript study for myself.

use by typescript eg.

import { IDbOptions, DbType,dbFactory, DataTypes } from "../db-access";

let gcoa: IDbOptions = { dbType: DbType.oracle, dbConfig: { user: "gzgc", password: "***", connectString: "10.10.20.25:1521/mx25", externalAuth: false } };

let yf_gcgs: IDbOptions = { dbType: DbType.mssql, dbConfig: { user: "sa", password: '***', server: '10.10.20.15', database: 'GCGS', options: { encrypt: true } } };

async function ora_query_test() { console.log('ora_test');

let db = dbFactory(gcoa);
let db_gcgs = dbFactory(yf_gcgs);

let sql = `select mv001,mv002 from cmsmv 
    where mv001=:mv001`;
let cmd = await db.sql(sql);
cmd.parameter({
    name: 'mv001',
    value: '000057'
});
let data = await cmd.queryMany();

let r;
await db.useTransaction(true);
try {
    sql = `update cmsmv set mv002='王绍伟1' where mv001='000057'`;
    cmd = await db.sql(sql);
    r = await cmd.execute();
    await db.commit();
}
catch (err) {
    await db.rollback();
    throw (err);
}
finally {
    try {
        await db.useTransaction(false);
    } catch (err) {
        console.error(err);
    }
}

sql = `select f_bianhao,f_mingcheng from gimquyuhed where f_youxiaoma='Y'`;
cmd = await db.sql(sql);
let data1 = await cmd.queryMany();


sql = `select MV001,MV002 from CMSMV`;
cmd = await db_gcgs.sql(sql);
let data2 = await cmd.queryMany();

console.log(data);
console.log(r);
console.log(data1);
console.log(data2);

} async function ora_insert_test() { console.log('ora_insert_test'); let db = dbFactory(gcoa); let result; await db.useTransaction(true); try { // let insert = await db.insert('itfwsqdhed'); // insert.column('ID', 123456789) // .column('F004', '000057') // .column('F005', '10199') // .column('F006', 'nodejs测试插入') // .column('CREATEDBY', '000057') // .column('LASTMODIFIEDBY', '000057') // .column('CREATORBM', 45); // result = await insert.execute();

    // console.dir(result);

    let insert2 = await db.insert('itfwsqdhed');
    insert2.column('ID', 223456789)
        .column('F002', '2018/12/24 16:19:00', DataTypes.DateTime)
        .column('F004', '000057')
        .column('F005', '10199')
        .column('F006', 'nodejs测试插入1231231')
        .column('CREATEDBY', '000057')
        .column('LASTMODIFIEDBY', '000057')
        .column('CREATORBM', 45);
    let id = await insert2.executeReturnLastId<number>('ID');

    console.dir(id);

    await db.commit();
}
catch (err) {
    await db.rollback();
    throw (err);
}
finally {
    try {
        await db.useTransaction(false);
    } catch (err) {
        console.error(err);
    }
}

} async function ora_update_test() { console.log('ora_update_test'); let db = dbFactory(gcoa); await db.useTransaction(true); try { let update = await db.update('itfwsqdhed'); update.where('ID', 223456789) .column('F002', new Date(), DataTypes.DateTime) .column('F006', 'nodejs测试update'); let r = await update.execute(); console.dir(r);

    await db.commit();
}
catch (err) {
    await db.rollback();
    throw (err);
}
finally {
    await db.useTransaction(false);
}

} async function ora_del_test() { console.log('ora_del_test'); let db = dbFactory(gcoa); await db.useTransaction(true); try { let del = await db.delete('itfwsqdhed'); //del.where('ID', 223456789); del.where('ID', 1417); let r = await del.execute(); console.dir(r);

    await db.commit();
}
catch (err) {
    await db.rollback();
    throw (err);
}
finally {
    await db.useTransaction(false);
}

} async function ora_storedProcedure_test() { console.log('ora_storedProcedure_test'); let db = dbFactory(gcoa);

//注：node-oracledb 执行存储过程使用的是PL/SQL方式，需要事务提交才生效
await db.useTransaction(true);
try {
    let proc = await db.storedProcedure('proc_nodejs_test');
    proc.parameter('en', 'wangshw')
        .parameter('cn', '王绍伟')
        .parameterOut('fullname');
    let r = await proc.execute();
    console.dir(r);

    await db.commit();
}
catch (err) {
    await db.rollback();
    throw (err);
}
finally {
    await db.useTransaction(false);
}

}

async function mssql_query_test() { console.log('mssql_test'); let db = dbFactory(yf_gcgs); let sql = select MV001,MV002 from CMSMV where MV001=@mv001; let cmd = await db.sql(sql); cmd.parameter({ name: 'mv001', value: 'WANGSW' }); let data = await cmd.queryMany(); console.log(data); } async function mssql_insert_test() { console.log('mssql_insert_test'); let db = dbFactory(yf_gcgs); let result; await db.useTransaction(true); try { let insert = await db.insert('INVMB_MT'); insert.column('MB001', '9999999999') .column('MB002', 'nodejs-mssql测试') .column('MB003', '测试数据') .column('MB109', 'N') .column('CREATEDBY', '000057') .column('LASTMODIFIEDBY', '000057') .column('CREATORBM', 45); result = await insert.execute(); console.dir(result);

    let insert2 = await db.insert('INVMB_MT');
    insert2.column('MB001', '88888888')
        .column('MB002', 'nodejs-mssql测试')
        .column('MB003', '测试数据')
        .column('MB004', '12')
        .column('MB109', 'N')
        .column('CREATEDBY', '000057')
        .column('LASTMODIFIEDBY', '000057')
        .column('LASTMODIFIEDDATE', '', DataTypes.DateTime)
        .column('CREATORBM', 45);;
    let id = await insert2.executeReturnLastId<number>('ID');
    console.dir(id);

    await db.commit();
}
catch (err) {
    await db.rollback();
    throw (err);
}
finally {
    try {
        await db.useTransaction(false);
    } catch (err) {
        console.error(err);
    }
}

} async function mssql_update_test() { console.log('mssql_update_test'); let db = dbFactory(yf_gcgs); await db.useTransaction(true); try { let update = await db.update('INVMB_MT'); update.where('ID', 17) .column('CREATEDBY', '000057') .column('LASTMODIFIEDDATE', new Date()) .column('CREATORBM', 45); let r = await update.execute(); console.dir(r);

    await db.commit();
}
catch (err) {
    await db.rollback();
    throw (err);
}
finally {
    await db.useTransaction(false);
}

} async function mssql_del_test() { console.log('mssql_del_test'); let db = dbFactory(yf_gcgs); await db.useTransaction(true); try { let del = await db.delete('INVMB_MT'); del.where('ID', 37); let r = await del.execute(); console.dir(r);

    await db.commit();
}
catch (err) {
    await db.rollback();
    throw (err);
}
finally {
    await db.useTransaction(false);
}

} async function mssql_storedProcedure_test() { console.log('mssql_storedProcedure_test'); let db = dbFactory(yf_gcgs); try { let proc = await db.storedProcedure('proc_nodejs_test'); proc.parameter('en', 'wangshw') .parameter('cn', '王绍伟') .parameterOut('fullname'); let r = await proc.execute(); console.dir(r); } catch (err) { throw (err); } }

async function run() { try { await ora_query_test(); //await ora_insert_test(); //await ora_update_test(); //await ora_del_test(); //await ora_storedProcedure_test();

    //await mssql_query_test();
    //await mssql_insert_test();
    //await mssql_update_test();
    //await mssql_del_test();
    //await mssql_storedProcedure_test();
}
catch (err) {
    console.dir(err);
    //console.log(err.message.split('\n')[0]);
}

}

run();