Welcome to Microsoft World

Difference between Int32.Parse(), Convert.ToInt32 and Int32.TryParse()

2009-07-09T13:41:00.001+05:30

Int32.Parse Method

-Converts the string representation of a number to its 32-bit signed integer equivalent.
-When s is a null reference, it will throw ArgumentNullException.
-If s is other than integer value, it will throw FormatException.
-When s represents a number out of range, it will throw OverflowException.

Example Program
int quantity;
try
{
quantity = int.Parse(txtQuantity.Text);
}
catch (FormatException)
{
quantity = 0;
}
catch (OverflowException)
{
quantity = 0;
}

Convert.ToInt32(string)

-Converts the specified string representation of 32-bit signed integer equivalent. This calls in turn Int32.Parse () method.
-When s is a null reference, it will return 0 rather than throw ArgumentNullException.
-If s is other than integer value, it will throw FormatException.
-When s represents a number out of range, it will throw OverflowException.

Int32.TryParse Method

-Converts the specified string representation of 32-bit signed integer equivalent to out variable, and returns true if it is parsed successfully, false otherwise.
-When s is a null reference, it will return 0.
-If s is other than an integer value, the out variable will have 0.
-When s represents a number out of range, the out variable will have 0.

int quantity;
if (int.TryParse(txtQuantity.Text, out quantity) == false)
{
quantity = 0;
}

T-SQL Queries Interview Questions

2009-06-23T11:16:00.002+05:30

1. 2 tables
Employee
empid
empname
salary
mgrid

Phone
empid
phnumber

2. Select all employees who doesn't have phone?

SELECT empname FROM Employee
WHERE (empid NOT IN (SELECT DISTINCT empid FROM phone))

3. Select the employee names who is having more than one phone numbers.

SELECT empname FROM employee
WHERE (empid IN (SELECT empid FROM phone GROUP BY empid HAVING COUNT(empid) > 1))

4. Select the details of 3 max salaried employees from employee table.

SELECT TOP 3 empid, salary FROM employee
ORDER BY salary DESC

5. Display all managers from the table. (manager id is same as emp id)

SELECT empname FROM employee
WHERE (empid IN (SELECT DISTINCT mgrid FROM employee))

6. Write a Select statement to list the Employee Name, Manager Name under a particular manager?

SELECT e1.empname AS EmpName, e2.empname AS ManagerName
FROM Employee e1 INNER JOIN Employee e2
ON e1.mgrid = e2.empid
ORDER BY e2.mgrid

7. 2 tables emp and phone.
emp fields are - empid, name
Ph fields are - empid, ph (office, mobile, home). Select all employees who doesn't have any ph nos.

SELECT * FROM employee
LEFT OUTER JOIN phone
ON employee.empid = phone.empid
WHERE (phone.office IS NULL OR phone.office = ' ')
AND (phone.mobile IS NULL OR phone.mobile = ' ')
AND (phone.home IS NULL OR phone.home = ' ')

8. Find employee who is living in more than one city.
Two Tables:
employee
Emp
Empid
empName
Salary

City
Empid
City

9.
SELECT empname, fname, lname
FROM employee
WHERE (empid IN (SELECT empid FROM city GROUP BY empid HAVING COUNT(empid) > 1))

10. Find all employees who is living in the same city. (table is same as above)

SELECT fname FROM employee
WHERE (empid IN (SELECT empid FROM city a WHERE city IN (SELECT city FROM city b GROUP BY city
HAVING COUNT(city) > 1)))

11. There is a table named MovieTable with three columns - moviename,
person and role. Write a query which gets the movie details where Mr.
Amitabh and Mr. Vinod acted and their role is actor.

SELECT DISTINCT m1.moviename FROM MovieTable m1
INNER JOIN MovieTable m2
ON m1.moviename = m2.moviename
WHERE (m1.person = 'amitabh' AND m2.person = 'vinod' OR m2.person = 'amitabh' AND m1.person = 'vinod') AND (m1.role = 'actor') AND (m2.role = 'actor')
ORDER BY m1.moviename

12. There are two employee tables named emp1 and emp2. Both contains
same structure (salary details). But Emp2 salary details are incorrect
and emp1 salary details are correct. So, write a query which corrects
salary details of the table emp2

update a set a.sal=b.sal from emp1 a, emp2 b where a.empid=b.empid

13.Given a Table named “Students” which contains studentid, subjectid and
marks. Where there are 10 subjects and 50 students. Write a Query to
find out the Maximum marks obtained in each subject.

14.In this same tables now write a SQL Query to get the studentid also to
combine with previous results.

15. Three tables – student , course, marks – how do go at finding name of
the students who got max marks in the diff courses.

SELECT student.name, course.name AS coursename, marks.sid,
marks.mark FROM marks
INNER JOIN student
ON marks.sid = student.sid
INNER JOIN course
ON marks.cid = course.cid WHERE (marks.mark = (SELECT MAX(Mark)FROM Marks MaxMark
WHERE MaxMark.cID = Marks.cID))

16. There is a table day_temp which has three columns dayid, day and
temperature. How do I write a query to get the difference of
temperature among each other for seven days of a week?

SELECT a.dayid, a.dday, a.tempe, a.tempe - b.tempe AS Difference FROM day_temp
a INNER JOIN day_temp b
ON a.dayid = b.dayid + 1

OR

Select a.day, a.degree-b.degree from temperature a, temperature b
where a.id=b.id+1

17. There is a table which contains the names like this. a1, a2, a3, a3, a4, a1, a1, a2 and their salaries. Write a query to get grand total salary, and total salaries of individual employees in one query.

SELECT empid, SUM(salary) AS salary
FROM employee
GROUP BY empid WITH ROLLUP
ORDER BY empid

18. How to know how many tables contains empno as a column in a database?

SELECT COUNT(*) AS Counter
FROM syscolumns
WHERE (name = 'empno')

19. Find duplicate rows in a table? OR I have a table with one column which has many records which are not distinct. I need to find the distinct values from that column and number of times it’s repeated.

SELECT sid, mark, COUNT(*) AS Counter
FROM marks
GROUP BY sid, mark
HAVING (COUNT(*) > 1)

20. How to delete the rows which are duplicate (don’t delete both duplicate records).

SET ROWCOUNT 1
DELETE yourtable FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0

DELETE yourtable FROM yourtable a WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0

21. How to find 6th highest salary

SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a
ORDER BY salary

22. Find top salary among two tables

SELECT TOP 1 sal FROM (SELECT MAX(sal) AS sal FROM sal1
UNION
SELECT MAX(sal) AS sal FROM sal2) a
ORDER BY sal DESC

23. Write a query to convert all the letters in a word to upper case

SELECT UPPER('test')

24. Write a query to round up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.

SELECT CEILING (7.1)

25. Write a SQL Query to find first day of month?

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1,GETDATE())) AS FirstDay

Datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms

26. Table A contains column1 which is primary key and has 2 values (1, 2) and Table B contains column1 which is primary key and has 2 values (2, 3). Write a query which returns the values that are not common for the tables and the query should return one column with 2 records.

SELECT tbla.a FROM tbla, tblb
WHERE tbla.a <> (SELECT tblb.a FROM tbla, tblb WHERE tbla.a = tblb.a)
UNION
SELECT tblb.a FROM tbla, tblb
WHERE tblb.a <> (SELECT tbla.a FROM tbla, tblb WHERE tbla.a = tblb.a)

OR (better approach)

SELECT a FROM tbla
WHERE a NOT IN (SELECT a FROM tblb)
UNION ALL
SELECT a FROM tblb
WHERE a NOT IN (SELECT a FROM tbla)

27. There are 3 tables Titles, Authors and Title-Authors (check PUBS db).
Write the query to get the author name and the number of books written by that author, the result should start from the author who has written the maximum number of books and end with the author who has written the minimum number of books.

SELECT authors.au_lname, COUNT(*) AS BooksCount FROM authors INNER JOIN
titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN
titles ON titles.title_id = titleauthor.title_id
GROUP BY authors.au_lname
ORDER BY BooksCount DESC

28. UPDATE emp_master
SET emp_sal = CASE WHEN emp_sal > 0 AND emp_sal <= 20000 THEN (emp_sal * 1.01) WHEN emp_sal > 20000 THEN (emp_sal * 1.02)
END

29. List all products with total quantity ordered, if quantity ordered is null show it as 0.

SELECT name, CASE WHEN SUM(qty) IS NULL THEN 0 WHEN SUM(qty) > 0 THEN SUM(qty) END AS tot FROM [order] RIGHT OUTER JOIN product
ON [order].prodid = product.prodid
GROUP BY name

Result:
coke 60
mirinda 0
pepsi 10

30.ANY, SOME, or ALL?
ALL means greater than every value--in other words, greater than the
maximum value. For example, >ALL (1, 2, 3) means greater than 3.
ANY means greater than at least one value, that is, greater than the minimum. So >ANY (1, 2, 3) means greater than 1.
SOME is an SQL-92 standard equivalent for ANY.

31. IN & = (difference in correlated sub query)

Interview Questions C#

2009-06-23T10:56:00.002+05:30

1. What’s the implicit name of the parameter that gets passed into the class’ set method?
Value, and its datatype depends on whatever variable we’re changing.

2. How do you inherit from a class in C#?
Place a colon and then the name of the base class. Notice that it’s double colon in C++.

3. Does C# support multiple inheritance?
No, use interfaces instead.

4. When you inherit a protected class-level variable, who is it available to?
Classes in the same namespace.

5. Are private class-level variables inherited?
Yes, but they are not accessible, so looking at it you can honestly say that they are not inherited. But they are.

6. Describe the accessibility modifier protected internal.
It’s available to derived classes and classes within the same Assembly (and naturally
from the base class it’s declared in).

7. C# provides a default constructor for me. I write a constructor that takes a string as a parameter, but want to keep the no parameter one. How many constructors should I write?
Two. Once you write at least one constructor, C# cancels the freebie constructor,and now you have to write one yourself, even if there’s no implementation in it.

8. What’s the top .NET class that everything is derived from?
System.Object.

9. How’s method overriding different from overloading?
When overriding, you change the method behavior for a derived class. Overloading simply involves having a method with the same name within the class.

10.What does the keyword virtual mean in the method definition?
The method can be over-ridden.

11. Can you declare the override method static while the original method is non-static?
No, you can’t, the signature of the virtual method must remain the same, only the keyword virtual is changed to keyword override.

12. Can you override private virtual methods?
No, moreover, you cannot access private methods in inherited classes, have to be protected in the base class to allow any sort of access.

13. Can you prevent your class from being inherited and becoming a base class for some other classes?
Yes, that’s what keyword sealed in the class definition is for. The developer trying to derive from your class will get a message: cannot inherit from Sealed class
WhateverBaseClassName. It’s the same concept as final class in Java.

14. Can you allow class to be inherited, but prevent the method from being over-ridden?
Yes, just leave the class public and make the method sealed.

15.What’s an abstract class?
A class that cannot be instantiated. A concept in C++ known as pure virtual method. A class that must be inherited and have the methods over-ridden. Essentially, it’s a blueprint for a class without any implementation.

16.When do you absolutely have to declare a class as abstract (as opposed to free-willed educated choice or decision based on UML diagram)?
When at least one of the methods in the class is abstract. When the class itself is inherited from an abstract class, but not all base abstract methods have been over-ridden.

17.What’s an interface class?
It’s an abstract class with public abstract methods all of which must be implemented in the inherited classes.

18.Why can’t you specify the accessibility modifier for methods inside the interface?
They all must be public. Therefore, to prevent you from getting the false impression that you have any freedom of choice, you are not allowed to specify any accessibility, it’s public by default.

19. Can you inherit multiple interfaces?
Yes, why not.

20. And if they have conflicting method names?
It’s up to you to implement the method inside your own class, so implementation is left entirely up to you. This might cause a problem on a higher-level scale if similarly named methods from different interfaces expect different data, but as far as compiler cares you’re okay.

21.What’s the difference between an interface and abstract class?
In the interface all methods must be abstract; in the abstract class some methods can be concrete. In the interface no accessibility modifiers are allowed, which is ok in abstract classes.

22. How can you overload a method?
Different parameter data types, different number of parameters, different order of parameters.

23. If a base class has a bunch of overloaded constructors, and an inherited class has another bunch of overloaded constructors, can you enforce a call from an inherited constructor to an arbitrary base constructor?
Yes, just place a colon, and then keyword base (parameter list to invoke the appropriate constructor) in the
overloaded constructor definition inside the inherited class.

24.What’s the difference between System.String and System.StringBuilder classes? System.String is immutable; System.StringBuilder was designed with the purpose of having a mutable string where a variety of operations can be performed.

25.What’s the advantage of using System.Text.StringBuilder over System.String? StringBuilder is more efficient in the cases, where a lot of manipulation is done to the text. Strings are immutable, so each time it’s being operated on, a new instance is created.

26. Can you store multiple data types in System.Array?
No.

27.What’s the difference between the System.Array.CopyTo() and System.Array.Clone()? The first one performs a deep copy of the array, the second one is shallow.

28. How can you sort the elements of the array in descending order?
By calling Sort() and then Reverse() methods.

29.What’s the .NET datatype that allows the retrieval of data by a unique key?
HashTable.

30.What’s class SortedList underneath?
A sorted HashTable.

31. Will finally block get executed if the exception had not occurred?
Yes.

32.What’s the C# equivalent of C++ catch (…), which was a catchall statement for any possible exception?
A catch block that catches the exception of type System.Exception. You can also omit the parameter data type in this case and just write catch {}.

33. Can multiple catch blocks be executed?
No, once the proper catch code fires off, the control is transferred to the finally block (if there are any), and then whatever follows the finally block.

34.Why is it a bad idea to throw your own exceptions?
Well, if at that point you know that an error has occurred, then why not write the proper code to handle that error instead of passing a new Exception object to the catch block? Throwing your own exceptions signifies some design flaws in the project.

35.What’s a delegate?
A delegate object encapsulates a reference to a method. In C++ they were referred to as function pointers.

36.What’s a multicast delegate?
It’s a delegate that points to and eventually fires off several methods.

37. How’s the DLL Hell problem solved in .NET?
Assembly versioning allows the application to specify not only the library it needs to run (which was available under Win32), but also the version of the assembly.

38.What are the ways to deploy an assembly?
An MSI installer, a CAB archive, and XCOPY command.

39.What’s a satellite assembly?
When you write a multilingual or multicultural application in .NET, and want to distribute the core applicationseparately from the localized modules, the localized assemblies that
modify the core application are called satellite assemblies.

40.What namespaces are necessary to create a localized application? System.Globalization, System.Resources.

41.What’s the difference between // comments, /* */ comments and /// comments?
Single-line, multi-line and XML documentation comments.

42. How do you generate documentation from the C# file commented properly with a command-line compiler?
Compile it with a /doc switch.

43.What’s the difference between and XML documentation tag?


Single line code example and multiple-line code example.

44. Is XML case-sensitive? 
Yes, so  and  are different elements.

45.What debugging tools come with the .NET SDK? 
CorDBG – command-line debugger, and DbgCLR – graphic debugger. Visual Studio .NET uses the DbgCLR. To use CorDbg, you must compile the original C# file using the /debug switch.

46.What does the This window show in the debugger? 
It points to the object that’s pointed to by this reference. Object’s instance data is shown.

47.What does assert() do? 
In debug compilation, assert takes in a Boolean condition as a parameter, and shows the error dialog if the condition is false. The program proceeds without any interruption if the condition is true.

48.What’s the difference between the Debug class and Trace class?
Documentation looks the same. Use Debug class for debug builds, use Trace class for both debug and release builds.

49.Why are there five tracing levels in System.Diagnostics.TraceSwitcher? 
The tracing dumps can be quite verbose and for some applications that are constantly running you run the risk of overloading the machine and the hard drive there. Five levels range from None to Verbose, allowing to fine-tune the tracing activities.

50.Where is the output of TextWriterTraceListener redirected? 
To the Console or a text file depending on the parameter passed to the constructor.

51. How do you debug an ASP.NET Web application? 
Attach the aspnet_wp.exe process to the DbgClr debugger.

52.What are three test cases you should go through in unit testing?
Positive test cases (correct data, correct output), negative test cases (broken or missing data, proper handling), exception test cases (exceptions are thrown and caught properly).

53. Can you change the value of a variable while debugging a C# application? 
Yes, if you are debugging via Visual Studio.NET, just go to Immediate window.

54. Explain the three services model (three-tier application).
Presentation (UI), business (logic and underlying code) and data (from storage or other sources).

55.What are advantages and disadvantages of Microsoft-provided data provider classes in ADO.NET? 
SQLServer.NET data provider is high-speed and robust, but requires SQL Server license purchased from Microsoft. OLE-DB.NET is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix, but it’s a .NET layer on top of OLE layer, so not the fastest thing in the world. ODBC.NET is a deprecated layer provided for backward compatibility to ODBC engines.

56.What’s the role of the DataReader class in ADO.NET connections?
It returns a read-only dataset from the data source when the command is executed.

57.What is the wildcard character in SQL? 
Let’s say you want to query database with LIKE for all employees whose name starts
with La. The wildcard character is %, the proper query with LIKE would involve ‘La%’.

58. Explain ACID rule of thumb for transactions. 
Transaction must be Atomic (it is one unit of work and does not dependent on previous and following transactions), Consistent (data is either committed or roll back, no “in-between” case where something has been updated and something hasn’t), Isolated (no transaction sees the intermediate results of the current transaction), Durable (the values persist if the data had been committed even if the system crashes right after).

59.What connections does Microsoft SQL Server support? 
Windows Authentication (via Active Directory) and SQL Server authentication (via
Microsoft SQL Server username and passwords).

60.Which one is trusted and which one is untrusted? 
Windows Authentication is trusted because the username and password are checked with the Active Directory, the SQL Server authentication is untrusted, since SQL Server is the only verifier participating in the transaction.

61.Why would you use untrusted verificaion? 
Web Services might use it, as well as non-Windows applications.

62.What does the parameter Initial Catalog define inside Connection String? 
The database name to connect to.

63.What’s the data provider name to connect to Access database?
Microsoft.Access.

64.What does Dispose method do with the connection object?
Deletes it from the memory.

65.What is a pre-requisite for connection pooling? 
Multiple processes must agree that they will share the same connection, where every
parameter is the same, including the security settings.



Sql Interview Questions
2009-06-05T12:39:00.000+05:30
What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. 

What is normalization?

Database normalization, is a data design and organization process, applied to data structures based on rules that helps build relational databases. In relational database design, the process of organizing data is to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What are different normalization forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

 

What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

 

What is the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What are the different index configurations a table can have?
A table can have one of the following index configurations:

No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes

What is cursors?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor we need to perform some steps in the following order:

Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor

What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.
DBCC CHECKALLOC - To check that all pages in a db are correctly allocated.
DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.
Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

What are different type of Collation Sensitivity?
Case sensitivity
A and a, B and b, etc.

Accent sensitivity
a and á, o and ó, etc.

Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is a NOLOCK?
Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).

What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.

DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back using logs.
DELETE is DML Command.
DELETE does not reset identity of the table.

Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What types of Joins are possible with Sql Server?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. HAVING criteria is applied after the the grouping of rows has occurred.

What is sub-query? Explain properties of sub-query.
Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

Properties of Sub-Query
A subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator, and
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.

What are types of sub-queries?
Single-row subquery, where the subquery returns only one row.
Multiple-row subquery, where the subquery returns multiple rows,.and
Multiple column subquery, where the subquery returns multiple columns.

What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is User Defined Functions?
User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

What kind of User-Defined Functions can be created?
There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.

Scalar User-Defined Function
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

Inline Table-Value User-Defined Function
An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement Table-Value User-Defined Function
A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number.both on client and the server.

What are the authentication modes in SQL Server? How can it be changed?
Windows mode and mixed mode (SQL & Windows).

To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

Where are SQL server users names and passwords are stored in sql server?
They get stored in master db in the sysxlogins table.

Which command using Query Analyzer will give you the version of SQL server and operating system?

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), 

What is SQL server agent?
SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is @@ERROR?
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

What is Raiseerror?
Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

What is log shipping?
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

What is the difference between a local and a global variable?
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What command do we use to rename a db?
sp_renamedb ‘oldname’ , ‘newname’
If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

What is sp_configure commands and set commands?
Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

What are the different types of replication? Explain.
The SQL Server 2000-supported replication types are as follows:

    * Transactional
    * Snapshot
    * Merge

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.

What are the OS services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)

What are three SQL keywords used to change or set someone’s permissions?
GRANT, DENY, and REVOKE.

What does it mean to have quoted_identifier on? What are the implications of having it off?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

What is the STUFF function and how does it differ from the REPLACE function?
STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.
REPLACE function to replace existing characters of all occurance. Using this syntax REPLACE(string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?

SELECT * 

    FROM table1

SELECT COUNT(*) 

    FROM table1

SELECT rows 

    FROM sysindexes 

    WHERE id = OBJECT_ID(table1) 

        AND indid < 2

How to rebuild Master Database?
Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.
In the Rebuild Master dialog box, click Browse.
In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.
In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.
To continue, you may need to stop a server that is running.
Source: http://msdn2.microsoft.com/en-us/library/aa197950(SQL.80).aspx

What is the basic functions for master, msdb, model, tempdb databases?
The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.

What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is data integrity? Explain constraints?
Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What are the properties of the Relational tables?
Relational tables have six properties:

    * Values are atomic.
    * Column values are of the same kind.
    * Each row is unique.
    * The sequence of columns is insignificant.
    * The sequence of rows is insignificant.
    * Each column must have a unique name.

What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

How to get @@error and @@rowcount at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset.
And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers, the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

What is a Scheduled Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that.

What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

How do you load large data to the SQL server database?
BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.

Can we rewrite subqueries into simple select statements or with joins?
Subqueries can often be re-written to use a standard outer join, resulting in faster performance. As we may know, an outer join uses the plus sign (+) operator to tell the database to return all non-matching rows with NULL values. Hence we combine the outer join with a NULL test in the WHERE clause to reproduce the result set without using a sub-query.

Can SQL Servers linked to other servers like Oracle?
SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

How to know which index a table is using?
SELECT table_name,index_name FROM user_constraints

How to copy the tables, schema and views from one SQL server to another?
Microsoft SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets user extract, transform, and consolidate data from disparate sources into single or multiple destinations.

What is Self Join?
This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.

What is Cross Join?
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Which virtual table does a trigger use?
Inserted and Deleted.

List few advantages of Stored Procedure.

    * Stored procedure can reduced network traffic and latency, boosting application performance.
    * Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
    * Stored procedures help promote code reuse.
    * Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
    * Stored procedures provide better security to your data.

What is DataWarehousing?

    * Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
    * Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
    * Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting;
    * Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.

What is OLTP(OnLine Transaction Processing)?
In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

How do SQL server 2000 and XML linked? Can XML be used to access data?
FOR XML (ROW, AUTO, EXPLICIT)
You can execute SQL queries against existing relational databases to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures. To retrieve XML results, use the FOR XML clause of the SELECT statement and specify an XML mode of RAW, AUTO, or EXPLICIT.

OPENXML
OPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment.

What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.


Nullable Types in C#
2009-05-26T10:13:00.002+05:30
In 1.x days we cannot assign a null value to value types.Assigning a null value will throw an error.

int mobile = null; //Will throw an error

This is an inconvenience whenever we are retrieving a value from a database that can allow nulls and assigning to .net variable. But this inconvenience is no more now because of the Nullable types. A nullable type can be declared similar to normal variable but with ? modifier at the end of keyword.

i.e., a valid nullable type declaration will be, 

int? mobile = null; 

Alternatively we can declare like,

Nullable mobile = null;

int? will be the shorthand notation of Nullable.

Things to know about Nullable Types:

Nullabe types are instances of System.Nullable struct.Nullable type structure differs from normal type by having flag extra to indicate whether it contain null value.Additional it have 2 other properties which we will be looking in detail in this article.

Working with Nullable Types:
How to retrieve the value from a nullable type?

As we discussed early it has 2 other property called Has Value and Value.
Value:

This property is a read only property which retrieves the value associated with the type.This property will throw an exception when it is accessed with a null value.

int? mobile = null;
int my mobile = mobile.Value;

The above line will throw a exception,

"Invalid operation exception"

Nullable object should have value.

HasValue:

This property returns a Boolean indicating whether the variable contains a value.
So the above exception can be prevented by writing the code like,

if (mobile.HasValue)
{
   int mymobile = mobile.Value;
}

Consider a situation like,

int? n1 = 2;
int n2 = 3;

Now what will be output type of (n1*n2) ?

int sum = n1 * n2;

The above operation will throw an error.

This operation gives a result which a nullable type can hold and thus it can be assigned to a variable like,

int? sum = n1 * n2;

We can call these operators as Lifted operator’s i.e., it will lift the non nullable type to nullable type. Assigning null to any of the above variable will result null. The same behavior applies to + and – operators.

Getting a default value with Nullable Types:

int? n1 = null;

int n2 = 3;

(n1 ?? 10) will return the value 10.This will be useful when we are fetching a allow null field from database and assigning to a non nullable type or it can be used in a operation like below. Hence we can assign a default value.

int product = (n1 ?? 10) * n2;

Now product will hold 30 since (n1 ?? 10) will return 10. 

Note:

We cannot create nullable types with reference types.


Nested Classes in C#
2009-04-24T13:01:00.002+05:30
Introduction

In this short tutorial we are going to discuss nested classes. Nested classes are classes within classes. Let us try and understand better by writing simple programs.

P1.cs

class OurOuterClass
{
    public static void Main()
    {
        System.Console.WriteLine("OurOuterClass");
    } 
}

Output:
OurOuterClass

The above program compiles and runs successfully to give the desired output. The above program consists of one single class named OurOuterClass. OK! So let's try another class within OurOuterClass.

P2.cs

class OurOuterClass
{
    public static void Main()
    {
        System.Console.WriteLine("OurOuterClass");
    }

    class OurNestedClass
    {

    } 
}

Output:

OurOuterClass

The above program compiles and runs successfully to give the desired output. The above program consists of one single class named OurOuterClass. Not only that! OurOuterClass contains OurNestedClass within it. That's the nested class.

Let's understand how a nested class looks through one more example.

P3.cs

class Demo
{
    public static void Main()
    {
        System.Console.WriteLine("DemoClass");
    }
}

class OuterClass
{
    class NestedClass
    {

    }
}

Output:

DemoClass

The above program compiles and runs successfully to give the desired output. The above program consists of three classes: the Demo class that consists of the Main method, the outer class named OuterClass, and the nested Class named NestedClass within the outer class.

Now let's explore the nested classes. Can we create an object of NestedClass from within the Demo class? Let's find out.

P4.cs

class Demo
{
    public static void Main()
    {
        System.Console.WriteLine("DemoClass");
        NestedClass obj = new NestedClass();
    }
}

class OuterClass
{
    class NestedClass
    {

    }
}

Output:

P4.cs(6,7): error CS0246: The type or namespace name 'NestedClass' could not 
be found (are you missing a using directive or an assembly reference?)

P4.cs(6,29): error CS0246: The type or namespace name 'NestedClass' could not 
be found (are you missing a using directive or an assembly reference?)
Error! That's because the Demo class is not allowed to use the class NestedClass directly as it is contained within the class OuterClass. OK! So can we create an object of NestedClass from within the OuterClass? Let's find out.

P5.cs

class Demo
{
    public static void Main()
    {
        System.Console.WriteLine("DemoClass");
    }
}

class OuterClass
{
    class NestedClass
    {

    }
    public void CreateObjectOfNestedClass()
    {
        NestedClass obj = new NestedClass();
    }
}

Output:

DemoClass

The above program compiles and runs successfully to give the desired output. The above program shows that we can create an object of NestedClass from within the OuterClass. In the above program we have defined a new method CreateObjectOfNestedClass() within the OuterClass. Inside this method we have created the object of NestedClass.

What if we want to get a reference to a NestedClass object obj from within the Demo class? Well, we need to modify the function CreateObjectOfNestedClass() to return an object of type NestedClass. Then in the Main() block of the Demo class, we need to create an object of OuterClass. Then we need to invoke the function CreateObjectOfNestedClass() using the object from OuterClass. The CreateObjectOfNestedClass() returns the object obj of NestedClass. We catch this object in the reference of type NestedClass, created in the Main() block of the Demo class. This is what the following program does:

P6.cs
class Demo
{
    public static void Main()
    {
        System.Console.WriteLine("DemoClass");

        OuterClass oc = new OuterClass();
        NestedClass reference = oc.CreateObjectOfNestedClass();
    }
}

class OuterClass
{
    class NestedClass
    {

    }
    public void CreateObjectOfNestedClass()
    {
        NestedClass obj = new NestedClass();
        return obj;
    }
}

Output:

P6.cs(9,7): error CS0246: The type or namespace name 'NestedClass' could not 
be found (are you missing a using directive or an assembly reference?)
Error! We did everything according to our initial plan. Then where did we fail? Well we failed at line 9 where we are referencing the nested class directly from Main() in the Demo class. Didn't we learn from programs P3.cs and P4.cs that the Demo class is not allowed to use NestedClass directly, as it is contained within the OuterClass? Certainly we did. So what's the solution to this problem? We need to know the right way to reference the NestedClass from Main() inside Demo. Well we need to use the fully qualified name for NestedClass within Main() in the Demo class, which is done with the following program.

P7.cs

class Demo
{
    public static void Main()
    {
        System.Console.WriteLine("DemoClass");

        OuterClass oc = new OuterClass();
        OuterClass.NestedClass reference = oc.CreateObjectOfNestedClass();
    }
}

class OuterClass
{
    class NestedClass
    {

    }
    public NestedClass CreateObjectOfNestedClass()
    {
        NestedClass obj = new NestedClass();
        return obj;
    }
}

Output:

P7.cs(19,23): error CS0050: Inconsistent accessibility: return type 
'OuterClass.NestedClass' is less accessible than method 
'OuterClass.CreateObjectOfNestedClass()'

P7.cs(15,10): (Location of symbol related to previous error)
Error again! Now where did we go wrong? The compiler error tells us that we need to make the class NestedClass public.

P8.cs

class Demo
{
    public static void Main()
    {
        System.Console.WriteLine("DemoClass");

        OuterClass oc = new OuterClass();
        OuterClass.NestedClass reference = oc.CreateObjectOfNestedClass();
    }
}

class OuterClass
{
    public class NestedClass
    {

    }
    public NestedClass CreateObjectOfNestedClass()
    {
        NestedClass obj = new NestedClass();
        return obj;
    }
}

Output:

DemoClass

Bingo! The above program compiles and runs successfully to give the desired output. We know that we can reference the NestedClass from Main() in Demo by using its fully qualified name, and making the NestedClass public. Then can we do away with the extra headache of creating object oc of OuterClass and invoking oc.CreateObjectOfNestedClass()? Yes, certainly we can get rid of this headache. We can get rid of CreateObjectOfNestedClass() defined within the NestedClass class. This is how we do so.

P9.cs
class Demo
{
    public static void Main()
    {
        System.Console.WriteLine("DemoClass");
        OuterClass.NestedClass reference = new OuterClass.NestedClass();
    }
}

class OuterClass
{
    public class NestedClass
    {

    }
}

Output:

DemoClass

The above program compiles and runs successfully to give the desired output. Wasn't that simple?

Nested Classes and Static Members

Let us see how nested classes behave with the static members.

P10.cs
class Demo
{
    public static void Main()
    {
        System.Console.WriteLine(OuterClass.NestedClass.x);     
    }
}

class OuterClass
{
    public class NestedClass
    {
        public static int x = 100;
    }
}

Output:

100

The instance class is not required to access static variables. The rules that apply to standalone classes in case of static variables also apply to nested classes. For nested classes, fully qualified names of nested classes must be used to access the static variables as shown by the above program.

Nested Classes and Members in Containers

Let us see how nested classes behave with the members in containing classes.

P11.cs

class Demo
{
  public static void Main()
  {
      System.Console.WriteLine("Demo");     
  }
}
 
class OuterClass
{
   public int y = 100;
 
   public class NestedClass
   {
      public void abc()
      {
          System.Console.WriteLine(y);     
      }
   }
}

Output:

P11.cs(17,35): error CS0038: Cannot access a nonstatic member of outer type

'OuterClass' via nested type 'OuterClass.NestedClass'
Error! That's because the NestedClass cannot access the non-static, member y declared in the OuterClass. The members of the OuterClass are not directly available to the NestedClass. Well, then how can the NestedClass access the members of the containing class? This is how:

P12.cs

class Demo
{
  public static void Main()
  {
      System.Console.WriteLine("Demo");
      OuterClass.NestedClass.abc();     
  }
}
 
class OuterClass
{
   public static int y = 100;
 
   public class NestedClass
   {
      public static void abc()
      {
              System.Console.WriteLine(OuterClass.y);     
      }
   }
}

Output:

Demo

100

The above program compiles and runs successfully to give the desired output. NestedClass can access static member y declared in the OuterClass. Declaring a member as static in the OuterClass makes it directly available in the NestedClass.

It is not always necessary to declare an outer class member as static to make it directly available in the nested class. There is one more way by which the nested class can directly access non-static members of the container class. This is how:

P13.cs

class Demo
{
    public static void Main()
    {
        System.Console.WriteLine("Demo");
        OuterClass.NestedClass.abc();     
    }
}

class OuterClass
{
    public int y = 100;

    public class NestedClass
    {
        public static void abc()
        {
            OuterClass oc = new OuterClass();
            System.Console.WriteLine(oc.y);     
        }
    }
}

Output:

Demo

100

The above program compiles and runs successfully to give the desired output. The NestedClass can access the non static members of the container class via objects created in the nested class. In the above program the function abc() is a member of the nested class. Inside the function abc() we have created the object of the outer class and then we are accessing the non static member y of the OuterClass.

Constructors and Nested Classes

Let us try to understand (through small examples) how constructors in outer and nested classes behave.

P14.cs
class Demo
{
    public static void Main()
    {
        System.Console.WriteLine("Demo");
        OuterClass oc = new OuterClass();    
    }
}

class OuterClass
{
    public OuterClass()
    {
        System.Console.WriteLine("OuterClass");     
    }

    public class NestedClass
    {
        public NestedClass()
        {
            System.Console.WriteLine("NestedClass");     
        }
    }
}

Output:

Demo

OuterClass

The above program compiles and runs successfully to give the desired output. An attempt was made to create an object of OuterClass. Therefore the constructor of OuterClass got executed. There is no reason why the constructor of NestedClass should get executed.

P15.cs

class Demo
{
    public static void Main()
    {
        System.Console.WriteLine("Demo");
        OuterClass.NestedClass nc = new OuterClass.NestedClass();    
    }
}

class OuterClass
{
    public OuterClass()
    {
        System.Console.WriteLine("OuterClass");     
    }

    public class NestedClass
    {
        public NestedClass()
        {
            System.Console.WriteLine("NestedClass");     
        }
    }
}

Output:

Demo

NestedClass

The above program compiles and runs successfully to give the desired output. An attempt was made to create an object of NestedClass. Therefore the constructor of NestedClass got executed. There is no reason why the constructor of OuterClass should get executed.

Inheritance and Nested Classes

We can inherit classes that have already inherited classes. This is how:

P16.cs

class OuterClass
{
    public OuterClass()
    {
        System.Console.WriteLine("OuterClass");     
    }

    public class NestedClass
    {
        public NestedClass()
        {
            System.Console.WriteLine("NestedClass");     
        }
    }
}

class Demo : OuterClass
{
    public static void Main()
    {
        Demo dc = new Demo();    
        System.Console.WriteLine("Demo");
    }
}

Output:

OuterClass

Demo

The above program compiles and runs successfully to give the desired output. The base class constructor gets executed first and then the derived class's constructor gets executed.

A final question: can we have a NestedClass inheriting from the OuterClass? Well yes! Check it out.

P17.cs

class Demo
{
    public static void Main()
    {
        System.Console.WriteLine("Demo");
        OuterClass.NestedClass.abc();     
    }
}

class OuterClass
{
    private int y = 100;

public class NestedClass : OuterClass
{
    public static void abc()
    {
        OuterClass oc = new OuterClass();
        System.Console.WriteLine(oc.y);     
    }
}
}

Output:

Demo

100

The above program compiles and runs successfully to give the desired output. It is perfectly fine to have a nested class that is derived from the outer class.


Uploading-Images-to-a-Database-C#
2009-02-05T11:48:00.002+05:30
SQl Table Creation

CREATE TABLE [dbo].[image] (
[img_pk] [int] IDENTITY (1, 1) NOT NULL ,
[img_name] [varchar] (50) NULL ,
[img_data] [image] NULL ,
[img_contenttype] [varchar] (50) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[image] WITH NOCHECK ADD 
CONSTRAINT [PK_image] PRIMARY KEY NONCLUSTERED 
(
[img_pk]
) ON [PRIMARY] GO

in aspx page:

form id="form1" name="form1" enctype="multipart/form-data" runat="server"
Image Uploader
Enter A Name: input type="text" id="txtImgName" runat="server">
asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Required"
ControlToValidate="txtImgName"> /asp:RequiredFieldValidator>
br>
Select File To Upload:
input id="UploadFile" type="file" runat="server"
asp:Button ID="UploadBtn" Text="Upload Me!" OnClick="UploadBtn_Click" runat="server"
/asp:Button
/form

In .cs File 

using System;
using System.Configuration;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.IO;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;


public partial class UploadImage : System.Web.UI.Page
{
    public UploadImage() { }
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    public void UploadBtn_Click(object sender, System.EventArgs e)
    {
        if (Page.IsValid) //save the image
        {
            Stream imgStream = UploadFile.PostedFile.InputStream;
            int imgLen = UploadFile.PostedFile.ContentLength;
            string imgContentType = UploadFile.PostedFile.ContentType;
            string imgName = txtImgName.Value;
            byte[] imgBinaryData = new byte[imgLen];
            int n = imgStream.Read(imgBinaryData, 0, imgLen);

            int RowsAffected = SaveToDB(imgName, imgBinaryData, imgContentType);
            if (RowsAffected > 0)
            {
                Response.Write("
The Image was saved");
            }
            else
            {
                Response.Write("
An error occurred uploading the image");
            }

        }
    }
    private int SaveToDB(string imgName, byte[] imgbin, string imgcontenttype)
    {
        //use the web.config to store the connection string
        string connectionString = @"Data Source =DEIVAMURUGAN\SQLEXPRESS ;Initial Catalog=master;Integrated Security=True";
        SqlConnection connection = new SqlConnection(connectionString);

        //SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["conString"]);
        SqlCommand command = new SqlCommand( "INSERT INTO Image (img_name,img_data,img_contenttype) VALUES ( @img_name, @img_data,@img_contenttype )", connection );

        SqlParameter param0 = new SqlParameter( "@img_name", SqlDbType.VarChar,50 );
        param0.Value = imgName;
        command.Parameters.Add( param0 );

        SqlParameter param1 = new SqlParameter( "@img_data", SqlDbType.Image );
        param1.Value = imgbin;
        command.Parameters.Add( param1 );

        SqlParameter param2 = new SqlParameter( "@img_contenttype", SqlDbType.VarChar,50 );
        param2.Value = imgcontenttype;
        command.Parameters.Add( param2 );

        connection.Open();
        int numRowsAffected = command.ExecuteNonQuery();
        connection.Close();

        return numRowsAffected;
    }
}


Query to Get All the Tables From Database
2009-01-21T16:43:00.002+05:30
SELECT * FROM information_schema.tables where table_type= 'BASE TABLE'
This Query will return TABLE_CATALOG, TABLE_SCHEMA ,TABLE_NAME and TABLE_TYPE

SELECT name FROM sysobjects where type ='U'
This Query will return Only TABLE_NAME

SELECT * FROM sys.Objects where type = 'U'
OR
SELECT * FROM sysObjects where Xtype='U'

for User Tables
Xtype = 'U'

for View
Xtype='V'

for procedures
Xtype='P'

for Scalar Functions
Xtype = 'FN'

This Query will return All the Values Regarding to the particular table.

You can change and use it for getting nth highest salary from Employee table as follows

SELECT TOP 1 salary 
    FROM (
    SELECT DISTINCT TOP n salary 
        FROM employee
        ORDER BY salary DESC) a 
    ORDER BY salary
Note:
where n > 1 (n is always greater than one)

Select 2nd Highest Salary

SELECT MAX(Salary) FROM Employee WHERE Salary <> (SELECT MAX(Salary) FROM Employee) 

select MAX(Salary) from Employee where Salary!=(select MAX(Salary) from Employee)

Select MAX(Salary) From Employee where Salary not in ( Select MAX (Salary) From Employee)

SELECT TOP 1 Salary FROM (SELECT DISTINCT TOP 2 Salary FROM employee ORDER BY Salary DESC) a ORDER BY Salary

SELECT DISTINCT TOP 2 Salary FROM employee ORDER BY Salary DESC


Using dropdownlist in user control and to access the dropdownlist control value in .aspx page
2008-12-17T18:04:00.002+05:30
A Common requirement we have would be to have a Dropdownlist of items which would be common across all the pages (top navigation or in the left navigation).

Retrieving the selected item in a dropdownlist which resides in the same page, is pretty straight forward dropdownlist1.selecteditem.value.

However, when it is in a usercontrol, its pretty difficult to retrieve it since you will get the "Object Reference not set to an instance" error if you try the normal method. Even if you create an instance of the usercontrol and try to access its dropdownlist, you will get the above error.

The following code demonstrates how to overcome this problem.

UserControl objCtrl = ((UserControl)(FindControl("Id of the Usercontrol as declared in the container page")));
DropDownList ddl = ((DropDownList)(objCtrl.FindControl("Id of the Dropdownlist control in the usercontrol")));

Response.Write(ddl.SelectedItem.Value);
Thats it and you can access the value.

Cheers and Happy Programming!


How ASP.NET databinding deals with Eval() and Bind() statements
2008-12-16T16:25:00.000+05:30
A recent question on one of our internal mailing lists asked where the definition for the Bind() method is located in ASP.NET. The asker had located the definition of the Eval() method on the TemplateControl class, but the Bind() method was nowhere to be found, even using Reflector. 

If you're familiar with databinding in ASP.NET 2.0, you probably know that for read-only values such as Labels you can use the Eval() statement, and for read-write values such as TextBoxes (also known as "two-way databinding") you can use the Bind() statement. Where does that Bind() statement come from? 

To the surprise of many readers, there isn’t a bind method in ASP.NET! When ASP.NET parses your file and sees you're using a databinding expression (in the angle-bracket-percent-pound format, "<%# %>") it has special-case code to parse for the Bind syntax and generates some special code for it. When you use <%# Bind("Name") %> it's not a real function call. If ASP.NET parses the code and detects a Bind() statement, it splits the statement into two parts. The first part is the one-way databinding portion, which ends up being just a regular Eval() call. The second part is the reverse portion, which is typically some code along the lines of "string name = TextBox1.Text" that grabs the value back out from where it was bound.

Non-Bind() databinding statements are literal code (we use CodeSnippetExpressions in CodeDom), so arbitrary code in the language of your choice is allowed. However, because ASP.NET has to parse Bind() statements, two-way databinding doesn’t support anything other than Bind(). For example, the following syntax is invalid because it tries to invoke arbitrary code and use Bind() at the same time:
                <%# FormatNameHelper(Bind("Name")) %>
The only formats supported in two-way databinding are Bind("field") and Bind("field", "format string {0}"). There are some very minor variations of these syntax examples, such as allowing use of single quotes and not just double quotes. Since some languages supported by ASP.NET favor one format over the other, we have to support both formats, even though the language you're using may support only one.

The moral of the story is: There are some things that Reflector won't tell you.


Types Of  Parameters In C#
2008-12-02T11:43:00.001+05:30
Parameters are means: way of passing values to a method.The syntax of passing parameter in C# is:

[modifier] DataType ParameterName

There are four different ways of passing parameters to a method in C#. The four different types of parameters are:

1. Value 
2. Out 
3. Ref 
4. Params

1. Value parameters This is the default parameter type in C#. If the parameter does not have any modifier it is "value" parameter by default.When we use "value" parameters the actual value is passed to the function,which means changes made to the parameter is local to the function and is not passed back to the calling part.

using System;
class ParameterInCSharp
{
       static void ParameterType(int intValueParameter)
      {
             intValueParameter = 100;
      }
      static void Main()
      {
              int intAnotherValue = 5;
              ParameterType(intAnotherValue);
              Console.WriteLine(intAnotherValue);                  
      }
}

Output of the above program would be 5.Eventhough the value of the parameter Param1 is changed within MyMethod it is not passed back to the calling part since value parameters are input only.

2. Out parameters "out" parameters are output only parameters meaning they can only passback a value from a function.We create a "out" parameter by preceding the parameter data type with the out modifier. When ever a "out" parameter is passed only an unassigned reference is passed to the function.

using System;
class ParameterInCSharp
{
         static void ParameterType(out int outParameter)
         {
                  outParameter = 100;
         }
         static void Main()
         {
                  int intAnotherValue = 5;
                  ParameterType(intAnotherValue);
                  Console.WriteLine(intAnotherValue);             
         }
}

Output of the above program would be 100 since the value of the "out" parameter is passed back to the calling part.

Note:

The modifier "out" should precede the parameter being passed even in the calling part. "out" parameters cannot be used within the function before assigning a value to it. A value should be assigned to the "out" parameter before the method returns.

3. Ref parameters "ref" parameters are input/output parameters meaning they can be used for passing a value to a function as well as to get back a value from a function.We create a "ref" parameter by preceding the parameter data type with the ref modifier. When ever a "ref" parameter is passed a reference is passed to the function.

using System;
class ParameterInCSharp
{
         static void ParameterType(ref int refParameter)
         {
                  refParameter = refParameter + 10;
         }
         static void Main()
         {
                  int intAnotherValue=5;
                  ParameterType(intAnotherValue);
                  Console.WriteLine(intAnotherValue);              
         }
}

Output of the above program would be 15 since the "ref" parameter acts as both input and output.

Note:

The modifier "ref" should precede the parameter being passed even in the calling part. "ref" parameters should be assigned a value before using it to call a method. 4.Params parameters "params" parameters is a very useful feature in C#. "params" parameter are used when a variable number of arguments need to be passed.The "params" should be a single dimensional or a jagged array.

using System;
class ParameterTest
{
     static int ParameterType(params int[] paramsParameter)
     {
        int testParameter = 0;
        foreach(int intParams in paramsParameter)
        {
            testParameter = testParameter + intParams;
        }
        return testParameter;
     }
     static void Main()
     {
        Console.WriteLine(ParameterType(1,2,3));
     }
}

Output of the above program would be 6.

Note:

The value passed for a "params" parameter can be either comma separated value list or a single dimensional array. "params" parameters are input only.


MultiSelectDropDownList
2008-12-01T16:26:00.002+05:30


In CSS
.Default
{
 font-size:smaller;
 font-family:Tahoma; 
}

table 
{ 
 padding:0px;
 border-collapse:collapse; 
}

.DropDownLook
{
 padding:0px;
 border-style:solid;
 border-width:1px;
}

.DivClose
{
 display:none;
 position:absolute;
 width:250px;
 height:220px;
 border-style:solid;
 border-color:Gray; 
 border-width:1px;
 background-color:#99A479;
}

.LabelClose
{
 vertical-align:text-top; 
 position:absolute;
 bottom:0px;
 font-family:Verdana;
}

.DivCheckBoxList
{
 display:none;
 background-color:White;
 width:250px;
 position:absolute;
 height:200px;
 overflow-y:auto; 
 overflow-x:hidden;
 border-style:solid;
 border-color:Gray; 
 border-width:1px;
}

.CheckBoxList
{
 position:relative;
 width:250px;
 height:10px; 
 overflow:scroll;
 font-size:small;
}

In .CS

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            lstMultipleValues.Attributes.Add("onclick", "FindSelectedItems(this," + txtSelectedMLValues.ClientID + ");");
            SetMultiValue();
            lblDescription.Text = "On click of dropdown, the multi select option will appear.
 Once you remove focus from the dropdown"
            + " area it will get auto collapsed. 
In case needed, you can go ahead and close it with the close ('X') provided at the bottom.
";
        }
    }

    protected void SetMultiValue()
    {
        DataTable dt = new DataTable("Table1");
        
        DataColumn dc1 = new DataColumn("Text");
        DataColumn dc2 = new DataColumn("Value");
        dt.Columns.Add(dc1);
        dt.Columns.Add(dc2);

        //To get enough data for scroll
        dt.Rows.Add("Bangalore", 1);
        dt.Rows.Add("Kolkata", 2);
        dt.Rows.Add("Pune", 3);
        dt.Rows.Add("Mumbai", 4);
        dt.Rows.Add("Noida", 5);
        dt.Rows.Add("Gurgaon", 6);
        dt.Rows.Add("Hyderabad", 7);
        dt.Rows.Add("Chennai", 8);
        dt.Rows.Add("Jaipur", 8);
        dt.Rows.Add("Patna", 8);
        dt.Rows.Add("Ranchi", 8);

        lstMultipleValues.DataSource = dt;
        lstMultipleValues.DataTextField = "Text";
        lstMultipleValues.DataValueField = "Value";
        lstMultipleValues.DataBind();
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        // Text showed that were selected
        lblTextSelected.Text = "Submitted Values: " + txtSelectedMLValues.Value;

        // One can loop through the checkboxlist control to get the Values too for the text selected if required.
    }
}

In JavaScript
       
        var timoutID;
     function ShowMList()
     {
         var divRef = document.getElementById("divCheckBoxList");
         divRef.style.display = "block";
         var divRefC = document.getElementById("divCheckBoxListClose");
         divRefC.style.display = "block";
     }
     
     function HideMList()
     {
         document.getElementById("divCheckBoxList").style.display = "none";   
         document.getElementById("divCheckBoxListClose").style.display = "none";   
     }
     
        function FindSelectedItems(sender,textBoxID)
        {       
            var cblstTable = document.getElementById(sender.id);
            var checkBoxPrefix = sender.id + "_";
            var noOfOptions = cblstTable.rows.length;
            var selectedText = "";
            for(i=0; i < noOfOptions ; ++i)
            {
                if(document.getElementById(checkBoxPrefix+i).checked)
                {
                    if(selectedText == "")                
                        selectedText = document.getElementById(checkBoxPrefix+i).parentNode.innerText;
                    else
                        selectedText = selectedText + "," + document.getElementById(checkBoxPrefix+i).parentNode.innerText;
                }
            }    
            document.getElementById(textBoxID.id).value = selectedText;   
        }

In .ASPX

asp:UpdatePanel ID="upDefault" runat="server"
            ContentTemplate     
                asp:Label ID="lblDescription" runat="server" Text="Label"  /asp:Label
                div id="divCustomCheckBoxList" runat="server" onmouseover="clearTimeout(timoutID);" onmouseout="timoutID = setTimeout('HideMList()', 750);"
                    table
                        tr
                            td align="right" class="DropDownLook"
                                input id="txtSelectedMLValues" type="text" readonly="readonly" onclick="ShowMList()" style="width:229px;" runat="server" /
                            /td
                            td align="left" class="DropDownLook"
                                img id="imgShowHide" runat="server" src="drop.gif" onclick="ShowMList()" align="left" /                
                            /td
                        /tr
                        tr
                            td colspan="2" class="DropDownLook"
                                div
                                 div runat="server" id="divCheckBoxListClose" class="DivClose"                           
                                  label runat="server" onclick="HideMList();" class="LabelClose" id="lblClose"  x /label
                              /div
                                    div runat="server" id="divCheckBoxList" class="DivCheckBoxList"
                                  asp:CheckBoxList ID="lstMultipleValues" runat="server" Width="250px" CssClass="CheckBoxList" /asp:CheckBoxList                                       
                              /div
                          /div
                            /td
                        /tr
                    /table
                /div
                div
                    asp:Button ID="btnSubmit" runat="server" Text="Submit Selection" OnClick="btnSubmit_Click" / 
                    asp:Label ID="lblTextSelected" runat="server" Text="" Font-Bold="true" >/asp:Label
                /div
            /ContentTemplate
        /asp:UpdatePanel


Understanding Identity Columns
2008-12-01T14:24:00.003+05:30
How can I reset an Identity column and not start where it left?" I've been getting quite a few questions about identity columns lately. This article should cover everything I know about them. I'll cover creating them, populating them, resetting them and a few other goodies. (This article has been updated through SQL Server 2005.)

Creating an Identity Column

In it's simplest form an identity column creates a numeric sequence for you. You can specify a column as an identity in the CREATE TABLE statement:

CREATE TABLE dbo.Yaks ( YakID smallint identity(7,2), YakName char(20) )
The identity clause specifies that the column YakID is going to be an identity column. The first record added will automatically be assigned a value of 7 (the seed) and each subsequent record will be assigned a value 2 higher (the increment) than the previous inserted row. Most identity columns I see are specified as IDENTITY(1,1) but I used IDENTITY(7,2) so the difference would be clear. If you don't specify the identity and seed they both default to 1. Identity columns can be int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0 (i.e. no places to the right of the decimal).

Populating the Table

When you insert into a table with an identity column you don't put a value into the identity column.

INSERT INTO dbo.Yaks (YakName) values ('Gertrude')
INSERT INTO dbo.Yaks (YakName) values ('Helga')

SELECT YakID, YakName
FROM dbo.Yaks

- - - - - - - - - - - - - - - - - - - - - - - - 

YakID  YakName              
------ -------------------- 
7      Gertrude            
9      Helga
The value for YakID was automatically filled in. If you do try to fill in a value for an identity column it will give you an error:

INSERT INTO dbo.Yaks (YakID, YakName) values (5, 'Sam')

- - - - - - - - - - - - - - - - - - - - - - - - 

Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Yaks' when IDENTITY_INSERT is set to OFF.
Finding the Identity Value that was Inserted

If you want to see what identity value was just inserted you can use @@IDENTITY.

INSERT INTO dbo.Yaks (YakName) values ('Sam the Yak')
SELECT SCOPE_IDENTITY() as NewRec

- - - - - - - - - - - - - - - - - - - - - - - - 

NewRec
----------------
11
Older applications might use @@IDENTITY to return the last identity value inserted. If you use @@IDENTITY and insert into a table that runs a trigger and generates another identity value, you will get back the last value generated in any table. That's why you use SCOPE_IDENTITY to return the inserted value. Every procedure, trigger, function and batch is it's own scope. SCOPE_IDENTITY shows the most recently inserted IDENTITY in the current scope (which ignores any triggers that might fire). SCOPE_IDENTITY is only available in SQL Server 2000 and higher. You can also see the most recent identity value for a table regardless of scope or session (process). You can use IDENT_CURRENT for that (again, SQL Server 2000 and higher only).

SELECT IDENT_CURRENT('Yaks') as SameRecordAgain

- - - - - - - - - - - - - - - - - - - - - - - - 

SameRecordAgain
----------------
11
Notice that we had to pass in the table name to the IDENT_CURRENT function. This will give you the most recent identity value for that table regardless of who inserted it.

Inserting Explicit Values into an Identity Column

If you want to insert a value into an identity column you can use the SET IDENTITY_INSERT statement.

SET IDENTITY_INSERT Yaks ON
INSERT INTO dbo.Yaks (YakID, YakName) Values(1, 'Mac the Yak')
SET IDENTITY_INSERT Yaks OFF
SELECT * from yaks

- - - - - - - - - - - - - - - - - - - - - - - - 

YakID  YakName              
------ -------------------- 
7      Gertrude            
9      Helga               
11     Sam the Yak         
1      Mac the Yak
You can only turn on IDENTITY_INSERT for one table per session so it's always a good idea to turn it off when you're done with it.

Deleting rows from a table with an Identity Column

If you delete all the records from a table it won't reset the identity.

DELETE FROM dbo.Yaks
INSERT INTO dbo.Yaks (YakName) Values ('New Herd')
SELECT SCOPE_IDENTITY()
returns the inserted identity as 13. To reset the identity seed you need to use a DBCC command.

DELETE FROM dbo.Yaks
DBCC CHECKIDENT('Yaks', RESEED, 7)
INSERT INTO dbo.Yaks (YakName) Values ('New Herd')
SELECT SCOPE_IDENTITY()
This returns an identity of 9. After the seed value is reset to 7, it's incremented by 2 (as we specified when we created the table) for the next record. Which also FINALLY answered Karla's question. You can also run DBCC CHECKIDENT without specifying a reseed value. If the current seed is lower than the highest value in the table, the seed is updated to the highest value in the table.

SQL Server makes no attempt to guarantee sequential gap-free values in identity columns. If records are deleted SQL Server won't go back and populate using those values. It's also possible for an insert to fail and "use up" an identity value. Books Online has additional detailed information about identity columns.


C# 2.0 Anonymous Methods
2008-12-01T10:08:00.001+05:30
C# 2.0 provides a new feature called Anonymous Methods, which allow you to create inline un-named ( i.e. anonymous ) methods in your code, which can help increase the readability and maintainability of your applications by keeping the caller of the method and the method itself as close to one another as possible. This is akin to the best practice of keeping the declaration of a variable, for example, as close to the code that uses it.

Here is a simple example of using an anonymous method to find all the even integers from 1...10:

 

private int[] _integers =
      { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

int[] evenIntegers = Array.FindAll(_integers,
                           delegate(int integer)
                           {
                               return (integer%2 == 0);
                           }
    );
 

The Anonymous Method is:

 

delegate(int integer)
    {
        return (integer%2 == 0);
    }
 

which is called for each integer in the array and returns either true or false depending on if the integer is even.

If you don't use an anonymous method, you will need to create a separate method as such:

 

private int[] _integers = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

int[] evenIntegers = Array.FindAll(_integers, IsEven);

private bool IsEven(int integer)
{
    return (integer%2 == 0);
}
 

When you have very simple methods like above that won't be reused, I find it much more elegant and meaningful to use anonymous methods. The code stays closer together which makes it easier to follow and maintain.

Here is an example that uses an anonymous method to get the list of cities in a state selected in a DropDownList ( called States ):

 

List citiesInFlorida =
cities.FindAll(delegate(City city)
     {
         return city.State.Name.Equals(States.SelectedValue);
     }
);
 

You can also use anonymous methods as such:

 

button1.Click +=

    delegate
        {
            MessageBox.Show("Hello");
        };
 

which for such a simple operation doesn't “deserve“ a separate method to handle the event.

Other uses of anonymous methods would be for asynchronous callback methods, etc.

Anonymous methods don't have the cool factor of Generics, but they do offer a more expressive in-line approach to creating methods that can make your code easier to follow and maintain.


Maximum Capacity Specifications for SQL Server 2005
2008-11-30T09:17:00.000+05:30
# Batch size : -> 65,536 * Network Packet Size
# Bytes per short string column : -> 8000
# Bytes per text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or XML column : -> 231 -1 bytes/p>
# Bytes per GROUP BY, ORDER BY : -> 8060
# Bytes per index : -> 900
# Bytes per foreign key : -> 900
# Bytes per primary key : -> 900
# Bytes per row : -> 8060
# Bytes in source text of a stored procedure : -> Lesser of batch size or 250 MB
# Clustered indexes per table : -> 1
# Columns in GROUP BY, ORDER BY : -> Limited only by number of bytes
# Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement : -> 10
# Columns per index : -> 16
# Columns per foreign key : -> 16
# Columns per primary key : -> 16
# Columns per base table : -> 1024
# Columns per SELECT statement : -> 4096
# Columns per INSERT statement : -> 1024
# Connections per client : -> Maximum value of configured connections
# Database size : -> 1,048,516 terabytes
# Databases per instance of SQL Server : -> 32767
# Filegroups per database : -> 32767
# Files per database : -> 32767
# File size (data) : -> 32 terabytes
# File size (log) : -> 32 terabytes
# Foreign key table references per table : -> 253
# Identifier length (in characters) : -> 128
# Instances per computer : -> 50
# Length of a string containing SQL statements (batch size) : -> 65,536 * Network packet size
# Locks per connection : -> Maximum locks per server
# Locks per instance of SQL Server : -> Up to 2,147,483,647
# Nested stored procedure levels : -> 32
# Nested subqueries : -> 32
# Nested trigger levels : -> 32
# Nonclustered indexes per table : -> 249
# Objects concurrently open in an instance of SQL Server : -> 2,147,483,647 per database (depending on available memory)
# Objects in a database : -> 2147483647
# Parameters per stored procedure : -> 2100
# Parameters per user-defined function : -> 2100
# REFERENCES per table : -> 253
# Rows per table : -> Limited by available storage
# Tables per database : -> Limited by number of objects in a database
# Tables per SELECT statement : -> 256
# Triggers per table : -> Limited by number of objects in a database
# UNIQUE indexes or constraints per table : -> 249 nonclustered and 1 clustered


OOPS Concepts:
2008-11-28T11:21:00.003+05:30
1) Encapsulation: Wrapping up of data and methods in to a
single unit is called as encapsulation.
Protecting our data

2) Abstraction: Hidding our irrelavance data

3) Inheritence: Process of Aquiring properties from one
object to another without changes.

4) Polymorphism: Process of aquiring properies from one
object to anotherwith changes.Different behaviors at diff. instances
There are two types of polymorphisms.
    i)Compile-time polymorphism:what object will be assigned to
the present variable.This will be evaluated at compile time.
This is known as compile-time polymorphism.
   ii)Run-time polymorphism:what object will be assigned to the
present variable.This will be evaluated at runtime depending
on condition.This is known as Run-time polymorphism.

5) Message Passing:message passing is possible from one
object to another.

6) Robust and Secure: every object is strong one.         
every object is secure one with their access specifiers.


Abstract, Sealed, and Static Modifiers in C#.

C# provides many modifiers for use with types and type members. Of these, three can be used with classes: abstract, sealed and static. 

abstract
Indicates that a class is to be used only as a base class for other classes. This means that you cannot create an instance of the class directly. Any class derived from it must implement all of its abstract methods and accessors. Despite its name, an abstract class can possess non-abstract methods and properties. 

sealed
Specifies that a class cannot be inherited (used as a base class). Note that .NET does not permit a class to be both abstract and sealed. 

static
Specifies that a class contains only static members (.NET 2.0). 


Virtual keyword

The virtual keyword allows polymorphism too. A virtual property or method has an implementation in the base class, and can be overriden in the derived classes.

To create a virtual member in C#, use the virtual keyword:

public virtual void Draw()

To create a virtual member in VB.NET, use the Overridable keyword:

Public Overridable Function Draw()

Override keyword

Overriding is the action of modifying or replacing the implementation of the parent class with a new one. Parent classes with virtual or abstract members allow derived classes to override them.

To override a member in C#, use the override keyword:

public override void CalculateArea()

To override a member in VB.NET, use the Overrides keyword:

Public Overrides Function CalculateArea()


What's the difference between override and new in C#?

This is all to do with polymorphism. When a virtual method is called on a reference, the actual type of the object that the reference refers to is used to decide which method implementation to use. When a method of a base class is overridden in a derived class, the version in the derived class is used, even if the calling code didn't "know" that the object was an instance of the derived class. For instance:
public class Base
{
    public virtual void SomeMethod()
    {
    }
}

public class Derived : Base
{
    public override void SomeMethod()
    {
    }
}

...

Base b = new Derived();
b.SomeMethod();

 
will end up calling Derived.SomeMethod if that overrides Base.SomeMethod. 

Now, if you use the new keyword instead of override, the method in the derived class doesn't override the method in the base class, it merely hides it. In that case, code like this:
public class Base
{
    public virtual void SomeOtherMethod()
    {
    }
}

public class Derived : Base
{
    public new void SomeOtherMethod()
    {
    }
}

...


Base b = new Derived();
Derived d = new Derived();
b.SomeOtherMethod();
d.SomeOtherMethod();

 
Will first call Base.SomeOtherMethod , then Derived.SomeOtherMethod . They're effectively two entirely separate methods which happen to have the same name, rather than the derived method overriding the base method. 

If you don't specify either new or overrides, the resulting output is the same as if you specified new, but you'll also get a compiler warning (as you may not be aware that you're hiding a method in the base class method, or indeed you may have wanted to override it, and merely forgot to include the keyword).


GridView Search Revisited
2008-11-24T13:36:00.003+05:30
in .aspx
    Enter Search Keyword: 
asp:TextBox ID="txtSearchBox" runat="server"
asp:Button ID="Btn_Search" OnClick="Search" runat="server" Text="Search" />
asp:GridView ID="gvProducts" runat="server"  AutoGenerateColumns="false"  AllowPaging="True" Font-Size="Large" OnPageIndexChanging="gvProducts_PageIndexChanging" PageSize="10">
Columns>
asp:TemplateField HeaderText="Product Name">
ItemTemplate>    
asp:Label ID="lblProductName" runat="server" Text = '<%# SearchKeyWord(  searchWord,(string) Eval("ProductName") ) %>' />    
/ItemTemplate>    
/asp:TemplateField>
/Columns>
/asp:GridView>
asp:Panel ID="Panel1" runat="server" Height="50px" Width="263px">


.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

public partial class _Default : System.Web.UI.Page
{
    protected string searchWord = String.Empty;   
    private const int PAGE_SIZE = 10;

    private Hashtable hMatches = new Hashtable();   
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindData();    
        }       

        CreateLinks(); 
       

    }

    private void CreateLinks()
    {
        Hashtable myHashTable = ViewState["MyHashTable"] as Hashtable; 

        if(myHashTable != null && myHashTable.Count > 0) 
        {

        foreach (object key in new ArrayList(myHashTable.Keys))
        {
            LinkButton link = new LinkButton();
            link.Command += new CommandEventHandler(link_Command);
            link.CommandArgument = key.ToString();
            link.Text = String.Format("There are {0} results on page {1}",myHashTable[key].ToString() , (Convert.ToInt32(key) + 1).ToString()) + "
";
                                   
            Panel1.Controls.Add(link);
        }

        Panel1.DataBind();
        }
    }


    private void BindData()
    {
        string connectionString = @"Server=CB-36A\SQLEXPRESS;Database=master;Trusted_Connection=true";
        SqlConnection myConnection = new SqlConnection(connectionString);
        SqlDataAdapter ad = new SqlDataAdapter("SELECT ProductName FROM Product", myConnection);

        DataSet ds = new DataSet();
        ad.Fill(ds);

        gvProducts.DataSource = ds;
        gvProducts.DataBind(); 
    }

    protected void Search(object sender, EventArgs e)
    {
        // clear the old results 
        Panel1.Controls.Clear();
        Panel1.DataBind(); 
        
        searchWord = txtSearchBox.Text;
        SearchContainer(searchWord);      

        // Hmmm  
        BindData();      

    }

    protected string SearchKeyWord(string searchString, string text)
    {
        Regex reg = new Regex(searchString.Replace(" ", "|"), RegexOptions.IgnoreCase);
        return reg.Replace(text,new MatchEvaluator(ReplaceKeyWords));   
        
    }

    private void SearchContainer(string searchString)
    {
        int index = 0;        

        // get the container
        DataSet ds = GetDataSet(); 
       

        double pageIndex = 0; 

        Regex reg = new Regex(searchString.Replace(" ","|"),RegexOptions.IgnoreCase); 

        foreach(DataRow row in ds.Tables[0].Rows) 
        {
            if (reg.IsMatch(row["ProductName"] as String))
            {
                pageIndex = Math.Ceiling( Convert.ToDouble( index / PAGE_SIZE ));

                if (hMatches.ContainsKey(pageIndex))
                {
                    hMatches[pageIndex] = ((int)hMatches[pageIndex]) + 1;
                }
                else
                {
                    hMatches.Add(pageIndex, 1); 
                }               
            }

            index++; 
        }

        ViewState["MyHashTable"] = hMatches;

        CreateLinks(); 


    }

    void link_Command(object sender, CommandEventArgs e)
    {
        gvProducts.PageIndex = Convert.ToInt32( e.CommandArgument);
        searchWord = txtSearchBox.Text;
        BindData(); 
    }   

    private DataSet GetDataSet()
    {
        string connectionString = @"Server=CB-36A\SQLEXPRESS;Database=master;Trusted_Connection=true";
        SqlConnection myConnection = new SqlConnection(connectionString);
        SqlDataAdapter ad = new SqlDataAdapter("SELECT ProductName FROM Product", myConnection);

        DataSet ds = new DataSet();

        ad.Fill(ds); 

        return ds; 
    }

    private string ReplaceKeyWords(Match m)
    {
        return "" + m.Value + ""; 
    }

    protected void gvProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvProducts.PageIndex = e.NewPageIndex;
        searchWord = txtSearchBox.Text;
        BindData(); 
    }   
}


What is custom control? What is the difference between custom control and user control?
2008-11-20T16:47:00.001+05:30
Custom controls are controls that are developed by the developer
or a third party vendor. Custom controls are not provided along with 
.NET. 

Difference between Custom Controls and User Controls.

1.User Control is a page file with extension .ascx which can only be used within
a single application. But custom controls are assemblies(dll files) that can be 
used in multiple applications.

2.User Controls cannot be added to the ToolBox of VS.NET . To use a user Control with in an
aspx page u have to drag the user Control from the solution Explorer to designer page.
But Custom Controls can be added to ToolBox of VS.NET.

3.User Controls can be viewed as a sort of generic controls during the design time.
The proper GUI of user controls can be viewed only during the run time.
But Custom Controls can be viewed during the design time.

4. User controls are created from existing Webserver and html server controls .
But a developer who creates custom controls have to render every thing from the scratch.

5.Since the dll assembly of a custom control is being used,a custom control developed in C# can be used in a project developed in VB.NET or any other managed code and vice versa.

User Controls:

1.Have an ascx extension.
2.Are compiled at runtime when the page is loaded.
3.Visual design is possible, just like an aspx page and uses the ASP.Net page model with code behind file.
4.Can only be used on a host aspx page or another user control.
5.Cannot be added to the ToolBox.
6.Can only be used in the current web application (source must be copied to another application to use).

Custom Server Control:

1.Exist in precompiled assemblies.
2.Code entirely contained in .cs (or .vb)
3.No visual designer. Any HTML code needs to be declared programmatically.
4.Can be used in .aspx pages, user controls or other custom server controls.
5.Can be added to the ToolBox (drag and drop)
6.Can be shared between web applications.


Change GridView RowColor OnMouseClick
2008-11-10T17:48:00.000+05:30
Introduction: 
In this article I will demonstrate that how you can change the color of the GridView row by using simple mouse click and change it back to its original color by clicking the row twice. 
The Row Created Event of the GridView:
Like my last article "Changing GridView Row Color OnMouseOver" I used the same approach in populating the GridView. The difference lies in the RowCreated event of the GridView control. Take a look at the RowCreated event below to have a better idea: 

protected void MyGridView_RowCreated(object sender, GridViewRowEventArgs e)
{

string rowID = String.Empty; 

if (e.Row.RowType == DataControlRowType.DataRow)

{

rowID = "row"+e.Row.RowIndex; 

e.Row.Attributes.Add("id","row"+e.Row.RowIndex);

e.Row.Attributes.Add("onclick","ChangeRowColor(" +"'" + rowID + "'" + ")");

} 

}

As, you can see in the method above I am assigning different ids to the rows by embedding the id attribute. This will help me distinguish one row from the other. Later, on the onclick attribute I attach the Java Script function "ChangeRowColor" which is responsible for changing the color of the GridView row. The ChangeRowColor function takes in the id of the row. Let's take a look at the ChangeRowColor function. 

script language ="javascript" type="text/javascript"
document.body.style.cursor = 'pointer'; 

var oldColor = '';
function ChangeRowColor(rowID) 
{ 
var color = document.getElementById(rowID).style.backgroundColor;
if(color != 'yellow') 
oldColor = color;
if(color == 'yellow') 
document.getElementById(rowID).style.backgroundColor = oldColor;
else document.getElementById(rowID).style.backgroundColor = 'yellow'; 
}

/script

The ChangeRowColor function takes in the id of the row and finds the color of the row. Then it checks that if the color is 'yellow' which, is the color of the highlight row. If it is not yellow then it simply assigns the color to the public variable oldColor which is used to save the previous color of the GridView row. If the color is yellow then we get the previous color and assign to the row so that it can come back to its original state.


Searching Inside the GridView Control And Highlight
2008-11-10T17:36:00.000+05:30

There was a post on ASP.NET forums where the user asked how to search inside the GridView control. I was managed to implement this feature although if you plan to use the following code then I would highly recommend that you refactor it as it lacks in performance. In my opinion since you are searching inside the GridView the whole procedure should be handled on the client side using JavaScript or VBScript. Anyhow the following code uses the server side button click event to parse the GridView and highlight the search fields. 
protected void Button1_Click(object sender, EventArgs e)
{
  string searchString = txtSearch.Text;
  StringBuilder sb = new StringBuilder();
  DataTable dt = new DataTable();
  List rows = new List(); 
  foreach (GridViewRow row in GridView1.Rows)
  {
   TableCellCollection cells = row.Cells;
   foreach (TableCell cell in cells)
   {
     if (cell.Text.ToLower().StartsWith(searchString.ToLower()))
     {
       cell.BackColor = System.Drawing.Color.Yellow;
       foreach (TableCell c in cells)
       {
        sb.Append(c.Text);
       }
       sb.Append("
"); 
     }
     else
     {
       cell.BackColor = System.Drawing.Color.White;
     }
   }
 }
}
The code above simply goes through the GridView row by row and cell by cell. And if it finds a string that starts with the searchString then it highlights it and append it in the StringBuilder object.


Difference Between User Defined functions And Stored Procedure
2008-09-26T12:47:00.001+05:30
1>Procedure can return zero or n values whereas function can return one value which is mandatory.

2>Procedures can have input,output parameters for it whereas functions can have only input parameters.

3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.

4>Functions can be called from procedure whereas procedures cannot be called from function.

5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6>We can go for transaction management in procedure whereas we can't go in function.

7>Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.


DataList Custom Paging
2008-09-25T15:53:00.001+05:30


.cs


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    PagedDataSource pds = new PagedDataSource();
    DataTable dt = new DataTable();
    static int lastPage = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //lastPage = 0;
            ddlPageSize.Items.Add("5");
            ddlPageSize.Items.Add("10");
            ddlPageSize.Items.Add("15");
            BindGrid();
        }
    }
    private void BindGrid() 
    {
            string connectionString = @"Data Source =CB-36A\SQLEXPRESS ;Initial Catalog=master;Integrated Security=True";
            SqlConnection myConnection = new SqlConnection(connectionString);
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Country", myConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            pds.DataSource = dt.DefaultView;
            pds.AllowPaging = true;
            pds.PageSize = Convert.ToInt32(ddlPageSize.SelectedValue);
            pds.CurrentPageIndex = CurrentPage;
            lnkbtnNext.Enabled = !pds.IsLastPage;
            lnkbtnPrevious.Enabled = !pds.IsFirstPage;
            lnkbtnFirst.Enabled = !pds.IsFirstPage;
            lnkbtnLast.Enabled = !pds.IsLastPage;
            Countrydlst.DataSource = pds;
            Countrydlst.DataBind();
            doPaging();
    }
    protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
    {
        BindGrid();
        doPaging();
    }
    public int CurrentPage
    {
        get
        {
            if (this.ViewState["CurrentPage"] == null)
                return 0;
            else
                return Convert.ToInt16(this.ViewState["CurrentPage"].ToString());
        }
        set
        {
            this.ViewState["CurrentPage"] = value;
        }

    }
    private void doPaging()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("PageIndex");
        dt.Columns.Add("PageText");
        for (int i = 0; i < pds.PageCount; i++)
        {
            DataRow dr = dt.NewRow();
            dr[0] = i;
            dr[1] = i + 1;
            dt.Rows.Add(dr);
        }
        lastPage = int.Parse(dt.Rows[dt.Rows.Count - 1][0].ToString()); 
        Pagingdlst.DataSource = dt;
        Pagingdlst.DataBind();
    }
    protected void Pagingdlst_ItemCommand(object source, DataListCommandEventArgs e)
    {
        if (e.CommandName.Equals("lnkbtnPaging"))
        {
            CurrentPage = Convert.ToInt16(e.CommandArgument.ToString());
            BindGrid();
        }
    }
    protected void lnkbtnPrevious_Click(object sender, EventArgs e)
    {
        CurrentPage -= 1;
        BindGrid();
    }
    protected void lnkbtnNext_Click(object sender, EventArgs e)
    {
        CurrentPage += 1;
        BindGrid();
    }
    protected void Pagingdlst_SelectedIndexChanged(object sender, EventArgs e)
    {
        CurrentPage = 0;
        BindGrid();   
    }
    protected void Pagingdlst_ItemDataBound(object sender, DataListItemEventArgs e)
    {
        LinkButton lnkbtnPage = (LinkButton)e.Item.FindControl("lnkbtnPaging");
        if (lnkbtnPage.CommandArgument.ToString() == CurrentPage.ToString())
        {
            lnkbtnPage.Enabled = false;
            lnkbtnPage.Font.Bold = true;
        }
    }
    protected void lnkbtnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 0;
        BindGrid();
    }
    protected void lnkbtnLast_Click(object sender, EventArgs e)
    {
        CurrentPage = lastPage;
        BindGrid();
    }
}


How to Export the File in to Pdf, Doc and xls Using Crystal Report
2008-09-23T16:01:00.000+05:30
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportSource;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
//protected CrystalDecisions.Web.CrystalReportViewer CrystalReportViewer1;
//protected System.Web.UI.WebControls.Button Button1;
ReportDocument rDoc = new ReportDocument();
protected void Page_Load(object sender, EventArgs e)
{
string FilePath = Server.MapPath("CrystalReport.rpt");
rDoc.Load(FilePath);
DataSet ds = new DataSet();
CrystalReportViewer1.ReportSource = rDoc;
CrystalReportViewer1.Visible = true;
}
protected void Button1_Click(object sender, EventArgs e)
{
MemoryStream oStream= new MemoryStream (); // using System.IO
switch (DropDownList1.SelectedItem.Text )
{
case "Pdf":
oStream = (MemoryStream)rDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/pdf";
Response.AddHeader("Content-Disposition", "attachment;filename=SearchResult.pdf");
Response.BinaryWrite(oStream.ToArray());
Response.End();
break ;
case "Doc":
oStream = (MemoryStream)rDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.WordForWindows);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/doc";
Response.AddHeader("Content-Disposition", "attachment;filename=SearchResult.doc");
Response.BinaryWrite(oStream.ToArray());
Response.End();
break;
case "Excel":
oStream = (MemoryStream)rDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=SearchResult.xls");
Response.BinaryWrite(oStream.ToArray());
Response.End();
break;
}
}
}


Insert, Update, Delete with Gridview....simple way using Stored Procedure
2008-09-16T16:47:00.000+05:30










Introduction
This is a simple way to display, Update, Delete and Insert through a single page.
 
Using the code
Many of us may be encountered with Update and Delete operations with GridView. I’m explaining this with more functionality that is Inserting record through the GridView. There could me more way to achieve this functionality but I think it’s a very simple way to achieve this. I’m taking a very simple table named “quest_categories”
 
CREATE TABLE [dbo].[quest_categories](
[cat_id] [int] IDENTITY(1,1) NOT NULL,
[cat_name] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_quest_categories] PRIMARY KEY CLUSTERED
(
[cat_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Here is the Code for “Add Category”
protected void btnAdd_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings\["exam_moduleConnectionString"].ConnectionString);
SqlDataAdapter da = new SqlDataAdapter("SELECT cat_id, cat_nameFROMquest_categories",con);
DataTable dt = new DataTable();
da.Fill(dt);
// Here we'll add a blank row to the returned DataTableDataRow dr = dt.NewRow();dt.Rows.InsertAt(dr, 0);//Creating the first row of GridView to be EditableGridView1.EditIndex = 0;
GridView1.DataSource = dt;
GridView1.DataBind();//Changing the Text for Inserting a New Record((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text = "Insert";
}
 
And according to Text Diplays we’ll do further processing as “Update” or “Insert” record.
Here is the code for “RowUpdating” event.
 
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
if (((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text == "Insert")
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO quest_categories(cat_name) VALUES(@cat_name)";
cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[2].Controls[0]).Text;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Response.Redirect("quest_categories.aspx");
}
else
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UPDATE quest_categories SET cat_name=@cat_name WHERE cat_id=@cat_id";
cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
GridView1.EditIndex = -1;
BindData();
}
Rest operations are same as you might have done before so I’m not explaining it further.



Lambda Expressions Extension Methods and LINQ in C# 3.0
2008-07-30T13:44:00.000+05:30
The idea behind anonymous methods it to write methods inline to the code so you don't have to go through the trouble of declaring a formal named method. They are mainly used for small methods that don't require any need for reuse.

Instead of declaring a separate method IsAbe to find Abe in a list of strings:

 
class Program
{
    static void Main(string[] args)
    {                    
        List names = new List();
        names.Add("Dave");
        names.Add("John");
        names.Add("Abe");
        names.Add("Barney");
        names.Add("Chuck");
        string abe = names.Find(IsAbe);
        Console.WriteLine(abe);
    }
    public static bool IsAbe(string name)
    {
        return name.Equals("Abe");
    }
}

 

You can declare an anonymous method inline to save you the trouble:

 
class Program
{
    static void Main(string[] args)
    {                    
        List names = new List();
        names.Add("Dave");
        names.Add("John");
        names.Add("Abe");
        names.Add("Barney");
        names.Add("Chuck");
        string abe = names.Find(delegate(string name)
                        {
                            return name.Equals("Abe");
                        });
        Console.WriteLine(abe);
    }
}

 

It can get a lot fancier than that, but that is basically the gist. Declare the method inline to the code for easy stuff not needing reuse, because it saves some typing and puts the method closer to where it is being used which helps with maintenance.
Lambda Expressions

Lambda Expressions make things even easier by allowing you to avoid the anonymous method and that annoying statement block:

 
class Program
{
    static void Main(string[] args)
    {                    
        List names = new List();
        names.Add("Dave");
        names.Add("John");
        names.Add("Abe");
        names.Add("Barney");
        names.Add("Chuck");
        string abe = names.Find((string name)
                          => name.Equals("Abe"));
        Console.WriteLine(abe);
    }
}

 

Because Lambda Expressions are smart enough to infer variable types, I don't even have to explicity mention that name is a string above. I can remove it for even more simplicity and write it as such:

 
class Program
{
    static void Main(string[] args)
    {                    
        List names = new List();
        names.Add("Dave");
        names.Add("John");
        names.Add("Abe");
        names.Add("Barney");
        names.Add("Chuck");
        string abe = names.Find(name =>
                           name.Equals("Abe"));
        Console.WriteLine(abe);
    }
}

 

Now there is no particular reason why I have to use name as my variable. Often developers will use 1 character variable names in Lambda Expressions just to keep things short. Here we replace name with p and all works the same.

 
class Program
{
    static void Main(string[] args)
    {                    
        List names = new List();
        names.Add("Dave");
        names.Add("John");
        names.Add("Abe");
        names.Add("Barney");
        names.Add("Chuck");
        string abe = names.Find(p => p.Equals("Abe"));
        Console.WriteLine(abe);
    }
}

 
Lambda Expressions Using a Customer Class

I used a list of strings above, but you can just as easily use a list of objects to do the same thing. I will take an abbreviated form of a Customer Class:

 
public class Customer
{
    public int Id;
    public string Name;
    public string City;
    public Customer(int id, string name, string city)
    {
        Id = id;
        Name = name;
        City = city;
    }
}

 

and now use Lambda Expressions to find a particular Customer with a name of "Abe".

 
class Program
{
    static void Main(string[] args)
    {            
        List customers = new List();
        customers.Add(new Customer(1,"Dave","Sarasota"));
        customers.Add(new Customer(2,"John","Tampa"));
        customers.Add(new Customer(3,"Abe","Miami"));
        Customer abe = customers.Find(c =>
                               c.Name.Equals("Abe"));
    }
}

 

Again, we could make things more obvious by explicity saying that c is of type Customer, but I wouldn't bet on many people doing it :) You could write the above as follows:

 
class Program
{
    static void Main(string[] args)
    {            
        List customers = new List();
        customers.Add(new Customer(1,"Dave","Sarasota"));
        customers.Add(new Customer(2,"John","Tampa"));
        customers.Add(new Customer(3,"Abe","Miami"));
        Customer abe = customers.Find((Customer c) =>
                                   c.Name.Equals("Abe"));
    }
}

 
Combining Extension Methods and Lambda Expressions for Help In Finding Our Customer

Let's say we have a custom CustomerCollection Class not within our control that doesn't provide us an easy way to find a Customer:

 
public class CustomerCollection :
                     IEnumerable
{
    IEnumerable _customers;
    public CustomerCollection(IEnumerable
                                        customers)
    {
        _customers = customers;
    }
    public IEnumerator GetEnumerator()
    {
        foreach (Customer customer in _customers)
            yield return customer;
    }
    System.Collections.IEnumerator System.Collections
                        .IEnumerable.GetEnumerator()
    {
        return _customers.GetEnumerator();
    }
}

 

Let's add an Extension Method to the CustomerCollection Class, called GetCustomer:

 
public static class CustomerExtensions
{
    public static Customer GetCustomer(this
            CustomerCollection customers,
            Predicate isMatch)
    {
        foreach (Customer customer in customers)
            if (isMatch(customer))
                return customer;
        return null;
    }
}

 

And now, we can use the CustomerCollection Class to easily find Abe like:

 
class Program
{
    static void Main(string[] args)
    {                    
        CustomerCollection collection = GetCustomers();    
        // Using my GetCustomer Method Extension...
        Customer abe = collection.GetCustomer(c =>
                                 c.Name.Equals("Abe"));
    }
    
    // Pretend We Don't See This :)
    static CustomerCollection GetCustomers()
    {
        List customers = new List();
        customers.Add(new Customer(1,"Dave","Sarasota"));
        customers.Add(new Customer(2,"John","Tampa"));
        customers.Add(new Customer(3,"Abe","Miami"));
        
        return new CustomerCollection(customers);
    }
}

 
But This Is LINQ!

But, of course, since CustomerCollection implements IEnumerable, in this case, IEnumerable, screw the Extension Methods and just use LINQ:

 
class Program
{
    static void Main(string[] args)
    {                    
        CustomerCollection collection = GetCustomers();    
        // LINQ
        var abe = collection.Single(c =>
                         c.Name.Equals("Abe"));
    }
    
    // Pretend We Don't See This :)
    static CustomerCollection GetCustomers()
    {
        List customers = new List();
        customers.Add(new Customer(1,"Dave","Sarasota"));
        customers.Add(new Customer(2,"John","Tampa"));
        customers.Add(new Customer(3,"Abe","Miami"));
        
        return new CustomerCollection(customers);
    }
}


2008-07-29T16:56:00.000+05:30
When ASP.NET hit the street a couple of years ago, it was a real eye-opener. Microsoft's tool for creating dynamic, server side web applications introduced Web Forms, a feature with the same rapid drag and drop convenience enjoyed by Visual Basic developers, along with a method for creating XML-based web services. ASP.NET was more than an upgrade of Active Server Pages it was a quantum leap ahead. Now Microsoft has a new version of ASP.NET as part of its upcoming next generation release of the Visual Studio .NET development platform. ASP.NET 2.0 is already available in beta release, and web developers are anxious to get a good look at it. That's exactly what our new Developer's Notebook allows you to do. More than just an introduction to ASP.NET 2.0, this practical guide acquaints you with all of the new features through nearly 50 hands-on projects. Each one places emphasis on changes in the new release that can increase productivity, simplify programming tasks, and help you add functionality to your applications. For example, ASP.NET 2.0 includes master pages, themes, and skins so you can build applications with a consistent page layout and design. Other changes allow for the automatic creation of web pages for use on mobile devices, while wizards and controls allow you to perform frequent tasks (like data access) without having to write a single line of code. ASP.NET 2.0: A Developer's Notebook also includes suggestions for further experimentation, links to on-line documentation, and practical notes and warnings from the author regarding changes to the new version. The new Developer's Notebooks series from O'Reilly offers an in-depth first look at important new tools for software developers. Emphasizing example over explanation and practice over theory, they focus on learning by doing you'll get the goods straight from the masters, in an informal and code-intensive style. If you want to get up to speed on ASP.NET 2.0 before its official release, this all lab, no lecture book will get you there.