Download files

1.  Create interface for downloads method and path property.  Interface :  An interface contains only the signatures of methods, properties, events or indexers.  A class or struct that implements the interface must implement the members of the interface  that are specified in the interface definition.

    public interface downloads 
    {
        string path { get; set; }
        void download();
    }
   

2.  create class name general and inherit the interface class in it  Inheritance :   Inheritance allows us to define a class in terms of another class,   which makes it easier to create and maintain an application.  set the property and method for download by using httpresponse download the file from server to local system.

    public class general  : downloads
    {
   
        public string path
        {
            get;
            set;
        }
   
        public void download()
        {
            using (FileStream fs = File.OpenRead(this.path))
            {
                int length = (int)fs.Length;
                byte[] buffer;
   
                using (BinaryReader br = new BinaryReader(fs))
                {
                    buffer = br.ReadBytes(length);
                }
   
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.Buffer = true;
                HttpContext.Current.Response.AddHeader("content-disposition", 
                String.Format("attachment;filename={0}",  Path.GetFileName(path)));
                HttpContext.Current.Response.ContentType = 
                     "application/" + Path.GetExtension(path).Substring(1);
                HttpContext.Current.Response.BinaryWrite(buffer);
                HttpContext.Current.Response.End();
            }   //
        }
    }
   
   
   

3.  create the object for class general and set the path to property  call the download method to make a download the file.  

     public void sDownload()
        {
            downloads obj = new general();
            obj.path = @"C:\GoogleBackup\googleService.txt";
            obj.download();
        }
   
   

Download

Search tables and procedures by given text in it

Find all tables and procedures by giving string in it using like operator in SQL

1. FOR TABLES

    SELECT COLUMN_NAME, TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE '%searchstring%'
   

2. FOR PROCEDURES

    SELECT OBJECT_NAME(object_id), 
           OBJECT_DEFINITION(object_id)
    FROM sys.procedures
    WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%'
   

Get RAM and CPU usage in C#

Get Cpu and Ram Usage by kernel132.dll in c# How to get CPU percentage ?

1. Step 1: include namespace in class file

    using System;
    using System.Diagnostics;
    using System.Runtime.InteropServices;
    using System.IO;
   

2. Step 2: Declare performance counter and other variables and we just get average of cpu 5 times with difference of 5 seconds

    Public string GetCpuPercentage()
     {
      PerformanceCounter cpuCounter;
      double cpuUsage = 0;
      int totalCpuUsage = 0;
      double returnLoopCount = 0;
   
      cpuCounter = new PerformanceCounter();
      cpuCounter.CategoryName = "Processor";
      cpuCounter.CounterName = "% Processor Time";
      cpuCounter.InstanceName = "_Total";
      for (int i = 0; i < 5; i++)
      {
      cpuUsage += cpuCounter.NextValue();
      System.Threading.Thread.Sleep(1000);
      }
      totalCpuUsage = Convert.ToInt32(Math.Ceiling(cpuUsage / 5));
      return totalCpuUsage;
     }
   

3. Step 3:Declare performance counter and other variables and we just get average of RAM memory usage by using 'Kernel32.dll'

    public string GetRamPercentage()
     {
         PerformanceCounter ramCounter;
              double ramUsage = 0;
              int TotalRamMemory = 0;
              int AvailableRamMemory = 0;
              int UsedRamMemory = 0;
              int RamUsagePercentage = 0;
              double returnLoopCount = 0;
              MEMORYSTATUSEX statEX = new MEMORYSTATUSEX();
              statEX.dwLength = (uint)Marshal.SizeOf(typeof(MEMORYSTATUSEX));
              GlobalMemoryStatusEx(ref statEX);
   
              double ram = (double)statEX.ullTotalPhys;
              //float ram = (float)stat.TotalPhysical;
              ram /= 1024;
              ram /= 1024;
   
              TotalRamMemory = Convert.ToInt32(Math.Round(ram, 0));
              ramCounter = new PerformanceCounter("Memory", "Available MBytes");
              for (int i = 0; i < 5; i++)
              {
                  ramUsage += ramCounter.NextValue();
                  System.Threading.Thread.Sleep(1000);
              }
              AvailableRamMemory = Convert.ToInt32(Math.Round((ramUsage / 5), 0));
              UsedRamMemory = TotalRamMemory - AvailableRamMemory;
              RamUsagePercentage = ((UsedRamMemory * 100) / TotalRamMemory);
   
      return RamUsagePercentage;
           
     }
   
        [return: MarshalAs(UnmanagedType.Bool)]
        [DllImport("Kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        internal static extern bool GlobalMemoryStatusEx(ref MEMORYSTATUSEX lpBuffer);
   
    [StructLayout(LayoutKind.Sequential)]
    internal struct MEMORYSTATUSEX
     {
          internal uint dwLength;
          internal uint dwMemoryLoad;
          internal ulong ullTotalPhys;
          internal ulong ullAvailPhys;
          internal ulong ullTotalPageFile;
          internal ulong ullAvailPageFile;
          internal ulong ullTotalVirtual;
          internal ulong ullAvailVirtual;
          internal ulong ullAvailExtendedVirtual;
     }
   
   

Download

How to create stored procedure in SQL?

SQL STORED PROCEDURE :

1. SQL Procedure are nothing but set of sql statement grouped and perform a specific task. it will give high performance for database
2. stored procedure compiled at once it created in database Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan
3. it is usability of SQL code and re-usability purpose of same kind of operation using twice in our source code

How to create stored procedure ?

 SYNTAX:

 CREATE PROCEDURE <PROC NAME>
 AS
 @param1 <datatype>
 BEGIN
  //Code of T-SQL
 END


EXPLAINATION : 

 CREATE PROC <PROC NAME>
 AS
 @param1 BIGINT  //input param
 BEGIN

 CREATE TABLE #TEMP (name VARCHAR(50)) // CREATiNG TEMP TABLE
 INSERT INTO #TEMP    // bulk insert
 SELECT * FROM table1 WHERE salary > @param // get users from table 1 
  UNION 
 SELECT * FROM table2 WHERE salary < @param  // get users from table 2

 SELECT * FROM #TEMP
 DROP TABLE #TEMP

 END

How to alter stored procedure ?

 SYNTAX:

 ALTER PROCEDURE <PROC NAME>
 AS
 @param1 <datatype>
 BEGIN
  //code of T-SQL
 END

EXPLAIN : 
//instead of create we can give alter and we can save changes in sp(STORED PROC)

EXPLAINATION : 

 ALTER PROC <PROC NAME>
 AS
 @param1 BIGINT   //input param
 BEGIN

 CREATE TABLE #TEMP (name VARCHAR(50)) // CREATiNG TEMP TABLE
 INSERT INTO #TEMP    // bulk insert
 SELECT * FROM table1 WHERE salary > @param // get users from table 1 
  UNION 
 SELECT * FROM table2 WHERE salary < @param  // get users from table 2

 SELECT * FROM #TEMP ORDER BY name DESC
 DROP TABLE #TEMP

 END

How to execute stored procedure ?

 SYNTAX:
 EXECUTE <sp_name> @param = 1000
  OR
 EXEC <sp_name> 1000
  OR 
 <sp_name> 1000

Summary : Stored procedure is reusing the code,performance of execution plan,reduce the traffic of the network sp(STORED PROC) is one of the best way to use in heavy transactions over the network

Bulk insert from one table to another table in SQL

Bulk Insert

1. we can use two types of bulk insert in MSSQL
2. it is very useful to transfer whole data or particular data from one table to another
3. Type 1: we need to create schema or we can use existing schema for insertion
4. Type 2: we cannot insert data's in existing table schema.this type will copy the schema of selected table and create table in database or we can use temp table
5. Mostly this bulk insert are used in stored procedure or user-defined functions for calculations.

Type 1:

 CREATE TABLE TABLE1( name INT,EmpSalary BIGINT)
 INSERT INTO TABLE1
 SELECT name,salary FROM TABLE2
 WHERE salary > 1000

Type 2 :

 SELECT * INTO #TEMP FROM TABLE2 //this will create temp table with selected table schema
 WHERE salary > 1000
   OR
 SELECT * INTO TABLE FROM TABLE2   // this will create real table with selected table schema in DB
 WHERE salary > 1000

Bulk Update using join in SQL

UPDATE WITH JOIN

 UPDATE t1
  SET t1.name = t2.empname  //copy content from one table to another
 FROM table1 t1
    INNER JOIN table2 t2 ON
        t1.id = t2.t1id