ExecuteNonQuery Violation of Primary Key after putting value?

392 Views Asked by At

I'm trying to write a CRUD app and I have a problem with the Create method. Program is crashing with error

System.Data.SqlClient.SqlException: 'Violation of PRIMARY KEY constraint 'PK_Pracownicy'. Cannot insert duplicate key in object 'dbo.Pracownicy'. The duplicate key value is (11). The statement has been terminated.'

But I can see in my SQL Server that this position is added to Pracownicy table, so I don't know where is a problem. Its look like the error is generated after I put new values to table

enter image description here

class SqlHelper
{
    public int RowsLenght { get; set; }
    private SqlConnection sqlConnection;
    public string Command { get; set; }

    public SqlHelper(string connectionString)
    {
        sqlConnection = new SqlConnection(connectionString);
        sqlConnection.Open();
       
    }
    public int Create(string command, SqlParameter []parameters)
    {
        //wykonanie polecenia na bazie
        using var cmd = new SqlCommand(command, sqlConnection);
        cmd.Parameters.AddRange(parameters);
        ShowTable(cmd);
        return cmd.ExecuteNonQuery();
    }
    public int Read(string command)
    {
        //wykonanie polecenia na bazie
        using var cmd = new SqlCommand(command, sqlConnection);
        ShowTable(cmd);
        return cmd.ExecuteNonQuery();
    }
    private int ShowTable(SqlCommand command)
    {
        var reader = command.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine(reader.GetInt32(0) + "\t" + reader.GetString(2) + " " +
                reader.GetString(1) + "\t" + reader.GetString(3));
            RowsLenght++;
        }
        reader.Close();
        return RowsLenght;
    }
}

class Program
{
    static void Main()
    {
        SqlConnectionStringBuilder connectionString = new SqlConnectionStringBuilder
        {
            DataSource = @"HAL\MSSERVER",
            InitialCatalog = "ZNorthwind",
            IntegratedSecurity = true,
            ConnectTimeout = 30,
            Encrypt = false,
            TrustServerCertificate = false,
            ApplicationIntent = 0,
            MultiSubnetFailover = false
        };
        var connectionS = connectionString.ConnectionString;
        SqlHelper sql = new SqlHelper(connectionS);

        var command = "SELECT * FROM dbo.Pracownicy";
        sql.Read(command);

        command = "INSERT INTO dbo.Pracownicy (IDpracownika, Nazwisko, Imię, Stanowisko) VALUES (@IDpracownika, @Nazwisko, @Imie, @Stanowisko)";
        var parameters = SetUpParameters(sql.RowsLenght);
        sql.Create(command, parameters);
    }
    private static SqlParameter[] SetUpParameters(int lenght)
    {
        //inicjalizacja zmiennych:
        Console.WriteLine("Podaj imie nowego pracownika: ");
        var fname = Console.ReadLine();
        Console.WriteLine("Podaj nazwisko pracownika: ");
        var lname = Console.ReadLine();
        Console.WriteLine("Podaj stanowisko pracownika: ");
        var position = Console.ReadLine();

        SqlParameter []param = new SqlParameter[4];
        param[0] = new SqlParameter("IDpracownika", lenght + 1);
        param[1] = new SqlParameter("Imie", fname);
        param[2] = new SqlParameter("Nazwisko", lname);
        param[3] = new SqlParameter("Stanowisko", position);

        return param;
    }
}

Thanks

0

There are 0 best solutions below