SqlBulkcopy in C#

Background

In a programming world, there are requirements where the developer need to Insert large number of records into a Database. Usually the developer uses SqlCommand class and Insert into query to insert records into database table. The SqlCommand Class inserts one record at a time into database table. So in case of large no of records, this approach will take so much time and resources. in such cases, SqlBulkCopy comes into picture.



What is SqlBulkCopy and How it is used?

SqlCommand Class resides in namespace System.Data.SqlClient.

This class improves the performance of inserting large data into the Table. It has several properties and methods to achieve this function.

Data to be inserted is filled in DataTable or read with IDataReader instance.

Program

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace sqlbulkcopy
{
class Program
{
static DataTable dt = new DataTable();
static void Main(string[] args)
{
dt.Columns.Add("ID");
dt.Columns.Add("CustomerName");
dt.Columns.Add("Customer_Type");
dt.Columns.Add("OrderID");
dt.Columns.Add("Date");
InsertlargeData();
}
internal static void InsertlargeData()
{
string connstring = ConfigurationManager.AppSettings["DBCS"].ToString();
SqlConnection con = new SqlConnection(connstring);
con.Open();
for(int i=1;i<=10000;i++)
{
dt.Rows.Add(i,"Sandy" + i, "prime" + i,i,DateTime.Now.ToString());
}
using (var sqlbul = new SqlBulkCopy(connstring))
{
sqlbul.BatchSize = 1000;
sqlbul.NotifyAfter = 1000;
sqlbul.ColumnMappings.Add("ID", "ID");
sqlbul.ColumnMappings.Add("CustomerName", "CustomerName");
sqlbul.ColumnMappings.Add("Customer_Type", "Customer_Type");
sqlbul.ColumnMappings.Add("OrderID", "OrderID");
sqlbul.ColumnMappings.Add("Date", "Date");
sqlbul.SqlRowsCopied += new SqlRowsCopiedEventHandler(rowcopied);
sqlbul.DestinationTableName = "Customers_2";
sqlbul.WriteToServer(dt);
}
}
private static void rowcopied(object sender,SqlRowsCopiedEventArgs data)
{
Console.WriteLine("Wrote " + data.RowsCopied + " records.");
}
}
}

Output

All the 10000 rows will be inserted into given Table.

Explanation

Here , we created a DataTable to fill with the large number of data.

We added the columns to the DataTable. The name and number of columns are same as that of the Table in SQL Server in which data to be inserted.

Then we established a connection to the SQL server table.

To insert the data , we used the for loop and written the values to insert.

Then using SqlBulkCopy we mapped the columns of the DataTable to Sql Server Table column using the property ColumnMappings.

To know the rows are getting copied , we defined the event and write on the console the number of rows getting copied

Properties

Batch Size

- the number of rows in a batch. Operations are carried in a batch.

Bulk Copy Timeout

- The number of seconds to complete the operation.

Column mappings

- defines the relation between column of the source DataTable and columns of the Destination table.

Destination Table Name

- The name of the table in which Data to be inserted.

Notify after

- defined no of rows before generating notification event.

Methods

There are many methods available. But we will study few important methods.

Write ToServer (Data Table)

- Copied all the rows in the provided DataTable to the Destination table.

Write ToServer Async (Data Table)

- Same as above but the rows are copied asynchronously.

Method Overriding in Java

What is Method Overriding- Method Overriding in Java is where the name of the method is same in Parent class and Child class but thr behavior of the method depends on the reference given to the object at the run time. In other words , sublclass method can implement the method and it's behavior according to it's requirement.

Let's have a look on below program.

Example

package methodOverriding;
//Class Vehicle
public class Vehicle {
void wheels()
{
System.out.println("Vehicles are of 2 or 4 wheels");
}
}
Class Car
public class Car extends Vehicle {
void wheels()
{
System.out.println("Cars are of 4 wheels");
}
}
//Class Main
public class OverRideDemo {
public static void main(String[] args) {
Vehicle v=new Vehicle(); //Vehicle reference and it's object
Vehicle c=new Car(); //Vehicle reference but Car object
v.wheels(); //method in Vehicle Class
c.wheels(); //method in Car class
}
}

Output

Vehicles are of 2 or 4 wheels
Cars are of 4 wheels

Explanation

In above program, we created two classes Vehicle and and it's subclass Car having same method name and behavior.

In the Main class we created object of Vehicle and it's reference and second object of Vehiclebut Car reference.

So first the method in Vehicle class is executed then method in Car class is executed. Because the object may be of Vehicle class but the reference is of Carclass.

Using super Keyword.

To access the method in Super class in subclass ,the super keyword is used.

Example

package methodOverriding;
//Class Vehicle
public class Vehicle {
void wheels()
{
System.out.println("Vehicles are of 2 or 4 wheels");
}
}
Class Car
public class Car extends Vehicle {
void wheels()
{
super.wheels();
System.out.println("Cars are of 4 wheels");
}
}
//Class Main
public class OverRideDemo {
public static void main(String[] args) {
Vehicle c=new Car(); //Vehicle reference but Car object
c.wheels(); //method in Car class
}
}

Output

Vehicles are of 2 or 4 wheels
Cars are of 4 wheels

Method Overriding Rules

The syntax and return type and name of the overriden method should be same as in parent class.

final method cannot be overridden.

Static method cannot be overridden.

To override a method , it should be inherited first.

SQL Server Functions - Date Functions

Below are the SQL server DATE Functions.

CURRENT_TIMESTAMP

- The function returns the current datetime of the system in a yyyy-dd-MM hh:mm:ss Format.

Syntax

CURRENT_TIMESTAMP

Example

SELECT CURRENT_TIMESTAMP as current_datetime

Output

current_datetime
2018-09-11 21:10:38.157

DATEADD

- This function adds the date/time to the specified date and returns the value.

Syntax

DATEADD(interval,value,date)

Parameters

interval- the date or time to return. this can be yyyy-year,mm- month,DD-Day and many more.
value- numeric value. the number of intervals.
date- the date in which intervals to be added.

Example

SELECT DATEADD(DD,3,GETDATE()) as newdatetime

Output

newdatetime
2018-09-14 21:40:00.600

DATEDIFF

-This function return the difference between two dates as specified by the interval. Syntax

DATEDIFF(interval,date1,date2)

Parameters

interval- the date or time to return. this can be yyyy-year,mm- month,DD-Day and many more.
date1- first date value.
date2- second date value.

Example

SELECT DATEDIFF(DD,GETDATE()-4,getdate()) as datedifference

Output

datedifference
4

Example 2

SELECT DATEDIFF(Q,'2018-01-01 00:00:00','2018-04-01 00:00:00') as quarterdiff

Output

quarterdiff
1

DATENAME

- This function return the part of the specified date as string value. Syntax

DATENAME(interval,date)

Example

SELECT DATENAME(YY,GETDATE()) as yearofdate

Output

yearofdate
2018

Example 2

SELECT DATENAME(MM,GETDATE()) as monthofdate

Output

monthofdate
September

DATEPART

- This function return the part of the date in integer format. Syntax

DATEPART(interval,value)

Example

SELECT DATEPART(MM,GETDATE()) as monthofdate

Output

monthofdate
9

DAY

- This function returns the day (from 1- 31) from the specified date. Syntax

DAY(date)

Example

SELECT DAY(GETDATE()) as dayofthemonth

Output

dayofthemonth 11

GETDATE()

- This function returns the current date and time of the system. Syntax

GETDATE()

Example

SELECT GETDATE() as currentdate

Output

currentdate
2018-09-12 20:00:01.607

GETUTCDATE()

- This function returns the current date and time of the system. Syntax

GETDATE()

Example

SELECT GETUTCDATE() as currentutcdate

Output

currentutcdate
2018-09-12 14:32:57.773

ISDATE(date)

- This functions return 1 if expression is valid date otherwise 0. Syntax

ISDATE(date)

Example

SELECT ISDATE('2018-09-12 14:32:57.773') as isvaliddate

Output

isvaliddate
1

MONTH(date)

- This function return the month from the provided date. Syntax

MONTH(date)

Example

SELECT MONTH('2018-09-12') as monthindate

Output

monthindate
9

YEAR(date)

- This function return the Year from the provided date. Syntax

YEAR(date)

Example

SELECT YEAR('2018-09-12') as yearindate

Output

yearindate
2018

Java program showing concept of Inheritance

What is Inheritance?
Inheritance is a feature of Object oriented language like JAVA where child class acquires properties and methods of it's parent class.

Program

package inheritance;

public class SuperDemo
{
public void showname()
{
System.out.println("This is my SuperClass");
}
}

public class DemoClass extends SuperDemo
{
public void display()
{
System.out.println("This is subclass");
}
public static void main(String[] args)
{
DemoClass demo=new DemoClass();
demo.showname();
demo.display();
}
}

Output

This is my SuperClass
This is subclass

Explanation

Above is simple program showing the inheritance in Java.

The program declares a class named SuperDemo and one method in it.

the second class DemoClass extends the SuperDemo class and declares another method in it.

In the Main method , object of subclass i.e DemoClass is created and called the method of SuperDemo class and then the method of DemoClass.

---

Using super

super keyword is used to access the members of superclass directly from subclass where the members of superclass and sub class having the same name.

It is also used to call superclass constructor from subclass.

Program

package inheritance;
public class Expensive
{
int tv_price=20000;
public void showExpense()
{
System.out.println("The price of tv is " + tv_price);
}
}

public class NonExpensive extends Expensive
{
int cooler=5000;
public void showExpense()
{
System.out.println("The price of cooler is " + cooler);
}
public void getExpense()
{
NonExpensive nonex=new NonExpensive();
//call subclass method
nonex.showExpense();
//call superclass method
super.showExpense();
//get the value of tv in superclass
int k=super.tv_price;
System.out.println(k);
}
public static void main(String[] args)
{
NonExpensive ex=new NonExpensive();
ex.getExpense();
}
}

Output

The price of cooler is 5000
The price of tv is 20000
20000

Explanation

Above program declares a superclass named Expensive and a variable 'tv' and assign a value to it. A method is implemented to show the price of tv

A subclass named Nonexpensive extends superclass and declared a method.

In the method of subclass the super keyword is used to access the method and member of a superclass.

Reading and Writing Files using StreamReader in C#

Reading and writing files is an important for any program. C# has provided many classes to perform operations on Files.
Below is the Program showing use of StreamReader and StreamWriter to write the Characters in the Files.

using System;
using System.IO;
using static System.Console;
namespace StreamDemo
{
class Program
{
static string line="";
static void Main(string[] args)
{
StreamWriter st = new StreamWriter("D:\\Test.txt",true);
string [] cars = { "Audi", "Jeep","Jaguar" };
foreach(string s in cars)
{
st.WriteLine(s);
}
st.Close();
StreamReader sr = new StreamReader("D:\\Test.txt");
while ((line = sr.ReadLine()) != null)
{
WriteLine(line);
}
sr.Close(); ReadLine();
}
}
}

Output

Explanation

Above Program writes the array of cars in the File character by character.

StreamReader and StreamWriter performs the operations on files character by characters.

We initialized the object of StreamWriter class and gave the path where the file is to be created.

If the files with the same name is already present there at the path, StreamWriter opens that file.

If the file at the given path has already some text , and the append property is set to true, the StreamWriter class will append the text at the end of existing text.

using foreach loop , we write the array of string is the file.

Then we close the connection of the StreamWriter to the file

Closing the open connection is necessary , otherwise you will not able to access that file with StreamReader. It will throw an error that file is already in use.

Then using the StreamReader object , we access that file.

Using While loop we read the containts of the file and display it on the console.

Let's do the same program in different way.

using System;
using System.IO;
using static System.Console;
namespace FileApplication
{
class Program
{
static void Main(string[] args)
{
string[] names = new string[5];
WriteLine("Enter names of fruits");
for (int i = 0; i < names.Length; i++)
{
names[i] = ReadLine();
}
using (StreamWriter sw = new StreamWriter("D:\\names2.txt"))
{
WriteLine("Fruit names are below");
foreach (string s in names)
{
sw.WriteLine(s);
}
}
// Read and show each line from the file.
string line = "";
using (StreamReader sr = new StreamReader("D:\\names2.txt"))
{
while ((line = sr.ReadLine()) != null)
{
WriteLine(line);
}
}
ReadKey();
}
}
}

Output

Explanation

In above program , we are taking user input in the array of strings and writing in the file at the given path.

We have not specified if the append is true or false, so by default it is false.

Here we have used the statement using so that the connection is automatically close with the close of using statement.

the program ask user for name 5 fruits and writes in the file and also displays on the console.

Interface in C#

interface is an another aspect of object oriented programming. An interface contains the declaration of properties,methods ,events. To implement these methods , a class must implement interface and define the methods in the interface. An interface is useful in a scenarios where there are multiple class that needs to have the same methods/functions. In this case , an interface can be defined containing only the method declaration and rest is the responsibility of the class to implement the interface and the methods according to class functionality.

A class can implement multiple interfaces. If a class implements an interface, It must implement all the methods declared in interface.

An Interface declaration is similar to class declaration. To declare an interface , interface keyword is used.

Syntax

public interface IAnimal {
void run();
void hunt();
}

Implementing Interface

Class c1:interface_name
{
}

Example

using System;
namespace InterfaceDemo
{
public interface Operations
{
void buyItems(string s1,string s2);
int showAmount();
}
}

Class Implementing interface.

using System;
namespace InterfaceDemo
{
public class ItemsList : Operations
{
private string item1;
private string item2;
private int amt;
public ItemsList(int a)
{
amt = a;
}
public void buyItems(string sI1,string sI2)
{
item1 = sI1;
item2 = sI2;
Console.WriteLine("Items to buy \" {0}\" , \"{1}\"", item1, item2);
}
public int showAmount()
{
return amt;
}
}
}

Main Class

using System;
namespace InterfaceDemo
{
class Program
{
static void Main(string[] args)
{
ItemsList i = new ItemsList(2000);
i.buyItems("Cooler", "Sofa");
int k=i.showAmount();
Console.WriteLine("Amount to be given is {0}", k);
Console.ReadKey();
}
}
}

Explanation

First we created an class file and declared an interface in it named operation

We declared two methods in this one with two parameters and other with return type.

Then we created as class called as ItemsList and implemented the interface operation.

We implemented both the methods defined in the interface in the class ItemsList

Finally in the main class , we created an object of ItemsList class and called the two methods.

Note- A class has to implement all the methods defined in the interface. Other wise program will throw an error.

Output

Program showing creating, reading and writing files in Java

Files also plays important role in any program. For example , if a program needs a log file to be used for storing the records of all the operations performed , then we can create a file and store at some location so that we can track activities performed by that program.

Java contains a package named Java.io is having multiple classes that are used to perform input output operation of Files.

Stream- a Stream is nothing but a sequence of data.There are two Streams in Java.

Inputstream - to data from Files.

Outputstreams- to write data in Files. Basically there are 3 streams in Java.

Byte Stream- performs I/O of 8 bit bytes.
Character Stream- used to perform input output on 16 bit Unicode characters.
Standard Stream

Byte Stream
The following program shows how to read and write bytes using two classes. FileInputStream and FileOutputStream.

package FilePackage;

import java.io.IOException;
import java.io.*;
public class FileClass {
public static void main(String[] args {
FileInputStream fin=null;
FileOutputStream fout=null;
try
{
fin=new FileInputStream("D:/input.txt");
fout=new FileOutputStream("D:/output.txt");
int n;
while((n=fin.read())!= -1)
{
fout.write(n);
}
}
catch(IOException ex)
{
System.out.println("Exception occured " + ex.toString());
}
finally
{
if(fin!=null)
{
fin.close();
}
if(fout!=null)
{
fout.close();
}
}

Explaination

FileInputStream and FileOutputStream are the two basic classes used for reading and writing bytes into files.

In above program, we created objects of FileInputStream and FileOutputStream classes and initialized them in try block with the file name provided to constructors.

Remember the File from which data needs to be read must be present at particular location. Otherwise program will throw FileNotFoundException

Then using while loop we read the input.txt file to the end and write the text into the output file.

We used catch block to handle the exception if occurred

In finally we closed the open connections to both the streams.

---

Character Stream

The following program shows how to read and write characters using two classes. FileReader and FileWriter.

package FilePackage;

import java.io.IOException;
import java.io.*;
public class FileClass {
public static void main(String[] args {
FileReader fin=null;
FileWriter fout=null;
try
{
fr=new FileReader("D:/input.txt");
fw=new FileWriter("D:/output.txt");
int n;
while((n=fr.read())!= -1)
{
fw.write(n);
}
}
catch(IOException ex)
{
System.out.println("Exception occured " + ex.toString());
}
finally
{
if(fr!=null)
{
fr.close();
}
if(fw!=null)
{
fw.close();
}
}

Explaination

FileReader and FileWriter are the two basic classes used for reading and writing characters into files.

In above program, we created objects of FileReader and FileWriter classes and initialized them in try block with the file name provided to constructors.

Remember the File from which data needs to be read must be present at particular location. Otherwise program will throw FileNotFoundException

Then using while loop we read the input.txt file to the end and write the text into the output file.

We used catch block to handle the exception if occurred

In finally we closed the open connections to both the streams.

---

Standard Streams

Like other programming languages , Java also provides mechanism for Standard input , standard output and standard Error.

Standard Input -Providing data to users program via standard input device i.e Keyboard.
Standard output- Producing output data provided by input to the output device i.e monitor.
Standard Error- Outout the Error data to the computer screen using the standard error stream.
Below Program demonstrate the use of Standard Input.

import java.io.*;
public class ReadStream {
public static void main(String args[]) {
InputStreamReader cin = null;
try {
cin = new InputStreamReader(System.in);
System.out.println("Enter the characters, press 'q' to quit.");
char ch;
do {
ch = (char) cin.read();
System.out.print(ch);
} while(ch != 'q');
catch(IOException ex) { System.out.println("Exception occurred" + ex.toString());
}
}finally {
if (cin != null) {
cin.close();
}
}
}
}

Please write your comment below

SQL Server Functions- Numeric Functions

Following are the SQL SERVER Numeric Functions.

ABS

- The ABS function returns the absolute value of the expression specified.

Syntax

ABS(number)

Example

select ABS(-542) as Absolute_Of_Number

Output

Absolute_Of_Number
542

AVG

- The AVG function returns the average value of the number.

Syntax

AVG(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select AVG(Experience) as Avrage_Experience

Output

Avrage_Experience
4

CEILING

- The CEILING function returns the smallest integer value that is greater than equal to the number specified in the expression.

Syntax

CEILING(number)

Example

select CEILING(23.24) as Ceiling_Number

Output

Ceiling_Number
24

COUNT

- The COUNT function returns the number of records in that particular table for expression specified. .

Syntax

Count(number)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select COUNT(Experience) as CountExperience

Output

CountExperience
4

FLOOR

- The FLOOR function is the opposite of CEILING function. IT returns the largest integer value that is less than equal to the number specified in the expression.

Syntax

FLOOR(number)

Example

select FLOOR(23.24) as floor_Number

Output

floor_Number
23

MAX

- The MAX function is used to obtain the maximum value of expression.

Syntax

MAX(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select MAX(Experience) as Max_Experience

Output

Max_Experience
7

MIN

- The MIN function is used to obtain the minimum value of expression.

Syntax

MIN(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select MIN(Experience) as Max_Experience

Output

Max_Experience
2

RAND

- The RAND function returns the random decimal number greater than 0 and less than 1.

Syntax

RAND(seed)

Example

select RAND() as Random_Number

Output

Random_Number
0.849042974736912

Note- The RAND function returns the any random value. The seed is optional. If seed is provided, it will give repeatable sequence of Random number every time.

ROUND

- The ROUND function returns the number rounded to certain number of decimal places.

Syntax

ROUND(number_to_round,decimal_places,operation)

Parameters

number_to_round- number to be rounded.
decimal_places- number of decimal places upto which number to be rounded.
operation- This is optional parameter. It can be 0 or 1. When specified as 0, it will round the result to the number of decima_places. Or if any value other than 0 is specified. It will truncate the result to the number of decimal_places. By defalut , value is 0.

Example

select ROUND(232.25,1) as Rounded_Number

Output

Rounded_Number
232.30

Example_2

select ROUND(232.25,1,1) as Rounded_Number

Output

Rounded_Number
232.20

SIGN

- The SIGN function returns the value indicating sign of the number.

Syntax

SIGN (number)

Example

select SIGN(25) as Sign_Of_Number

Output

Sign_Of_Number
1

If the number provided is >0 then returns 1 If the number provided is =0 then returns 0 If the number provided is <0 then returns -1

Example_2

select SIGN(-25) as Sign_Of_Number

Output

Sign_Of_Number
-1

SUM

- The SUM function returns the sum of the given expression.

Syntax

SUM(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select SUM(Experience) as Sum_Number

Output

Sum_Number
16

SQL Server Functions - String Functions

Following are the SQL server String Functions and their use in SQL.

ASCII

- The ASCII function returns the ASCII code for the particular/specified character.

Syntax

ASCII('character')

Example

SELECT ASCII('A') as Code_of_character

Output

Code_of_character
65

If more than one characters are specified, the ASCII functions will return the code for the first character of the word , other characters will be ignored.

Example

SELECT ASCII('Tech') as Code_of_character

Output

Code_of_character
84

CHAR

- The CHAR function is the opposite of ASCII function. It will return the Character of the specified code in the expression.

Syntax

CHAR('number')

Example

select char('65') as Character_Of_Code

Output

Character_of_code
A

CHARINDEX

- The CHARINDEX function returns the integer stating the location of a substring in a specified string.

Syntax

CHARINDEX(substring,string,start_position)

Parameters
substring-The substring to find in string.This is required.
string- The string to find in. This is required.
start_position- The is the position in string from the search will start- first position is 1. It is an optional parameter.


Example

select CHARINDEX('v','vishal') as Position_Of_Character

Output

Position_Of_Character
1

CONCAT

- The CONCAT function Concatenate two or more strings together.

Syntax

CONCAT(string_1,string_2,........string_n)

Parameters
string_1,string_2...string_n- strings to concat.

Example

select CONCAT('google','.com') as Concatenated_string

Output

Concatenated_string
google.com

DATALENGTH

- This function gives the length of an expression in bytes.

Syntax

DATALENGTH(expression)

Parameters
expression. string of which the length to calculate.

Example

select DATALENGTH('google') as Length_Of_String

Output

Length_Of_String
6

Note- DATALENGTH function also calculate the space.

Example

select DATALENGTH('google ') as Length_Of_String

Output

Length_Of_String
7

LEFT

- The LEFT function extracts a substring from the string starting from the left.

Syntax

LEFT(string,no_of_characters)

Parameters
string- strings from which the characters need to extract.
no_of_characters- no of characters to extract.

Example

select LEFT('google',2) as Extracted_String

Output

Extracted_String
go

Note- LEFT function also takes space into consideration if provided.

Example

select LEFT(' google',2) as Extracted_String

Output

Extracted_String
g

LEN

- The LEN function return the length of the string.

Syntax

LEN(expression)

Parameters
string - to calculate length.

Example

select LEN('google') as Length_Of_String

Output

Length_Of_String
6

Note- The LEN function does not count trailing spaces but it does count leading spaces in expression.

LOWER

- The LOWER function return the string into lowercase letter.

Syntax

LOWER (expression)

Parameters
string - to lowercase.

Example

select LOWER('GOOGLE') as Lowercase_String

Output

Lowercase_String
google

UPPER

- The UPPER function return the string into uppercase letter.

Syntax

UPPER (expression)

Parameters
string - to uppercase.

Example

select UPPER('google') as Uppercase_String

Output

Uppercase_String
GOOGLE

LTRIM

- The LTRIM function removes the leading spaces.

Syntax

LTRIM(expression)

Parameters
string - to remove leading spaces from.

Example

select LTRIM(' google' ) as removedleadingspace

Output

removedleadingspace
google

RTRIM

- The RTRIM function removes the trailing spaces.

Syntax

RTRIM(expression)

Parameters
string - to remove leading spaces from.

Example

select RTRIM('google ' ) as removedtrailingspace

Output

removedtrailingspace
google

PATINDEX

- The PATINDEX function returns the location of pattern in a string.

Syntax

PATINDEX (expression)

Parameters
pattern- the pattern to find. The pattern must be provided with wildcard characters
string- the string to search in.

Wildcard characters

%- matches string with any length including zero.
_ - matches single character.
[]- matches any characters provided in the bracket.
[^]- matches any characters which are not in the bracket.

Example

select PATINDEX('%goo%','google') as location_Of_Searched_Pattern

Output

location_Of_Searched_Pattern
google

Note- If supplied pattern not found , the function returns zero.

REPLACE

- The REPLACE function replaces the characters in the given string with another sequence of characters.

Syntax

REPLACE (string, string_to_replace,replacement_string)

Parameters
string - in which the characters to replace.
string_to_replace- the string to replace in string.
replacement_string- string to replace for string_to_replace.
Example

select REPLACE('KGS','K','S') as replaced_string

Output

replaced_string
SGS

RIGHT

- The RIGHT function return the substring from a string starting from the right.

Syntax

RIGHT(string,no_of_characters)

Parameters
string - the string from which the characters need to extract from .
no_of_characters - number characters to extract.

Example

select RIGHT('google',3) as Extracted_From_Right

Output

Extracted_From_Right
gle

SPACE

- The SPACE function returns the string with given spaces.

Syntax

SPACE(number)

Parameters
number- number of spaces to return.

Example

select SPACE(10) as SpaceReturn

Output

SpaceReturn

STR

- The STR function returns the string representation of the number.

Syntax

STR(number,length,decimal_places)

Parameters
number- number to convert to string.
length- the length of the returned string. Default length is 10
decimal_places- the number of decimals to display.

Example

select STR(54,4,1) as returnednumber

Output

returnednumber
54.0

STUFF

- The STUFF function deletes given number of characters from the string and inserts another characters at the position starting from the given position.

Syntax

STUFF(source_string,start,length,another_string)

Parameters
source_string- the string to modify.
start- the position in source string from which to delete characters and start inserting another characters.
length- the number of character to delete from source string.
another_string- the string to insert in place of deleted characters.

Example

select STUFF('DJJEE',1,3,'ggg') as returnedstring

Output

returnedstring
gggEE

SUBSTRING

- The SUBSTRING function extract substring from given string.

Syntax

SUBSTRING(source_string,start,number_of_characters)

Parameters
source_string- the string to modify.
start- the position to start from.
number_of_characters- the number of character to extract.

Example

select SUBSTRING('google',1,3) as returnedstring

Output

returnedstring
goo

Calling Stored Procedure in C#

In Previous post we learnt how to use Sqlparameter to pass the parameters to command.So that in this way we can avoid SQL Injection. If you have not read that post , please click on the given link.Using SQLParameter in C#
Today we are going to learn how to call Stored Procedure created in Database from C# using Sqlparameter . Just like we pass parameter to sqlcommand object , we will be passing parameters to the stored procedure. Let's understand in more detail.

Explanation

We have created a stored procedure in SQL Server names GetDetails with the input parameter as ID having INT datatype. The Stored procedure just select the records from a table in database having data of Employees based on ID passed to procedure.
Then we have a Windows Application program in C# which is having a textbox and a Datagridview and a Button. We are going to enter the ID of employee on the textbox and after click on button , we can view the details of the employee in the datagridview.
In the Back end , we have used sqlparameter to call the stored procedure and pass the ID parameter to it to retrieve the employee data.

Stored Procedure

USE [Test]
GO
/****** Object: StoredProcedure [dbo].[GetDetails] Script Date: 13/05/2018 11:59:40 AM ******/ SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDetails] @ID int
AS
BEGIN
SET NOCOUNT ON;
Select * from tbl_Record where ID=@ID
END

Program

using System;
using System.Data.SqlClient;
namespace CallStoredProc
{
    class Connection
    {
     public SqlConnection getcon()
     {
      SqlConnection con = new SqlConnection("data source=VISHAL-PC\\SQL2014;database=Test;uid=sa;password=sql@123");
      con.Open();
      return con;
     }
    }
}

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace CallStoredProc
{
    public partial class Form1 : Form
    {
     Connection con = new Connection();
     DataTable dt = new DataTable();
     public Form1()
     {
      InitializeComponent();
     }
     private void getdetailbutton_Click(object sender, EventArgs e)
     {
      SqlConnection sqlcon = con.getcon();
      SqlDataAdapter da = new SqlDataAdapter("GetDetails", sqlcon);
      da.SelectCommand.CommandType = CommandType.StoredProcedure;
      da.SelectCommand.Parameters.AddWithValue("@ID", Idbox.Text);
      da.Fill(dt);
      empgrid.DataSource = dt;
     }
    }
}

Output

Using SQLParameter in C#

In this post , I am going to show you how to use Sqlparameter class and it's methods and properties in C#.

Sqlparameter class is contained in System.Data.SqlClient Namespace. The properties and methods of this class is used when interacting with database. Sometimes, we want to filter data to be entered in database based on user input, in that case we can use sqlparameter Class. Also in another case, for example if we want to insert data in database, we generally use Sqlcommand class to directly insert values in database, which can lead to SQL Injection problem. So to avoid this problem , we can use Sqlparameter Class to insert data by using parameters.

SqlParameter para1 = new SqlParameter()

- Creating new instance of Sqlparameter.

ParameterName

=Name of the Parameter.

value

= Value of the parameter.

Below Program is using Sqlparameter class to insert data into Database using Windows Form Application.

Connection.cs File

using System;
using System.Data;
using System.Data.SqlClient;
namespace UseOfSQLParameter
{
    class Connection
    {
     public SqlConnection getcon()
     {
      SqlConnection con = new SqlConnection("data source=VISHAL-PC\\SQL2014; database=Test;uid=sa;password=sa@123");
      con.Open();
      return con;
     }
    }
}

Main Program.

using System;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace UseOfSQLParameter
{
    public partial class Form1 : Form
    {
     Connection conn = new Connection();
     public Form1()
     {
      InitializeComponent();
      comboBox1.Items.Add("Andhra Pradesh");
      comboBox1.Items.Add("Goa");       comboBox1.Items.Add("Rajasthan");
      comboBox1.Items.Add("Hariyana");
      comboBox1.Items.Add("Maharashtra");
      comboBox1.Items.Add("Delhi");
     }
     private void button1_Click(object sender, EventArgs e)
     {
      SqlConnection sqlcon = conn.getcon();
      SqlCommand cmd = new SqlCommand();
      SqlParameter para1 = new SqlParameter();
      {
       para1.ParameterName = "@name";
       para1.Value = textBox1.Text;
      }
      SqlParameter para2 = new SqlParameter();
      {
       para2.ParameterName = "@state";
       para2.Value = comboBox1.SelectedItem;
      }
      SqlParameter para3 = new SqlParameter();       {
       para3.ParameterName = "@joindate";
       para3.Value = dateTimePicker1.Value;
      }
      SqlParameter para4 = new SqlParameter();
      {
       para4.ParameterName = "@exp";
       para4.Value = textBox4.Text;
      }
      cmd.Parameters.Add(para1);
      cmd.Parameters.Add(para2);
      cmd.Parameters.Add(para3);
      cmd.Parameters.Add(para4);
      cmd.CommandText = "INSERT INTO tbl_Record (Name,State,joindate,Experience) values(@name,@state,@joindate,@exp);";
      cmd.Connection = sqlcon;
      cmd.CommandType = CommandType.Text;
      cmd.ExecuteNonQuery();
      MessageBox.Show("Employee Details Saved");
     }
    }
}

Output

Method Overriding in C#

Method overriding is the most powerful feature of object oriented programming. Method Overriding can be defined as changing the definition or behavior of the Methods of the same Inheritance/Hierarchy in order to override their logic. Method Overriding is used to achieve runtime polymorphism. Method Overriding is accomplished by using the three keywords.

Virtual

override

Base

new

Using the keywords virtual , override and Base we can extend or override the logic of the methods derived in Base class. But the keyword new keyword we can hide the method derived in the base class.

Let's see the use of each keyword in detail.

Virtual

- This keyword is used with the method which can be overridden.

override

- This keyword is used with the method which is overriding the same method derived in the base class.

Base

- This keyword is used to call the base class method in the derived class.

new

- This keyword is used to hide the base class implementation of method.

using System;
namespace Method_overriding
{
    class Override
    {
     public virtual void show()
     {
      Console.WriteLine("Baseclass Show");
     }
    }
    class override2:Override
    {
     public override void show()
     {
     base.show(); Calling base class method
     Console.WriteLine("Sub show");
     }
    }
    class Program
    {
     static void Main(string[] args)
     {
      Override over = new Override();
      over.show();
      Console.ReadLine();
     }
    }
}

Output

Baseclass Show

Explanation

We created a class override and a method show() with the keyword virtual.

Then we created another class override2 inherited from override and defined same method show() with the keyword override in order to override parent class method show().

In the subclass show() method, we called base class show() method using keyword base.

in the main method, we created Object of override class and called show() method.

Let's see another example

using System;
namespace Method_overriding
{
    class Override
    {
     public virtual void show()
     {
      Console.WriteLine("Baseclass Show");
     }
    }
    class override2:Override
    {
     public override void show()
     {
     base.show(); Calling base class method
     Console.WriteLine("Sub show");
     }
    }
    class Program
    {
     static void Main(string[] args)
     {
      Override over = new Override2();
      over.show();
      Console.ReadLine();
     }
    }
}

Output

Baseclass Show
Sub show

Explanation

We created a class override and a method show() with the keyword virtual.

Then we created another class override2 inherited from override and defined same method show() with the keyword override in order to override parent class method show().

In the subclass show() method, we called base class show() method using keyword base.

in the main method, we created Object of override class and gave the reference of override2 class and called show() method.

Now we will remove the Base.show() from the subclass show() method.

using System;
namespace Method_overriding
{
    class Override
    {
     public virtual void show()
     {
      Console.WriteLine("Baseclass Show");
     }
    }
    class override2:Override
    {
     public override void show()
     {
     base.show() removed
     Console.WriteLine("Sub show");
     }
    }
    class Program
    {
     static void Main(string[] args)
     {
      Override over = new Override2();
      over.show();
      Console.ReadLine();
     }
    }
}

In the above program , note we have created object of parent class but given the reference of subclass.

Output

Sub show

Use of new keyword

As you see above , with the virtual, override keywords we override the parent class method with subclass method. But if you see , if we created object of parent class and gave reference of subclass , then the subclass method will be called not base class method.
But in case of new keyword, there is slight difference. If you create object of Parent class and gave the reference of subclass , then the parent class method will be called, because the new keyword hides the implementation of base class method in derived class. see the below example.

using System;
namespace Method_overriding
{
    class Override
    {
     public virtual void show()
     {
      Console.WriteLine("Baseclass Show");
     }
    }
    class override2:Override
    {
     public new void show()
     {
          Console.WriteLine("Sub show");
     }
    }
    class Program
    {
     static void Main(string[] args)
     {
      Override over = new Override2();
      over.show();
      Console.ReadLine();
     }
    }
}

Output

Baseclass Show

Please comment below if you have any questions.

Function/Method Overloading in C#

In previous post , we learned what are the classes and methods and objects. If you have not seen that, here is the link to it.
Classes in C#

What is Function Overloading

Function Overloading is nothing but a function/Method with the same name but multiple function definition. The function are different from each other by Function Definition and the number of parameters passed to the function.
Function Overloading is one of the way we can implement static Polymorphism. Polymorphism stands for the term having multiple forms. The Polymorphism have two types .
1)Static .
2)Dynamic .
The function overloading is a Static Polymorphism.

Example

using System;
namespace FunctionOverloading
{
      class PrintData
      {
             public void show(int a)
            {
                    Console.WriteLine("Age of Raju is {0}", a);
            }
             public void show(double amount)
            {
                    Console.WriteLine("Amount he has taken is {0}",amount);
             }
             public void show(string s)
            {
                     Console.WriteLine("Name is {0}", s);
                     Console.ReadLine();
             }
      }
}
      class Program
      {
         static void Main(string[] args)
         {
           PrintData p = new PrintData();
           p.show(20);
           p.show(500.45);
           p.show("Raju");
         }
      }

Outout

Age of Raju is 20
Amount he has taken is 500.45
Name is Raju

Explanation

In the above program, We wrote the function public void show for three times to print three different values of different data type.int,double and string respectively.

But the difference is in each function we provided different data type of parameters to the function.

Next we created object of PrintData class in the main method.

in the main method , we called the all the three methods using object of PrintData class.

So it printed all the parameters passed to method on the console as seen in the output

Example 2

using System;
namespace Overloading_2
{
      class Calculate
      {
         public void calculation(int a ,int b)
         {
           Console.WriteLine("Addition of two numbers is {0} ",a+b);
         }
         public void calculation(int a,int b,int c)
         {
           Console.WriteLine("Addition of three numbers is {0} ", a + b + c);
           Console.ReadLine();
         }
        }
}
class Program
{
    static void Main(string[] args)
    {
       Calculate c = new Calculate();
       c.calculation(3, 3);
       c.calculation(2, 4, 3);
    }
}

Output

Addition of two numbers is 6
Addition of three numbers is 9

Explanation

In the above program , we created class named calculate

We defined and implemented two functions named CALCULATION with different number of parameter passed.

i.e two parameters for first function and three parameters for second function.

Despite having the same name , the program was executed successfully because we passed different number of parameters to both the functions implementing method overloading.

This is how method overloading is implemented.

SQL IN Operator

Using IN operator in SQL, We can SELECT, UPDATE or DELETE operations providing multiple values in the WHERE clause.

That means SQL query will be executed for those provided values in the IN clause. This IN clause works like OR condition. If one value supplied in the IN clause is not found the query will look for other values.
You can also provide SELECT Query in the IN clause.

Syntax (supply values)

SELECT column_name1,column_name2...column_name_n
FROM table_name WHERE
column_name IN (value1,value2...valuen)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Tarun Sharma	Maharashtra	2017-06-15 01:03:34.247	5
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Query_1

Select those employees from above table who's experience is either 7 or 5 years or both.

SELECT * FROM Employee_details
WHERE Experience in (7,5)

Output

ID	Name	State	JoinDate	Experience
2	Tarun Sharma	Maharashtra	2017-06-15 01:03:34.247	5
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Query_2

Select those employees from above table who belongs to state either punjab or Pune or both.

SELECT * FROM tbl_Record WHERE
State in ('Punjab','Pune')

Output

ID	Name	State	JoinDate	Experience
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3

Apply NOT to IN clause

Query_3

Select those employees from above table who's experience not in 3 or 5 years.

SELECT * FROM tbl_Record
WHERE Experience NOT IN (3,5)

Output

ID	Name	State	JoinDate	Experience
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Using SELECT query in IN clause

Syntax

SELECT column_name1,column_name2...column_name_n
FROM table_name WHERE
column_name IN (SELECT Query)

Consider below tables

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Tarun Sharma	Maharashtra	2017-06-15 01:03:34.247	5
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

ID	CustomerName	Customer_Type	OrderID	Date
1	Bob	Prime	334	2018-02-11 05:45:54.000
2	Raj	Non-Prime	364	2018-01-04 04:30:54.000

Query_4

SELECT * FROM tbl_Record
WHERE ID in (SELECT ID FROM customers)

Output

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3

Basic Calculator in c#

HI Programmers,
Today we are going to create a small basic calculator in C#. This calculator will perform operations like Addition,Subtraction,Multiplication and Division. This is basically a Windows Application. I choose to use windows Application over the Console Application for this program because it is a User Interface and also looks good and interesting to use. Below is the C# Window Application for calculator program.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Windows_Form_Activities
{
     public partial class Form1 : Form
     {
      int result;
      public Form1()
      {
       InitializeComponent();
      }
      private void button1_Click(object sender, EventArgs e)
      {
       if ((string.IsNullOrWhiteSpace(textBox1.Text)) || (string.IsNullOrWhiteSpace(textBox2.Text)))
       {
        MessageBox.Show("Please enter value first", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        return;
       }
       else
       {
        result = Convert.ToInt32(textBox1.Text) + Convert.ToInt32(textBox2.Text);
        MessageBox.Show("Addition is " + result);
       }
      }
      private void sub_Click(object sender, EventArgs e)
      {
       if ((string.IsNullOrWhiteSpace(textBox1.Text)) || (string.IsNullOrWhiteSpace(textBox2.Text)))
       {
        MessageBox.Show("Please enter value first","Error",MessageBoxButtons.OK,MessageBoxIcon.Warning);
        return;
       }
       else
       {
       result = Convert.ToInt32(textBox1.Text) - Convert.ToInt32(textBox2.Text);
       MessageBox.Show("Subtraction is " + result);
       }
}
        private void mul_Click(object sender, EventArgs e)
        {
        if ((string.IsNullOrWhiteSpace(textBox1.Text)) || (string.IsNullOrWhiteSpace(textBox2.Text)))
        {
         MessageBox.Show("Please enter value first", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
         return;
        }
        else
        {
         result = Convert.ToInt32(textBox1.Text) * Convert.ToInt32(textBox2.Text);
         MessageBox.Show("Multiplication is " + result);
        }
      }
      private void div_Click(object sender, EventArgs e)
      {
        if ((string.IsNullOrWhiteSpace(textBox1.Text)) || (string.IsNullOrWhiteSpace(textBox2.Text)))
        {
         MessageBox.Show("Please enter value first", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
         return;
        }
        else
        {
         result = Convert.ToInt32(textBox1.Text) / Convert.ToInt32(textBox2.Text);
         MessageBox.Show("Division is " + result);
        }
      }
     }
}

Explaination

The above application program calculator performs four basic operations like addition,subtraction,Multiplication and division.

We have taken two TextBoxes and Four Buttons for four different operations respectively.

The User have to enter two numbers in two Textboxes respectively.

After entering numbers , he has to click on button of which he wants to perform operations.

The operations will be performed and the result will be appeared as messagebox to user.

From the beginning , the user first need to deisgn UI for the application and then need to write code.

There are four events for each operation

You don't need to write events separately. You just double click on button in design and that event code will be automatically generated.

In above program, We first checked whether numbers are inserted in two textboxes or not. We perform that validation using IF statement.

Then We perform the operation based on the Button clicked.