I want auto generation of numbers in c#. Eg. E001,E002......C001,C002......

There are many examples of this here in CodeProject so I do encourage you to do some further searching here to get other ideas, however ...

Don't store the formatted EmpCode on your database.

Why? Say Employee C007 moves from being a 'E' type of employee to an 'C' type of employee? You will need to change their ID from "E100" to ... whatever the next number is for 'C'-types. You can't assume that you can use 'C100' for the same employee.

Now put yourself into the real world. A Person, Employee John, is currently employee "E099" but moves jobs into the "C..." world. No problem, except you have had to change his Employee number to "C100" because "C099" is already taken! John is now confused - is his Employee Number 099 or 100? What about old payslips? Payroll has to change too, and any other data that refers to John the person.

Another reason.

Your company is very successful with lots of employees. Finally it takes on Janet as the 1000th employee in the "C" group. You allocate an EmpId to Janet of "C1000". Hm... it doesn't fit, or worse gets stored as "C100" - Now who does "C100" refer to ... Janet or John?

Ok you think, I'll just make EmpIds a letter followed by four digits. Great, but you have to go update those 999 records you already have on the database and let everybody know - so Janet and John get confused all over again!

I could go on but that's enough.

In summary - If there is formatting to be done, do it in the UI

Here is an example of how you could achieve your automatically generated numbers

create table demo
(
	AutoId int identity(1,1),
	EmployeeType char(1),
	OtherData varchar(max)
)

insert into demo values
('E','some other data1'),
('E','some other data2'),
('C','some other data3'),
('C','some other data4')

which gives

1	E	some other data1
2	E	some other data2
3	C	some other data3
4	C	some other data4

but the query

SELECT EmployeeType + RIGHT(REPLICATE('0', 3) + CAST(AutoId as varchar), 3), OtherData
from demo

would produce

E001	some other data1
E002	some other data2
C003	some other data3
C004	some other data4

If you absolutely must have C001 - 999 and E001 - 999 then I suggest you pre-allocate the numbers and just update each candidate number once it has been used. E.g. to generate the numbers

CREATE TABLE EmployeeNumbers
(
	id int identity(1,1),
	EmployeeType char(1),
	EmployeeNo int,
	Used int
)
;WITH q AS
(
    SELECT  1 AS num
    UNION ALL
    SELECT  num + 1 FROM q WHERE num < 999
)
insert into EmployeeNumbers
SELECT 'E', num, 0 FROM q 
UNION
SELECT 'C', num, 0 FROM q option( maxrecursion 999)

Take care if using this approach and allow for more than one user trying to access the table at once.