DataTable table = new DataTable(); SqlDataReader myReader; string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon"); SqlConnection sql = new SqlConnection(sqlDataSource); sql.Open(); SqlCommand scCommand = new SqlCommand("SP_Grouplist", sql); scCommand.CommandType = System.Data.CommandType.StoredProcedure; scCommand.Parameters.Clear(); foreach (var t in StringToAarry) { scCommand.Parameters.Add("@Group_Name", SqlDbType.VarChar).Value = model.Group_Name; scCommand.Parameters.Add("@user_id", SqlDbType.VarChar).Value = t; scCommand.Parameters.Add("@ImagePath", SqlDbType.VarChar).Value = model.ImagePath; scCommand.Parameters.Add("@Created_by", SqlDbType.VarChar).Value = Created_by; scCommand.Parameters.Add("@Flag", SqlDbType.Int).Value = flag; flag = flag + 1; //scCommand.Parameters.Clear(); } scCommand.ExecuteNonQuery();
myReader = scCommand.ExecuteReader();
table.Load(myReader); myReader.Close(); sql.Close();
-------
this is my Stored procedure
ALTER PROCEDURE [dbo].[SP_Grouplist]
@Group_Name varchar(50), @user_id varchar(500), @ImagePath varchar(500), @Created_by varchar(50), @Flag int AS Declare @GroupId Int
BEGIN SET NOCOUNT ON;
/* Create the New Group in Group Name Table */ if Not exists (Select * from GroupNames with (nolock ) where Group_Name=@Group_Name and Created_by =@Created_by ) Begin Select @GroupId = IsNull(Max(Group_Id),0)+1 from GroupNames with (nolock) Insert into GroupNames (Group_id, Group_Name,Created_by) Select @GroupId , @Group_Name , @Created_by End
/* Get Group Id from Group Name table for the existing record */ if exists (Select * from GroupNames with (nolock ) where Group_Name=@Group_Name and Created_by =@Created_by ) Begin Select @GroupId=Group_id from GroupNames with (nolock ) where Group_Name=@Group_Name and Created_by =@Created_by End
/* Going to add the member/user into the Group list table by refering Group Name Table */
if @Flag=1
Begin --Delete from GroupNames where Group_Id=@GroupId Delete from GroupList Where Group_Id=@GroupId and created_by = @Created_by End
if Exists (Select * from GroupList where Group_Id=@GroupId and created_by = @Created_by) Begin --Select @GroupId= Group_Id from GroupList where Group_Name=@Group_Name and created_by = @Created_by --Delete from GroupList where Group_Name=@Group_Name and created_by =@Created_by
Insert into GroupList Select @GroupId Group_Id, @ImagePath ImagePath,item user_id, @Created_by Created_by, getdate() created_date from dbo.[SplitString] (@user_id,',') Where item Not in (Select user_id from GroupList where Group_Id= @GroupId and created_by = @Created_by) Update GroupList set ImagePath=@ImagePath where Group_Id=@GroupId and created_by = @Created_by
End Else Begin --Select @GroupId= IsNull(Max(Group_Id),0)+1 from GroupList --where Group_Name=@Group_Name and created_by = @Created_by Insert into GroupList Select @GroupId Group_Id,@ImagePath ImagePath, item user_id , @Created_by Created_by,getdate() created_date from dbo.[SplitString] (@user_id,',')
End
END