ADO.NET : Record Insert and Display Using StoredProcedure

Windows Application Screenshot :

***************************



****************************

Screenshot Of StoredProcedure in SQL Server 2005
================================================

================================================
StoredProcedure : GetEmployee
================================================
USE [ANGEL]
GO
/****** Object: StoredProcedure [dbo].[GetEmployee] Script Date: 09/17/2010 08:52:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[GetEmployee]

AS SELECT EmpNo,EmpName, EmpSal,DeptNo FROM Employee
====================================================
StoredProcedure : InsertUser
====================================================

USE [ANGEL]
GO
/****** Object: StoredProcedure [dbo].[InsertUser] Script Date: 09/17/2010 08:54:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertUser] (
@Username varchar(50), @Password varchar(50)
)
AS INSERT INTO Users VALUES(@Username, @Password)

==================================================

Code :
==================================================

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace WindowsApplication1
{
public partial class Form1 : Form
{

SqlConnection conn;
SqlCommand command;
SqlCommand command1;
SqlDataAdapter adapter;
DataSet ds;
public Form1()
{

InitializeComponent();
conn = new SqlConnection("Data Source=localhost; Initial Catalog=ANGEL; Integrated Security=SSPI");
conn.Open();

}

private void Form1_Load(object sender, EventArgs e)
{

command = new SqlCommand("GetEmployee", conn);
adapter = new SqlDataAdapter(command);
ds = new DataSet();
adapter.Fill(ds, "Employee");
dg_Data.DataSource = ds.Tables[0];


}

private void button1_Click(object sender, EventArgs e)
{
string username=Username.Text;
string password=Password.Text;

command1= new SqlCommand("InsertUser", conn);
command1.CommandType = CommandType.StoredProcedure;
command1.Parameters.Add("@Username", SqlDbType.VarChar).Value = username;
command1.Parameters.Add("@Password", SqlDbType.VarChar).Value = password;

int rows = command1.ExecuteNonQuery();
conn.Close();
if (rows == 1)
{
MessageBox.Show("Record Inserted successfully ...");
}
else
{
MessageBox.Show("Problem in insert !!! Check coding ");
}
Username.Text = "";
Password.Text = "";
}
}
}

=====================================================================

IEnumerable and Ienumerator Interfaces Details:

1) IEnumerable interface members helps foreach loop to iterate over a collection.
2) The method like GetEnumerator() of IEnumerable interface has to be defined to iterate a collection.
3) There is 1 method of IEnumerable interface. Which is IEnumerator GetEnumerator (). (Return type is Ienumerator type)
4) It returns an IEnumerator object that can be used to iterate through the collection.
5) Its not required to implement IEnumerable interface though we are overriding the GetEnumerator(), because we are using Namespace System.Collections. So it’s not mandatory to right IEnumerable interface.
6) We should implement the IEnumerable to use foreach iteration.
7) There are 2 public methods of IEnumerator Interface.
8) MoveNext() advances the enumerator to the next element of the collection.
9) Reset() sets the enumerator to its initial position, which is before the first element in the collection.
10) MoveNext() returns bool. And the Syntax is bool MoveNext().
11) If it’s true then enumerator was successfully advanced to the next element; false if the enumerator has passed the end of the collection.
12) After an enumerator is created or after the Reset method is called, an enumerator is positioned before the first element of the collection, and the first call to the MoveNext method moves the enumerator over the first element of the collection.
If MoveNext passes the end of the collection, the enumerator is positioned after the last element in the collection and MoveNext returns false. When the enumerator is at this position, subsequent calls to MoveNext also return false until Reset is called.
13) An enumerator remains valid as long as the collection remains unchanged.
14) If changes are made to the collection, such as adding, modifying, or deleting elements, the enumerator is irrecoverably invalidated and the next call to MoveNext or Reset throws an InvalidOperationException.
15) IEnumerator.Current Property gets the current element in the collection. Its syntax is Object Current { get; }

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Example:

Employee.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Ienumeratordemo
{
class Employee
{
private string name;
private int empno;


public Employee(int empno, string name)
{
this.empno = empno;
this.name = name;
}

public int Empno
{
get { return empno; }
set { empno = value; }
}
public string Name
{
get { return name; }
set { name = value; }
}
}
}

EmployeeInfo.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
namespace Ienumeratordemo
{
class EmployeeInfo //: IEnumerable
{

public Employee[] empArray = new Employee[3];


public EmployeeInfo()
{
empArray[0] = new Employee(1001, "Ajit");
empArray[1] = new Employee(1002, "Sunil");
empArray[2] = new Employee(1003, "Raj");

}


public IEnumerator GetEnumerator()
{
return empArray.GetEnumerator();
}
}
}


Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace Ienumeratordemo
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("IEnumerable / IEnumerator Demo\n");

EmployeeInfo empinfoObj = new EmployeeInfo();

// Hand over each Employee in the collection ?
foreach (Employee e in empinfoObj)
{
Console.WriteLine("{0} \t {1}",e.Empno, e.Name);
}


//manually iterate
IEnumerator i = empinfoObj.GetEnumerator();
i.MoveNext();

Employee myEmployee = (Employee)i.Current;
Console.WriteLine("{0} \t {1}", myEmployee.Empno, myEmployee.Name);
i.Reset();

Console.WriteLine(" *******************************************");

IEnumerator i1 = empinfoObj.GetEnumerator();
i1.MoveNext();


i1.MoveNext();




i1.MoveNext();

Employee myEmployee1 = (Employee)i1.Current;
Console.WriteLine("{0} \t {1}", myEmployee1.Empno, myEmployee1.Name);

Console.Read();


}
}
}

Output :

Object Sender & EventArgs Parameter in System.EventHandler Delegate

EventHandler Delegate : Represents the method that will handle an event that has no event data.
Namespace: System
Assembly: mscorlib (in mscorlib.dll)

Syntax :

public delegate void EventHandler(Object sender,EventArgs e)

sender
Type: System.Object
The source of the event.

e
Type: System.EventArgs
An EventArgs that contains event data.

************************************************************

Check the Code below :

private void btnShowDel_Click(object sender, EventArgs e)
{
int x = 6;
int y = 7;

int z = x + y;
MessageBox.Show("" + z);
MessageBox.Show(e.ToString());
}

On this above code, btnShowDel_Click is the eventhandler. Which is match with the predefined delegate System.EventHandler.

See the following code :

this.btnShowDel.Click += new System.EventHandler(this.btnShowDel_Click);

This code tells that, btnShowDel is an object and Click is the event. And btnShowDel raises the event. The event model in the .NET Framework is based on having an event delegate that connects an event with its handler. To raise an event, two elements are needed:

* A delegate that identifies the method that provides the response to the event.
* A class that holds the event data.

The delegate is a type that defines a signature, that is, the return value type and parameter list types for a method. You can use the delegate type to declare a variable that can refer to any method with the same signature as the delegate. The standard signature of an event handler delegate defines a method that does not return a value, whose first parameter is of type Object and refers to the instance that raises the event, and whose second parameter is derived from type EventArgs and holds the event data.If the event does not generate event data, the second parameter is simply an instance of EventArgs. Otherwise, the second parameter is a custom type derived from EventArgs and supplies any fields or properties needed to hold the event data.

EventHandler is a predefined delegate that specifically represents an event handler method for an event that does not generate data. If your event does generate data, you must supply your own custom event data type and either create a delegate where the type of the second parameter is your custom type, or use the generic EventHandler delegate class and substitute your custom type for the generic type parameter.

To associate the event with the method that will handle the event, add an instance of the delegate to the event. The event handler is called whenever the event occurs, unless you remove the delegate.

The following code example demonstrates the declaration of an event handler delegate that does not use event data. The EventHandler class is the type of the event delegate, sender is the object that raises the event, and e is an event data object that contains no data. The second line of code in the example defines the event member in your class for an event that has no data.

************************************************************

public delegate void EventHandler(Object sender, EventArgs e);
public event EventHandler NoDataEventHandler;

**************************************************************