Difference Between .ToString() and Convert.ToString()

This is a very important question which usually ask in the interviews.

Let us discuss-

Answer- This question have very simple answer that .Tostring() can’t handle the null value but Convert.ToString can.

Example 1- understand it with the help of program.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication2
{
    public partial class Test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
          
            Session["Name"] = null;
            string Name=Session["Name"].ToString();

          }
    }
}



Explanation- We can see in above example that there is a session which have null value, when we apply .ToString() it will throw Exception ‘object reference not set to an instance of an object’

Example 2-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication2
{
    public partial class Test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
          
            Session["Name"] = null;
            string Name= Convert.ToString( Session["Name"]);

          }
    }
}

Explanation- We can see in above example that there is a session which also have null value, when we apply Convert.ToString it will not  throw Exception, it will handle it and sting ‘Name’ have  empty string.

                                                      Author- Er. Rahul Kr. Yadav

How we can find that – How many spaces and characters are there in a String

There is number of ways through which  we can achieve the above task, But I am going to tell you that How we can achieve it in optimize manner.

Example – There is a string-    "Rahul Yadav Dot Net Developer" , We need to find out how many spaces and characters are there ?

Program in C#-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication6
{
    class Program
    {
        static void Main(string[] args)
        {

            Rahul obj = new Rahul();
            obj.Find_Char();

        }
    }

   class Rahul
    {

        public void Find_Char()
        {

            int k = 0;
            int j = 0;

           string Name = "Rahul Yadav Dot Net Developer";
           foreach (Char g in Name)
            {

                if (char.IsWhiteSpace(g))
                {

                    k = k + 1;

                }


               else
                {

                    j = j + 1;
               
               
                }
           
            }
            Console.WriteLine("Number Of Spaces :" + k);
            Console.Write("Number Of Characters :" + j);
            Console.ReadLine();

            }
      
    }
}


Output:

                                   
                                             Author- Er. Rahul Kr. Yadav

SQL Interview Questions and Answers Part -1

Question- What provider ADO.NET use by default?

Answer- There’s no default provider as such but there are generic providers (OLE DB and ODBC) which are not limited to a specific database such as SQL Server or Oracle.

Question - What is reference cursor in SQL?

Answer - A Ref cursor is just a pointer to the result set of the cursor with which it is associated.

Question - How to Display duplicate rows in a table?

Answer - This query for finding duplicates in a table. Suppose you want to find all Names in a table that exist more than once:

SELECT UserName,
 COUNT(UserName) AS TotalOccurrences
FROM users
GROUP BY UserName
HAVING ( COUNT(UserName) > 1 )

OR

SELECT *
  FROM EMP A
 WHERE EXISTS (SELECT 1
                 FROM EMP
                WHERE empno = A.empno AND ROWID < A.ROWID)

Question - How to select last N records from a Table ?

Answer - SELECT TOP N *  FROM EMP ORDER BY EMPNO DESC

Question - What is use of CASCADE CONSTRAINTS in SQL?

Answer - Cascade Constraints, are usually used when there exists a parent-child relationship between tables using foreign key or referential constraints defined.

For Example, In case of deletion a record from the Parent Table, where there exists a foreign key to the child table,
If you do not specify cascading deletes, the default behavior of the database prevents us from deleting data in the Parent table if the child table has a reference to the Parent.

Question – What is the use of in command in SQL.

Answer - The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

IN Operator Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select the persons with a last name equal to "Hansen" or "Pettersen" from the table above.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen')

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Please refer the below link

http://www.w3schools.com/sql/sql_in.asp

Question - How to change database name in sql server.

Answer – “sp_renamedb”

You open that sp and find logic from there. Try this sp_helptext sp_renamedb

Question - How to find specify row value in SQL

Answer - Suppose

I have a table it's name is table1 , in it 3 column is specified , column name is id, name, date if in name column three are three same name like abc, abc, abc add id is a primary key, how to get second last name and date  in this table. table may be contain lot of records.

This way you will get the second last row (name, date). Incase if you need 3rd last you need to increase the 'red' number to 2 and so on......

 SELECT TOP 1 * FROM [Table1] 

WHERE ID NOT IN (SELECT TOP 1 ID FROM [Table1] ORDER BY ID DESC)

ORDER BY ID DESC          

What is SQL Injection?

SQL Injection is one of the many web attack mechanisms used by hackers to steal data from organizations. It is perhaps one of the most common application layer attack techniques used today. It is the type of attack that takes advantage of improper coding of your web applications that allows hacker to inject SQL commands into say a login form to allow them to gain access to the data held within your database.

SQL Injection is the hacking technique which attempts to pass SQL commands (statements) through a web application for execution by the backend database. If not sanitized properly, web applications may result in SQL Injection attacks that allow hackers to view information from the database and/or even wipe it out.

What is the impact of SQL Injection?        
 
Once an attacker realizes that a system is vulnerable to SQL Injection, he is able to inject SQL Query / Commands through an input form field. This is equivalent to handing the attacker your database and allowing him to execute any SQL command including DROP TABLE to the database.

An attacker may execute arbitrary SQL statements on the vulnerable system. This may compromise the integrity of your database and/or expose sensitive information. Depending on the back-end database in use, SQL injection vulnerabilities lead to varying levels of data/system access for the attacker. It may be possible to manipulate existing queries, to UNION (used to select related information from two tables) arbitrary data, use sub selects, or append additional queries.

In some cases, it may be possible to read in or write out to files, or to execute shell commands on the underlying operating system. Certain SQL Servers such as Microsoft SQL Server contain stored and extended procedures (database server functions). If an attacker can obtain access to these procedures, it could spell disaster.

Unfortunately the impact of SQL Injection is only uncovered when the theft is discovered. Data is being unwittingly stolen through various hack attacks all the time. The more expert of hackers rarely get caught.

1. Malicious SQL statements:

   a. For numeric Field :-SQL Injection Based on 1=1 is Always Return True.

   b. For  String   Field:-   SQL Injection Based on ""="" is Always Return  True .

 

  Note: The Hacker used these above  malicious sql statement to fetch the record without knowing there

Password and Id.

Let See The Work Demonstration: 

 

 Is a Table Structure(Employee)                 

                                               

Emp_Id	Emp_Name	Emp_Password	Emp_Salary
1001	RahulGanga	HA123	10,000
1002	Sathya	BA321	20,000
1003	Akhil	SA132	30,000
1004	Bubi	FA213	40,000

Our Targets is:

1.Fetch the Employee Details Without Knowing Their Password and even their name Or ID.

2.Modify the Record of Employee Without Knowing Their Password.

3. Delete the Record of Emplyee Without Knowing Their Password.

Example1.(InSQL)

1.     Fetch  the Employee Details Without Knowing Their Password and even ther name Or ID.

Sol: select * from employee where Emp_Id=1 or 1=1 and Emp_Pwd='Dhoom3' or''=''

Out Put:

 

 

2.     Modify the Record of Employee Without Knowing Their Password.

 First see actual result of table "empTb"

 

Sol: update empTb set Emp_Name='Titanic',Emp_Pwd='0' where Emp_Id=1 or 1=1

     and  Emp_Pwd=123 or 1=1

Out Put:

3. Delete the Record of Emplyee Without Knowing Their Password.

 

Sol:delete from employee where Emp_Id=1 or 1=1

Out Put:

How do I prevent SQL Injection attacks?

 

This Problem is solved by parameterized sql, SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.And  it Does not take any extra values because it take the value by Add parameter .

code:

_con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con"].ConnectionString);

        _cmd = new SqlCommand("select * from  SqlInjectionTb where id=@0 and pwd=@1", _con);

        _cmd.Parameters.AddWithValue("@0",Convert.ToInt32(TxtId.Text));

        _cmd.Parameters.AddWithValue("@1", TxtPwd.Text);

  

        _con.Open();

        _cmd.ExecuteNonQuery();

        DataTable _dt = new DataTable();

        SqlDataReader _dr = _cmd.ExecuteReader();

        _dt.Load(_dr);

        GridView1.DataSource = _dt;

        GridView1.DataBind();

Note:

It Not Accept any Extra Values by Hackers.

IT Fire Error…

After execution of this above code it absolutely fire the exception as use see below :

Difference between RANK, DENSE_RANK and ROW_NUMBER in SQL

Previously We discussed that How to Use  Dense_Rank() function to find Nth highest salary in sql server.

Now we are going to discuss What is difference between 

             Dense_Rank()     &      Rank()     &     Row_Number()

As I told you that Dense_Rank() create  one temporary  run time column which numbers the Salary according to the Rank priority like (1,2,3.....).

Rank() and  Row_Number() do the same thing but in different Ways. How?

Lets discuss- 

1-  Rank()-  It generate Temporary column at rum time, and do the numbering for every distinct row ,but it contains gaping in sequencing number.

Lets understand with query  and Example :

select Employee_Name,Salary, Rank() over(order by Salary desc) from Employee_Master

Explanation – Here we can see that Carry and Paul have same salary (10000) that’s why query give sequence (1) in Column RK because it gives different sequence number to distinct  Record.

John have Second highest salary, but it have Sequence 3 instead of 2 in Column RK because there are two members (Carry and Paul) having same salary.

There could be possible that 3 Members have highest salary (10000) then first three members will get sequence (1) and John will get 4 in RK column.

That’s why we mentioned above that function Rank() contains gaping in sequencing number.


2-Dense_Rank()-It generate Temporary column at rum time, and do the numbering for every distinct row ,but it doesn't contain gaping in sequencing number.

Lets understand with query  and Example :

select Employee_Name,Salary, Dense_Rank() over(order by Salary desc) as RK from Employee_Master

Explanation – Here we can see that Carry and Paul have same salary (10000) that’s why query  give sequence(1) in Column RK because it gives different sequence number to distinct  Record.

John have Second highest salary , it have Sequence 2  there is no gaping in Sequencing number

That’s why we mentioned above  that function  Dense_Rank() doesn't contain gaping in sequencing number.


3- Row_Number() – It also generate Temporary column at rum time, but the main thing is that it do numbering for every individual row without any gaping in sequencing number.

Let’s understand with query and Example:

select Employee_Name,Salary, Row_Number() over(order by Salary desc) as RK from Employee_Master

Explanation – Here we can see that Carry and Paul have same salary (10000) but they have different Sequence(1,2) .

That’s why we mentioned above  that  function  Row_Number() do numbering for every individual row without any gaping in sequencing number.

Difference in a Glance – 

 

Author-Er. Rahul Kr. Yadav

Remove duplicate records from a table in SQL Server

Many developers face this problem, that they have column with duplicate data, and want to delete duplicate records from SQL table , but one should never delete duplicate record without observing that ,that what  is going to delete ,and prefer to have latest record. So I am explain some steps to delete duplicate records from table and assuming that and table has unique incremental id.

1.Table  with duplicate record:-

insert into student values('yashodhara')

insert into student values('New')

insert into student values('New')

insert into student values('Old')

insert into student values('Old')

insert into student values('Avarage')

insert into student values('Keep')

insert into student values('Keep')

insert into student values('After')

2. check data

select * from student

Output is:

3.To find which record is duplicate and how many times it is repeated

 Select name,COUNT(*)as duplicate from student group by name having count(*)>1 order by count(*) desc

Out put is 

4. Delate duplicate records and keep lates record of every dulicate data

delete from student where rollno not in(

select max(rollno) from student group by name

)

5 Check final data

select * from student

                                                 Author: Er. Yashodhara Sharma