none
How to update and delete data without a few words from text file RRS feed

  • Question

  • My database tables includes columns like that id,name,surname,phone,address,date. Id is automatically increasing.

    name=joe|surname=clark|phone=23132131|address=jdsakldjakldja|date=11.02.2015 14:30:45
    name=betty|surname=ugly|phone=32112121|address=dsadaewqeqrsa|date=11.02.2015 14:30:45

    This is my INSERT codes

    string connStr = @"Data Source=ANLZ\SQLEXPRESS;Initial Catalog=testdb; Trusted_Connection=True;"; string createQuery = "INSERT INTO tbl_test(name,surname,phone,address,date) VALUES(@name,@surname,@phone,@address,@date)"; SqlConnection conn; SqlCommand cmd; string[] importfiles = Directory.GetFiles(@"C:\Users\An\Desktop\", "test.txt"); using (conn) { using (cmd = new SqlCommand(createQuery, conn)) { cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50); cmd.Parameters.Add("@surname", SqlDbType.NVarChar, 50); cmd.Parameters.Add("@phone", SqlDbType.NVarChar, 50); cmd.Parameters.Add("@address", SqlDbType.NVarChar, 200); cmd.Parameters.Add("@date", SqlDbType.DateTime); foreach (string importfile in importfiles) { string[] allLines = File.ReadAllLines(importfile); baglanti.Open(); for (int index = 0; index < allLines.Length; index++) { string[] items = allLines[index].Split(new[] { '|' }) .Select(i => i .Split(new[] { '=' })[1]) cmd.Parameters["@name"].Value = items[0]; cmd.Parameters["@surname"].Value = items[1]; cmd.Parameters["@phone"].Value = items[2]; cmd.Parameters["@address"].Value = items[3]; cmd.Parameters["@date"].Value = items[4]; cmd.ExecuteNonQuery(); } conn.Close(); } } }

    I would like to update and delete certain text to my database. Also i don't need to save same records with same id. How can i do it?

    • Moved by Kristin Xie Tuesday, August 25, 2015 3:10 AM move to appropriate forum
    Sunday, August 23, 2015 11:23 AM

All replies

  • "Also i don't need to save same records with same id."

    What do you mean? No database will allow you to enter 2 rows with the same id if the id is defined as a primary key.

    So, if your datebase is configured correctly, you'll get an error, anyway, the insertion will not happen.

     

    Noam B.


    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...

    Sunday, August 23, 2015 2:02 PM
  • for example,

    id = 3, name joe

    when i changed name id should be same (id=3) and name will be change (james).

    I would like that

    Sunday, August 23, 2015 2:30 PM
  • Do you IDs in your text file "test.txt"?

    If yes, get the IDs from that file as well like you get other table properties and insert that too.

    cmd.Parameters["@Id"].Value = items[5]; // I do not know what is the index of Id


    Sunday, August 23, 2015 2:59 PM
  • No. Text file doesn't have id column. Id column in database table

    name=joe|surname=clark|phone=23132131|address=jdsakldjakldja|date=11.02.2015 14:30:45



    Sunday, August 23, 2015 3:14 PM
  • Than you need to find something unique in each record such as phone number.

    Query your database with the phone number in the text line and get the ID value from there and than update the SQL query . Use a SQL query like this:

    string findQueryId = "SELECT ID FROM tbl_test WHERE phone=(@Id)"

    command.Parameters.AddWithValue("@Id", items[2]);

    Check if findQueryId null or not and accordingly Insert or Update the record.

    For updating add an update command:

    "UPDATE INTO tbl_test(name,surname,phone,address,date)

    VALUES(@id, name,@surname,@phone,@address,@date)

    WHERE Id=(@findQueryId)";


    There can be typos but basically this logic will work.

    Sunday, August 23, 2015 3:42 PM
  • it doesn't work
    Sunday, August 23, 2015 8:47 PM
  • Hi Anıl,

    For Visual C# forum is  for C# code issues inspecific.  Based on your code, your case more related to Data platform development using ADO.NET managed providers. I moved your issue to

    Data Platform Development > ADO.NET Managed Providers forum for better support.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 25, 2015 3:12 AM
  • it doesn't work

    What is the error? Or what is the problem you have?
    Wednesday, September 2, 2015 9:00 AM
  • You can't just say "it doesn't work" without telling us what the problem is, what the error message is and, most importantly, what your code now looks like after trying out @Val's suggestion. We're not mind readers ...

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 5, 2015 9:02 PM