Feeds:
Posts
Comments

Using Queries we can get rows and set that rows in our DataSet. But based on some rows we may need to calculate some values and if we want to add that values also in our dataset at runtime then we can do that in following way:

Suppose, we have a SQL query which select 5 fields from a table. Based upon 2 fields among those 5 fields we want to calculate percentage value and add that field at the last column of dataset.

SqlConnection connection = new SqlConnection(“server='” + server + “‘;” + “uid= ‘” + username + “‘;” + “pwd='” + password + “‘;” + “database=Attn; Connect Timeout=10000”);

string selectQuery = “SELECT Table1.[Date], Table1.[On duty], Table1.[Off duty], Table1.[Work Time], Table1.ATT_Time”

DataSet myDS = new DataSet(“MonthlyReport”);
DataTable dtMyTable = new DataTable(“EmpMonthlyReport”);

DataColumn myCol1;
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(selectQuery, connection);
DataRow myNewRow;
CrystalDecisions.CrystalReports.Engine.ReportDocument myReportDocument;
DataTable MyTable = new DataTable(“ETC_MR”);
DataColumn Col1;
DataRow NewRow;
Col1 = new DataColumn(“From”);

MyTable.Columns.Add(Col1);
NewRow = MyTable.NewRow();

MyTable.Rows.Add(NewRow);

MyTable.AcceptChanges();

try
{
connection.Open();
myReader = myCommand.ExecuteReader();
int fc = myReader.FieldCount;
int j=0;
for (int i = 0; i < fc; i++)
{
myCol1 = new DataColumn(myReader.GetName(i));
myCol1.DataType = myReader.GetFieldType(i);
if (myCol1.DataType.Equals(“string”)) { myCol1.MaxLength = 256; } else { }
myCol1.AllowDBNull = true;
dtMyTable.Columns.Add(myCol1);
}

dtMyTable.Columns.Add(“WHPercentage”, typeof(float));

float ATT_TimeResult = 0.0F, WorkTimeResult = 0.0F, pc = 0.0F;
float t1, t2;
while (myReader.Read())
{
myNewRow = dtMyTable.NewRow();
for (int i = 0; i < fc; i++)
{
myNewRow[i] = myReader[i];
j = i;
}

OnDuty = myReader[1].ToString();
OffDuty = myReader[2].ToString();

if ((string)myReader[4] != “”)
ATT_TimeResult = calcTime(myReader[4].ToString());

t1 = calcTime(OnDuty);
t2 = calcTime(OffDuty);
WorkTimeResult = (float)(t2 – t1);

if (WorkTimeResult > 0.0)
pc = ((float)ATT_TimeResult * 100) / (float)WorkTimeResult;
else
pc = 0.0F;

myNewRow[j + 1] = pc;//adding percentage at the last column

dtMyTable.Rows.Add(myNewRow);
dtMyTable.AcceptChanges();
count++;
ATT_TimeResult = 0.0F; WorkTimeResult = 0.0F;
}

myDS.Tables.Add(dtMyTable);
myDS.Tables.Add(MyTable);

myReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
if (count > 0)
{
myReportDocument.Load(@Server.MapPath(“AttMonthlyReport.rpt”));
myReportDocument.SetDataSource(myDS);
myReportDocument.SetParameterValue(“rptMonth”, reportMonth);
CrystalReportViewer1.ReportSource = myReportDocument;
CrystalReportViewer1.DataBind();
}
else
{
myReportDocument.Load(@Server.MapPath(“NoData.rpt”));
CrystalReportViewer1.ReportSource = myReportDocument;
}

}
catch (Exception ce)
{
Response.Write(ce.ToString());
}
try
{
myReader.Close();
connection.Close();
}
catch (Exception cce)
{
Console.WriteLine(cce.ToString());
}

//split the time with ‘:’ and make that into float number; i.e. 08:30 –> 8.5
private float calcTime(string OnTime)
{
string[] splits = OnTime.Split(new char[] { ‘:’ });
int n1, n2;
float f3, f4;
n1 = Int32.Parse(splits[0]);
n2 = Int32.Parse(splits[1]);
f3 = (float)n2 / 60;
f4 = (float)n1 + f3;
return f4;
}

Day before yesterday on 2nd April i have submitted BCS form to DC office. To collect a signature from a officer i had to move several floors and finally one magistrate provide the signature and successfully submit the form.

Before that at 10 a.m. joined a written exam to Dataedge software firm. The exam was nice.¬† After that from Nilkhet book market bought necessary books abt BCS. Now its time for study….

Last evening (29th March) there was heavy rainfall with  hail. When i left office at that time the sky was cloudy. Me with my colleague was returning home by rickshaw. Near the home we faced to rain. We suddenly took shelter inside a shop. After that we noticed hail-storm. It was a lovely scene after many days.

Thinking…

Thinking to seat for the upcoming BCS exam. Need to prepare myself for this.