Read the excel data to a datatable.
Now, you can create a sp like-
CREATE PROC YourProc
(
@Status VARCHAR(50),
@Gender VARCHAR(50),
@Type VARCHAR(50)
)
AS
BEGIN
DECLARE @StatusID INT
IF(EXISTS(SELECT ID FROM StatusMaster WHERE Desc=@Status))
BEGIN
SELECT @StatusID=ID FROM StatusMaster WHERE Desc=@Status
END
ELSE
BEGIN
INSERT INTO StatusMaster VALUES(@Status)
SELECT @StatusID = IDENT_CURRENT(‘StatusMaster’)
END
DECLARE @GenderID INT
IF(EXISTS(SELECT ID FROM GenderMaster WHERE Desc=@Gender))
BEGIN
SELECT @GenderID =ID FROM GenderMaster WHERE Desc=@Gender
END
ELSE
BEGIN
INSERT INTO GenderMaster VALUES(@Gender)
SELECT @GenderID = IDENT_CURRENT(‘GenderMaster’)
END
DECLARE @TypeID INT
IF(EXISTS(SELECT ID FROM TypeMaster WHERE Desc=@Type))
BEGIN
SELECT @TypeID =ID FROM TypeMaster WHERE Desc=@Type
END
ELSE
BEGIN
INSERT INTO TypeMaster VALUES(@Type)
SELECT @TypeID = IDENT_CURRENT(‘TypeMaster’)
END
INSERT INTO MasterTable (StatusID, GenderID, TypeID, createdBy)
VALUES(@StatusID,@GenderID,@TypeID,'')
END
Call this for each row of your datatable.
Hope, it helps :)
Update:
As per OP's request here is the code for ado.net.
foreach(DataRow dr in yourDatatable.Rows)
{
using (SqlConnection conn = new SqlConnection("Server=YourSQLServerInstanceName;DataBase=YourDBName;UserId=YourUserId;Password=YourPassword"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("YourProc", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Status", dr["Status"].ToString()));
cmd.Parameters.Add(new SqlParameter("@Gender", dr["Gender"].ToString()));
cmd.Parameters.Add(new SqlParameter("@Type", dr["Type"].ToString()));
cmd.ExecuteNonQuery();
}
}
This should do your job. If not, please let me know :)