C# - 将第一列从文本文件插入数据库

Question

我想从文本文件中读取并将他的第一列作为行插入到数据库中。

这是文本文件的样子:

1264311 | SMS_DR | DELIVERED
 373414 | SMS_DR | EXPIRED
    418 | SMS_DR | UNDELIVERABLE
1628984 | SMS_MT | 

我只想获取具有整数值的第一列，并将它们作为一行插入到我的数据库中:

DELIVERED   EXPIRED     UNDELIVERABLE       BLABLA
1264311     373414      418                 1628984 

当我运行我的代码时，它会插入所有 4 行，这是我的代码:

sr1 = new StreamReader("C:\\Users\\adamoui\\Desktop\\Statjbm_20161009.txt");

string[] allLines1 = File.ReadAllLines("C:\\Users\\adamoui\\Desktop\\Statjbm_20161009.txt");

for (int i = 0; i < allLines1.Length; i++)
{                
    string[] column = allLines1[i].Split(new char[] { '|' });
    SqlCommand cmdJBM = new SqlCommand("INSERT INTO dbo.StatJBM_NEW (Noeud, Total_MT, Date, DELIVERED, EXPIRED, UNDELIVERABLE) VALUES (@Noeud, @Total_MT, @Date, @DELIVERED, @EXPIRED, @UNDELIVERABLE)", con);
    cmdJBM.Parameters.AddWithValue("@Noeud", "JBM");
    cmdJBM.Parameters.AddWithValue("@DELIVERED", column[0]);
    cmdJBM.Parameters.AddWithValue("@EXPIRED", column[0]);
    cmdJBM.Parameters.AddWithValue("@UNDELIVERABLE", column[0]);
    cmdJBM.Parameters.AddWithValue("@Total_MT", column[0]);
    cmdJBM.Parameters.AddWithValue("@Date", DateTime.Now.AddDays(-1));
    cmdJBM.ExecuteNonQuery();                
}
con.Close();

结果:

Answer 1

您的代码为每一行插入一个，使用第一列作为所有参数。可能不是你的想法。试试这个:

var values = allLines1.Split('|')
                      .Select(arr => arr[0])
                      .ToArray();

SqlCommand cmdJBM = new SqlCommand(@"INSERT INTO dbo.StatJBM_NEW
                                    (Noeud, Total_MT, Date, DELIVERED, EXPIRED,
                                    UNDELIVERABLE) VALUES (@Noeud, @Total_MT, @Date,
                                    @DELIVERED, @EXPIRED, @UNDELIVERABLE)", con);
cmdJBM.Parameters.AddWithValue("@Noeud", "JBM");
cmdJBM.Parameters.AddWithValue("@DELIVERED", values[0]);
cmdJBM.Parameters.AddWithValue("@EXPIRED", values[1]);
cmdJBM.Parameters.AddWithValue("@UNDELIVERABLE", values[2]);
cmdJBM.Parameters.AddWithValue("@Total_MT", values[3]);
cmdJBM.Parameters.AddWithValue("@Date", DateTime.Now.AddDays(-1));
cmdJBM.ExecuteNonQuery();