sql server 2019 string_split function defult

select value from string_split('A,B',',')

SQL Server 2017 Features

 1. TRANSLATE: It basically takes a string as input and then translates the characters with some new characters, see below Syntax:  

TRANSLATE ( inputString, characters, translations)

For Example : SELECT TRANSLATE ('4*{1+1}/[81-41]','[]{}','()()')

Output : 4*(1+1)/(81-41)

This function is work as REPLACE function. but TRANSLATE function is easier to use and also useful when we have to replace more than one value in the string.

2. CONCATE_WS:  It simply concats all input arguments with the specified input delimiter.

Syntax : CONCAT_WS ( separator, argument1, argument1, [argumentN]… )

For Example :

SELECT CONCAT_WS(',','Count', '1', '2', '3', '4' ) AS counter;

Output : nikhil, hardik, nilesh, sandip

3. TRIM:  It simply works as the C# trim function, removes all spaces from start and end of the string.

Syntax: TRIM(inputString)

For Example :

SELECT TRIM('     Samlpe demo    ') AS result;

Output : Samlpe demo

It removes only space from start and ends, not between the word and string.

4. String_AGG:  It will used to create single comma-separated string.

Syntax: String_AGG(column name,',')

For Example :

create table names ( [name] varchar(50) )go 

insert into names values ('1'),('2'),('3'),('4')

Here I have used stuff

select stuff((select ',' + [name] as [text()] 

from names for xml path('')),1,1,'')

Here I have used string_agg

select string_agg([name],',') from names

Output : 1,2,3,4

Pagination with OFFSET and FETCH in SQL Query

 Pagination with OFFSET and FETCH in SQL Server.

In the real world, we load the data on the front from the database one time and load it into memory via data table or in some other form (main table and other supported tables) and

then loop through individual records in Parallel For Each for better performance. However, in some cases, if the data load is huge,

we may end up having memory issues loading all the records on the front so weload each individual record inside the loop in SQL which could potentially affect the performance.

Sample : 

SELECT First_Name + ' ' + Last_Name FROM REgistration

ORDER BY First_Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

In this example, it will skip the first 10 rows and return the next 5 rows.

Limitations while Using OFFSET-FETCH

ORDER BY is mandatory to use OFFSET and FETCH clause.

OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

TOP cannot be combined with OFFSET and FETCH in the same query expression.

The OFFSET/FETCH row count expression can be any arithmetic, constant, or parameter expression that will return an integer value. The row count expression does not support scalar sub-queries.

How To See Logs Of Dropped Tables From The Database in Microsoft SQL SERVER

 

Here, I will explain you how you can see logs of users.

Step 1 : First, create a new database with name "test".
Step 2 : Create a new table.

Step 3 : Now, go and drop the table by running the following command.

Step 4 : Now, select your database under Object Explorer and go to Reports >> Standard Reports >> Schema Changes History.

Step 5 : You will then see the schema change history. The report will show you who has dropped this table.

Finally, you can locate the user activity with the help of log.

sql server user login history

 SELECT * 

FROM master.dbo.sysprocesses sp 

JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid

ORDER BY spid 

go

 SELECT 

      c.session_id, 

      c.net_transport, 

      s.host_name, 

      s.program_name, 

      s.nt_user_name,

      c.connect_time, 

      s.client_interface_name,

      c.client_net_address,

      c.local_net_address, 

      s.login_name, 

      s.nt_domain, 

      s.login_time 

  FROM sys.dm_exec_connections AS c

  JOIN sys.dm_exec_sessions AS s

    ON c.session_id = s.session_id;

go

SELECT *

FROM sys.dm_exec_sessions

WHERE database_id > 0 -- OR 4 for user DBs

--GROUP BY database_id, login_name, status, host_name, program_name, nt_domain, nt_user_name;

Sql server Function Name Find

-- Get all of the dependency information 
SELECT OBJECT_NAME(sed.referencing_id) AS referencing_entity_name, 
    o.type_desc AS referencing_desciption, 
    COALESCE(COL_NAME(sed.referencing_id, sed.referencing_minor_id), '(n/a)') AS referencing_minor_id, 
    sed.referencing_class_desc, sed.referenced_class_desc, 
    sed.referenced_server_name, sed.referenced_database_name, sed.referenced_schema_name, 
    sed.referenced_entity_name, 
    COALESCE(COL_NAME(sed.referenced_id, sed.referenced_minor_id), '(n/a)') AS referenced_column_name, 
    sed.is_caller_dependent, sed.is_ambiguous 
-- from the two system tables sys.sql_expression_dependencies and sys.object 
FROM sys.sql_expression_dependencies AS sed 
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id 
-- on the function dbo.ufnGetProductDealerPrice 
WHERE sed.referencing_id = OBJECT_ID('dbo.ufnTest');

go

SELECT sm.object_id, 
   OBJECT_NAME(sm.object_id) AS object_name, 
   o.type, 
   o.type_desc, 
   sm.definition, 
   sm.uses_ansi_nulls, 
   sm.uses_quoted_identifier, 
   sm.is_schema_bound, 
   sm.execute_as_principal_id 
-- using the two system tables sys.sql_modules and sys.objects 
FROM sys.sql_modules AS sm 
JOIN sys.objects AS o ON sm.object_id = o.object_id 
-- from the function 'dbo.ufnGetProductDealerPrice' 
WHERE  sm.definition like '%ufnTest%'
ORDER BY o.type;  

.NET Annotated Monthly | October 2019

October. Around the globe, seasons ease into autumn and spring, awaiting the changing leaves or the return of nature. Stores in the USA and Germany are selling Christmas goods already – that’s just crazy! Nonetheless, October means there’s no better time to catch up on some technical reading, preferrably with a hot pumpkin-spice drink. October is a great month!

.NET news

The previous month has been full of big news! It’s here! It’s here! .NET Core 3.0 has finally been released, after only 9 (count ‘em, 9!) previews and a Release Candidate!

Download .NET Core 3 – At this link you can download the .NET Core 3.0 SDK, Runtime, or view the release notes.

The announcement was made at the .NET Conf virtual conference in which both Maarten Balliauw and Rachel Appel from JetBrains presented.

.NET tutorials and tips

How to build command line interfaces and distribute them as self-contained executables – CLIs are all the rage these days, and Radu Matei demonstrates how to build and distribute them as self-contained executables.

Use Performance Counters in .NET to measure Memory, CPU, and Everything – Full Guide Michael Shpilt does some serious measuring in this post about profiling and performance.

Detecting Sync over Async Code in ASP.NET Core – There are some excellent tips in this post about sync and async code by Derek Comartin, as well as how to avoid common async pitfalls.

gRPC and C# 8 Async stream cancellation – A continuation from last month’s post about async streams by Laurent Kempe.

ASP.NET Core Server-Side Blazor with Authentication – Eric L. Anderson blogs about server-side authentication in Blazor, an important aspect of every app.

Try out Nullable Reference Types – Phillip Carter, the Program Manager for .NET and Languages has created an excellent piece on nullable reference types, a new feature of the C# language.

3 ways to improve the EF Core performance in your .NET Core app – Performance! It’s often overlooked until the software slows to a crawl and users complain. Why not try some of these tips on performance by Sergiy Korz in your next app?

Let’s Learn Blazor: Dependency Injection – Check out this video by Brian Lagunas on DI in Blazor! Brian creates some great content, most recently, in video form.

Analyzing website memory dumps – I know, this is everyone’s favorite: analyzing memory dumps. Or maybe not. Either way, John Verbiest wrote a detailed post that you can use to make your next memory analysis session a lot less painful than it often is.

Here are some of my favorite .NET tutorials and tips posts this month. Check them out!

GC Perf Infrastructure Part 0 – Maoni Stephens on the .NET team posts a super-deep deep-dive regarding Garbage Collection performance in .NET. This is a shorter but seriously hard core post, folks! You’ll learn about things you didn’t even know you needed to know, until now.

WPF and .NET Generic Host with .NET Core 3.0 – If you haven’t ventured into why you should consider creating a generic .NET host, Laurent Kempe makes the case for doing so, and shows you how.

Goodbye Javascript! Build an Authenticated Web App in C# with Blazor + ASP.NET Core 3.0 – “Curious what the experience would be like to trade in Javascript for C# on the front end? You are about to find out!” says Heather Downing as she shows how to build a JavaScript-free front-end using Blazor.

Moving from the switch statement to switch expressions (C# 8) – Looking for a better switch statement in C#? This post by Christian Nagel highlights a new alternative.

Events, community and culture

Here are some events where you’ll find JetBrains staff:

• Techorama Netherlands
• Dotnet Days [Rachel speaking]
• DevReach [Rachel speaking]
• .NET Fest [Rachel, Mattihas speaking]
• GoTech World [Maarten speaking]

A better way to sell your skills as a developer than just “hire me!” or mass applying – At some point, we all need to look for a job or contract. So sometimes a little bit of marketing and sales knowledge goes a long way. Post by Corey Cleary.

Technology Isn’t Designed to Fit Women – Victoria Turk shows the many ways that technology products ignore half of the world’s population. I urge everyone to consider how technology just might not (and probably won’t) work for everyone you think you are building it for. You absolutely must specifically write requirements to reach your entire target audience, and must take steps to think outside of the box and reduce bias when it comes to really know what actually works for all your customers.

Random, interesting, and cool stuff

Have you ever wondered what, exactly, makes code high quality code? What do other people think high quality code is? Well, I asked just that question on various social media sites, and created a tag cloud of the most popular responses (about 200 in total). By far, readable code wins hands down, followed by code that is maintainable, reusable, testable, loosely-coupled, and consistent.

Since a good style is key for readability, I’ve created a series (still in progress) about how to style your code easily and efficiently using Rider:

• Find your perfect coding style using Rider
• Find your perfect C# style using Rider

What do you think? What makes code high quality? Comment on the JetBrains .NET blog and let us know.

And finally…

Here’s a chance to catch up on JetBrains articles and news that you might have missed:

• Async Streams – A Look at New Language Features in C# 8 – by Matthias Koch
• Developing Xamarin applications in Rider – by Maarten Balliauw
• Build HTML and JavaScript apps using Rider – by Rachel Appel
• Find your perfect C# style using Rider – by Rachel Appel
• Better Xamarin development with Rider on Mac – Maarten Balliauw hosts Dylan Berry in this Webinar

If you have any interesting or useful .NET or general programming news to share via .NET Annotated Monthly, leave a comment here or drop me a message via Twitter.

Check out this fantastic offer! CODE Magazine is offering a free subscription to JetBrains customers!

How To Implement NLog With WebAPI In Asp.Net(C#).

What is NLog?

NLog is a flexible and free logging platform for various .NET platforms, including .NET standard. NLog is easy to apply and it includes several targets (database, file, event viewer).

Which platform support it?
.NET Framework 3.5, 4, 4.5, 4.6 & 4.7
.NET Framework 4 client profile
Xamarin Android
Xamarin iOS
Windows Phone 8
Silver light 4 and 5
Mono 4

ASP.NET 4 (NLog.Web package)
ASP.NET Core (NLog.Web.AspNetCore package)
.NET Core (NLog.Extensions.Logging package)
.NET Standard 1.x - NLog 4.5
.NET Standard 2.x - NLog 4.5
UWP - NLog 4.5
There are several log levels.
Fatal : Something terrible occurred; the application is going down
Error : Something fizzled; the application might possibly proceed
Warn : Something surprising; the application will proceed
Info : Normal conduct like mail sent, client refreshed profile and so on.
Debug : For troubleshooting; the executed question, the client confirmed, session terminated
Trace : For follow troubleshooting; start technique X, end strategy X
Where we can log?
Console
Database
Email
Event Viewer Log
Files
Network
Implementation of NLog

Step 1 : Open Visual Studio. Select File > New > Project.



Step 2 : Select Web> ASP.NET Web Application > Give your project name "NLogTestApp", and then click OK.



Step 3 : Select Web API and click OK.




Step 4 : Now, we add NLog in our project. Right-click on your project solution, click on "Manage NugGet Packages". Click on the "Browse" link button and in the search box, type nlog. It will show you NLogDll as shown below. Just install this dll.

After that, when you click "Install", you will get the following window, i.e., Preview Changes.




In this popup window, click the OK button. After it is successfully installed, you will get the Nlog dll in your project reference.

Step 5 : Now, add the following code to your config file.

<configSections>
    <section name="nlog" type="NLog.Config.ConfigSectionHandler, NLog" />
</configSections>
<nlog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <targets>
        <target name="logfile" xsi:type="File" fileName="${basedir}/MyLogs/${date:format=yyyy-MM-dd}-api.log" />
        <target name="eventlog" xsi:type="EventLog" layout="${message}" log="Application" source=" My Custom Api Services" />
        <target name="database" type="Database" connectionString="Data Source=SRKSUR5085LT\SQLEXPRESS;initial catalog=NLog;user id=sa;password=OPTICAL;MultipleActiveResultSets=True;">
            <commandText> insert into ExceptionLog ([TimeStamp],[Level],Logger, [Message], UserId, Exception, StackTrace) values (@TimeStamp, @Level, @Logger, @Message, case when len(@UserID) = 0 then null else @UserId end, @Exception, @StackTrace); </commandText>
            <parameter name="@TimeStamp" layout="${date}" />
            <parameter name="@Level" layout="${level}" />
            <parameter name="@Logger" layout="${logger}" />
            <parameter name="@Message" layout="${message}" />
            <parameter name="@UserId" layout="${mdc:user_id}" />
            <parameter name="@Exception" layout="${exception}" />
            <parameter name="@StackTrace" layout="${stacktrace}" />
            <dbProvider>System.Data.SqlClient</dbProvider>
        </target>
    </targets>
    <rules>
        <!-- I am adding my 3 logging rules here -->
        <logger name="*" minlevel="Debug" writeTo="database" />
        <logger name="*" minlevel="Trace" writeTo="logfile" />
        <logger name="*" minlevel="Trace" writeTo="eventlog" />
    </rules>
</nlog>

We will targeting to log into three places.
database
txt file
Event Viewer
Your config look like below.





Step 6 : We need to create our DB Script as shown below

CREATE TABLE [dbo].[exceptionlog] 
  ( 
     [id]         [INT] IDENTITY(1, 1) NOT NULL, 
     [timestamp]  [DATETIME] NOT NULL, 
     [level]      [VARCHAR](100) NOT NULL, 
     [logger]     [VARCHAR](1000) NOT NULL, 
     [message]    [VARCHAR](3600) NOT NULL, 
     [userid]     [INT] NULL, 
     [exception]  [VARCHAR](3600) NULL, 
     [stacktrace] [VARCHAR](3600) NULL, 
     CONSTRAINT [PK_ExceptionLog] 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] 

Step 7 : Now, go to your home controller and add following code in index method.

using NLog;
using System;
using System.Web.Mvc;


namespace NLogTestApp.Controllers
{ 
    public class HomeController : Controller
    {
        private static Logger logger = LogManager.GetCurrentClassLogger();
        public ActionResult Index()
        {
            ViewBag.Title = "Home Page";
            logger.Info("Hello we have visited the Index view" + Environment.NewLine + DateTime.Now);         
            return View();
        }
    }
}

Now, run the application.


data-ad-format="auto" And open your SQL. You will see the following logs inserted in your database.




You can see this in your Event Viewer.


You can see the following in your text file.






When you are working with WEPAPI that time NLog is very useful and easy to apply.

How to convert records in a table to XML format using T-SQL

DECLARE @stSqlString NVARCHAR(MAX) = '' DECLARE @stStrXml XML SET @stSqlString = 'set @stStrXml=(select * from TableName FOR XML AUTO, elements)' EXEC sp_executesql @stSqlString, N'@stStrXml XML OUTPUT',@stStrXml OUTPUT SELECT @stStrXml

How to get list of all Folder in network sharing path using SQL Server

IF OBJECT_ID('tempdb..#TempTableName') IS NOT NULL DROP TABLE #TempTableName ; CREATE TABLE #TempTableName ( id INT IDENTITY(1, 1) , subdirectory NVARCHAR(512) , depth INT ) ; INSERT #TempTableName ( subdirectory ) EXEC MASTER.dbo.xp_cmdshell 'dir DriveName:\ /b /o:n /ad' IF NOT EXISTS ( SELECT 1 FROM #TempTableName ) BEGIN EXEC XP_CMDSHELL 'net use DriveName: \\ServerName\DriveName /user:domain\USERNAME PASSWORD'---code for Mapping drive in local Pc END

Create Window Service Setup Using .bat file

-----------------------------For Install------------------------------------------------- @ECHO OFF echo Installing WindowsService... echo --------------------------------------------------- C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\InstallUtil /i C:\RapnetService\Debug\ImportRapnetData.exe echo --------------------------------------------------- echo Service Install Done Successfully. pause ---------------------------------For Uninstall--------------------------------------------- @ECHO OFF echo Installing WindowsService... echo --------------------------------------------------- C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\InstallUtil /u C:\RapnetService\Debug\ImportRapnetData.exe echo --------------------------------------------------- echo Service UnInstall Done Successfully. pause

WCF Service Web.Config File Setting

Query For Calculate CPU Usage in SQL Server

SELECT TOP 10 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads -- The worst reads, disk reads ,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time DESC -- Find queries that have the highest average CPU usage SELECT TOP 10 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads -- The worst reads, disk reads ,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time/qs.execution_count DESC

Angular Project Demo

1) create Two Folder :- Controllers and Factory (WebApplication2 is project name) -----------------------------------------MytestConroller.js--------------------------------------------------------- angular.module('WebApplication2.Controllers').controller('MytestController', MytestController); MytestController.$inject = ['$scope', 'MyButtonClickEvent', '$http']; function MytestController($scope, MyButtonClickEvent, $http) { var vm = this; $scope.username = 'World'; $scope.sayHello = function () { //First Code //$scope.greeting = MyButtonClickEvent.BtnClick($scope.username); //MyButtonClickEvent.GetData($scope.username).then(function (result) { // $scope.greeting = result.data.aaData; //}) ///Second Code var item = { 'msgName': $scope.username } MyButtonClickEvent.GetDataClass(item).then(function (result) { debugger $scope.greeting = result.data.aaData.msgName; }) // third code //var item = []; //item.push({ msgName: $scope.username }); //MyButtonClickEvent.GetDataList(item).then(function (result) { // $scope.greeting = result.data.aaData[0].msgName; //}) }; } -----------------------------------------MytestFactory.js--------------------------------------------------------- angular.module('WebApplication2.Services').service('MyButtonClickEvent', MyButtonClickEvent); MyButtonClickEvent.$inject = ['$http']; function MyButtonClickEvent($http) { var list = {}; list.BtnClick = function (data) { return 'Hello ' + data; } list.GetData = function (data) { return $http({ url: '/Mytest/GetMsg', method: "GET", params: { MyData: data }, }); }; list.GetDataClass = function (data) { return $http({ url: '/Mytest/GetMsgClass', method: "GET", params: data }); }; list.GetDataList = function (myObject) { return $http({ url: '/Mytest/GetMsgList', method: "post", data: myObject }); }; return list; } -----------------------------------App.js-------------------------------------------------------------------- angular.module('WebApplication2', ['WebApplication2.Controllers', 'WebApplication2.Services']); angular.module('WebApplication2.Controllers', []); angular.module('WebApplication2.Services', []); -----------------------------------Index.cshtml----------------------------------------------------------

Your name:

---

{{greeting}}

-------------------------------------Controller.cs-------------------------------------------------------- using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; namespace WebApplication2.Controllers { public class MytestController : Controller { // GET: Mytest public ActionResult Index() { return View(); } [HttpGet] public JsonResult GetMsg(string MyData) { var JsonResult = Json(new { aaData = MyData }, JsonRequestBehavior.AllowGet); JsonResult.MaxJsonLength = Int32.MaxValue; return JsonResult; } [HttpGet] public JsonResult GetMsgClass(myList MyData) { var JsonResult = Json(new { aaData = MyData }, JsonRequestBehavior.AllowGet); JsonResult.MaxJsonLength = Int32.MaxValue; return JsonResult; } [HttpPost] public JsonResult GetMsgList(List MyData) { var JsonResult = Json(new { aaData = MyData }, JsonRequestBehavior.AllowGet); JsonResult.MaxJsonLength = Int32.MaxValue; return JsonResult; } } } public class myList { public string msgName { get; set; } } ----------------------------------Layout.cshtml-------------------------

File Upload in Angular 2

****************Change Event ************* onChange(event: EventTarget) { let formData = new FormData(); let eventObj: MSInputMethodContext = event; let target: HTMLInputElement = eventObj.target; let files: FileList = target.files; this.file = files[0]; let formData = new FormData(); formData.append('FileDocument', this.file); return this.http.post(`UrlPath`, formData) .map((res: Response) => { }); } ******************WEB API****************** [HttpPost] public void GetImage() { var ImageData= System.Web.HttpContext.Current.Request.Files["FileDocument"]; ImageData.SaveAs(Path); }

Input Only Number in Iput Type in Angular 2

Input Only Number in Input Type in Angular 2 -- Html Code here First Create Directive.ts File and Put Code Inside *************************onlynumber.directive.ts*************************** import { Directive, ElementRef, HostListener, Input } from '@angular/core'; @Directive({ selector: '[OnlyNumber]' }) export class OnlyNumber { constructor(private el: ElementRef) { } @Input() OnlyNumber: boolean; @HostListener('keydown', ['$event']) onKeyDown(event) { let e = event; if (e.which == 13) { e.preventDefault(); var $next = $('[tabIndex=' + (+ parseInt(e.target['tabIndex']) + 1) + ']'); if (!$next.length) { $next = $('[tabIndex=1]'); } $next.focus(); } else if (this.OnlyNumber) { if ([46, 8, 9, 27, 13, 190].indexOf(e.keyCode) !== -1 || (e.keyCode == 65 && e.ctrlKey === true) || (e.keyCode == 67 && e.ctrlKey === true) || (e.keyCode == 88 && e.ctrlKey === true) || (e.keyCode >= 35 && e.keyCode <= 39)) { return; } if ((e.shiftKey || (e.keyCode < 48 || e.keyCode > 57)) && (e.keyCode < 96 || e.keyCode > 105)) { e.preventDefault(); } } } }

Passing Data To Component in Agular2 With Using Service

-------------Create First Service--------------- import { Injectable } from '@angular/core'; import { Subject } from 'rxjs/Subject'; @Injectable() export class ProgressSpinner { public _subject = new Subject(); public event = this._subject.asObservable(); public SpinnerLoader(data: any) { this._subject.next(data); } } ------------Create Object in AppComponent-------------- import { Component } from '@angular/core'; import { alertservice, ProgressSpinner } from './shared' @Component({ selector: 'app-root', templateUrl: './app.component.html', styleUrls: ['./app.component.css'] }) export class AppComponent { title = 'app'; loading: boolean = false; constructor(public _ProgressSpinner: ProgressSpinner) { _ProgressSpinner.event.subscribe((data) => { this.loading = data }); } } ***************Second Component*************** import { Component, OnInit, ViewEncapsulation, ElementRef } from '@angular/core' import { MenuItem, Message, Shape_Master, Shape_Col } from '../../shared' import { DepartmentGroupervice, GenericClass, Pagination, ProgressSpinner } from '../../shared' import { ConfirmationService, GrowlModule } from 'primeng/primeng'; @Component({ selector: 'app-shape', templateUrl: './shape.component.html', styleUrls: ['./shape.component.css'] }) export class ShapeComponent implements OnInit { constructor( private _ProgressSpinner: ProgressSpinner) { this._ProgressSpinner.SpinnerLoader(false); } }

Not Empty Table List In Sql Server

WITH TableName AS
(
   SELECT
      SUM(row_count) AS [row_count],
      OBJECT_NAME(OBJECT_ID) AS TableName
   FROM
      sys.dm_db_partition_stats
   WHERE
      index_id = 0 OR index_id = 1
   GROUP BY
      OBJECT_ID
)

SELECT *
FROM TableName
WHERE [row_count] > 0

Get Hour And minute, Second From Second in Sql Server

DECLARE @Second1 BIGINT=5401
SET @Second1=ABS(@Second1)
DECLARE @ETIME AS VARCHAR(20)
SELECT @ETIME = RIGHT(CAST(@Second1 / 3600 AS VARCHAR(10)),7) + '.' +
RIGHT('0' + CAST((@Second1 / 60) % 60 AS VARCHAR(10)),2) + ':' + RIGHT('0' + CAST(@Second1 % 60 AS VARCHAR(2)),2)
SELECT @ETIME

Convert Minute To Hour in SQL Server function

CREATE FUNCTION [dbo].[MinToHour]
(
@Second1 BIGINT=0
)
RETURNS TIME
AS
BEGIN
DECLARE @Res TIME =NULL

SET @Second1=ABS(@Second1)
DECLARE @ETIME AS VARCHAR(20)
SELECT @ETIME = RIGHT(CAST(@Second1 / 60 AS VARCHAR(10)),7) + '.' + RIGHT('0' + CAST((@Second1) % 60 AS VARCHAR(10)),2)
SELECT @RES= CAST(REPLACE(@ETIME,'.',':') AS TIME)
RETURN @RES
END

Convert Second to Hour in Sql Server function

CREATE FUNCTION [dbo].[SecTOHour]
(
@Second1 BIGINT=0
)
RETURNS TIME
AS
BEGIN
DECLARE @Res TIME =NULL

SET @Second1=ABS(@Second1)
DECLARE @ETIME AS VARCHAR(20)
SELECT @ETIME = RIGHT(CAST(@Second1 / 3600 AS VARCHAR(10)),7) + '.' +
RIGHT('0' + CAST((@Second1 / 60) % 60 AS VARCHAR(10)),2) + ':' + RIGHT('0' + CAST(@Second1 % 60 AS VARCHAR(2)),2)
SELECT @RES= CAST(REPLACE(@ETIME,'.',':') AS TIME)
RETURN @RES
END

Rotate image in asp.net

public void FlipImage(string Filename, string FileNameNew)
 {
      System.Drawing.Image img = System.Drawing.Image.FromFile(System.Web.Hosting.HostingEnvironment.MapPath("/images/") + Filename);
      //Rotate the image in memory
      img.RotateFlip(System.Drawing.RotateFlipType.Rotate270FlipNone);
      System.IO.File.Delete(System.Web.Hosting.HostingEnvironment.MapPath("/images/" + FileNameNew));
      //save the image out to the file
      img.Save(System.Web.Hosting.HostingEnvironment.MapPath("/images/" + FileNameNew));
      //release image file
      img.Dispose();
 }

Split value using Charindex in SQL Server

Convert (varchar,Substring('11/12',0,charindex('/','11/12')))

OutPut => 11

Turning a Comma Separated string into individual rows in Sql Server

select t.ID,x.Code
    from Emp t
    cross apply (select Code from dbo.Split(t.Data,',') ) x

Solve - How to show a string in indian rupees format in C#.net?

double dblAmt=134600.00;

System.Globalization.CultureInfo info = System.Globalization.CultureInfo.GetCultureInfo("en-IN");
string StrAmt = dblAmt.ToString("N2", info);
txtAmt.Text = StrAmt;


Output :=   1,34,600.00

Find Tables With Foreign Key Constraint in SQL Server

SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

INI File Read and Write in C#.Net

1. text file Read and Write in C#.Net

public class File

    {

        private string filePath;

         

        [DllImport("kernel32")]

        private static extern long WritePrivateProfileString(string section,

        string key,

        string val,

        string filePath);

        [DllImport("kernel32")]

        private static extern int GetPrivateProfileString(string section,

        string key,

        string def,

        StringBuilder retVal,

        int size,

        string filePath);

        

        public File(string filePath)

        {

            this.filePath = filePath;

        }

        public void Write(string section, string key, string value)

        {

            WritePrivateProfileString(section, key, value.ToLower(), this.filePath);

        }

        public string Read(string section, string key)

        {

            StringBuilder SB = new StringBuilder(255);

            int i = GetPrivateProfileString(section, key, "", SB, 255, this.filePath);

            return SB.ToString();

        }

         

        public string FilePath

        {

            get { return this.filePath; }

            set { this.filePath = value; }

        }

    }

File ini = new INI("C:\\config.ini");

ini.Write("DbName", "Demo", "Demo");

//OutPut









File ini = new File("C:\\config.ini");

Console.WriteLine("The Value is:" +ini.Read("Demo", "Demo"));