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

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?

The easier way is to use a SQLCommandbuilder. The command builder take a Select query and creates 3 additional commands : Insert, Update, Delete. Then you can put your data into a datatable using a SQLDataAdapter. When you change the datatable you can use an update() method which automatically updates the database. See following webpage : https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder(v=vs.110).aspx — jdweng, Aug 23 '15 at 11:59

score 0 · Answer 1 · edited May 23 '17 at 11:52

The ID of any Table should have AUTO_INCREMENT and be of BIGINT or INT type, and code never set the ID, the SQL Server does that.

To update you would make a new SQLCommand of and update type.

string connStr = @"Data Source=ANLZ\SQLEXPRESS;Initial Catalog=testdb; Trusted_Connection=True;";
int updateId = int.Parse(formTextBox.Text); //Or where ever you set the ID to when it is pulled from the database.
string updateCommand = "UPDATE tbl_test SET [surname]=@surname WHERE ID = @id"; 

using (OleDbConnection conn = new OleDbConnection(connStr))
{
    using (OleDbCommand comm = new OleDbCommand())
    {
        comm.Connection = conn;
        comm.CommandText = updateCommand;
        comm.CommandType = CommandType.Text
        comm.Parameters.AddWithValue("@surname", items[1])
        comm.Parameters.AddWithValue("@id",updateId);
        try
        {
            comm.Open();
            conn.ExecuteNonQuery();
        }
        catch(OleDbException ex)
        {
            //Do some error catching Messagebox/Email/Database entry 'Or Nothing'
        }
    }
}

Deleting is Much easier

string connStr = @"Data Source=ANLZ\SQLEXPRESS;Initial Catalog=testdb; Trusted_Connection=True;";
int updateId = int.Parse(formTextBox.Text); //Or where ever you set the ID to when it is pulled from the database.
string deleteComand = "Delete FROM tbl_test WHERE ID = @id";

using (OleDbConnection conn = new OleDbConnection(connStr))
{
    using (OleDbCommand comm = new OleDbCommand())
    {
        comm.Connection = conn;
        comm.Parameters.AddWithValue("@id", updateId);
        try
        {
            comm.Open();
            conn.ExecuteNonQuery();
        }
        catch (OleDbException ex)
        {
            //Do some error catching Messagebox/Email/Database entry 'Or Nothing'
        }
    }
}

Couple things to Note - Using statements, and Try Catch Blocks.

Using will Dispose of and Connection or other object that has a Dispose implemented.

Try Catch will grab any errors that come from doing the Database call, unable to connect, or the Row to Update could not be updated.

How i can change that Update codes?

public string updateQuery = "UPDATE tbl_test SET name=@name,surname=@surname,phone=@phone,address=@address,date=@date WHERE id=@id ";

// ...

conn = new SqlConnection(connStr);
try
{
    string[] importfiles = Directory.GetFiles(@"C:\Users\An\Desktop\", "test.txt");

    using (conn)
    {
        using (cmd = new SqlCommand(updateQuery, 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);
            cmd.Parameters.Add("@id", SqlDbType.Int);

            foreach (string importfile in importfiles)
            {
                string[] allLines = File.ReadAllLines(importfile);
                conn.Open();

                for (int index = 0; index < allLines.Length; index++)
                {
                    string[] items = allLines[index].Split(new[] { '|' })
                        .Select(i => i
                        .Split(new[] { '=' })[1])
                        .ToArray();

                    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();
            }

        }
    }
}

This is for delete

public string deleteQuery = "DELETE FROM tbl_test WHERE id=@id";

// ...

conn = new SqlConnection(connStr);
try
{
    string[] importfiles = Directory.GetFiles(@"C:\Users\An\Desktop\", "test.txt");

    using (conn)
    {
        using (cmd = new SqlCommand(deleteQuery, baglanti))
        {
            cmd.Parameters.Add("@id", SqlDbType.Int);

            foreach (string importfile in importfiles)
            {
                string[] allLines = File.ReadAllLines(importfile);
                conn.Open();

                for (int index = 0; index < allLines.Length; index++)
                {
                    string[] items = allLines[index].Split(new[] { '|' })
                        .Select(i => i
                        .Split(new[] { '=' })[1])
                        .ToArray();

                    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();
            }
        }
    }
}

For Select it is a little more involved.

**public string selectQuery= "Select * FROM tbl_test" ; 
conn = new SqlConnection(connStr);
try
{
    using (conn)
    {
        using (cmd = new SqlCommand(selectQuery, conn))
        {
            using(var dataReader = cmd.ExecuteReader())
            {
                while(datareader.reader())
                {
                     //Read the datareader for values and set them .
                     var id = datareader.GetInt32(0);
                }
            }

        }
    }
}**

how can i pull id number from database? that column is auto_increment. I'm not using textbox — anilleuss, Aug 23 '15 at 18:16
Does not always work? If it works once, then why does it not work again? The index being deleted is not in the Table? — juanvan, Sep 06 '15 at 22:36

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

1 Answers1