使用的数据查询语句
string QuerySqlString = "SELECT DBA04.NCYL \"年产油量\" FROM DBA04 ";
NCYL 字段类型是 NUMBER(8,4);
使用不同的驱动绑定结果如图:
代码如下:
Code
string QuerySqlString = "SELECT DBA04.NCYL \"年产油量\" FROM DBA04 where ROWNUM <=200";
private void Form2_Load(object sender, EventArgs e)
{
this.dataGridView1.DataSource = OracleGetData();
this.dataGridView2.DataSource = DonetGetData();
}
/**//// <summary>
/// 使用Donet自带驱动获取oracle数据库的表中的数据
/// </summary>
/// <returns></returns>
public DataTable DonetGetData()
{
DataTable dt = null;
string strcon = "Data Source=orcl;User Id=kfzxca;Password=kfzxca;";
using (System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(strcon))
{
con.Open();
System.Data.OracleClient.OracleCommand cmd = con.CreateCommand();
cmd.CommandText = QuerySqlString;
System.Data.OracleClient.OracleDataAdapter dap = new System.Data.OracleClient.OracleDataAdapter();
dap.SelectCommand = cmd;
dt = new DataTable();
dap.Fill(dt);
con.Close();
}
return dt;
}
/**//// <summary>
/// 使用oracle for .net 驱动获取oracle数据库的表中的数据
/// </summary>
/// <returns></returns>
public DataTable OracleGetData()
{
DataTable dt = null;
string strcon = "Data Source=orcl;User Id=kfzxca;Password=kfzxca;";
using (Oracle.DataAccess.Client.OracleConnection con = new Oracle.DataAccess.Client.OracleConnection(strcon))
{
con.Open();
Oracle.DataAccess.Client.OracleCommand cmd = con.CreateCommand();
cmd.CommandText = QuerySqlString;
Oracle.DataAccess.Client.OracleDataAdapter dap = new Oracle.DataAccess.Client.OracleDataAdapter();
dap.SelectCommand = cmd;
dt = new DataTable();
dap.Fill(dt);
//字段类型修改后没有多余的小数点位
//dt = ConvertDataTable.ConvertOraclTableToDoNetTable(dt);
con.Close();
}
return dt;
}
string QuerySqlString = "SELECT DBA04.NCYL \"年产油量\" FROM DBA04 where ROWNUM <=200";
private void Form2_Load(object sender, EventArgs e)
{
this.dataGridView1.DataSource = OracleGetData();
this.dataGridView2.DataSource = DonetGetData();
}
/**//// <summary>
/// 使用Donet自带驱动获取oracle数据库的表中的数据
/// </summary>
/// <returns></returns>
public DataTable DonetGetData()
{
DataTable dt = null;
string strcon = "Data Source=orcl;User Id=kfzxca;Password=kfzxca;";
using (System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(strcon))
{
con.Open();
System.Data.OracleClient.OracleCommand cmd = con.CreateCommand();
cmd.CommandText = QuerySqlString;
System.Data.OracleClient.OracleDataAdapter dap = new System.Data.OracleClient.OracleDataAdapter();
dap.SelectCommand = cmd;
dt = new DataTable();
dap.Fill(dt);
con.Close();
}
return dt;
}
/**//// <summary>
/// 使用oracle for .net 驱动获取oracle数据库的表中的数据
/// </summary>
/// <returns></returns>
public DataTable OracleGetData()
{
DataTable dt = null;
string strcon = "Data Source=orcl;User Id=kfzxca;Password=kfzxca;";
using (Oracle.DataAccess.Client.OracleConnection con = new Oracle.DataAccess.Client.OracleConnection(strcon))
{
con.Open();
Oracle.DataAccess.Client.OracleCommand cmd = con.CreateCommand();
cmd.CommandText = QuerySqlString;
Oracle.DataAccess.Client.OracleDataAdapter dap = new Oracle.DataAccess.Client.OracleDataAdapter();
dap.SelectCommand = cmd;
dt = new DataTable();
dap.Fill(dt);
//字段类型修改后没有多余的小数点位
//dt = ConvertDataTable.ConvertOraclTableToDoNetTable(dt);
con.Close();
}
return dt;
}
Code
DataTable字段类型转换#region DataTable字段类型转换
/**//// <summary>
/// ConvertDataTable
/// oracle for.net的驱动 将字段内型是NUMBER(8,4) 解析成 System.Double;
/// System.Data.OracleClient 将字段内型是NUMBER(8,4) 解析成 System.Decimal;
/// ConvertDataTable【转换类】将使用oracle for.net的驱动返回的DataTale
/// 列类型是System.Double修改成System.Decimal;
/// </summary>
public class ConvertDataTable
{
public static DataTable ConvertOraclTableToDoNetTable(DataTable table)
{
DataTable dt = new DataTable();
foreach (DataColumn dc in table.Columns)
{
DataColumn column = new DataColumn();
column.DataType = GetDataType(dc.DataType);
column.ColumnName = dc.ColumnName;
column.Caption = dc.Caption;
dt.Columns.Add(column);
}
dt.Merge(table, false, MissingSchemaAction.Ignore);
return dt;
}
public static Type GetDataType(Type dataType)
{
switch (dataType.ToString())
{
case "System.Double":
return System.Type.GetType("System.Decimal");
default:
return dataType;
}
}
}
#endregion
DataTable字段类型转换#region DataTable字段类型转换
/**//// <summary>
/// ConvertDataTable
/// oracle for.net的驱动 将字段内型是NUMBER(8,4) 解析成 System.Double;
/// System.Data.OracleClient 将字段内型是NUMBER(8,4) 解析成 System.Decimal;
/// ConvertDataTable【转换类】将使用oracle for.net的驱动返回的DataTale
/// 列类型是System.Double修改成System.Decimal;
/// </summary>
public class ConvertDataTable
{
public static DataTable ConvertOraclTableToDoNetTable(DataTable table)
{
DataTable dt = new DataTable();
foreach (DataColumn dc in table.Columns)
{
DataColumn column = new DataColumn();
column.DataType = GetDataType(dc.DataType);
column.ColumnName = dc.ColumnName;
column.Caption = dc.Caption;
dt.Columns.Add(column);
}
dt.Merge(table, false, MissingSchemaAction.Ignore);
return dt;
}
public static Type GetDataType(Type dataType)
{
switch (dataType.ToString())
{
case "System.Double":
return System.Type.GetType("System.Decimal");
default:
return dataType;
}
}
}
#endregion
通过类型转换后数据结果再绑定 小数位异常没了。