SQL Tools( Count, MAX, MIN)

COUNT is a method which will count the total no of columns,total no of rows,total no of values.Like :-

SELECT COUNT(Column_Name) FROM Table_Name
This will count total no of rows where First_Name is Saurabh.
SELECT Count(*) FROM Table_Name WHERE First_Name = 'Saurabh'

SELECT COUNT(First_Name) AS NoOfStudents FROM STUDENTS
STUDENTS is a Table_Name

Max :-
SELECT MAX(Column1) From Table1
SELECT MAX(DateOfBirth) AS BirthDate FROM Employee WHERE DateOfBirth > 12202009

Min :-
SELECT MIN(Column1) From Table1
SELECT MIN(DateOfBirth) AS BirthDate FROM Employee WHERE DateOfBirth < 12202009

Read More

SQL Tools(SELECT INTO, INSERT INTO)

SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table at the same time.
The general SQL SELECT INTO syntax looks like this:

SELECT Column1, Column2, Column3,
INTO Table2 FROM Table1

This will create a Table2 same as Table1.

SQL INSERT INTO :-

1:- INSERT INTO Table1 VALUES (value1, value2, value3…)
2:- INSERT INTO Table1 (column1,column2,column3) VALUES(value1,value2,value3)

Read More

SQL Tools(JOIN Operator(Inner join, Left join, Right join))

INNER JOIN Operator :-
INNER Join return all rows from the join of left table and right table if there are same data available in both tables if no data will match then it will return null
SELECT * FROM Table_Name_1 INNER JOIN Table_Name_2 WHERE Table_Name_1.Column_Name = Table_Name_2.Column_Name

You can divide your result in group like :-
If a table contains First_Name, Last_Name, Address, Ph_No and there are so many result according to First_Name then you can use Group_By Like this :-

SELECT * FROM Old_Persons As OP INNER JOIN New_Persons As NP WHERE OP.First_Name = NP.First_Name Group By OP.First_Name

LEFT JOIN Operator :-
LEFT join return all rows from the left table even there are no rows in right table.

SELECT * FROM Old_Persons As OP LEFT JOIN New_Persons As NP WHERE OP.First_Name = NP.First_Name Group By OP.First_Name

RIGHT JOIN Operator :-
RIGHT JOIN return all rows from the right table even if there are no rows available in left table.

SELECT * FROM Old_Persons As OP RIGHT JOIN New_Persons As NP WHERE OP.First_Name = NP.First_Name Group By OP.First_Name

Read More

SQL Tools(Alias Operator & In Operator)

ALIAS Name for Column :-
SELECT Column_Name AS Alias_Name FROM Table_Name

ALIAS Name for table :-
SELECT Column_Name From Table_Name AS Alias_Name

ALIAS Example :-
SELECT p.FirstName,p.LastName, po.OrderID
FROM Persons AS p, Product AS po
WHERE p.FirstName='Saurabh' AND p.LastName='Singh' AND po.OrderID > 2

Without using AS
SELECT p.FirstName,p.LastName, po.OrderID
FROM Persons p, Product po
WHERE p.FirstName='Saurabh' AND p.LastName='Singh' AND po.OrderID > 2

IN Operator :-
If you want to select OrderID of between 2,3,4,5 then :-
SELECT p.FirstName,p.LastName, po.OrderID
FROM Persons p, Product po
WHERE p.FirstName='Saurabh' AND p.LastName='Singh' AND po.OrderID IN (2,3,4,5)

OR :-
SELECT * FROM Persons
WHERE FirstName IN ('Saurabh','Saanjh', 'vivek')

Read More

SQL - Tools (BETWEEN Operator)

SELECT column_name(s) FROM table_name
WHERE column_name BETWEEN value1 AND value2

SELECT * FROM COILS WHERE COIL_NAME BETWEEN SomeValue1 AND SomeValue2

Between operator is like It will select a row where COIL_NAME has SomeValue1 . Value depends on database to database . Some database will select rows where COIL_NAME has SomeValue1 and where COIL_NAME has SomeValue2.

We can use NOT BETWEEN operator if you you dont want to select those values like :-
SELECT * FROM COILS WHERE COIL_NAME NOT BETWEEN SomeValue1 AND SomeValue2
This query will select a value where coil_name is not equal to SomeValue1 and SomeValue2.

Read More

SQL - Tools(Top Clause AND Like Operator)

SELECT TOP 1 FROM Table_Name

This will select 1 row from table

SELECT TOP 1* FROM Table_Name (Select all columns of 1st row)
SELECT TOP 2* FROM Table_Name (Select first two rows of table)
SELECT TOP 50 PERCENT * FROM Table_Name (Select 50 % rows from table)

LIKE OPERATOR

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

IF a column name CITY from Persons table contains Kanpur, Delhi , NewDelhi , Kannauj , Nagar values then If you want to select city name starts from K then :-

SELECT * FROM Persons
WHERE CITY LIKE 'k%'

this will retrieve Kanpur , Kannauj.

If you want to select city name ends from i then :-
SELECT * FROM Persons
WHERE CITY LIKE '%i'


IF you want to select a city which has particular format like Select a city which has 'elh' pattern
SELECT * FROM Persons
WHERE City LIKE '%elh%'

this will retrieve Delhi , NewDelhi.

If you dont want to select a city which has particular format like 'elh'

SELECT * FROM Persons
WHERE City NOT LIKE '%elh%'

Read More

SQL - Tools ( INSERT,UPDATE and DELETE Statement)

INSERT INTO table_name
VALUES (value1, value2, value3,...)

IF a table Persons contains P_Id,LastName,FirstName,Address,City columns.If you want to insert values then :-
INSERT INTO Persons
VALUES (4,'Singh', 'Saurabh', 'Sector-23', 'Gurgaon')

IF NOT EXISTS (SELECT * FROM Persons WHERE P_Id = 4)
BEGIN
INSERT INTO Persons
VALUES (4,'Singh', 'Saurabh', 'Sector-23', 'Gurgaon')
END

By this way it will check firstly, if this p_id exist in the table then do not insert otherwise insert these values in the table.

Update statement :-
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

We use If not exist statement whether there is a need to update or not.

DELETE Statement :-
DELETE From table_name WHERE some_column=some_value
Note :- Never use Delete * use Delete tableName.*

Read More

SQL - Tools(ORDER BY)

ORDER BY keyword is used for sorting suppose in a table there is a column named first_name.
If you want to show the result in ascending order then :-

SELECT * FROM Persons WHERE Age >= 25 GROUP BY first_name ASC

If you want to show the result in descending order then :-
SELECT * FROM Persons WHERE Age >= 25 GROUP BY first_name DESC

By default it is ascendeng . if we use like GROUP BY first_name then it sorts in ascending order.

Read More

SQL - Tools (WHERE, AND, OR Clause)

Where clause is use for filtering like :-

SELECT * FROM table_name WHERE column_name (operator) value
(operator) :- =, >, <,! and more operators.

A Friends table contains firstName, secondName, address, phone_no and firstName values are :- saurabh, sandy, gaurav, somu, saurabh and address column values are :- kanpur, allahabad, delhi, kanpur, varanasi.

SELECT * FROM Friends WHERE firstName = 'Saurabh' AND address = 'kanpur'

This will select only first row because only first row satisfies WHERE condition

SELECT * FROM Friends WHERE firstName = 'Saurabh' OR address = 'kanpur'

This will select first row, fourth row and fifth row due to oR condition in where clause

Operators :-
< :- lessthan
> :- greaterthan
<= :- less than or equal to
>= :- greater than or equal to
<> :- not equal to
BETWEEN :- between an inclusive range
LIKE :- for search pattern
IN :- SEARCH for exact values like if i know values are 1987, 1989

Use of IN clause :-
EXAMPLE :- SELECT * FROM Friends WHERE year IN (1987, 1989)

Read More

SQL - Tools....(SELECT Clause)

SELECT :-

SELECT * FROM TABLE_NAME this will select whole table

If Coils table contains 5 columns p_id, coil_no, coil_width, coil_length, coil_name.Then you have to select coil_width and coil_no like :-

SELECT coil_width, coil_length FROM Coils

If you have to select distinct columns from table like coil_name columns have ABCDE12, ABCD13, ABCD14, ABCD12, ABCD14 then you have to choose only distinct values :-

SELECT DISTINCT column_name FROM table_name
SELECT DISTINCT coil_name FROM coils

Read More

Visual Studio .Net ShortCut keys

DecreaseFilterLevel : ALT + ,
IncreaseFilterLevel : ALT + .
GotoBrace : CTRL + ]
GotoBraceExtend : CTRL _ SHIFT + ]
LineEnd : END
LineEndExtendColumn : SHIFT + ALT + END
ToggleWordWrap : CTRL + E, CTRL + W
ScrollLineDown : CTRL + DOWN ARRAY
LineDownExtendColumn : SHIFT + ALT + DOWN ARRAY
WordDeleteToEnd : CTRL + DEL
CopyParameterTip : CTRL + SHIFT + ALT + C
WordDeleteToStart : CTRL + BACKSPACE
SelectCurrentWord : CTRL + W
ViewWhiteSpace : CTRL + R , CTRL + W
Commenting : CTRL + k , CTRL + C
Uncommenting : CTRL + K , CTRL + U

Read More

How to implement a progressBar while opening a textFile in an application ?

Make a claas like this one to create ProgressBar :-
public class StatusProgressBar : ToolStripProgressBar
{
#region Private Fields

private static StatusProgressBar _instance = null;

#endregion

#region Constructor

private StatusProgressBar()
{
this.Style = ProgressBarStyle.Blocks;
this.Step = 1;
}

#endregion

#region Properties

///

/// Get Singleton instance of progressBar
///

public static StatusProgressBar Instance
{
get
{
if (_instance == null)
{
_instance = new StatusProgressBar();
}

return _instance;
}
}

#endregion
}

Now main issue how to increase the bar and how calculate the percentage then firstly i would say calculate numer of lines to be read or parsed in amy editor according to your application

public const char NEWLINE_CHARACTER = '\n';
string[] lines = t.Split(NEWLINE_CHARACTER);

This will perform for all the lines and lines will be incremented then progress bar will be increemented accordingly.
for (int lineIndex = 0; lineIndex < lines.Length; lineIndex++)
{
if (lineIndex % 2 == 0)
{
StatusProgressBar.Instance.PerformStep();
StatusProgressBar.Instance.ToolTipText = Convert.ToString((StatusProgressBar.Instance.Value / StatusProgressBar.Instance.Maximum) * 100) + "%";
}
}

Happy to code................

Read More

What is Reference counting in COM ?

Reference counting is a memory management technique used to count how many times an object has a pointer referring to it. The first time it is created, the reference count is set to one. When the last reference to the object is nulled, the reference count is set to zero and the object is deleted.
Care must be exercised to prevent a context switch from changing the reference count at the time of deletion. In the methods that follow, the syntax is shortened to keep the scope of the discussion brief and manageable.

Read More

What is COM ?

Microsoft’s COM is a technology for component software development. It is a binary standard which is language independent. DCOM is a distributed extension of COM.
Microsoft COM (Component Object Model) technology in the Microsoft Windows-family of Operating Systems enables software components to communicate. COM is used by developers to create re-usable software components, link components together to build applications, and take advantage of Windows services. COM objects can be created with a variety of programming languages. Object-oriented languages, such as C++, provide programming mechanisms that simplify the implementation of COM objects. The family of COM technologies includes COM+, Distributed COM (DCOM) and ActiveX® Controls.

Microsoft provides COM interfaces for many Windows application programming interfaces such as Direct Show, Media Foundation, Packaging API, Windows Animation Manager, Windows Portable Devices, and Microsoft Active Directory (AD).

COM is used in applications such as the Microsoft Office Family of products. For example COM OLE technology allows Word documents to dynamically link to data in Excel spreadsheets and COM Automation allows users to build scripts in their applications to perform repetitive tasks or control one application from another.

Read More

How can we make Windows API calls in .NET?

Windows API call are not COM based and they are invoked through Platform Invoke Services.StringConversionType is for what type of conversion should take place. Either we can specify Unicode to convert all strings to Unicode values, or Auto to convert strings according to the .NET runtime rules.

There are few thumbrules to make API calls :-
1:- MethodName is the name of the API to call.
2:- DllName is the name of the DLL.
3:- Args are any arguments to the API call.
4:- Type is the return type of the API call.

partial class Form1 : Form
{
[DllImport(“Kernel32.dll”)]
static extern int Sleep(long dwMilliseconds);
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
MessageBox.Show(“Starting of 5000 ms...”);
Sleep(5000);
MessageBox.Show(“End of 5000 ms...”);
}
}
}

Read More

ShFileOperation not working under Wista and Windows7.

I've used ShFileOperation for file operations but was facing some problems and i was not able to understand then i do googling and found the 'cause' of the problems with the SHFileOperation function in Vista . It turns out that this function is not thread safe under Vista. It works fine with earlier operating systems when used in a multi threading application.

Then i got to know about IFileOPeration interface in vista, you can say a replacement of ShFileOperation.

Read More

How to convert 2d array to 1d array ?

Suppose you to insert or get the values from 1d array using 2d dimensions like :-
Insert value at (1,2) and the value is 5 then you have to find the logic to get the index :-
Firstly you have to know the size of 2d array here suppose :- (2x3)

int xPosition = 1;
int yPosition = 2;

3 is ySize of 2d array.
int 1dIndex = (3*xPosition)+ yPosition ;

Insert at this index or get from this index.

Read More

How to convert 3d array to 1d array ?

Suppose you have to insert a value at (2,1,0) and the value is :-5
means firstly you have to find the index through (2,1,0) then you have to insert value 5 at that index .

Logic is :-
Firstly you must have to know the 3d array size suppose here is (3x2x3).
int xPosition = 2;
int yPosition = 1;
int zPosition = 0;

int indexOf1dArray = (xPosition *2*3) + ((yPosition * 3)+zPosition) ;

Using this insert the value at this index indexOf1dArray and if you have to get the value then again find index using this method and then find the value at this place.

Read More

What is the difference between .ToString() and Convert.ToString() ?

int value = 3;
string stringConversion = value.ToString();
string stringConversion = Convert.ToString(value);

We can convert the integer “value ” using “value .ToString()” or “Convert.ToString”
The basic difference between them is “Convert” function handles NULLS .It handles null exception while “value .ToString()”does not it will throw a NULL reference exception error. So as good coding practice using “convert” is always safe.

Happy to code...

Read More

Built-in Code Snippets (C#)

List of built in code snippets -

#if :- Creates a #if directive and a #endif directive.
#region :- Creates a #region directive and a #endregion directive.
~ :- Creates a destructor for the containing class.
checked :- Creates a checked block.
ctor :- Creates a constructor for the containing class.
cw :- Creates a call to Console.WriteLine.
for :- Creates a for loop.
forr :- Creates a for loop that decrements the loop variable after each iteration.
invoke :- Creates a block that safely invokes an event.
iterindex :- Creates a "named" iterator and indexer pair by using a nested class.
lock :- Creates a lock block.
mbox :- Creates a call to System.Windows.Forms.MessageBox.Show. You
may need to add a reference to System.Windows.Forms.dll.
prop :- Creates a automatic property
propg :- Creates a property declaration with only a "get" accessor and a backing field.
propfull : Creates a property with private field.
sim :- Creates a static int Main method declaration.
svm :- Creates a static void Main method declaration.
try :- Creates a try-catch block.
tryf :- Creates a try-finally block.
unsafe :- Creates an unsafe block.
unchecked :- Creates an unchecked block.

Happy to code.

Read More