Ref Versus Out keyword

(A) Ref Keyword

  1. When a parameter pass with ref keyword in function then function work with same variable value that is passed in function call. If variable value change then function parameter value also change.
  2. Both the function definition and function calling must explicitly use the ref keyword.
  3. In function call argument passed to a ref parameter must first be initialized.
  4. ref parameter variable should not be declare as a constant variable.
  5. It is not compulsory that ref parameter name should be same in both function definition and function call.

Illustration with an Example

using System;
    class Program
    {
        static void Add(ref int val)
        {
            val += 12;
            Console.WriteLine("Number in Method : {0}", val);
        }
        static void Main(string[] args)
        {
            int number = 13;
            Console.WriteLine("Number before Method call:{0}",number);
            Add(ref number);
            Console.WriteLine("Number after Method call:{0}",number);
            Console.Read();
        }
    }

Output:
Ref keyword output

(B) Out Keyword

  1. When a parameter pass with out keyword in function then function work with same variable value that is passed in function call. If variable value change then function parameter value also change.
  2. Both the function definition and function calling must explicitly use the out keyword.
  3. It is not necessary to initialize out parameter variable that is pass in function call.
  4. out parameter variable should not be declare as a constant variable.
  5. It is not compulsory that out parameter name should be same in both function definition and function call.

Illustration with an Example

using System;
    class Program
    {
        static void Add(out int val)
        {
            val = 12;
            val += 13;
            Console.WriteLine("Number in Method call:{0}",val);
        }
        static void Main(string[] args)
        {
            int number;
            Add(out number);
            Console.WriteLine("Number after Method Call:{0}",number);
            Console.Read();
        }
    }

Output:
out keyword

Although the ref and out keywords cause different run-time behaviour, they are not considered part of the method signature at compile time. Therefore, methods cannot be overloaded if the only difference is that one method takes a ref argument and the other takes an out argument.

Delegate

A delegate is an object that can refer to a method. Thus, when we create a delegate, we are creating an object that can hold a reference to a method. Furthermore, the method can be called through this reference. Thus, a delegate can invoke the method to which it refers.

The principal advantage of a delegate is that it allows us to specify a call to a method, but the method actually invoked is determined at runtime, not at compile time.

Some Features:

  1. Delegates can be declared either outside a class definition or as part of a class through the use of the delegate keyword.
  2. Delegates have two parts in the relationship: the delegate declaration and the delegate instance or static method.
  3. If an exception is thrown, the delegate stops processing methods in the invocation list. It does not matter whether or not an exception handler is present.
  4. The keyword delegate and the .NET infrastructure provided by the System.Delegate (all delegate types are derived) and System.Delegate.MulticastDelegate classes.
  5. Delegates are the heart and soul of event handling in .NET.
  6. It is a compile-time error for the same modifier to appear multiple times in a delegate declaration.
  7. Delegate types are implicitly sealed.

(A). Simple Delegate
Declaration of delegate:
delegate-modifier delegate return-type delegate-name(parameters)
Implementation of delegate:
Delegate-name delegate-object=new Delegate-name(method of class)
Illustration with an Example:

using System;
namespace SimpleDelegate
{
    public delegate int AddNumber(int i,int j);
    public class ImplementDelegate
    {
        public int Add(int i, int j)
        {
            return i + j;
        }
    }
    class Program
    {       
        static void Main(string[] args)
        {
            int sum=0;
            ImplementDelegate objimp = new ImplementDelegate();
            AddNumber d = new AddNumber(objimp.Add);
            sum=d(12, 13);
            Console.WriteLine("Addition of 12 and 13 is " + sum);
            Console.Read();
        }
    }
}

Out Put:
Delegate in C#

(B) MulticastDelegate

One of the most exciting features of a delegate is its support for multicasting. In simple terms, multicasting is the ability to create a chain of methods that will be called automatically when a delegate is invoked. Simply instantiate a delegate, and then use the + or += operator to add methods to the chain. To remove a method, use – or – =. If the delegate returns a value, then the value returned by the last method in the list becomes the return value of the entire delegate invocation. For this reason, a delegate that will make use of multicasting will often have a void return type.

Illustration with an Example:

using System;
namespace DelegateExample
{
   public delegate void MyDelegate(int i);
    public class DelegateImplement
    {
        public void ClassMethod(int i)
        {
            Console.WriteLine("Value of i in ClassMethod:{0}", i);
        }

        public static void StaticClassMethod(int i)
        {
           Console.WriteLine("Value of i in StaticClassMethod:{0}",i);
        }
        public void AnotherClassMethod(int i)
        {
           Console.WriteLine("Value of i in AnotherClassMethod:{0}",i);
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            DelegateImplement objimp = new DelegateImplement();

            MyDelegate d = new MyDelegate(objimp.ClassMethod);
            d(10);
            Console.WriteLine();
            d += new MyDelegate(DelegateImplement.StaticClassMethod);
            d(12);
            Console.WriteLine();
            d += new MyDelegate(objimp.AnotherClassMethod);
            d(15);
            Console.Read();
        }
    }
}

Output:Example of Delegate in C#

Normalization Part-I

Normalization is the systematic and scientific process for deciding which attributes should be grouped together in relation. It reduce the amount of space a database consumes , validating and improving the logical design. It is a series of test on a relation.

Goal:

  1. Eliminating redundant data.
  2. Storing related data in same relation.
  3. Promotes integrity.

Normal Form:

A normal form is a state of a relation that result from applying simple rules and guidelines regarding dependencies means relationship between attributes to that relation.

In practical applications , we ‘ll often use 1NF,2NF and 3NF along with the occasional 4NF. 5NF is very rarely uses.

1. First Normal Form(1NF):

1NF have following rules

  1. Elimination of repeating group.
  2. Create separate table for each group.
  3. Table should be atomic.
  4. Predecessor table and new table have relationship(often 1:Many or Many :1)
  5. In predecessor table each row identifies with unique column


Illustration with anExample

There are one relation to store user information and UserId is a primary key. In this relation each user can have one or more than one telephone number .

UserId Name Tel1 Tel2
1 Sandeep 9461162949 NULL
2 Raviendra 9416612949 9849166129
3 Pradeep 9929412061 NULL

Here Telephone Number is repeating group. Here is two phoneno if suppose here is five column and an user have only one telephone number than remaining five column will be waste and consume extra space in database. So this problem solution is First Normal Form(1NF).

UserId Name
1 Sandeep
2 Raviendra
3 Pradeep

UserId TelephoneNo
1 9461162949
2 9416612949
2 9849166129
3 9929412061

Now that is First Normal Form (1NF).

2. Second Normal Form (2NF):

2NF have following rules

  1. Relation should be in 1NF.
  2. Every non-key attribute is fully and functionally depends on primary key.
  3. Remove subsets of data and place them in separate table.
  4. Create relationship between new table and predecessor table using foreign key.
  5. All the attributes in the relation are component of primary key.

Inllustration with an Example
Here is a relation that contain user information and UserId is primary key.

UserId Name City State Zip
1 Sandeep Jaipur Rajasthan 302006
2 Rupendra Jhunjhunu Rajasthan 333801

Here we have a set of related columns {Zip,State,City}. Columns State and City depend on column Zip.So we create a separate table for set {Zip,State,City} where Zip is a primary key.

UserId Name Zip
1 Sandeep 302006
2 Rupendra 333801

Zip State City
302006 Rajasthan Jaipur
333801 Rajasthan Jhunjhunu

Now that is in Second Normal Form(2NF).

3.Third Normal Form(3NF):

3NF has following rules

  1. Relation should be in 2NF.
  2. Remove columns that are not depend upon primary key.

Illustration with an Example

There is relation that have employee information. Here EmployeeId is a primary key.

EmployeeId Name Company Location
1 Jaiveer XYZ.Pvt.Ltd Jaipur
2 Jitendra ABC.Pvt.Ltd Mumbai
3 Jogendra XYZ.Pvt.Ltd Jaipur

Here company location is not depend on EmployeeId. Location depends on Company so we remove this column. Below both tables have relationship of foreign key and ComapnyId is a primary key .

CompanyId Company Location
1 XYZ.Pvt.Ltd Jaipur
2 ABC.Pvt.Ltd Mumbai

EmployeeId Name CompanyId
1 Jaiveer 1
2 Jitendra 2
3 Jogendra 1

Now it is 3NF

Import Data From Text File To Database Table Using SSIS 2005

We first of all create a table tbl_contact in Sql server 2005. This table tbl_contact has following definition

Column Name Data Type Constraint/Description
user_id int Identity(1,1) Primary Key
name Varchar(50) Name of Person
lastname Varchar(50) Last Name of Person
city Varchar(50) City of Person

Now we have a CSV(Comma Separated Value) file. This is a text file that contain name of person, last name of person and city of person each field separated by a comma and each set{name,lastname,city} is separated by vertical bar(|).This file can have any name and its extension is .txt.
Now our functionality is import data from this CVS text file to database table tbl_contact ,so our function are 10 simple steps.
1. Start-All Programs-Microsoft SQL Server 2005-SQL Server Business Intelligence Development Studio-ok
2. File-New-Project-Integration Services Project.
Here we give Integration Services Project name and location where we are create this project and click OK.

3. Drag and Drop Data Flow Task from ToolBox to Control Flow of Package.dtsx

4. Now we switch from Control Flow to Data Flow. In Data Flow pane we drag and drop Flat File Source for our CVS Text File.

5. Now Right Click in Connection Managers Pane -select New Flat File Connection…
In this Fill Connection Manager name (can be any name no special), File name(Text file for import data) and select Header row delimiter(Here use of Comma(,))

6. Now Flat File Connection Manager Editor in left Pane , Click on Columns Tab and define Row delimiter(Here Vertical Bar{|} same as CVS File) and Column delimiter (Same as pervious Comma{,}) and click OK.

7. Now Drag and drop OLE DB Destination in Data Flow Pane from ToolBox. OLE DB Destination should be lower from Flat File Source and connect Flat Source File to OLE DB with green arrow. This green arrow represents Source to Destination of data.

8. Now Double Click on OLE DB Destination and define Connection to database which we create in sql server 2005(here is TEST)and select a table tbl_contact for insert data from CVS Text file.

9. Now left pane of OLE DB Destination Editor select mapping for column relationship between table tbl_contact and CVS Text file columns and Click OK.

10. Now Execute means press F5 and Show result in table tbl_conatct

COMMIT and ROLLBACK in Sql Server

SQL provides many transaction management features. SQL commands COMMIT, ROLLBACK and SAVEPOINT helps in managing the transaction.

The COMMIT command is the transactional command used to save changes made by a transaction to the database.The COMMIT command will save all changes to the database since the last COMMIT or ROLLBACK command. Frequent commits in the case of transaction involving large amount of data is recommended. But too many commits can affect performance. In many implementations, an implicit commit of all the changes is done, if user logs off from the database.

The ROLLBACK command is the transactional control command to undo the transactions that have not already been committed to the database. The ROLLBACK command can be issued to undo the changes since the last COMMIT or ROLLBACK.

Illustration with an Example

There are two relational tables one for user registration information (tbl_userinfo) and another for user login information(tbl_login). Both tables have primary key foreign key relationship.

(A).User registration Information(tbl_userinfo)

Column Name Data Type Constraint/Description
user_id Int Identity(1,1) Primary Key
name Varchar(50) Name of User
email Varchar(50) Email of User

(B).user login information(tbl_login)

Column Name Data Type Constraint/Description
login_id Int Identity(1,1) Primary Key
user_id Int Foreign Key with tbl_userinfo
user_name Varchar(50) User Name for login
password Varchar(50) User Password for login

(A)General Transaction: In this transaction userreg , one table tbl_userinfo is affected and one row is entered and another table tbl_login is not affected. Here first insert query is successful executed but second insert query is not successful executed due to table tbl_login where column login_id is primary key and auto increment so we can’t pass value
and CATCH block will be executed.

begin try
begin tran userreg
declare @userid int
insert into tbl_userinfo(name, email)
values(‘sandeep’,’sandeep.shekhawat88@gmail.com’)
select @userid=max(user_id) from tbl_userinfo
insert into tbl_login(login_id, user_id, user_name, password)
values(2,@userid,’singh’,’singh’)
end try

begin catch
print ‘ónly one table entery’
end catch

(B)Implementation of ROLLBACK command: In this transaction userreg , one table tbl_userinfo is affected and one row is entered and another table tbl_login is not affected. Here first insert query is successful executed but second insert query is not successful executed due to table tbl_login where column login_id is primary key and auto increment so we can’t pass value. So CATCH block is executed here and ROLLBACK Command executed. After ROLLBACK Command execution our database change are undo means here tbl_userinfo row entry remove and both tables have no row .

begin try
begin tran userreg
declare @userid int
insert into tbl_userinfo(name, email)
values(‘sandeep’,’sandeep.shekhawat88@gmail.com’)
select @userid=max(user_id) from tbl_userinfo

insert into tbl_login(login_id, user_id, user_name, password)
values(2,@userid,’singh’,’singh’)
end try

begin catch
rollback tran userreg
end catch

(C)Implementation of COMMIT command: In this transaction userreg, both Insert queries successful executed. After successfully execution of queries, transaction userreg is committed and permanently save database changes.

begin try
begin tran userreg
declare @userid int
insert into tbl_userinfo(name, email)
values(‘sandeep’,’sandeep.shekhawat88@gmail.com’)
select @userid=max(user_id) from tbl_userinfo

insert into tbl_login( user_id, user_name, password)
values(@userid,’singh’,’singh’)
commit tran userreg
end try

begin catch
rollback tran userreg
end catch

User Defined Function in Sql Server Part-I

A user-defined function (UDF) is a prepared code segment that can accept parameters ( from 0 to 1024), process some logic, and then return some data(either a scalar value or a table).

Different Kinds of User-Defined Functions created are:

  1. Scalar User-Defined Function
  2. Inline Table-Value User-Defined Function
  3. Multi-statement Table-Value User-Defined Function

Scalar User-Defined Function

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

Illustration with an Example
We have a table tbl_student. We want generate a registration number for each new student that admission in college.So we create a function dbo.Registration_Number() that has return type varchar(50) which is a registration number for new student

Student Table(tbl_student):

Column Name Data Type Constraint/Description
Student_id int Identity(1,1) Primary Key
Name varchar(50) Name of student
Registration_No varchar(50) New Registration no for each student

(A) Create a function for Registration Number
create function Registration_Number()
returns varchar(50)
as
begin
declare @totalrow int
declare @reg_no varchar(50)
select @totalrow=count(*) from tbl_student
set @reg_no=’STU’+cast(@totalrow as varchar(5))
return @reg_no
end

(B) Implementation of function
insert into tbl_student(Name,Registration_No) values(‘Shekhawat’,dbo.Registration_Number())

OutPut

Student_id Name Registration_No
1 Shekhawat STU0

Trigger in Sql Server

Triggers are executed by the database when specific types of data manipulation commands are performed on specific tables. Such commands may include inserts, updates and deletes. Updates of specific columns may also be used a triggering events.

A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level.

Illustration with an example

There are two database tables one  for stock of items that contain stock of items and another for sale of items. Both have relationship.

(A) Table Stock (tbl_stock) :

Column Name Data Type Constraint/Description
stock_id int Identity(1,1) primary key
Item_id int Foreign key
Stock int Total stock of items

(B) Table Sale(tbl_sale) :

Column Name Data Type Constraint/Description
Sale_id int Identity(1,1) primary key
stock_id int Foreign key
sale int Total sale of items

(1)INSERT : Trigger on table tbl_sale when new sale is enter in table tbl_sale then update existing stock in table tbl_stock

create trigger trginsert
on tbl_sale
after insert
as
declare @sale int
declare @id int
select @sale=sale,@id=stock_id from inserted
update tbl_stock set stock=stock-@sale where stock_id=@id

(2)DELETE: Trigger on table tbl_sale when existing sale is delete then update existing stock of table tbl_stock

create trigger trgdelete
on tbl_sale
after delete
as
declare @sale int
declare @id int
select @sale=sale,@id=stock_id from deleted
update tbl_stock set stock=stock+@sale where stock_id=@id

(3)UPDATE:Trigger on table tbl_sale when existing sale is update in table tbl_sale then update existing stock in table tbl_stock

create trigger trgupdate
on tbl_sale
after update
as
declare @sale int
declare @saleold int
declare @id int
select @saleold=sale from deleted
select @sale=sale,@id=stock_id from inserted
update tbl_stock set stock=(stock+@saleold)-@sale where stock_id=@id

Follow

Get every new post delivered to your Inbox.