SQL :To get comma seperated value from a column

DECLARE @stringCommaSeperated VARCHAR(1000)

--Make comma seperated values from a column
SET @stringCommaSeperated = SUBSTRING((SELECT ', ' + s.ColumnName FROM Table_Name s FOR XML PATH('')),2,200000)

SELECT @stringCommaSeperated

Read More

SQL Tools(DROP , TRUNCATE)

DROP TABLE :-
DROP TABLE table_name

DROP DATABASE :-
DROP DATABASE database_name

TRUNCATE TABLE :-
We use truncate statement if we want to delete the values of the table not the table itself. Like :-

TRUNCATE TABLE table_name

DROP INDEX :-
DROP INDEX table_name.index_no

Read More

SQL Tools(Check , DEFAULT, CREATE INDEX)

Check :-

CREATE TABLE SHIP_Orders
(
@O_ID INT NOT NULL,
@ORDER_NAME VARCHAR(25),
@ORDER_ADDRESS VARCHAR(45),
CHECK(@O_ID>2)
)

DEFAULT :-

We can provide default values to the table like in the above table :-

CREATE TABLE SHIP_ORDERS
(
@O_ID INT NOT NULL,
@ORDER_NAME VARCHAR(25) DEFAULT 'SLITTING',
@ORDER_ADDRESS VARCHAR(45) DEFAULT 'DELHI AIRPORT',
CHECK(@O_ID>2)
)

TO ALTER DEFAULT VALUE :-
ALTER TABLE SHIP_ORDERS
ALTER COLUMN @ORDER_NAME SET DEFAULT 'SPLIT'

TO DROP DEFAULT VALUES :-
ALTER TABLE SHIP_ORDERS
ALTER COLUMN @ORDER_NAME DROP DEFAULT

CREATE INDEX :-

CREATE INDEX index_name
ON table_name (column_name)

LIKE :-
CREATE INDEX index
ON SHIP_ORDERS (@ORDER_NAME,@ORDER_ADDRESS)

Read More

SQL Tools(Create DB, Create Table, UNION, UNION ALL, NOT NULL, UNIQUE, PRIMARY KEY, Foreign Key)

To Create Database :-
CREATE DATABASE Db_Name

To Create Table :-

CREATE TABLE Person
(
@ID INT NOT NULL,
@Name VARCHAR(20),
@ADDRESS VARCHAR(255) NOT NULL,
@PS_NO SMALLINT,
@FIRST_CHAR CHAR
)

if a column contains NOT NULL values then it means it does not allow the null values.

Unique :-

CREATE TABLE Person
(
@ID INT NOT NULL UNIQUE,
@Name VARCHAR(20),
@ADDRESS VARCHAR(255) NOT NULL,
@PS_NO SMALLINT,
@FIRST_CHAR CHAR
)

Another way to represent is and the way to define primary key :-

CREATE TABLE Person
(
@ID INT NOT NULL,
@Name VARCHAR(20),
@ADDRESS VARCHAR(255) NOT NULL,
@PS_NO SMALLINT,
@FIRST_CHAR CHAR,
UNIQUE(@ID),
PRIMARY KEY(@ID)
)

A primary key value can not have null values.

If more than one column has unique value then below way we will handle :-
UNIQUE(@ID, @ADDRESS)

FOREIGN KEY :-

If a table does not have primary key then it points to a primary key to another table then that key calls a foreign key for that table like :-

CREATE TABLE Person_Values
(
@P_ID INT NOT NULL,
@ID INT NOT NULL,
@P_Name VARCHAR(20),
@P_ADDRESS VARCHAR(255) NOT NULL,
@P_PS_NO SMALLINT,
@P_FIRST_CHAR CHAR,
FOREIGN KEY (@Id) REFERENCES Persons(@Id)
)

@ID is a primary key of Persons table and now working as a foreign key for Person_Values table.

UNION AND UNION ALL :-
Union will select the distinct values from both columns. In more general form Union with distinct values
Union All select all the values from selected columns.

UNION :-

SELECT column_name From table1
UNION
SELECT column_name From table2

UNION ALL :-

SELECT column_name From table1
UNION ALL
SELECT column_name From table2

Read More

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