What is the best way for read a large file into a byte array using c#

Description : In this post how to convert a file stream in a byte array using c#. this code is use for convert a file into byte array and also send this byte array into FTP or Other file storage code. in my early post of upload file in FTP using c# use this code for convert any file from stream or file path to byte array

- Below code for convert file stream to byte array from specific file path using c#

public byte[] ReadAllBytes(string fileName)
{
    byte[] buffer = null;
    using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
    {
        buffer = new byte[fs.Length];
        fs.Read(buffer, 0, (int)fs.Length);

    or

    using (var reader = new BinaryReader(filestream))
    {
        byte[] file = reader.ReadBytes((int)filestream.Length);
        }
    }
    return buffer;
}

Convert byte array to string and string to byte array using c#

Description : In this post how to convert a simple string into byte array or convert a byte array to string using c#. this code is use in my early post for Download file from FTP.

- Below code for convert string to byte array using c#

string stringToConvert = "Hello World !";

byte[] buffer = Encoding.UTF8.GetBytes(stringToConvert);

- Below code for convert byte array to string using c#

string converted = Encoding.UTF8.GetString(buffer, 0, buffer.Length);

How to remove queue from azure queue using c#

Description : In this post how to remove queue from azure using c#. here same steps follow in my previous post for how to add message in azure using c#. and also check how to retrive queue from azure using c#

Step 1 : Downlod below NuGet package for add message using c#. Install both package one by one in your project use NuGet console or NuGet package managerr

- WindowsAzure.Storage
- WindowsAzure.ConfigurationManager

Step 2 : Get your connection string from Azure. Go to Azure Portal
Go to Storage Account -> Click on your storage account -> Inside Setting Click on Access keys -> In this window select Copy connection string. your connection string look like below

- DefaultEndpointsProtocol=https;AccountName=YourStorageAccountName;AccountKey=YourStorageAccountKey

Step 3 : Write below code for remove message from queue using c#

string ConnectionString = "DefaultEndpointsProtocol=https;AccountName=YourStorageAccountName;AccountKey=YourStorageAccountKey"

CloudStorageAccount account = CloudStorageAccount.Parse(ConnectionString); // Cloud Storage Account

CloudQueueClient queueClient = account.CreateCloudQueueClient(); // Queue Client Create

CloudQueue messageQueue = queueClient.GetQueueReference("YourAzureQueueName"); // Get Queue Reference

// this line for remove message from azure queue
messageQueue.DeleteMessage(MessageID, PopReceipt, null, null); // here message id is queue is get from azure and PopReceipt also you can get from queue

How to retrive queue from azure using c#

Description : In this post how to retrive queue message from azure using c#. here same steps for get message in my previous post of how to get message from azure using c#

Step 1 : Downlod below NuGet package for add message using c#. Install both package one by one in your project use NuGet console or NuGet package managerr

- WindowsAzure.Storage
- WindowsAzure.ConfigurationManager

Step 2 : Get your connection string from Azure. Go to Azure Portal
Go to Storage Account -> Click on your storage account -> Inside Setting Click on Access keys -> In this window select Copy connection string. your connection string look like below

- DefaultEndpointsProtocol=https;AccountName=YourStorageAccountName;AccountKey=YourStorageAccountKey

Step 3 : Create class for add json message in azure queue using c#

public class ResponseClass
{
    public string id { get; set; }
    public string name { get; set; }
}

Step 3 : Write below code for retrive queue from azure using c#

string ConnectionString = "DefaultEndpointsProtocol=https;AccountName=YourStorageAccountName;AccountKey=YourStorageAccountKey"

CloudStorageAccount account = CloudStorageAccount.Parse(ConnectionString); // Cloud Storage Account

CloudQueueClient queueClient = account.CreateCloudQueueClient(); // Queue Client Create

CloudQueue messageQueue = queueClient.GetQueueReference("YourAzureQueueName"); // Get Queue Reference

// this line for retrive 10 messages from azure queue
List<CloudQueueMessage> retrievedMessage = messageQueue.GetMessages(10).ToList();

using for loop one by one get queue response like below code

for (int i = 0; i < retrievedMessage.Count; i++)
{
    string Message = retrievedMessage[i].AsString;
    string ID = retrievedMessage[i].Id;
    string PopReceipt = retrievedMessage[i].PopReceipt; // this PopReceipt use for delete queue

    JsonResponse obj = Newtonsoft.Json.JsonConvert.DeserializeObject<ResponseClass>(Message);
}

How to add message in azure queue using C#

Description : In this post how to add message in azure queue storage using c# code. This code use in any .Net application like Winform application or MVC or ASP.Net. below steps for how to successfully add message in azure queue using c#

Step 1 : Downlod below NuGet package for add message using c#. Install both package one by one in your project use NuGet console or NuGet package managerr

- WindowsAzure.Storage
- WindowsAzure.ConfigurationManager

Step 2 : Get your connection string from Azure. Go to Azure Portal
Go to Storage Account -> Click on your storage account -> Inside Setting Click on Access keys -> In this window select Copy connection string. your connection string look like below

- DefaultEndpointsProtocol=https;AccountName=YourStorageAccountName;AccountKey=YourStorageAccountKey

Step 3 : Create class for add json message in azure queue using c#

public class RequestClass
{
    public string id { get; set; }
    public string name { get; set; }

    public RequestClass() { }

    public RequestClass(string _id, string _name)
    {
        this.id = _id; this.name = _name;
    }
}

Step 4 : Write below code for add message in your queue using C#.

string ConnectionString = "DefaultEndpointsProtocol=https;AccountName=YourStorageAccountName;AccountKey=YourStorageAccountKey"

CloudStorageAccount account = CloudStorageAccount.Parse(ConnectionString); // Cloud Storage Account

CloudQueueClient queueClient = account.CreateCloudQueueClient(); // Queue Client Create

CloudQueue messageQueue = queueClient.GetQueueReference("YourAzureQueueName"); // Get Queue Reference

string MessageToAdd = JsonConvert.SerializeObject(new RequestClass(GUID, Name)); // Serialize JSON for add in queue

CloudQueueMessage m = new CloudQueueMessage(MessageToAdd); // Generate Cloud Message

messageQueue.AddMessage(m); // Finally add message in queue

Go to next control by pressing Enter Key in c# winform application

Description : In this post go to next control using Enter Key in c# windows application

- Add 2 Textbox in winform

- Generate KeyDown event of both textbox control

- Add Below code in both keydown event

private void textbox1_KeyDown(object sender, KeyEventArgs e) 
{
    if (e.KeyCode == Keys.Enter) 
        textbox2.Focus(); 
}

private void textbox2_KeyDown(object sender, KeyEventArgs e) 
{ 
    if (e.KeyCode == Keys.Enter) 
        textbox1.Focus(); 
}

Note : if focus in textbox 1 than press enter and change focus in textbox2 if again press enter than focus set in textbox1

How to pass OUTPUT parameter in MS SQL StoreProcedure

Description : In this post how to pass OUTPUT parameter in procedure and how to return value from procedure

- Create Simple StoreProcedure

CREATE PROCEDURE [dbo].[MyOUTPUTParamCheck]
    @Name Varchar(100)
    ,@OUTPARAMETER Varchar(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

        SET @OUTPARAMETER = 'Hi ' + @Name

    PRINT @OUTPARAMETER
END

- Call procedure

[dbo].[MyOUTPUTParamCheck] 'Test',''

- Result : Hi Test

How to display browser notification in website using javascript

Description : In this post how to display notification in website using javascript. this notfication display if your browser is open or minimized. write below javascript for display browser notfication

Step 1 : add below javascript for notfication display

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

Step 2 : add below javascript code for display notfication

<script>
        $(document).ready(function () {

            document.addEventListener('DOMContentLoaded', function () {
                if (Notification.permission !== "granted") {
                    Notification.requestPermission();
                }
            }); 

        });

        function NotifyMe(title, desc, url) {
            if (Notification.permission !== "granted") {
                Notification.requestPermission();
            }
            else {
                var notification = new Notification(title, {
                    icon: url,
                    body: desc,
                });

                /* Remove the notification from Notification Center when clicked.*/
                notification.onclick = function () {
                    window.open(url);
                };

                /* Callback function when the notification is closed. */
                notification.onclose = function () {
                    console.log('Notification closed');
                };
            }
        } 
</script>

Step : Add HTML button if click on button it call function NotfiyMe. here 3 parameter for set dynamic text in notfication 1st is notficiation title, 2nd is description of notficiation and last 3rd is URL for display image in notification

<button onclick="NotifyMe('Browser Notification', 'This is testing browser notification.','https://png.icons8.com/color/260/comedy.png')">Display Notification</button>

- Below screenshot for Notification looks like

SQL Server database backup using SQL StoreProcedure in c# winform application

Description : in this post how to take DB backup from winform using SQL Storeprocedure

- Step 1 : Create new sample database for generate backup

Database Name : SampleDBTest

- Step 2 : Create StoreProcedure for generate backup file

CREATE PROC DatabaseBackupProcedure
AS 
BEGIN 

    DECLARE @path Varchar(1000); 

    SET @path='YourDBBackupPath\SampleDBTest'+CONVERT(CHAR(10),  GETDATE(), 121)+'.bak'; 
   
    BACKUP DATABASE SampleDBTest to DISK=@path; 
END 

- Step 3 : Create winform application for call store procedure of database backup

- Step 4 : Get Connection String of SampleDBTest database and add in App.Config file

- Step 5 : Write below c# code in button click for call procedure

Code Note : Get Connection string from App.Config file and add below namespace for get connection string from App.Config and SQL Connection

using System.Data.SqlClient; 
using System.Configuration; 

SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()); 
       
private void btnBackupDB_Click(object sender, EventArgs e) 
{ 
    con.Open(); 
    SqlCommand cmd = new SqlCommand("DatabaseBackupProcedure", Connection); 
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.ExecuteNonQuery(); 
    con.Close(); 
    MessageBox.Show("Back up Done successfully");
}

Note : By using this code database direct create .bak file in your folder path set in sql storeprocedure

How to parse json data in table row using MS SQL OPENJSON

Description : In this post how to parse json data in MS SQL using OPENJSON first we need json here declare one json variable and set static json in this variable. write one select querye with OPENJSON for convert json to SQL Table

DECLARE @json NVARCHAR(MAX)

SET @json = 
N'[ 
       { "name": "Test 1", "surname": "Testing" },
       { "name": "Test 2", "surname": "Testing Again" }
 ]' 

SELECT * FROM OPENJSON(@json) 
WITH (
    FirstName nvarchar(50) '$.name'
    , LastName nvarchar(50) '$.surname'
)

Below screen shot display how to write query of OPENJSON

Below screen shot display how result look like

How to check file exists in FTP server in C#

Description : In this post when you try to delete file from FTp but get error 550, File Unavailable to solve this first check file is exists or not in FTP.
here create one method for check file in FTP

private bool FileCheckInFTP(string fileName)   
{   
    var request = (FtpWebRequest)WebRequest.Create("ftp://www.server.com/" + fileName);   
    request.Credentials = new NetworkCredential("username", "password");   
    request.Method = WebRequestMethods.Ftp.GetFileSize;   
       
    try   
    {   
        FtpWebResponse response = (FtpWebResponse)request.GetResponse();   
        return true;   
    }   
    catch (WebException ex)   
    {   
        FtpWebResponse response = (FtpWebResponse)ex.Response;   
        if (response.StatusCode == FtpStatusCode.ActionNotTakenFileUnavailable)   
            return false;   
    }   
    return false;   
}

- if the response get from FTP like "ActionNotTakenFileUnavailable" it means file is unavailble.

How to delete file from FTP using c#

Description : In this post how to delete file from FTP server using c#

private string DeleteFileFromFTP(string fileName)   
{   
    FtpWebRequest request = (FtpWebRequest)WebRequest.Create("ftp://www.server.com/" + fileName);   
    request.Method = WebRequestMethods.Ftp.DeleteFile;   
    request.Credentials = new NetworkCredential("username", "password");   
       
    using (FtpWebResponse response = (FtpWebResponse)request.GetResponse())   
    {   
        return response.StatusDescription;       
    }   
}

- Above code is delete file from FTP in the code WebRequestMethods is "DeleteFile". when file delete from FTP server it return response and description is "250 File deleted successfully"

How to get all file and directory from FTP server using c#

Description : In this post FTP server return all files and directory in response uing c#

private List GetAllFilesnDirectory() 
{ 
    try 
    { 
         FtpWebRequest request = (FtpWebRequest)WebRequest.Create("ftp://www.server.com/"); 
         request.Method = WebRequestMethods.Ftp.ListDirectory; 
 
         request.Credentials = new NetworkCredential("username", "password"); 
         FtpWebResponse response = (FtpWebResponse)request.GetResponse(); 
         Stream responseStream = response.GetResponseStream(); 
         StreamReader reader = new StreamReader(responseStream); 
         string names = reader.ReadToEnd(); 
 
         reader.Close(); 
         response.Close(); 
 
         return names.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries).ToList(); 
    } 
    catch (Exception) 
    { 
        throw; 
    } 
}

- Above code return ListDirectory from FTP response and get in stream than parse response and get list of files and directory

How to capture desktop screenshot using c# winform application

Description : In this post how to capture desktop screen shot using windows application add below namespace and code for take screen shot.

- Add below namespace for generate image

using System.Windows.Forms;
using System.Drawing.Imaging;

- Add below code for capture image and save in specific directory

Bitmap bitmap = new Bitmap(Screen.PrimaryScreen.Bounds.Width, Screen.PrimaryScreen.Bounds.Height); 
Graphics graphics = Graphics.FromImage(bitmap as System.Drawing.Image); 
graphics.CopyFromScreen(0, 0, 0, 0, bitmap.Size); 
bitmap.Save(@"FolderPath", ImageFormat.Jpeg); 

C# Input / Output classes [I/O Class]

Description : System.IO some classes for perform a create, delete, read and write files. below are some classes in the System.IO

- BinaryReader : Reads primitive data from a binary stream.
- BinaryWriter : Writes primitive data in binary format.
- BufferedStream : temporary storage for a stream of bytes.
- Directory : manipulating a directory structure.
- DirectoryInfo : performing operations on directories.
- DriveInfo : Provides information for the drives.
- File : performing operations on files.
- FileStream : read from and write to any location in a file.
- MemoryStream : random access to streamed data stored in memory.
- Path : Performs operations on path information.
- StreamReader : reading characters from a byte stream.
- StreamWriter : writing characters to a stream.
- StringReader : reading from a string buffer.
- StringWriter : writing into a string buffer.

How to use AngularJS filters in html controls

Description : in this post how to convert datem currency, lowercase and uppercase in angular js simple pass filter from filter list

Filters List :

- currency : format number in currency format
- date : format date to formated date
- lowercase : fromat string to lowercase
- uppercase : format string to uppercase

How to create table using SQL CREATE TABLE

Description : CREATE TABLE statement use for create a table in your existsing SQL database

Syntax :

CREATE TABLE YourTableName (
    TableColumn1 DataType,
    TableColumn2 DataType,
    TableColumn3 DataType,
)

Example :

CREATE TABLE Employee (
    EmployeeID Int,
    EmployeeName Varchar(50),
    City Varchar(50),
    PhoneNo Varchar(50)
)

Syntax : Syntax for how to create table using another table of database

CREATE TABLE YourTableName AS
SELECT Column1,
Column2 FROM CreatedTableName

How to DROP Database using SQL DROP DATABASE

Description : SQL DROP DATABASE statement drop existing database from your SQL

Syntax :

DROP DATABASE YourDatabaseName

Example :

DROP DATABASE EmployeeDB

How to create database in SQL using CREATE DATABASE Statement

Description : SQL CREATE DATABASE statement use for create Database in SQL

Syntax :

CREATE DATABASE YourDatabaseName

Example :

CREATE DATABASE EmployeeDB

How to fix VS2017 missing XAML tools build error

Error :

The "Microsoft.Build.Tasks.Xaml.PartialClassGenerationTask" task could not be loaded from the assembly C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\amd64\XamlBuildTask.dll. Could not load file or assembly 'file:///C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\amd64\XamlBuildTask.dll' or one of its dependencies. The system cannot find the file specified. Confirm that the <UsingTask> declaration is correct, that the assembly and all its dependencies are available, and that the task contains a public class that implements Microsoft.Build.Framework.ITask

To solve this issue just open visual studio installer and go to Individual Components tab and select "Windows Workflow Foundation"

How to call function after complete page load in javascript

Description : in this post how to call function after complete page load in aspx, html, cshtml page just add below code in script

Write below code and add your function in setInterval

<script>
        document.addEventListener("DOMContentLoaded", function () {
            setInterval(calculateTotalFees(), 0);
        }, false);
</script>

How to upercase / lowercase text in SQL query

Description : in this post how to get result of state name in upper case  or in lower case

Query Syntax for upper case text return in result

- SELECT UPPER(sta_StateName) AS State FROM StateMaster

Result :

DISTRICT OF COLUMBIA
TEXAS
NEW MEXICO

Query Syntax for lower case text return in result

- SELECT LOWER(sta_StateName) AS State FROM StateMaster

Result :

district of columbia
texas
new mexico

Screen Shot :

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