Monday, June 9, 2008

A new way to generate data table and bind to gird view

public DataTable getAlloyElement(string AlloyNo)
{
DataTable dt = new DataTable();
DataTable dtResult = new DataTable();
DataColumn dCol;
dCol = new DataColumn("alloy_element");
dt.Columns.Add(dCol);
dCol = new DataColumn("alloy_target");
dt.Columns.Add(dCol);
dCol = new DataColumn("alloy_actual");
dt.Columns.Add(dCol);
dCol = new DataColumn("colNumber");
dt.Columns.Add(dCol);
dCol = new DataColumn("colName");
dt.Columns.Add(dCol);

dt.Rows.Clear();

if (gbl.cnOAS.State != ConnectionState.Open)
{
gbl.cnOAS.Open();
}

SqlDataAdapter adaAlloy = new SqlDataAdapter("select * from alloys where als_code = '"+AlloyNo+"'", gbl.cnOAS);
if (adaAlloy != null)
{
adaAlloy.Fill(dtResult);
for (int rowCount = 0; rowCount < dtResult.Rows.Count; rowCount++)
{
for (int colCount = 23; colCount < 83; )
{
int colValue = colCount;
if (dtResult.Rows[rowCount][colValue] != null && dtResult.Rows[rowCount][colValue].ToString().Trim() != "" && dtResult.Rows[rowCount][colValue].ToString().Trim() != "0" && dtResult.Columns[colValue].ColumnName.ToString().Trim().ToLower().Contains("als_element"))
{
DataRow dtRow = dt.NewRow();
SqlDataAdapter adaElementDesc = new SqlDataAdapter("select ele_code, ele_description from elements where ele_code = '" + dtResult.Rows[rowCount][colValue].ToString().Trim()+ "'", gbl.cnOAS);
if (adaElementDesc != null)
{
DataTable ele_des = new DataTable();
adaElementDesc.Fill(ele_des);
dtRow["alloy_element"] = ele_des.Rows[0]["ele_code"].ToString().Trim() + " " + ele_des.Rows[0]["ele_description"].ToString().Trim();
}
dtRow["alloy_target"] = dtResult.Rows[rowCount][colValue + 1].ToString(); ;
dtRow["alloy_actual"] = dtResult.Rows[rowCount][colValue + 2].ToString(); ;
dtRow["colNumber"] = colValue;
dtRow["colName"] = dtResult.Columns[colValue].ColumnName.ToString().Trim().ToLower();
dt.Rows.Add(dtRow);
}
colCount = colCount + 4;
}
}
}

DataView dView = new DataView(dt);
dView.Sort = "alloy_element";

//Convert dataview to datatable
DataTable obNewDt = dView.Table.Clone();
int idx = 0;
string[] strColNames = new string[obNewDt.Columns.Count];
foreach (DataColumn col in obNewDt.Columns)
{
strColNames[idx++] = col.ColumnName;
}

System.Collections.IEnumerator viewEnumerator = dView.GetEnumerator();
while (viewEnumerator.MoveNext())
{
DataRowView drv = (DataRowView)viewEnumerator.Current;
DataRow dr = obNewDt.NewRow();
try
{
foreach (string strName in strColNames)
{
dr[strName] = drv[strName];
}
}
catch (Exception ex)
{
// Trace.WriteLine(ex.Message);

}
obNewDt.Rows.Add(dr);
}

dtResult.Dispose();
return obNewDt;
}

No comments: