如何在没有文字文件的单词的情况下更新和删除数据

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

我的数据库表包括id，姓名，姓名，电话，地址，日期等列。 ID会自动增加。

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

这是我的INSERT代码

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?

我想更新和删除我的数据库中的某些文本。此外，我不需要保存具有相同ID的相同记录。我该怎么做？

1 个解决方案

#1

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.

任何表的ID都应该具有AUTO_INCREMENT并且是BIGINT或INT类型，并且代码永远不会设置ID，SQL Server会这样做。

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

要更新，您将创建一个新的SQLCommand和更新类型。

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.

几点注意事项 - 使用语句和Try Catch Blocks。

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

使用将Dispose和Connection或其他已实现Dispose的对象。

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.

尝试Catch将抓取来自执行数据库调用，无法连接或无法更新行更新的任何错误。

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

        }
    }
}**

#1