Database

'aaa,bbb,ccc'

,ccc

'A,B,C'

,C

AQQQQQ,BFFFFF,CYYYYY

,CYYYYY

ALTER PROCEDURE SP_UPDATELASTCOLUMN 
	-- 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;

	-- VARIABLE DECLARATION SECTION

		DECLARE @CNTRECORDS INT
		DECLARE @NAMEVALS VARCHAR(50)
		DECLARE @I INT

	-- SETTING THE INITIAL VALUES
		SET @I  = 1
     
        SELECT @CNTRECORDS = COUNT(*) FROM TBLEXTRACT_STRING

		SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROWID,* INTO #TEMP FROM TBLEXTRACT_STRING

		
		WHILE ( @I <= @CNTRECORDS )

			BEGIN
					SELECT @NAMEVALS = [NAME] 
					FROM #TEMP
					WHERE ROWID = @I
					
					UPDATE TBLEXTRACT_STRING

					SET PART = (SELECT TOP 1 STRINGVAL FROM DBO.FNSPLIT(@NAMEVALS,',') 
								ORDER BY COUNTER DESC)

					WHERE ID  = (121 + @I)					
					SET @I = @I + 1		
			END

		DROP TABLE #TEMP
END
GO

ALTER FUNCTION [dbo].[fnSplit] 
(@oldstring as varchar(100),@delimeter as varchar(1))
RETURNS @mytab table(counter int,stringval varchar(100)) 
AS
Begin 
		
		Declare @newstring as varchar(100)
		Declare @pos as int
		Declare @i as int
		Declare @c as int	

		set @newstring = '';		
		set @i = 1
		set @c = 0

		set @pos = CHARINDEX(@delimeter, @oldstring) 

		WHILE (@i != 0)

			Begin

				set @c = @c +1
				insert into @mytab(counter,stringval) values(@c,@newstring + Substring(@oldstring,0, @pos))
				
				set @oldstring = Substring(@oldstring,@pos+1,len(@oldstring))

				set @pos = CHARINDEX(@delimeter, @oldstring)

				set @i = @pos;
				if (@i = 0)
                Begin
                    set @i = 0;
						set @c = @c +1
                
					insert into @mytab(counter,stringval) values(@c,@newstring + @oldstring)
				
				
                End
			End

			return

End

sujithkumarsl wrote:

Will the INDEX cause, the INSERT operation to take more time to completed?

sujithkumarsl wrote:

several SELECT as well as INSERT operation are running at same time

jonhbt wrote:

SELECT USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = \"I\" AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));

SELECT MAX(LOGID) as MaxLogID, UserID FROM CHECKINOUT
group by UserID
where CHECKTYPE = 'I'
Order by UserID;

"select USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) inner join qryMaxLogID on userinfo.UserID = qryMaxLogID.UserID inner join INNER JOIN CHECKINOUT as main ON qryMaxLogID.MaxLogID = main.LogID;"

Name Flag Queue

A     Y     1

B     X     2

C     X     3

D     X     4

E     X     5

public class DBConnection
{
    string connectionPath = "Data Source=SUMERUSYS135\\SQLEXPRESS;Initial Catalog=test;Integrated Security=True";
    string command = string.Empty ;

	public DBConnection()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public DataTable FetchRecord()
    {

        SqlDataAdapter adapter = new SqlDataAdapter();
        SqlConnection connection = new SqlConnection(@connectionPath);
        command = "Select * from AlternateTable";
        connection.Open();
        SqlCommand sqlCom = new SqlCommand(command, connection);
        adapter.SelectCommand = sqlCom;
        DataTable table = new DataTable();
        table.Locale = System.Globalization.CultureInfo.InvariantCulture;
        adapter.Fill(table);
        return table;
    }
}

Step 1: Create a custom datatable(say CustomDt) with the column 
                  names that are to be exposed.

          Step 2: Get all the records from the database and place it 
                   inside a datatable(say OriginalDt).

         Step 3:  Loop thru the second record of the datatable(OriginalDt)
                  till the end.

         Step 4: Check if a even row has come or not from the OriginalDt.
 
                 If it is true, then add the first record of the 
                 datasource to the Custom Datatable after adding the even 
                 row from the Original Data Table.

                 Else, add the other records of the original datasource to 
                 the Custom Datatable.

         Step 5: Make the Custom Datatable as the source for the gridview

protected void Page_Load(object sender, EventArgs e)
    {
        DBConnection objdbconnection = new DBConnection();

        //Original datasource
        DataTable OriginalDt = objdbconnection.FetchRecord();

        #region Final Custom Datatable

        DataTable CustomDt = new DataTable();
        CustomDt.Columns.Add("Name");
        CustomDt.Columns.Add("Flag");
        CustomDt.Columns.Add("Queue");

        #endregion


        for (int i = 1; i < OriginalDt.Rows.Count; i++)
        {
            if (i % 2 == 0)
            {
                CustomDt.Rows.Add(OriginalDt.Rows[i][0], OriginalDt.Rows[i][1], OriginalDt.Rows[i][2]);
                CustomDt.Rows.Add("A", "Y", "1");
            }
            else
            {
                CustomDt.Rows.Add(OriginalDt.Rows[i][0], OriginalDt.Rows[i][1], OriginalDt.Rows[i][2]);
            }
            
        }

        grdAlternateRow.DataSource = CustomDt;
        grdAlternateRow.DataBind();

    }