将文本文件转换为SQL数据库表 [英] Convert text file to SQL database table

问题描述

我正在使用此代码....但只有o和1插入数据库colomnCOLOMN_1

任何身体帮助我

可以将此为循环是错的？

i am using this code ....but only o and 1 are inserted in database colomn "COLOMN_1"
any body help me
may bi this for loop is wrong??

StreamReader reader = new StreamReader(File.OpenRead(@"D:\GEODATASOURCE-COUNTRY.TXT"));
                List<string> listA = new List<string>();
                List<string> listB = new List<string>();
                List<string> listC = new List<string>();
                List<string> listD = new List<string>();
                //string vara1, vara2, vara3, vara4;
                while (!reader.EndOfStream)
                {
                    string line = reader.ReadLine();
                    if (!String.IsNullOrWhiteSpace(line))
                    {
                        string[] values = line.Split('\t');
                        if (values.Length >= 4)
                        {
                            listA.Add(values[0]);
                            listB.Add(values[1]);
                            listC.Add(values[2]);
                            listD.Add(values[3]);
                        }
                    }
                }
                string[] firstlistA = listA.ToArray();
                string[] firstlistB = listB.ToArray();
                string[] firstlistC = listC.ToArray();
                string[] firstlistD = listD.ToArray();
                for (int i = 0; i < firstlistA.Length; i++)
                {
                    
                
                    
                    string query = "INSERT INTO Country (COLOMN_1) VALUES('" +i.ToString()  + "')";
                    con.ConnectionString = @"";
                    SqlCommand com = new SqlCommand(query, con);
                    con.Open();
                    com = con.CreateCommand();
                    com.CommandText = query;

                    int c = com.ExecuteNonQuery();
                    if (c > 0)
                    {
                        con.Close();

                    }
                    else
                    {
                        con.Close();

                    }



                }
            }
        }

已添加代码块[ /编辑]

Code block added[/Edit]

推荐答案

是的。它将是。

Well yes. It will be.

for (int i = 0; i < firstlistA.Length; i++)

string query = "INSERT INTO Country (COLOMN_1) VALUES('" +i.ToString() + "')";

你期望做什么？

如果你想插入列表内容，那么我会使用forech循环而不是for：

What did you expect that to do?
If you wanted to insert the list content, then I would use a forech loop instead of a for:

foreach (string country in firstListA)

或使用SQL查询将每个列表的内容插入到行中，具体取决于您正在执行的操作。



BTW：不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。



它正在工作......但如果我想使用

or use an SQL query to insert the contents of each list into the row, depending on what you are doing.

BTW: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

"its working ... but if i want use

string[] firstlistA = listA.ToArray(); 
string[] firstlistB = listB.ToArray(); 
string[] firstlistC = listC.ToArray(); 
string[] firstlistD = listD.ToArray();

所有这些数组然后如何将它们插入数据库4 feild table ???



那么你需要一个的循环 - 但你无需将列表转换为数组。您可以通过它的索引访问列表，就像您可以使用数组一样：

all these arrays then how to insert them in database 4 feild table???"

Then you need a for loop - but you don''t need to convert lists to arrays anyway. You can access a list via it''s index just as you can an array:

string sql = "INSERT INTO Country (COLOMN_1, COLOMN_2, COLOMN_3, COLOMN_4) VALUES (@C1, @C2, @C3, @C4)";
for (int i = 0; i < listA.Count; i++)
    {
    using (SqlCommand cmd = new SqlCommand(sql, con))
       {
       cmd.Parameters.AddWithValue("@C1", listA[i]);
       cmd.Parameters.AddWithValue("@C2", listB[i]);
       cmd.Parameters.AddWithValue("@C3", listC[i]);
       cmd.Parameters.AddWithValue("@C4", listD[i]);
       cmd.ExecuteNonQuery();
       }
    }

这篇关于将文本文件转换为SQL数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！