none
Moving a Record after the record is marked delivered RRS feed

  • Question

  • Hi,

    Haven't found the right answer for this, and don't know if im searching for the right question..

    I have two databases, where database 1 contains orders for delivery. And database 2 is a database of delivered orders.

    Now, I want to move the record from database 1 to database 2 after the record is marked delivered.

    Is there a good way to do this?

    My Applications is just a simple Windows Forms App.

    Tuesday, February 19, 2019 5:01 PM

All replies

  • Hello,

    See my code sample where I use a stored procedure as shown below to INSERT a specific record by it's primary key into another table in the same database but you can modify the stored procedure to INSERT into another database on the same server by changing (in this case) dbo.PersonsDeleted to another catalog e.g. SomeotherDatabase.dbo.TableName.

    Here is the SP performing insert/remove in the same database which needs to be modified to work with two databases.

    CREATE PROCEDURE [dbo].[MovePersonToDeletedTable] (@id AS INT) AS 
    BEGIN 
    BEGIN TRAN 
        INSERT INTO dbo.PersonsDeleted (id,FirstName,LastName,GenderIdentifier) SELECT id,FirstName,LastName,GenderIdentifier FROM dbo.Persons2 AS p WHERE p.id = @id 
        IF (@@ERROR <> 0) BEGIN 
            ROLLBACK TRAN 
            RETURN 1 
        END 
        DELETE FROM dbo.Persons2 WHERE id = @id 
        IF (@@ERROR <> 0) BEGIN 
            ROLLBACK TRAN 
            RETURN 1 
        END 
    COMMIT TRAN 
    RETURN 0 
    END 
    Another method is SELECT INTO with a WHERE condition on the record's primary key


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, February 19, 2019 6:02 PM
    Moderator
  • This is something that can be done if you were using a layered style of presentation layered, the windows form project, and the presentation layer had reference to a data access layer. The Windows form can just call an object in the DAL to do it data access logic.

    https://docs.microsoft.com/en-us/previous-versions/msp-n-p/ee658117(v=pandp.10)

    https://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET

    The above architectural style concerning layered impalements seperation of concerns.

    https://en.wikipedia.org/wiki/Separation_of_concerns

    Tuesday, February 19, 2019 6:12 PM
  • Hi Experimentus,

    Thank you for posting here.

    For your question, you want to move the record from database 1 to database 2 after the record is marked delivered.

    I make a simple code to get it, you could refer to it.

    private void button1_Click(object sender, EventArgs e)
    
            {
    
                string strcon = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Data1;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
    
                SqlConnection sqlConnection = new SqlConnection(strcon);
    
                sqlConnection.Open();
    
                string sql = "update test1 set Status=1 where Status=0";
    
                using (SqlCommand cmd = new SqlCommand(sql, sqlConnection))
    
                {
    
                    cmd.ExecuteNonQuery();                               //deliver
    
                }
    
                string sql2 = "select * from test1 where Status=1";
    
                SqlDataAdapter sda = new SqlDataAdapter(sql2, sqlConnection);
    
                DataSet dataSet = new DataSet();
    
                sda.Fill(dataSet, "test1");
    
                foreach (DataRow item in dataSet.Tables["test1"].Rows)
    
                {
    
                    string m = item["Order"].ToString();
    
                    string n= item["Status"].ToString();
    
                    string strcon2 = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Data2;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
    
                    SqlConnection sqlConnection2 = new SqlConnection(strcon2);
    
                    sqlConnection2.Open();
    
                    string sql3 =string.Format ("INSERT INTO test2 values('{0}','{1}' ) ",m,n);
    
                    using (SqlCommand cmd = new SqlCommand(sql3, sqlConnection2))
    
                    {
    
                        cmd.ExecuteNonQuery();                     //add delivered order
    
                    }
    
                    sqlConnection2.Close();
    
                }
    
                string sql1 = "delete from test1 where status=0";
    
                using (SqlCommand cmd = new SqlCommand(sql1, sqlConnection))
    
                {
    
                    cmd.ExecuteNonQuery();
    
                }
    
                sqlConnection.Close();
    
                load1();
    
                load2();
    
                MessageBox.Show("success");
    
            }
    
            private void Form1_Load(object sender, EventArgs e)
    
            {
    
                load1();
    
                load2();
    
            }
    
            private void load1()
    
            {
    
                string strcon = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Data1;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
    
                SqlConnection sqlConnection = new SqlConnection(strcon);
    
                sqlConnection.Open();
    
                string sql = "select *from test1";
    
                SqlDataAdapter sda = new SqlDataAdapter(sql, sqlConnection);
    
                DataSet dataSet = new DataSet();
    
                sda.Fill(dataSet, "test1");
    
                dataGridView1.DataSource = dataSet;
    
                dataGridView1.DataMember = "test1";
    
                sqlConnection.Close();
    
            }
    
            private void load2()
    
            {
    
                string strcon = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Data2;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
    
                SqlConnection sqlConnection = new SqlConnection(strcon);
    
                sqlConnection.Open();
    
                string sql = "select *from test2";
    
                SqlDataAdapter sda = new SqlDataAdapter(sql, sqlConnection);
    
                DataSet dataSet = new DataSet();
    
                sda.Fill(dataSet, "test2");
    
                dataGridView2.DataSource = dataSet;
    
                dataGridView2.DataMember = "test2";
    
                sqlConnection.Close();
    
            }

    Result:

    Hope my solution could be helpful.

    Best regards,

    Jack



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, February 20, 2019 7:56 AM
    Moderator