Wednesday, June 21, 2023

connectnodejs

 var sql = require("mssql");


var config = {
  user: "sa",
  password: "Welcome@123",
  server: "NATTUTHURAI\\SQLEXPRESS",
  database: "eventmanagement",
  port: "1433",
  dialect: "mssql",
  dialectOptions: {
    instanceName: "SQLEXPRESS",
  },
};

(async () => {
  try {
    let pool = await sql.connect(config);
    const request = pool.request();
    request.query("select * from Sales", (err, result) => {
      console.dir(result);
    });
  } catch (err) {
    console.log("This is Error");
    console.log(err);
    console.dir(err);
  }
})();

sql.on("error", (err) => {
  console.log("This is Error handler");
});

Saturday, June 3, 2023

SQL Express NodeJS

 'use strict';


const eventData = require('../data/events');

const getAllEvents = async (req, res, next) => {
    try {

        const eventlist = await eventData.getEvents();
        res.send(eventlist);        
    } catch (error) {
        res.status(400).send(error.message);
    }
}

const getEvent = async (req, res, next) => {
    try {
        const eventId = req.params.id;
        const event = await eventData.getById(eventId);
        res.send(event);
    } catch (error) {
        res.status(400).send(error.message);
    }
}

const addEvent = async (req, res, next) => {
    try {
        const data = req.body;
        const insert = await eventData.creatEvent(data);
        res.send(insert);
    } catch (error) {
        res.status(400).send(error.message);
    }
}

const updatEvent = async (req, res, next) => {
    try {
        const eventId =  req.params.id;
        const data = req.body;
        const updated = await eventData.updateEvent(eventId, data);
        res.send(updated);
    } catch (error) {
        res.status(400).send(error.message);
    }
}

const deleteEvent = async (req, res, next) => {
    try {
        const eventId = req.params.id;
        const deletedEvent = await eventData.deleteEvent(eventId);
        res.send(deletedEvent);
    } catch (error) {
        res.status(400).send(error.message);
    }
}

module.exports = {
    getAllEvents,
    getEvent,
    addEvent,
    updatEvent,
    deleteEvent
}

createEvent.sql

INSERT INTO [dbo].[events]
    (
        [eventTitle],
        [eventDescription],
        [startDate],
        [endDate],
        [avenue],
        [maxMembers]
    )
VALUES
    (
        @eventTitle,
        @eventDescription,
        @startDate,
        @endDate,
        @avenue,
        @maxMembers
    )

SELECT SCOPE_IDENTITY() AS eventId

deleteEvent.sql

DELETE [dbo].[events]
WHERE [eventId]=@eventId

eventbyId.sql

SELECT [eventId]
    ,[eventTitle]
    ,[eventDescription]
    ,[startDate]
    ,[endDate]
    ,[avenue]
    ,[maxMembers]
FROM [dbo].[events]
WHERE [eventId]=@eventId

eventslist.sql

SELECT [eventId]
      ,[eventTitle]
      ,[eventDescription]
      ,[startDate]
      ,[endDate]
      ,[avenue]
      ,[maxMembers]
  FROM [dbo].[events]

index.js

'use strict';
const utils = require('../utils');
const config = require('../../config');
const sql = require('mssql');

const getEvents = async () => {
    try {
        let pool = await sql.connect(config.sql);
        const sqlQueries = await utils.loadSqlQueries('events');
        const eventsList = await pool.request().query(sqlQueries.eventslist);
        return eventsList.recordset;
    } catch (error) {
        console.log(error.message);
    }
}

const getById = async(eventId) => {
    try {
        let pool = await sql.connect(config.sql);
        const sqlQueries = await utils.loadSqlQueries('events');
        const event = await pool.request()
                            .input('eventId', sql.Int, eventId)
                            .query(sqlQueries.eventbyId);
        return event.recordset;
    } catch (error) {
        return error.message;
    }
}

const creatEvent = async (eventdata) => {
    try {
        let pool = await sql.connect(config.sql);
        const sqlQueries = await utils.loadSqlQueries('events');
        const insertEvent = await pool.request()
                            .input('eventTitle', sql.NVarChar(100), eventdata.eventTitle)
                            .input('eventDescription', sql.NVarChar(1500), eventdata.eventDescription)
                            .input('startDate', sql.Date, eventdata.startDate)
                            .input('endDate', sql.Date, eventdata.endDate)
                            .input('avenue', sql.NVarChar(200), eventdata.avenue)
                            .input('maxMembers', sql.Int, eventdata.maxMembers)
                            .query(sqlQueries.createEvent);                            
        return insertEvent.recordset;
    } catch (error) {
        return error.message;
    }
}

const updateEvent = async (eventId, data) => {
    try {
        let pool = await sql.connect(config.sql);
        const sqlQueries = await utils.loadSqlQueries('events');
        const update = await pool.request()
                        .input('eventId', sql.Int, eventId)
                        .input('eventTitle', sql.NVarChar(100), data.eventTitle)
                        .input('eventDescription', sql.NVarChar(1500), data.eventDescription)
                        .input('startDate', sql.Date, data.startDate)
                        .input('endDate', sql.Date, data.endDate)
                        .input('avenue', sql.NVarChar(200), data.avenue)
                        .input('maxMembers', sql.Int, data.maxMembers)
                        .query(sqlQueries.updateEvent);
        return update.recordset;
    } catch (error) {
        return error.message;
    }
}

const deleteEvent = async (eventId) => {
    try {
        let pool = await sql.connect(config.sql);
        const sqlQueries = await utils.loadSqlQueries('events');
        const deleteEvent = await pool.request()
                            .input('eventId', sql.Int, eventId)
                            .query(sqlQueries.deleteEvent);
        return deleteEvent.recordset;
    } catch (error) {
        return error.message;
    }
}

module.exports = {
    getEvents,
    getById,
    creatEvent,
    updateEvent,
    deleteEvent
}


updateEvent.sql

UPDATE [dbo].[events]
SET [eventTitle]=@eventTitle,
    [eventDescription]=@eventDescription,
    [startDate]=@startDate,
    [endDate]=@endDate,
    [avenue]=@avenue,
    [maxMembers]=@maxMembers
WHERE [eventId]=@eventId

SELECT [eventId]
      ,[eventTitle]
      ,[eventDescription]
      ,[startDate]
      ,[endDate]
      ,[avenue]
      ,[maxMembers]
  FROM [dbo].[events]
  WHERE [eventId]=@eventId

utils.js

'use strict';
const fs = require('fs-extra');
const {join} = require('path');

const loadSqlQueries = async (folderName) => {
    const filePath = join(process.cwd(), 'data', folderName);
    const files = await fs.readdir(filePath);
    const sqlFiles = files.filter(f => f.endsWith('.sql'));
    const queries = {};
    for (const sqlfile of sqlFiles) {
        const query = fs.readFileSync(join(filePath,  sqlfile), {encoding: "UTF-8"});
        queries[sqlfile.replace(".sql", "")] = query;
    }
    return queries;
}

module.exports = {
    loadSqlQueries
}

eventRoutes.js

'use strict';

const express = require('express');
const eventControll = require('../controllers/eventController');
const router = express.Router();

router.get('/events', eventControll.getAllEvents);
router.get('/event/:id', eventControll.getEvent);
router.post('/event', eventControll.addEvent);
router.put('/event/:id', eventControll.updatEvent);
router.delete('/event/:id', eventControll.deleteEvent);


module.exports = {
    routes: router
}

NODE_ENV=development

#express server config
PORT=8080
HOST=localhost
HOST_URL=http://localhost:8080


#sql server config
SQL_USER=sa
SQL_PASSWORD=Welcome@123
SQL_DATABASE=eventmanagement
SQL_SERVER=Nattuthurai
SQL_ENCRYPT=false

config.js

'use strict';

const dotenv = require('dotenv');
const assert = require('assert');

dotenv.config();

const {PORT, HOST, HOST_URL, SQL_USER, SQL_PASSWORD, SQL_DATABASE, SQL_SERVER} = process.env;

const sqlEncrypt = process.env.SQL_ENCRYPT === "true";

assert(PORT, 'PORT is require');
assert(HOST, 'HOST is required');

module.exports = {
    port: PORT,
    host: HOST,
    url: HOST_URL,
    sql: {
        server: SQL_SERVER,
        database: SQL_DATABASE,
        user: SQL_USER,
        password: SQL_PASSWORD,
        options: {
            encrypt: sqlEncrypt,
            enableArithAbort: true
        },
    },
};

index.js

'use strict';
const express = require('express');
const config = require('./config');
const cors = require('cors');
const bodyParser = require('body-parser');
const eventRoutes = require('./routes/eventRoutes');

const app = express();

app.use(express.json());
app.use(cors());
app.use(bodyParser.json());

app.use('/api', eventRoutes.routes);



app.listen(config.port, () => {
  console.log('app listening on url http://localhost:' + config.port )
});






Thursday, May 12, 2011

Sharepoint users who have not viewed documents in a SharePoint document library

        public string[] GetAllSharePointPeople()
        {
            SPSite Site = new SPSite("http://crm-srv:6677/");
            SPUserCollection AllUsers = Site.RootWeb.AllUsers;
            string strUserName = string.Empty;
            foreach (SPUser user in AllUsers)
            {
                if (strUserName == string.Empty)
                {
                    strUserName = user.Name.ToString();
                }
                else
                {
                    strUserName += "/" + user.Name.ToString();
                }
            }
            return strUserName.Split('/');
        }
        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dtTable = new DataTable();
            dtTable.Columns.Add("DocumentName");
            dtTable.Columns.Add("UnViewedPeopleName");
            string[] arrAllUsers = GetAllSharePointPeople().Distinct().ToArray();
            SPSite oSiteCollection = new SPSite("http://crm-srv:6677/");
            SPWebCollection collWebsites = oSiteCollection.AllWebs;
            foreach (SPWeb oWebsite in collWebsites)
            {
                SPListCollection collLists = oWebsite.Lists;
                foreach (SPList oList in collLists)
                {
                    if (oList.BaseType == SPBaseType.DocumentLibrary)
                    {
                        SPDocumentLibrary oDocumentLibrary = (SPDocumentLibrary)oList;
                        if (oList.BaseTemplate == SPListTemplateType.DocumentLibrary)
                        {
                            SPListItemCollection collListItems = oDocumentLibrary.Items;
                            string viewedUserName = string.Empty;
                            foreach (SPListItem oListItem in collListItems)
                            {
                                if (oList.Title != "Site Collection Images" &&
                                    oList.Title != "Images" &&
                                    oList.Title != "Style Library" &&
                                    oList.Title != "Reporting Templates" &&
                                    oList.Title != "Application Pages")
                                {
                                    string strWebSiteName = oWebsite.Name;
                                    string strListTitle = oList.Title;
                                    string strListItemName = oListItem.Name;
                                    SPAuditQuery wssQuery;
                                    SPAuditEntryCollection auditCol;
                                    wssQuery = new SPAuditQuery(oSiteCollection);
                                    wssQuery.RestrictToListItem(oListItem);
                                    auditCol = oWebsite.Audit.GetEntries(wssQuery);
                                    foreach (SPAuditEntry oAuditEntry in auditCol)
                                    {
                                        if (oAuditEntry.Event.ToString()  == "View")
                                        {
                                            string[] strArray = oAuditEntry.DocLocation.ToString().Split('/');
                                            if (strArray[strArray.Length - 1] == strListItemName)
                                            {
                                                if (viewedUserName == string.Empty)
                                                {
                                                    viewedUserName = oWebsite.AllUsers.GetByID(Convert.ToInt32(oAuditEntry.UserId)).Name;
                                                }
                                                else
                                                {
                                                    viewedUserName += "/" + oWebsite.AllUsers.GetByID(Convert.ToInt32(oAuditEntry.UserId)).Name;
                                                }
                                                strListItemName = string.Empty;
                                            }
                                        }
                                       
                                    }
                                }
                                string[] arrViewedUsers=viewedUserName.Split('/');
                                string[] arrNotViewUsers = arrAllUsers.Except(arrViewedUsers).ToArray();
                                foreach (string arr in arrNotViewUsers)
                                {
                                    DataRow dr = dtTable.NewRow();
                                    dr["DocumentName"]=oListItem.Name;
                                    dr["UnViewedPeopleName"] = arr;
                                    dtTable.Rows.Add(dr);
                                }
                                viewedUserName = string.Empty;
                            }
                        }
                    }
                }
                oWebsite.Dispose();
            }
            DataTable dt = dtTable;
        }

Get SharePoint webservice list to DataTable

       public DataTable GetCountryDataTable()
        {
            DataTable dtTable = null;
            try
            {
                SharePointList.Lists oList = new SharePointList.Lists();
                oList.PreAuthenticate = true;
                oList.Credentials = new System.Net.NetworkCredential(UserName, Password, Domain);
                oList.Url = Url;
                XmlNode itemCollection = oList.GetListItems("CountryList", string.Empty, null, null, "0", null, "");
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.LoadXml(itemCollection.OuterXml);
                DataSet ds = new DataSet();
                StringReader stringReader = new StringReader(xmlDoc.OuterXml);
                ds.ReadXml(stringReader);
                stringReader.Dispose();
                if (ds.Tables[1].Rows.Count > 0)
                {
                    dtTable = ds.Tables[1];
                }
            }
            catch (Exception ex)
            {
                Err.ErrorLog(HttpContext.Current.Server.MapPath("Logs/ErrorLog"), "GetCountryDataTable Method:" + ex.Message);
            }
            finally
            {
            }
            return dtTable;
        }

select all colum from sharepoint webservice list and fetch more than 5000 rows by paging

        public .DataTable GetSchedulerCalendarDataTable()
        {
            System.Data.DataTable dtTable = null;
            try
            {
                SharePointList.Lists listService = new SharePointList.Lists();
                string listName = "Scheduler Calendar";
                listService.Credentials = new NetworkCredential(UserName, Password, Domain);
                XmlNamespaceManager nsmgr;
                XmlNode listView = listService.GetListAndView(listName, null);
                nsmgr = new XmlNamespaceManager(listView.OwnerDocument.NameTable);
                nsmgr.AddNamespace("a", "http://schemas.microsoft.com/sharepoint/soap/");
                List<String> fieldNames = new List<string>();
                foreach (XmlNode field in listView.SelectNodes("a:List/a:Fields/a:Field", nsmgr))
                {
                    XmlAttribute attr = field.Attributes["Name"];
                    // it should not be null, but we check it
                    if (attr != null)
                    {
                        // we store all fields in a collection for later use
                        fieldNames.Add(attr.Value);
                    }
                }
                // get data from list
                XmlDocument xmlDoc = new System.Xml.XmlDocument();
                XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query", "");
                XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");
                XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");
                ndQueryOptions.InnerXml = "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>";
                ndQuery.InnerXml = "<OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy>";
                XmlNode positionNext = null;
                while (true)
                {
                    XmlNode items = listService.GetListItems(listName, null, ndQuery, ndViewFields, "1000", ndQueryOptions, null);
                    nsmgr = new XmlNamespaceManager(items.OwnerDocument.NameTable);
                    nsmgr.AddNamespace("z", "#RowsetSchema");
                    nsmgr.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");
                    XmlNodeList itemNodeList = items.SelectNodes("rs:data/z:row", nsmgr);
                    foreach (XmlNode itemNode in itemNodeList)
                    {
                        foreach (String fieldName in fieldNames)
                        {
                            String wsFieldName = "ows_" + fieldName;
                            XmlAttribute attr = itemNode.Attributes[wsFieldName];
                            // if the attribute is missing, we should add it
                            if (attr == null)
                            {
                                attr = itemNode.OwnerDocument.CreateAttribute(wsFieldName);
                                itemNode.Attributes.Append(attr);
                            }
                        }
                    }
                    DataSet listDataSet = new DataSet();
                    // read the result into a data set
                    XmlTextReader readerListDataSet = new XmlTextReader(items.OuterXml, XmlNodeType.Document, null);
                    listDataSet.ReadXml(readerListDataSet);
                    if (listDataSet.Tables[1].Rows.Count > 0)
                    {
                        foreach (DataRow item in listDataSet.Tables[1].Rows)
                        {
                            if (dtTable == null)
                            {
                                dtTable = GetExcelSheetDataTable();
                            }
                            DataRow dr = dtTable.NewRow();
                            string strartDate = string.Empty;
                            if (item["ows_EventDate"] != null) strartDate = (item["ows_EventDate"].ToString() == string.Empty) ? string.Empty : Convert.ToDateTime(item["ows_EventDate"].ToString()).ToString("MM/dd/yyyy");                           
dtTable.Rows.Add(dr);
                        }
                    }

                    positionNext = items.SelectSingleNode("//@ListItemCollectionPositionNext");
                    if (positionNext == null)
                        break; // break if it is the last page
                    else
                        // set next page info if there is more page
                        ndQueryOptions.InnerXml = "<Paging ListItemCollectionPositionNext='" + positionNext.InnerXml + "' />";
                }

            }
            catch (Exception ex)
            {
                Err.ErrorLog(HttpContext.Current.Server.MapPath("Logs/ErrorLog"), "getSchedulerCalendarDataTable Method:" + ex.Message);
            }
            finally
            {
            }
            return dtTable;
        }

101% shares

101% shares
about me