Coder's Space

  ALTER PROCEDURE [dbo].[SP_UPDATEUSERLEVEL] @Userid INT AS BEGIN Declare @CountOfUserReportedTweets INT Declare @LevelBadgeId INT SET @CountOfUserReportedTweets=(select count(*) from OnGoingReports where UserId=@Userid) --CHECKS WHETHER THE LEVEL EXIST OR NOT IF EXISTS(select Top 1 * from LevelBadge where NoofTweets<=@CountOfUserReportedTweets order by NoofTweets desc) BEGIN --IF LEVEL EXISTS UPDATE OR INSERT WITH THAT LEVEL ID SET @LevelBadgeId=(select Top 1 Id from LevelBadge where NoofTweets<=@CountOfUserReportedTweets order by NoofTweets desc) IF EXISTS(select * from UserLevel where UserId=@Userid) BEGIN UPDATE UserLevel SET LevelBadgeId=@LevelBadgeId WHERE UserId=@Userid END ELSE BEGIN INSERT INTO UserLevel(UserId,LevelBadgeId) VALUES (@Userid,@LevelBadgeId) END END ELSE BEGIN --IF NO LEVEL EXISTS THEN INSERTING DEFAULT LEVEL IF EXISTS(select * from UserLevel where UserId=@Userid) BEGIN UPDATE UserLeve...

 ALTER PROCEDURE [dbo].[SP_GETUSERPROFILE] @UserId int AS BEGIN --checks whether user reported any tweet or not IF EXISTS(SELECT * FROM OnGoingReports WHERE UserId=@UserId) BEGIN --if user already reported tweets then gets the user details and rank select UserId,UserName,Name,UserReportedTweetCount,Rank      from  (select UserId,UserName,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Rank  from      (select ogr.UserId,uc.UserName,Name,Count(*) UserReportedTweetCount from UserCredentials uc       inner join OnGoingReports ogr       on       ogr.UserId=uc.UserId        GROUP BY ogr.UserId,uc.Name,uc.UserName) as TopList ) as UserRank      where UserId=@UserId END ELSE BEGIN --if user not reported any tweets then it get the default value as 0 and rank based on tweets reported select UserId,UserName,Nam...

 ALTER PROCEDURE [dbo].[SP_GETTWEETSBYTOPIC_REPORTEDSTATUS] @TopicId INT, @UserId INT AS BEGIN select distinct tw.TweetId,tw.Id,tw.CreatedOn, tw.TwitterHandle,tw.TwitterProfileLink,tw.Tweet,tw.TweetedOn,tw.Rating, tw.NoofComments,tw.NoofLikes,tw.NoofReTweets,tw.IsActive, (select case when ogr.userid IS NOT NULL and ogr.TweetId IS NOT NULL then CAST(1 AS BIT) else CAST(0 AS BIT) end as isreported ) IsReported from tweets tw inner join tweettopic tt on tt.twtid=tw.id left join ongoingreports ogr on ogr.TweetId=tw.TweetId and ogr.UserId=@UserId where tt.TopicId=@TopicId and tw.IsActive=1 and  (UPPER(tw.Rating)='NEGATIVE' OR UPPER(tw.Rating)='FALSE') END

  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