如何在数据表中插入空小数
本文关键字：插入 小数 数据表 | 更新日期: 2023-09-27 18:18:28
我正在创建一个数据表:
public DataTable dt;
public ReadFileIntoDataTable(string inputfile)
{
dt = new DataTable();
dt.Columns.Add("Specimen_ID",typeof(string));
dt.Columns.Add("Client_Key",typeof(int));
dt.Columns.Add("Outcome",typeof(string));
dt.Columns.Add("Medications",typeof(string));
dt.Columns.Add("Date_Collected",typeof(DateTime));
dt.Columns.Add("Time_Collected",typeof(DateTime));
dt.Columns.Add("Date_Entered",typeof(DateTime));
dt.Columns.Add("Time_Entered",typeof(DateTime));
dt.Columns.Add("Date_Completed",typeof(DateTime));
dt.Columns.Add("Time_Completed",typeof(DateTime));
dt.Columns.Add("Test_Date",typeof(DateTime));
dt.Columns.Add("Test_Time",typeof(DateTime));
dt.Columns.Add("Practice_Name",typeof(string));
dt.Columns.Add("Practice_Code",typeof(string));
dt.Columns.Add("Client_ID",typeof(string));
dt.Columns.Add("Requesting_Physician",typeof(string));
dt.Columns.Add("Other_Medications",typeof(string));
dt.Columns.Add("Order_Comments",typeof(string));
dt.Columns.Add("Reference_Number",typeof(string));
dt.Columns.Add("Order_Count",typeof(int));
dt.Columns.Add("_6_MAM_HEROIN_METABOLITE",typeof(decimal));
dt.Columns.Add("_6_MAM_N_HEROIN_METABOLITE",typeof(decimal));
dt.Columns.Add("_6_MAM_S_HEROIN_METABOLITE",typeof(decimal));
dt.Columns.Add("_6_MAM_SN_HEROIN_METABOLITE",typeof(decimal));
dt.Columns.Add("_6_MAM_SP_HEROIN_METABOLITE",typeof(decimal));
...
..
..
string Specimen_ID;
string Client_Key;
string Outcome;
string Medications;
string Date_Collected;
string Time_Collected;
string Date_Entered;
string Time_Entered;
string Date_Completed;
string Time_Completed;
string Test_Date;
string Test_Time;
string Practice_Name;
string Practice_Code;
string Client_ID;
string Requesting_Physician;
string Other_Medications;
string Order_Comments;
string Reference_Number;
string Order_Count;
string _6_MAM_HEROIN_METABOLITE;
string _6_MAM_N_HEROIN_METABOLITE;
string _6_MAM_S_HEROIN_METABOLITE;
string _6_MAM_SN_HEROIN_METABOLITE;
string _6_MAM_SP_HEROIN_METABOLITE;
string _6ACE_C_6_MAM__Heroin_metabolite;
.....
........
.....
using (GenericParser parser = new GenericParser())
{
parser.SetDataSource(inputfile);
char[] delimiters = new char[] { ',' };
parser.ColumnDelimiter = delimiters[0];
parser.FirstRowHasHeader = true;
//parser.SkipDataRows = 10;
parser.MaxBufferSize = 4096;
parser.MaxRows = 500;
parser.TextQualifier = ''"';
while (parser.Read())
{
Specimen_ID = parser["Specimen ID"];
Client_Key = parser["Client Key"];
Outcome = parser["Outcome"];
Medications = parser["Medications"];
Date_Collected = parser["Date Collected"];
Time_Collected = parser["Time Collected"];
Date_Entered = parser["Date Entered"];
Time_Entered = parser["Time Entered"];
Date_Completed = parser["Date Completed"];
Time_Completed = parser["Time Completed"];
Test_Date = parser["Test Date"];
Test_Time = parser["Test Time"];
Practice_Name = parser["Practice Name"];
Practice_Code = parser["Practice Code"];
Client_ID = parser["Client ID"];
Requesting_Physician = parser["Requesting Physician"];
Other_Medications = parser["Other Medications"];
Order_Comments = parser["Order Comments"];
Reference_Number = parser["Reference Number"];
Order_Count = parser["Order Count"];
_6_MAM_HEROIN_METABOLITE = parser["6-MAM-HEROIN METABOLITE"];
_6_MAM_N_HEROIN_METABOLITE = parser["6-MAM_N-HEROIN METABOLITE"];
_6_MAM_S_HEROIN_METABOLITE = parser["6-MAM_S-HEROIN METABOLITE"];
_6_MAM_SN_HEROIN_METABOLITE = parser["6-MAM_SN-HEROIN METABOLITE"];
_6_MAM_SP_HEROIN_METABOLITE = parser["6-MAM_SP-HEROIN METABOLITE"];
_6ACE_C_6_MAM__Heroin_metabolite = parser["6ACE_C-6-MAM (Heroin metabolite)"];
.....
......
.......
DataRow newRow = dt.NewRow();
newRow["Specimen_ID"] = Specimen_ID;
newRow["Client_Key"] = Client_Key;
newRow["Outcome"] = Outcome;
newRow["Medications"] = Medications;
newRow["Date_Collected"] = Date_Collected;
newRow["Time_Collected"] = Time_Collected;
newRow["Date_Entered"] = Date_Entered;
newRow["Time_Entered"] = Time_Entered;
newRow["Date_Completed"] = Date_Completed;
newRow["Time_Completed"] = Time_Completed;
newRow["Test_Date"] = Test_Date;
newRow["Test_Time"] = Test_Time;
newRow["Practice_Name"] = Practice_Name;
newRow["Practice_Code"] = Practice_Code;
newRow["Client_ID"] = Client_ID;
newRow["Requesting_Physician"] = Requesting_Physician;
newRow["Other_Medications"] = Other_Medications;
newRow["Order_Comments"] = Order_Comments;
newRow["Reference_Number"] = Reference_Number;
newRow["Order_Count"] = Order_Count;
newRow["_6_MAM_HEROIN_METABOLITE"] = ????
newRow["_6_MAM_N_HEROIN_METABOLITE"] = _6_MAM_N_HEROIN_METABOLITE;
newRow["_6_MAM_S_HEROIN_METABOLITE"] = _6_MAM_S_HEROIN_METABOLITE;
newRow["_6_MAM_SN_HEROIN_METABOLITE"] = _6_MAM_SN_HEROIN_METABOLITE;
newRow["_6_MAM_SP_HEROIN_METABOLITE"] = _6_MAM_SP_HEROIN_METABOLITE;
.........
........
......
正如你所看到的，我正在做以下的事情:
- 创建数据表
- 使用genericparser 解析文件
- 将数据从文件 插入数据表
我的问题是当文件没有DECIMAL类型字段的值时。我得到以下错误:
System.ArgumentException: Input string was not in a correct format.Couldn't store <> in _6_MAM_HEROIN_METABOLITE Column. Expected type is Decimal. ---> System.FormatException: Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt)
at System.String.System.IConvertible.ToDecimal(IFormatProvider provider)
at System.Data.Common.DecimalStorage.Set(Int32 record, Object value)
at System.Data.DataColumn.set_Item(Int32 record, Object value)
--- End of inner exception stack trace ---
at System.Data.DataColumn.set_Item(Int32 record, Object value)
at System.Data.DataRow.set_Item(DataColumn column, Object value)
at CreateTableScript.ReadFileIntoDataTable..ctor(String inputfile) in C:'Users'agordon'Documents'Visual Studio 2008'Projects'SubversionRepository'Internal Application'SalesDWH'CreateTableScript'CreateTableScript'ReadFileIntoDataTable.cs:line 930
at CreateTableScript.Form1.button1_Click(Object sender, EventArgs e) in C:'Users'agordon'Documents'Visual Studio 2008'Projects'SubversionRepository'Internal Application'SalesDWH'CreateTableScript'CreateTableScript'Form1.cs:line 33
则根本不设置该值，将使用数据表中分配的默认值。您可以将默认值更改为0，非零或DbNull。值
不知道你是否尝试过这个，但是如果你在插入之前检查值，如果没有值，插入"0.0"怎么办?
我几乎想说你可以传递一个DBNull，但我不是100%确定，目前无法验证。
如果这是一个空小数，请将列设置为可空类型
dt.Columns.Add("_6_MAM_HEROIN_METABOLITE",typeof(decimal?));
不确定null值是否会插入而没有错误，或者您是否需要先检查null然后进行强制转换…
(decimal?)null
读取文件时，将结果放入字符串中。在将字符串赋值给数据列之前，应该将其转换为十进制。
可以使用decimal.tryparse:
http://msdn.microsoft.com/en-us/library/9zbda557.aspx 一些psuedo-code:decimal convertedDecimal;
bool success = decimal.tryparse(_6_MAM_HEROIN_METABOLITE, out convertedDecimal);
if (!success)
{
newRow["_6_MAM_HEROIN_METABOLITE"] = DBNull.Value;
}
else
{
newRow["_6_MAM_HEROIN_METABOLITE"] = convertedDecimal;
}
没有看到实际将从文本文件读取的值插入到DataTable中的代码，但在插入函数中应该足够了，使用如下代码:
-
第一个选项
decimal decimalDefaultValue = 0.0; //let's say it's a 0 in your case decimal decimalValue = decimalDefaultValue; decimal.TryParse(stringReadFromFile, out decimalValue ); //insert decimalValue inside DataRow -
第二种选择
在数据库列
中设置默认值在插入函数期间，如果值为null或空，则跳过它。