Coder's Space

  CREATE PROCEDURE [dbo].[SP_GETLEADERBOARD]  @UserId INT  AS  BEGIN   DECLARE @IsExist Int IF EXISTS(Select * from OnGoingReports where UserId=@UserId) BEGIN SET @IsExist=1 END ELSE BEGIN SET @IsExist=0 END --gets the user name,tweetsreported,rank of top 10 users from ongoingreports table select Top 10 UserId,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Rank  from (select ogr.UserId,Name,Count(*) UserReportedTweetCount from UserCredentials uc inner join OnGoingReports ogr on ogr.UserId=uc.UserId  where uc.IsActive=1 GROUP BY ogr.UserId,uc.Name) TopList   UNION    --if user reported then the if block executes and calculates rank based on tweets reported select UserId,Name,UserReportedTweetCount,Rank from (select UserId,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Ran...

  public List<CityMaster> getcities()         {             List<CityMaster> cities = new List<CityMaster>();             try             {                 var res = new List<CityMaster>();                 string path1 = $"{Directory.GetCurrentDirectory()}{@"\wwwroot\assets\citieslist.json"}";                 using (StreamReader r = new StreamReader(path1))                 {                     string json = r.ReadToEnd();                     res = JsonConvert.DeserializeObject<List<CityMaster>>(json);                 }      ...

 SELECT City, COUNT (OrgId) as NoofOrgs FROM Organisation_Address oa inner join Organisations org  on org.Id=oa.OrgId Where City!='' and Org.IsActive=1 and org.IsApproved=1 GROUP BY City 

SELECT ChildUserType.Id as CategoryId,ChildUserType.Category as CategoryName, ChildUserType.IsActive,ChildUserType.CategoryURL as CategoryURL, ChildUserType.CreatedOn,ChildUserType.ParentId, ParentUserType.Category as ChildParentname,ChildUserType.NoofOrgs FROM  [dbo].[Categories] AS ChildUserType LEFT JOIN  [dbo].[Categories] AS ParentUserType  ON  ChildUserType.ParentId = ParentUserType.Id 

--creating a trigger CREATE TRIGGER [dbo].UPDATEORGSCOUNTINCATEGORY_AFTERINSERT_FROMORGANISATIONS]   ON --here we mention the table name   [dbo].[Organisations] --here we can have insert, update, delete as trigger point --I want to execute my trigger when a record is inserted in organizations table (step-1) --then get the count of orgs in each category from organisationcategories table and save it in temp table(step-2) --then update the category table with orgs count in each category(step-3)   AFTER INSERT   AS   BEGIN   --we are updating the count bcz a listing or nonprofi can be approved or rejected then the count changes. --creating a temp table with two columns of int type DECLARE @temp TABLE ( CategoryId INT, NoofOrgs INT ) --inserting values into temp table INSERT INTO @temp SELECT * from (         --here we are selecting these two columns select CategoryId,ISNULL(NoofOrgs , 0) AS 'NoofOrgs' from (   ...

  public static DateTime GetDateTime()   { //here we are trying to get the indian datetime irrespective of hosted server location             TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("India Standard Time");             DateTime now = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, timeZoneInfo);             return now;   }

 Here I'm attaching the Json String of Countries List, States List, Cities List and also the scripts for all countries, states, cities. You can just , use and convert the Json string to List Object and use them. Models For Country, State, City: public class CountryMaster { public int ID { get; set; } public string CountryName { get; set; } public string CountryCode { get; set; } } public class StateMaster { public int ID { get; set; } public string StateName { get; set; } public int CountryID { get; set; } } public class CityMaster { public int ID { get; set; } public string CityName { get; set; } public int StateID { get; set; } } Note: The above models are as per json   For Sample, To get the country list you can use below code,  var res = JsonConvert.DeserializeObject<List<CountryMaster>>("country json string");  Drive link: drivelink