How to print a print statement of sql in asp.net c# - how to call a another event in asp.net event

 using System;

using System.Data.SqlClient;

public class SqlPrintExample

{

    public static void Main(string[] args)

    {

        string connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=True";

        string sqlQuery = "PRINT 'This message comes from SQL.'; SELECT GETDATE() AS CurrentDateTime;";

        using (SqlConnection connection = new SqlConnection(connectionString))

        {

            // Attach an event handler to capture InfoMessage events (including PRINT statements)

            connection.InfoMessage += Connection_InfoMessage;

or// conn.InfoMessage += new SqlInfoMessageEventHandler( Connection_InfoMessage);

            try

            {

                connection.Open();

                using (SqlCommand command = new SqlCommand(sqlQuery, connection))

                {

                    using (SqlDataReader reader = command.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            Console.WriteLine($"Current Date Time from SQL: {reader["CurrentDateTime"]}");

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                Console.WriteLine($"Error: {ex.Message}");

            }

        }

    }

    private static void Connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)

    {

        // Display the message from the SQL PRINT statement

        Console.WriteLine($"SQL InfoMessage: {e.Message}");

    }

}

do while loop in sql server - Cursor in Sql Server

For data looping in sql Server, we use While loop  or cursor

 DECLARE @cnt INT = 0;

WHILE @cnt < 10

BEGIN

   PRINT 'Inside simulated FOR LOOP on TechOnTheNet.com';

   SET @cnt = @cnt + 1;

END;

PRINT 'Done simulated FOR LOOP on TechOnTheNet.com';

GO

For cursor:-

https://www.c-sharpcorner.com/blogs/how-to-use-cursors-and-while-loop-in-sql-server

bulk insert with single insert query in sql

 INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

A network-related or instance-specific error occurred while establishing a connection to SQL Server. Error

>First open services.msc from wondow + r (Window Services)

> Then right click on sql server (SQL express)

>go to Properties. 

Then go to Log On tab and select Local System account. 

Click on Ok button and start the service.

Restore of database failed sql server

>Open sql server

>Right Click on database >click on task>restore>database

>From device >select database bak file

>In option tab > Check overwrite the existing database (with replace)

or > in file tab click on reallocate all files to folder

How to create unique key in SQL server management studio

 >First Open Visual Studio

>Right Click on table and click on Design

>Right Click on column name left side

>Click on Indexes/Key

>Select Unique key in 'Type' Filed

Note:- Sometime unique key option is not visible

How to write query with query builder in SQL server

 > Open Sql server management studio

> Right click on editor and click on "Design query in editor"

> And right click in that to change insert update or delete query.

How to create a new table with select query in SQL - INSERT RECORD FROM ONE TABLE TO ANOTHER - COPY TABLE

 SELECT   [Date]

      ,[SchemeNo] ,[Commission]

INTO        test3

FROM     PolicyDetails

Pivot function in SQL Server

 https://www.selectdistinct.co.uk/2023/05/22/pivot-function-in-sql/

CRUD Operation in SQL

 https://www.geeksforgeeks.org/sql-server-crud-operations/

Disable Underline errors in SQL Server

https://www.linkedin.com/pulse/tips-tricks-disabling-spell-checker-tool-microsoft-sql-matt-monfils

How to insert date in SQL Server

 insert into emp values('20240625')

'YYYYMMDD'

Convert Date time to Date in SQL

 replace(convert(varchar, PE.PEDate, 106),' ','-')

SQL Server commands - To find columns from Table

 sp_help :- To get information about table and stored procedure

sp_help  'tablename'

 to get column name etc

sp_helptext  'storeprocedurename' 

will display stored procedure text 

To get Columns of table

SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('tablename') 

Find table and views in database

SELECT * FROM INFORMATION_SCHEMA.TABLES 

SELECT * FROM INFORMATION_SCHEMA.VIEWS

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS  

EXEC sp_columns 'Employees'

ref:- https://stackoverflow.com/questions/578454/useful-system-stored-procedures-in-sql-server

How to join two tables without join and output as XML - SQL Server

 SELECT

    (SELECT d.DtFinalJudOrder FROM CourtApplicationDisposals d FOR XML PATH('CourtApplicationDisposalsList'), TYPE) AS CourtApplicationDisposals,

    (SELECT s.DtOfStay FROM CourtApplicationStayDetails s FOR XML PATH('CourtApplicationStayDetailsList'), TYPE) AS CourtApplicationStayDetails

FOR XML PATH(''), ROOT('root')

For single table with root

===========

SELECT  Doc.DocumentName, Doc.Subject, Doc.DocumentFilePath

FROM Document Doc

where Doc.ObjectType = 337 AND Doc.IsActive = 1

FOR XML RAW('DocumentList'),ELEMENTS,ROOT('Document')

Rename column in SQL Server table and change data type in SQL

 https://www.tutorialsteacher.com/sql/sql-modify-column-datatype-size

Union vs union all SQL server - Union SQL

Union will remove duplicates. Union All does not.

Note:- For union , must have same columns.

example

Union

Select top (select COUNT(*) from CourtApplicationDetails) * From CourtApplicationDetails

union 

Select top ((select COUNT(*) from CourtApplicationDetails)-(1)) * From CourtApplicationDetails

===============================

union all

Select top (select COUNT(*) from CourtApplicationDetails) * From CourtApplicationDetails

union all

Select top ((select COUNT(*) from CourtApplicationDetails)-(1)) * From CourtApplicationDetails

Except Keyword SQL Server - SQl Server Except

 Select top (select COUNT(*) from CourtApplicationDetails) * From CourtApplicationDetails

EXCEPT

Select top ((select COUNT(*) from CourtApplicationDetails)-(1)) * From CourtApplicationDetails

ref:- https://github.com/jaygajera17/Tour_Management_Asp.Net/blob/main/DotNetFrameworkProject_CE040_CE087/Tour_Management/usercrud.aspx

SQL Server Reporting Services

 > First install SQL Server

> Download and Install SQL Server Reporting Services

> Download and Install Microsoft Report Builder.

> When you install both

>Open "Report Server Configuration Manager"

>Than click on Desktop -LUDN019/SSRS >Click on Start

>Click on "Service Account"> Click on Use built in account and Virtual service account in (droptdown) and click on apply

>Click on "Webservice URL " > Enter Virtual Directory name to ReportServer and click on Apply

>Click on "Database"> Click on change database> Click on "choose an existing report database"> Click on next>Type SQL Server name "DESKTOP-LUDN019\sqlexpress">Select Authentication type to SQL Server Account> Enter User name and password> Click on test connection and click on Next> Select Report Server Database to Reportserver(dropdown)> Click on next> Click on Authentication Type and select SQL Server Credentials>Enter Username and Password> CLick on next> Click on next and finish

>Click on "Database"> Click on change credentials>Select Authentication Type to SQl server account> Enter User name and password.> Click on next>Enter Authentication Type to SQL Server Credentials> Enter User Name and password>Click on Next , Next and Finish.

> Click on "Web Portal URL"> Enter Virtual Directory to Reports

>Note:- Click on "Encryption keys"> Click on Delete the encryption keys.

> Click on "Web portal URL " and Click URLs "http://desktop-ludn019/Reports/"

>When Open URL in browser > Reports will be display and at last "Data Sources"

> Click on "Data Sources"> Click on "three dots" link and click on manage

> Check the checkbox "Enable this data source"

>Click on Type Under Connection> Select "Microsoft SQL Server" in drop down

>In the connection string type "Data Source=DESKTOP-LUDN019\sqlexpress;Initial Catalog=SBI; user id=sa;password=LIC@123" without quotes

> In the radiobox > click "using the following credentials " under "credentials"> Select the "Database Username and password in type of credentials" > Enter Username and Password and click on Test connection and Apply

references: https://www.sqlshack.com/sql-server-reporting-service-configuration-manager/

and how to add reporting service project to visual studio

https://learn.microsoft.com/en-us/sql/reporting-services/tutorial-step-01-create-report-server-project-reporting-services?view=sql-server-ver17

https://www.mssqltips.com/sqlservertip/7506/add-report-server-project-to-visual-studio-solution/

https://www.mssqltips.com/sqlservertip/6555/sql-server-reporting-services-reusable-code-blocks/

https://www.mssqltips.com/sqlservertip/7506/add-report-server-project-to-visual-studio-solution/

https://www.c-sharpcorner.com/article/sql-create-a-basic-table-report-ssrs-tutorial/

SQL Server find current year - Previous Year

  SELECT        TimePeriod

          FROM            (SELECT        YEAR(GETDATE()) AS TimePeriod

          UNION

          SELECT        YEAR(GETDATE()) - 1 AS Expr1

          UNION

          SELECT        YEAR(GETDATE()) - 2 AS Expr1

          UNION

          SELECT        YEAR(GETDATE()) - 3 AS Expr1

          UNION

          SELECT        YEAR(GETDATE()) - 4 AS Expr1) AS t

          ORDER BY TimePeriod DESC