如何将表从数据库转换为.txt(文本)格式? [英] how to convert a table from database to .txt(text) format?

问题描述

大家好，

我编写了将数据库表转换为记事本的代码，并且可以正常工作，但是我的问题是，当用户在表中进行更新时(如果他更改了表中的任何字段，例如firstname或lastname或其他任何内容)还需要在记事本中对其进行更新.
我编写的用于转换为记事本的代码是:

Hi all,

I wrote the code for converting a database table into a notepad and its working fine, but my problem is that when a user makes an update in the table (if he changed any fields in table like firstname or lastname or anything) it needs to be updated in the notepad also.
The code I wrote for conversion to notepad is:

  private void Form1_Load(object sender, EventArgs e)
        { 
          Timer MyTimer = new Timer();       
          MyTimer.Interval = (10000); 
          MyTimer.Tick += new EventHandler(timer1_Tick_1);      
          MyTimer.Start();         
        }

 private void timer1_Tick_1(object sender, EventArgs e)
        {
            employeedetails();
        }

public void employeedetails()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["nConnectionString"].ConnectionString);

            // using (SqlConnection connection = new SqlConnection(str))
            {
                try
                {
                    conn.Open();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {

                    return;
                }
                string selectCommandText = "SELECT distinct      userid,fullname,lastname,firstname,localjobtitle,employmentstatusid,dateofjoining,dateofexit,siteid,email,topfunctionid,active,exitingthecompany,lmsroleid,locallanguagejobtitle FROM tblUserProfile up,tblreportingto tr,tblCountry where temo is null ";
                using (SqlDataAdapter adapter = new SqlDataAdapter(selectCommandText, conn))
                {
                    using (DataTable table = new DataTable("tblUserProfile"))
                    {
                        adapter.Fill(table);
                        StringBuilder commaDelimitedText = new StringBuilder();
                        //commaDelimitedText.Append("EMP_Number|Name|Lastname|Firstname|Middleinitial|NameSuffix|Positiontitle|EMPcode|EMPstatus|Manager number|Approvernumber|Startdate|Enddate|Lastreviewdate|Address1|Address2|City|State|Zipcode|Country|Email|Phone1|Phone2|Fax|URL|Note|DomainCode|Organizationcode|Jobcode|Active|Enabled|Username|NTlogin|Role|Timezone|Currency|Language|Text1|Text2|Text3|Text4|Memo1|Date1|Date2|Indicator1|Money1|Integer1|Float1|JobJoiningdate|Organizationjoiningdate");
                        foreach (DataRow row in table.Rows)
                        {
                            string value = string.Format("{0}|{1}|{2}|||{3}||{4}|{5}||||||{6}||{7}|{8}|{9}||||||||{10}|{11}|{12}|{13}|", row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14]);
                            commaDelimitedText.AppendLine(value);
                        }

                        File.AppendAllText(@"C:\Documents and Settings\aj99823\Desktop\project\Employee Details.txt", commaDelimitedText.ToString());

                    } 
                }
            }
        }

我是编程的新手.

我以为原因是因为我在使用计时器，所以在每个间隔中它将文本附加到记事本中.因此重复的数据传到了记事本中.

任何人都可以帮助我.

I am a new comer to programming.

I thought the reason is since I am using a timer, in every interval it will append the text to notepad. So repeated data are coming to the notepad.

Can anyone help me.

推荐答案

上面提供的代码将无限期地将记录追加到文件中，而不管数据的状态如何. AppendAllText 将继续将相同的数据追加到文件中.在提供的代码中创建的文件类型似乎表明它是定界符分隔值文件.这些通常是指读/写的单向操作(请注意，不包括更新和插入).一些建议:

1.提供的代码使用的是 DataTable 中的数据a>更新文件.因此，请确保在用户进行更改时有代码可更新DataTable，否则新数据将永远不会保存到文件中.具体来说，将需要更新数据库以包括用户更改.

2.考虑重新添加文件.提供的代码将在每次计时器迭代时继续将DataTable中的所有数据追加到文件中，从而生成重复数据.通过创建/打开文件，覆盖其中的文本并关闭文件，可以防止出现重复数据问题.为此，需要组合文件方法:OpenWrite 和 LINQ-to-CSV库-代码项目和 FileHelpers .

4.改用XML文件. .NET框架对XML文件管理有很好的支持.可以在MSDN上找到有关此内容的更多信息.请参考 XMLDocument ，在C#中创建，修改和读取XML文件.

The code provided above will append records to the file indefinitely no matter the state of the data. AppendAllText will keep appending the same data to the file. The type of file created in the code provided seems to indicate it is meant to be delimiter separated value file. These are generally meant to be one-way operations of Read/Write (note the exclusion of Update and Insert). A few suggestions:

1. The code provided is using data from the DataTable to update the file. Therefore, make sure there is code to update the DataTable when a user makes a change, otherwise the new data will never make it to the file. The database, specifically, will need to be updated to include the user changes.

2. Consider recreating the file as apposed to appending. The code provided will keep appending all of the data in the DataTable upon each timer iteration generating duplicate data in the file. By creating/opening the file, overwriting the text inside it, and closing the file will prevent the duplicate data issue. To do this a combination of File methods will be needed: OpenWrite and WriteAllText

3. Assuming scalability was in mind, parsing the file will be needed. The idea is to open the file, look for the particular record, update the record, and then save/close the file. To parse the file there must be a way to uniquely identify the record to update. At this point, a text file is beginning to be more unsuitable from a maintenance stand point. There are libraries available to manage CSV files such as LINQ-to-CSV Library - Code Project and FileHelpers.

4. Use an XML file instead. The .NET framework has great support for XML file management. More information about this can be found on MSDN. Please reference XMLDocument, DataSet and XmlDataDocument Synchronization, and Create, Modify and Read XML Files in C#.

这篇关于如何将表从数据库转换为.txt(文本)格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！