SQLite / ADO.. NET数据适配器没有向SQLite文件写入正确的行数
本文关键字：SQLite 文件 ADO NET 数据 适配器 | 更新日期: 2023-09-27 18:16:33
继续我将程序输出从CSV转换为SQLite的故事，我遇到了另一个问题。我正在取一个文本文件，用行和逗号将其分解，并将其结果放入列表中。然后，我将按照本教程学习如何使用ADO。.NET数据集和.NET数据提供程序将数据插入到表中，因为我之前的方法没有正确创建表。
文件的行读取器返回它读取了134行，这是文件中正确的行数(顺便说一下，可以在这里找到…您将受益于使用它进行测试)。此外，为将数据加载到数据适配器而创建的DataTable对象也总共读取134行。但是，当打开文件并查看其中的内容时(我一直在使用wxSQLite+)，并不是所有的行都在那里。写入的行数各不相同，但不可避免地不是134行。此外，跨行应用的数据类型也不统一。奇数行的数据字段与创建表时输入的模式保持一致，但偶数行的所有整型行都转换为double。我不太确定是什么原因造成的，并且偏离了教程的代码一点，但原则应该仍然是相同的。它可能是一些简单而愚蠢的东西(我很擅长犯这些错误)，但我的博士生导师看不出有什么明显的错误。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
namespace C_Sharp_SQLite_Testbed{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window{
private static string fileName;
private static string dataName;
public static string[] lines;
public MainWindow(){
InitializeComponent();
}
private void btnLoad_Click(object sender, RoutedEventArgs e){
//open input file
string dateTime = DateTime.Today.ToString("dd_mm_yyyy");
Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
dlg.FileName = "Document";
dlg.DefaultExt = ".txt";
dlg.Filter = "Text Documents (.txt)|*.txt";
Nullable<bool> result = dlg.ShowDialog();
string inputFilePath = dlg.FileName;
if (result == true){
//check for input in the table name textbox
if(txtTableName.GetLineLength(0) == 0){
string errorBoxText = "Please enter a name for the table.";
string caption = "Error";
MessageBox.Show(errorBoxText, caption, MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
//create save dialog box for .db file
//create output file
Microsoft.Win32.SaveFileDialog sdg = new Microsoft.Win32.SaveFileDialog();
sdg.FileName = "databaseName";
sdg.DefaultExt = ".db";
sdg.Filter = "SQLite3 Database File (.db)|*.db";
Nullable<bool> saveResult = sdg.ShowDialog();
if (saveResult == true){
string saveFileName = sdg.FileName;
string cs = string.Format("URI=file:{0}", saveFileName);
string tableName = txtTableName.Text;
int i = 0;
#region SQLite logic
#region create table
using (SQLiteConnection con = new SQLiteConnection(cs)){
con.Open();
using (SQLiteCommand cmd = new SQLiteCommand(con)){
cmd.CommandText = string.Format(@"CREATE TABLE {0} (Timestamp INTEGER PRIMARY KEY, LeftHipState INTEGER NOT NULL, LeftHipX REAL, LeftHipY REAL, LeftHipZ REAL, LeftKneeState INTEGER NOT NULL, LeftKneeX REAL, LeftKneeY REAL, LeftKneeZ REAL, LeftAnkleState INTEGER NOT NULL, LeftAnkleX REAL, LeftAnkleY REAL, LeftAnkleZ REAL, LeftFootState INTEGER NOT NULL, LeftFootX REAL, LeftFootY REAL, LeftFootZ REAL, RightHipState INTEGER NOT NULL, RightHipX REAL, RightHipY REAL, RightHipZ REAL, RightKneeState INTEGER NOT NULL, RightKneeX REAL, RightKneeY REAL, RightKneeZ REAL, RightAnkleState INTEGER NOT NULL, RightAnkleX REAL, RightAnkleY REAL, RightAnkleZ REAL, RightFootState INTEGER NOT NULL, RightFootX REAL, RightFootY REAL, RightFootZ REAL, LeftKneeFlexion REAL, LeftKneeValgus REAL, RightKneeFlexion REAL, RightKneeValgus REAL)",
txtTableName.Text);
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
}
con.Close();
}
#endregion
using (SQLiteConnection con = new SQLiteConnection(cs)){
DataTable table = new DataTable(tableName);
#region create columns
table.Columns.Add("Timestamp", System.Type.GetType("System.String"));
//------------------------------------------------------------------
table.Columns.Add("LeftHipState", System.Type.GetType("System.Int32"));
table.Columns.Add("LeftHipX", System.Type.GetType("System.Double"));
table.Columns.Add("LeftHipY", System.Type.GetType("System.Double"));
table.Columns.Add("LeftHipZ", System.Type.GetType("System.Double"));
table.Columns.Add("LeftKneeState", System.Type.GetType("System.Int32"));
table.Columns.Add("LeftKneeX", System.Type.GetType("System.Double"));
table.Columns.Add("LeftKneeY", System.Type.GetType("System.Double"));
table.Columns.Add("LeftKneeZ", System.Type.GetType("System.Double"));
table.Columns.Add("LeftAnkleState", System.Type.GetType("System.Int32"));
table.Columns.Add("LeftAnkleX", System.Type.GetType("System.Double"));
table.Columns.Add("LeftAnkleY", System.Type.GetType("System.Double"));
table.Columns.Add("LeftAnkleZ", System.Type.GetType("System.Double"));
table.Columns.Add("LeftFootState", System.Type.GetType("System.Int32"));
table.Columns.Add("LeftFootX", System.Type.GetType("System.Double"));
table.Columns.Add("LeftFootY", System.Type.GetType("System.Double"));
table.Columns.Add("LeftFootZ", System.Type.GetType("System.Double"));
//-------------------------------------------------------------------
table.Columns.Add("RightHipState", System.Type.GetType("System.Int32"));
table.Columns.Add("RightHipX", System.Type.GetType("System.Double"));
table.Columns.Add("RightHipY", System.Type.GetType("System.Double"));
table.Columns.Add("RightHipZ", System.Type.GetType("System.Double"));
table.Columns.Add("RightKneeState", System.Type.GetType("System.Int32"));
table.Columns.Add("RightKneeX", System.Type.GetType("System.Double"));
table.Columns.Add("RightKneeY", System.Type.GetType("System.Double"));
table.Columns.Add("RightKneeZ", System.Type.GetType("System.Double"));
table.Columns.Add("RightAnkleState", System.Type.GetType("System.Int32"));
table.Columns.Add("RightAnkleX", System.Type.GetType("System.Double"));
table.Columns.Add("RightAnkleY", System.Type.GetType("System.Double"));
table.Columns.Add("RightAnkleZ", System.Type.GetType("System.Double"));
table.Columns.Add("RightFootState", System.Type.GetType("System.Int32"));
table.Columns.Add("RightFootX", System.Type.GetType("System.Double"));
table.Columns.Add("RightFootY", System.Type.GetType("System.Double"));
table.Columns.Add("RightFootZ", System.Type.GetType("System.Double"));
//-------------------------------------------------------------------
table.Columns.Add("LeftKneeFlexion", System.Type.GetType("System.Double"));
table.Columns.Add("LeftKneeValgus", System.Type.GetType("System.Double"));
table.Columns.Add("RightKneeFlexion", System.Type.GetType("System.Double"));
table.Columns.Add("RightKneeValgus", System.Type.GetType("System.Double"));
#endregion
var output = new List<MyData>();
foreach (var line in File.ReadAllLines(inputFilePath)){
output.Add(new MyData(line.Split(',')));
}
foreach (MyData outputLine in output){
DataRow row = table.NewRow();
row["Timestamp"] = outputLine.Time;
row["LeftHipState"] = outputLine.HlState;
row["LeftHipX"] = outputLine.HLX;
row["LeftHipY"] = outputLine.HLY;
row["LeftHipZ"] = outputLine.HLZ;
row["LeftKneeState"] = outputLine.KlState;
row["LeftKneeX"] = outputLine.KLX;
row["LeftKneeY"] = outputLine.KLY;
row["LeftKneeZ"] = outputLine.KLZ;
row["LeftAnkleState"] = outputLine.AlState;
row["LeftAnkleX"] = outputLine.ALX;
row["LeftAnkleY"] = outputLine.ALY;
row["LeftAnkleZ"] = outputLine.ALZ;
row["LeftFootState"] = outputLine.FlState;
row["LeftFootX"] = outputLine.FLX;
row["LeftFootY"] = outputLine.FLY;
row["LeftFootZ"] = outputLine.FLZ;
row["RightHipState"] = outputLine.HrState;
row["RightHipX"] = outputLine.HRX;
row["RightHipY"] = outputLine.HRY;
row["RightHipZ"] = outputLine.HRZ;
row["RightKneeState"] = outputLine.KrState;
row["RightKneeX"] = outputLine.KRX;
row["RightKneeY"] = outputLine.KRY;
row["RightKneeZ"] = outputLine.KRZ;
row["RightAnkleState"] = outputLine.ArState;
row["RightAnkleX"] = outputLine.ARX;
row["RightAnkleY"] = outputLine.ARY;
row["RightAnkleZ"] = outputLine.ARZ;
row["RightFootState"] = outputLine.FrState;
row["RightFootX"] = outputLine.FRX;
row["RightFootY"] = outputLine.FRY;
row["RightFootZ"] = outputLine.FRZ;
row["LeftKneeFlexion"] = outputLine.LKFX;
row["LeftKneeValgus"] = outputLine.LKVG;
row["RightKneeFlexion"] = outputLine.RKFX;
row["RightKneeValgus"] = outputLine.RKVG;
table.Rows.Add(row);
i++;
Console.WriteLine("{0}| {1} {2:N4} {3:N4} {4:N4}| {5} {6:N4} {7:N4} {8:N4}| {9} {10:N4} {11:N4} {12:N4}| {13} {14:N4} {15:N4} {16:N4}| {17} {18:N4} {19:N4} {20:N4}| {21} {22:N4} {23:N4} {24:N4}| {25} {26:N4} {27:N4} {28:N4}| {29} {30:N4} {31:N4} {32:N4}| {33:N2} {34:N2} {35:N2} {36:N2}'n",
outputLine.Time, outputLine.HlState, outputLine.HLX, outputLine.HLY, outputLine.HLZ, outputLine.KlState, outputLine.KLX, outputLine.KLY, outputLine.KLZ, outputLine.AlState, outputLine.ALX, outputLine.ALY, outputLine.ALZ, outputLine.FlState, outputLine.FLX, outputLine.FLY, outputLine.FLZ, outputLine.HrState, outputLine.HRX, outputLine.HRY, outputLine.HRZ, outputLine.KrState, outputLine.KRX, outputLine.KRY, outputLine.KRZ, outputLine.ArState, outputLine.ARX, outputLine.ARY, outputLine.ARZ, outputLine.FrState, outputLine.FRX, outputLine.FRY, outputLine.FRZ, outputLine.LKFX, outputLine.LKVG, outputLine.RKFX, outputLine.RKVG);
}
Console.WriteLine("{0}, {1}", i, table.Rows.Count);
string sql = string.Format("SELECT * FROM {0}", tableName);
using (SQLiteDataAdapter da = new SQLiteDataAdapter(sql, con)){
using (new SQLiteCommandBuilder(da)){
da.Update(table);
}
}
con.Close();
#endregion
}
}
#region
//for input file
//fileName = dlg.FileName;
//OutputConsole.Text = " ";
//OutputConsole.Text = fileName;
//for output file
//dataName = sdg.FileName;
#endregion
}
}
private void btnExit_Click(object sender, RoutedEventArgs e){
Environment.Exit(0);
}
private void btnInfo_Click(object sender, RoutedEventArgs e){
Environment.CurrentDirectory = Environment.GetEnvironmentVariable("windir");
DirectoryInfo info = new DirectoryInfo(".");
lock (info){
OutputConsole.Text = String.Format("Directory info: " + info.FullName);
}
}
}
public class MyData{
public MyData(string[] values){
Time = int.Parse(values[0]);
HlState = int.Parse(values[1]);
HLX = double.Parse(values[2]);
HLY = double.Parse(values[3]);
HLZ = double.Parse(values[4]);
KlState = int.Parse(values[5]);
KLX = double.Parse(values[6]);
KLY = double.Parse(values[7]);
KLZ = double.Parse(values[8]);
AlState = int.Parse(values[9]);
ALX = double.Parse(values[10]);
ALY = double.Parse(values[11]);
ALZ = double.Parse(values[12]);
FlState = int.Parse(values[13]);
FLX = double.Parse(values[14]);
FLY = double.Parse(values[15]);
FLZ = double.Parse(values[16]);
HrState = int.Parse(values[17]);
HRX = double.Parse(values[18]);
HRY = double.Parse(values[19]);
HRZ = double.Parse(values[20]);
KrState = int.Parse(values[21]);
KRX = double.Parse(values[22]);
KRY = double.Parse(values[23]);
KRZ = double.Parse(values[24]);
ArState = int.Parse(values[25]);
ARX = double.Parse(values[26]);
ARY = double.Parse(values[27]);
ARZ = double.Parse(values[28]);
FrState = int.Parse(values[29]);
FRX = double.Parse(values[30]);
FRY = double.Parse(values[31]);
FRZ = double.Parse(values[32]);
LKFX = double.Parse(values[33]);
LKVG = double.Parse(values[34]);
RKFX = double.Parse(values[35]);
RKVG = double.Parse(values[36]);
}
public int Time { get; set; }
public int HlState { get; set; }
public double HLX { get; set; }
public double HLY { get; set; }
public double HLZ { get; set; }
public int KlState { get; set; }
public double KLX { get; set; }
public double KLY { get; set; }
public double KLZ { get; set; }
public int AlState { get; set; }
public double ALX { get; set; }
public double ALY { get; set; }
public double ALZ { get; set; }
public int FlState { get; set; }
public double FLX { get; set; }
public double FLY { get; set; }
public double FLZ { get; set; }
public int HrState { get; set; }
public double HRX { get; set; }
public double HRY { get; set; }
public double HRZ { get; set; }
public int KrState { get; set; }
public double KRX { get; set; }
public double KRY { get; set; }
public double KRZ { get; set; }
public int ArState { get; set; }
public double ARX { get; set; }
public double ARY { get; set; }
public double ARZ { get; set; }
public int FrState { get; set; }
public double FRX { get; set; }
public double FRY { get; set; }
public double FRZ { get; set; }
public double LKFX { get; set; }
public double LKVG { get; set; }
public double RKFX { get; set; }
public double RKVG { get; set; }
}
}
它是微妙的，还是实际上是错误的?我需要修复行数问题和移动数据类型。
以下是MainWindow的XAML代码，因此您不必复制它:
<Window x:Class="C_Sharp_SQLite_Testbed.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="350" Width="525">
<Grid Background="LightGray">
<Button x:Name="btnLoad" Content="Load CSV File to Database" HorizontalAlignment="Left" Margin="10,258,0,0" VerticalAlignment="Top" Width="153" Click="btnLoad_Click"/>
<Button x:Name="btnExit" Content="Exit" HorizontalAlignment="Left" Margin="312,258,0,0" VerticalAlignment="Top" Width="75" Click="btnExit_Click"/>
<TextBlock x:Name="OutputConsole" HorizontalAlignment="Left" Margin="10,10,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Height="243" Width="497" Background="Black" Foreground="#FF00E800" FontFamily="Lucida Console"/>
<Button x:Name="btnInfo" Content="Directory Information" HorizontalAlignment="Left" Margin="168,258,0,0" VerticalAlignment="Top" Width="139" Click="btnInfo_Click"/>
<TextBox x:Name="txtTableName" HorizontalAlignment="Left" Height="23" Margin="200,285,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="120"/>
<Label Content="Enter name for table in database:" HorizontalAlignment="Left" Margin="10,282,0,0" VerticalAlignment="Top"/>
</Grid>
</Window>
项目的类型是一个控制台项目，所以你需要在打开一个新的WPF项目后设置它。
也许还有一个愚蠢的问题:我在写完这些文件后等待打开其中一个文件的时间有什么关系吗?
由于这里发布的代码是正确的，所以行数较少的问题来自于在插入完成之前退出应用程序。
整数显示为小数的问题是由于工具被用来查看SQLite文件，而不是实际的数据库本身。