Upload image in folder with parameter using MVC with c#

Description : in this post how to upload image in folder. in this code use form data in jquery for upload image in folder front end html side use jquery code for upload image and c# code side create method that return result in json for successfully upload image

add file upload html control in your html

<input type="file" style="visibility: hidden;height:0"

 id="Logo" name="Logo" accept=".png,.jpg,.jpeg,.gif,.tif" />

ajax call for upload image with parameter

function Save()
{
 if (window.FormData !== undefined)
 {
  var fileUpload = $("#Logo").get(0);
  var files = fileUpload.files;

  // Create FormData object
  var fileData = new FormData();

  // Looping over all files and add it to FormData object
  for (var i = 0; i < files.length; i++)
  {
   fileData.append(files[i].name, files[i]);
  }

  // Adding one more key to FormData object
  fileData.append('Parameter1', $("#Parameter1").val());

  $.ajax({
   url: '/YourControllerName/YourMethodName',
   type: "POST",
   contentType: false, // Not to set any content header
   processData: false, // Not to process data
   data: fileData,
   success: function (result)
   {
    alert(result);
   },
   error: function (err)
   {
    alert(err.statusText);
   }
  });
 }
 else
 {
  alert('FormData is not supported.');
 }
}

below code for get image in .cs file side and proccess image like save image in folder and save record in database

public JsonResult Save()
{
    string Parameter1 = Request.Form["Parameter1"].ToString()

    if (Request.Files.Count > 0)
    {
        try
        {
            //  Get all files from Request object 
            HttpFileCollectionBase files = Request.Files;
            for (int i = 0; i < files.Count; i++)
            {
                HttpPostedFileBase file = files[i];
                string FileName;

                // Checking for Internet Explorer 
                if (Request.Browser.Browser.ToUpper() == "IE"

                || Request.Browser.Browser.ToUpper() == "INTERNETEXPLORER")
                {
                    string[] testfiles = file.FileName.Split(new char[] { '\\' });
                    FileName = testfiles[testfiles.Length - 1];
                }
                else
                {
                    FileName = file.FileName;
                }

                FileInfo fInfo = new FileInfo(FileName);

                FileName = Guid.NewGuid().ToString() + fInfo.Extension;
               
                // Get the complete folder path and store the file inside it. 
                FileName = Path.Combine(Server.MapPath("~/YourImageFolderSavePath/"), FileName);
                file.SaveAs(FileName);
            }
        }
        catch (Exception ex)
        {
            return Json("Error occurred. Error details: " + ex.Message);
        }
    }

    // WRITE YOUR SAVE RECORD CODE HERE
   
    return Json("Image upload successfully.", JsonRequestBehavior.AllowGet);
}

Converting DateTime to Ticks or Ticks to DateTime using SQL Server function

Description : In this function how to convert a datetime to ticks in sql function and ticks to DateTime

Below function for Convert DateTime to Ticks

CREATE FUNCTION dbo.ConvertDateTimeToTicks (@DateTime datetime)
  RETURNS bigint
AS
BEGIN
    RETURN DATEDIFF_BIG( microsecond, '00010101', @DateTime ) * 10 +
    (DATEPART( NANOSECOND, @DateTime ) % 1000) / 100;
END

Call function

SELECT dbo.ConvertDateTimeToTicks('2018-02-26 14:05:24.250')

Result : 636552507242500000

Below function for convert ticks to datetime

CREATE FUNCTION dbo.ConvertTicksToDateTime ( @Ticks bigint )
  RETURNS datetime
AS
BEGIN
    DECLARE @DateTime datetime2 = '00010101';
    SET @DateTime = DATEADD( DAY, @Ticks / 864000000000, @DateTime );
    SET @DateTime = DATEADD( SECOND, ( @Ticks % 864000000000) / 10000000, @DateTime );
    RETURN DATEADD( NANOSECOND, ( @Ticks % 10000000 ) * 100, @DateTime );
END

SELECT dbo.ConvertDateTimeToTicks(636552507242500000)

Result : 2018-02-26 14:05:24.2500000

Datetime convert in SQL Server

Description : In this post how to convert date in different formats in sql server

SELECT CONVERT(VARCHAR, GETDATE(), 100)
Result : MON DD YYYY HH:MMAM

------------------------------------------
SELECT CONVERT(VARCHAR, GETDATE(), 101)
Result : MM/DD/YYYY – 10/02/2008                 
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 102)
Result : YYYY.MM.DD – 2008.10.02          
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 103)
Result : DD/MM/YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 104)
Result : DD.MM.YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 105)
Result : DD-MM-YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 106)
Result : DD MON YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 107)
Result : MON DD, YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 108)
Result : HH:MM:SS
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 109)
Result : MON DD YYYY HH:MM:SS:MMMAM (OR PM)
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 110)
Result : MM-DD-YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 111)
Result : YYYY/MM/DD
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 112)
Result : YYYYMMDD
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 113)
Result : DD MON YYYY HH:MM:SS:MMM
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 114)
Result : HH:MM:SS:MMM(24H)
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 120)
Result : YYYY-MM-DD HH:MM:SS(24H)
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 121)
Result : YYYY-MM-DD HH:MM:SS.MMM
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 126)
Result : YYYY-MM-DDTHH:MM:SS.MMM

Format query result as json using FOR JSON and xml using FOR XML

Description : in this post sql query result return in json or xml by adding FOR JSON PATH or FOR XML PATH if need query result in json than add at last FOR JSON PATH and need result in XML than add at last FOR XML PATH and the result return in json or xml

JSON Return query syntex

SELECT TOP 3 sta_StateName AS StateName, sta_StateShortName AS ShortName FROM StateMaster FOR JSON PATH

JSON Result

[{"StateName":"Montana","ShortName":"MT"},{"StateName":"Maryland","ShortName":"MD"},{"StateName":"Tennessee","ShortName":"TN"}]

XML Return query syntex

SELECT TOP 3 sta_StateName AS StateName, sta_StateShortName AS ShortName FROM StateMaster FOR XML PATH

XML Result

<row><StateName>Montana</StateName><ShortName>MT</ShortName></row><row><StateName>Maryland</StateName><ShortName>MD</ShortName></row><row><StateName>Tennessee</StateName><ShortName>TN</ShortName></row>

How to use cursor in SQL

Description : In this post learn about Cursor. Cursor is difficult concept in SQL, cursor let you create a lop in your SQL procedure you can fatch data record by record. cursor stored data set that go through each record and than prform some kind of logic in record field

- Here is simple example of print state name and its short name

-- First declare State name and short name variables
DECLARE @sta_StateName Varchar(50), @sta_StateShortName Varchar(50)

-- Declare cursor name is state cursor that fatch 5 rows from State Master table
DECLARE StateCursor CURSOR FOR SELECT TOP 5 sta_StateName, sta_StateShortName FROM StateMaster

-- open the cursor
OPEN StateCursor;

-- here fetch 1st row from cursor
FETCH NEXT FROM StateCursor INTO @sta_StateName, @sta_StateShortName

-- while loop
WHILE @@FETCH_STATUS = 0
BEGIN
   
    -- print fetched state name
    PRINT @sta_StateName + ' ['+@sta_StateShortName+']'

    -- again fetch next row from cursor
    FETCH NEXT FROM StateCursor INTO @sta_StateName, @sta_StateShortName
END;

-- close the cursor
CLOSE StateCursor;

-- deallocate cursor
DEALLOCATE StateCursor;

How to send Twillo SMS from Azure function

Description : in this post how to send Text SMS from twillo using azure function C# script code

Step 1 : "Twilio": "5.9.0" Add this library in your function project.json file of azure function

Step 2 : #r "Twilio.Api" for import twillo library add this line in your run.csx file of azure function

Step 3 : using Twilio; add this for use twillo code

Step 4 :

string accountSid = "YourAccountSID";
string authToken = "YourAccountAuth";

Step 5 : Create class for return response

public class OutResponse
{
    public string ID {get;set;}
    public string Status {get;set;}
    public string MobileNo {get;set;}
    public string DBID {get;set;}
}

Step 6 : Twillo SMS Send Code

OutResponse mytempList = new OutResponse();

var client = new TwilioRestClient(accountSid, authToken);
     
var message=client.SendMessage(
    FromNo "Get From Twillo Panel",
    myQueueItem.SendToMobileNo,
    myQueueItem.SMSText
);

mytempList.ID = message.Sid;
mytempList.Status = message.Status;
mytempList.MobileNo = message.To;
mytempList.DBID = myQueueItem.EmailBody;

How to create Insert or Update query from select statement

Description : In this post how to create a insert or update query using select query in sql server

- Insert Query
SELECT 'INSERT INTO AspNetRoles VALUES ('''+CONVERT(NVARCHAR(50),Id)+''', '''+Name+''')' FROM AspNetRoles

- Update Query
SELECT 'UPDATE AspNetRoles SET Name = '+Name+' WHERE ID = '+Id+'' FROM AspNetRoles

How to upload file in azure blob storage explorer

Step 1 : Download NugetPackage "WindowsAzure.Storage"

Step 2 : Below code for upload file

// Retrieve storage account from connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(Your Azure connection string);

// Create the blob client.
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

// Retrieve reference to a previously created container.
CloudBlobContainer container = blobClient.GetContainerReference(Report Upload Blob Name);

// Retrieve reference to a previously created container.
var directort = container.GetDirectoryReference(Upload Directory Name);

// Retrieve reference to a blob named "myblob".
CloudBlockBlob blockBlob = directort.GetBlockBlobReference(Upload File Name);

// Create or overwrite the "myblob" blob with contents from a local file.
using (var fileStream = System.IO.File.OpenRead(Your local folder file path))
{
    blockBlob.UploadFromStream(fileStream);
}

Send mail using office365 email id

Description : In this post how send mail using office 365 email credential and smtp below is c# code for send mail

string EmailSender = "YourEmailID";
string EmailPassword = "YourPassword";

var client = new SmtpClient("smtp.office365.com", 587);
client.EnableSsl = true;
client.Timeout = 300000;
client.UseDefaultCredentials = false;
client.Credentials = new System.Net.NetworkCredential(EmailSender, EmailPassword);
client.DeliveryMethod = SmtpDeliveryMethod.Network;
var from = new MailAddress("FromEmailID", $"DisplayName", System.Text.Encoding.UTF8);
var message = new MailMessage { From = @from };

message.Subject = Subject;
message.To.Add(ToEmailID);
message.IsBodyHtml = true;
message.Body = myQueueItem.EmailBody
message.BodyEncoding = Encoding.UTF8;
message.SubjectEncoding = Encoding.UTF8;
client.Send(message);
message.Dispose();

Download image or file from Azure Blob Storage using c#

Step 1 : Download NugetPackage "WindowsAzure.Storage"

Step 2 : Below code for download image or file

string storageConnectionString = "YourStorageConnectionString";

// get storage account from connection string
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(storageConnectionString);

// Create the blob client.
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

// Retrieve reference to a previously created container.
CloudBlobContainer container = blobClient.GetContainerReference("YourBlobName created in azure storage");

// Retrieve reference to a previously created container.
var directort = container.GetDirectoryReference(Directory Name if create in blob storage);

// Retrieve reference to a blob named "myblob".
CloudBlockBlob blockBlob = directort.GetBlockBlobReference(Your file name);

if (blockBlob.Exists())
{
    // Create or overwrite the "myblob" blob with contents from a local file.
    using (var fileStream = System.IO.File.Create(File save path))
    {
        blockBlob.DownloadToStream(fileStream);
    }
}

How to create excel file from data table using NPOI in c# asp.net or MVC

Step 1 : Download or Install "NPOI" Nuget from package manager & "Newtonsoft" Nuget

Step 2 : Get Data in your datatable like

        DataTable dt1 = new DataTable();

            dt1.Columns.Add("ID");
            dt1.Columns.Add("Name");

            DataRow dr = dt.NewRow();
            dr["ID"] = "1";
            dr["Name"] = "Test";

            dt.Rows.Add(dr);

Step 3 : Create class

public class SummaryClass
{
   public string ID { get; set; }
   public string Name { get; set; }
}

Step 3 :

using NPOI.HSSF.UserModel;
using Newtonsoft.Json;

// Create Work Book
var workbook = new HSSFWorkbook();

// Add name of sheet
var sheet = workbook.CreateSheet("Daily Summary");

// convert your datatable to list

string JSON = JsonConvert.SerializeObject(dt);
var items = JsonConvert.DeserializeObject<List<SummaryClass>>(JSON);

// Create column & header string array
var columns = new[] { "ID", "Name" }; // Your DataTable Fields Name
var headers = new[] { "Sr. No.", "Client Name" }; // Header display name in excel

// Create row in excel sheet
var headerRow = sheet.CreateRow(0);

//create header
for (int i = 0; i < columns.Length; i++)
{
    var cell = headerRow.CreateCell(i);
    cell.SetCellValue(headers[i]);
}

//fill content
for (int i = 0; i < items.Count; i++)
{
    var rowIndex = i + 1;
    var row = sheet.CreateRow(rowIndex);

    for (int j = 0; j < columns.Length; j++)
    {
        var cell = row.CreateCell(j);
        var o = items[i];
        cell.SetCellValue(o.GetType().GetProperty(columns[j]).GetValue(o, null).ToString());
    }
}

// Store data in memory strem
var stream = new MemoryStream();
workbook.Write(stream);

//write to file
FileStream file = new FileStream("Your File Save Path", FileMode.CreateNew, FileAccess.Write);
stream.WriteTo(file);
file.Close();
stream.Close();

How to delete files from directory using c# MVC

Description : In this article how to remove multiple files remove from 1 single directory using foreach loop in MVC using c#

string FilePath = HttpContext.Server.MapPath("~/YourMainDirectoryName/");

// First check if directory exists or not
if (!Directory.Exists(FilePath))
{
 string[] files = Directory.GetFiles(FilePath);

 if (files.Length > 0)
 {
  foreach (string filePaths in files)
                      System.IO.File.Delete(filePaths);
 }
}

// at the end of loop if you want to delete main directory write below line
Directory.Delete(FileSavePath);

// again if you want to create same blank directory write below line
Directory.CreateDirectory(FileSavePath);

How to generate random 10 digit number using sql

Description : In this post how to create 10 digits random number from sql select query, use this type query in generate Order Number or Client Unique number

SELECT RIGHT('000000' + CAST(ABS(CHECKSUM(NEWID())) % 9999999999 AS varchar(10)), 10)

Result : 0027125182

How to mearge multiple PDF files with page number using PDFSharp using C# MVC

Description : In this article how to mearge multiple PDF file using PDFSharp dll in c# MVC

Step 1 : first install this package from nuget "Install-Package PdfSharp -Version 1.32.3057"

string DirectoryPath = HttpContext.Server.MapPath("~/YourDirectoryName/");

// Get all PDF Files
string[] pdfs = Directory.GetFiles(DirectoryPath);

// Pass all PDF files in function
MergeMultiplePDFIntoSinglePDF(DirectoryPath + "YourMeargeFileName.pdf", pdfs);

// Add below function for generate single pdf file
private void MergeMultiplePDFIntoSinglePDF(string outputFilePath, string[] pdfFiles)
{
 PdfDocument document = new PdfDocument();

 foreach (string pdfFile in pdfFiles)
 {
  PdfDocument inputPDFDocument =

  PdfReader.Open(pdfFile, PdfDocumentOpenMode.Import);



  document.Version = inputPDFDocument.Version;

  foreach (PdfPage page in inputPDFDocument.Pages)
  {
     document.AddPage(page);
  }
 }

 XFont font = new XFont("Verdana", 9);
 XBrush brush = XBrushes.Black;

 string noPages = document.Pages.Count.ToString();

 for (int i = 0; i < document.Pages.Count; ++i)
 {
  PdfPage page = document.Pages[i];

  // Make a layout rectangle.
  XRect layoutRectangle = new

  XRect(240/*X*/, page.Height - font.Height - 10/*Y*/

  , page.Width/*Width*/, font.Height/*Height*/);

  using (XGraphics gfx = XGraphics.FromPdfPage(page))
  {
     gfx.DrawString("Page " + (i + 1).ToString() + " of " +

     noPages, font, brush, layoutRectangle, XStringFormats.Center);
  }
 }

 document.Options.CompressContentStreams = true;
 document.Options.NoCompression = false;
 document.Save(outputFilePath);
}

How to create Roman Numerals function in SQL

Description : In this post how to generate roman numerals return function in sql

CREATE Function [dbo].[GetRomanNo] ( @N as varchar(20) )
RETURNS VARCHAR(100)
AS
BEGIN
  DECLARE @s varchar(100), @r varchar(100), @i bigint, @p int, @d bigint
  SET @s = ''
  SET @r = 'IVXLCDM' -- Roman Symbols

  /* There is no roman symbol for 0, but I don't want to return an empty string */
 IF @n=0
  SET @s = '0'
 ELSE
 BEGIN
  SELECT @p = 1, @i = ABS(@n)
  WHILE(@p<=5)
  BEGIN
   SET @d = @i % 10
   SET @i = @i / 10
   SELECT @s = (CASE WHEN @d IN (0,1,2,3) THEN Replicate(SubString(@r,@p,1),@d) + @s
        WHEN @d IN (4) THEN SubString(@r,@p,2) + @s
        WHEN @d IN (5,6,7,8) THEN SubString(@r,@p+1,1) + Replicate(SubString(@r,@p,1),@d-5) + @s
        WHEN @d IN (9) THEN SubString(@r,@p,1) + SubString(@r,@p+2,1) + @s END)
   SET @p = @p + 2
  END

  SET @s = Replicate('M',@i) + @s

  IF @n < 0
   SET @s = '-' + @s
  END

 RETURN @s
END

How to use if / else condition in sql server

Description : in this post display color code name when pass int value in @ColorCode variable if pass 2 in @ColorCode = 2 then it check condition and result will return "GREEN" bacause color code = 2 is green

DECLARE @ColorCode Int

SET @ColorCode = 2

IF (@ColorCode = 1)
BEGIN
 SELECT 'RED' AS ColorName
END
IF (@ColorCode = 2)
BEGIN
 SELECT 'GREEN' AS ColorName
END
IF (@ColorCode = 3)
BEGIN
 SELECT 'BLUE' AS ColorName
END

SQL DataType length

Description : in this post sql datatype store value in database and c# side

1. BigInt
    From C# side it stores Int64 Value
    MaxValue = 9223372036854775807
    MinValue = -9223372036854775808

2. Bit
    C# : Store Bool Value Like True or False

3. DateTime
    C# : Store Date Time

4. Float
    C# : Store Float Value
    MaxValue = 3.40282e+038f
    MinValue = -3.40282e+038f

5. Int
    C# : Store Int32 Value
    MaxValue = 2147483647
    MinValue = -2147483648

6. Numeric(18,0)
    SQL store value like 18 digits with 2 or more decimal place
    e.g. 12.00

7. Nvarchar(50)
    SQL store Text & unique code value with limited size

8. Nvarchar(MAX)
    SQL store Text & unique code value no limit for store data

9. Varchar(50)
    SQL store only text Varchar datatype do not support unique code value

10. Varchar(MAX)
    SQL store only text Varchar datatype do not support unique code value

11. Uniqueidentifier
    SQL store Numeric, Characters, and - with the lenth of 36 characters
    Uniqueidentifier do not repeat it generate unique value every time
    C# : store GUID value

Using a temporary table in dynamic sql query in a stored procedure

Description : in this post how to use temporary table in dynamic sql query first add city table data in #tmpCity and use in dynamic sql insert query

Declare @Sql Nvarchar(max),@Sql1 Nvarchar(max)

CREATE TABLE #tmpCity (CityIDP BigInt, CityName Varchar(50))

SET @Sql = 'INSERT INTO #tmpCity SELECT CityIDP, CityName  FROM mCity'
EXEC(@Sql)
SELECT @@ROWCOUNT
SET @Sql1 = 'SELECT * FROM #tmpCity'
EXEC(@Sql1)
GO
DROP TABLE #tmpCity

Introduction of ASP

ASP is a framework for develop web pages

Development tools for ASP

- Classic ASP

- ASP.NET Web Forms

- ASP.NET MVC

- ASP.NET Web Pages

- ASP.NET API

- ASP.NET Core

Description about ASP

- Classic ASP introduce in 1998 as first script langualge of Microsoft. classic asp file extension is .asp written in VB

Description about ASP.NET

- ASP.NET released in 2002 and file extension of ASP.NET is aspx written in C# and ASP.NET 4.7 is latest version of ASP.NET

Description about ASP.NET Web Pages

- ASP.NET Web Pages is and single page application similar to PHP and ASP.NET Web Pages mearged in ASP.NET Core

Description about ASP.NET MVC

- ASP.NET MVC s Model - View - Controller and this is also mearge in ASP.NET Core

Description about ASP.NET API

- ASP.NET API is Application Programming Interface and this is also mearge in ASP.NET Core

Description about ASP.NET Web Forms

- ASP.NET Web Forms is event driven application model and this is not a part of ASP.NET Core

Description about ASP.NET Core

- ASP.NET core release in 2016 and Core mearge ASP.NET, MVC, Web API and ASP.NET Web Pages

Search Text in all Procedure, View, Function, Table of sql server

Description : Below are queries for search text from all sql server procedure, table, view and function in single database

-- SEARCH TEXT IN ALL PROCEDURE OF SQL SERVER
SELECT OBJECT_NAME(OBJECT_ID) SP_Name, OBJECT_DEFINITION(OBJECT_ID) SP_Definition FROM sys.procedures
WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+'SEARCH TEXT'+'%'
   

-- SEARCH TEXT IN ALL VIEW OF SQL SERVER
SELECT OBJECT_NAME(OBJECT_ID) View_Name
,OBJECT_DEFINITION(OBJECT_ID) View_Definition
FROM   sys.views
WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+'SEARCH TEXT'+'%'

-- SEARCH TEXT IN ALL FUNCTION OF SQL SERVER
SELECT ROUTINE_NAME           Function_Name
,ROUTINE_DEFINITION     Function_definition
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_DEFINITION LIKE '%'+'SEARCH TEXT'+'%'
AND ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME

-- SEARCH TEXT IN ALL TABLE OF SQL SERVER
SELECT t.name      AS Table_Name
,c.name      AS COLUMN_NAME
FROM   sys.tables  AS t
INNER JOIN sys.columns c
ON  t.OBJECT_ID = c.OBJECT_ID
WHERE  c.name LIKE '%'+'SEARCH TEXT'+'%'
ORDER BY
Table_Name

SQL DATALENGTH() Function

Return length of text given in function

Example

SELECT DATALENGTH('Text');

RESULT : 4

SQL CONCAT() Function

Join two or more string togathet

Example

SELECT CONCAT('Text1', '-2');

RESULT : Text1-2

SQL CHARINDEX() Function

Return character index of text

Example

SELECT CHARINDEX('x', 'Text')

RESULT : 3

SQL CHAR() Function

Return character given in function

Example

SELECT CHAR(97)

RESULT : a

SQL ASCII() Function

Return ascii value of given in function

Example

SELECT ASCII('a')

RESULT : 97

How to use SQL ALTER Table

- ALTER TABLE ADD COLUMN

Syntax

ALTER TABLE TableName ADD Column ColumnName DataType

Example

ALTER TABLE CityMaster ADD IsActibe Bit

- ALTER TABLE DROP COLUMN

Syntax

ALTER TABLE TableName DROP COLUMN ColumnName

Example

ALTER TABLE CityMaster DROP IsActive

How to use SQL DROP DATABASE

DROP DATABASE is use for delete existing SQL database

Syntax

DROP DATABASE databasename

Example

DROP DATABASE dbTest

How to use SQL CREATE DATABASE

CREATE DATABASE is use for create new database in SQL

Syntax

CREATE DATABASE databasename

Example

CREATE DATABASE dbTest

SQL Comments Example

SQL Comments Example

1. Single line comment

-- SELECT ALL CITY RECORD
SELECT * FROM CityMaster

2. Multi line comment

/*
    Return all city
    records from city mater
*/
SELECT * FROM CityMaster

How to use SQL EXISTS operator

EXISTS operator is use for check record exists or not in table and EXISTS operator return true if table contains more than one record

EXISTS Syntax

SELECT ColName FROM TableName WHERE EXISTS (SELECT ColName FROM TableName)

Example

1. IF EXISTS (SELECT TOP 1 CityName FROM CityMaster WHERE CityName = 'TEST')

How to assign grade student mark wise in SQL

Description : In this post how to apply a grade in student table using case when condition

Step 1 : Write below query from update rank in student table

UPDATE tblStudent SET tblStudent.Grade = CASE
    WHEN Mark BETWEEN 0 AND 100 THEN 'C'
    WHEN Mark BETWEEN 101 AND 120 THEN 'B'
    WHEN Mark BETWEEN 121 AND 150 THEN 'A'
    ELSE 'A+' END
FROM tblStudent AS St

Step 2 : Select student table for check result

SELECT * FROM tblstudent

Result is

Student1        100    C
Student2        120    B
Student3        100    C
Student4    150    A
Student5    150    A
Student6    160    A+
Student7    135    A
Student8    129    A
Student9    155    A+

How to use SQL HAVING

HAVING is use with GROUP BY because WHERE condition not use with query

HAVING Syntax

SELECT ColName
FROM TableName
WHERE ColName
GROUP BY ColName
HAVING Condition
ORDER BY ColName

Example

SELECT COUNT(EmpID) FROM EmployeeMaster
GROUP BY Country
HAVING COUNT(EmpID) > 1

above query return number of employee in each country (only add countries with more than 1 employee)

How to use SQL GROUP BY

GROUP BY statement is use with SQL Aggregate function like COUNT, MIN, MAX, AVG, SUM for get result in one or more column or row

GROUP BY Syntax

SELECT ColName
FROM TableName
WHERE Condition
GROUP BY ColName
ORDER BY ColName

Example

1. SELECT COUNT(CountryName) FROM City GROUP BY CountryName

2. SELECT COUNT(CountryName) FROM City GROUP BY CountryName ORDER BY COUNT(CountryName)

How to use SQL UNION

UNION is use for join multiple SELECT query in one result

Note :

1. Each column match with Each select query column

2. Each column datatype match with Each select query column

3. Each column order match with each select query

UNION Syntax

SELECT YourColumnName FROM YourTableName
UNION
SELECT YourColumnName FROM YourTableName

Example

SELECT CityName FROM Employee
UNION
SELECT CityName FROM Manager
ORDER BY CityName

How to use SELF JOIN in SQL Query

SELF JOIN Syntax

SELECT YourColumnName FROM YourTableName T1, SelfJoinTableName T2 WHERE Condition

Example

SELECT t1.CityName, t2.CountryName FROM City t1, Country t2
WHERE t1.CountryName = t2.CountryName
ORDER BY t2.CountryName

How to use RIGHT OUTER JOIN in SQL Query

RIGHT OUTER JOIN Syntax

SELECT YourColumnName FROM YourTableName
RIGHT OUTER JOIN InnerTableName ON InnerTableName.Column = YourTableName.Column

Example

SELECT * FROM City
RIGHT JOIN Country ON Country.CountryID = City.CountryIDF

OR

SELECT * FROM City
RIGHT OUTER JOIN Country ON Country.CountryID = City.CountryIDF

How to use LEFT OUTER JOIN in SQL Query

LEFT OUTER JOIN Syntax

SELECT YourColumnName FROM YourTableName
LEFT OUTER JOIN InnerTableName ON InnerTableName.Column = YourTableName.Column

Example

SELECT * FROM City
LEFT JOIN Country ON Country.CountryID = City.CountryIDF

OR

SELECT * FROM City
LEFT OUTER JOIN Country ON Country.CountryID = City.CountryIDF

How to use SUM() function in SQL Query

SUM() function return total sum of numeric, int, float, bigint value

SUM() Syntax

SELECT SUM(YourNumericValueColumn) WHERE YourTableName

Example

SELECT SUM(Salary) FROM Employee

How to user AVG() function in SQL Query

AVG() function return average value from table

AVG() Syntax

SELECT AVG(YourColumnName) FROM YourTableName

Example

SELECT AVG(Salary) FROM Employee

SQL DELETE Query

DELETE query is delete all existing record from table with condition or without condition

DELETE Syntax

DELETE FROM YourTableName WHERE Condition

Example

1. Delete specific record form table
DELETE FROM City WHERE CityID = 1

1. Delete all record from table
DELETE FROM City

SQL UPDATE Query

UPDATE Statement is use for change values in existing record

UPDATE Syntax

UPDATE YourTableName SET Column1 = Value1 WHERE Condition for update record

Example

1. Single Record Update
- UPDATE City SET CityName = 'Test 2' WHERE ID = 1

2. Multiple Record Update
- UPDATE Student SET Standard = 2 WHERE Standard = 1

3. Update all record in table
- UPDATE Student SET Standard = 1

SQL INSERT INTO Query

INSERT INTO use for insert record in table

There are 2 ways to use INSERT INTO statement

1st

INSERT INTO YourTableName (ColumnName1,ColumnName1) VALUES (Value1,Value2)

2nd

INSERT INTO YourTableName (Value1, Value2)

Example

1. INSERT INTO City (ID, CityName) VALUES (1, 'Test 1')

2. INSERT INTO City VALUES (1, 'Test 1')

How to Use SQL ORDER BY Keyword

ORDER BY keyword is sorting result by ascending or descending order record wise

ORDER BY is by default sort result ascending not require to type ASC but if result get in decending than type DESC keyword with ORDER BY

ORDER BY Syntax

SELECT col1, col2 FROM YourTableName ORDER BY YourTable Column Like col1 ASC or DESC

Example

SELECT * FROM City ORDER BY CityName DESC

Result sort by CityName Z to A wise if type ASC keyword result sort by CityName A to Z

Use multiple column name in ORDER BY

Example

1. SELECT * FROM Student ORDER BY StudentName, Dateofbirth DESC

2. SELECT * FROM Student ORDER BY StudentName ASC, Dateofbirth DESC

SQL use AND, OR and NOT operator in WHERE Condition

In WHERE Condition we can use AND, OR and NOT operator

AND Operator use if All condition set in WHERE is TRUE

OR Operator use if Any 1 condition set in WHERE is TRUE

NOT Operator use if condition set in WHERE is NOT TRUE

AND Operator Syntax

SELECT * FROM YourTableName WHERE Condition1 AND Condition2

OR Operator Syntax

SELECT * FROM YourTableName WHERE Condition1 OR Condition2

NOT Operator Syntax

SELECT * FROM YourTableName WHERE NOT Condition1

Example

AND Operator

1. SELECT * FROM Student WHERE FirstName = 'Test1' AND LastName = 'Test2'

OR Operator

1. SELECT * FROM Student WHERE FirstName = 'Test1' OR LastName = 'Test2'

NOT Operator

1. SELECT * FROM Student WHERE NOT FirstName = 'Test1'

SQL WHERE Condition

WHERE Condition is used for filter records

WHERE Condition is used to filter records that match in specified WHERE condition

WHERE Syntax

SELECT C1, C2 FROM YourTableName WHERE YourCondition

Example

1. SELECT * FROM City WHERE CityName = 'Test'

2. SELECT * FROM Country WHERE CountryID = 1

Operators Use in Where Condition

1. = Equal Record match

2. <> or != Not Equal Record match

3. > Grater than record match

4. < Less than record match

5. >= Grater than and equal to record match

6. <= Less than and equal to record match

7. BETWEEN record filter between range

8. LIKE filter with pattern LIKE Operator condition use like %

9. IN filter all possible record in table

Example of operator wise

1. SELECT * FROM Country WHERE Name = 'India'

2. SELECT * FROM Country WHERE ID <> 1 or SELECT * FROM Country WHERE ID != 1

3. SELECT * FROM Employee WHERE Salary < 10000

4. SELECT * FROM Employee WHERE Salary > 10000

5. SELECT * FROM Employee WHERE Salary <= 10000

6. SELECT * FROM Employee WHERE Salary >= 10000

7. SELECT * FROM Employee WHERE Salary BETWEEN 5000 AND 10000

8. SELECT * FROM Employee WHERE EmployeeName LIKE '%A' end with A

9. SELECT * FROM Employee WHERE EmployeeName LIKE 'A%' start with A

10. SELECT * FROM Employee WHERE EmployeeName LIKE '%A%' start with A or end with A

11. SELECT * FROM Employee WHERE EmployeeName IN ('A', 'B', 'C')

SQL Select statement

Select statement use for select data from MS SQL database

Syntax  : SELECT column1, column2 FROM tablename

Example : SELECT ID, NAME FROM tblStudent

Result : Return your tblStudent table data ID & Name

SQL Distinct statement

SQL SELECT DISTINCT Statement

SELECT DISTINCT statement is return only different data from SQL table

Syntax : SELECT DISTINCT column1, column2 FROM tablename

Example : SELECT DISTINCT City FROM tblCity (if city contains duplicate name DISTINCT not get that value)

How to use INNER JOIN in SQL Query

INNER JOIN Syntax

SELECT YourColumnName FROM YourTableName
INNER JOIN InnerTableName ON InnerTableName.Column = YourTableName.Column

Example

SELECT * FROM City
INNER JOIN Country ON Country.CountryID = City.CountryIDF

SQL JOIN use for combine rows in one or more tables in SQL

SQL Join types

1. INNER JOIN return that value if both column record match in table

2. LEFT OUTER JOIN return records match in left table and match right table

3. RIGHT OUTER JOIN return records match in right table and match left table

4. FULL OUTER JOIN return records match in left and right table

How to use SQL BETWEEN Operator

BETWEEN operator is use from filter data range wise

BETWEEN Syntax

SELECT YourColumnName FROM YourTableName WHERE ConditionColumnName BETWEEN Value1 AND / OR Value 2

BETWEEN Example

1. SELECT * FROM Employee WHERE Salary BETWEEN 5000 AND 10000

2. SELECT * FROM Student WHERE RegisterDate BETWEEN 2018-01-08 AND 2018-01-10

How to use SQL IN Operator in Query

IN operator use for get single condition or multiple condition data from sql table

IN Operator syntax

SELECT YourColumnName FROM YourTableName WHERE ConditionColumnName IN (Values)

IN Operator Example

IN Operator you can use like

SELECT * FROM City WHERE CityName IN ('T1', 'T1')

OR

SELECT * FROM City WHERE CountryName IN (SELECT CountryName FROM Country WHERE CountryName LIKE 'a%')

How to use SQL LIKE operator different way in SQL Query

LIKE operator use in WHERE condition that search pattern wise in column

LIKE operator use 2 wildcard

1. % percent sign use for more than 1 characters

2. _ underscore use for single characters

LIKE Syntax

SELECT ColumnName FROM YourTableName WHERE ColumnName LIKE YourCondition

Example

1. SELECT TOP 1 * FROM Client WHERE LastName LIKE 'A%'
- filter records start with A character

2. SELECT TOP 1 * FROM Client WHERE LastName LIKE '%A'
- filter records end with A character

3. SELECT TOP 1 * FROM Client WHERE LastName LIKE '%A%'
- filter records anywhere get char A in field

4. SELECT TOP 1 * FROM Client WHERE LastName LIKE '_A%'
- filter records that contains second char A

5. SELECT TOP 1 * FROM Client WHERE LastName LIKE 'A%S'
- filter records that first char A and last char is S

SQL SELECT TOP Record Condition

SQL TOP Condition is use for return number of record return from table

SQL TOP is useful if large number of records store in your table. TOP condition is useful in performance

SELECT TOP Syntax

SELECT TOP No or Percent ColumnName FROM YourTableName WHERE Condition

Example

1. SELECT TOP 10 * FROM City
- Return 10 records from city table

2. SELECT TOP 10 PERCENT * FROM City
- Return TOP 10 percent record from city table

How to use MIN() and MAX() function in SQL Query

- MIN() function return small value of column

- MAX() function return large value of column

MIN() Syntax

SELECT MIN(YourColumnName) FROM YourTableName WHERE Condition

MAX() Syntax

SELECT MAX(YourColumnName) FROM YourTableName WHERE Condition

Example

1. SELECT MIN(Salary) AS LowSalary FROM Employee

2. SELECT MAX(Salary) AS HighSalary FROM Employee

How to use COUNT() function in SQL Query

COUNT() function return number of records exists in table

COUNT() Syntax

SELECT COUNT(ColumnName or *) FROM YourTableName

Example

SELECT COUNT(ID) FROM City

Introduction of SQL

What is SQL ?

- SQL Stand for Structured query language
- SQL is an ANSI standard

What SQL can do ?

- SQL execute query
- SQL select data
- SQL Store data
- SQL Modify data
- SQL Delete data
- SQL create procedure
- SQL create view
- SQL create table
- SQL create functionc