Sample SQL Database script with data

DB Diagram:

DB Script:

create table zipcodes (

  zip int primary key,

  city  varchar(30),

  State varchar(20));

create table employees (

  eno  varchar(10) primary key,

  ename  varchar(30),

  zip  int references zipcodes,

  hire_date date);

create table books (

  bno  int primary key,

  bname  varchar(30),

  qoh  int not null,

  price  dec(6,2) not null);

create table customers (

  cno   int primary key,

  cname  varchar(30),

  street varchar(30),

  zip  int  references zipcodes,

  phone  char(12));

create table orders (

  ono  int  primary key,

  cno  int  references customers,

  eno  varchar(10)  references employees,

  received date,

  shipped date

);

create table odetails (

  ono  int  references orders,

  bno  int  references books,

  quantity int not null,

  primary key (ono, bno));

insert into zipcodes values (98225, 'Bellingham', 'WA'); 

insert into zipcodes values (95388, 'Winton', 'CA'); 

insert into zipcodes values (44242, 'Stow', 'OH'); 

insert into zipcodes values (61536, 'Hanna city', 'IL'); 

insert into zipcodes values (01254, 'Richmond', 'MA'); 

insert into zipcodes values (95124, 'San Jose', 'CA'); 

insert into zipcodes values (95382, 'Turlock', 'MA'); 

insert into zipcodes values (95380, 'Turlock', 'CA');

insert into employees values ('P0239400', 'Jones Hoffer',98225, '2000-12-12'); 

insert into employees values ('P0239401', 'Jeffrey Prescott',95388, '2006-01-01'); 

insert into employees values ('P0239402', 'Fred NcFaddeb',95124, '2008-09-01');

insert into books values (10506, 'Accounting 101',200, 129.99); 

insert into books values (10507, 'Management 101',159, 109.99); 

insert into books values (10508, 'Fraud Cases',190, 179.99); 

insert into books values (10509, 'CPA Review',65, 299.99); 

insert into books values (10601, 'Peachtree for Dummies',322, 49.99); 

insert into books values (10701, 'Financial Accounting',129, 164.99); 

insert into books values (10800, 'Managerial Accounting',155, 114.99); 

insert into books values (10900, 'Cost Accounting',122, 119.99); 

insert into books values (10901, 'Intermediate Accounting',123, 164.99); 

insert into books values (10902, 'XBRL in Nutshell',124, 109.99);

insert into customers values (23511, 'Michelle Kuan', '123 Main St.',98225, '360-636-5555'); 

insert into customers values (23512, 'George Myer', '237 Ash Ave.',95124, '312-678-5555'); 

insert into customers values (23513, 'Richard Gold', '111 Inwood St.',95124, '312-883-7337'); 

insert into customers values (23514, 'Robert Smith', '54 Gate Dr.',95388, '206-832-1221'); 

insert into customers values (23515, 'Christopher David', '777 Loto St.',98225, '360-458-9878'); 

insert into customers values (23516, 'Adam Beethoven', '234 Park Rd..',95380, '209-546-7299'); 

insert into customers values (23517, 'Lidwig Bach', '5790 Walnut St.',95382, '209-638-2712');

insert into orders values (1020, 23511, 'P0239400', '2009-10-18', '2009-10-20'); 

insert into orders values (1021, 23511, 'P0239400', '2009-10-29', '2009-10-31'); 

insert into orders values (1022, 23512, 'P0239401', '2009-11-10', '2009-11-13'); 

insert into orders (ONO, CNO, ENO, RECEIVED) values (1023, 23513, 'P0239402', '2009-11-15'); 

insert into orders (ONO, CNO, ENO, RECEIVED) values (1024, 23511, 'P0239400', '2009-11-16');

insert into odetails values (1020, 10506,1); insert into odetails values (1020, 10507,2); 

insert into odetails values (1020, 10508,2); insert into odetails values (1020, 10509,3); 

insert into odetails values (1021, 10601,4); insert into odetails values (1022, 10601,1); 

insert into odetails values (1022, 10701,2); insert into odetails values (1023, 10800,4); 

insert into odetails values (1023, 10900,1); insert into odetails values (1024, 10900,7);

How can a totally logical machine like a computer generate a random number?

Have you wondered how a random number is generated in programming languages on a totally logical device like a Computer?
Recently I read an article about it which throws a light on this interesting topic.


Here is the link:
http://computer.howstuffworks.com/question697.htm

Phone Numbers Comparison in Different Country Formats

In every country phone numbers are represented in its own format. For example : In some country a phone number could be in (914) 575-4231 format whereas in another country the same number could be in 914-575-42131 format. So if we want to compare two numbers represented in different format, we could do this by removing their formatting and compare their actual value.

using System;

using System.Collections;

class Program

{

    static void Main()

    {

        string num1 = "800-555-1212"; // Format -1

        string num2 = "(800) 555 1212"; // Format -2

        if (FetchDigitsOnlyFromPhoneNumber(num1) ==

        FetchDigitsOnlyFromPhoneNumber(num2))

        {

            Console.WriteLine("Number in both the formats are Equal !");

        }

        else

        {

            Console.WriteLine("Unequal Numbers !");

        }

        Console.Read();

    }

    private static string FetchDigitsOnlyFromPhoneNumber(string formattedNumber)

    {

        string actualNumber;

        actualNumber = formattedNumber;

        actualNumber = actualNumber.Replace("(", string.Empty);

        actualNumber = actualNumber.Replace(")", string.Empty);

        actualNumber = actualNumber.Replace("+", string.Empty);

        actualNumber = actualNumber.Replace("-", string.Empty);

        actualNumber = actualNumber.Replace(" ", string.Empty); //Blank

        actualNumber = actualNumber.Replace(".", string.Empty);

        return actualNumber;

    }

}

One liner Factorial calculating program using recursion in C#

using System;

public class Program

{

    //Using recursion

    static long Factorial(long number)

    {

        return ((number <= 1) ? 1 : number * Factorial(number - 1));

    }

    static void Main(string[] args)

    {

        long numToFindFact = 6;

        Console.WriteLine("The factorial of " + numToFindFact  + " is: {0}\n", Factorial(numToFindFact));

    }

}

Currency format of String

My article on this topis is at:



 

http://www.c-sharpcorner.com/UploadFile/0f68f2/converting-a-number-in-currency-format-for-different-culture/

Binding DataSource to a ComboBox with DataView

To explain how to bind data source to a ComboBox with DataView, I wrote an article for the following site:

http://www.c-sharpcorner.com/UploadFile/0f68f2/programmatically-binding-datasource-to-combobox-in-multiple/

Binding DataSource to a ComboBox with DataTable

To explain how to bind data source to a ComboBox with DataTable, I wrote an article for the following site:

http://www.c-sharpcorner.com/UploadFile/0f68f2/programmatically-binding-datasource-to-combobox-in-multiple/

Binding DataSource to a ComboBox with DataSet

To explain how to bind data source to a with DataSet, I wrote an article for the following site:

http://www.c-sharpcorner.com/UploadFile/0f68f2/programmatically-binding-datasource-to-combobox-in-multiple/

Binding DataSource to a ComboBox with List

To explain how to bind data source to a ComboBox with List Values, I wrote an article for the following site:

http://www.c-sharpcorner.com/UploadFile/0f68f2/programmatically-binding-datasource-to-combobox-in-multiple/

Binding DataSource to a ComboBox with Array

To explain how to bind data source to a ComboBox with Array values, I wrote an article for the following site:

http://www.c-sharpcorner.com/UploadFile/0f68f2/programmatically-binding-datasource-to-combobox-in-multiple/

Binding DataSource to a ComboBox with Enumeration Values

To explain how to bind data source to a ComboBox with Enumeration Values, I wrote an article for the following site:

http://www.c-sharpcorner.com/UploadFile/0f68f2/programmatically-binding-datasource-to-combobox-in-multiple/

Simple example of IEnumerable and IEnumerator in C#

Here is a simplest example of using IEnumerable and IEnumerator in C#. Since 'String' is an IEnumerable object, so we can enumerate it using IEnumerable and IEnumerator. 'String' object is just chosen for sake of simplicity. The way we are using "IEnumerable and IEnumerator" here can be extended to any IEnumerable object (such as List<T>, Dictionary<TK, TV>, Hashset, SortedSet etc)

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Collections;

namespace IEnumerableDemo

{

    class Program

    {

        static void Main(string[] args)

        {

            //The following example illustrates low-level use of IEnumerable and IEnumerator:

            string s = "How to use IEnumerable and IEnumerator";

            // Because string implements IEnumerable, we can call GetEnumerator():

            IEnumerator rator = s.GetEnumerator();

            // Iterating entire collection one by one until it reaches at the end

            while (rator.MoveNext())

            {

                // Getting the current object and cast it into its corresponding object with which

                // the IEnumerable collection is made (For example: String collection is made with char object)

                char curObject = (char)rator.Current;

                Console.Write(curObject + ".");

            }

            Console.Read();

        }

    }

}

Feel free to put your comments and concern if any.

Thanks,
Hemant

How to Create a Windows Service Dependency (Server 2003/2008)

Here is the link for my article on this topic for the C-SharpCorner site.

http://www.c-sharpcorner.com/UploadFile/0f68f2/adding-service-dependency-on-a-windows-service-through-regis/

Reading paragraph from a MS Word document using C#

Here I am going to share how we can read paragraphs from a Microsoft Word Documnet using Microsoft.Office.Interop.Word DLL.

(1) Add reference of Microsoft.Office.Interop.Word DLL into your project
(2) With the help of following class, we can read a specific paragraph of a MS Word Document.

   class WordDocReader

    {

        //To read paragraph contents of a Word document using Microsoft.Office.Interop.Word DLL

        public  string ReadFileContent(string path, int paraGraphNum)

        {

            int i = 0;

            StringBuilder sb = new StringBuilder();

            Microsoft.Office.Interop.Word.ApplicationClass wordApp = new ApplicationClass();

            object file = path;

            object nullobj = System.Reflection.Missing.Value;

            Microsoft.Office.Interop.Word.Document doc = wordApp.Documents.Open

                                                    (ref file, ref nullobj, ref nullobj,

                                                    ref nullobj, ref nullobj, ref nullobj,

                                                    ref nullobj, ref nullobj, ref nullobj,

                                                    ref nullobj, ref nullobj, ref nullobj);

            Microsoft.Office.Interop.Word.Paragraphs DocPar = doc.Paragraphs;

            // Count number of paragraphs in the file

            long parCount = DocPar.Count;

            // Step through the paragraphs

            while (i < parCount)

            {

                i++;

                if (i == paraGraphNum)

                {

                    sb.Append(DocPar[i].Range.Text);

                    break;

                }

            }

            doc.Close(ref nullobj, ref nullobj, ref nullobj);

            wordApp.Quit(ref nullobj, ref nullobj, ref nullobj);

            return(sb.ToString());

        }

    }

Setting background color in WPF RichTextBox

Recently I encountered a situation where I was to highlight a word in WPF RichTextBox. So I thought, I should share a small tip to show how to highlight a word (by setting background color) in a WPF Rich TextBox. Here is the code:

        /// <summary>

        /// This method highlights a word with a given color in a WPF RichTextBox

        /// </summary>

        /// <param name="richTextBox">RichTextBox Control</param>

        /// <param name="word">The word which you need to highlighted</param>

        /// <param name="color">The color with which you highlight</param>

        private void HighlightWordInRichTextBox(RichTextBox richTextBox, String word, SolidColorBrush color)

        {

            //Current word at the pointer

            TextRange tr = new TextRange(richTextBox.Document.ContentEnd, richTextBox1.Document.ContentEnd);

            tr.Text = word;

            tr.ApplyPropertyValue(TextElement.BackgroundProperty, color);

        }

Spellchecker in WPF RichTextBox using Microsoft Word Object Library

Here is my article for spelleing checking in WPF RichTextBox:

http://www.c-sharpcorner.com/UploadFile/0f68f2/spelling-checking-using-microsoft-word-object-library-in-wpf/

Palindrome Checking in C#

Here is a short code to do this:

       private string CheckPalinDrome(string text)
        {
            //Converting into Upper case and remove blank spaces in between
            text = textBox2.Text.ToUpper().Replace(" ", string.Empty);
            string revText = ReverseString(text);
            if (revText == text)
            {
               return("palindrome");
            }
            else
            {
                return ("Not palindrome");
            }
        }

        private string ReverseString(string s)
        {
            char[] arr = s.ToCharArray();
            Array.Reverse(arr);
            return new string(arr);
        }

How to set directory path for setup project in visual studio 2010

Here I am going to share how we can set directory path for our installed package.
Suppose you have a setup project already included in your main project. (To know how to add setup project in Visual Studio-2010, you may refer to article at:

http://www.c-sharpcorner.com/Blogs/9817/adding-a-setup-project-in-visual-studio-2010.aspx)








Now you want to set directory path for your install, so that your app get installed in the desired location. In order to do this, you need to follow steps as:

 1.Right Click on your Setup project -> View -> File System

 2.Then Click Application Folder, open property tab

 3.Set 'AlwaysCreate' property to TRUE.
  (This property creates a directory structure for your install, if directory structure doesn’t exist.)

 4.Then Set 'DeafultLocation' property to your desired location (e.g. C:\MyWinApps\).
 (You may choose any directory structure of your choice except some folders/Directories which are protected by Windows Operating System)

Automatic Windows Locking using Timer

I wrote an article for the same topic for C-SharpCorner site. Please go through the link below:

http://www.c-sharpcorner.com/UploadFile/0f68f2/automatic-system-locking-using-timer/

Thanks,
Hemant Srivastava

Converting DataSet to a Byte Array

Here is the code that can be used for converting a DataSet into its corresponding byte array. Sometimes when we need to send the data across the network through WCF service, we can get advantage of sending our data into a byte array. The reason behind to send the data in Byte Array format is that byte array is the one of the primitive data type available in .Net

        private byte[] ConvertDataSetToByteArray(DataSet dataSet)

        {

            byte[] binaryDataResult = null;

            using (MemoryStream memStream = new MemoryStream())

            {

                BinaryFormatter brFormatter = new BinaryFormatter();

                dataSet.RemotingFormat = SerializationFormat.Binary;

                brFormatter.Serialize(memStream, dataSet);

                binaryDataResult = memStream.ToArray();

            }

            return binaryDataResult;

        }