Share Programming Ideas

Difference Between For loop and For each loop

2013-07-11T11:03:00.001+05:30

For Loop:

It’s preferred when you know how many iteration you want. It is used to executes a block of statements for as long as a specified condition is true.

General Syntax:

for(Initialization; Test Condition; Increment)
{
Statement;
}

Example: To add the number from 1 to 10 by using for loop and sum the number by series of 1+2+3+...+10.

using System;
using System.Collections.Generic;
using System.Text;
namespace for_loop
{
class Program
{
static void Main(string[] args)
{
int[] arry = new int[] {1,2,3,4,5,6,7,8,9,10 };
    int s = 0;
    for (int i = 0; i < arry.Length; i++)
   {
        s = s+ arry[i];
   }
Console.WriteLine("Sum is: {0}",s);
Console.ReadKey();
}
}
}

Output : Sum is:55

For Each Loop:

It operates on collections of items, for instance arrays or other built-in list types. It does not use an integer index. Instead, it is used on a collection and returns each element in order. In the foreach-statement, you do not need to specify the loop bounds minimum or maximum. It traverse whole collection.

General Syntax:

foreach( Datatype item in items )
{
Statement;
}

Example: To add the number from 1 to 10 by using foreach loop and sum the number by series of 1+2+3+...+10.

using System;
using System.Collections.Generic;
using System.Text;
namespace for_loop
{
class Program
{
static void Main(string[] args)
{
    int[] arry = new int[] {1,2,3,4,5,6,7,8,9,10 };
    int s = 0;
    foreach (int i in arry)
     {
        s = s+ i;
     }
Console.WriteLine("Sum is: {0}",s);
Console.ReadKey();
}
}
}

Output : Sum is:55

Conclusion:

Both the concepts are same only. For each is used for traversing items in a collection. It usually maintain no explicit counter and no use of index. They essentially say "do this to everything in this set", rather than "do this x times".

For loop is classified as an iteration statement. The statements in the for loop repeat continuously for a specific number of times. It as counter and integer index (ex: a[i], i represent the index value and change it by increment operation on each iteration).

LINQ with DML Queries

2013-07-10T23:14:00.001+05:30

This Post is a continuation of LINQ.
Here the following concepts are explain about LINQ with DML Queries.

DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. They are:

Select
Insert
Update
Delete

Select

The SELECT statement returns a result set of records from one or more tables. The SELECT statement has many optional clauses: WHERE, ORDER BY, GROUP BY

For Example:

A Table name as tbl_Student.
This table contain the following columns: StudentID, Firstname, Lastname, Location, Email.

LINQ SELECT Statement with WHERE Clause:

DataClasses1DataContext db = new DataClasses1DataContext();
var student =
      from stud in db.tbl_Students
      where db.Location == "Pondy"
      select stud;

LINQ SELECT Statement with ORDER BY:

DataClasses1DataContext db = new DataClasses1DataContext();
var student =
      from stud in db.tbl_Students
      orderby stud.Location
      select stud;

LINQ SELECT Statement with GROUP BY:

DataClasses1DataContext db = new DataClasses1DataContext();
var student =
      group s by s.tbl_Students into g
      where g.count() >= 1
      select new
      {
          g.Key,
          LocationCount = g.Count()
      };

Insert

To execute a SQL Insert, just add objects to the object model you have created, and call SubmitChanges on the DataContext.

The following example, Let insert a new Student detail into tbl_Student table. This table contain the following columns: StudentID, Firstname, Lastname, Location & Email.

LINQ INSERT Statement:

DataClasses1DataContext db = new DataClasses1DataContext();
tbl_Student ts = new tbl_Student();
      ts.StudentID = textBox1.Text;
      ts.Firstname = textBox2.Text;
      ts.Lastname = textBox3.Text;
      ts.Location = textBox4.Text;
      ts.Email = textBox5.Text;
      db.tbl_Students.InsertOnSubmit(ts);
      db.SubmitChanges();
MessageBox.Show("Value inserted Successfully");

Update

To Update a database entry, first retrieve the item and edit it directly in the object model. After you have modified the object, call SubmitChanges on the DataContext to update the database.

The following example, Let retrieve a Student detail from tbl_Student table who are all from Location Pondy. Then change Location = “Pondy” to “Puducherry”. Finally SubmitChanges is called to send the changes to database.

LINQ UPDATE Statement:

DataClasses1DataContext db = new DataClasses1DataContext();
var Loc = from s in db.tbl_Student
      where Loc.Location.Contains(”Pondy”)
      select Loc;
foreach (var student in Loc)
{
   if (student.Location == "Pondy")
   {
        student.Location = "Puducherry";
   }
}
db.SubmitChanges();

Delete

To Delete an item, remove the item from the collection to which it belongs, and then call SubmitChanges on the DataContext to commit the change.

The following example, delete a Student detail from tbl_Student table who are all from Location 'Chennai'. Finally SubmitChanges is called to send the changes to database.

LINQ DELETE Statement:

DataClasses1DataContext db = new DataClasses1DataContext();
var delLoc = from s in db.tbl_Student
      where s.Location == "Chennai"
      select s;
   if (delLoc.Count() > 0)
   {
        db.tbl_Student.DeleteOnSubmit(delLoc.First());
        db.SubmitChanges();
   }

LINQ–Language Integrated Query

2013-07-09T22:40:00.001+05:30

Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. Visual Studio includes LINQ provider assemblies that enable the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET Datasets, and XML documents.

LINQ Providers:

LINQ to SQL
LINQ to XML
LINQ to Dataset
LINQ to Objects

LINQ to SQL:

LINQ to SQL is a component of .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects. In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution. When the database returns the results, LINQ to SQL translates them back to objects that you can work with in your own programming language.

By using LINQ to SQL, you can use the LINQ technology to access SQL databases just as you would access an in-memory collection.

Step 1: The Below picture will explain how to add LINQ to SQL in your project

Open Visual Studio and goto New Project and select the Windows Form Application.
In Solution Explorer, right click the application name -> Add -> New item.

Step 2: After open the "Add New Item Wizard" select the LINQ to SQL classes file in Data Category.

Step 3: The Object Relational Designer allows you to visualize data classes in your code. By click Server Explorer to add the table.

Step 4: Connect Database using Server Explorer at Right side or click the link enable text.

Step 5: Connect to Database using Server Explorer.

1.Default DataSource is SQL Server(Sqlclient).

2.Type the server name.

3.Select authentication type based on sql server

4.Select the Database.

5.(Optional) Verify the connection using Test Connection.

6.Press OK to make a connection with database and our project.

Step 6: Drag the table from server explorer to Dataclasses file.

Step 7: Double click the Form in solution explorer and place a Datagridview control from Toolbox to form. Write the below code in Form Load event.

LINQ with SELECT Statement and without Where Clause

namespace linq_example

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

//Create DataContext object by using LINQ file DataClasses.

{

DataClasses1DataContext db = new DataClasses1DataContext();

//Linq query using IQueryable for Select statement query.

IQueryable<tbl_Student> student =

from stud in db.tbl_Students

select stud;

//set Datasource to DataGridView for bind the data.

dataGridView1.DataSource = student;

}

The Above code used the concept of linq to sql with Select statment and bind the data into datagridview.

OUTPUT:

For Download Source Code: Click Here

ASP.NET 4.0 Providers

2013-07-08T23:27:00.001+05:30

Several ASP.NET application services rely on a provider to manage storing and retrieving data from a data source. Each provider is specific to the data source. ASP.NET includes a SQL Server provider for the following ASP.NET features:

Membership (the SqlMembershipProvider class).
Role management (the SqlRoleProvider class).
Profile (the SqlProfileProvider class).
Web Parts personalization (the SqlPersonalizationProvider class).
Web events (the SqlWebEventProvider class).

The above features are we need to define in asp.net 'Web.config' file.

Web.config is the main settings and configuration file for an ASP.NET web application. The file is an XML document that defines configuration information regarding the web application.This file stores the information about how the web application will act. The web.config file contains information that controls module loading, security configuration, session state configuration, and application language and compilation settings. Web.config files can also contain application specific items such as database connection strings.

<?xml version="1.0"?>
<configuration>
<connectionstrings>
<add name="ApplicationServices"
connectionstring="data source=localhost;Integrated Security=True;Initial Catalog=aspnetdb" providername="System.Data.SqlClient" />
</connectionstrings>

<system.web>
<compilation debug="false" targetframework="4.0" />

<authentication mode="[Windows|Forms|Passport|None]">
</authentication>

<membership defaultprovider="name of membership provider what you've created.">
<providers>"part for membership provider properties." </providers>
</membership>

<profile>
<providers>"part for profile provider properties." </providers>
</profile>

<rolemanager enabled="false">
<providers>"part for role provider properties." </providers>
</rolemanager>
</system.web>

<system.webserver>
<modules runallmanagedmodulesforallrequests="true" />
</system.webserver>
</configuration>

Configuring SQL Server Provider by Web.config file.

SQL Membership Provider:

ASP.NET membership gives you a built-in way to validate and store user credentials. ASP.NET membership therefore helps you manage user authentication in your Web sites. You can use ASP.NET membership with ASP.NET forms authentication by using with the ASP.NET login controls to create a complete system for authenticating users.

ASP.NET membership supports facilities for:

Create a new users and passwords.
Storing membership information (user names, passwords, and supporting data)
Authenticating users who visit your site.
Managing passwords, which includes creating, changing, and resetting them .

SQL Role Provider:

ASP.NET role management helps you to manage authorization, allowing you to specify which resources various users in your application are allowed to access. Role management lets you treat groups of users as a unit by assigning users to roles such as manager, sales, member, and so on. The primary purpose of establishing roles is to give you an easy way to manage access rules for groups of users. You create users and then assign the users to roles (in Windows, to groups). A typical use is to then create a set of pages that you want to restrict to certain users.

ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)

2013-07-08T17:08:00.001+05:30

The ASP.NET SQL Server registration tool (Aspnet_regsql.exe) is used to create a Microsoft SQL Server database that is used by the SQL Server providers in ASP.NET. The tool is also used to add or remove options from an existing database.

You can run Aspnet_regsql.exe without any command-line arguments to run a wizard that will walk you through specifying connection information for your SQL Server installation, and installing or removing the database elements for the membership, role management, profile, Web Parts personalization, and health monitoring features.

goto [ c: -> windows folder -> Microsoft.NET folder -> Framework folder -> folder with version like v 4.0, v3.5 and select the file “aspnet_regsql.exe” ]

step 1: After double click the "aspnet_regsql.exe" file, the below

step 2: Click Next to continue the wizard. And select the "Configure SQL server application services"

step 3: Click Next to continue the wizard. And select the “Server and Database.”

step 4: View the Setting summary details.

step 5: By click finish to Complete the configuration.

SQL QUERY Library Management System

2013-06-28T18:03:00.002+05:30

Problem Scenario: This Scenario is to develop a Library Management System (LMS) to store information of the members, books, status of books issue, book availability and suppliers details.

List of Tables:

Table 1: LMS_MEMBERS

Table 2: LMS_SUPPLIERS_DETAILS

Table 3: LMS_FINE_DETAILS

Table 4:

LMS_BOOK_DETAILS
Table 5: LMS_BOOK_ISSUE

ER Diagram:

Database and Table Creations - DDL Queries:	Download LMS_DDL.sql

Loading Data - DML Queries:	Download LMS_DML.sql

Solve this Scenario.

Simple Questions:
Problem # 1:
Write a query to display the member id, member name, city and membership status who are all having life time membership. Hint: Life time membership status is “Permanent”.

Ans: select member_id,member_name,city,membership_status from LMS_MEMBERS where membership_status='permanent'

Problem # 2:
Write a query to display the book code, publication, price and supplier name of the book witch is taken frequently.

Ans: SELECT b1.book_code,b2.publication,b2.price,b3.supplier_name FROM(SELECT b1.book_code FROM LMS_BOOK_ISSUE b1 GROUP BY book_code HAVING COUNT(b1.BOOK_CODE) > 1) b1 JOIN LMS_BOOK_DETAILS b2 ON b1.book_code = b2.book_code JOIN LMS_SUPPLIERS_DETAILS b3 ON b2.supplier_id = b3.supplier_id

Problem # 3:
Write a query to display the member id, member name who have taken the book with book code 'BL000002'.

Ans: select b1. member_id,b1.member_name,b2.book_code from LMS_MEMBERS b1,LMS_BOOK_ISSUE b2 where b2.BOOK_CODE='BL000002'

Problem # 4:
Write a query to display the book code, book title and author of the books whose author name begins with 'P'.

Ans: select book_code,book_title,author from LMS_BOOK_DETAILS where AUTHOR like 'P%'

Problem # 5:
Write a query to display the total number of Java books available in library with alias name ‘NO_OF_BOOKS’.

Ans: select COUNT(category)as NO_OF_BOOKS from LMS_BOOK_DETAILS where CATEGORY='JAVA'

Problem # 6:
Write a query to list the category and number of books in each category with alias name ‘NO_OF_BOOKS’.

Ans: select category,COUNT(category)as NO_OF_BOOKS from LMS_BOOK_DETAILS group by CATEGORY

Problem # 7:
Write a query to display the number of books published by "Prentice Hall” with the alias name “NO_OF_BOOKS”.

Ans: select COUNT(publication) as NO_OF_BOOKS from LMS_BOOK_DETAILS where PUBLICATION='Prentice Hall'

Problem # 8:
Write a query to display the book code, book title of the books which are issued on the date "1^st April 2012".

Ans(without issue_date): select b1.book_code,b2.book_title from LMS_BOOK_ISSUE b1 inner join LMS_BOOK_DETAILS b2 on b1.BOOK_CODE=b2.BOOK_CODE where b1.DATE_ISSUE='2012-04-01'

Ans(with issue date): select b1.book_code,b1.DATE_ISSUE,b2.book_title from LMS_BOOK_ISSUE b1 inner join LMS_BOOK_DETAILS b2 on b1.BOOK_CODE=b2.BOOK_CODE where b1.DATE_ISSUE='2012-04-01'

Problem # 9:
Write a query to display the member id, member name, date of registration and expiry date of the members whose membership expiry date is before APR 2013.
Ans: select member_id,member_name,DATE_REGISTER,DATE_EXPIRE from LMS_MEMBERS where DATE_EXPIRE < '2013-04-01'

Problem # 10:
write a query to display the member id, member name, date of registration, membership status of the members who registered before "March 2012" and membership status is "Temporary"

Ans: select member_id,member_name,DATE_REGISTER,MEMBERSHIP_STATUS from LMS_MEMBERS where DATE_REGISTER < '2012-03-01' and MEMBERSHIP_STATUS='Temporary'

Problem #11:
Write a query to display the member id, member name who’s City is CHENNAI or DELHI. Hint: Display the member name in title case with alias name 'Name'.

Ans: select member_id,member_name as 'Name' from LMS_MEMBERS where CITY='chennai' or CITY='delhi'

Problem #12:
Write a query to concatenate book title, author and display in the following format.
Book_Title_is_written_by_Author
Example: Let Us C_is_written_by_Yashavant Kanetkar
Hint: display unique books. Use “BOOK_WRITTEN_BY” as alias name.

Ans: select book_title + '_is_written_by' + author as 'BOOK_WRITTEN_BY' from LMS_BOOK_DETAILS

Problem #13:
Write a query to display the average price of books which is belonging to ‘JAVA’ category with alias name “AVERAGEPRICE”.

Ans: select AVG(price) as 'AVERAGEPRICE' from LMS_BOOK_DETAILS where CATEGORY='java'

Problem #14:
Write a query to display the supplier id, supplier name and email of the suppliers who are all having gmail account.

Ans: select supplier_id,SUPPLIER_NAME,EMAIL from LMS_SUPPLIERS_DETAILS where EMAIL like '%gmail.com'

Problem#15:
Write a query to display the supplier id, supplier name and contact details. Contact details can be either phone number or email or address with alias name “CONTACTDETAILS”. If phone number is null then display email, even if email also null then display the address of the supplier. Hint: Use Coalesce function.

ANS: select Supplier_id,supplier_name,coalesce(contact,email,address) as contactdetails from Lms_suppliers_details

Problem#16:
Write a query to display the supplier id, supplier name and contact. If phone number is null then display ‘No’ else display ‘Yes’ with alias name “PHONENUMAVAILABLE”. Hint: Use ISNULL.

ANS: select SUPPLIER_ID,SUPPLIER_NAME,case when CONTACT is NULL then 'NO' when CONTACT is not null then 'YES' end as PHONE_NUM_AVAILABLE from LMS_SUPPLIERS_DETAILS

Problem#17:
Write a query to display the member id, member name, city and member status of members with the total fine paid by them with alias name “Fine”.

ANS: SELECT m.MEMBER_ID, m.MEMBER_NAME, m.CITY, SUM(f.FINE_AMOUNT) AS FINE FROM LMS_MEMBERS m INNER JOIN LMS_BOOK_ISSUE i ON m.MEMBER_ID = i.MEMBER_ID INNER JOIN LMS_FINE_DETAILS f ON i.FINE_RANGE = f.FINE_RANGE GROUP BY m.MEMBER_ID, m.MEMBER_NAME, m.CITY

Average Questions:
Problem # 1:
Write a query to display the member id, member name of the members, book code and book title of the books taken by them.

ANS: select MEMBER_ID,(select MEMBER_NAME from LMS_MEMBERS where MEMBER_ID=t1.MEMBER_ID )as MEMBER_NAME,BOOK_CODE,(select BOOK_TITLE from LMS_BOOK_DETAILS where BOOK_CODE=t1.BOOK_CODE)as BOOK_TITLE from LMS_BOOK_ISSUE t1

Problem # 2:
Write a query to display the total number of books available in the library with alias name “NO_OF_BOOKS_AVAILABLE” (Which is not issued). Hint: The issued books details are available in the LMS_BOOK_ISSUE table.

ANS: SELECT COUNT(t1.BOOK_CODE)AS NO_OF_BOOKS_AVAILABLE FROM LMS_BOOK_DETAILS t1 LEFT JOIN LMS_BOOK_ISSUE t2 ON t1.BOOK_CODE = t2.BOOK_CODE WHERE t2.BOOK_CODE IS NULL

Problem # 3:
Write a query to display the member id, member name, fine range and fine amount of the members whose fine amount is less than 100.

ANS: SELECT T1.MEMBER_ID,(SELECT MEMBER_NAME FROM LMS_MEMBERS WHERE MEMBER_ID=T1.MEMBER_ID)AS MEMBER_NAME,(SELECT T2.FINE_RANGE FROM LMS_MEMBERS WHERE MEMBER_ID=T1.MEMBER_ID)AS FINE_RANGE,(SELECT FINE_AMOUNT FROM LMS_FINE_DETAILS T2 WHERE FINE_RANGE= T1.FINE_RANGE)AS FINE_AMOUNT FROM LMS_BOOK_ISSUE T1 LEFT JOIN LMS_FINE_DETAILS T2 ON T1.FINE_RANGE = T2.FINE_RANGE WHERE T2.FINE_AMOUNT < 100

Problem # 4:
Write a query to display the book code, book title, publisher, edition, price and year of publication and sort based on year of publication, publisher and edition.

ANS: SELECT BOOK_CODE,BOOK_TITLE,PUBLICATION,BOOK_EDITION,PRICE,YEAR(PUBLISH_DATE) AS YEAR_OF_PUBLICATION FROM LMS_BOOK_DETAILS ORDER BY YEAR_OF_PUBLICATION

Problem # 5:
Write a query to display the book code, book title and rack number of the books which are placed in rack 'A1' and sort by book title in ascending order.

ANS: SELECT BOOK_CODE,BOOK_TITLE,RACK_NUM FROM LMS_BOOK_DETAILS where RACK_NUM='A1' ORDER BY BOOK_TITLE ASC

Problem # 6:
Write a query to display the member id, member name, due date and date returned of the members who has returned the books after the due date. Hint: Date_return is due date and Date_returned is actual book return date.

ANS: SELECT MEMBER_ID,(SELECT MEMBER_NAME FROM LMS_MEMBERS WHERE MEMBER_ID=T1.MEMBER_ID)AS MEMBER_NAME,DATE_RETURN AS DUE_DATE,DATE_RETURNED FROM LMS_BOOK_ISSUE T1 WHERE DATE_RETURN < DATE_RETURNED

Problem # 7:
Write a query to display the member id, member name and date of registration who have not taken any book.

ANS: SELECT T1.MEMBER_ID,MEMBER_NAME,DATE_REGISTER FROM LMS_MEMBERS T1 LEFT JOIN LMS_BOOK_ISSUE T2 ON T1.MEMBER_ID = T2.MEMBER_ID WHERE T2.MEMBER_ID IS NULL

Problem # 8:
Write a Query to display the member id and member name of the members who has not paid any fine in the year 2012.

ANS:
Using Sub Query:
select t1.MEMBER_ID,(select t2.MEMBER_NAME from LMS_MEMBERS t2 where t1.MEMBER_ID=t2.MEMBER_ID)as MEMBER_NAME from LMS_BOOK_ISSUE t1 where t1.DATE_RETURN >=t1.DATE_RETURNED and year(t1.DATE_RETURNED)=2012
(or)
Using Join:
select t1.MEMBER_ID,t2.MEMBER_NAME from LMS_BOOK_ISSUE t1 join LMS_MEMBERS t2 on t1.MEMBER_ID=t2.MEMBER_ID where t1.DATE_RETURN>=t1.DATE_RETURNED and YEAR(t1.DATE_RETURNED)=2012

Problem # 9:
Write a query to display the date on which the maximum numbers of books were issued and the number of books issued with alias name “NOOFBOOKS”.

ANS: select DATE_ISSUE,count(DATE_ISSUE) as NO_OF_BOOKS from LMS_BOOK_ISSUE group by DATE_ISSUE having COUNT(DATE_ISSUE)=(select MAX(counted) from (select COUNT(DATE_ISSUE) as counted from LMS_BOOK_ISSUE group by DATE_ISSUE) as t)

Problem # 10:
Write a query to list the book title and supplier id for the books authored by “Herbert Schildt" and the book edition is 5 and supplied by supplier ‘S01’.

ANS: select BOOK_TITLE,SUPPLIER_ID from LMS_BOOK_DETAILS where AUTHOR='Herbert Schildt' and BOOK_EDITION='5' and SUPPLIER_ID='S01'

Problem # 11:
Write a query to display the rack number and the number of books in each rack with alias name “NOOFBOOKS” and sort by rack number in ascending order.

ANS: select RACK_NUM,COUNT(book_code) as NO_OF_BOOKS from LMS_BOOK_DETAILS group by RACK_NUM

Problem # 12:
Write a query to display book issue number, member name, date or registration, date of expiry, book title, category author, price, date of issue, date of return, actual returned date, fine amount.

ANS: Select BOOK_ISSUE_NO,MEMBER_NAME,DATE_REGISTER,DATE_EXPIRE,BOOK_TITLE,CATEGORY, PRICE, DATE_ISSUE,DATE_RETURN,DATE_RETURNED,(select FINE_AMOUNT from LMS_FINE_DETAILS f where i.FINE_RANGE= f.FINE_RANGE)as FINE_AMOUNT from LMS_BOOK_ISSUE i,LMS_MEMBERS m,LMS_BOOK_DETAILS b where i.MEMBER_ID=m.MEMBER_ID and b.BOOK_CODE=i.BOOK_CODE

Problem # 13:
Write a query to display the book code, title, publish date of the books which is been published in the month of
December.

ANS: select BOOK_CODE,BOOK_TITLE,PUBLISH_DATE from LMS_BOOK_DETAILS where MONTH(PUBLISH_DATE)='12'

Problem # 14:
Write a query to display the book code, book title ,supplier name and price of the book witch takes maximum price based on each supplier.

ANS: select BOOK_CODE,BOOK_TITLE,SUPPLIER_NAME,PRICE from LMS_BOOK_DETAILS b inner join LMS_SUPPLIERS_DETAILS s on b.SUPPLIER_ID=s.SUPPLIER_ID where b.PRICE=(select MAX(PRICE) from LMS_BOOK_DETAILS k where b.SUPPLIER_ID=k.SUPPLIER_ID)

Problem # 15:
Write a query to display book code, book name, and publisher, how old the book is. Sorted as older to newer.
ANS: select BOOK_CODE,BOOK_TITLE,PUBLICATION,YEAR(GETDATE())-YEAR(DATE_ARRIVAL)as YEARS from LMS_BOOK_DETAILS order by YEARS desc

Complex Questions:
Problem # 1:
Write a query to display the book code, book title and supplier name of the supplier who has supplied maximum number of books. For example, if “ABC Store” supplied 3 books, “LM Store” has supplied 2 books and “XYZ Store” has supplied 1 book. So “ABC Store” has supplied maximum number of books, hence display the details as mentioned below.
Example:
BOOK_CODE       BOOK_TITLE               SUPPLIER_NAME
BL000008             Easy Reference for Java       ABC STORE
BL000001             Easy Reference for C            ABC STORE
BL000003             Easy Reference for VB         ABC STORE

ANS: select BOOK_CODE,BOOK_TITLE,SUPPLIER_NAME from LMS_BOOK_DETAILS B inner join LMS_SUPPLIERS_DETAILS S on B.SUPPLIER_ID = S.SUPPLIER_ID where S.SUPPLIER_ID in (select SUPPLIER_ID from LMS_BOOK_DETAILS group by SUPPLIER_ID having COUNT(SUPPLIER_ID)=(select MAX(cnt) from (select COUNT(*) as cnt from LMS_BOOK_DETAILS group by SUPPLIER_ID)as T))

Problem # 2:
Write a query to display the member id, member name and number of remaining books he/she can take with “REMAININGBOOKS” as alias name. Hint: Assuming a member can take maximum 3 books. For example, Ramesh has already taken 2 books; he can take only one book now. Hence display the remaining books as 1 in below format.
Example:
MEMBER_ID    MEMBER_NAME       REMAININGBOOKS
LM001                   RAMESH                          1
LM002                   MOHAN                           3

ANS: select m.MEMBER_ID,MEMBER_NAME,T.cb as REMAINING_BOOKS from LMS_MEMBERS m inner join (select a.member_id,COUNT(i.MEMBER_ID) as cb from LMS_MEMBERS a left outer join LMS_BOOK_ISSUE i on a.MEMBER_ID= i.MEMBER_ID group by a.MEMBER_ID)T on T.MEMBER_ID=m.MEMBER_ID

Problem # 3
Write a query to display the supplier id and supplier name of the supplier who has supplied minimum number of books. For example, if “ABC Store” supplied 3 books, “LM Store” has supplied 2 books and “XYZ Store” has supplied 1 book. So “XYZ Store” has supplied minimum number of books, hence display the details as mentioned below.
Example:
SUPPLIER_ID     SUPPLIER_NAME
S04                            XYZ STORE

ANS: select S.SUPPLIER_ID,SUPPLIER_NAME from LMS_BOOK_DETAILS B inner join LMS_SUPPLIERS_DETAILS S on B.SUPPLIER_ID = S.SUPPLIER_ID where S.SUPPLIER_ID in (select SUPPLIER_ID from LMS_BOOK_DETAILS group by SUPPLIER_ID having COUNT(SUPPLIER_ID)=(select MIN(cnt) from (select COUNT(*) as cnt from LMS_BOOK_DETAILS group by SUPPLIER_ID)as T))

Download Question & Answer: LMS.doc

.NET Framework Basics

2013-06-26T12:47:00.001+05:30

.NET Framework

The .NET Framework is a software framework developed by Microsoft that runs primarily on Microsoft Windows. It includes a large library and provides language interoperability (each language can use code written in other languages) across several programming languages. Programs written for the .NET Framework execute in a software environment, known as the Common Language Runtime (CLR), an application virtual machine that provides services such as security, memory management, and exception handling. The class library and the CLR together constitute the .NET Framework.

The .NET Framework's Base Class Library provides user interface, data access, database connectivity, cryptography, web application development, numeric algorithms, and network communications. Programmers produce software by combining their own source code with the .NET Framework and other libraries. The .NET Framework is intended to be used by most new applications created for the Windows platform. Microsoft also produces an integrated development environment largely for .NET software called Visual Studio.

CLR – Common Language Runtime

The .NET framework provides a run-time environment called the Common Language Runtime, which runs the code & provides services that make the development process easier.

Compilers and tools expose the common language runtime's functionality and enable you to write code that benefits from this managed execution environment. Code that you develop with a language compiler that targets the runtime is called managed code; it benefits from features such as cross-language integration, cross-language exception handling, enhanced security, versioning and deployment support, a simplified model for component interaction, and debugging and profiling services.

The common language runtime makes it easy to design components and applications whose objects interact across languages. Objects written in different languages can communicate with each other, and their behaviors can be tightly integrated.

Features:
1. The common language runtime manages memory, thread execution, code execution, code safety verification, compilation, and other system services.
2. The runtime enforces code access security.
3. The runtime also enforces code robustness by implementing a strict type-and-code-verification infrastructure called the common type system (CTS).
4. The runtime can be hosted by high-performance, server-side applications, such as Microsoft SQL Server and Internet Information Services (IIS).

.NET Framework Class Library

The .NET Framework class library is a collection of reusable types that tightly integrate with the common language runtime. The class library is object oriented, providing types from which your own managed code can derive functionality. This not only makes the .NET Framework types easy to use, but also reduces the time associated with learning new features of the .NET Framework. In addition, third-party components can integrate seamlessly with classes in the .NET Framework.

The .NET Framework types enable you to accomplish a range of common programming tasks, including tasks such as string management, data collection, database connectivity, and file access. In addition to these common tasks, the class library includes types that support a variety of specialized development scenarios. For example, you can use the .NET Framework to develop the following types of applications and services:

1. Console applications.
2. Windows GUI applications (Windows Forms).
3. Windows Presentation Foundation (WPF) applications.
4. ASP.NET applications.
5. Windows services.
6. Service-oriented applications using Windows Communication Foundation (WCF).
7. Workflow-enabled applications using Windows Workflow Foundation (WF).

.NET Framework 4.5

The .NET Framework is a development platform for building apps for Windows, Windows Phone, Windows Server, and Windows Azure. It consists of the common language runtime (CLR) and the .NET Framework class library, which includes classes, interfaces, and value types that support an extensive range of technologies. The .NET Framework provides a managed execution environment, simplified development and deployment, and integration with a variety of programming languages, including Visual Basic and Visual C#.

The following are the new features and improvements in the following areas of the .NET Framework 4.5.

.NET for Windows Store Apps
Portable Class Libraries
Core New Features and Improvements
Tools

.NET Framework 4

The .NET Framework is an application development platform that provides services for building, deploying, and running desktop, web, and phone applications and web services. It consists of the common language runtime (CLR), which provides memory management and other system services, and an extensive class library, which includes tested, reusable code for all major areas of application development.

The following are the new features and improvements in the following areas of the .NET Framework 4.

Application Compatibility and Deployment
Core New Features and Improvements
Managed Extensibility Framework
Parallel Computing
Web
Client
Data

.NET Framework 3.5

The .NET Framework is a technology that supports building and running the next generation of applications and Web services. The .NET Framework consists of the common language runtime (CLR) and the .NET Framework class library, which includes ADO.NET, ASP.NET, Windows Forms, and Windows Presentation Foundation (WPF). The .NET Framework provides a managed execution environment, simplified development and deployment, and integration with a wide variety of programming languages.

The following are the new features and improvements in the following areas of the .NET Framework 3.5.

.NET Compact Framework
.NET Framework Client Profile
Click Once
Common Language Runtime(CLR)
Cryptography
Networking
Windows Forms
LINQ

.NET Framework 3.0

The .NET Framework version 3.0 was issued solely to include the following technologies in the .NET Framework and in the Windows Software Development Kit (SDK):

Windows Communication Foundation (WCF)
Windows Presentation Foundation (WPF)
Windows Workflow Foundation (WWF)
Windows CardSpace

.NET Framework 2.0

The Microsoft .NET Framework version 2.0 extends the .NET Framework version 1.1 with new features, improvements to existing features, and enhancements to the documentation. This section provides information about some key additions and modifications.

The following are the new features and improvements in the following areas of the .NET Framework 2.

64-bit Platform Support
SQL cache dependency
Master Pages
Membership and roles
Distributed Computing
Generics and Generic Collections
Manifest-Based Activation
Security Exceptions
Serial I/O Device Support
Serialization
Web Services
Windows Forms
XML

Database Exploration with C# and SQL Server

2013-06-18T11:34:00.001+05:30

This is a easy way to explore the items from database.
In this Program 3 Modules having:

module 1:
    Select all database name and load into combo box.
module 2:
    Select all Table name from particular database and load into list box.
module 3:
    Select all the column name from particular table and load into list box.

Note: Here particular database or table means dynamic selection at run time.

namespace db_explore
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        //Variable declaration
                SqlConnection con;
                SqlCommand cmd;
                SqlDataReader dr = null;
                String st;

        //For database
        private void Form1_Load(object sender, EventArgs e)
        {
            //DB Connection string
            con = new SqlConnection();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
            con.Open();
            MessageBox.Show("Successfully Connected."); //message notification for database connection

            //select database and load into combo box
            st = "select name from sys.databases"; //database selection query
            cmd = new SqlCommand(st, con);
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                cbox.Items.Add(dr[0].ToString());   //add the database names into combo box
            }
            dr.Close();
        }

        //For Table name
        private void cbox_SelectedIndexChanged(object sender, EventArgs e)
        {
            lbox1.Items.Clear();    // clearing listbox1
            lbox2.Items.Clear();    // clearing listbox2
            String ts;
            ts = "SELECT * FROM " + cbox.SelectedItem + ".sys.Tables"; //table selection query for particular DB
            cmd = new SqlCommand(ts,con);
            dr = cmd.ExecuteReader();   //execute data reader
            while (dr.Read())
            {
                lbox1.Items.Add(dr[0].ToString()); //add the table names into listbox2 for particular DB
            }
            dr.Close();
        }

       //For column name
        private void lbox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            lbox2.Items.Clear();    //clearing listbox2
            String cs;
            //list the column from particular database & table
            cs = "SELECT column_name 'Column Name' FROM " + cbox.SelectedItem + ".INFORMATION_SCHEMA.columns WHERE table_name ='"+ lbox1.SelectedItem +"' ";
          // MessageBox.Show(cs);    //message for verification of query
            cmd = new SqlCommand(cs, con);
            dr = cmd.ExecuteReader();   //execute data reader
            while (dr.Read())
            {
                lbox2.Items.Add(dr[0].ToString()); //add the column names into listbox2 for particular table
            }
            dr.Close();
        }

        private void btn_can_Click(object sender, EventArgs e)
        {
            this.Close();   //close the application
        }
    }
}

It is a dynamic. We can select the database dynamically to explore the content for tables available in database and we can select the table name to explore the column names for particular table.

For download full program: db_explorer.rar

Sample output:

OOPS concept in C#

2013-06-17T18:20:00.001+05:30

1.Class:

Class is group of object that share common properties and relationships.
Class members are private by default.
Classes are reference type that is they are stored on heap.
Class can be inherited and but can be instantiated.

Class Employee
{
public in CustID;
public in CustName;
}

2.Structure:

Structure is collection of different types of data types.
Structure members are public by default.
Structure are value type that is they are stored on stack.
Structure can not be inherited but can be instantiated.

Struct Employee
{
public int CustID;
public string name;
}

3.Object:

Object is basic runtime entity OR object is instance of class.
Object consistsof data and function together.
Object allows designing systems that are more robust and portable through the proper application of abstraction.

public class Student
{
public string First_Name { get; set; }
public int Weight { get; set; }
public Person(string First_Name, int Weight)
{
First_Name = first_Name;
Weight = weight;
}
//Other properties, methods, events...
}
class Program
{
static void Main()
{
Student person1 = new Student("Anil", 66);
Console.WriteLine("Student First_Name = {0} Weight = {1}", person1.First_Name, person1.Weight);
}
}

Output:
Student First_Name = Anil Weight = 66

4.Abstraction:

Abstraction is one of the principle of object oriented programming. It is used to display only necessary and essential features of an object to ouside the world.Means displaying what is necessary and encapsulate the unnecessary things to outside the world.Hiding can be achieved by using "private" access modifiers.
Note - Outside the world means when we use reference of object then it will show only necessary methods and properties and hide methods which are not necessary.

namespace Abstraction
{
public abstract class Shape
{
private float _area;
private float _perimeter;
public float Area
{
get
{
return _area;
}
set
{
_area = value;
}
}
public float Perimeter
{
get
{
return _perimeter;
}
set
{
_perimeter = value;
}
}
public abstract void CalculateArea();
public abstract void CalculatePerimeter();
}
}

Advantages of abstraction are the hiding of implementation details, component reuse, extensibility, and testability. When we hide implementation details, we reveal a cleaner, more comprehensible and usable interface to our users. We are separating our interface from our implementation, and this makes component reuse more practical. Many, if not all of the object-oriented concepts we have discussed throughout this document play a role in the abstraction principle. Working together, their end goal is the same, to produce software that is flexible, testable, maintainable, and extensible.

5.Data Encapsulation:

Encapsulation, in the context of C#, refers to an object's ability to hide data and behavior that are not necessary to its user.
Encapsulation enables a group of properties, methods and other members to be considered a single unit or object.
Encapsulation is also known as information hiding.
An encapsulated object is often called an abstract data type.

public class School
{
private string Schooldepartname;
public string SchoolDepartname
{
get
{
return Schooldepartname;
}
set
{
Schooldepartname =value;
}
}
}
public class Departmentmain
{
public static int Main(string[] args)
{
School d= new School();
d.SchoolDepartname="Communication";
Console.WriteLine("The Dept. Name is :{0}",d.SchoolDepartname);
return 0;
}
}

Output:
The Dept. Name is : Communication

Benefits of Encapsulation :

In Encapsulation fields of a class can be read-only or can be write-only.
A class can have control over in its fields.
A class can change data type of its fields anytime but users of this class do not need to change any code.

6. Inheritance:

Inheritance is a way to form new classes (instances of which are called objects) using classes that have already been defined.
Inheritance is employed to help reuse existing code with little or no modification.
The new classes, known as Sub-class or derived class, inherit attributes and behavior of the pre-existing classes, which are referred to as Super-class or Base class.

C# supports two types of Inheritance mechanisms

1) Implementation Inheritance

2) Interface Inheritance

Implementation Inheritance:

When a class (type) is derived from another class (type) such that it inherits all the members of the base type it is Implementation Inheritance.

Interface Inheritance:

When a type (class or a struct) inherits only the signatures of the functions from another type it is Interface Inheritance.

Benefits of using Inheritance:

Once a behavior (method) or property is defined in a super class (base class),that behavior or property is automatically inherited by all subclasses (derived class).
Code reusability increased through inheritance
Inheritance provide a clear model structure which is easy to understand without much complexity.
Using inheritance, classes become grouped together in a hierarchical tree structure.
Code are easy to manage and divided into parent and child classes.

public class ParentClass
{
public ParentClass()
{
Console.WriteLine("Parent Constructor.");
}
public void print()
{
Console.WriteLine("Parent Class.");
}
}
public class ChildClass : ParentClass
{
public ChildClass()
{
Console.WriteLine("Child Constructor.");
}
public static void Main()
{
ChildClass child = new ChildClass();
child.print();
}
}

Output:
Parent Constructor.
Child Constructor.
Parent Class.

7. Polymorphism:

It allows you to invoke derived class methods through a base class reference during run-time. Polymorphism is extensively used in implementing Inheritance.

The Polymorphism can be classified into 2 types:

Compile Time Polymorphism (Method Overloading)
Run Time Polymorphism (Method Overriding)

public class Customer
{
public virtual void CustomerType()
{
Console.WriteLine("I am a customer");
}
}
public class CorporateCustomer : Customer
{
public override void CustomerType()
{
Console.WriteLine("I am a corporate customer");
}
}
public class PersonalCustomer : Customer
{
public override void CustomerType()
{
Console.WriteLine("I am a personal customer");
}
}
public class MainClass
{
public static void Main()
{
Customer[] C = new Customer[3];
C[0] = new CorporateCustomer();
C[1] = new PersonalCustomer();
C[2] = new Customer();
foreach (Customer CustomerObject in C)
{
CustomerObject.CustomerType();
}
}
}

Output:
I am a corporate customer.
I am a personal customer.
I am a customer.

Method Overloading ( Compile Time Polymorphism):

Method with same name but with different arguments is called method overloading.
Method Overloading forms compile-time polymorphism.

class A1
{
void hello()
{
Console.WriteLine("Hello");
}
void hello(string s)
{
Console.WriteLine("Hello {0}", s);
}
}

Method Overriding ( Run Time Polymorphism):

Method overriding occurs when child class declares a method that has the same type arguments as a method declared by one of its superclass.
Method overriding forms Run-time polymorphism.
Note: By default functions are not virtual in C# and so you need to write “virtual” explicitly. While by default in Java each function are virtual.

class parent
{
virtual void hello()
{
Console.WriteLine("Hello from Parent");
}
}
class child : parent
{
override void hello()
{
Console.WriteLine("Hello from Child");
}
}
static void main()
{
parent objParent = new child();
objParent.hello();
}

Output:
Hello from Child.

8.Interface:

Interface is nothing but an contract of the system which can be implemented on accounts.
.Net doesn't support the multiple inheritance directly but using interfaces we can achieve multiple inheritance in .net.
An Interface can only contains abstract members and it is a reference type.
Interface members can be Methods, Properties, Events and Indexers. But the interfaces only contains declaration for its members.
Class which inherits interface needs to implement all it's methods.
All declared interface member are implicitly public.

class Program
{
interface BaseInterface
{
void BaseInterfaceMethod();
}
interface DerivedInterface : BaseInterface
{
void DerivedToImplement();
}
class InterfaceImplementer : DerivedInterface
{
public void DerivedToImplement()
{
Console.WriteLine("Method of Derived Interface called.");
}
public void BaseInterfaceMethod()
{
Console.WriteLine("Method of Base Interface called.");
}
}
static void Main(string[] args)
{
InterfaceImplementer er = new InterfaceImplementer();
er.DerivedToImplement();
er.BaseInterfaceMethod();
Console.Read();
}
}

Output:
Method of Derived Interface called.
Method of Base Interface called.

Save Image into SQL Server via VB.net

2012-10-31T16:26:00.000+05:30

OpenFileDialog for browse the Image and showing in picture box.
OpenFileDialog1.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif"
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
sfile = OpenFileDialog1.FileName ' Store the file name in variable "sfile"
pbox.Image = System.Drawing.Bitmap.FromFile(sfile)
pbox.SizeMode = PictureBoxSizeMode.StretchImage
End If

Convert image into Byte using Filestream

  Try
Dim fs As FileStream
fs = New FileStream(sfile, FileMode.Open, FileAccess.Read) 'Pass the sfile value
Dim picByte As Byte() = New Byte(fs.Length - 1) {}
fs.Read(picByte, 0, System.Convert.ToInt32(fs.Length))
fs.Close()

Insert statement for SQL query
Dim sqltxt As String
sqltxt = "insert into nstudent values('" & txt_sID.Text & "','" & txt_fname.Text & "','" & txt_lname.Text & "','" & cmb_gender.Text & "','" & txt_dob.Text & "','" & txt_addr.Text & "',@photo)"

Store image as Binary value using SQLParameter
  Dim imgP As New SqlParameter
imgP.SqlDbType = SqlDbType.Binary
imgP.ParameterName = "photo"
imgP.Value = picByte
sqlcmd = New SqlCommand(sqltxt, con)
sqlcmd.Parameters.Add(imgP)
sqlcmd.ExecuteNonQuery()
MsgBox("Data Saved Successfully")
sqlcmd.Dispose()
  con.Dispose()
    Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Retrieve Image from database and set in Picture box
Sub rimage(ByVal imgno As Integer) 'User created Function "rimage"

   If pbox.Image IsNot Nothing Then
pbox.Image.Dispose()
End If

Dim arr() As Byte
arr = dr.Item(imgno) 'Pass the varaible imgno from funciton "rimage"
pbox.Image = Image.FromStream(New IO.MemoryStream(arr))
pbox.SizeMode = PictureBoxSizeMode.StretchImage
End Sub

Using select command for retrieving data from database
Dim s as String
s = "Select * from nstudent where sID = '" & valu & "'" 'Getting input using InputBox & store the value in string varaible
Dim cmd As New SqlCommand(s, con)
dr = cmd.ExecuteReader
Dim n As Byte
n = dr.Read()
If n > 0 Then
txt_sID.Text = dr.Item(0)
txt_fname.Text = dr.Item(1)
txt_lname.Text = dr.Item(2)
cmb_gender.Text = dr.Item(3)
txt_dob.Text = dr.Item(4)
txt_addr.Text = dr.Item(5)
Call rimage(6)    'Call the Function
Else
MsgBox("NotFound")
End If
If Not dr.IsClosed Then dr.Close()
End Sub