Python Web Scraping

 import requests

import json

cookies = None;
url = 'https://www.nseindia.com/api/option-chain-indices?symbol=NIFTY'
headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, '
                         'like Gecko) '
                         'Chrome/80.0.3987.149 Safari/537.36',
           'upgrade-insecure-requests': '0',
           'accept-language': 'en,gu;q=0.9,hi;q=0.8', 'accept-encoding': 'gzip, deflate, br'}

def loadnsecookie():
    baseurl = "https://www.nseindia.com/"
    session = requests.Session()
    request = session.get(baseurl, headers=headers, timeout=5)
    cookies = dict(request.cookies)


#response = session.get(url, headers=headers, timeout=5, cookies=cookies)
#print(response.json())
#r = requests.get(url, headers=headers)
expiry = "20-Oct-2022"
#print(expiry)
#exit

def fetch_oi():
    print('fetch oi called')
    r = requests.get(url, headers=headers, timeout=5, cookies=cookies).json()
    print('response received')
    #with open('oidata.json',"w") as files:
    #    files.write(json.dumps(r, indent=4, sort_keys=True))
    #print('file written')


def main():
    loadnsecookie()
    fetch_oi()

print(__name__)
if __name__ == '__main__':
    print(1)
    main()
    print(2)

Update MYSQL Definers - All at once

 show procedure status where Db <> 'DatabaseName' and Definer = 'localuser@%';

 

SET SQL_SAFE_UPDATES = 0;

update mysql.proc set definer='localuser@%' where definer='root@localhost' and db = 'DatabaseName'        ;

SET SQL_SAFE_UPDATES = 1;

 

Moment: Creating datefield with knockout

ko.bindingHandlers.datepicker = {

        init: function(elementvalueAccessorallBindingsAccessor) {

            var $el = $(element);

            

            //initialize datepicker with some optional options

            var options = allBindingsAccessor().datepickerOptions || {};

            $el.datepicker(options);

            //handle the field changing

            ko.utils.registerEventHandler(element"change"function() {

                var observable = valueAccessor();

                observable($el.datepicker("getDate"));

            });

            //handle disposal (if KO removes by the template binding)

            ko.utils.domNodeDisposal.addDisposeCallback(elementfunction() {

                $el.datepicker("destroy");

            });

        },

        update: function(elementvalueAccessor) {

            var value = ko.utils.unwrapObservable(valueAccessor()),

                $el = $(element),

                current = $el.datepicker("getDate");

            

            if (value - current !== 0) {

                $el.datepicker("setDate"value);   

            }

        }

    };

 

 

<input class="form-control" data-bind="datepicker:paidon"/>

 

self.paidon = ko.observable(new Date());

Brain Dumps

 https://free-braindumps.com/microsoft/free-pl-200-braindumps.html?p=3

Py Test Holder

  import pyodbc
import pyodbc as po
 
cnxn_str = (
        r'DRIVER=ODBC Driver 17 for SQL Server;'
        r'SERVER=(LocalDB)\MSSQLLocalDB;'
        r'Trusted_Connection=yes;'
        r'AttachDbFileName=D:\StockProject\tickertestdb.mdf;'
    )
 
try:
    # Connection string
    cnxn = po.connect(cnxn_str)
    cursor = cnxn.cursor()
    cursor2 = cnxn.cursor()
    
    stp = "Exec GetTickers"
    cursor.execute( stp )
    row = cursor.fetchone()
    while row:
        # Print the row
        print(row[0])
        
        cursor = cnxn.cursor()
        storedProc2 = "Exec WUP_FindCrosses @ScriptId = ?"
        params = (row[0])
        cursor2.execute( storedProc2, params )

        print("Fetch Success")
        row2 = cursor2.fetchone()
        try:
            while row2:
                # Print the row
                print(str(row2[0]) + " : " + str(row2[1] or '') )
                row2 = cursor2.fetchone()

            row = cursor.fetchone()
        except Exception as t:
            print("Error: %s" % t)            
    # Prepare the stored procedure execution script and parameter values
    # storedProc = "Exec WUP_FindCrosses @ScriptId = ?"
    # params = (1)
 
    # Execute Stored Procedure With Parameters
    # cursor.execute( storedProc, params )
 
    # Iterate the cursor
    # row = cursor.fetchone()
    # while row:
    #     # Print the row
    #     print(str(row[0]) + " : " + str(row[1] or '') )
    #     row = cursor.fetchone()
 
    # Close the cursor and delete it
    cursor.close()
    del cursor
 
    # Close the database connection
    cnxn.close()
 
except Exception as e:
    print("Error: %s" % e)

import pyodbc import pyodbc as po cnxn_str = ( r'DRIVER=ODBC Driver 17 for SQL Server;' r'SERVER=(LocalDB)\MSSQLLocalDB;' r'Trusted_Connection=yes;' r'AttachDbFileName=D:\StockProject\tickertestdb.mdf;' ) try: # Connection string cnxn = po.connect(cnxn_str) cursor = cnxn.cursor() cursor2 = cnxn.cursor() stp = "Exec GetTickers" cursor.execute( stp ) row = cursor.fetchone() while row: # Print the row print(row[0]) cursor = cnxn.cursor() storedProc2 = "Exec WUP_FindCrosses @ScriptId = ?" params = (row[0]) cursor2.execute( storedProc2, params ) print("Fetch Success") row2 = cursor2.fetchone() try: while row2: # Print the row print(str(row2[0]) + " : " + str(row2[1] or '') ) row2 = cursor2.fetchone() row = cursor.fetchone() except Exception as t: print("Error: %s" % t) # Prepare the stored procedure execution script and parameter values # storedProc = "Exec WUP_FindCrosses @ScriptId = ?" # params = (1) # Execute Stored Procedure With Parameters # cursor.execute( storedProc, params ) # Iterate the cursor # row = cursor.fetchone() # while row: # # Print the row # print(str(row[0]) + " : " + str(row[1] or '') ) # row = cursor.fetchone() # Close the cursor and delete it cursor.close() del cursor # Close the database connection cnxn.close() except Exception as e: print("Error: %s" % e)

Dummy Test

USE [jintemples] GO /****** Object: Table [dbo].[DBBhojanShala] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBBhojanShala]( [Id] [int] NOT NULL, [Title] [nvarchar](50) NULL, [TrustId] [int] NULL, [ContactDetailId] [int] NULL, [NavkarshiTime] [nvarchar](50) NULL, [LunchTime] [nvarchar](50) NULL, [HighTeaTime] [nvarchar](50) NULL, [DinnerTime] [nvarchar](50) NULL, CONSTRAINT [PK_DBBhojanShala] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBCity] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBCity]( [Id] [int] NOT NULL, [Name] [nvarchar](50) NULL, [State] [nvarchar](50) NULL, [Country] [nvarchar](50) NULL, [Latitude] [nvarchar](50) NULL, [Longitude] [nvarchar](50) NULL, CONSTRAINT [PK_DBCity] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBContactDetails] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBContactDetails]( [Id] [int] NOT NULL, [Address1] [nvarchar](50) NULL, [Address2] [nvarchar](50) NULL, [ContactNumber1] [nvarchar](50) NULL, [ContactNumber2] [nvarchar](50) NULL, [EmailAddress] [nvarchar](50) NULL, [ContactPerson] [nvarchar](50) NULL, [Website] [nvarchar](50) NULL, CONSTRAINT [PK_DBContactDetails] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBDataRequest] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBDataRequest]( [Id] [int] NOT NULL, [RequestEntity] [int] NULL, [RequestedJson] [nvarchar](max) NULL, [Status] [int] NULL, [Field] [nvarchar](50) NULL, [Value] [nvarchar](50) NULL, [RequestedBy] [nvarchar](50) NULL, [RequestedDate] [datetime] NULL, [ApprovedBy] [nvarchar](50) NULL, [ApprovedDate] [datetime] NULL, CONSTRAINT [PK_DBDataRequest] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DBDataUpdates] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBDataUpdates]( [Id] [int] NOT NULL, [UpdatedOn] [datetime] NULL, [UpdatedBy] [int] NULL, [Status] [int] NULL, [PointTransactionId] [int] NULL, [UpdatedEntityPrimaryId] [int] NULL, [UpdatedField] [nvarchar](50) NULL, [UpdatedFieldValue] [nvarchar](max) NULL, [ReviewedBy] [int] NULL, [ReviewedOn] [int] NULL, [ReviewerComment] [nvarchar](255) NULL, CONSTRAINT [PK_DBDataUpdates] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DBDharmshala] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBDharmshala]( [Id] [int] NOT NULL, [Title] [nvarchar](50) NULL, [TrustId] [int] NULL, [ContactDetailId] [int] NULL, [CheckInTime] [nvarchar](50) NULL, CONSTRAINT [PK_DBDharmshala] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBENumChild] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBENumChild]( [Id] [int] NOT NULL, [EnumMasterId] [int] NULL, [Value] [int] NULL, [Title] [nvarchar](50) NULL, [Description] [nvarchar](max) NULL, CONSTRAINT [PK_DBENumChild] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DBEnumMaster] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBEnumMaster]( [Id] [int] NOT NULL, [Title] [nvarchar](50) NULL, [IsActive] [bit] NULL, CONSTRAINT [PK_DBEnumMaster] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBIdolMaster] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBIdolMaster]( [Id] [int] NOT NULL, [Title] [nvarchar](50) NULL, [IdolTypeId] [int] NULL, [PanchKalyanak] [nvarchar](50) NULL, [KevalgyanOn] [nvarchar](50) NULL, CONSTRAINT [PK_DBIdolMaster] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBIdolMasterTempleMapping] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBIdolMasterTempleMapping]( [Id] [int] NOT NULL, [TempleId] [int] NULL, [IdolId] [int] NULL, [IsMoolnayak] [int] NULL, [Image] [nvarchar](255) NULL, CONSTRAINT [PK_DBIdolMasterTempleMapping] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBIdolType] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBIdolType]( [Id] [int] NOT NULL, [Title] [nvarchar](50) NULL, CONSTRAINT [PK_DBIdolType] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBImageMapping] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBImageMapping]( [Id] [int] NOT NULL, [Image] [nvarchar](255) NULL, [ImageType] [nvarchar](50) NULL, [SequenceOrder] [int] NULL, CONSTRAINT [PK_DBImageMapping] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBMaharaj] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBMaharaj]( [Id] [int] NOT NULL, [Name] [nvarchar](50) NULL, [Gender] [int] NULL, [Thana] [nvarchar](50) NULL, [DikshaDate] [nvarchar](50) NULL, [Designation] [nvarchar](50) NULL, CONSTRAINT [PK_DBMaharaj] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBMembers] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBMembers]( [Id] [int] NOT NULL, [FirstName] [nvarchar](50) NULL, [MiddleName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [ContactDetailsId] [int] NULL, CONSTRAINT [PK_DBMembers] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBPointsDistribution] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBPointsDistribution]( [Id] [int] NOT NULL, [Title] [nvarchar](50) NULL, [Description] [nvarchar](255) NULL, [Points] [decimal](18, 2) NULL, CONSTRAINT [PK_DBPointsDistribution] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBPointTransaction] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBPointTransaction]( [Id] [int] NOT NULL, [PointsEarned] [decimal](18, 2) NULL, [TransactionTime] [datetime] NULL, [TransactionNote] [nvarchar](255) NULL, [Status] [int] NULL, [UserId] [int] NULL, CONSTRAINT [PK_DBPointTransaction] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBReferrals] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBReferrals]( [Id] [int] NOT NULL, [UserId] [nvarchar](255) NULL, [ReferralUserId] [nvarchar](255) NULL, [Status] [int] NULL, CONSTRAINT [PK_DBReferrals] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBTemple] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBTemple]( [Id] [int] NOT NULL, [Title] [nvarchar](50) NULL, [BriefDescription] [nvarchar](255) NULL, [ContactDetailId] [int] NULL, [TrustId] [int] NULL, [CityId] [int] NULL, [TempleTypeId] [int] NULL, [EstablishmentYear] [nvarchar](50) NULL, [EstablishBy] [nvarchar](255) NULL, CONSTRAINT [PK_DBTemple] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBTempleImageMapping] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBTempleImageMapping]( [Id] [int] NOT NULL, [TempleId] [int] NULL, [Image] [nvarchar](255) NULL, [SequenceOrder] [int] NULL, CONSTRAINT [PK_DBTempleImageMapping] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBTrust] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBTrust]( [Id] [int] NOT NULL, [Title] [nvarchar](50) NULL, [ContactDetailsId] [int] NULL, CONSTRAINT [PK_DBTrust] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBTrustCommittee] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBTrustCommittee]( [Id] [int] NOT NULL, [TrustId] [int] NULL, [Designation] [nvarchar](50) NULL, [IsActive] [int] NULL, CONSTRAINT [PK_DBTrustCommittee] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBUpasara] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBUpasara]( [Id] [int] NOT NULL, [Title] [nvarchar](50) NULL, [TrustId] [int] NULL, [ContactDetailId] [int] NULL, [IsTemp] [int] NULL, CONSTRAINT [PK_DBUpasara] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBUpasaraMaharajMapping] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBUpasaraMaharajMapping]( [Id] [int] NOT NULL, [FromDate] [datetime] NULL, [ToDate] [datetime] NULL, [MaharajId] [int] NULL, [UpasaraId] [int] NULL, CONSTRAINT [PK_DBUpasaraMaharajMapping] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBUserComments] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBUserComments]( [Id] [int] NOT NULL, [ReferenceTypeId] [int] NULL, [ReferenceTypePrimaryId] [int] NULL, [CommentUserId] [int] NULL, [Comments] [nvarchar](255) NULL, [CommentBy] [int] NULL, [CommentType] [int] NULL, [CommentedOn] [datetime] NULL, [Status] [int] NULL, CONSTRAINT [PK_DBUserComments] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBUsers] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBUsers]( [Id] [int] NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [MiddleName] [nvarchar](50) NULL, [Email] [nvarchar](250) NULL, [Password] [nvarchar](50) NULL, [ReferralKey] [nvarchar](50) NULL, [RegistrationDate] [datetime] NULL, [ActivationDate] [datetime] NULL, [Status] [int] NULL, [MobileNumber] [nvarchar](50) NULL, [WhatsAppNumber] [nvarchar](50) NULL, [ContributionLevel] [nvarchar](50) NULL, CONSTRAINT [PK_DBUsers] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBUserTrustMapping] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBUserTrustMapping]( [Id] [int] NOT NULL, [TrustId] [int] NULL, [UserId] [int] NULL, CONSTRAINT [PK_DBUserTrustMapping] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[DBBhojanShala] WITH CHECK ADD CONSTRAINT [FK_DBBhojanShala_DBContactDetails] FOREIGN KEY([ContactDetailId]) REFERENCES [dbo].[DBContactDetails] ([Id]) GO ALTER TABLE [dbo].[DBBhojanShala] CHECK CONSTRAINT [FK_DBBhojanShala_DBContactDetails] GO ALTER TABLE [dbo].[DBBhojanShala] WITH CHECK ADD CONSTRAINT [FK_DBBhojanShala_DBTrust] FOREIGN KEY([TrustId]) REFERENCES [dbo].[DBTrust] ([Id]) GO ALTER TABLE [dbo].[DBBhojanShala] CHECK CONSTRAINT [FK_DBBhojanShala_DBTrust] GO ALTER TABLE [dbo].[DBDharmshala] WITH CHECK ADD CONSTRAINT [FK_DBDharmshala_DBContactDetails] FOREIGN KEY([ContactDetailId]) REFERENCES [dbo].[DBContactDetails] ([Id]) GO ALTER TABLE [dbo].[DBDharmshala] CHECK CONSTRAINT [FK_DBDharmshala_DBContactDetails] GO ALTER TABLE [dbo].[DBDharmshala] WITH CHECK ADD CONSTRAINT [FK_DBDharmshala_DBTrust] FOREIGN KEY([TrustId]) REFERENCES [dbo].[DBTrust] ([Id]) GO ALTER TABLE [dbo].[DBDharmshala] CHECK CONSTRAINT [FK_DBDharmshala_DBTrust] GO ALTER TABLE [dbo].[DBTemple] WITH CHECK ADD CONSTRAINT [FK_DBTemple_DBCity] FOREIGN KEY([CityId]) REFERENCES [dbo].[DBCity] ([Id]) GO ALTER TABLE [dbo].[DBTemple] CHECK CONSTRAINT [FK_DBTemple_DBCity] GO ALTER TABLE [dbo].[DBTemple] WITH CHECK ADD CONSTRAINT [FK_DBTemple_DBTrust] FOREIGN KEY([TrustId]) REFERENCES [dbo].[DBTrust] ([Id]) GO ALTER TABLE [dbo].[DBTemple] CHECK CONSTRAINT [FK_DBTemple_DBTrust] GO ALTER TABLE [dbo].[DBTemple] WITH CHECK ADD CONSTRAINT [FK_DBTemple_DBUsers] FOREIGN KEY([CityId]) REFERENCES [dbo].[DBUsers] ([Id]) GO ALTER TABLE [dbo].[DBTemple] CHECK CONSTRAINT [FK_DBTemple_DBUsers] GO ALTER TABLE [dbo].[DBTrustCommittee] WITH CHECK ADD CONSTRAINT [FK_DBTrustCommittee_DBTrust] FOREIGN KEY([TrustId]) REFERENCES [dbo].[DBTrust] ([Id]) GO ALTER TABLE [dbo].[DBTrustCommittee] CHECK CONSTRAINT [FK_DBTrustCommittee_DBTrust] GO ALTER TABLE [dbo].[DBUpasara] WITH CHECK ADD CONSTRAINT [FK_DBUpasara_DBTrust] FOREIGN KEY([TrustId]) REFERENCES [dbo].[DBTrust] ([Id]) GO ALTER TABLE [dbo].[DBUpasara] CHECK CONSTRAINT [FK_DBUpasara_DBTrust] GO ALTER TABLE [dbo].[DBUpasaraMaharajMapping] WITH CHECK ADD CONSTRAINT [FK_DBUpasaraMaharajMapping_DBMaharaj] FOREIGN KEY([MaharajId]) REFERENCES [dbo].[DBMaharaj] ([Id]) GO ALTER TABLE [dbo].[DBUpasaraMaharajMapping] CHECK CONSTRAINT [FK_DBUpasaraMaharajMapping_DBMaharaj] GO ALTER TABLE [dbo].[DBUpasaraMaharajMapping] WITH CHECK ADD CONSTRAINT [FK_DBUpasaraMaharajMapping_DBUpasara] FOREIGN KEY([UpasaraId]) REFERENCES [dbo].[DBUpasara] ([Id]) GO ALTER TABLE [dbo].[DBUpasaraMaharajMapping] CHECK CONSTRAINT [FK_DBUpasaraMaharajMapping_DBUpasara] GO ALTER TABLE [dbo].[DBUserComments] WITH CHECK ADD CONSTRAINT [FK_DBUserComments_DBReferrals] FOREIGN KEY([CommentUserId]) REFERENCES [dbo].[DBReferrals] ([Id]) GO ALTER TABLE [dbo].[DBUserComments] CHECK CONSTRAINT [FK_DBUserComments_DBReferrals] GO ALTER TABLE [dbo].[DBUserComments] WITH CHECK ADD CONSTRAINT [FK_DBUserComments_DBReferrals1] FOREIGN KEY([CommentBy]) REFERENCES [dbo].[DBReferrals] ([Id]) GO ALTER TABLE [dbo].[DBUserComments] CHECK CONSTRAINT [FK_DBUserComments_DBReferrals1] GO ALTER TABLE [dbo].[DBUserTrustMapping] WITH CHECK ADD CONSTRAINT [FK_DBUserTrustMapping_DBTrust] FOREIGN KEY([TrustId]) REFERENCES [dbo].[DBTrust] ([Id]) GO ALTER TABLE [dbo].[DBUserTrustMapping] CHECK CONSTRAINT [FK_DBUserTrustMapping_DBTrust] GO /****** Object: StoredProcedure [dbo].[CheckLogin] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[CheckLogin] @Email nvarchar(250), @Password nvarchar(50) as begin select * from DBUsers where Email = @Email and Password = @Password end GO /****** Object: StoredProcedure [dbo].[GetMyPoints] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[GetMyPoints] @UserId int AS begin select * from DBPointTransaction where UserId = @UserId end GO /****** Object: StoredProcedure [dbo].[GetMyReferrals] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[GetMyReferrals] @Id int AS begin select b.FirstName, b.LastName, b.ActivationDate from DBReferrals --left join DBUsers a on DBReferrals.UserId = a.Id left join DBUsers b on DBReferrals.ReferralUserId = b.Id where DBReferrals.UserId = @Id and DBReferrals.Status = 1 end GO /****** Object: StoredProcedure [dbo].[GetMySubmissions] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[GetMySubmissions] @Id int AS begin select 'To be implement' end GO /****** Object: StoredProcedure [dbo].[GetReferralCode] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[GetReferralCode] @Id int AS begin select ReferralKey from DBUsers where Id = @Id end GO /****** Object: StoredProcedure [dbo].[GetTempleByTempleId] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[GetTempleByTempleId] @TempleId int as begin select * from DBTemple where Id = @TempleId end GO /****** Object: StoredProcedure [dbo].[GetTemplesByCity] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[GetTemplesByCity] @CityId int as begin select * from DBTemple where CityId = @CityId end GO /****** Object: StoredProcedure [dbo].[GetTemplesByTrust] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[GetTemplesByTrust] @TrustId int as begin select * from DBTemple where TrustId = @TrustId end GO /****** Object: StoredProcedure [dbo].[Register] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[Register] @FirstName nvarchar(50), @LastName nvarchar(50), @Email nvarchar(250), @Password nvarchar(50), @ReferralKey nvarchar(50), @MobileNumber nvarchar(50) as begin select * from DBUsers where Email = @Email and Password = @Password insert into DBUsers (FirstName, LastName, Email, Password, ReferralKey, RegistrationDate, Status, MobileNumber, WhatsAppNumber, ContributionLevel) Values (@FirstName, @LastName, @Email, @Password, @ReferralKey, GETUTCDATE(), 1, @MobileNumber, @MobileNumber, 'BRONZE') end GO /****** Object: StoredProcedure [dbo].[UpdatePassword] Script Date: 27-08-2021 02:25:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create procedure [dbo].[UpdatePassword] @Email nvarchar(250), @OldPassword nvarchar(50), @NewPassword nvarchar(50) as begin Update DBUsers set Password = @NewPassword where Email = @Email and Password = @OldPassword end GO

Nav Menu

Before Login
------------
Login
Search
Categories
Browse
Gallary
Recent Submissions
Recent Redemptions
About Us
Contact Us
-------------
After Login
-------------
Search
Categories
Browse
Submit Information
Submission Status
Gallary
My Account
Logout
---------------
My Account
---------------
Update Profile
Change Password
My Points
Redeem Points
Redeemption Status
Invite
------------------
Submit Information
-------------------
Add New Place
Add Place Contact Details
Etc etc.
Recent Submissions
Recent Redemptions
-------------------


http://nyshah-002-site1.1tempurl.com/





User Custom Action

Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

$RootUrl = "https://<PORTAL>.sharepoint.com" #Make sure no '/' at the end
$UrlCollection = "https://<PORTAL>.sharepoint.com","https://<PORTAL>.sharepoint.com/search"
$SiteAssetFolderPath = "$($PSScriptRoot)\SiteAssets"

$UserName = "O365 USERNAME/EMAIL"
$Password = "" #If not set, script will show dialog to enter password at runtime.


if($Password) {
    $SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
}
else {
    $SecurePassword = Read-Host -Prompt "Enter the password" -AsSecureString
}

Function Activate-Script([string]$Title,[int]$Sequence,[string]$ScriptSrc)
{
    $SingleCustomAction = $CustomActions | ?{$_.Title -eq $Title }
    if($SingleCustomAction)
    {
        $cAction = $SingleCustomAction[0];
        $cAction.Location = "ScriptLink";
        $cAction.Sequence = $Sequence;
        $cAction.ScriptBlock = "";
        $cAction.ScriptSrc = $ScriptSrc;
        $cAction.Update();
        $CustomActions.Update();
        #$site.Update();
        $Context.ExecuteQuery();
        Write-Host "Applying $($ScriptSrc) to $($site.Url) as UserCustomAction $($Title) [Updating]";
Write-Host ""
    }
    else
    {
        $cAction = $CustomActions.Add();
        $cAction.Title = $Title;
        $cAction.Location = "ScriptLink";
        $cAction.Sequence = $Sequence;
        $cAction.ScriptBlock = "";
        $cAction.ScriptSrc = $ScriptSrc;
        $cAction.Update();
        #$site.Update();
        $CustomActions.Update();
        $Context.ExecuteQuery();
        Write-Host "Applying $($ScriptSrc) to $($site.Url) as UserCustomAction $($Title) [Adding]";
Write-Host ""
    }
}
Function Activate-ScriptBlock([string]$Title,[int]$Sequence,[string]$ScriptBlock)
{
    $SingleCustomAction = $CustomActions | ?{$_.Title -eq $Title }
    if($SingleCustomAction) # AB Dev note, looks to be duplication of code here, can we consolidate?
    {
        $cAction = $SingleCustomAction[0];
        $cAction.Location = "ScriptLink";
        $cAction.ScriptBlock = $ScriptBlock;     
        $cAction.Sequence = $Sequence;
        $cAction.Update();
        $CustomActions.Update();
        #$site.Update();
        $Context.ExecuteQuery();
        Write-Host "Applying $($ScriptBlock) to $($site.Url) as UserCustomAction $($Title) [Updating]";
Write-Host ""
    }
    else
    {
        $cAction = $CustomActions.Add();
        $cAction.Title = $Title;
        $cAction.Location = "ScriptLink";
        $cAction.Sequence = $Sequence;
        $cAction.ScriptBlock = $ScriptBlock;
        $cAction.Update();
        #$site.Update();
        $CustomActions.Update();
        $Context.ExecuteQuery();
        Write-Host "Applying $($ScriptBlock) to $($site.Url) as UserCustomAction $($Title) [Adding]";
Write-Host ""
    }
}

Function Upload-AllFiles([Microsoft.SharePoint.Client.Web] $SPWeb, [string]$ListName, [string]$FolderPath)
{
    $List = $SPWeb.Lists.GetByTitle($ListName)
    $Context.Load($List)
    $Context.ExecuteQuery()

    Foreach ($File in (dir $FolderPath -File))
    {
        $FileStream = New-Object IO.FileStream($File.FullName,[System.IO.FileMode]::Open)
        $FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
        $FileCreationInfo.Overwrite = $true
        $FileCreationInfo.ContentStream = $FileStream
        $FileCreationInfo.URL = $File
        $Upload = $List.RootFolder.Files.Add($FileCreationInfo)
        $Context.Load($Upload)
        $Context.ExecuteQuery()
        $FileStream.Close()
    }
}

# Upload files to root site's Site Assets Library.

Write-Host $RootUrl
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($RootUrl)
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,$SecurePassword)
$Context.Credentials = $Credentials


$web = $Context.Web
 
$Context.Load($web);
$Context.ExecuteQuery();

Upload-AllFiles -SPWeb $web -ListName "Site Assets" -FolderPath $SiteAssetFolderPath

foreach($Url in $UrlCollection)
{
    Write-Host $Url
    $Context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,$SecurePassword)
    $Context.Credentials = $Credentials

    $site = $Context.Site
    $CustomActions = $site.UserCustomActions

    $Context.Load($site);
    $Context.Load($CustomActions);
    $Context.ExecuteQuery();
 
    $dateStamp = Get-Date -format "ddMMMyyyyHHmmss"

    Activate-Script -Title "<PORTAL> JQuery" -Sequence 1000 -ScriptSrc "$($RootUrl)/SiteAssets/jquery-1.8.0.min.js?v=$($dateStamp)";
    Activate-Script -Title "<PORTAL> Navigation JS" -Sequence 1002 -ScriptSrc "$($RootUrl)/SiteAssets/Navigation.js?v=$($dateStamp)";
    Activate-Script -Title "<PORTAL> Google Analytics JS" -Sequence 1002 -ScriptSrc "$($RootUrl)/SiteAssets/GoogleAnalytics.js?v=$($dateStamp)";

    Activate-ScriptBlock -Title "<PORTAL> CSS" -Sequence 1001 -ScriptBlock "document.write('<link rel=""stylesheet"" href=""$($RootUrl)/SiteAssets/Styles.css?v=$($dateStamp)"" />');";
    Activate-ScriptBlock -Title "<PORTAL> Navigation CSS" -Sequence 1005 -ScriptBlock "document.write('<link rel=""stylesheet"" href=""$($RootUrl)/SiteAssets/Navigation.css?v=$($dateStamp)"" />');";

    $Context.Dispose();
}


Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#$RootUrl = "https://<PORTAL>.sharepoint.com" #Make sure no '/' at the end
$UrlCollection = "https://<PORTAL>.sharepoint.com","https://<PORTAL>.sharepoint.com/search"

$UserName = "O365 USERNAME/EMAIL"
$Password = ""

if($Password) {
    $SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
}
else {
//If we set password as empty, it will prompt from here.
    $SecurePassword = Read-Host -Prompt "Enter the password" -AsSecureString
}

foreach($Url in $UrlCollection)
{
    $Context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,$SecurePassword)
    $Context.Credentials = $Credentials

    $Context.Load($Context.Site.UserCustomActions)
    $Context.ExecuteQuery();

    $Context.Site.UserCustomActions | Select Title

    $Context.Site.UserCustomActions.Clear()
    $Context.ExecuteQuery();
}


-------

{"postalcodes":[{"adminCode2":"474","adminName3":"Gandhinagar","adminCode1":"09","adminName2":"Ahmedabad","lng":73.063043,"countryCode":"IN","postalcode":"382481","adminName1":"Gujarat","placeName":"Chandlodia","lat":23.994149},{"adminCode2":"473","adminName3":"Gandhi Nagar","adminCode1":"09","adminName2":"Gandhi Nagar","lng":73.063043,"countryCode":"IN","postalcode":"382481","adminName1":"Gujarat","placeName":"Dr.Baou","lat":23.994149},{"adminCode2":"474","adminName3":"Daskroi","adminCode1":"09","adminName2":"Ahmedabad","lng":72.1960228926951,"countryCode":"IN","postalcode":"382481","adminName1":"Gujarat","placeName":"Gota","lat":22.709981466664846},{"adminCode2":"474","adminName3":"Ahmadabad City","adminCode1":"09","adminName2":"Ahmedabad","lng":73.063043,"countryCode":"IN","postalcode":"382481","adminName1":"Gujarat","placeName":"Nirnaynagar","lat":23.994149}]}

.Net Data Helper

This class file will help you to write .net SQL Operations easily.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace GMS.DAL {
public class DALHelper {#region Fields

public SqlConnection _Connection = null;

#endregion Fields

#region Constructors

public DALHelper(string connectionString) {
_Connection = new SqlConnection(connectionString);
}

#endregion Constructors

#region Methods

public static DataTable GetDatatable(List < string > dataList, string tableName, List < string > columnList) {
DataTable dt;
dt = new DataTable(tableName);

foreach(string column in columnList) {
dt.Columns.Add(column, typeof(string));
}

foreach(string item in dataList) {
dt.Rows.Add(item);
}

return dt;
}

public static DataTable GetDataTableOneColumn(List < object > dataList, string tableName, string columnName, Type columnType) {
DataTable dt;
dt = new DataTable(tableName);
dt.Columns.Add(columnName, columnType);

foreach(object item in dataList) {
dt.Rows.Add(item);
}

return dt;
}

public static SqlParameter SqlParameter(string parameterName, SqlDbType parameterType, object parameterValue) {
SqlParameter paramParameter = new SqlParameter("@" + parameterName, parameterType);
paramParameter.Value = parameterValue;
return paramParameter;
}

public void Dispose() {
try {
if (_Connection != null && _Connection.State != ConnectionState.Closed) _Connection.Close();
}
catch(Exception e) {
throw e;
}
}

public int ExecuteNonQuery(string query, params SqlParameter[] parameters) {
int returnValue = 0;
try {
Open();
SqlCommand oCommand = new SqlCommand(query, _Connection);
if (parameters != null) oCommand.Parameters.AddRange(parameters);

returnValue = oCommand.ExecuteNonQuery();
oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}

return returnValue;
}

public int ExecuteNonQueryProcedure(string storedProcName, params SqlParameter[] parameters) {
int returnValue = 0;
try {
Open();
SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
returnValue = oCommand.ExecuteNonQuery();
oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}

return returnValue;
}

public DataSet ExecuteProcedureReader(string storedProcName, params SqlParameter[] parameters) {
DataSet Result = new DataSet();
SqlDataAdapter objSqlDataAdapter = null;
try {
Open();
SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
oCommand.CommandTimeout = 240;
objSqlDataAdapter = new SqlDataAdapter(oCommand);
objSqlDataAdapter.Fill(Result);
oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}
return Result;
}

public DataSet ExecuteQueryReader(string query, params SqlParameter[] parameters) {
DataSet Result = new DataSet();
SqlDataAdapter objSqlDataAdapter = null;
try {
Open();

SqlCommand oCommand = new SqlCommand(query, _Connection);

if (parameters != null) oCommand.Parameters.AddRange(parameters);

objSqlDataAdapter = new SqlDataAdapter(oCommand);
objSqlDataAdapter.Fill(Result);

oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}
return Result;
}

public int ExecutScalarProcedure(string storedProcName, params SqlParameter[] parameters) {
int returnValue = 0;
try {
Open();
SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
returnValue = Convert.ToInt32(oCommand.ExecuteScalar());
oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}

return returnValue;
}

public string ExecutScalarProcedureString(string storedProcName, params SqlParameter[] parameters) {
string returnValue = string.Empty;
try {
Open();
SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
returnValue = oCommand.ExecuteScalar().ToString();
oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}

return returnValue;
}

internal static SqlParameter SqlParameter(string parameterName, SqlDbType parameterType, int parameterSize, string parameterValue) {
SqlParameter paramParameter = new SqlParameter("@" + parameterName, parameterType, parameterSize);
paramParameter.Value = parameterValue;
return paramParameter;
}

private SqlCommand GetSqlCommandObject(string storedProcName, params SqlParameter[] parameters) {
SqlCommand oCommand = new SqlCommand(storedProcName, _Connection);
oCommand.CommandType = CommandType.StoredProcedure;
if (parameters != null) oCommand.Parameters.AddRange(parameters);
return oCommand;
}

private void Open() {
try {
if (_Connection != null && _Connection.State == ConnectionState.Closed) _Connection.Open();
}
catch(Exception) {}
}

#endregion Methods
}
}

Search This Blog

Link Within Related Posts Plugin for WordPress, Blogger...