大家看看这个程序有什么问题,怎么老出错,提示类型转换错误~
在一个数据库的通用类中,把SqlDataReader从数据库读的东西加载到一个DataTable表中:
.....
public DataTable ConverDataReaderToDataTable(SqlDataReader dr)
{
//add object of DataTable
DataTable myTable = new DataTable();
DataTable schemaTable = dr.GetShemaTable();
//dynamically add to coll
try
{//create columns of the table
foreach(DataRow myRow in schemaTable.Rows)
{
DataColumn myCol = new DataColumn();
myCol.DataType = myRow.GetType();
MyCol.ColumnName = myRow[0].ToString();
myTable.Columns.Add(myCol);
myCol = null;
}
while(dr.Read())
{
DataRow temRow = myTable.NewRow();
for(int i =0;i<schemaTable.Rows.Count;i++)
{
temRow[i] = dr[i].ToString();
//the error occur here,program break down
}
myTable.Rows.Add(temRow);
temRow = null;
}
schemaTable = null;
dr.Close();
return myTable;
}
catch( Exception ex)
{
throw new Exception("Convert Error!",ex);
}
}
问题就出在了那行:temRow[i] = dr[i].ToString();
大家快看看,问题为什么出在这儿,怎么改进??.....
在一个数据库的通用类中,把SqlDataReader从数据库读的东西加载到一个DataTable表中:
.....
public DataTable ConverDataReaderToDataTable(SqlDataReader dr)
{
//add object of DataTable
DataTable myTable = new DataTable();
DataTable schemaTable = dr.GetShemaTable();
//dynamically add to coll
try
{//create columns of the table
foreach(DataRow myRow in schemaTable.Rows)
{
DataColumn myCol = new DataColumn();
myCol.DataType = myRow.GetType();
MyCol.ColumnName = myRow[0].ToString();
myTable.Columns.Add(myCol);
myCol = null;
}
while(dr.Read())
{
DataRow temRow = myTable.NewRow();
for(int i =0;i<schemaTable.Rows.Count;i++)
{
temRow[i] = dr[i].ToString();
//the error occur here,program break down
}
myTable.Rows.Add(temRow);
temRow = null;
}
schemaTable = null;
dr.Close();
return myTable;
}
catch( Exception ex)
{
throw new Exception("Convert Error!",ex);
}
}
问题就出在了那行:temRow[i] = dr[i].ToString();
大家快看看,问题为什么出在这儿,怎么改进??.....
temRow[i] = dr[i];
//the error occur here,program break down
{
//add object of DataTable
DataTable myTable = new DataTable();
DataTable schemaTable = dr.GetShemaTable();
//dynamically add to coll
try
{//create columns of the table
/*foreach(DataRow myRow in schemaTable.Rows)
{
DataColumn myCol = new DataColumn();
myCol.DataType = myRow.GetType();
MyCol.ColumnName = myRow[0].ToString();
myTable.Columns.Add(myCol);
myCol = null;
}*/
//从列复制列 ,不是由行复制列
foreach(DataColumn col in schemaTable.Columns){
DataColumn myCol = new DataColumn();
myCol.DataType = col.DataType
MyCol.ColumnName = col.ColumnName
myTable.Columns.Add(myCol);
}
while(dr.Read())
{
DataRow temRow = myTable.NewRow();
for(int i =0;i<schemaTable.Rows.Count;i++)
{
temRow[i] = dr[i];
//the error occur here,program break down}
myTable.Rows.Add(temRow);}
dr.Close();
return myTable;
}
catch( Exception ex)
{
throw new Exception("Convert Error!",ex);
}
}
==这儿也应该改为以列循环
for(int i =0;i<schemaTable.Columns.Count;i++)
/// 将DataReader转换为DataTable
/// </summary>
/// <param name="reader">要被转换的DataReader</param>
/// <returns>转换后的DataTable</returns>
public static DataTable ConvertDataReaderToDataTable(IDataReader reader)
{
DataTable objDataTable = new DataTable();
int intFieldCount = reader.FieldCount; //将列名添加到DataTable中
for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
{
objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
} //装入内容
objDataTable.BeginLoadData(); object[] objValues = new object[intFieldCount];
while (reader.Read())
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
reader.Close();
objDataTable.EndLoadData(); return objDataTable;
}这个好使,不过是2.0的
DataColumn myCol = new DataColumn();
myCol.DataType = myRow.GetType(); //这里定义了字段类型
。
DataRow temRow = myTable.NewRow();
for(int i =0;i<schemaTable.Rows.Count;i++)
{
temRow[i] = dr[i].ToString(); //全都转换成了字符串,那么
//你的myTable每个字段的类型都是字符类型吗?这样添加到myTable的row中,我想当然会出错了!
//the error occur here,program break down
}
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
改为:
while(reader.Read())
{
for(int i =0;i<intFieldCount;i++)
{
objValues[i] = reader.GetValues(i);
}
objDataTable.LoadDataRow(objValues, true); }这样就行了,谢谢~