Friday, January 28, 2011

Virtual Methods in C-Sharp


The virtual keyword is used to modify a method, property, indexer or event declaration, and allow it to be overridden in a derived class. For example, this method can be overridden by any class that inherits it:


A virtual inherited property can be overridden in a derived class by including a property declaration that uses the override modifier.


When a virtual method is invoked, the run-time type of the object is checked for an overriding member in derived classes if not it will use base method.
By default, methods are non-virtual. You cannot override a non-virtual method.
You cannot use the virtual modifier with the static, abstract, private or override modifiers.

It is an error to use the virtual modifier on a static property.

Virtual properties behave like abstract methods, except for the differences in declaration and invocation syntax. The main difference between abstract method and virtual method is abstract method doesn't have declaration where as virtual method does.

Here is the example
class Fruits
    {
        public virtual void Name()
        {
            // its a generic here it can be any fruit like Apple,Banana,Mango etc.
            Console.WriteLine("Iam a fruit");
        }
       
    }
    class Apple : Fruits
    {
        public override void Name()
        {
            //base.Name(); here using this you can directly use base method
            // or you can override it.
            Console.WriteLine("Iam a Apple");
        }
    }
    class Mango : Fruits
    {
        public override void Name()
        {
            Console.WriteLine("Iam a Mango");
        }
    }
    class Banana : Fruits
    {
        public override void Name()
        {
            Console.WriteLine("Iam a Banana");
        }
    }

Importance of Override Keyword
The override modifier is required to extend or modify the abstract or virtual implementation of an inherited method, property, indexer, or event.
An override method provides a new implementation of a member inherited from a base class. The method overridden by an override declaration is known as the overridden base method. The overridden base method must have the same signature as the override method. For information on inheritance, see Inheritance.


You cannot override a non-virtual or static method. The overridden base method must be virtual, abstract.


An override declaration cannot change the accessibility of the virtual method. Both the override method and the virtual method must have the same access level modifier.
You cannot use the modifiers new, static, virtual, or abstract to modify an override method.
An overriding property declaration must specify the exact same access modifier, type, and name as the inherited property, and the overridden property must be virtual, abstract, or override.

Microsoft Visual C# 2010 Step by Step

Thursday, January 27, 2011

Inserting Record and Binding Data to Gridview Using Stored Procedures with Linq to Sql



In this post I explained How to insert a record using linq to Sql and how to get the data and append to grid using StoredProcedures.

For this I have taken two tables named Department and Employee

Please Create tables and stored procedures as follows.

Department Table

CREATE TABLE [dbo].[Dept](
      [DeptId] [int] IDENTITY(1,1) CONSTRAINT [PK_Dept] PRIMARY KEY CLUSTERED ,
      [DeptName] [nvarchar](20) NULL)

Insert into Dept Values('Software')
Insert into Dept Values('HR')

Employee Table

CREATE TABLE [dbo].[Employee](
      [EmpId] [int] CONSTRAINT [PK_Employee] PRIMARY KEY IDENTITY(1,1) ,
      [EmpName] [nvarchar](50) NULL,
      [Designation] [nvarchar](50) NULL,
      [Location] [nvarchar](50) NULL,
      [Deptid] int CONSTRAINT FK_Employee_Dept FOREIGN KEY REFERENCES dbo.[Dept](DeptId))



CREATE PROCEDURE GetDepartmentDetails
      -- Add the parameters for the stored procedure here
     
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      Select * from Dept
END

 CREATE PROCEDURE GetEmployeeDetails     
     AS
     BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
     
      SELECT E.EmpName,E.Designation,E.Location,D.DeptName
    from Employee E
    Inner Join
    Dept D
    on D.DeptId=E.DeptId   
 END

 CREATE PROCEDURE InsertEmployeeDetails
      -- Add the parameters for the stored procedure here
      @EmpName nvarchar(50),
      @Designation nvarchar(50),
      @Location nvarchar(50),
      @DeptId  int
      AS
      BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      Insert into Employee values(@EmpName,@Designation,@Location,@DeptId)
       Return @@RowCount

     END

Take new project with name LinqtoSql 

 
Click on ok button to create new project




Right click on Solution explorer click on add new item. Here you will find dbml classes add dbml class named as EmployeeDept 

  
Now we need to connect sqlserver through Server Explorer, if you do not find server explorer on left side corner please go to view and select server explorer
right click on dataconnections in  Server explorer click on add connection.

 you will have a option to choose data source type, for our project we have selected Microsoft SqlServer then click on continue.

Here we need select Server name and Database Name once you have selected Server name and Database Name test connection weather succeed.


Once finished the  connection with SqlServer you can get all the tables and Stored procedures.


Just drag and drop Tables and Stored Procedures on the dbml Class Surface.visual studio automatically creates properties for table and methods for stored procedures.


Inserting Record
copy and paste flowing html code in asp page.here i have taken three textboxes, one dropdownlist and one button.

<div>
   <table>
   <tr>
   <td colspan="2">
       <asp:Label ID="lblMessage" runat="server" Font-Bold="True" ForeColor="Blue"
         ></asp:Label>
   </td>
   </tr>
   <tr>
   <td>
      EmpName
   </td>
   <td>
       <asp:TextBox ID="txtEmpName" runat="server"></asp:TextBox>
   </td>
   </tr>
   <tr>
   <td>
       Designation</td>
   <td>
       <asp:TextBox ID="txtDesignation" runat="server"></asp:TextBox>
   </td>
   </tr>
   <tr>
   <td>
       Location</td>
   <td>
       <asp:TextBox ID="txtLocation" runat="server"></asp:TextBox>
   </td>
   </tr>
   <tr>
   <td>
       Department</td>
   <td>
       <asp:DropDownList ID="ddlDepartment" runat="server">
       </asp:DropDownList>
   </td>
   </tr>
   <tr>
   <td>
       &nbsp;</td>
   <td>
       <asp:Button ID="btnSubmit" runat="server" Text="Submit"
           onclick="btnSubmit_Click" />
   </td>
   </tr>
   </table>
   </div>


Code Here

 
public partial class _Default : System.Web.UI.Page
    {
        EmployeeDeptDataContext objEmp = new EmployeeDeptDataContext();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindDatatoDropDown();
            }
        }
        void BindDatatoDropDown()
        {
            var v = objEmp.GetDepartmentDetails();
            ddlDepartment.DataTextField = "DeptName";
            ddlDepartment.DataValueField = "DeptId";
            ddlDepartment.DataSource = v;
            ddlDepartment.DataBind();
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
         int iCount=objEmp.InsertEmployeeDetails(txtEmpName.Text, txtDesignation.Text, txtLocation.Text, Convert.ToInt32(ddlDepartment.SelectedValue));
         if (iCount > 0)
         {
             lblMessage.Text = "Record Inserted Successfully";
         }
         else
         {
             lblMessage.Text = "Failed";
         }
        }
    }

after finishing writing the code click on F5 to run your Project



Binding Data to Grid

Please copy and paste the following code in another Asp page named as Grid

<div>
        <asp:GridView ID="grdEmployees" runat="server" AutoGenerateColumns="False"
            CellPadding="4" ForeColor="#333333" GridLines="None">
            <RowStyle BackColor="#EFF3FB" />
            <Columns>
                <asp:BoundField DataField="EmpName" HeaderText="Emp Name" />
                <asp:BoundField DataField="Designation" HeaderText="Designation" />
                <asp:BoundField DataField="Location" HeaderText="Location" />
                <asp:BoundField DataField="DeptName" HeaderText="Dept Name" />
            </Columns>
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
    </div>

Code Here
public partial class Grid : System.Web.UI.Page
    {
        EmployeeDeptDataContext objEmp = new EmployeeDeptDataContext();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }
        }
        void BindGrid()
        {
            DataTable dt = new DataTable();
            var v = objEmp.GetEmployeeDetails();
            dt = ObtainDataTableFromIEnumerable(v);
            grdEmployees.DataSource = dt;
           grdEmployees.DataBind();
        }

        private DataTable ObtainDataTableFromIEnumerable(System.Data.Linq.ISingleResult<GetEmployeeDetailsResult> result)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("EmpName",typeof(string));
            dt.Columns.Add("Designation", typeof(string));
            dt.Columns.Add("Location", typeof(string));
            dt.Columns.Add("DeptName", typeof(string));
            foreach (var v in result)
            {
                DataRow dr = dt.NewRow();
                dr["EmpName"] = v.EmpName;
                dr["Designation"] = v.Designation;
                dr["Location"] = v.Location;
                dr["DeptName"] = v.DeptName;
                dt.Rows.Add(dr);
            }
            return dt;
        }
    }

Once you added above code click on F5.




 

Monday, January 24, 2011

How to Reset Identity column in SqlServer

In this post i explained how to reset the identity column in a table.
we have two methods to reset the identity column 
1) Using Truncate Method.
2) Using DBCC (database consistency checker)
But TRUNCATE TABLE cannot be used when a foreign key references the table to be truncated where as DBCC can.

here is the Query using DBCC

DBCC CHECKIDENT('Table Name', RESEED, 0)
In Above query '0' is the reset idetity seed number,you can give any number whatever you want like 20,30 etc..
Here is the Query Using Truncate 
truncate table TableName

Insert value from one Database Table to another Database Table


In this post i have explained how to insert value from one database to another database table.

i have created two tables one in master database another one in sample database
in master table EmpId is identity column,

Here i am getting identity(EmpId) value from Employee table of master database.
Here is the query.

use master
declare @EmpID int
insert into Employee(EmpName,DeptId) values('AAA',1)
set @EmpID=@@Identity
use sample
insert into EmpDetails(EmpId,Location,Email) Values(@EmpID,'Hyderabad','aaa@gmail.com')