CODEWITHSUNDEEP
c#sql
user3361813
user3361813

Reputation: 1

handle sql replication issues in c#

I am new to this sql replication. We have 4 subscribers. I have one column in one of the table has unique key. the data type is nvarchar. if subscriberA inserts a row with 'zxc' and subscriber B inserts a row with 'zxc'. One of the insert fails. how can i handle this in the application show the user proper message to the users.

For example I have Two Subscribers:

1.SubA
2.SubB

I have a table.

Table name : Names

I have City column(nvarchar) in my Names table and it is unique key.

User 'A' connects to SubA. user 'B' connects to SubB.

'A' inserts a row with 'JAKARTA' in to Names table @ 10:30 am. It will take 20 min to update the publisher. 'B' inserts a row with 'JAKARTA' in to Names table @10:35 am.

I have the unique constraint on the table so User A's input is updated in the publisher @10:50. But user B's input caught in the conflicts.

I want the city column should be unique across all the subs. How could i handle this? How should i display proper message for user B? How should i do validation across all the subscribers?

My application is winforms. I am using textbox for city. I am validating on button click event. Ideal solution will be if i can able to capture this in click event and display Message like "One record already exist with this name.try again."

private int Validation( string str)
    {
        SqlConnection conn = new SqlConnection("ConnectionString");
        conn.Open();
        SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM dbo.Names  where city = @str", conn);

        cmd.Parameters.Add("@City", SqlDbType.VarChar, 100);
        cmd.Parameters["@City"].Value = str;

        int count = (Int32) cmd .ExecuteScalar();
        conn.Close();
        return count;
     }

    private void button1_Click(object sender, System.EventArgs e)
    {
        try
        {
            if(Validation(testbox1.text) == 0)
            {
                SqlConnection conn = new SqlConnection("ConnectionString");
                string  sql = "INSERT INTO dbo.Names ( city) VALUES( @City)";
                SqlCommand cmd = new SqlCommand(sql, conn);                    
                cmd.Parameters.Add("@City", SqlDbType.VarChar, 100);
                cmd.Parameters["@Name"].Value = textbox1.text;
                cmd.ExecuteNonQuery();
            }
            else
            {
                //display message
            }
    }
    catch(Exception ex)
    {

    }  
}

Let me know if you need more information

Upvotes: 0

Views: 90

Answers (1)

Andrew B
Andrew B

Reputation: 61

This is an application/database design issue. In this case the first subscriber to synchronize their local copy of the Names table with the publication server wins and the second will get an error. The best solution is to avoid collisions altogether and add another column as part of the unique key (subscriber id?) which will make the record unique or just remove the unique constraint altogether and have a server process cull the duplicate city names later if needed.

Upvotes: 2

Related Questions