Spliting a DataTable/ DataSet into two dataTable for multiple Grid bind

the following code will split a data table having some rows to the 2 data table with the number of rows provided by user.
if your data table contain 50 rows and you want to split it into two data table then provide the datatable and number of rows as input parameter and it will return the dataset having two datatable.

private DataSet SplitDataTable(DataTable dt,int noOfRowsInTable)
		  {
		      DataSet ds = new DataSet();
		      try
		      {

		          ds.Tables.Add(dt.Clone());
		          ds.Tables[0].TableName = "FirstSet";
		          ds.Tables.Add(dt.Clone());
		          ds.Tables[1].TableName = "SecondSet";
		          if (dt != null)
		          {
		              if (dt.Rows.Count > noOfRowsInTable)
		              {
		                  for (int i = 0; i < noOfRowsInTable; i++)
		                  {
		                      DataRow dr = ds.Tables[0].NewRow();
		                      for (int k = 0; k < dt.Columns.Count; k++)
		                      {							
		                          dr[k]= dt.Rows[i][k];								
		                      }
		                      ds.Tables[0].Rows.Add(dr);
		                  }
		                  for (int j = noOfRowsInTable; j < dt.Rows.Count; j++)
		                  {
		                      DataRow dr1 = ds.Tables[1].NewRow();
		                      for (int l = 0; l < dt.Columns.Count; l++)
		                      {
		                      dr1[l] = dt.Rows[j][l];							
		                      }
		                      ds.Tables[1].Rows.Add(dr1);
		                  }
		              }

		          }
		          return ds;
		      }

		      catch (Exception ex)
		      {
		          lblDate.Text = ex.Message;
		          return ds;
		      }

		  }

Or by using QSL Query:

select StudentId,0 as Marks from (
SELECT StudentId, ROW_NUMBER() OVER(ORDER BY StudentId DESC) AS Row
from T_Term_Std_Result_Theory_Details
) T where Row >= 1 and Row <= 7

C# Code

protected void DrpListSection_SelectedIndexChanged(object sender, EventArgs e)
{
fsTheoryResult.Visible = true;
LabelMsg.Text = “”;
Int32 NoofTotalRow = 0;
Int32 Barrier = 0;

try
{
//String query = “SELECT DISTINCT StudentId, Ceiling(IsNull(Section” + DrpListSection.Text.Trim() + “,0)) AS Section FROM T_Term_Std_Result_Theory_Details” +
// ” WHERE (StudentId NOT IN (SELECT StudentId FROM T_StudentListBellow60PercentAttendance WHERE TermId='” + this.DrpListTermId.Text + “‘ AND CourseId='” + this.DrpListCourseTitle.Text + “‘))” +
// ” AND TermId='” + this.DrpListTermId.Text + “‘ AND CourseId='” + this.DrpListCourseTitle.Text + “‘ AND ResultProcessingStatus=0”;

String query = “select COUNT(*) ValueCount from T_Term_Std_Result_Theory_Details WHERE (StudentId NOT IN (SELECT StudentId FROM T_StudentListBellow60PercentAttendance WHERE TermId='” + this.DrpListTermId.Text + “‘ AND CourseId='” + this.DrpListCourseTitle.Text + “‘))” +
” AND TermId='” + this.DrpListTermId.Text + “‘ AND CourseId='” + this.DrpListCourseTitle.Text + “‘ AND ResultProcessingStatus=0”;
DataSet ds = ClsCommon.GetAdhocResult(query);

NoofTotalRow = Int32.Parse(ds.Tables[0].Rows[0][“ValueCount”].ToString());
Barrier = NoofTotalRow / 3;

Int32 TT = Barrier + 1;

query = “select StudentId, Section from ( SELECT StudentId,Ceiling(IsNull(Section” + DrpListSection.Text.Trim() + “,0)) AS Section, ROW_NUMBER() OVER(ORDER BY StudentId ASC) AS Row from T_Term_Std_Result_Theory_Details WHERE (StudentId NOT IN (SELECT StudentId FROM T_StudentListBellow60PercentAttendance WHERE TermId='” + this.DrpListTermId.Text + “‘ AND CourseId='” + this.DrpListCourseTitle.Text + “‘))” +
” AND TermId='” + this.DrpListTermId.Text + “‘ AND CourseId='” + this.DrpListCourseTitle.Text + “‘ AND ResultProcessingStatus=0 ) T where Row >= 1 and Row <= ” + TT + ” “;
ds = ClsCommon.GetAdhocResult(query);

GrdViewResultEntry.DataSource = ds.Tables[0];
GrdViewResultEntry.DataBind();
GrdViewResultEntry.Visible = true;
BtnSubmitResult.Visible = true;

Int32 LL = Barrier + 2;
Int32 LLL = 2 * (Barrier + 1);
query = “select StudentId,Section from ( SELECT StudentId,Ceiling(IsNull(Section” + DrpListSection.Text.Trim() + “,0)) AS Section, ROW_NUMBER() OVER(ORDER BY StudentId ASC) AS Row from T_Term_Std_Result_Theory_Details WHERE (StudentId NOT IN (SELECT StudentId FROM T_StudentListBellow60PercentAttendance WHERE TermId='” + this.DrpListTermId.Text + “‘ AND CourseId='” + this.DrpListCourseTitle.Text + “‘))” +
” AND TermId='” + this.DrpListTermId.Text + “‘ AND CourseId='” + this.DrpListCourseTitle.Text + “‘ AND ResultProcessingStatus=0 ) T where Row >= ” + LL + ” and Row <= ” + LLL + ” “;
ds = ClsCommon.GetAdhocResult(query);

GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
GridView1.Visible = true;

Int32 pp = (2 * (Barrier + 1) + 1);
Int32 PPP = NoofTotalRow – (2 * (Barrier + 1));
query = “select StudentId,Section from ( SELECT StudentId,Ceiling(IsNull(Section” + DrpListSection.Text.Trim() + “,0)) AS Section, ROW_NUMBER() OVER(ORDER BY StudentId ASC) AS Row from T_Term_Std_Result_Theory_Details WHERE (StudentId NOT IN (SELECT StudentId FROM T_StudentListBellow60PercentAttendance WHERE TermId='” + this.DrpListTermId.Text + “‘ AND CourseId='” + this.DrpListCourseTitle.Text + “‘))” +
” AND TermId='” + this.DrpListTermId.Text + “‘ AND CourseId='” + this.DrpListCourseTitle.Text + “‘ AND ResultProcessingStatus=0 ) T where Row >= ” + pp + ” and Row <= ” + PPP + ” “;
ds = ClsCommon.GetAdhocResult(query);

GridView2.DataSource = ds.Tables[0];
GridView2.DataBind();
GridView2.Visible = true;
if (ds.Tables[0].Rows.Count < 1)
BtnSubmitResult.Visible = false;
}
catch (Exception ex)
{
BtnSubmitResult.Visible = false;
}

CourseTitle();
}


					
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s