Database

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();

    }